(Engine-Level Function)
| Description | Performs an ODBC command and returns a dynamically allocated array if required. |
| Returns | Array |
| Usage | Script Only. |
| Function Groups | Database and Data Source, ODBC |
| Related to: | ODBCConfigureData | ODBCConnect | ODBCDisconnect | ODBCSources | ODBCStatus | ODBCTables | TODBC | TODBCConnect | TODBCDisconnect |
Format:  |
ODBC(DB, SQLCommand [, Attrib, ErrorMsg, SQLState, ErrorCode]) Refer to the comments section of TODBC for a discussion on the differences between blocking and non-blocking ODBC calls. |
| Parameters |
| DB | ||||||||||
| Required. An ODBC value for the ODBC database as returned by ODBCConnect. | ||||||||||
| SQLCommand | ||||||||||
| Required. Any text expression for the SQL command to perform on the ODBC database driver. If the query involves long binary data types, then a structure should be used. See examples. | ||||||||||
| Attrib | ||||||||||
An optional parameter that returns a 2D array, with each row in the array holding detailed attributes for one column of the result set, as per the following
|
| 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. |
| 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). 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, VTScada 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. 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 VTScada 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 binary large objects:
{ Declare and initialize the structure }
ODBCQuery STRUCT [
QueryString;
Parameters;
];
Query = ODBCQuery();
Query\QueryString = "INSERT INTO TestLargeBlobs (id, Blob1, Blob2) VALUES (1, ?, ?)";
Query\Parameters = New(2);
Query\Parameters[0] = MakeBuff(10000000, 65);
Query\Parameters[1] = MakeBuff(20000000, 66);
ODBC(DBHandle, Query);
Similarly, to do an update:
Query = ODBCQuery(); Query\QueryString = "UPDATE TestLargeBlobs SET Blob2=? WHERE id=1"; Query\Parameters = MakeBuff(30000000, 67); ODBC(DBHandle, Query);
Example 3:
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 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 4
Suppose that in a VTScada 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 5
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 6
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 7
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 following example shows an SQL command placed into a VTScada 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 VTScada that they are part of the string and not the end of the string. This applies to all text constants used anywhere in VTScada.
