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]
Veröffentlicht unter Access, Datenbanken, MS SQL Server, Sparx Systems Enterprise Architect, SQL | Hinterlasse einen Kommentar

Sparx Systems Enterprise Architect: Get Composite Diagram by SQL

Problem

The menu point „Set Composite Diagram“ allows to create a clickable link to another diagram from objects. Unfortunetly to get the references in SQL (with start and end GUID) is a little bit tricky…

Approach

Open the .eap (MDB Plus) file or der SQL Server structure (MS SQL Server Studio Express) in a tool that allows you to test SQL Statements

Solution

Composite Diagrams of Activities

i.e. for the .eap-File (Access):
To get the activities Composite diagrams, you need to join the t_object.PData1 Columne with the t_diagram.Diagram_ID Column. This can be done in the following way:

SELECT compobjects.ea_guid As src_guid,
       compobjects.name As src_name,
       b.ea_guid As dest_guid,
       b.name As dest_name 
FROM 
(
      SELECT ea_guid, 
             Name, 
             Object_Type, 
             Stereotype, 
             PData1 
      FROM t_object 
      WHERE NType=8 AND PData1 IS NOT null AND isnumeric(PData1)) compobjects
INNER JOIN t_diagram b ON b.Diagram_ID=CInt(compobjects.PData1)

Composite Diagrams of Classes

SELECT Client As src_guid, 
       obj.name As src_name, 
       Supplier As dest_guid, 
       obj2.name As dest_name   
FROM ( t_xref xr
LEFT JOIN t_object obj ON xr.Client=obj.ea_guid)
LEFT JOIN t_diagram obj2 ON xr.Supplier=obj2.ea_guid 
WHERE Supplier<>'<none>'

All Composites by source guid

SELECT src_guid,
       src_name,
       dest_guid,
       dest_name
FROM       
(
	SELECT compobjects.ea_guid As src_guid,
		   compobjects.name As src_name,
		   b.ea_guid As dest_guid,
		   b.name As dest_name 
	FROM
	(
		  SELECT ea_guid, 
				 Name, 
				 Object_Type, 
				 Stereotype, 
				 PData1 
		  FROM t_object 
		  WHERE NType=8 AND PData1 IS NOT null AND isnumeric(PData1)) compobjects
	INNER JOIN t_diagram b ON b.Diagram_ID=CInt(compobjects.PData1)
	UNION ALL 
	SELECT Client As src_guid, 
		   obj.name As src_name, 
		   Supplier As dest_guid, 
		   obj2.name As dest_name   
	FROM ( t_xref xr
	LEFT JOIN t_object obj ON xr.Client=obj.ea_guid)
	LEFT JOIN t_diagram obj2 ON xr.Supplier=obj2.ea_guid 
	WHERE Supplier<>'<none>'
)
WHERE src_guid='Put your GUID here'
Veröffentlicht unter Sparx Systems Enterprise Architect, SQL | Hinterlasse einen Kommentar

C#.NET + MS SQL Server : Nach INSERT direkt die Auto-Increment ID erhalten ohne zweite Abfrage

Problem

Um die Auto-Increment ID zu erhalten werden desöfteren 2 Statements abgesetzt, obwohl das INSERT-Statement direkt die Auto-Increment ID zurückgeben kann

Ansatz – Approach

Anstelle von comm.ExecuteNonQuery() sollte man lieber comm.ExecuteScalar() mit SELECT SCOPE_IDENTITY() kombinieren.

Lösung – Solution

