Archiv der Kategorie: MS SQL Server

IBM Doors DXL: How-to access Microsoft SQL Server & Microsoft Access from DXL (eine Bibliothek für SQL Statements / a library to perform sql statements)

Aufgabenstellung — Problem

Es wird eine einfache Bibliothek benötigt um SELECT Statements auf eine Microsoft SQL Server Datenbank abzusetzen und diese in einem DXL Programm zu verwenden

A library is needed for easily creating SQL Statements with result sets. This library should be embeddable in a DXL Skript.

Ansatz — Approach

Die Bibliothek wird von Capri-Soft (Björn Karpenstein) ständig weiterentwickelt und an dieser Stelle mit regelmäßigen Updates zur Verfügung gestellt.

The library will be provided on this site with frequently updates and can be downloaded at this site.

Lösung — Solution

Implemented Functions

Skip selectStatement(string sql, string dbServer, string dbName, string dbUsername, string dbPassword)
Skip selectStatementOnMDB(string sql, string mdbFile) [since Version 0.3]
Array selectStatement(string sql, string dbServer, string dbName, string dbUsername, string dbPassword)
Array selectStatementOnMDB(string sql, string mdbFile)  [since Version 0.3]
int executeQuery(string sql, string dbServer, string dbName, string dbUsername, string dbPassword)
int executeQueryOnMDB(string sql, string mdbFile)  [since Version 0.3]
OleAutoObj openDatabase(string dbServer, string dbName, string dbUsername, string dbPassword)
OleAutoObj openDatabaseOnMDB(string mdbFile)  [since Version 0.3]

Download

First download the DXL-Library here…

sqllibrary.dxl — Version 0.3 [match filename the includes of the examples!!!]
sqlserverlibrary.dxl — Version 0.2
sqlserverlibrary.dxl — Version 0.1

Create your DXL Skript in Doors

Recommendation: When you are writing your SQL Statements, use the CAST-Method every time you define a Field in the SELECT-Clause.

Eample for Version 0.3 with MS Access Support

In this example (only works since version 0.3) you can access .mdb Files like in the following example:

#include "C:\\dev\\dxl\\client\\sqllibrary.dxl";


Skip result = selectStatementOnMDB("SELECT [Connector_ID], [Connector_Type], [Btm_Mid_Label], [Stereotype]  FROM q_connector", "\\\\bbmag295\\ProcessModellingPrototypeAdvanced\\mdb\\modell.eap");
				
// Iteration through the SkipList
for myIterator in result do 
{
   string keyValue = (string key(result));
   DxlObject currentObject = null;

   if(find(result, keyValue, currentObject))
   {
       // Just put the column names here.. it will work
       print (string currentObject->"Connector_ID") "\t";
       print (string currentObject->"Connector_Type") "\t";
       print (string currentObject->"Btm_Mid_Label") "\t";
       print (string currentObject->"Stereotype") "\n";
   }
}

delete result;

Example:

string customFieldSQL= "" //-
" SELECT  CAST(pk.PROJECT_KEY+'-'+CAST(ji.issuenum as VARCHAR(7)) as varchar(10)) as pkey,  " //-
"	     CAST(convert(char, cg.created, 120) as varchar(30)) as on_date, " //-
"		 CAST(ci.field as varchar(50)), " //-
"		 CAST(cg.author as varchar(10)) as changed_by, " //-
"		 CAST(ISNULL(ci.oldstring,'') as varchar(4096)) as changed_from,  " //-
"		 CAST(ISNULL(ci.newstring,'') as varchar(4096)) as changed_to " //-
" FROM jiraissue ji, changegroup cg, changeitem ci, project_key pk " //-
" WHERE ci.groupid = cg.id " //-
" AND cg.issueid = ji.id  " //-
" AND pk.PROJECT_ID=ji.PROJECT " //-
" AND ci.field = '" customField "' " 

Examples for Version 0.2

#include "sqlserverlibrary.dxl"; // renamed to sqllibrary since version 0.3

