If you are proficient in the C programming language, you can call ODBC routines directly. The ODBC Driver supports all platforms (operating systems) that Ingres supports, so this is an option if you want to run your ODBC application on more than one platform.

Low-level ODBC applications can be more efficient than higher-level applications, since the layers of software between your application and the data are reduced. The disadvantage of low-level ODBC applications is that less of the work is automated, and the interface is less intuitive than a higher-level application interface such as ADO or the .NET Data Provider for ODBC.

The following functional overview examines some of the ODBC function calls and discusses issues specific to the Ingres environment.

ODBC handles store the environment of ODBC execution components. The components that the handles describe are not visible to the ODBC application, but they are visible internally in the ODBC driver.

There are four types of ODBC handles:

SQLAllocHandle() allocates ODBC driver resources for these four types of handles. The format of SQLAllocHandle() is:

ODBC applications are never linked directly against the ODBC driver DLL or shared library. Instead, the applications are linked against the ODBC Driver Manager, which can be the Microsoft ODBC Driver Manager, the unixODBC Driver Manager, or the ODBC CLI.

The ODBC application has no knowledge of the driver until a connection is made. During a successful connection, the ODBC Driver is dynamically loaded into the program image. After loading, the functions called in the ODBC application are passed down to the driver implementation of the function. In this way, the same ODBC application can be used with different ODBC drivers, and thus can be used with a variety of different databases. This portability is a principal aim of ODBC.

The ODBC Driver is designed to work from a driver manager such as unixODBC or the ODBC CLI. Applications link with the driver manager. The ODBC driver is loaded into the driver manager when the first connection is made to a database. One can theoretically link directly with the ODBC driver, leaving out the driver manager, but this is not supported and may yield unpredictable results.

The ODBC Driver, the ODBC CLI, and the unixODBC Driver Manager are implemented as shared libraries, but the names vary according to platform conventions. For example, on Linux, for 32-bit Ingres: the ODBC Driver is implemented as $II_SYSTEM/ingres/lib/libiiodbcdriver.1.so and libiiodbcdriverro.1.so, and the ODBC CLI is implemented as libiiodbc.1.so.

SQLConnect() allows you to connect to the database using connection attributes stored in the ODBC Data Source Name (DSN) definition. For information about creating an ODBC DSN definition, see Configure a Data Source (Windows) and Configure a Data Source (Linux).

The following program is the minimum code required for:

If your login name is a valid Ingres or Vector user for a local database, the above code is all you need to connect through SQLConnect(). See the ODBC User Authorization section for a discussion on authorizing yourself as another user.

Other details of the connection, such as the database name and type of database, are pre-defined in the ODBC DSN definition referenced by the string “myDSN”.

Note that SQLAllocHandle() was invoked to initialize the environment handle and that SQLSetEnvAttr() sets the ODBC version to SQL_OV_ODBC3. These two calls are necessary to initialize your ODBC application as an ODBC level 3 driver. The default is ODBC level 2. We recommend initializing as version 3 because some ODBC level 3 functions, such as the treatment of date and time values, depend on the ODBC version being set at level 3.

You can bypass ODBC DSN information completely if you use the SQLDriverConnect() function instead of SQLConnect(). SQLDriverConnect() uses a connection string instead of an ODBC DSN definition; the odbc.ini file is not used unless the DSN connection string attribute is specified in the connection string.

Connection strings are typically used in higher-level languages that use ODBC, such as:

An ODBC connection string is structured as a set of “attribute, value” pairs in the following abstract:

connectStr = attribute=value[;attribute=value[;...]]

Each set of “attribute, value” pairs is separated by a semicolon (“;”). No spaces or other separators, such as <TAB>, are allowed between semicolons and the next attribute pair.

The attribute pair “DSN=myDSN” causes SQLDriverConnect() to search for an ODBC DSN specification just as in SQLConnect(). However, other connection attributes specified in the connection string override any similar specification in the ODBC DSN definition.

SQLDriverConnect() requires you to provide the following minimum connection attributes:

The following program using SQLDriverConnect() performs exactly the same function as the previous code example.

The previous example applies to Windows programs. On Linux, if you are using the Ingres ODBC CLI, only the database name is required as the minimum connection attribute:

The Ingres ODBC CLI assumes that the driver type is “Ingres”, the server type is “Ingres”, and that the server is “(local)” if you do not so specify.