public string insertMainTherapyData(string serial_no
                    ,string therapy_start
                    ,string machine_type
                    ,string file_source
                    ,string dialog_version
                    ,string tlc_version
                    ,string versions
                    ,DateTime uploaded_on
                    ,string uploaded_by
                    ,bool processed
                    ,string user_original_file
                    ,string user_country
                    ,string user_comment
                    ,string user_upload_reason
                    ,string user_location)
{
    string sqlStatement=@"
        INSERT INTO [dbo].[therapy]
                    ([serial_no]
                    ,[therapy_start]
                    ,[machine_type]
                    ,[file_source]
                    ,[dialog_version]
                    ,[tlc_version]
                    ,[versions]
                    ,[uploaded_on]
                    ,[uploaded_by]
                    ,[processed]
                    ,[user_original_file]
                    ,[user_country]
                    ,[user_comment]
                    ,[user_upload_reason]
                    ,[user_location])
        VALUES
                    (@serial_no
                    ,@therapy_start
                    ,@machine_type
                    ,@file_source
                    ,@dialog_version
                    ,@tlc_version
                    ,@versions
                    ,@uploaded_on
                    ,@uploaded_by
                    ,@processed
                    ,@user_original_file
                    ,@user_country
                    ,@user_comment
                    ,@user_upload_reason
                    ,@user_location);
            SELECT SCOPE_IDENTITY()
            ";
    int myID = -1;
    SqlConnection conn = new SqlConnection(MyConfigurationManager.prdSqlServerString);
    try
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = sqlStatement;
        comm.Parameters.AddWithValue("serial_no", serial_no);
        comm.Parameters.AddWithValue("therapy_start", therapy_start);
        comm.Parameters.AddWithValue("machine_type", machine_type);
        comm.Parameters.AddWithValue("file_source", file_source);
        comm.Parameters.AddWithValue("dialog_version", dialog_version);
        comm.Parameters.AddWithValue("tlc_version", tlc_version);
        comm.Parameters.AddWithValue("versions", versions);
        comm.Parameters.AddWithValue("uploaded_on", uploaded_on);
        comm.Parameters.AddWithValue("uploaded_by", uploaded_by);
        comm.Parameters.AddWithValue("processed", processed);
        comm.Parameters.AddWithValue("user_original_file", user_original_file);
        comm.Parameters.AddWithValue("user_country", user_country);
        comm.Parameters.AddWithValue("user_comment", user_comment);
        comm.Parameters.AddWithValue("user_upload_reason", user_upload_reason);
        comm.Parameters.AddWithValue("user_location", user_location);
        myID = Convert.ToInt32(comm.ExecuteScalar());
    }
    catch (Exception ex)
    {
        return "ERROR: "+ex.Message;
    }
    finally
    {
        conn.Close();
    }

    return myID.ToString();
}
Veröffentlicht unter .NET, C#, MS SQL Server, Programmierung | Hinterlasse einen Kommentar

Javascript: Read URL GET Parameter from the browsers address bar that have been passed / commited

Problem

A javascript should read the browsers address bar to get GET Üarameters that have been appended to the adress bar.

Approach – Ansatz

Usage of decodeURIComponent

Solution

var getUrlParameter = function getUrlParameter(sParam) {
	var sPageURL = decodeURIComponent(window.location.search.substring(1)),
		sURLVariables = sPageURL.split('&'),
		sParameterName,
		i;

	for (i = 0; i < sURLVariables.length; i++) {
		sParameterName = sURLVariables[i].split('=');

		if (sParameterName[0] === sParam) {
			return sParameterName[1] === undefined ? true : sParameterName[1];
		}
	}
};

var myMap = getUrlParameter("map");
if (myMap) {
	diagramType = myMap;
}

You can call now the script like this:
http://webseite.html?map=Test

Veröffentlicht unter Javascript | Hinterlasse einen Kommentar

Intersector for Instagram: Which persons do I follow that aren’t following me back?

Hi!

I wanted to write a simple DIFF Tool, that shows the intersection of my followers and people who i am following. But after reading Instagrams review conditions it seems like to me, they are only giving support for companies. The client that is calling the REST API needs to request a permission „follower_list“ but they do not accept applications any longer for review.

This is the only output i can get from the API:

Veröffentlicht unter Allgemein | Hinterlasse einen Kommentar

WordPress Plugin: Supported Languages of SyntaxHighlighter Evolved

Problem