// Here you can define your connection setting
string dbServer="IPorSERVERNAME";
string dbName="Databasename";
string dbUsername="databaseuser";
string dbPassword="databasepasswort";

// Reduce methods only to get SQL 
Skip selectStatement(string sql) 
{	return selectStatement(sql,dbServer,dbName,dbUsername,dbPassword); }
Array selectStatement(string sql) 
{	return selectStatement(sql,dbServer,dbName,dbUsername,dbPassword); }
int executeQuery(string sql) 
{ return executeQuery(sql,dbServer,dbName,dbUsername,dbPassword); }

// A manipulation like INSERT/UPDATE/DELETE
executeQuery("INSERT INTO table(col1) VALUES('TEST')");

// SKIPLIST: SELECT statement will return a SKIP-List with 
//           dynamic attributes (KEY is first column) 
Skip result = //-
selectStatement("SELECT [id], [col1], [col2], [col3], " //-
			    "[col4], [col5], [col6] " //-
				"FROM table " //-
				"WHERE col1<>'TEST' ORDER BY id ASC");
				
// Iteration through the SkipList
for myIterator in result do 
{
   string keyValue = (string key(result));
   DxlObject currentObject = null;

   if(find(result, keyValue, currentObject))
   {
       // Just put the column names here.. it will work
       print (string currentObject->"id") "\t";
       print (string currentObject->"col1") "\t";
       print (string currentObject->"col2") "\t";
       print (string currentObject->"col3") "\t";
       print (string currentObject->"col4") "\t";
       print (string currentObject->"col5") "\t";
       print (string currentObject->"col6") "\n";
   }
}

delete result;

// Array: SELECT statement will return an  Array 
Array result2 = selectStatement("SELECT [id], [col1], [col2], " //-
				"[col3], [col4], [col5], [col6] " //-
				"FROM table " //-
				"WHERE col1<>'TEST' ORDER BY id ASC");

int i;
for (i=0; i<resultCount; i++)
{
   DxlObject currentObject2 = (DxlObject get(result2,i,1));
   
   if(!null(currentObject2))
   {
       // Just put the column names here.. it will work
       print (string currentObject2->"id") "\t";
       print (string currentObject2->"col1") "\t";
       print (string currentObject2->"col2") "\t";
       print (string currentObject2->"col3") "\t";
       print (string currentObject2->"col4") "\t";
       print (string currentObject2->"col5") "\t";
       print (string currentObject2->"col6") "\n";
   }
}

delete result2;

Example for Version 0.1

#include "sqlserverlibrary.dxl"; // renamed to sqllibrary since version 0.3

// Here you can define your connection setting
Skip selectStatement(string sql)
{
	// Connection details for test
	string dbServer="DATABASE_SERVER_OR_IP";
	string dbName="DATABASE_NAME";
	string dbUsername="DATABASE_USER";
	string dbPassword="DATABASE_PASSWORD";

	return selectStatement(sql,dbServer,dbName,dbUsername,dbPassword);
}

// selectStatement will return a SKIP-List with dynamic attributes
Skip result = //-
selectStatement( //-
"SELECT col1,col2,col3,col4,col5,col6 " //-
			"FROM table " //-
			"WHERE col1 " //-
			" NOT IN ( " //-
			"	SELECT col1 " //-
			"	FROM table " //-
			" 	WHERE LTRIM(RTRIM(col1))<>'---' " //-
			"   GROUP BY alarm_id " //-
			"	HAVING COUNT(*)>1 " //-
			"	) ")
				
// Iteration durch SkipList
for myIterator in result do 
{
   string keyValue = (string key(result));
   DxlObject currentObject = null;

   if(find(result, keyValue, currentObject))
   {
       // Just put the column names here.. it will work
       print (string currentObject->"col1") " ";
       print (string currentObject->"col2") " ";
       print (string currentObject->"col3") " ";
       print (string currentObject->"col4") " ";
       print (string currentObject->"col5") " ";
       print (string currentObject->"col6") "\n";
   }
}

delete result;

Comment

Don’t forget to leave some improvement comments here!!!

IBM Doors DXL: Zugriff auf MS SQL Server