A list of ODBC connection attributes and values is available in Connection String Keywords.

Vnode definitions allow applications to connect to databases over the network. The "server" connection string attribute equates to the vnode name.

Normally, you use Director, VDBA, ingnet, or netutil to define the vnode name and network connections, similar to ODBC DSN definitions. However, both DSN and vnode definitions can be bypassed as shown in the following example code snippet:

The above connection string is an example of dynamic vnode definitions discussed in the Using Net chapter of this guide. The example shows that the ODBC can connect to a database without pre-defining any connection information.

There are several methods for authorizing users from within ODBC applications.

SQLConnect() and SQLDriverConnect() allow your application to specify a user (login) name and password. For example:

In SQLDriverConnect(), the "uid" and "pwd" attributes specify the local user name and local password, respectively.

Specification of the user name and password authorizes the ODBC application to act as it were logged in to the local machine as that user. This is true even if the ODBC DSN definition specifies a network connection to another machine. If you specify an invalid user name or password, the connection fails regardless of whether the connection target is local or over the network.

An Ingres user with security administrator privileges can assume the identity of other users without having to provide a password. Such users are called Ingres "super" users, as in Linux notation. An example in SQLConnect() follows:

The example code allows the ODBC application to authorize as "altUserName" without a password. In functional terms, this is the equivalent to the -u flag in Terminal Monitor.

SQLConnect() and SQLDriverConnect() can be used with installation passwords. The following code example demonstrates the use of installation passwords with SQLDriverConnect(). Note that the user name and password are not specified.

If the ODBC application executes in the local "ingres" account, the target Name Server authenticates "ingres" using the installation password for the target database. You cannot use installation passwords for a local database unless the connection is done through a vnode definition.

The Ingres super user concept applies to installation passwords. Ingres super users can specify alternate user names, but do not need to supply passwords, as shown in the following SQLConnect() example:

The example above assumes "myDSN" references a vnode definition.

If the target installation is not configured for DBMS authentication, DBMS passwords are extra passwords defined for Ingres users and maintained in the Ingres database. When an Ingres user account is associated with a DBMS password, the correct DBMS password must be supplied as a connection parameter, otherwise the connection attempt will be rejected.

DBMS passwords in the DSN definition in odbc.ini are not supported. The ODBC Driver supports DBMS passwords only through SQLDriverConnect(), as shown in the following example:

If the target installation is configured for DBMS authentication, user names and passwords are defined in the DBMS Server. In such cases, DBMS passwords are ignored, because they are not "extra" authentication. Rather, DBMS passwords are effectively the same as OS passwords in environments that do not support DBMS authentication.

The code in the previous example would still work, provided that "myPassword" was defined in the DBMS. For DBMS authentication, only the definition in the DBMS Server matters, not whether the DBMS name and password are also defined by the operating system.

User name or password specifications are not required in a Kerberos authentication environment, as shown in the following example:

For a full discussion of Kerberos authentication in an Ingres environment, see the Security Guide.

SQLConnect() authenticates the application according to the Kerberos principal. The user name of the Kerberos principal is passed to the DBMS as the owner of the connection; otherwise, no authentication information is passed between the client and server when SQLConnect() is invoked. Authentication is performed by the Ingres Service Principal of the server-side Name Server.

Some ODBC applications require that the user name and password are not hard-coded in the code. To address these requirements, ODBC applications can prompt the user for more information.

When prompted for connection information, the ODBC Driver displays the following input page:

Users can then enter the missing login information in the prompt window, and the application will attempt to connect using the supplied information.

The ODBC Driver supports the DriverCompletion argument in SQLDriverConnect(). The DriverCompletion argument is the eighth and last argument in SQLDriverConnect(). The value SQL_DRIVER_PROMPT directs the driver to display a prompt window. The value SQL_DRIVER_NOPROMPT directs the driver to not display a prompt window.

The example above displays a prompt window in a program that has a valid handle to a Windows application. Windows-sensitive applications such as ADO or the .NET Data Provider for ODBC are usually able to display prompts.

SQLDriverConnect() is not visible in Windows applications such as ADO or the .NET Data Provider for ODBC. It is not possible to modify the DriverCompletion argument in these cases. Ingres or Vector ODBC DSN definition pages include a Prompt User ID and Password check box that forces the application to prompt for more information, as in the following example:

If the application references the ODBC DSN definition at connect time, the prompt window is displayed.

