ODBCConnect

(Engine-Level Function)

Description: Forms a connection to an ODBC-compliant database and returns the ODBC value associated with that database.
Returns: ODBC database
Usage: Script Only.
Function Groups: Database and Data Source,  ODBC
Related to: ODBC | ODBCConfigureData | ODBCDisconnect | ODBCSources | ODBCStatus | ODBCTables | TODBC | TODBCConnect | TODBCDisconnect
Format: ODBCConnect(DSName, UserName, Password [, ErrorMsg, SQLState, ErrorCode, Disconnect, LoginTimeout, ConnectTimeout])
Parameters:  
DSName
Required. Any text expression for the ODBC data source name, as configured in the ODBC setup menu under Microsoft Windows™.
UserName
Required. Any text expression for the ODBC login user name.
Password
Required. Any text expression for the ODBC login password.
ErrorMsg
A descriptive error or status message, returned by the function. If valid, both ErrorMsg and SQLState will be valid.
SQLState
A 5-character SQL State code. SQL State codes are defined by Microsoft and by the vendor of each ODBC driver.
ErrorCode
An unsuccessful operation always returns a non-zero value, which is a numeric error code specific to the DBMS vendor's ODBC driver or Microsoft's ODBC Driver Manager.

A successful operation will always return a 0. ErrorMsg and SQLState may or may not be set valid in the event of a successful connection. If set valid, they should be examined for relevant status information.
Disconnect
An optional parameter that is any logical expression that determines how errors are to be handled. If true (non-0), the connection to the database will be disconnected should any error (no matter how minor) occur.

If false (0) an error will not cause a disconnect to occur. The default value is false.
LoginTimeout
Optional. An SQLUINTEGER (unsigned long) value corresponding to the number of seconds to wait for a login request to complete before returning to the application. The default is driver-dependent. If the value is 0, then the timeout is disabled and a connection attempt will wait indefinitely.

If the specified timeout exceeds the maximum login timeout in the data source, then the driver substitutes that value and returns SQLSTATE 01S02 (Option value changed).
ConnectTimeout
Optional. An SQLUINTEGER value corresponding to the number of seconds to wait for any request on the connection to complete before returning to the application. The driver should return SQLSTATE HYT00 (Timeout expired) any time that it is possible to time out in a situation not associated with query execution or login. 
If the value is equal to 0 (the default) then there is no timeout.
Comments:

If the UserName and Password parameters are specified as invalid, the DSName parameter is treated as a literal connection string for the ODBC connection. For example, in the statement ODBCConnect("DSN=MyData;UID=;PWD=", Invalid, Invalid), the first parameter is used to set the attributes of the connection, which makes this statement equivalent to ODBCConnect("MyData", "", "").

In the case of the optional parameters, any parameter that is not required may be set to 0 if it is followed by a valid parameter, or may be simply omitted if no valid parameters follow it.

On successful completion of the ODBC and ODBCConnect functions, the native error code will be set to 0, allowing the user to tell if a command that has no result set has been completed. The user should not assume that since a 0 is returned, then the command has been executed successfully. Some drivers (such as Excel) will return a 0 in the native error code, even when an error has occurred.

All ODBC operations can result in one or more status or error conditions arising. VTScada records the entire set of status/error conditions arising and buffers them internally. Use the ODBCStatus function to retrieve the entire set. Only the first condition that occurred or, if both error and advisory conditions occur, the first error condition, is returned in the ErrorMsg and SQLState values.

From VTS 10.0 onwards, VTS uses ODBC 3.x compliant operations (formerly ODBC 2.x). This has the side effect that different SQLState return values are returned for some SQLState values. If you have written code that depends on the value returned by SQLState, you may need to change the value you expect. See http://msdn.microsoft.com/en-us/library/ms712451%28VS.85%29.aspx for a reference on the value changes.

64-bit VTScada is able to connect to either 64-bit or 32-bit ODBC data sources. ODBCConnect will first try to connect to the database through a 64-bit ODBC driver. If this fails for any reason it will then try the connection through a 32-bit ODBC driver. This means that any ODBC code that worked under 32-bit VTScada should not need to be modified for use with 64-bit VTScada, but 64-bit VTScada has the extra ability of being able to use 64-bit ODBC drivers.