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!!!

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]