Archiv der Kategorie: Datenbanken

Unter Datenbanken verstehe ich alle persistenten Speicherungssysteme die über ein relationales, objektorientiertes oder hierarchisches Datenmodell und über eine standardisierte Abfragesprache wie SQL, XQuery etc… verfügen.
Auch Datenbank-APIs wie Hibernate passen in diese Kategorie.

MS SQL Server, Programmierung, SQL

Microsoft SQL Server: How-to realize a SQL Statement that only returns the maximum or the minimum rows of a group (similar to an non-existing aggregate function for the GROUP BY clause)

26. Februar 2020 Björn Karpenstein Schreibe einen Kommentar

Actual situation

A change request (field „pcr“) only can have one unique assigned user that shall be informed about the assignment every night.

The assigned user (field „created_by“) of a change requests (field „pcr“) is changing several times a day. Every time the user (field „created_by“) changes to another user, a table entry with the actual time stamp (field „created_on“) is inserted to a SQL table.

At the end of the day, the system shall report via email the users that have been assigned to a PCR (only one user per change request).

The following table contains entries with people wo shall be informed every evening.

id	pcr	created_on	created_by
1	PCR-1	2020-02-26 09:20:52.977	user1
2	PCR-1	2020-02-26 09:20:54.667	user1
3	PCR-2	2020-02-26 09:20:57.137	user1
4	PCR-2	2020-02-26 09:20:59.003	user1
5	PCR-2	2020-02-26 09:21:07.540	user3
6	PCR-3	2020-02-26 09:21:09.850	user3
7	PCR-4	2020-02-26 09:21:12.850	user3
8	PCR-4	2020-02-26 09:21:15.100	user3
9	PCR-5	2020-02-26 09:21:55.323	user3
10	PCR-2	2020-02-26 09:22:06.037	user2
11	PCR-5	2020-02-26 09:22:07.037	user2
12	PCR-7	2020-02-26 09:22:08.760	user2
13	PCR-7	2020-02-26 10:20:07.233	user2
14	PCR-8	2020-02-26 10:20:08.967	user2
15	PCR-8	2020-02-26 10:20:13.393	user1
16	PCR-9	2020-02-26 10:20:15.020	user1
17	PCR-7	2020-02-26 10:20:19.497	user3
18	PCR-9	2020-02-26 10:20:20.163	user3

A nightly executed batch job (at 23:59h every evening) shall provide the possibility to inform user1, user2 or user3 about their assigned PCR’s. Only the latest entry per PCR, that can be recognized by the timestamp (field „created_on“) represent the user assigned user at the end of the day

Problem

The SQL Statement shall output the latest entry per assigned user of each PCR. With a standard aggregate function this is not possible because the whole row entry has to be retrieved.

Approach

The Microsoft SQL Server has a proprietary function RANK(), that prints out the ranking of the PCR entry in connection with PARTITION BY and ORDER BY. The ORDER BY has to be used DESC, so that the latest entry always is on rank 1.

Because this methods only can be used within the SELECT statements fields and not withing WHERE, we need a second parent query to select everything with rank 1.

Solution

SELECT *
FROM (
	SELECT	id, 
			pcr,
			created_on, 
			created_by,
			RANK () OVER ( 
				PARTITION BY pcr
				ORDER BY created_on DESC
			) as biggestHasRangOne
	FROM nightly_email
)tab 
WHERE biggestHasRangOne=1 
AND CONVERT(date, GETDATE())=CONVERT (date, created_on)

Only the latest PCR assignment has been selected for that day.

To get the smallest one (minimum) in for that day you have to use

ORDER BY created_on ASC

In the RANK() – Partition by -Syntax.

Atlassian JIRA, MS SQL Server

Atlassian JIRA mit MS SQL : Select the text, date or string value from a custom field of an issue

12. November 2019 Björn Karpenstein Schreibe einen Kommentar

Problem

A string, date or a text-value of an custom field shall be retrieved from an issue

Solution

SELECT	pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max)) as issue, 
		cfv.STRINGVALUE, 
		cfv.TEXTVALUE,
		cfv.DATEVALUE
FROM jiraissue ji 
INNER JOIN customfieldvalue cfv ON ji.id=cfv.issue 
INNER JOIN project_key pk ON ji.PROJECT=pk.PROJECT_ID 
WHERE pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max))='AT-15' 
AND cfv.CUSTOMFIELD=(SELECT TOP 1 id FROM customfield WHERE cfname='Requested document')

Atlassian JIRA, MS SQL Server

Atlassian JIRA MS SQL Server: Get Affects version or fixed version from the Database by query

11. November 2019 Björn Karpenstein Schreibe einen Kommentar

Problem

To fetch the values „Affects version“ or „Fixed Version“ from the JIRA Database via SQL, the use of different relations/tables is necessary

Solution – Lösung

You can use the ASSOCIATION_TYPE=’IssueVersion‘ for Affects version and ASSOCIATION_TYPE=’IssueFixVersion‘ to get the Fixed version

The next example shows how to retrieve Affects Version for Issue-ID AT-15:

SELECT projectversion.id, vname, project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max)) as pkey, ASSOCIATION_TYPE
FROM   projectversion, nodeassociation, jiraissue, project_key
WHERE  SINK_NODE_ID = projectversion.id
AND    SOURCE_NODE_ID = jiraissue.id
AND    project_key.PROJECT_ID=jiraissue.PROJECT
AND    ASSOCIATION_TYPE='IssueVersion' 
AND    project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max))='AT-15'

Access, Datenbanken

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

15. September 2018 Björn Karpenstein Schreibe einen Kommentar

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.

Access, Datenbanken, MS SQL Server, Sparx Systems Enterprise Architect, SQL

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

7. Juni 2018 Björn Karpenstein Schreibe einen Kommentar

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

Access, Datenbanken, MS SQL Server, Sparx Systems Enterprise Architect, SQL

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

6. Juni 2018 Björn Karpenstein Schreibe einen Kommentar

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

.NET, Access, C#, Datenbanken, MS SQL Server, Sparx Systems Enterprise Architect, SQL

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

17. Mai 2018 Björn Karpenstein Schreibe einen Kommentar

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

Create a new ASP.NET Webform and name it GetImage.aspx
Go to the Page_Load function in it
Paste the code under solution in the area in customize according your data structure (here it is Sparx EA).
Create a img-Tag in HTML, that has a src-Attribute pointing to that webpage with a get parameter img={your image id}
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();
        }
    }
}

Access, Datenbanken, MS SQL Server, Sparx Systems Enterprise Architect, SQL

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

17. Mai 2018 Björn Karpenstein Schreibe einen Kommentar

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]

Access, Datenbanken, MS SQL Server, Sparx Systems Enterprise Architect, SQL

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

17. Mai 2018 Björn Karpenstein Schreibe einen Kommentar

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

Access, Datenbanken, MS SQL Server, Sparx Systems Enterprise Architect, SQL

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

17. Mai 2018 Björn Karpenstein Schreibe einen Kommentar

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]

Actual situation

Problem

Approach

Solution

Problem

Solution

Problem

Solution – Lösung

Problem

Ansatz – Approach

Lösung – Solution

Problem

Approach

Solution

Problem

Approach – Ansatz

Solution – Lösung

Problem

Approach

Solution

Problem

Approach

Solution

Problem

Approach

Solution

Problem

Approach

Solution

by Björn Karpenstein