Aufgabenstellung

In der Programmiersprache DXL von IBM Doors soll auf Daten eines Microsoft SQL Servers zugegriffen werden.

Ansatz

Die Verwendung der COM-Schnittstelle erlaubt Zugriff auf die ADODB.Connection. Hierbei handelt es sich um eine Microsoft Bibliothek für den Zugriff auf SQL Server.

Lösung 1

Das folgende Skript erlaubt Zugriff auf den Microsoft SQL Server und führt eine SELECT-Anweisung durch:

// Connection details
string dbServer=“SERVER“;
string dbName=“DATABASE“;
string dbUsername=“USER“;
string dbPassword=“PASS“;

string connectionString = „Data Source='“ dbServer „‚;
Initial Catalog='“ dbName „‚;User Id='“ dbUsername „‚;Password='“ dbPassword „‚;“;

OleAutoArgs oleAutoArgs=create;
OleAutoObj adodbConnection, adodbRecordset, objFields, objField;

string fieldName, result, err;
int numFields, index;

// Instantiate a new ADODB Connection object
adodbConnection = oleCreateAutoObject „ADODB.Connection“;

if (null adodbConnection)
{
print „Unable to instantiate database connection\n“;
halt;
}

// Instantiate a new ADODB Recordset object
adodbRecordset = oleCreateAutoObject „ADODB.Recordset“;

if(null adodbRecordset)
{
print „Unable to create Recordset object\n“;
halt;
}

// Connection details

// Set the provider and data source of the connection
// based on information from connectionstrings.com
olePut(adodbConnection, „Provider“, „sqloledb“);
clear oleAutoArgs;
put(oleAutoArgs, connectionString );
// „Password=“ dataPass „;“)
// Open the connection to the database
err=oleMethod(adodbConnection, „Open“, oleAutoArgs);

if(!null err „“)
{
print „Error opening database: “ err „\n“;
halt;
}

// SQL Command: Open a cursor to return all columns and rows of ‚tableName‘
clear oleAutoArgs

put(oleAutoArgs, „select * from [alarms] order by 1“) // SQL Command
put(oleAutoArgs, adodbConnection) // ACTIVE CONNECTION
put(oleAutoArgs, 1) // CURSOR TYPE – ‚adOpenKeyset‘
put(oleAutoArgs, 1) // LOCK TYPE – ‚adLockReadOnly‘
put(oleAutoArgs, 1) // OPTIONS – ‚adCmdText‘

err=oleMethod(adodbRecordset, „Open“, oleAutoArgs);

if(!null err „“)
{
print „Error opening table: “ err „\n“;
halt;
}

// From the Recordset object, list each field name (defined in database)
oleGet(adodbRecordset, „Fields“, objFields);
oleGet(objFields, „Count“, numFields);

for(index=0; index0?“\“,“:““) „\““ fieldName;
}

print „\“\n“;

// From the Recordset object cursor, loop through and print each row
while(true)
{
result=““;
clear oleAutoArgs;
put(oleAutoArgs, 2); // StringFormat – ‚adClipString‘
put(oleAutoArgs, 1); // NumRows
put(oleAutoArgs, „\“,\““); // ColumnDelimiter
put(oleAutoArgs, „\““); // RowDelimiter
put(oleAutoArgs, „“); // NullExpr
oleMethod(adodbRecordset, „GetString“, oleAutoArgs, result);
if(length(result)<=0) break else print "\"" result "\n"; } [/javascript]

Lösung 2

Das untere Beispiel zeigt die Kapselung in eine Funktion und den Zugriff auf Einzelwerte, bzw. auf einzelne Zellen eines Recordsets. Dieses Beispiel lässt sich ausgezeichnet in andere Bibliotheken auslagern:

OleAutoArgs args = null;

void cleanup (OleAutoObj &obj)
{
if (!null obj)
{
oleCloseAutoObject obj;
obj = null;
}
}

// some syntax helpers for arguments (from DXL standard library) …
void checkNull (string s)
{
if (!null s)
{
print „Error: “ s „\n“ dxlHere();
halt;
}
}

