Developer Reference > Data Access Methods > ODBC Guide > Zen ODBC Reference
Zen ODBC Reference
This reference covers information for the relational interface and ODBC, including connection strings, metadata versions, limits, and SQL grammar under the following topics:
Data Source Name Connection String Keywords
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.
SQL Grammar Statement
Minimum
Core
Extended
ALTER TABLE
 
 
X
CREATE GROUP
 
 
X
CREATE INDEX
 
X
 
CREATE TABLE
 
 
X
CREATE TABLE
 
 
X
CREATE TRIGGER
 
 
X
CREATE VIEW
 
X
 
DELETE (positioned)
X
 
 
DELETE (searched)
X
 
 
DROP GROUP
 
 
X
DROP INDEX
 
X
 
DROP PROCEDURE
 
 
X
DROP TABLE
X
 
 
DROP TRIGGER
 
 
X
DROP VIEW
 
X
 
GRANT
 
X
 
INSERT
X
 
 
JOIN LEFT OUTER (Select)
 
 
X
REVOKE
 
X
 
SELECT (with INTO)
X
 
 
Approximate Numeric Literal
 
X
 
Between Predicate
 
X
 
Correlation Name
 
X
 
Date Arithmetic
 
 
X
Date Literal
 
 
X
Exact Numeric Literal
 
X
 
extended predicates
 
 
X
In Predicate
 
X
 
Set Functions
 
X
 
Time Literal
 
 
X
Time Stamp Literal
 
 
X
Subqueries
 
X
 
SET SECURITY
 
 
X
UPDATE (positioned)
X
 
 
UPDATE (searched)
X
 
 
UNION
 
 
X
Delimited Identifiers in SQL Statements
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.
See Zen Supported Data Types in SQL Engine Reference for the following information about the data types:
• Zen metadata type code
• Size
• Create and Add parameters
• Notes specific to each data type
 
Relational Type
ODBC Type (code)1
AUTOTIMESTAMP
SQL_TIMESTAMP(93)
BFLOAT4
SQL_REAL(7)
BFLOAT8
SQL_DOUBLE(8)
BIGIDENTITY
SQL_BIGINT(-5)
BIGINT
SQL_BIGINT(-5)
BINARY
SQL_BINARY(-2)
BIT
SQL_BIT(-7)
CHAR
SQL_CHAR(1)
CURRENCY
SQL_DECIMAL(3)
DATE
SQL_DATE(91)
DATETIME
SQL_TIMESTAMP(93)
DECIMAL
SQL_DECIMAL(3)
DOUBLE
SQL_DOUBLE(8)
IDENTITY
SQL_INTEGER(4)
INTEGER
SQL_INTEGER(4)
LONGVARBINARY
SQL_LONGVARBINARY(-4)
LONGVARCHAR
SQL_LONGVARCHAR(-1)
MONEY
SQL_DECIMAL(3)
NCHAR
SQL_WCHAR(-8)
NLONGVARCHAR
SQL_WLONGVARCHAR(-10)
NUMERIC
SQL_NUMERIC(2)
NUMERICSA
SQL_NUMERIC(2)
NUMERICSLB
SQL_NUMERIC(2)
NUMERICSLS
SQL_NUMERIC(2)
NUMERICSTB
SQL_NUMERIC(2)
NUMERICSTS
SQL_NUMERIC(2)
NVARCHAR
SQL_WVARCHAR(-9)
REAL
SQL_REAL(7)
SMALLIDENTITY
SQL_SMALLINT(5)
SMALLINT
SQL_SMALLINT(5)
TIME
SQL_TIME(92)
TIMESTAMP
SQL_TIMESTAMP(93)
TIMESTAMP2
SQL_TIMESTAMP(93)
TINYINT
SQL_TINYINT(-6)
UBIGINT
SQL_BIGINT(-5)
UINTEGER
SQL_INTEGER(4)
UNIQUEIDENTIFIER
SQL_GUID(-11)
USMALLINT
SQL_SMALLINT(5)
UTINYINT
SQL_TINYINT(-6)
VARCHAR
SQL_VARCHAR(12)
1SQL_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 1 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.
 
Last modified date: 10/31/2023