TODBC

(Engine-Level Function)

Description	Performs an ODBC command. Similar to ODBC except that it runs in its own thread (see "Comments" section for differences).
Returns	Nothing (see parameters)
Usage	Script Only.
Function Groups	Database and Data Source,  ODBC
Threaded	Yes
Related to:	ODBC | ODBCConfigureData | ODBCConnect | ODBCDisconnect | ODBCSources | ODBCStatus | ODBCTables | TODBCConnect | TODBCDisconnect
Format:	TODBC(DB, SQLCommand, Attrib, Result, ErrorMsg, SQLState, ErrorCode [, QueryTimeout])
Parameters

DB

Required. An ODBC value for the ODBC database as returned by TODBCConnect or ODBCConnect.

SQLCommand

Required. Any text expression for the SQL command to perform on the ODBC database driver.

Attrib

Required. Any variable that will be set to an array that gives certain information regarding the table returned in the next parameter. The first dimension of the array contains the name of the column in the return table, while the second dimension contains the type - 0 for character data, 1 for numeric data.

Result

Required. Any variable that will be set to an array containing the data resulting from the SQL command. The format for the array is Result[Field][Record].

ErrorMsg

Required. Any variable that will contain the last error message returned by the function. If no errors occurred this parameter will be set to 0 to indicate the termination of the thread.

SQLState

Required. Any variable that will contain the SQL state that the statement was in when the last error occurred.

ErrorCode

Required. Any variable that will contain the native error code for the given driver and an error condition for the last error that occurred.

QueryTimeout

An optional parameter that sets the period (in seconds) which the driver will wait for a query request to complete. The default value of "0" indicates that there is no timeout. Please note that not all ODBC drivers support the optional QueryTimeout parameter. In particular, the Microsoft Access (.mdb) driver. If the driver does not support this option, then an error message will be returned by the statement. If this occurs, then the parameter should be left as Invalid to allow the statement to proceed.

Comments

This command will require a knowledge of SQL (Structured Query Language). This function is typically used when a large amount of data is being processed. When TODBC is executed in its script, it starts its own thread and VTScada will continue executing. When the TODBC statement is finished executing, it will set the data in Attrib, give the resultant table in Result and set ErrorCode. Some SQL statements, such as the one for inserting a record into a table, do not return any data, in this case the only indication that the thread has terminated will be that ErrorCode will be set to a valid value - 0 if no errors occurred. Notice that unlike ODBC, the resultant table is returned in one of the parameters, rather than as a return value and the last three parameters are not optional. If any error, no matter how minor, occurs as a result of the SQL command having been executed, and the TODBCConnect or ODBCConnect that connected to the database had its Disconnect parameter set true, the value of DB will become invalid (i.e. the connection to the database will be dropped). This includes such trivial indiscretions as using an incorrect table name in the SQL command. Differences between blocking and non-blocking ODBC calls: Prior to VTS version 10.0, executing one of the following blocking ODBC operations - ODBC, ODBCTables, ODBCConnect, ODBCDisconnect, ODBCBeginTrans, ODBCCommit or ODBCRollback would cause all VTScada script code execution and window painting to suspend until the operation was complete. From VTS 10.0 onwards, only the VTScada script thread making the call is suspended. All other threads and window painting continue to function. Prior to VTS version 10.0, a non-blocking ODBC operation was used to avoid this issue (same set of operations with a 'T' prepended). This leads to more complex script code insofar as you have to initiate an operation in a script and wait for completion in steady-state, but did allow script threads and window painting to continue. However, the Txxx operations were more time consuming to execute, as they each spin up a thread to execute the operation asynchronously. From VTS 10.0 onwards, these operations are more efficient - there is now no performance difference between the Txxx non-blocking variants and the blocking ones. As blocking ODBC calls no longer suspend all other threads, the only reason for using a Txxx variant call is where you wish to allow other script code statements in the same VTScada script thread to execute while the ODBC operation is processing.

Example:

If TimeOut(1, 3) { Execute SQL command every 3 seconds };
[
  TODBC(ODBCHandle { Handle to database },
        "SELECT ALL * FROM LogTasks" { SQL command },
        Attrib, ReturnArray { Results of command },
        ErrMsg, ErrState, ErrCode { Error details });
]