For remote connections, the specification of user names and passwords overrides the user names or passwords in the vnode definition.

For local connections, the specification of user names and passwords causes the ODBC application to behave as if it was logged in as the alternate user. This behavior is true regardless of whether or not the current login is an Ingres "super" user.

It does not matter if user names and passwords are provided interactively or are hard-coded, except in the case of Kerberos. For Kerberos, if you specify user names and passwords, the ODBC application authenticates against the local installation in addition to the Kerberos authentication. The Kerberos user specified in "kinit" must match the user name specified in SQLConnect() or SQLDriverConnect().

SQLBrowseConnect() is another function that can be used to prompt for connection information. SQLBrowseConnect() returns missing connection information in a formatted output string that is used as a guide in the next connection attempt. When SQLBrowseConnect() receives the minimum connection information, a connection is made to the database.

This section explores methods for executing queries in ODBC applications.

SQLExecDirect() is commonly used for executing queries. The following code snippet creates a table and inserts a row of data.

For queries that are executed multiple times, preparing the queries before execution may improve performance because the DBMS stores the query plan of prepared queries. Rather than calculating a query plan each time a query is executed, the DBMS references the query plan for all subsequent iterations of the query.

SQLPrepare() and SQLExecute() perform the prepare and execute functions, respectively.

Unless the ODBC application explicitly prepares a query via SQLPrepare(), the ODBC always executes queries directly; there is no provision for automatically preparing queries in Ingres.

The following code snippet is the equivalent of the SQLExecDirect() code example in the Execute Queries Directly section.

Prepared queries persist in the default autocommit state. However, if you turn off autocommit via SQLSetConnectAttr(), the query plan associated with a prepared query is destroyed whenever the query is rolled back or committed. You must then re-prepare the query via SQLPrepare().

ODBC applications frequently require data to be sent to the database dynamically. Such data cannot be hard-coded in the query itself.

For comparison, the following set of queries inserts three rows with hard-coded parameters:

The above set of queries would be adequate if it was known in advance that the target table would always receive this data. Often this is not the case, so the data must be sent dynamically. The ODBC uses question marks ("?") as placeholders for dynamic queries:

A character string can be defined and bound to the query. Binding a parameter to a query means that the data is sent as a parameter along with the query. The DBMS reconstructs the parameter data with the query as if the data were hard-coded in the original query.

To send data to the DBMS, the ODBC must tell the DBMS what the data looks like. The ODBC function SQLBindParameter() serves that purpose. The following example allows an ODBC application to send one row of data dynamically to a table consisting of an integer and a varchar of length 20:

Dynamic parameters can be used in WHERE clauses. The following example selects from the cars table, using a dynamic parameter. This query is known as a searched query:

Faster inserts also can be achieved if the following conditions are met:

If the database procedure has no parameters, the procedure can be executed in a straightforward manner using SQLExecDirect():

If the database procedure requires parameters, ODBC "escape sequence" syntax must be used. The ODBC uses escape sequence syntax to signify to the ODBC Driver Manager that implementation of the syntax in question is to be performed in a way that is specific to the driver.

The general form of escape syntax for database procedures is:

The ODBC Driver supports the following Ingres database procedures:

SQLBindParameter() binds parameters for database procedures, just as for other types of queries. The following example executes a procedure that has no input parameters and returns an integer value:

The value returned from the procedure "myDbProc" is returned in the integer "retval" after the procedure is executed. Note that the third argument, ParameterType, is designated as SQL_PARAM_OUTPUT.

Input parameters are sent to the database procedure but not returned to the application. The following example shows how input parameters are used:

Note that the ParameterType argument for "inputVal" is now SQL_PARAM_INPUT. The parameter marker "?" is now designated as an input parameter to myDbProc by placing it within the parentheses after myDbProc.

BYREF parameters can be used for both input and output. The following example is almost the same as the Input Parameters example, but with one exception:

Since this procedure handles BYREF parameters, the call to SQLExecDirect() can begin with a value of 500 for the "byRef" variable, but return with any valid integer value, such as -1.

No special parameter treatment is required for database procedures that return rows. SQLBindParameter() can be used for return values, input parameters, and BYREF parameters as before, regardless of whether rows are to be returned.

The following example shows a procedure that returns rows but has no input parameters:

Database procedures execute a set of queries on the database server. This is server-based batch execution. The ODBC Driver allows sets of queries to be defined and executed in an application; this is client-based batch execution.

