PSQL ODBC Reference
Reference Information on Connection Strings, Metadata Versions, Limits and SQL Grammar
This chapter contains reference information for the Relational Interface and ODBC:
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 PSQL 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 PSQL:
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.
Table 7 SQL Grammar Support
SQL Grammar Statement | Minimum | Core | Extended |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
DELETE (searched) | | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
extended predicates | | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
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 PSQL. 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
PSQL Transactional and Relational Data Types in
SQL Engine Reference for the following information about the data types:
•PSQL metadata type code
•Size
•Create and Add parameters
•Notes specific to each data type
Table 8 PSQL Supported ODBC Data Types
Relational Type | ODBC Type (code)1 |
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(9) |
DATETIME | SQL_TIMESTAMP(11) |
DECIMAL | SQL_DECIMAL(3) |
DOUBLE | SQL_DOUBLE(8) |
IDENTITY | SQL_INTEGER(4) |
INTEGER | SQL_INTEGER(4) |
LONGVARBINARY | SQL_LONGVARBINARY(-4) |
LONGVARCHAR | SQL_LONGVARCHAR(-1) |
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(10) |
TIMESTAMP | SQL_TIMESTAMP(11) |
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) |
1 SQL_FLOAT and SQL_VARBINARY are not supported by PSQL |
Representation of Infinity
When PSQL 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.
