Archiv der Kategorie: Access

Microsoft Access bietet die Möglichkeit mit VBA Datenbankanwendungen zu erstellen. Da ich für gewöhnlich aber kein Microsoft Access nutze um Oberflächen zu programmieren, geht es hier hauptsächlich um das Arbeiten mit Daten wenn Access nur als Backend-Dateiformat (*.mdb) genutzt wird oder werden soll.
Erfahrungsgemäß hat das Microsoft Access-Format (*.mdb) Probleme auf 64-BIT-Servern (meistens ist kein Treiber verfügbar wie Microsoft JET) und wenn Datenbanken größer werden als 2 Gigabyte. Ein Vorteil ist das einfache Copy-And-Paste zwischen Access und Excel, was oft von kaufmännischen Kunden gewünscht ist.
Bei konkurrierenden Zugriffen werden oftmals die Datenbanktreiber gefordert um Parallelität zu gewährleisten sowie Transaktionen und SQL-Engines zu simulieren.

MS Access SQL: Case Sensitive Upper Lower Case exact match in WHERE clause / In der WHERE Bedingung Groß- und Kleinbuchstaben berücksichtigen

Problem

MS ACCESS is not recognizing Upper and Lower Case Letters in search conditions within the WHERE-Clause
MS ACCESS berücksichtigt keine Groß- und Kleinbuchstaben in der WHERE-Clause der SQL Anweisungen.

Ansatz – Approach

Usage of the VBA Function StrComp can differentiate between upper and lower case
Die Nutzung der VBA Funktion StrComp kann Unterschiede zwischen Upper und Lower Case (Groß- und Kleinbuchstaben) aufdecken

Lösung – Solution

SELECT * 
FROM t_connector
WHERE StrComp(stereotype, 'Supports', 0)=0 
ORDER BY stereotype ASC

Findet alles was mit großem Supports anfängt, aber filtert alles mit kleinem supports raus.

Sparx Systems Enterprise Architect: Get Images of Diagram By Diagram Name SQL

Problem

The Images of a specific diagram in the data structure of the Sparx Systems Enterprise Architect should be read by diagram name.

Approach

Usage of MDB Plus and Data Mining

Solution

This is SQL for the Access / .eap-File – it should be rewritten for other SQL Servers (i.e. the brackets can be removed and Functions like InStr/Mid shall be replaced)

SELECT	dia.Name As Diagram,
		IIF(Trim(objstart.[Name]) = 'Text', 'Text', objstart.stereotype) As stereotype, 
		diaobj.RectLeft As x,
		diaobj.RectTop As y,
		diaobj.RectRight-diaobj.RectLeft As Width,
		Abs(diaobj.RectBottom-diaobj.RectTop) As Height,
		objstart.Object_ID,
		IIF(Trim(objstart.[Name]) = 'Text', 'Text-'& objstart.Object_ID ,objstart.Alias) As [key],
		IIF(objstart.[Name] = 'Text', objstart.Note, objstart.[Name]) As phaseName,
		objstart.[ea_guid] As [guid],
		diaobj.ObjectStyle,
		IIF(
                    InStr(diaobj.ObjectStyle, "ImageID")>0, 
                    Mid(diaobj.ObjectStyle, InStr(diaobj.ObjectStyle, "ImageID")+8 , Len(diaobj.ObjectStyle)- (InStr( diaobj.ObjectStyle, "ImageID")+8) ),
                    ''
        ) As ImageId     