Explicit batch execution can be considered an alternative to database procedures. In explicit batch execution, a set of CREATE, DELETE, UPDATE, or EXECUTE PROCEDURE queries are chained together in a single SQLExecDirect() statement:

Explicit batch execution supports dynamic parameters. The parameters are bound with the column number 1 to N as if the batch were a single statement:

Since Ingres and Vector do not support multiple result sets, select queries are not allowed in explicit batches. Likewise, row-returning database procedures are not supported. Database procedures that include BYREF or output parameters are not supported in explicit batch.

A single statement that uses dynamic parameters can be effectively executed multiple times using parameter arrays. An ODBC application can bind rows of data or columns of data, called row-wise and column-wise binding, respectively. The following example uses column-wise binding:

We strongly recommend the use of parameter arrays, which can result in dramatically improved performance if the database server is Ingres 10.0 or later or Vector 2.0 or later.

As with dynamic parameters, ODBC applications must bind the fetched data to variables in the ODBC application. Data can be fetched one row at a time, or the application can declare an array to serve as a record set.

It is not necessary for the type of variable to be similar to the type of column in the DBMS. For instance, one can read a table containing an integer and bind it to a character string. The ODBC performs the conversion internally.

SQLFetch() fetches a single row of data after a select query has been executed. As with all ODBC functions, SQLFetch() returns a status that can be analyzed to determine whether the end of the result set has been reached.

The following example shows how SQLFetch() is used:

The previous SQLFetch() example fetches rows from the database, but does not make the data available to the application. The functions SQLGetData() and SQLBindCol() bind the fetched data to variables in the application. SQLGetData() binds the variables after the fetch; SQLBindCol() binds the variables before the fetch. SQLGetData() and SQLBindCol() can be used separately or together, although it is somewhat redundant to do both.

The following example expands the SQLFetch() example to show the use of SQLGetData() and SQLBindCol():

The above example works, but would work just as well if only SQLBindCol() or SQLGetData() were called. Both are included in the example to show how they are used. Note that SQLBindCol() is called only once and serves to bind data for all successive fetches. SQLGetData() is called after every fetch. SQLGetData() is called in this way to fetch variable-length data, such as large objects.

SQLFetchScroll() returns record sets, or blocks of data. By itself, SQLFetchScroll() does not provide enough information to describe the characteristics of the record set; instead, a series of calls to SQLSetStmtAttr (set query attributes) define the record set characteristics.

The following example shows how the cars table is fetched into a record set containing five rows:

The previous SQLFetchScroll() example depicts column-wise binding, which is the default. In column-wise binding, the variable arrays describe the columns of data to be fetched.

It is also possible to set up structures in your program that describe the rows to be fetched, rather than the columns. This is called row-wise binding.

The following program excerpt fetches exactly the same data as the SQLFetchScroll() example, but uses row-wise binding instead of column-wise binding. The structure typedef MODEL_ROW can be considered a snapshot of information about each row. Each column in the row structure consists of the data to be fetched and a row status indicator.

The term cursor is an acronym for CURrent Set Of Records. A database cursor is similar to the cursor on your computer screen. However, instead of pointing at something on your screen, a database cursor points to a data row set.

Some database vendors make a distinction between client and server-side cursors. However, a cursor declared in an Ingres ODBC program is always a server-side cursor. This means that the properties of the cursor are applied only on the DBMS server of the target database.

An ODBC application can name a cursor directly via a call to SQLSetCursorName():

The above code creates a cursor named C1, which is also visible to the DBMS as C1.

For a cursor to be made updatable, the ODBC Driver imposes a set of syntax rules:

The following code highlights the minimum code required to declare an updatable cursor:

A loop is an iterative set of fetches. Thus, a cursor loop is a set of fetches using cursors. Select loops are a set of fetches without a cursor defined. The ODBC uses select loops by default. This is true whether or not an ODBC DSN definition is specified.

Declaration of a cursor name is the same as a cursor loop in this discussion.

Select loops fetch multiple sets of rows from the DBMS. This is sometimes referred to as block fetching. A single fetch may appear to return only one row, but often the ODBC driver has already fetched many more rows that are cached in the driver.

Cursor loops must be specified if the cursor is scrollable or updatable.

