# CHAPTER 4: Empress ODBC

## 4.1 Introduction

Empress ODBC is a library (a Dynamic Link Library for PC Windows platforms) that implements ODBC function calls and interacts with an Empress Connectivity (ODBC) Server on one side and the user's application on the other side. The Empress Connectivity (ODBC) Server passes the requests (SQL statements) from the Empress ODBC to the Dynamic SQL module or Empress Database Engine in the Empress RDBMS. The Dynamic SQL module processes the requests and returns the results.

The Empress ODBC Driver and Empress Local Access Driver is a Microsoft ODBC 3.5 Client according to Microsoft ODBC 3.5 Programmer's Reference and SDK Guide.

The Empress ODBC Client is a multiple-tiered Client which means that it processes ODBC calls but not SQL statements. The client passes SQL statements to the Empress Connectivity (ODBC) Server for processing. The ODBC standard defines two areas of conformance: ODBC API Conformance levels and ODBC SQL Conformance levels. The Empress ODBC Client complies to the:

- ODBC Core SQL Grammar Conformance level,
- ODBC Level 1 API Conformance level
- ODBC Level 2 API Conformance level

See the Microsoft ODBC 3.0 Programmer's Reference and SDK Guide for more detailed information about SQL Grammar Conformance levels and API Conformance levels.

## 4.2 ODBC Core Level API functions

The Empress ODBC Client supports the following ODBC Core Level ODBC API functions:

Table 4-1: ODBC Core Level ODBC API Functions

Function name | Description
`SQLAllocHandle` | Obtains an odbc handle.
`SQLBindCol` | Assigns storage for a result column and specifies the data type.
`SQLBindParameter` | Bind Parameter for sql statement
`SQLCancel` | Cancels an SQL statement.
`SQLCloseCursor` | Closes a cursor that has been opened on a statement and discards pending results.
`SQLColAttribute` | Describe attribute of a column in the result set.
`SQLColumns` | Returns the list of column names in specified tables.
`SQLConnect` | Connects to a specific driver by data source name, user ID, and password.
`SQLCopyDesc` | Copies descriptor information from one descriptor handle to another.
`SQLDescribeCol` | Describes a column in the result set.
`SQLDisconnect` | Closes the connection.
`SQLDriverConnect` | is an alternative to SQLConnect.
`SQLEndTran` | requests a commit or rollback operation for all active operations on all statements associated with a connection.
`SQLExecDirect` | Executes a statement.
`SQLExecute` | Executes a prepared statement.
`SQLFetch` | Returns a result row.
`SQLFetchScroll` | Fetches the specified rowset of data from the result set and returns data for all bound columns.
`SQLFreeHandle` | Frees resources associated with a specific environment, connection, statement, or descriptor handle.
`SQLFreeStmt` | Stops processing associated with a specific statement, closes any open cursors associated with the statement, discards pending results, or, optionally, frees all resources associated with the statement handle.
`SQLGetConnectAttr` | Returns the current setting of a connection attribute.
`SQLGetCursorName` | Returns the cursor name associated with a statement handle.
`SQLGetData` | Retrieves data for a single column in the result set.
`SQLGetDescField` | Returns the current setting or value of a single field of a descriptor record.
`SQLGetDescRec` | Returns the current settings or values of multiple fields of a descriptor record.
`SQLGetDiagField` | Returns the current value of a field of a record of the diagnostic data structure (associated with a specified handle) that contains error, warning, and status information.
`SQLGetDiagRec` | Returns the current values of multiple fields of a diagnostic record that contains error, warning, and status information.
`SQLGetEnvAttr` | Returns the current setting of an environment attribute.
`SQLGetInfo` | Returns general information about the driver and data source associated with a connection.
`SQLGetStmtAttr` | Returns the current setting of a statement attribute.
`SQLGetTypeInfo` | Returns information about data types supported by the data source.
`SQLNativeSql` | Returns the SQL string as modified by the driver.
`SQLNumParams` | Returns the number of parameters in an SQL statement.
`SQLNumResultCols` | Returns the number of columns in the result set.
`SQLParamData` | Used in conjunction with `SQLPutData` to supply parameter data at statement execution time.
`SQLPrepare` | Prepares an SQL statement for later execution.
`SQLPutData` | Allows an application to send data for a parameter or column to the driver at statement execution time.
`SQLRowCount` | Returns the number of rows affected by an insert, update, or delete request.
`SQLSetConnectAttr` | Sets attributes that govern aspects of connections
`SQLSetCursorName` | Specifies a cursor name.
`SQLSetDescField` | Sets the value of a single field of a descriptor record.
`SQLSetDescRec` | Sets multiple descriptor fields that affect the data type and buffer bound to a column or parameter data.
`SQLSetEnvAttr` | Sets attributes that govern aspects of environments
`SQLSetStmtAttr` | Sets attributes related to a statement.
`SQLSpecialColumn` | Retrieves the following information about columns within a specified table: The optimal set of columns that uniquely identifies a row in the table. Columns that are automatically updated when any value in the row is updated by a transaction.
`SQLStatictics` | Retrieves a list of statistics about a single table and the indexes associated with the table.
`SQLTables` | Returns the list of table, catalog, or schema names, and table types stored in a specific data source.

