ODBC functions

StreamServe ODBC functions enable interfacing with ODBC compliant data sources. You can connect to any data source that has an ODBC driver configured for the platform that StreamServe is operating on.

If you use transactional commands, such as BEGIN, COMMIT and TRANSACTION, then you should invoke them by using the build in StreamServe script functions, such as ODBCBeginTrans, ODBCEndTrans, OdbcRollbackTrans, etc.

To be able to use the StreamServe ODBC functions, you must be familiar with ODBC and data source concepts, SQL, scripting in StreamServe, and the operating platforms that both StreamServe and the data sources to be used will be running on.

Requirements

To use the ODBC script functions, you must

•	Configure an ODBC data source

You must configure an ODBC data source for each data source you want to use. Once configured, the data sources will be accessible for all ODBC functionality, not just with StreamServe.

On Windows, you configure the ODBC Drivers by using the ODBC Data Source Administrator found in Control Panel > Administrative Tools > Data Sources (ODBC). We recommended you read Microsoft documentation about ODBC connectivity.

On UNIX, you must configure the odbc.ini file in <StreamServe installation>/Platform to comply with your environment.

•	Use a reliable ODBC driver

The ODBC functions provide an interface to ODBC and not to a specific data source. You must choose a reliable and thoroughly tested ODBC driver for your data source. The StreamServe ODBC module does not check how your ODBC driver executes the SQL statements.

On UNIX, you must configure the odbcinst.ini files in <StreamServe installation>/Platform if you use any other driver than DataDirect.

•	Set up ODBC startup arguments

You can use startup arguments to enable the object pool for the scripting functions or to change the default time-out value for ODBC connections. See ODBC startup arguments.

Escape sequences

The less-than sign (<) is a reserved character and must be escaped if used in a string, see Escape sequences.

ODBC functions

The table below contains a short description of each script function. For information about syntax, examples, etc., see the full description of each script function.

Function name	Description
OdbcBeginTrans	Begins a new transaction, which opens the following possibilities: • To commit the transaction, call the ODBCCommitTrans function. • To end the transaction, call the ODBCRollbackTrans function. • No new function is called and the transaction is automatically rolled back, for example, if the connection times out or is lost.
OdbcCommitTrans	Commits a transaction that you have opened with ODBCBeginTrans. The changes within the transaction are saved to the database and the transaction is ended.
OdbcConnect	Connects the user to the data source.
OdbcConnectW	Connects the user to the data source. If the data retrieved with ODBCGetFirst, ODBCGetNext, or ODBCGetOne is of Unicode type, you must use the OdbcConnectW (instead of OdbcConnect) when connecting to the data source.
OdbcCloseQuery	Closes any open queries (statements) in the data source. An open query exists when OdbcGetFirst has been executed and all rows in the result set have not been retrieved with OdbcGetNext.
OdbcDate2Date	Extracts the date from a date and time string.
OdbcDate2Time	Extracts the time from a date and time string.
OdbcDisconnect	Disconnects the server from the data source.
OdbcExecute	Executes SQL statements. Any result sets that the statement generates are ignored. This function can be used, for example, to update record fields in the data source.
OdbcExecuteEx	Same as OdbcExecute. The difference is that this function can be configured to accept that a CREATE TABLE statement fails. This is useful if you do not want the StreamServer to stop when you use the function to try and create a table that already exists.
OdbcGetOne	Executes an SQL statement and copies the values from the first row in the result set to script variables.
OdbcGetFirst	Executes an SQL statement and copies the values from the first row in the result set to the specified script variables. The OdbcGetNext function is then used to retrieve the remaining parts of the result set.
OdbcGetNext	Retrieves the values of the columns in the next row of a result set. This function can only be called after OdbcGetFirst has been called to retrieve the first row in the result set. OdbcGetNext can then be called repeatedly to process all the remaining rows.
OdbcRollbackTrans	Rolls back and ends a transaction that you have opened with ODBCBeginTrans.
OdbcSetCodepage	Sets the code page.

Note:

ODBC functions that update the database (OdbcXxxTrans) cannot be run in the preprocessing phase. These functions must be inside an if (preproc() == 0) { } block.