{"id":752,"date":"2012-08-16T13:05:05","date_gmt":"2012-08-16T11:05:05","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=752"},"modified":"2012-09-14T08:59:25","modified_gmt":"2012-09-14T06:59:25","slug":"ibm-doors-dxl-zugriff-auf-ms-sql-server","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=752","title":{"rendered":"IBM Doors DXL: Zugriff auf MS SQL Server"},"content":{"rendered":"

Aufgabenstellung<\/h1>\n

In der Programmiersprache DXL von IBM Doors soll auf Daten eines Microsoft SQL Servers zugegriffen werden. <\/p>\n

Ansatz<\/h1>\n

Die Verwendung der COM-Schnittstelle erlaubt Zugriff auf die ADODB.Connection. Hierbei handelt es sich um eine Microsoft Bibliothek f\u00fcr den Zugriff auf SQL Server. <\/p>\n

L\u00f6sung 1<\/h1>\n

Das folgende Skript erlaubt Zugriff auf den Microsoft SQL Server und f\u00fchrt eine SELECT-Anweisung durch:<\/p>\n

\/\/ Connection details
\nstring dbServer=“SERVER“;
\nstring dbName=“DATABASE“;
\nstring dbUsername=“USER“;
\nstring dbPassword=“PASS“;<\/p>\n

string connectionString = „Data Source='“ dbServer „‚;
\nInitial Catalog='“ dbName „‚;User Id='“ dbUsername „‚;Password='“ dbPassword „‚;“;<\/p>\n

OleAutoArgs oleAutoArgs=create;
\nOleAutoObj adodbConnection, adodbRecordset, objFields, objField;<\/p>\n

string fieldName, result, err;
\nint numFields, index;<\/p>\n

\/\/ Instantiate a new ADODB Connection object
\nadodbConnection = oleCreateAutoObject „ADODB.Connection“;<\/p>\n

if (null adodbConnection)
\n{
\n\tprint „Unable to instantiate database connection\\n“;
\n\thalt;
\n}<\/p>\n

\/\/ Instantiate a new ADODB Recordset object
\nadodbRecordset = oleCreateAutoObject „ADODB.Recordset“;<\/p>\n

if(null adodbRecordset)
\n{
\n\tprint „Unable to create Recordset object\\n“;
\n\thalt;
\n}<\/p>\n

\/\/ Connection details<\/p>\n

\/\/ Set the provider and data source of the connection
\n\/\/ based on information from connectionstrings.com
\nolePut(adodbConnection, „Provider“, „sqloledb“);
\nclear oleAutoArgs;
\nput(oleAutoArgs, connectionString );
\n\/\/ „Password=“ dataPass „;“)
\n\/\/ Open the connection to the database
\nerr=oleMethod(adodbConnection, „Open“, oleAutoArgs);<\/p>\n

if(!null err „“)
\n{
\n\tprint „Error opening database: “ err „\\n“;
\n\thalt;
\n}<\/p>\n

\/\/ SQL Command: Open a cursor to return all columns and rows of ‚tableName‘
\nclear oleAutoArgs<\/p>\n

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

err=oleMethod(adodbRecordset, „Open“, oleAutoArgs);<\/p>\n

if(!null err „“)
\n{
\n\tprint „Error opening table: “ err „\\n“;
\n\thalt;
\n}<\/p>\n

\/\/ From the Recordset object, list each field name (defined in database)
\noleGet(adodbRecordset, „Fields“, objFields);
\noleGet(objFields, „Count“, numFields);<\/p>\n

for(index=0; index0?“\\“,“:““) „\\““ fieldName;
\n}<\/p>\n

print „\\“\\n“;<\/p>\n

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

L\u00f6sung 2<\/h1>\n

Das untere Beispiel zeigt die Kapselung in eine Funktion und den Zugriff auf Einzelwerte, bzw. auf einzelne Zellen eines Recordsets. Dieses Beispiel l\u00e4sst sich ausgezeichnet in andere Bibliotheken auslagern:<\/p>\n

OleAutoArgs args = null;<\/p>\n

void cleanup (OleAutoObj &obj)
\n{
\n\tif (!null obj)
\n\t{
\n \t\toleCloseAutoObject obj;
\n \t\tobj = null;
\n\t}
\n}<\/p>\n

\/\/ some syntax helpers for arguments (from DXL standard library) …
\nvoid checkNull (string s)
\n{
\n\tif (!null s)
\n\t{
\n\t\tprint „Error: “ s „\\n“ dxlHere();
\n\t\thalt;
\n\t}
\n}<\/p>\n

OleAutoArgs createArgs ()
\n{
\n\tif (!null args) delete args;
\n\targs = create();
\n\treturn args
\n}<\/p>\n

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

\tprint „\\“\\n“;<\/p>\n

\t\/\/ From the Recordset object cursor, loop through and print each row
\n\twhile(true)
\n\t{
\n\t\t\/\/ Break when we are still at the end
\n\t\tbool bEOF = false;
\n\t\tcheckNull oleGet(adodbRecordset, „EOF“, bEOF);
\n\t\tif (bEOF) break;<\/p>\n

\t\toleGet(adodbRecordset, „Fields“, objFields);<\/p>\n

\t\t\/\/ Hier werden die Einzelfelder ausgelesen
\n\t\toleGet(objFields, „Item“, createArgs <- 0 , objField);\n\t\tstring feld1 = stringProperty(objField, \"Value\");\n\n\t\toleGet(objFields, \"Item\", createArgs <- 1 , objField);\n\t\tstring feld2 = stringProperty(objField, \"Value\");\n\t\t\n\t\toleGet(objFields, \"Item\", createArgs <- 2 , objField);\n\t\tstring feld3 = stringProperty(objField, \"Value\");\n\n\n\t\tprint feld3 \" \" feld1 \" \" feld2 \"\\n\";\n\t\t\t\t\n\t\tcheckNull oleMethod (adodbRecordset, \"MoveNext\");\n\n\t}\n\t\n\tcleanup adodbConnection; \n cleanup adodbRecordset; \n cleanup objFields; \n cleanup objField;\n}\n\n\n\/\/ Connection details\nstring sql=\"select [alarm_id], [subsystem], [bitnumber] from [alarms] order by 1\";\nstring dbServer=\"SERVER\";\nstring dbName=\"DATENBANK\";\nstring dbUsername=\"USER\";\nstring dbPassword=\"PASS\";\n\nselectStatement(sql,dbServer,dbName,dbUsername,dbPassword);\n[\/javascript]\n<\/p>\n