## 4.3 ODBC Level 1 ODBC API Functions

The Empress ODBC Client supports the following ODBC Level 1 ODBC API functions:

Table 4-2: ODBC Level 1 ODBC API Functions

Function name | Description
`SQLBrowseConnect` | Returns successive levels of connection attributes and valid attribute values. When a value has been specified for each connection attribute, connects to the data source.
`SQLBulkOperation` | Performs bulk insertions.
`SQLMoreResults` | Determines whether more results are available on a statement containing SELECT, UPDATE, INSERT, or DELETE statements and, if so, initializes processing for those results.
`SQLPrimaryKeys` | Returns the column names that make up the primary key for a table.
`SQLProcedureColumns` | Returns the information about optimal set of columns that uniquely identifies a row in a specified table.
`SQLProcedures` | Returns the list of procedure names stored in a specific data source.
`SQLSetPos` | Sets the cursor position in a rowset and allows an application to refresh data in the rowset or to update or delete data in the result set.

## 4.4 ODBC Level 2 ODBC API Functions

The Empress ODBC Client supports the following ODBC Level 2 ODBC API functions:

Table 4-3: ODBC Level 2 ODBC API Functions

Function name | Description
`SQLColumnPrivileges` | Returns a list of columns and associated privileges for the specified table.
`SQLDescribeParam` | returns the description of a parameter marker associated with a prepared SQL statement.
`SQLForeignKeys` | returns a list of foreign keys in the specified table and a list of foreign keys in other tables that refer to the primary key in the specified table.
`SQLTablePrivileges` | Returns a list of tables and the privileges associated with each table.

## 4.5 Asynchronous Processing

The Empress ODBC driver executes ODBC calls synchronously by default; which means that driver does not return control to an application until function call completes. In the cases where Connectivity (ODBC) Server has to perform a long task, client application could wait in an idle state for a long period. In an asynchronous processing mode, when executing ODBC call Empress ODBC driver could immediately return SQL_STILL_EXECUTING return code to an application.

The Empress ODBC Driver supports connection level asynchronous execution. Either all statement handles associated with a given connection handle are in an asynchronous mode, or all are in a synchronous mode. A statement handle on a connection cannot be in an asynchronous mode while another statement handle on the same connection is in a synchronous mode, and vice versa.

## 4.6 Multi-thread Behaviour

To acquire parallelism in database applications both Empress ODBC Driver and Empress ODBC Local Access Driver can be used in threads. One should issue concurrent SQL operations (e.g. issue concurrent queries from different threads) by starting up multiple connections and by associating those connections with each thread.