ODBC

VTS Functions > O Functions

ODBC

Description: This function performs an ODBC command and returns a (dynamically allocated) array if required.

Returns: Array

Usage: Script

Format: ODBC(DB, SQLCommand [, Attrib, ErrorMsg, SQLState, ErrorCode])

Parameters: DB { ODBC value } { required } { no default }

An ODBC value for the ODBC database as returned by ODBCConnect.

SQLCommand { text } { required } { no default }

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

Attrib { array (return value) } { optional } { no default }

An optional parameter that a two dimensional array of field attributes for the table returned by the function:

Attrib	Attribute
Attrib[x][0]	Name of field x
Attrib[x][1]	Type indication for the field:

ErrorMsg { text (return value) } { optional } { no default }

An optional parameter that will contain the last error message returned by the function.

SQLState { numeric (return value) } { optional } { no default }

An optional parameter that will return the SQL state that the statement was in when the last error occurred.

ErrorCode { numeric (return value) } { optional } { no default }

An optional parameter that is a variable that will contain the native error code for the given driver and an error condition for the last error that occurred.

Comments: There may or may not be a return value for this function, depending on the nature of the SQL command that was executed. If a return value exists, it will be a dynamically allocated, two-dimensional array that contains the rows resulting from the query. The format for the array is Result[Field][Record].

If any error, no matter how minor, occurs as a result of the SQL command, and if the ODBCConnect that connected to the database had its Disconnect parameter set true, then the value of DB will become invalid (i.e. the connection to the database will be dropped).

With regards to error information returned on successful completion of the ODBC and ODBCConnect functions, the native error code will be set to 0, allowing the user to know if a command that has no result set has been completed. The user should not assume that since a 0 is returned in the native error code that 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.

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.

This command requires a knowledge of SQL (Structured Query Language). The examples provide several SQL statements which you can use as templates.

Example 1

Optional entities are enclosed in square brackets [ ], while required ones are enclosed by angled brackets < >. Italicized text represents names of tables, fields, etc. Embedded quotes need to appear twice to signify to VTS that they are part of the string, and do mark the close of the string.

To create a table, the basic format is:

Create Table "TableName" (< list of fields/types >)

where

< list of fields/types > is a comma separated list of fields and their types (the field and type are separated by a white space character) that define the table. The field names must be enclosed in quotation marks if they duplicate an SQL reserved word or if they contain a white space character. Field types include (but are not limited to):

• Int or Integer, SmallInt, Float, Real, Double, Precision, Dec(p,d) or Decimal(p,d), Numeric(p, d)

• Char(n) or Character(n), VarChar(n) or Char Varying(n) or Character Varying(n)

• Bit(n), Bit Varying(n)

• Date, Time

where p is the precision (total number of decimal digits) and d is the number of places after the decimal point.

To create a table for a custom tag type called Motor, the SQL command might look something like the following:

Create Table "Motor" ("Name" Char(32), "Area" Char(32), "Description" Char(32), "Input" Char(32), "Status" Int, "Temperature" Decimal(5, 1))

To insert an entry into a table, the basic format is:

Insert Into "TableName"

[(<list of fields >)]

Values (<list of values >)

where

[(< list of fields >)] is an optional clause that is a comma separated list of fields defining which fields to assign the values to. The field names must be enclosed in quotation marks if they duplicate an SQL reserved word or if they contain a white space character. If this field list is omitted, all fields must have an assigned value, even if they are null, in which case the reserved word NULL (no quotes) is used.

< list of values > is a comma separated list of field values that define the record and take the form:

'Value'

Values must be enclosed in single quotes if they are text strings.

Example 2:

To insert a record with a valid name and status (but all other fields invalid) into the table created in the previous example, the SQL command might look something like the following:

Insert Into "Motor" ("Name", "Status") Values ('Motor 1234', 1)

To retrieve data from a table, the basic format is:

Select [ number of records ]< list of fields >

From <list of tables >

[Where < conditions >]

[Order By < list of fields >]

where

[number of records] is an optional statement that limits the number of records retrieved. It takes the form:

Top N

where N is the number of records.

< list of fields > is a comma separated list of fields to retrieve for each record, or by which the records are sorted. The field names must be enclosed in quotation marks if they duplicate an SQL reserved word or if they contain a white space character. If all of the fields for a record are to be retrieved, an asterisk should be used; the asterisk must not be enclosed in quotation marks.

<list of tables > is a comma separated list of tables from which to retrieve the data. As with the attribute list, the table names must be enclosed in quotation marks if they duplicate an SQL reserved word or if they contain a white space character.

[< conditions >] is an optional clause giving the list of conditions that take the form:

"FieldName" = 'Value'

where FieldName is the actual name of the field and Value is the numeric or text value to match. Note that once again, each field name need only be enclosed in quotation marks if it duplicates an SQL reserved word or contains a white space character. Similarly, the value needs to be enclosed in single quotes only if it is a text string. Multiple conditions are separated by the key word And.

Example 3

Suppose that in a VTS application a user wanted retrieve the first 10 entries of an alphabetical list of names and descriptions for standard analog input tags that belonged to the system area and had questionable data:

Select Top 10 "Name", "Description" From "AnalogInput"

Where "Area" = 'System' And "Questionable = 1 Order by "Name"

To modify an entry in a table, the basic format is:

Update "TableName" Set <list of fields/values >

Where < conditions >

where

< list of fields/values > is a comma separated list of fields and their values that define the record and take the form:

"FieldName" = 'Value'

Field names must be enclosed in quotation marks if they duplicate an SQL reserved word or if they contain a white space character. Values must be enclosed in single quotes if they are text strings.

< conditions > is a list of conditions that define which record to modify. For more information, see the < conditions > section in item number 3.

Example 4

To change an existing record (tag) in the standard analog input table (tag type), the SQL command might look something like the following:

Update "AnalogInput" Set "Area" = 'System', "UnscaledMin" = 10, "UnscaledMax" = 80 Where "Name" = 'AI36'

To delete an entry from a table, the basic format is:

Delete From "TableName" Where < conditions >

where

< conditions > is a list of conditions that define which record to delete. For more information, see the < conditions > section in item number 2.

Example 5

To delete the record that was modified in the previous example entirely, the SQL command might look something like the following:

Delete From "AnalogInput" Where "Name" = 'AI36'

Example 6

To connect to an ODBC data source and create a table whose name is held in the variable tableName, and whose text string lengths are limited to the value of the variable maxLen, the calls might look something like the following:

If Valid(dsName) Main;

[

dbHandle = ODBCConnect(dsName, "", "", eMsg, eState, eCode);

eType = ODBCStatus(0);

IfThen(eType != 0,

Slay(Self(), 0);

);

commandString = Concat("Create Table """, tableName,

""" (""Name"" Char(", maxLen, "), ""Area"" Char(", maxLen,

""Description""", Char(", maxLen, "), "Input" Char(", maxLen,

"), "Status" Int, "Temperature" Decimal(5, 1));

result = ODBC(dbHandle, commandString, fieldAttrib, eMsg,

eState, eCode);

IfThen(eType != 0,

Slay(Self(), 0);

);

]

The example below displays a SQL command placed into a VTS parameter:

ODBC(DB, "Create Table ""Motor"" (""Name"" Char(32), ""Area"" Char(32), ""Description"" Char(32), ""Input"" Char(32), ""Status"" Int, ""Temperature"" Decimal(5, 1))");

Note that all embedded quotes need to appear twice to tell VTS that they are part of the string and not the end of the string. This applies to all text constants used anywhere in VTS.