OleAutoArgs createArgs ()
{
if (!null args) delete args;
args = create();
return args
}

OleAutoArgs ::<-(OleAutoArgs x, int a) { put(x, a); return x } OleAutoArgs ::<-(OleAutoArgs x, string a) { put(x, a); return x } OleAutoArgs ::<-(OleAutoArgs x, bool a) { put(x, a); return x } // Hier bekommt man eine Spalte des aktuellen Records als String string stringProperty (OleAutoObj obj, string s) { string result = null; checkNull oleGet (obj, s, result); return result } void selectStatement(string sql, string dbServer, string dbName, string dbUsername, string dbPassword) { string connectionString = "Data Source='" dbServer "';Initial Catalog='" dbName "';User Id='" dbUsername "';Password='" dbPassword "';"; OleAutoArgs oleAutoArgs=create; OleAutoObj adodbConnection, adodbRecordset, objFields, objField; string fieldName, result, err; int numFields, index; // Instantiate a new ADODB Connection object adodbConnection = oleCreateAutoObject "ADODB.Connection"; if (null adodbConnection) { print "Unable to instantiate database connection\n"; halt; } // Instantiate a new ADODB Recordset object adodbRecordset = oleCreateAutoObject "ADODB.Recordset"; if(null adodbRecordset) { print "Unable to create Recordset object\n"; halt; } // Connection details // Set the provider and data source of the connection // based on information from connectionstrings.com olePut(adodbConnection, "Provider", "sqloledb"); clear oleAutoArgs; put(oleAutoArgs, connectionString ); // "Password=" dataPass ";") // Open the connection to the database err=oleMethod(adodbConnection, "Open", oleAutoArgs); if(!null err "") { print "Error opening database: " err "\n"; halt; } // SQL Command: Open a cursor to return all columns and rows of 'tableName' clear oleAutoArgs put(oleAutoArgs, sql) // SQL Command put(oleAutoArgs, adodbConnection) // ACTIVE CONNECTION put(oleAutoArgs, 1) // CURSOR TYPE - 'adOpenKeyset' put(oleAutoArgs, 1) // LOCK TYPE - 'adLockReadOnly' put(oleAutoArgs, 1) // OPTIONS - 'adCmdText' err=oleMethod(adodbRecordset, "Open", oleAutoArgs); if(!null err "") { print "Error opening table: " err "\n"; halt; } // From the Recordset object, list each field name (defined in database) oleGet(adodbRecordset, "Fields", objFields); oleGet(objFields, "Count", numFields); for(index=0; index0?“\“,“:““) „\““ fieldName;
}

print „\“\n“;

// From the Recordset object cursor, loop through and print each row
while(true)
{
// Break when we are still at the end
bool bEOF = false;
checkNull oleGet(adodbRecordset, „EOF“, bEOF);
if (bEOF) break;

oleGet(adodbRecordset, „Fields“, objFields);

// Hier werden die Einzelfelder ausgelesen
oleGet(objFields, „Item“, createArgs <- 0 , objField); string feld1 = stringProperty(objField, "Value"); oleGet(objFields, "Item", createArgs <- 1 , objField); string feld2 = stringProperty(objField, "Value"); oleGet(objFields, "Item", createArgs <- 2 , objField); string feld3 = stringProperty(objField, "Value"); print feld3 " " feld1 " " feld2 "\n"; checkNull oleMethod (adodbRecordset, "MoveNext"); } cleanup adodbConnection; cleanup adodbRecordset; cleanup objFields; cleanup objField; } // Connection details string sql="select [alarm_id], [subsystem], [bitnumber] from [alarms] order by 1"; string dbServer="SERVER"; string dbName="DATENBANK"; string dbUsername="USER"; string dbPassword="PASS"; selectStatement(sql,dbServer,dbName,dbUsername,dbPassword); [/javascript]

MS SQL Server und C#.NET: Textdatei per SQL Bulk Insert

Aufgabenstellung

Es wird eine perfomante (ohne Cursor-Iterationen) Möglichkeit gesucht um Textdateien per C#.NET in den Microsoft SQL Server zu importieren.