It is unknown which [LANGUAGE][/LANGUAGE]-Tags are supported by the wordpress syntax highnlighting plugin.

Lösung – Solution

  • actionscript3
  • bash
  • clojure
  • coldfusion
  • cpp
  • csharp
  • css
  • delphi
  • diff
  • erlang
  • fsharp
  • go
  • groovy
  • html
  • java
  • javafx
  • javascript
  • latex (you can also render LaTeX)
  • matlab (keywords only)
  • objc
  • perl
  • php
  • powershell
  • python
  • r
  • ruby
  • scala
  • sql
  • text
  • vb
  • xml
\sum_{n=0}^{3}n=6
Veröffentlicht unter Wordpress | Hinterlasse einen Kommentar

MS SQL Server: Last Index Of / lastIndexOf / Get last Index Of

Problem

There is no SQL Function to get the last index of a string.

Ansatz – Approach

The first two lines only declare a variable myString with content SER-SOP-12 to Test the combined SQL Functions.
The SELECT-Clause returns the lastIndex of (can be used in standard MS SQL Statements)

Lösung – Solution

Just copy it to the MS SQL Server Management Studio in a query window to test and press F5:

DECLARE @myString AS varchar(255)
SET @myString ='SER-SOP-12'
SELECT LEN(@myString )-CHARINDEX('-', REVERSE(@myString )) 
Veröffentlicht unter MS SQL Server | Hinterlasse einen Kommentar

CSS: Vertical alignment of HTML elements in horizontal DIV

Problem

Mehrere Element in einem DIV-Container sollen horizontal zentriert werden.

Ansatz – Approach

Usage of FLEX Container (HTML Standard)

Lösung – Solution

DIV Declaration:

<div class="oneLineLayout"> ... </div>

CSS Class:

.oneLineLayout {
  display: flex;
  flex-direction: row;
  flex-wrap: wrap;
  justify-content: space-between;
  align-items: center;
}
Veröffentlicht unter HTML/CSS | Hinterlasse einen Kommentar

20 Buzzwords / Catchwords / Schlagwörter, die Sie 2018 möglichst oft in Besprechungen erwähnen sollten (Gartner und Forrester)

Die folgenden Wörter sollten möglichst oft in Besprechungen erwähnt werden, da sie die Kompetenz des Erwähners attestieren:

  1. Digitalisierung / Digitale Tranformation
  2. Internet of things (IoT) / Digital Twins
  3. Artificial Intelligence / Intelligent things
  4. Machine Learning / Deep learning
  5. Industrie 4.0
  6. Block Chain
  7. Cloud to the edge / Edge Computing
  8. Bit Coin
  9. Device Mesh
  10. Big Data / Data Warehouse / Data Mining / Information Retrieval
  11. Micro Services
  12. Zero Trust / Adaptive Sicherheitssysteme / Cybersecurity
  13. Customer Experience ( Aufbau einer emotionalen Bindung zwischen Anwender und Produkt oder Anbieter)
  14. Infrastructure as Service / Infrastructure as code (Cloud- und Softwaredefinierte Infrastruktur – https://en.wikipedia.org/wiki/Infrastructure_as_Code)
  15. BYOD – Bring your own device
  16. Green-Field-Ansatz (wir schmeissen alles weg und machens neu – soll die Strategie bei SAP S/4 HANA sein)
  17. Brute-Force-Methode (zur Problemlösung: alle möglichen Lösungen durchprobieren)
  18. Wow-Effekt (Begeisterungsauslöser)
  19. proaktiv (aus eigener Initiative getrieben)
  20. Bimodale IT (Schatten-IT vs. Kern-IT)

Nachfolgend ein Beispiel für die analoge Transformation des Snowman Lifecycles in Zeiten der voranschreitenden Digitalisierung, welche in einem Green-Field-Ansatz resultieren wird.

Veröffentlicht unter Allgemein | Hinterlasse einen Kommentar

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

Veröffentlicht unter Access | Hinterlasse einen Kommentar