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