Ansatz

C# bietet eine Klassenbibliothek mit den Klassen SqlBulkCopy und SqlBulkCopyOptions, die bereits im SAP Stammdaten Importer Tool vorgestellt wurde, und dessen Quellcode im unteren Abschnitt des Artikel als Visual Studio 2005 Projekt zum download angeboten wird. Dieses Tool ermöglicht das einlesen von SE16-Downloaddateien mit ein paar Millionen Datensätzen in weniger als 2 Sekunden. Bei der herkömmlichen Methode, einer Cursor-Iteration, wird je INSERT-Statement ein Datenbankaufruf/Netzwerkzugriff durchgeführt. Dies ist nur nicht mehr notwendig.

Lösung

DataTable füllen


...
DataTable dataTable = new DataTable();
...
// Spaltenüberschriften zum Table hinzufügen (in einer Schleife befüllbar)
for (/*Schleife um die Spaltennamen einzulesen*/)
{
   // Die Anzahl der Columns muss = objects array sein
   dataTable.Columns.Add("SPALTENNAME");
}
...
// Definition des Zeilenarrays
ArrayList objects = new ArrayList();
...
while(/*Schleife um zeilenweise über Datei zu iterieren*/)
{
  ...
  for (/*Schleife um über Zellen der Zeile zu iterieren*/)
  {
     ...
     objects.Add(aktuellesZellenElementString);
     ...
  }
   ...
   // Hinzufügen des Datenelements, das die Zeilendaten hat
   dataTable.Rows.Add(objects.ToArray());
}
...


DataTable in den MS SQL Server importieren


private void WriteToDatabase()
{
    // get your connection string
    string connString = msSqlServerString;
    // connect to SQL
    using (SqlConnection connection =  new SqlConnection(connString))
    {
        // make sure to enable triggers
        // more on triggers in next post

        SqlBulkCopy bulkCopy = new SqlBulkCopy
        (
            connection,
            SqlBulkCopyOptions.TableLock |
            SqlBulkCopyOptions.FireTriggers |
            SqlBulkCopyOptions.UseInternalTransaction,
            null
         );

        // set the destination table name
        bulkCopy.DestinationTableName = txtImportTable.Text;
        connection.Open();

        // write the data in the "dataTable"
        try
        {
            bulkCopy.WriteToServer(dataTable);
        }
        catch (Exception e)
        {
            MessageBox.Show("Es ist ein Fehler aufgetreten. "+
            "Stellen Sie sicher dass der Timeout im SQL Server "+
            "auf unendlich steht und AutoClose für die Verbindung "+
            "nicht aktiviert wurde. " + e.Message, "Fehler!");
        }
        connection.Close();
    }
    // reset
    this.dataTable.Clear();
}

Ein komplettes Beispiel lässt sich hier downloaden:
Quellcode SAP Stammdaten Importer (VS 2005 Projekt)

SQL Server: Anzahl Nachkommastellen ermitteln

Aufgabe

Es sollen die Anzahl der Nachkommastelle in einem SQL Query abgefragt werden

Lösung

SELECT * FROM completeyear2011ecoflac 
WHERE 
right(cast(completeactualyear as varchar(32)), 
  len(cast(completeactualyear as varchar(32))) 
 - charindex('.',cast(completeactualyear as varchar(32))))>0

Beurteilung

Wenn die SQL-Umgebung keine Funktion zur Verfügung stellt, kann man die obige SQL zwar nutzen, sollte den hinteren Teil allerdings in eine Funktion auslagern. Das Auslagern in eine Funktion dient der Übersichtlichkeit und verletzt das DRY-Prinzip nicht (Don’t repeat yourself!)

MS SQL Server: Split/Explode für SQL

Aufgabenstellung

Eine Spalte einer Tabelle beinhaltet eine Auflistung, die durch ein Zeichen getrennt ist. Im Beispiel die Spalte Gruppe.

Nr|Gruppe
-------------------------------
1 | Buerste,Besen,Feger
2 | Eier,Salz,Butter
3 | Bier,Schnaps,Wein 