Cursor loops may offer better performance for Windows applications, because the ODBC driver returns information that it supports unlimited active statements. This signifies, for example, that ADO applications can re-use existing connections for internal procedures.

Outside of Windows applications, the performance of cursor loops is often comparable to select loops, because the ODBC driver prefetches rows in blocks of 100 when cursors are used. The term prefetch means that multiple rows are fetched and cached in the ODBC driver before they are presented to the application.

If a cursor is declared as updatable, prefetching does not occur in order to preserve the current position for the update. Thus, updatable cursors may be slower that read-only cursors or select loops.

Only one select loop can be active at a time. As a result, select loops cannot be nested. For example, in ADO, multiple recordset objects cannot be retrieved within [Connection].BeginTrans and [Connection].CommitTrans methods. In direct ODBC code, SQLFreeStmt() must be called with the argument SQL_CLOSE before executing another select loop. By contrast, cursors place no limits on the number of active result sets. Cursor loops can be nested.

After the ODBC application has finished fetching, the cursor associated with the statement handle must be closed. If a cursor was not declared, the ODBC application still must tell the DBMS it has completed fetching.

A cursor is closed, or a select loop completes processing, via the SQLFreeStmt() function, as shown in this example:

SQLFreeStmt() can be called at any time during a fetch operation. When the SQL_CLOSE argument is specified, all resources associated with the fetch are released. The statement handle can be re-used for other types of queries without having to call SQLAllocStmt() or SQLAllocHandle().

The ODBC driver supports scrollable cursors through SQLFetchScroll() and SQLSetPos().

The driver supports static (read-only) and keyset-driven (updatable) cursor types. These cursor types allow the cursor to be positioned in any direction within a result set.

Static and keyset-driven cursors support the position directives described in SQLFetchScroll()--Fetch from a Scrollable Cursor. In contrast, forward-only cursors support only SQL_FETCH_NEXT.

Static cursors fetch rows as they are materialized from the current transaction isolation level. The result set is not updated if other sessions change data that applies to the result set. Static cursors are read-only.

Keyset-driven cursors allow updates to selected records in the result set. Keyset-driven cursors require the target tables to include unique primary keys. If an attempt is made to update or delete records in a result set, and the corresponding records in the target table have been deleted, an error may be returned, depending on the transaction isolation level.

Keyset-driven cursors can be used within a read-only context, but do not perform as well as static cursors.

The ODBC DSN definition or connection string must specify cursor loops, or name a cursor using the SQLSetCursorName() function. Select loops cannot be used with static or keyset-driven cursors.

Keyset-driven cursors require the cursor to be named. The cursor name must be included in the WHERE CURRENT OF clause in the update or delete query.

The SQLFetchScroll() function fetches records in the result set according to the directive specified in the FetchOrientation argument.

Cursor types can be specified in the SQLSetStmtAttr() function and queried by the SQLGetStmtAttr() function.

Use the SQLSetConnectvAttr() function to specify that the ODBC Driver is to be used for scrollable cursor functions.

SQLFetchScroll() positions the cursor according to the specified fetch orientation and then retrieves data.

SQLFetchScroll has the following syntax:

where:

SQLSetPos() allows the ODBC application to scroll the cursor to an absolute position within the result set and perform updates or deletes on the selected record.

SQLSetPos() has the following syntax:

where:

The following code demonstrates the use of static scrollable cursors:

The following code demonstrates keyset-driven cursors:

The SQLGetData() and SQLPutData() functions allow fetching or insertion of data in segments. Any non-atomic data type, such as char or byte varying, can be sent or fetched in segments, but generally segments are used with large objects, sometimes known as "blobs".

The ODBC Driver supports the following large objects:

Although large object locators are supported in Ingres 9.1 and later, the ODBC Driver does not support large object locators, since there is no corresponding support in the ODBC specification.

SQLPutData() sends blob data in segments. The length of the data must be known in advance, and applied to the SQL_LEN_DATA_AT_EXEC macro prior to execution of an insert or update query.

The following example sends a blob 5,000 characters in length to a table containing a long varchar. Each segment is 1,000 characters long.

Note that SQLBindParameter() never directly references the data buffer "blob". Instead, the number 1 is used to indicate that parameter 1 is a variable length parameter. The overall length of the blob is calculated as a negative number into the len argument via the SQL_LEN_DATA_AT_EXEC() macro. The ODBC driver uses these clues to detect that data is to be sent in segments.

