Es wird eine einfache Bibliothek ben\u00f6tigt um SELECT Statements auf eine Microsoft SQL Server Datenbank abzusetzen und diese in einem DXL Programm zu verwenden
\n—
\nA library is needed for easily creating SQL Statements with result sets. This library should be embeddable in a DXL Skript.<\/p>\n
Die Bibliothek wird von Capri-Soft (Bj\u00f6rn Karpenstein) st\u00e4ndig weiterentwickelt und an dieser Stelle mit regelm\u00e4\u00dfigen Updates zur Verf\u00fcgung gestellt.
\n—
\nThe library will be provided on this site with frequently updates and can be downloaded at this site.<\/p>\n
\r\nSkip selectStatement(string sql, string dbServer, string dbName, string dbUsername, string dbPassword)\r\nSkip selectStatementOnMDB(string sql, string mdbFile) [since Version 0.3]\r\nArray selectStatement(string sql, string dbServer, string dbName, string dbUsername, string dbPassword)\r\nArray selectStatementOnMDB(string sql, string mdbFile) [since Version 0.3]\r\nint executeQuery(string sql, string dbServer, string dbName, string dbUsername, string dbPassword)\r\nint executeQueryOnMDB(string sql, string mdbFile) [since Version 0.3]\r\nOleAutoObj openDatabase(string dbServer, string dbName, string dbUsername, string dbPassword)\r\nOleAutoObj openDatabaseOnMDB(string mdbFile) [since Version 0.3]\r\n<\/pre>\nDownload<\/h2>\n
First download the DXL-Library here…<\/p>\n
sqllibrary.dxl — Version 0.3 [match filename the includes of the examples!!!]<\/a>
\nsqlserverlibrary.dxl — Version 0.2<\/a>
\nsqlserverlibrary.dxl — Version 0.1<\/a><\/p>\nCreate your DXL Skript in Doors<\/h2>\n
Recommendation: When you are writing your SQL Statements, use the CAST-Method every time you define a Field in the SELECT-Clause. <\/p>\n
Eample for Version 0.3 with MS Access Support<\/h3>\n
In this example (only works since version 0.3) you can access .mdb Files like in the following example:<\/p>\n
\r\n#include "C:\\\\dev\\\\dxl\\\\client\\\\sqllibrary.dxl";\r\n\r\n\r\nSkip result = selectStatementOnMDB("SELECT [Connector_ID], [Connector_Type], [Btm_Mid_Label], [Stereotype] FROM q_connector", "\\\\\\\\bbmag295\\\\ProcessModellingPrototypeAdvanced\\\\mdb\\\\modell.eap");\r\n\t\t\t\t\r\n\/\/ Iteration through the SkipList\r\nfor myIterator in result do \r\n{\r\n string keyValue = (string key(result));\r\n DxlObject currentObject = null;\r\n\r\n if(find(result, keyValue, currentObject))\r\n {\r\n \/\/ Just put the column names here.. it will work\r\n print (string currentObject->"Connector_ID") "\\t";\r\n print (string currentObject->"Connector_Type") "\\t";\r\n print (string currentObject->"Btm_Mid_Label") "\\t";\r\n print (string currentObject->"Stereotype") "\\n";\r\n }\r\n}\r\n\r\ndelete result;\r\n\r\n<\/pre>\nExample:<\/p>\n
\r\nstring customFieldSQL= "" \/\/-\r\n" SELECT CAST(pk.PROJECT_KEY+'-'+CAST(ji.issuenum as VARCHAR(7)) as varchar(10)) as pkey, " \/\/-\r\n"\t CAST(convert(char, cg.created, 120) as varchar(30)) as on_date, " \/\/-\r\n"\t\t CAST(ci.field as varchar(50)), " \/\/-\r\n"\t\t CAST(cg.author as varchar(10)) as changed_by, " \/\/-\r\n"\t\t CAST(ISNULL(ci.oldstring,'') as varchar(4096)) as changed_from, " \/\/-\r\n"\t\t CAST(ISNULL(ci.newstring,'') as varchar(4096)) as changed_to " \/\/-\r\n" FROM jiraissue ji, changegroup cg, changeitem ci, project_key pk " \/\/-\r\n" WHERE ci.groupid = cg.id " \/\/-\r\n" AND cg.issueid = ji.id " \/\/-\r\n" AND pk.PROJECT_ID=ji.PROJECT " \/\/-\r\n" AND ci.field = '" customField "' " \r\n<\/pre>\nExamples for Version 0.2<\/h3>\n
\r\n#include "sqlserverlibrary.dxl"; \/\/ renamed to sqllibrary since version 0.3\r\n\r\n\/\/ Here you can define your connection setting\r\nstring dbServer="IPorSERVERNAME";\r\nstring dbName="Databasename";\r\nstring dbUsername="databaseuser";\r\nstring dbPassword="databasepasswort";\r\n\r\n\/\/ Reduce methods only to get SQL \r\nSkip selectStatement(string sql) \r\n{\treturn selectStatement(sql,dbServer,dbName,dbUsername,dbPassword); }\r\nArray selectStatement(string sql) \r\n{\treturn selectStatement(sql,dbServer,dbName,dbUsername,dbPassword); }\r\nint executeQuery(string sql) \r\n{ return executeQuery(sql,dbServer,dbName,dbUsername,dbPassword); }\r\n\r\n\/\/ A manipulation like INSERT\/UPDATE\/DELETE\r\nexecuteQuery("INSERT INTO table(col1) VALUES('TEST')");\r\n\r\n\/\/ SKIPLIST: SELECT statement will return a SKIP-List with \r\n\/\/ dynamic attributes (KEY is first column) \r\nSkip result = \/\/-\r\nselectStatement("SELECT [id], [col1], [col2], [col3], " \/\/-\r\n\t\t\t "[col4], [col5], [col6] " \/\/-\r\n\t\t\t\t"FROM table " \/\/-\r\n\t\t\t\t"WHERE col1<>'TEST' ORDER BY id ASC");\r\n\t\t\t\t\r\n\/\/ Iteration through the SkipList\r\nfor myIterator in result do \r\n{\r\n string keyValue = (string key(result));\r\n DxlObject currentObject = null;\r\n\r\n if(find(result, keyValue, currentObject))\r\n {\r\n \/\/ Just put the column names here.. it will work\r\n print (string currentObject->"id") "\\t";\r\n print (string currentObject->"col1") "\\t";\r\n print (string currentObject->"col2") "\\t";\r\n print (string currentObject->"col3") "\\t";\r\n print (string currentObject->"col4") "\\t";\r\n print (string currentObject->"col5") "\\t";\r\n print (string currentObject->"col6") "\\n";\r\n }\r\n}\r\n\r\ndelete result;\r\n\r\n\/\/ Array: SELECT statement will return an Array \r\nArray result2 = selectStatement("SELECT [id], [col1], [col2], " \/\/-\r\n\t\t\t\t"[col3], [col4], [col5], [col6] " \/\/-\r\n\t\t\t\t"FROM table " \/\/-\r\n\t\t\t\t"WHERE col1<>'TEST' ORDER BY id ASC");\r\n\r\nint i;\r\nfor (i=0; i<resultCount; i++)\r\n{\r\n DxlObject currentObject2 = (DxlObject get(result2,i,1));\r\n \r\n if(!null(currentObject2))\r\n {\r\n \/\/ Just put the column names here.. it will work\r\n print (string currentObject2->"id") "\\t";\r\n print (string currentObject2->"col1") "\\t";\r\n print (string currentObject2->"col2") "\\t";\r\n print (string currentObject2->"col3") "\\t";\r\n print (string currentObject2->"col4") "\\t";\r\n print (string currentObject2->"col5") "\\t";\r\n print (string currentObject2->"col6") "\\n";\r\n }\r\n}\r\n\r\ndelete result2;\r\n\r\n<\/pre>\nExample for Version 0.1<\/h3>\n
\r\n#include "sqlserverlibrary.dxl"; \/\/ renamed to sqllibrary since version 0.3\r\n\r\n\/\/ Here you can define your connection setting\r\nSkip selectStatement(string sql)\r\n{\r\n\t\/\/ Connection details for test\r\n\tstring dbServer="DATABASE_SERVER_OR_IP";\r\n\tstring dbName="DATABASE_NAME";\r\n\tstring dbUsername="DATABASE_USER";\r\n\tstring dbPassword="DATABASE_PASSWORD";\r\n\r\n\treturn selectStatement(sql,dbServer,dbName,dbUsername,dbPassword);\r\n}\r\n\r\n\/\/ selectStatement will return a SKIP-List with dynamic attributes\r\nSkip result = \/\/-\r\nselectStatement( \/\/-\r\n"SELECT col1,col2,col3,col4,col5,col6 " \/\/-\r\n\t\t\t"FROM table " \/\/-\r\n\t\t\t"WHERE col1 " \/\/-\r\n\t\t\t" NOT IN ( " \/\/-\r\n\t\t\t"\tSELECT col1 " \/\/-\r\n\t\t\t"\tFROM table " \/\/-\r\n\t\t\t" \tWHERE LTRIM(RTRIM(col1))<>'---' " \/\/-\r\n\t\t\t" GROUP BY alarm_id " \/\/-\r\n\t\t\t"\tHAVING COUNT(*)>1 " \/\/-\r\n\t\t\t"\t) ")\r\n\t\t\t\t\r\n\/\/ Iteration durch SkipList\r\nfor myIterator in result do \r\n{\r\n string keyValue = (string key(result));\r\n DxlObject currentObject = null;\r\n\r\n if(find(result, keyValue, currentObject))\r\n {\r\n \/\/ Just put the column names here.. it will work\r\n print (string currentObject->"col1") " ";\r\n print (string currentObject->"col2") " ";\r\n print (string currentObject->"col3") " ";\r\n print (string currentObject->"col4") " ";\r\n print (string currentObject->"col5") " ";\r\n print (string currentObject->"col6") "\\n";\r\n }\r\n}\r\n\r\ndelete result;\r\n<\/pre>\nComment<\/h1>\n
Don’t forget to leave some improvement comments here!!!<\/p>\n