These functions allow you to query or modify data on an SQL server. VisXpert comes with an built in SQL-Server database which is usually included in the project. However all functions allow you to specify an custom Connection string, allowing you to connect to any ODBC compatible database, such as SQL-Server or MS-Access Files.
To query data you use DBQuery and then parse the “result” string by functions like “GetRowCnt” and an combination of “GetRow” to get the Row you want to process and then “GetCol” to get the data-field you need. If you leave the “ConnectionString” parameter empty, the project internal database is used.
The result of DBQuery returns the data as an CSV formatted string. The first line hold the names of the columns, the second row hold their datatypes. The actual data starts at the third row
This usually follows this structure:
string result
result := DbQuery(”, ‘Select TestColumn1, Column2, OtherColumn3 from TestingData’)
long rowNo
rowNo := 3 //first row are the field names, second row holds the datatypes
//go through each row, starting at the first row. The first row is row number 1
while rowNo <= GetRowCnt(result)
string row, data
row := GetRow(result, rowNo) //first get the current row we are processing
data := GetCol(row, 2) //then get the column of that row, in our case the second row
data := GetColByName(result, rowNo, ‘Column2’) //this is equivalent to the previous line
….Process data here….
rowNo := rowNo + 1 //go to the next row
endwhile
Example of usage of SQL Query functions
DbExec
| Declaration | long DbExec (string ConnectString, string SqlStmt) |
| Function | Runs the specified string SqlStmt on the database specified by the String ConnectString and returns the number of changed records. If the ConnectString is passed empty string, the statement is displayed on the in the file.. USER_DATSQLGP.udl specified database. |
| Cross | DbQuery, DbTest, DbClose |
| Example | long nRows nRows := DbExec(“, ‘UPDATE AL_CLASSES SET DESCRIPTION_0 = ‘Message class 1′ WHERE ID = 1’) |
DbQuery
| Declaration | string DbQuery<(string ConnectString, string SqlStmt) |
| Function | Executes the specified Select statement SqlStmt on the database specified by the String ConnectString and returns the selected records in the CSV form (comma separated, CR LF) (row 1 contains the column names, row 2 contains the column types, and from row 3 the selected records, if any, are returned). If the ConnectString is passed empty string, the statement is displayed on the one in the file.. USER_DATSQLGP.udl specified database. |
| Cross | DbExec, DbTest, DbClose |
| Example | string szResultSet szResultSet := DbQuery(”, ‘SELECT * FROM AL_CLASSES’) |
DbTest
| Declaration | long DbTest (string ConnectString) |
| Function | Opens the specified database connection and returns the same as GetLastErrNo (“last error”) for the database connection specified by the String ConnectString. If the ConnectString is passed empty string, the “last error” of the one in the file.. USER_DATSQLGP.udl specified database. This function is useful to ensure that an database is connected. If this function is combined with DBTest, you can perform an database Reconnect |
| Cross | DbExec, DbQuery, DbClose |
| Example | long nResult nResult := DbTest(“) |
DbClose
| Declaration | DbClose (string ConnectString) |
| Function | Closes the database connection specified by the String ConnectString. If the ConnectString is passed empty string, the in the file.. USER_DATSQLGP.udl specified database closed. If this function is combined with DBTest, you can perform an database Reconnect |
| Cross | DbExec, DbQuery, DbTest |
| Example | DbClose(“) |
DbMsg
| Declaration | bool DbMsg(long message group, string operand, string action) |
| Function | Executes the message transaction specified by the string action with the message group and operand parameters and returns TRUE or FALSE. Message transactions operate on the current active Alarm Message archive and allow you to activate or deactivate alarm messages in the alarm system from within scripts. This function can be used to create Alarm messages not dependent on variables but rather are created via scripts. |
| Cross | DbMsgExt |
| Example | bool rv rv := DbMsg(1, ‘M 3.0’, ‘K’) |
DbMsgExt
| Declaration | bool DbMsgExt(long messagegroup, string operand, string action, string timestamp, string symbolik, string text, long messageclass, long messagetype) |
| Function | Executes the message transaction specified by the string action with the parameters message group, operand, timestamp, symbolic, test, message class and message type, and returns TRUE or FALSE. |
| Cross | DbMsg |
| Example | bool rv rv := DbMsgExt(1, ‘M 3.1’, ‘K’, ‘2008-03-12 13:09:11.000’, ‘SYM’, ‘TEXT’, 1, 2) |
GetRowCnt
| Declaration | long GetRowCnt(string s) |
| Function | Returns the number of rows of the specified string s. |
| Cross | GetColCnt |
| Example | long r r := GetRowCnt(‘”Zeile_1″‘ + ‘rn’ + ‘”Zeile_2″‘ + ‘rn’) |
GetColCnt
| Declaration | long GetColCnt(string s) |
| Function | Returns the number of columns of the specified string s. |
| Cross | GetRowCnt |
| Example | long c c := GetColCnt(‘”Spalte_1″, “Spalte_2″‘ + ‘rn’) |
GetRow
| Declaration | string GetRow(string s, long r) |
| Function | Returns the row r of the specified string s. |
| Cross | GetRowCnt, GetColCnt |
| Example | String s s := GetRow(‘”Zeile_1″‘ + ‘rn’ + ‘”Zeile_2″‘ + ‘rn’, 2) |
GetCol
| Declaration | string GetCol(string s, long r, long c) |
| Declaration | Returns the value of column c of row r of the specified string s. |
| Cross | GetColbyName |
| Example | String s s := GetCol(‘”Wert_1_1″, ” Wert_1_2″‘ + ‘rn’ + ‘”Wert_2_1″, ” Wert_2_2″ + ‘rn’, 2, 1) |
WARNING! the GetCol function does not consider Quoted strings. If you have an “new line” inside an field, it will be interpreted as an row delimiter, and wrong values will be returned. We advise you to use the following funcion, which acts as an replacement.
This issue is fixed as of VisXpert version 10.2.0.0. For versions prior, please use the provided funcion
GetColbyName
| Declaration | String GetColByName(string s, long r, string c) |
| Function | Returns the value of column c of row r of the specified string s. In the first row, all column names must be present. |
| Example | String s s := GetColByName(‘”Spalte_1″, “Spalte_2″‘ + ‘rn’ + ‘”Wert_1_1”, “Wert_1_2″‘ + ‘rn’ + ‘”Wert_2_1”, “Wert_2_2″‘ + ‘rn’, 3, ‘Spalte_2’) |
WARNING! the GetCol function does not consider Quoted strings. If you have an “new line” inside an field, it will be interpreted as an row delimiter, and wrong values will be returned. We advise you to use the following funcion, which acts as an replacement.
This issue is fixed as of VisXpert version 10.2.0.0. For versions prior, please use the provided funcion
