Mode

Mnemonic

Syntax

Argument

Meaning

Source

The name of the ODBC source. Type STR.

UserID

The user name to access the ODBC source. Type STR.

Password

The user password to access the ODBC source. Type STR.

Timeout

The connection time-out in seconds. Type LONG.

Mode

Mnemonic

Action

1

CONNECT

An attempt is made to connect to the specified ODBC source.

Return:

>0 The return is the handle for the connection which is then used in subsequent transactions.
-1 An error, either a problem in the instruction syntax or an error when connecting.

Argument

Meaning

CHandle

The handle returned when connecting to an ODBC source. Type LONG.

Mode

Mnemonic

Action

2

DISCONNECT

An attempt is made to disconnect from the ODBC source using the given handle.
If a transaction is in progress, it should be stopped or completed using COMMIT or ROLLBACK before attempting to DISCONNECT. If you execute a DISCONNECT before a transaction is finished, a ROLLBACK will be automatically executed.

Return:

0 if disconnection successful.
-1 if there is a problem with the syntax or with disconnection.
-2 if the handle is incorrect.

3

BEGINTRANS

Start a transaction.
By default, a connection operates in auto-commit, all transactions are automatically flagged as completed in the database without calling COMMIT explicitly. Calling this function disables the auto-commit. Auto-commit is reactivated when executing either a COMMIT or ROLLBACK function.

Return:

0 if transaction successful.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.

Not all ODBC drivers support transactions and those that do treat them in a different ways. This instruction only operates under the following conditions:

The driver supports transactions.
When a COMMIT or ROLLBACK instruction is processed the driver maintains the cursors in their current position or closes them, but does not destroy them.
No requests are being processed (use the FREE function).

4

COMMIT

Execute a COMMIT.
Modifications made to the ODBC source during the current transaction are accepted.

Return:

0 if successful.
-1 if there is a problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.

Before you can execute a COMMIT, you must first have started a transaction with BEGINTRANS instruction. The operation of COMMIT depends on the ODBC driver used.

5

ROLLBACK

Execute a ROLLBACK.
Cancel any modifications made to the ODBC source during the current transaction.

Return:

if successful.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.

Before you can execute a ROLLBACK, you must first have started a transaction with BEGINTRANS instruction. The operation of ROLLBACK depends on the ODBC driver used.

7

FETCH

Fetches a single record from the result of an EXECUTE instruction.
Each time the instruction runs, it increments an internal pointer so that the next time it runs it returns the next record and so on until all records have been retrieved. This behavior is similar to reading records from a sequential file.

Return:

0 if FETCH is successful and a record was returned.
1 if FETCH is successful but no records returned. Either there were no records to return or the last record was returned by the previous FETCH.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.

FETCH places the next record in an internal buffer but does not supply the values to the SCADA Basic script. To retrieve individual fields it must be followed with a GETCOL or GETROW instruction.

10

NUMCOL

Return the number of columns resulting from executing a command.

Return:

>=0 the instruction was executed successfully. The return is the number of columns.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.

The instruction must be preceded by an EXECUTE.

11

ROWCOUNT

Return the number of records affected by an Insert, Update or Delete query.

Return:

>=0 the instruction was executed successfully. The return is the number of records.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.

The instruction must be preceded by an EXECUTE.

14

FREE

Frees resources used by an SQL request.

Return:

0 if successful.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.

This instruction allows the recovery of resources that may still be allocated due to a failed request. A FREE action is also executed automatically when the instruction DISCONNECT is used. The FREE instruction must also be used prior to starting a transaction.

Argument

Meaning

CHandle

The handle returned when connecting to an ODBC source. Type LONG.

MsgString

The SQL command to be executed. Type STR.

Mode

Mnemonic

Action

6

EXECUTE

The SQL command supplied is executed.

Return:

0 if command is executed successfully.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.

Only a single SQL command may be active per connection. If you execute another command before the previous one has completed, the previous command will be terminated and the result lost.

Argument

Meaning

SHandle

The handle returned when connecting to an ODBC source. Type LONG.

ColNum

The column number of the field to retrieve. Starts at 1 and increments from left to right. Type INTEGER.

BHandle

The handle of a memory buffer into which the retrieved field is placed. Type LONG.

Mode

Mnemonic

Action

8

GETCOL

Retrieves a single field of data and places it in the specified memory buffer.
This instruction must be preceded by an EXECUTE and a FETCH.

The field is always retrieved as a character string regardless of its format within the Variables Tree. The maximum length field that can be retrieved is 255 characters.

12

GETCOLNAME

Retrieves the name of a column and places it in a memory buffer.

This instruction must be preceded by an EXECUTE and a FETCH.

Return:

0 < 2,047 the instruction was executed successfully. The return is the number of characters placed in the buffer.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.
-3 if the buffer is not large enough to accommodate the number of characters returned.

Argument

Meaning

CHandle

The handle returned when connecting to an ODBC source. Type LONG.

BHandle

The handle of a memory buffer into which the retrieved field is placed. Type LONG.

Delim

A chain of characters to be used as a field delimiter. Type STR.

Mode

Mnemonic

Action

9

GETROW

Retrieves an entire row of data and places it in the specified memory buffer.
This instruction must be preceded by an EXECUTE and a FETCH.

Return:

>= 0 the instruction was executed successfully. The return is the number of characters placed in the buffer.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.
-3 if the buffer is not large enough to accommodate the number of characters returned.

The data is always retrieved as a character string regardless of its format within the Variables Tree.

Argument

Meaning

CHandle

The handle returned when connecting to an ODBC source. Type LONG.

ColNum

A column number. Starts at 1 and increments from left to right. Type INTEGER.

Mode

Mnemonic

Action

13

GETCOLSIZE

Retrieves the maximum size of a column from a preceding EXECUTE instruction.

Return:

>= 0 the instruction was executed successfully. The return is the size of the column.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.
-3 if the buffer is not large enough to accommodate the number of characters returned.

The size returned corresponds to the number of characters necessary to store the column as text, rather than the number of bytes that the column occupies.

Argument

Meaning

CHandle

The handle returned when connecting to an ODBC source. Type LONG.

BHandle

The handle of a memory buffer. Type LONG.

Mode

Mnemonic

Action

6

EXECUTE

The SQL command contained in the memory buffer is executed.

Return:

0 if command is executed successfully.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.

Only a single SQL command may be active per connection. If you execute another command before the previous one has completed, the previous command will be terminated and the result lost.

15

ERROR

Returns the last error message from the ODBC driver and places in the memory buffer specified by the handle.

Return:

>=0 if command is executed successfully. The return corresponds to the number of bytes in the message.
-1 if there is problem with the syntax or the operation is refused by the driver.
-2 if the handle is incorrect.
-3 if the buffer is not large enough to accommodate the number of characters returned.