A connection string used to connect to a DSN may include any number of driver-defined keywords. Using these keywords, the driver has enough information to connect to the data source. The driver defines which keywords are required to connect to the data source.
See ODBC Connection Strings for a complete discussion of Zen connection strings and the keywords.
Closing an Open Table
Calling SQLFreeStmt with the SQL_CLOSE option changes the SQLState but does not close the open tables used by the hStmt. To close the tables currently used by hStmt, SQLFreeStmt must be called with the SQL_DROP option.
In the following example, the Emp and Dept tables remain open:
SQLPrepare(hStmt, “SELECT * FROM Emp, Dept”, SQL_NTS)
SQLExecute(hStmt)
SQLFetch until SQL_No_Data_Found
SQLFreeStmt(hStmt, SQL_CLOSE)
When SQLPrepare is subsequently called on the hStmt, the tables used in the previous statement are closed. For example, when the following call is made, both the Emp and Dept tables are closed by Zen:
SQLPrepare(hStmt, “SELECT * FROM Customer”,SQL_NTS)
The following call would then close the table Customer:
SQLFreeStmt(hStmt, SQL_DROP)
SQL Grammar Support
The ODBC v2.5 specification provides three levels of SQL grammar support: Minimum, Core, and Extended. Each higher level provides more fully-implemented data definition and data manipulation language support.
The relational interface fully supports the minimum SQL grammar, as well as many core and extended grammar statements. The relational interface support for SQL grammar is summarized in the following table. The grammar statements are documented in SQL Engine Reference.
Column names and table names can occur as delimited identifiers if they contain non-standard characters. If an identifier is a keyword, it must delimited.
The delimiter character for identifiers is the double-quote.
Examples
SELECT "last-name" FROM "non-standard-tbl"
The hyphen is a non-standard character.
SELECT "password" FROM my_pword_tbl
“Password” is a keyword in the SET PASSWORD statement.
Supported Data Types
The following table shows information about the relational data types supported by Zen for ODBC. The SRDE converts the relational data types to ODBC default types unless another data type conversion is specified when SQLGetData or SQLBindCol is called. For a discussion of data type conversions, refer to data types in the Microsoft ODBC documentation.
1 SQL_FLOAT and SQL_VARBINARY are not supported by Zen.
Representation of Infinity
When Zen is required by an application to represent infinity, it can do so in either a 4-byte (C float type) or 8-byte (C double type) form, and in either a hexadecimal or character representation, as shown in the following table:
Table 9 Infinity Representation
Value
Float Hexadecimal
Float Character
Double Hexadecimal
Double Character
Maximum Positive
0x7FEFFFFFFFFFFFFF
Maximum Negative
0xFFEFFFFFFFFFFFFF
Infinity Positive
0x7F800000
1E999
0x7FF0000000000000
1E999
Infinity Negative
0xFF800000
-1E999
0xFFF0000000000000
-1E999
Transactions
The START TRANSACTION statement is not supported outside of a stored procedure because the ODBC standard specifies that every statement is by default inside a transaction. The ODBC standard does not have an API to start a transaction. See START TRANSACTION in SQL Engine Reference.
ODBC provides for the application to decide if each SQL statement is in its own transaction or if the application will specify when each transaction is completed. ODBC automatically opens a transaction prior to any statement that is not in a transaction. Thus, with the first statement of a given connection, or with the first statement after a COMMIT or ROLLBACK, ODBC automatically starts a new transaction.
Within the ODBC standard, SQLSetConnectOption is used to specify whether each statement is in its own transaction or the application groups statements within a transaction.
Each statement is in its own transaction if SQLSetConnectOption is called specifying the option SQL_AUTOCOMMIT and the value SQL_AUTOCOMMIT_ON (this is the default). This usage means that a transaction is started at the beginning of executing a statement and the transaction is either automatically committed, if no error occurs, or rolled back, if error occurred, upon completion of statement execution.
The application can group statements in a transaction if SQLSetConnectOption is called specifying the option SQL_AUTOCOMMIT and the value SQL_AUTOCOMMIT_OFF value. This usage means that a transaction is started at the beginning of the first statement executed. The application then decides when and how to end the transaction by calling SQLTransact or executing a 'COMMIT WORK' or 'ROLLBACK WORK' statement. When the application ends one transaction, another transaction is automatically started on execution of the next statement.