The SQLParamData() function tracks the progress of SQLPutData(). If the status return of SQLParamData() is SQL_NEED_DATA, the ODBC driver has not yet finished sending data segments. The pToken argument returns the parameter number of the data to be sent; thus, more than one blob or variable-length datum can be sent in segments.

The above example could have been coded in the traditional way, as shown in this example:

This traditional approach may be acceptable for smaller-length blobs, but would be less practical for large blobs, especially those that consume substantial memory. The ODBC application would need to pre-allocate memory for the entire blob before using it as a query parameter. By contrast, if blobs are sent in segments, only memory for the blob segment needs to be allocated.

Fetching blob segments using SQLGetData() is more straightforward than SQLPutData(). If the previous SQLPutData() example were expanded to fetch the data after insertion, the following code would fetch the 5,000-character blob in 1,000-character segments:

Often, ODBC functions can share status code variables, but in this case, a second status code, "ret" must be declared in addition to "rc". This is because segment fetching uses two fetch loops: one for the fetch itself, and one for retrieving the segments. Each loop needs to track its own status.

There is no counterpart to SQLParamData() when fetching in segments. Instead, SQLGetData() returns a status of SQL_SUCCESS_WITH_INFO when there are more segments to be fetched. If the status of SQLGetData() were further analyzed, the SQLSTATE would have the value 01004 (data truncated). See Error Reporting in the next section for more information on SQLGetData().

The last argument to SQLGetData(), represented by the len variable, indicates the length of the data available in the ODBC driver cache. For the ODBC Driver, it is normal for this argument to contain a larger value than the segment length until the last segment is fetched.

When all of the blob segments are fetched, SQLGetData() returns a status of SQL_SUCCESS.

As with SQLPutData(), the ODBC Driver supports the traditional use of SQLGetData(). The ODBC application could make a single call to SQLGetData() specifying the entire length of the blob:

The ODBC application would need to know that the blob was at least 5,000 characters or less in order for the above example to work; otherwise, the application would truncate the data. Furthermore, the data buffer must be pre-allocated to 5,000 characters. Therefore, this approach may be inefficient for fetching very large blobs.

This section explores how the ODBC Driver and Ingres DBMS handle transactions, and describes ODBC support for data types.

The Ingres DBMS supports standard transaction sessions. Standard transaction sessions begin a transaction when the first query is issued, and end when a commit or rollback command is executed. By contrast, autocommit sessions commit each insert, delete or update query in the DBMS. Standard transaction sessions delete prepared statements and cursor declarations after a commit or rollback; autocommit sessions retain prepared statements and cursor declarations.

The SQLSetConnectAttr() function enables or disables autocommit. The ODBC driver default is to enable autocommit. The following example disables autocommit and manages the transaction manually.

The function SQLEndTran() commits or rolls back the transaction:

The Ingres DBMS places a restriction on cursor declarations during autocommit in that multiple cursors cannot be declared. However, multiple cursors can be declared for standard transaction sessions.

The Ingres restriction has ramifications on the ODBC. The ODBC specification allows multiple cursor declarations regardless of whether autocommit is enabled or not.

For the ODBC Driver to support multiple cursors during autocommit, the driver internally reverts to a state named simulated autocommit. During simulated autocommit, when the ODBC driver detects that a cursor is opened, and an update, insert or delete query is to be executed, the ODBC driver internally disables autocommit. Commits are issued internally from the ODBC driver when:

When all cursors are closed, the ODBC driver changes back to autocommit mode.

Simulated autocommit is not used if no cursors are open or if the only DBMS queries are fetch queries.

The ODBC Driver supports all transaction isolation levels available in the ODBC specification, including:

The above isolation levels are specified by the ODBC attributes SQL_ATTR_TXN_SERIALIZABLE, SQL_ATTR_TXN_READ_COMMITTED, SQL_TXN, READ_UNCOMMITTED, and SQL_TXN_REPEATABLE_READ, respectively. Transaction isolation is specified by SQLSetStmtAttr() function via the SQL_ATTR_TXN_ISOLATION connection attribute. The SQLGetStmtAttr() function returns the current isolation level when the SQL_ATTR_TXN_ISOLATION option is specified.

Other types of transaction isolation supported by Ingres, such as system, are not available in SQLSetStmtAttr(). Use SQLExecDirect() to execute the SET command directly in such cases. This is also the case when locking is specified with the SET LOCKMODE command, such as for row-level locking.