FROM
((
		[t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop, RectRight, RectBottom, ObjectStyle from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
WHERE objstart.Object_Type IN ('Text','Boundary') AND IIF(InStr(diaobj.ObjectStyle, "ImageID")>0, 
                                                                Mid(diaobj.ObjectStyle, InStr(diaobj.ObjectStyle, "ImageID")+8 , 
                                                                Len(diaobj.ObjectStyle)- (InStr( diaobj.ObjectStyle, "ImageID")+8) ),
                                                                '') <> ''
AND dia.Name='0' 
ORDER BY 12 DESC,1,2,3,4,5,6,7,8,9       

Sparx Systems Enterprise Architect SQL : Query to get the order of objects / elements in the project browser tree

Problem

The order of tree elements in the project browser should be queried in SQL.
Die Reihenfolge der Objekte im Projekt-Browser soll über SQL abgefragt werden

Approach – Ansatz

Usage of the TPos column within the tables t_object and t_package.
Verwendung der TPos Spalte in den Tabellen t_object und t_package.

Solution – Lösung

SELECT b.TPos as Package_Position, 
       b.Name as Package_Name, 
       a.TPos as Object_Tree_Position, 
       a.Object_ID, 
       a.Name as Object_Description
FROM t_object a INNER JOIN t_package b ON a.Package_ID=b.Package_ID 
WHERE Stereotype='Process Step'
ORDER BY 1,2,3

ASP.NET / Sparx Systems Enterprise Architect : Read binary Image from Database ( BLOB ) and show / display it on a webpage

Problem

A binary picture that has been saved in a database or an Access File (i.e. of the Sparx Systems Enterprise Architect) shall be displayed on a web page.

Approach

  1. Create a new ASP.NET Webform and name it GetImage.aspx
  2. Go to the Page_Load function in it
  3. Paste the code under solution in the area in customize according your data structure (here it is Sparx EA).
  4. Create a img-Tag in HTML, that has a src-Attribute pointing to that webpage with a get parameter img={your image id}
  5. Use Response.BinaryWrite in the way shown below

Solution

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class GetImage : System.Web.UI.Page
{
    // Mit folgender URL Kann ein Bild nun rausgeladen werden 
    // http://localhost:51241/GetImage.aspx?img=343868582
    // und entsprechend in HTML über den Image-Tag geladen werden:
    // <img src="GetImage.aspx?img=343868582" />
    protected void Page_Load(object sender, EventArgs e)
    {
        string sqlStatement = @"
            SELECT Image 
            FROM t_image 
            WHERE ImageID={ImageID}         
        ";

        sqlStatement = sqlStatement.Replace("{ImageID}", Request.QueryString["img"].Trim());

        OleDbConnection conn = new OleDbConnection(MyConfigurationManager.eapFilePath);

        try
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = sqlStatement;

            OleDbDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                Response.ContentType = "image/jpeg"; // if your image is a jpeg of course
                Response.BinaryWrite((byte[])reader.GetValue(0));
            }
        }
        catch (Exception ex)
        {
            //return e.Message;
        }
        finally
        {
            conn.Close();
        }
    }
}

Sparx Systems Enterprise Architect: SQL to get all Links with their start and end objects and a tagged value

Problem

A SQL statement is needed, that returns a list of all link relations with start and end object name. Additionally a tagged value (here it is called ‚Object identifier‘) should be appended

Approach

Usage of MDB Plus

Solution

Note: If you do not need tagged values, you can customize the following Access / .eap-File Statement in the way that you reduce it by leaving out the last 2 LEFT JOINs (and don’t forget to remove the Columns from the Select clause).

SELECT      objstart.[Object_ID] As EAIDSource,
            objstart.[Name] AS EANameSource,
            objpropstart.[Value] AS DoorsSoruceObjectID,
            tconn.[Connector_ID] AS EAConnectorID,
            tconn.[Direction] AS EADirection,
            tconn.[Connector_Type] AS EAConnectorType,
            tconn.[Stereotype] AS EAStereoType,
            objend.[Object_ID] AS EAIDTarget,
            objend.[Name] AS EANameTarget,
            objpropende.[Value] AS DoorsTargetObjectID
FROM                                                                                          
(((                                                              
[t_connector] tconn LEFT JOIN [t_object] objstart ON tconn.[Start_Object_ID]=objstart.[Object_ID])
                    LEFT JOIN [t_object] objend ON tconn.[End_Object_ID]=objend.[Object_ID])                                                                           
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropstart ON tconn.[Start_Object_ID]=objpropstart.[Object_ID] )
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropende ON tconn.[End_Object_ID]=objpropende.[Object_ID]

Sparx Systems Enterprise Architect: SQL To get (x,y) Coordinates , width and height of Diagram Objects

Problem

A SQL Statement is needed to get the coordinates of Diagram Object like Activities, Classes, Lanes and so on…
In this statement only the stereotypes ‚Project Phase‘, ‚Gate‘, ‚Product Maturity‘, ‚Lane‘ of the diagram ‚Product Creation Process‘ will be selected

Approach

Usage of MDB Plus or another SQL Tool

Solution

This example is for Access / .eap-Files

SELECT  dia.Name As Diagram,
                objstart.stereotype,
                diaobj.RectLeft As x,
                diaobj.RectTop As y,
                diaobj.RectRight-diaobj.RectLeft As Width,
                Abs(diaobj.RectBottom-diaobj.RectTop) As Height,
                objstart.Object_ID,                               
                objstart.Alias,
                objstart.[Name] As ProcessStep
FROM                                                 
((
                [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop, RectRight, RectBottom from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
WHERE dia.Name='Product Creation Process'
AND objstart.stereotype IN ('Project Phase', 'Gate', 'Product Maturity', 'Lane')
ORDER BY 1,2,3,4,5,6,7,8,9                                                                                                           

Sparx Systems Enterprise Architect: SQL to get the start / end coordinates of the link connector routing with customized lines

Problem

A SQL-Statement is needed (here Access / .eap) that gets all Coordinate-, Routing- and Position information of links in a diagram of the Sparx Systems Enterprise Architect.

Approach

Data Mining and the Usage of MDB Plus

Solution

The link routing is diagram dependent. In the following diagram the diagram „PLM Framework“ has been used. Further more the stereotype „BusinessProcess“ is requested (you have to replace these String according to your needs.

SELECT bp.[Object_ID] as [start_object_id],
       bp.[Name] as [start_object_name],
       tconn.[Connector_ID] as [conn_id],
       tconn.[Start_Edge] as [conn_start_edge],
       tconn.[End_Edge] as [conn_end_edge],
       tconn.[PtStartX] as [conn_pt_start_x],
       tconn.[PtStartY] as [conn_pt_start_y],
       tconn.[PtEndX] as [conn_pt_end_x],
       tconn.[PtEndY] as [conn_pt_end_y],
       tconn.[RouteStyle] as [conn_route_style],
       linkpath.[Path] as [conn_path],
       bpdest.[Object_ID] as [end_object_id],
       bpdest.[Name] as [end_object_name],
       bp.[Alias] as [start_key],
       bpdest.[Alias] as [end_key],
       startobjcoord.x as [start_obj_x],
       startobjcoord.y as [start_obj_y],
       endobjcoord.x as [end_obj_x],
       endobjcoord.y as [end_obj_y],
       linkpath.Geometry as [geometry] 
FROM
((((( 
   SELECT Object_ID, [Alias], Name 
   FROM t_object
   WHERE Stereotype='BusinessProcess'
)bp
INNER JOIN 
(  SELECT [Connector_ID],[Start_Object_ID], [End_Object_ID], [Start_Edge], [End_Edge], [PtStartX], [PtStartY], [PtEndX], [PtEndY], [RouteStyle] 
   FROM [t_connector] 
)tconn
ON tconn.[Start_Object_ID]=bp.[Object_ID])
INNER JOIN 
( 
   SELECT Object_ID, [Alias],Name 
   FROM  [t_object] 
)bpdest ON tconn.[End_Object_ID]=bpdest.Object_ID)
INNER JOIN
(
  SELECT ConnectorID, Path, Geometry
  FROM t_diagramlinks WHERE DiagramID IN (SELECT Diagram_ID FROM t_diagram WHERE Name='PLM Framework')
) linkpath ON linkpath.[ConnectorID]=tconn.[Connector_ID])
INNER JOIN
(
  SELECT objstart.Object_ID,
         objstart.Alias,
         diaobj.RectLeft As x,
         diaobj.RectTop As y
  FROM
  ((
      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
  WHERE dia.Name='PLM Framework'
  AND objstart.stereotype='BusinessProcess'
) startobjcoord ON startobjcoord.[Object_ID]=bp.[Object_ID])
INNER JOIN
(
  SELECT objstart.Object_ID,
         objstart.Alias,
         diaobj.RectLeft As x,
         diaobj.RectTop As y
  FROM
  ((
      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
  WHERE dia.Name='PLM Framework'
  AND objstart.stereotype='BusinessProcess'
) endobjcoord ON endobjcoord.[Object_ID]=bpdest.[Object_ID]

Get Microsoft Jet 4.0 Driver working on 64-Bit Windows Server

Problem

The Microsoft JET 4.0 Driver, which can open .MDB / Access Files, is only available for 32-Bit Systems

Ansatz – Approach

Register the DLLs for the operating system

Lösung – Solution

Solution 1 (Especially for IIS)

In IIS 7.x click on the app pool of your application -> advanced settings and look at the „General“ Section. The second Attribute is „Run in 32 Bit mode“. Check this.
Restart IIS.
If it is still not working, go on with Solution 2.

Im IIS Manager markiert man den AppPool der Anwendung und kann rechts auf „Advanced Settings“ (Erweiterte Einstellungen) klicken. Im „General“-Abschnitt (Allgemein) kann man im 2. Attribut sagen, man möchte im 32-Bit-Modus starten. Danach sollte der JET Treiber auch gehen.

Solution 2 (Especially for local applications)

The solution is to manually register those DLLs.

go to Start->Run and type cmd
this starts the Command Prompt
(also available from Start->Programs->Accessories->Command Prompt)

type cd .. and press return
type cd .. and press return again (keep doing this until the prompt shows :\> )

now you need to go to a special folder which might be c:\windows\system32 or it might be c:\winnt\system32 or it might be c:\windows\sysWOW64
try typing each of these eg
cd c:\windows\sysWOW64
(if it says The system cannot find the path specified, try the next one)
cd c:\windows\system32
cd c:\winnt\system32
when one of those doesn’t cause an error, stop, you’ve found the correct folder.

now you need to register the OLE DB 4.0 DLLs by typing these commands and pressing return after each

regsvr32 Msjetoledb40.dll
regsvr32 Msjet40.dll
regsvr32 Mswstr10.dll
regsvr32 Msjter40.dll
regsvr32 Msjint40.dll

Sparx Systems Enterprise Architect + SQL Queries : SQL Abfragen innerhalb von Enterprise Architect absetzen

Problem

Wer schon einmal versucht hat, eine lokale .EAP Datei in Microsoft Access zu öffnen, erlebt sein blaues Wunder, denn im Prinzip ist eine .EAP Datei nichts anderes als eine MS Access Datenbank (also ein .mdb-File), auf welches man SQL Queries absetzen kann. Bei der Migration eines lokalen .EAP Files in einen normalen SQL-Server, muss man zunächst die Datenstruktur anlegen und anschließend den Import starten. Eine großartige Transformation der Daten zwischen den Formaten findet also gar nicht statt. Was passiert aber wenn man kein MS Access / SQL Studio zur Verfügung hat?

Ansatz – Approach

Nutzung des kostenlosen Tools MDB Plus von Alex Nolan (das ist eine Windows Delphi Anwendung, die einfach gestartet werden kann).

Lösung – Solution

Das Tool lässt sich hier downloaden: http://www.alexnolan.net/ .
Anschließend geht man auf „Open“ und stellt die Datei-Extensionsuche von .mdb auf *.* um – danach kann man .eap Dateien öffnen.

C# .NET + Access : Auf .mdb-Datei zugreifen

Aufgabenstellung

Von C# soll auf eine Access-Datenbank zugegrieffen werden.

Lösung

public static string getCustomerCPD(string customerno)
{
   string cpd = "Customer using getCustomerCPD not found";
   OleDbConnection conn = new OleDbConnection(
   "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;"+
    "Jet OLEDB:Database Locking Mode=1; "+
    "Data Source=\"" + AppDomain.CurrentDomain.BaseDirectory +
     "\\OCT.mdb\";Jet OLEDB:Engine Type=5;"+
    "Provider=\"Microsoft.Jet.OLEDB.4.0\";Jet OLEDB:System database=;"+
    "Jet OLEDB:SFP=False;"+
    "persist security info=False;Extended Properties=;"+
    "Jet OLEDB:Encrypt Database=False;"+
    "Jet OLEDB:Create System Database=False;"+
     "Jet OLEDB:Don't Copy Locale on Compact=False;"+
    "Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;"+
     "Jet OLEDB:Global Bulk Transactions=1";);

     try
     {
       conn.Open();
       OleDbCommand comm = new OleDbCommand();
       comm.Connection = conn;

        comm.CommandText = "SELECT cpdnumber FROM [tbl_Customer] "+
                                           "WHERE debitor=@customerno";
        comm.Parameters.AddWithValue("customerno", customerno);

        OleDbDataReader reader = comm.ExecuteReader();

        while (reader.Read())
        {
            cpd = reader.GetValue(0).ToString();
        }
  }
  catch (Exception e)
  {
     return e.Message;
  }
  finally
  {
      conn.Close();
  }
  return cpd;
}