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

10 Gedanken zu „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)“

  1. Dear Björn Karpenstein,

    I’d like to use Your DXL script, but I’ve a problem with it. I use XAMPP virtual server, and MYSQL. When I try to run the script, I get the following error message:

    Error opening database: OLE problem: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied., OLE error code: -2147467259

    Could You help me?

    Best Regards,
    Bela

  2. Hi!

    I use this library to collect requirements from Doors, but I have some problems. If I run it in a folder that contains a lot of records, I got the following error:
    Error: OLE problem: Not enough storage is available to complete this operation., OLE error code: -2147024882

    Could You help me to solve this problem?

    1. Do one of the following, then retry the operation:
      (1) reduce the number of running programs;
      (2) remove unwanted files from the disk the paging file is on and restart the system;
      (3) check the paging file disk for an I/O error; or
      (4) install additional memory in your system.

  3. Unfortunatelly, I got different error almost every running.
    For example: Unspecified error, OLE error code: -2147467259

    The problem only exists if it runs in big folder (that contains lots of requirements)

    Laszlo

    1. Hi Laszlo,

      maybe this is because the script is not closing the connection? Have you tried to build in s.th. like this (into the library) after each execution?

      Be aware, that the connection is not openend several time, you have to tailor the script in this case. I am looking forward your response and maybe you can share your experience with this.

      I summarize: Do not open the connection multiple times and try s.th. like this:
      checkNull oleMethod (adodbConnection, „Close“ , createArgs <- statement);

  4. Dear Björn,

    I realised that the problem is that the script doesn’t close the connection, each query opens new connection. I think the cleanup() function doesn’t work well.

    My first solution is that I open the connection (adodbConnection) only once at the beginning of the script, and not in the functions.

    What the ’statement‘ is in Your suggested code? Is it same as in „Open“ or „Execute“ case?
    For example, after the
    checkNull oleMethod (adodbConnection, „Execute“ , createArgs <- statement);
    command I use
    checkNull oleMethod (adodbConnection, "Close" , createArgs <- statement);
    command to delete the connection, am I right?

    Laszlo

Schreibe einen Kommentar zu Björn Karpenstein Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.