The ODBC driver supports distributed (XA) transactions in the Windows environment using the Microsoft Distributed Transaction Coordinator. See Ingres ODBC and Distributed Transactions (Windows) in this guide for more information.

The ODBC Driver supports all ODBC data types except:

The ODBC function SQLBindParameter() allows coercion from "C" data types to SQL data types. Outside of the above exceptions, the ODBC supports all ODBC data type coercions as described in the "Converting Data from SQL to C Data Types" and "Converting Data from C to SQL Data Types" tables in the Microsoft ODBC Programmer's Reference. The following table summarizes the coercions available:

Prior to Ingres 9.1, the ODBC Driver supported SQL_C_TYPE_DATE, SQL_C_TYPE_TIME, and SQL_C_TYPE_TIMESTAMP for the Ingres "date" data type. In Ingres 9.1 and later, support was added for ISO datetime data types, including:

The ODBC driver supports all ISO data types in addition to the legacy "ingresdate" type. The ODBC driver is sensitive to the connection level of the target database, and thus can work seamlessly against pre-Ingres 9.1 installations.

Support for SQL_C_INTERVAL_YEAR_TO_MONTH SQL_INTERVAL_YEAR_TO_MONTH, SQL_C_INTERVAL_DAY_TO_SECOND, SQL_INTERVAL_DAY_TO_SECOND is in Ingres 9.1 and later.

The "precision" argument for SQLBindParameter() is supported for SQL_C_TYPE_TIMESTAMP in Ingres 9.1 and later, since ISO timestamps can be declared with a precision for fractions of a second.

If a datetime column is bound to a string type such as SQL_C_CHAR or SQL_C_WCHAR, Ingres rules regarding II_DATE_FORMAT apply, just as if the dates were handled from the Terminal Monitor or other Ingres utility.

Ingres rules on II_DATE_FORMAT do not apply if:

The ODBC Driver supports ISO 8601 syntax in drivers released with Ingres 9.1 and later. ISO syntax is commonly referred to as ANSI syntax.

ANSI syntax is enforced as follows:

The Ingres syntax for datetime data types supports many of the format rules in ISO 8601, but extends or departs from the standard in several ways. The INGRESDATE data type is overloaded to represent dates, timestamps, times, and intervals in one type.

The INGRESDATE syntax is enforced as follows:

The special Ingres syntax for dates is: YYYY_MM_DD.

Databases need to recognize a date value that corresponds to "TBD" or "Unknown". Null dates are suitable for this purpose. Ingres supports null dates for both ANSI datetime types and INGRESDATE types. The treatment in ODBC is the same as for other null data components.

Ingres supports a specific type of date value that is called an "empty" date. An empty date is similar to an empty string: the contents of an empty date are non-null, but have no data. In practice, empty dates can perform the same function as null dates. Both indicate the absence of a valid date.

A magic date is a specific date that represents a different value than the date itself. The ODBC uses the magic date 9999-12-31 to represent empty dates, and 9999-12-31 23:59:59 to represent empty timestamps. If Ingres syntax is in effect, any date or timestamp parameter containing the date 9999-12-31 is converted to an empty date. Similarly, if fetching an empty date into SQL_C_TIMESTAMP or SQL_C_DATE, the result is converted to the magic date.

If the ODBC driver is connected to a pre-Ingres 9.1 database, including gateways and EDBC, Ingres syntax is applied for date and time values.

If the ODBC driver is connected to an Ingres 9.1 or later database, ANSI syntax is applied for date and time values.

If both connection attributes DateAlias and SendDateTimeAsIngresDate are left unspecified, the default behavior of the driver may change. The following table shows how DateAlias and SendDateTimeAsIngresDate interact for undefined attributes:

ODBC does not know whether a given column is an ANSI datetime type or an Ingresdate type. This ambiguity can cause problems.

For example, 9999-12-31 is a valid ANSI date. Therefore, if the ODBC driver and the Ingres database support ANSI syntax, applications that insert dates of 9999-12-31 into ingresdate fields will retrieve 9999-12-31, but the result is not an empty date. Instead, this is the actual date 9999-12-31. This is a "false" empty date.

The ODBC Driver supports SQL_C_BIT and SQL_BIT for BOOLEAN data types. Use unsigned char (UCHAR) to define Boolean fields. Also acceptable are char, CHAR, or SCHAR.