Es soll nun mit einer Art Split/Explode wie in PHP die Spalte aufgesplittet werden und einzelne Werte ausgegeben werden

Ansatz

Der folgende Code erstellt eine Funktion, die für eine Datenbank gilt. Kopiert man diesen Code in das SQL Server Management Studio und führt ihn mit F5 aus, erhält die ausgewählte Datenbank die Funktion Split.

Lösung

CREATE FUNCTION [dbo].[split](@String varchar(8000), @Delimiter varchar(12), 
                                                 @returnItem int)
RETURNS varchar(8000)
AS
BEGIN
	DECLARE @id int
	DECLARE @idx int
	DECLARE @slice varchar(8000)
	DECLARE @returnslice varchar(8000)

	SET @id = 0
	SET @idx = 1
	SET @returnslice = null
	
	WHILE @idx!= 0
	BEGIN
		SET @id = @id + '1'
		SET @idx = charindex(@Delimiter,@String)

		IF @idx!=0
			SET @slice = left(@String,@idx - 1)
		ELSE
			SET @slice = @String

		IF @id = @returnItem SET @returnslice = @slice
		SET @String = SUBSTRING(right(@String,len(@String) - @idx),
                                                  len(@Delimiter), len(@String))

		IF len(@String) = 0 BREAK
	END
	RETURN @returnslice
END

Aufruf mit:

SELECT dbo.split('DIES/IST/EIN/TEST','/',2)

MS SQL Server: Ergebnisvariable aus EXEC SQL-Statement

Aufgabenstellung

Aus einem normalen SQL Statement, welches mit EXEC ausgeführt wird, soll eine Ergebnisvariable in dem „normalen“ TSQL-Skript weiterverwendet werden.

Ansatz

Hierzu wird der Rückgabewert des EXEC Statements in eine Variable geschrieben.

Lösung

SET @sql2='SELECT COUNT(*) FROM users WHERE ['+@forsheet+']=1'
		
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_NAME = 'ResultSet') 
DROP TABLE ResultSet;
		
CREATE TABLE ResultSet (mycount int)
 
INSERT INTO ResultSet
EXEC sp_executesql @sql2
		
SELECT @isdrin=mycount FROM ResultSet

MS SQL Server: DROP TABLE wenn die Tabelle nicht existiert

Aufgabenstellung

Eine Tabelle soll gelöscht werden wenn sie existiert.

Problem

Nutzt man in einem TSQL-Skript den DROP Table Befehl (z.B. in einer Stored Procedure) erhält man eine Fehlermeldung, das die zu löschende Tabelle nicht existiert. In einigen Fällen führt dies zum Abbruch des Skriptes.

Ansatz

Über das META-Schema vom MS SQL Server lässt sich erfragen, ob eine Tabelle existiert.

Lösung

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users') 
DROP TABLE users;

MS SQL Server und ACCESS: Führende Nullen in numerischem String abschneiden

Aufgabenstellung

In einer Spalte mit Materialnummer/Artikelnummern werden bei allen numerischen Materialnummern die Nullen entfernt, alphanumerische Materialnummern bleiben unberührt. Soll auch bei den alphanumerischen Materialnummern die führenden Nullen entfernt werden, verweise ich auf den Artikel in der Rubrik „MS SQL Server: Führende Nullen bei alphanumerischem String“.

Ansatz

Über das Case Statement wird gefragt ob der Artikel numerisch ist. Ist er numerisch, wird über die Konvertierung in einen Integer (und anschließendes Zurückkonvertieren in VARCHAR) die führenden Nullen entfernen.

Lösung

Microsoft SQL Server:

UPDATE tabelle SET material=
CASE WHEN isnumeric(material)=1 
        THEN convert(VARCHAR, convert(INTEGER, material))
        ELSE convert(VARCHAR, material)
END 

Microsoft Access:

   UPDATE articletexts SET material=
   IIF(IsNumeric(material), 
   REPLACE(LTRIM(REPLACE(material,'0',' '))
   ,' ','0'), material)