The ODBC Driver supports SQL_C_WCHAR, SQL_WCHAR, SQL_WVARCHAR and SQL_WLONGVARCHAR data types for the following Ingres data types:

For databases that do not support Unicode, the ODBC treats Unicode characters as multi-byte (also known as double-byte) for:

Sometimes an ODBC application needs to know information about items in the database, such as tables, permissions, primary keys, and so on. The ODBC Driver supports all ODBC functions pertaining to this information. The following table summarizes the functions available:

All ODBC functions return an error code. Both the ODBC Driver and the Driver Manager cache a list of any errors encountered. The list of errors is deleted when the next ODBC function is called.

A return of SQL_SUCCESS means that the function completed successfully. A return of SQL_ERROR means that an error occurred. A return of SQL_SUCCESS_WITH_INFO can be considered a warning or informational status code. SQL_INVALID_HANDLE means that the handle passed to the function was invalid.

More information on a status of SQL_ERROR or SQL_SUCCESS_WITH_INFO can be retrieved from the SQLGetDiagRec() function. The following example code snippet returns error information on a call to SQLConnect().

If ingPWD was an invalid password, the following errors would be displayed from the above code:

The SQLSTATE reference is specific to ODBC and does not correlate to SQLSTATE in Ingres. The Native Error Code is an Ingres error code, viewable from the errhelp utility:

An ODBC application can simply terminate after executing its queries, but this is considered poor programming practice. The DBMS server cannot distinguish between a program that exited without cleaning up and a program that aborted due to a serious error. As a result, spurious errors are reported in the error log.

To disconnect gracefully from the database, call SQLDisconnect():

The ODBC function SQLFreeStmt() is used to close a cursor or free all resources associated with a statement handle:

A call to SQLFreeStmt() with an argument of SQL_DROP implicitly closes the cursor before freeing resources.

The generic SQLFreeHandle() function can be used with all types of handles:

A call to SQLFreeHandle() with a handle type argument of SQL_HANDLE_STMT is the equivalent of calling SQLFreeStmt() with an argument of SQL_DROP.

Once a handle has been freed, the corresponding allocate function must be re-invoked to initialize resources associated with the handle.

ODBC connection pooling is a method of sharing active database connections with similar or identical connection characteristics. When a connection is released through a call to SQLDisconnect(), the connection is left open and added to a pool of active connections.

When an ODBC application opens a new connection, the application searches the pool for a connection with matching connection characteristics. If a match is found, the connection from the pool is used "under the covers" instead of creating a new connection. If a match is not found, a new connection is opened.

ODBC connection pooling can improve performance significantly because an ODBC application can take a long time to connect relative to the time it takes to process data.

ODBC pooled connections can be shared with single or multi-threaded ODBC applications, but are not shared between separate ODBC applications.

ODBC connection pooling is activated by invoking SQLSetEnvAttr() with the attribute SQL_ATTR_CONNECTION_POOLING, and with the directives SQL_CP_ONE_PER_DRIVER or SQL_CP_ONE_PER_HENV.

SQL_CP_ONE_PER_DRIVER means that there is only one connection pool for the entire ODBC application, regardless of the number of connections. If only one environment handle is allocated, SQL_CP_ONE_PER_DRIVER is essentially the same as SQL_CP_ONE_PER_HENV.

If multiple environment handles are allocated, it may make more sense to specify SQL_CP_ONE_PER_HENV, especially if the connections associated with each environment have similar characteristics. This directive will create multiple pools, each with a smaller number of connections to search through.

The SQLSetEnvAttr() function allows the ODBC application to specify match criteria for objects in the connection pool.

Connection objects with "strict" criteria must have identical connection specifiers for connection attributes specified in the connection string from SQLDriverConnect(). "Strict" is the only option supported, and other specifications are ignored.

"Relaxed" match criteria do not apply to SQLDriverConnect() in the Ingres ODBC CLI. If a relaxed match criterion is specified, only key connection attributes must match. For the ODBC CLI, the following minimum attributes must match, if specified:

The following connection string attributes are ignored for relaxed match criteria:

ODBC pooled connections can be configured to time out to prevent an unmanageable number of connections in the pool.

By default, the Ingres ODBC CLI allows connections to remain in the pool as long as the ODBC application is active. You can override the default connection pool timeout value using the Ingres ODBC Administrator (iiodbcadmin) on Linux.