Oracle Odbc Driver Configuration Fetch Buffer Size

• Oracle Odbc Driver Configuration Fetch Buffer Size For Free
• Odbc Configuration File

Simba Oracle ODBC Driver Release Notes

The default value of JDBC fetch size property is driver specific and for Oracle driver it is 10 indeed. For some queries fetch size should be larger, for some smaller. I think a good idea is to set some global fetch size for whole project and overwrite it for some individual queries where it should be bigger. Oracle ODBC Driver ignores the Fetch Buffer Size and only pre-fetches a set number of rows in the presence of the LONG and LOB data types. See Also: 'Format of the Connection String for the SQLDriverConnect Function'.

The release notes provide details of enhancements, features, and known issuesin Simba Oracle ODBC Driver 1.2.9, as well as the version history.

1.2.9

Released 2019-02-08

Enhancements & New Features

• [ORCL-464] Disable automatic IPD population

  You can now stop the driver from automatically populating the fields ofthe Implementation Parameter Descriptor (IPD). To do this, enable the newDisable Default Auto IPD (or DisableDefaultAutoIPD) property. For moreinformation, see the Installation and Configuration Guide.

Resolved IssuesThe following issue has been resolved in Simba Oracle ODBC Driver 1.2.9.

• [ORCL-438] The driver cannot insert more than 1056 rows into an Oracledata store.

Known IssuesThe following are known issues that you may encounter due to limitations inthe data source, the driver, or an application.

• When converting extremely large values to SQL_FLOAT(9.99999999999999e+125 or greater) the driver might stop responding.

Workflow Changes

The following changes may disrupt workflows from earlier versions.

1.2.7 ————————————————————————

• 32-bit applications no longer supported on macOS

  Beginning with this release, the driver uses Oracle Instant Client 12.2instead of 12.1. Due to platform support differences in the Oracle InstantClient, the macOS version of the driver no longer supports 32-bitapplications.

• Oracle Instant Client upgrade

  Beginning with this release, the driver requires Oracle Instant Client 12.2instead of 12.1. If you installed Oracle Instant Client and the driverthrough separate installation processes, then the Oracle Instant Clientdoes not get updated automatically and the driver may not work as expected.

  To resolve this, make sure to upgrade your installation of Oracle InstantClient. For instructions, see the Installation and Configuration Guide.

Version History

1.2.8 ————————————————————————

Released 2018-12-21

Enhancements & New Features

• [ORCL-413] Kerberos authentication

  You can now authenticate your connection using the Kerberos protocol. To dothis, enable the new Use External Credentials (or UseExternalCredentials)property. The driver then authenticates the connection using the Kerberossettings configured in your sqlnet.ora configuration file. For moreinformation, see the Installation and Configuration Guide.

• [ORCL-416] TCPS protocol

  You can now configure the driver to use the TCPS protocol, which enablesyou to encrypt your connections using SSL/TLS when connecting over TCP/IP.To do this, enable the new Use TCPS (or UseTCPS) property. The driver thenencrypts the connection using the SSL settings configured in yoursqlnet.ora configuration file. For more information, see the Installationand Configuration Guide.

• [ORCL-435] Updated workflow for TNS connections

  When you connect using a connection string or from a non-Windows machine,the TNS Name (or TNS) property now takes precedence over the Host, Port,and Service Name properties (Host, Port, and SVC, respectively).

1.2.7 ————————————————————————

Released 2018-10-12

Enhancements & New Features

• [ORCL-308] Upgrade to Oracle Instant Client 12.2

  The driver now uses version 12.2 of the Oracle Instant Client as adependency. Previously, the driver used version 12.1.

  IMPORTANT:
  • Due to platform support differences in the Oracle Instant Client, themacOS version of the driver no longer supports 32-bit applications.
  • If you installed Oracle Instant Client and the driver through separateinstallation processes, then make sure to manually upgrade Oracle InstantClient to version 12.2. For instructions, see the Installation andConfiguration Guide.

Resolved IssuesThe following issue was resolved in Simba Oracle ODBC Driver 1.2.7.

• [ORCL-433] The TIMESTAMP WITH TIME ZONE data type is not supported.

  This issue has been resolved. The driver is now able to work with theTIMESTAMP WITH TIME ZONE data type from Oracle databases, mapping it tothe SQL_TYPE_TIMESTAMP data type.

1.2.6 ————————————————————————

Released 2018-09-12

Resolved IssuesThe following issues were resolved in Simba Oracle ODBC Driver 1.2.6.

• [ORCL-259] Error messages produced by the driver are not formattedcorrectly.

• [ORCL-427] In some cases the driver returns NULL values when reading largeamounts of rows.

1.2.5 ————————————————————————

Released 2018-07-18

Enhancements & New Features

• [ORCL-381] Error messages enhanced

  When you enter an invalid character or a value outside the acceptablerange for 'Statement Cache Size' or 'Fetch Buffer Size' in the DSN setup,

  the driver now displays an error message that includes more informationabout the values that you entered.

• [ORCL-348] Updated encryption method for driver on Windows

  The driver now uses the DPAPI encryption method to make sure that thecredentials can only be used by the current user (for User DSNs) or usedby all users on the same machine (for System DSNs). The registry contentscannot be used to create the same user DSN or system DSN on anothercomputer. Also, the user DSN registry contents cannot be used to createa system DSN on the same computer.

1.2.4 ————————————————————————

Released 2018-06-05

Enhancements & New Features

• [ORCL-375] Updated log file name

  The log file that the driver generates when logging is enabled has beenrenamed from oracle_driver.log to simbaoracleodbcdriver.log.

• [ORCL-353] Internal Driver Improvements

  The driver has been updated with internal security improvements.

Resolved IssuesThe following issues were resolved in Simba Oracle ODBC Driver 1.2.4.

• [ORCL-366] Driver displays an incorrect alert for data source namevalidation.

  This issue has been resolved. When adding a DSN through the ODBCAdministrator, if you enter invalid characters in the Data Source Namefield, the driver now displays an alert with the correct message andprevents you from using invalid characters.

• [ORCL-362] Driver incorrectly handles SQL queries having argumentof zero length.

  This issue has been resolved. When a SQL query with an argument lengthequal to zero is run, the driver now responds with the correct errormessage such as 'Invalid string or buffer length'.

1.2.3 ————————————————————————

Released 2018-04-25

Enhancements & New Features

• Driver supports statement caching

  You can now cache statements using the driver. Use this feature if youwant to improve driver performance for parsing the same statement multipletimes in the same connection. Make sure that you enable statement cachingonly when required because statement caching requires more memory. For moreinformation, see the Installation and Configuration Guide.

• Upgrade to OpenSSL 1.1.0h

  The driver now uses OpenSSL 1.1.0h for encryption of data.

• MEMLIM option available through the user interface

  The MEMLIM configuration option, which was available only as a key name, isnow accessible via the Windows user interface for the driver. Use the FetchBuffer Size field to configure MEMLIM through the user interface. For moreinformation, see the Installation and Configuration Guide.

1.2.2 ————————————————————————

Released 2018-02-28

Enhancements & New Features

• Support for the Basic Light Oracle Instant Client

  You can now use the Basic Light version of the Oracle Instant Clientpackage. This Basic Light package requires less disk space comparedto the Basic Package.

1.2.1 ————————————————————————

Released 2018-02-09

Resolved IssuesThe following issues were resolved in Simba Oracle ODBC Driver 1.2.1.

• Under some circumstances the driver calls deprecated OCI functions.

• During installation the system message 'Error creating temporary swapfile name' may appear.

• When you link data to your data source in Microsoft Access, an error mayoccur, displaying an error message such as 'Reserved error (-7745); thereis no message for this error.'

1.2.0 ————————————————————————

Released 2017-12-12

Resolved IssuesThe following issue was resolved in Simba Oracle ODBC Driver 1.2.0.

• In some cases, the registry entries or error logs generated by the drivermay contain some sensitive information.

Note:

This appendix provides information related to using Oracle ODBC Driver. It contains the following sections:

E.1 Features Not Supported

Oracle ODBC Driver does not support the following ODBC 3.0 features:

• Interval data types

• SQL_C_UBIGINT and SQL_C_SBIGINT C data type identifiers

• Shared connections

• Shared environments

• The SQL_LOGIN_TIMEOUT attribute of SQLSetConnectAttr

• The expired password option

Oracle ODBC Driver does not support the SQL functions listed in the following table:

E.2 Implementation of Data Types

This section discusses the DATE, TIMESTAMP, and floating point data types.

DATE and TIMESTAMP

The semantics of Oracle DATE and TIMESTAMP data types do not correspond exactly with the ODBC data types with the same names. The Oracle DATE data type contains both date and time information. The SQL_DATE data type contains only date information. The Oracle TIMESTAMP data type also contains date and time information, but it has greater precision in fractional seconds. Oracle ODBC Driver reports the data types of both Oracle DATE and TIMESTAMP columns as SQL_TIMESTAMP to prevent information loss. Similarly, Oracle ODBC Driver binds SQL_TIMESTAMP parameters as Oracle TIMESTAMP values.

See Also:

Floating Point Data Types

When connected to a release 11.1 or later Oracle Database, Oracle ODBC Driver maps the Oracle floating point data types BINARY_FLOAT and BINARY_DOUBLE to the ODBC data types SQL_REAL and SQL_DOUBLE, respectively. In earlier releases, SQL_REAL and SQL_DOUBLE mapped to the generic Oracle numeric data type.

E.3 Limitations on Data Types

Oracle ODBC Driver and Oracle Database impose limitations on data types. The following table describes these limitations:

E.4 Format of the Connection String for the SQLDriverConnect Function

The SQLDriverConnect function is one of the functions implemented by Oracle ODBC Driver. The following table describes the keywords that you can include in the connection string argument of the SQLDriverConnect function call:

E.5 Reducing Lock Timeout in a Program

Oracle Database waits indefinitely for lock conflicts between transactions to be resolved. However, you can limit the amount of time that Oracle Database waits for locks to be resolved. To do this, set the SQL_ATTR_QUERY_TIMEOUT attribute of the ODBC SQLSetStmtAttr function while calling this function before connecting to the data source.

E.6 Linking ODBC Applications

When you link the program, you must link it with the Driver Manager library, libodbc.so.

E.7 Obtaining Information About ROWIDs

The ODBC SQLSpecialColumns function returns information about the columns in a table. When used with Oracle ODBC Driver, it returns information about the Oracle ROWIDs associated with an Oracle table.

E.8 ROWIDs in a WHERE Clause

ROWIDs may be used in the WHERE clause of an SQL statement. However, the ROWID value must be presented in a parameter marker.

E.9 Enabling Result Sets

Oracle reference cursors, also known as result sets, enable an application to retrieve data using stored procedures and stored functions. The following information describes how to use reference cursors to enable result sets through ODBC:

• You must use the ODBC syntax for calling stored procedures. Native PL/SQL is not supported through ODBC. The following code sample identifies how to call the procedure or function without a package and within a package. The package name in this case is RSET.

• The PL/SQL reference cursor parameters are omitted when calling the procedure. For example, assume procedure Example2 is defined to have four parameters. Parameters 1 and 3 are reference cursor parameters and parameters 2 and 4 are character strings. The call is specified as:

The following sample application shows how to return a result set by using Oracle ODBC Driver:

Oracle Odbc Driver Configuration Fetch Buffer Size For Free

E.10 Enabling EXEC Syntax

If the syntax of the SQL Server EXEC statement can be readily translated to an equivalent Oracle procedure call without requiring any change to it, then Oracle ODBC Driver can translate it if you enable this option.

The complete name of a SQL Server procedure consists of up to four identifiers:

• Server name

• Database name

• Owner name

• Procedure name

The format for the name is:

During the migration of Microsoft SQL Server database to Oracle Database, the definition of each SQL Server procedure or function is converted to its equivalent Oracle Database syntax and is defined in a schema in Oracle Database. Migrated procedures are often reorganized (and created in schemas) in one of the following ways:

• All procedures are migrated to one schema (the default option).

• All procedures defined in one SQL Server database are migrated to the schema named with that database name.

• All procedures owned by one user are migrated to the schema named with that user's name.

To support these three ways of organizing migrated procedures, you can specify one of these schema name options for translating procedure names. Object names in the translated Oracle procedure call are not case-sensitive.

E.11 Supported Functionality

This sections provides information about the functionality supported by Oracle ODBC Driver. It contains the following sections:

E.11.1 API Conformance

Oracle ODBC Driver release 10.2.0.1.0 and higher supports all Core, Level 2, and Level1 functions.

E.11.2 Implementation of ODBC API Functions

The following table describes how Oracle ODBC Driver implements specific functions:

SQLProcedures and SQLProcedureColumns

The SQLProcedures and SQLProcedureColumns calls have been modified to locate and return information about all procedures and functions even if they are contained within a package. In earlier releases, the calls only found procedures and functions that were outside of packages. The following examples and scenarios show what procedures or functions are returned if the SQL_ATTR_METADATA_ID attribute is set to SQL_FALSE.

Suppose that you have the following stored procedures:

When you look for % or %%%%%%, you get all eight procedures.

When you look for %_ or _%, you get the following:

When you look for . or .% or %.% or SQLPROC%. or SQLPROC%.%, you get the following:

When you look for %BAR, you get the following:

When you look for .%BAR or %.%BAR, you get the following:

When you look for SQLPROC% or .SQLPROC%, you get the following:

E.11.3 Implementation of the ODBC SQL Syntax

If a comparison predicate has a parameter marker as the second expression in the comparison and the value of that parameter is set to SQL_NULL_DATA with SQLBindParameter, then the comparison fails. This is consistent with the null predicate syntax in ODBC SQL.

E.11.4 Implementation of Data Types

For programmers, the most important part of the implementation of the data types concerns the CHAR, VARCHAR, and VARCHAR2 data types.

For an fSqlType value of SQL_VARCHAR, SQLGetTypeInfo returns the Oracle Database data type VARCHAR2. For an fSqlType value of SQL_CHAR, SQLGetTypeInfo returns the Oracle Database data type CHAR.

E.12 Unicode Support

This section provide information about Unicode support. It contains the following topics:

E.12.1 Unicode Support Within the ODBC Environment

ODBC Driver Manager makes all ODBC drivers, regardless of whether or not they support Unicode, appear as if they are Unicode compliant. This allows ODBC applications to be written independent of the Unicode capabilities of underlying ODBC drivers.

The extent to which the Driver Manager can emulate Unicode support for ANSI ODBC drivers is limited by the conversions possible between the Unicode data and the local code page. Data loss is possible when the Driver Manager is converting from Unicode to the local code page. Full Unicode support is not possible unless the underlying ODBC driver supports Unicode. Oracle ODBC Driver provides full Unicode support.

E.12.2 Unicode Support in ODBC API

The ODBC API supports both Unicode and ANSI entry points using the W and A suffix convention. An ODBC application developer does not need to explicitly call entry points with the suffix. An ODBC application that is compiled with the UNICODE and _UNICODE preprocessor definitions will generate the appropriate calls. For example, a call to SQLPrepare will be compiled as SQLPrepareW.

E.1 Features Not Supported

Oracle ODBC Driver does not support the following ODBC 3.0 features:

• Interval data types

• SQL_C_UBIGINT and SQL_C_SBIGINT C data type identifiers

• Shared connections

• Shared environments

• The SQL_LOGIN_TIMEOUT attribute of SQLSetConnectAttr

• The expired password option

Oracle ODBC Driver does not support the SQL functions listed in the following table:

E.2 Implementation of Data Types

This section discusses the DATE, TIMESTAMP, and floating point data types.

DATE and TIMESTAMP

The semantics of Oracle DATE and TIMESTAMP data types do not correspond exactly with the ODBC data types with the same names. The Oracle DATE data type contains both date and time information. The SQL_DATE data type contains only date information. The Oracle TIMESTAMP data type also contains date and time information, but it has greater precision in fractional seconds. Oracle ODBC Driver reports the data types of both Oracle DATE and TIMESTAMP columns as SQL_TIMESTAMP to prevent information loss. Similarly, Oracle ODBC Driver binds SQL_TIMESTAMP parameters as Oracle TIMESTAMP values.

See Also:

Floating Point Data Types

When connected to a release 11.1 or later Oracle Database, Oracle ODBC Driver maps the Oracle floating point data types BINARY_FLOAT and BINARY_DOUBLE to the ODBC data types SQL_REAL and SQL_DOUBLE, respectively. In earlier releases, SQL_REAL and SQL_DOUBLE mapped to the generic Oracle numeric data type.

E.3 Limitations on Data Types

Oracle ODBC Driver and Oracle Database impose limitations on data types. The following table describes these limitations:

E.4 Format of the Connection String for the SQLDriverConnect Function

The SQLDriverConnect function is one of the functions implemented by Oracle ODBC Driver. The following table describes the keywords that you can include in the connection string argument of the SQLDriverConnect function call:

E.5 Reducing Lock Timeout in a Program

Oracle Database waits indefinitely for lock conflicts between transactions to be resolved. However, you can limit the amount of time that Oracle Database waits for locks to be resolved. To do this, set the SQL_ATTR_QUERY_TIMEOUT attribute of the ODBC SQLSetStmtAttr function while calling this function before connecting to the data source.

E.6 Linking ODBC Applications

When you link the program, you must link it with the Driver Manager library, libodbc.so.

E.7 Obtaining Information About ROWIDs

The ODBC SQLSpecialColumns function returns information about the columns in a table. When used with Oracle ODBC Driver, it returns information about the Oracle ROWIDs associated with an Oracle table.

E.8 ROWIDs in a WHERE Clause

ROWIDs may be used in the WHERE clause of an SQL statement. However, the ROWID value must be presented in a parameter marker.

E.9 Enabling Result Sets

Oracle reference cursors, also known as result sets, enable an application to retrieve data using stored procedures and stored functions. The following information describes how to use reference cursors to enable result sets through ODBC:

• You must use the ODBC syntax for calling stored procedures. Native PL/SQL is not supported through ODBC. The following code sample identifies how to call the procedure or function without a package and within a package. The package name in this case is RSET.

• The PL/SQL reference cursor parameters are omitted when calling the procedure. For example, assume procedure Example2 is defined to have four parameters. Parameters 1 and 3 are reference cursor parameters and parameters 2 and 4 are character strings. The call is specified as:

The following sample application shows how to return a result set by using Oracle ODBC Driver:

Oracle Odbc Driver Configuration Fetch Buffer Size For Free

E.10 Enabling EXEC Syntax

If the syntax of the SQL Server EXEC statement can be readily translated to an equivalent Oracle procedure call without requiring any change to it, then Oracle ODBC Driver can translate it if you enable this option.

The complete name of a SQL Server procedure consists of up to four identifiers:

• Server name

• Database name

• Owner name

• Procedure name

The format for the name is:

During the migration of Microsoft SQL Server database to Oracle Database, the definition of each SQL Server procedure or function is converted to its equivalent Oracle Database syntax and is defined in a schema in Oracle Database. Migrated procedures are often reorganized (and created in schemas) in one of the following ways:

• All procedures are migrated to one schema (the default option).

• All procedures defined in one SQL Server database are migrated to the schema named with that database name.

• All procedures owned by one user are migrated to the schema named with that user's name.

To support these three ways of organizing migrated procedures, you can specify one of these schema name options for translating procedure names. Object names in the translated Oracle procedure call are not case-sensitive.

E.11 Supported Functionality

This sections provides information about the functionality supported by Oracle ODBC Driver. It contains the following sections:

E.11.1 API Conformance

Oracle ODBC Driver release 10.2.0.1.0 and higher supports all Core, Level 2, and Level1 functions.

E.11.2 Implementation of ODBC API Functions

The following table describes how Oracle ODBC Driver implements specific functions:

SQLProcedures and SQLProcedureColumns

The SQLProcedures and SQLProcedureColumns calls have been modified to locate and return information about all procedures and functions even if they are contained within a package. In earlier releases, the calls only found procedures and functions that were outside of packages. The following examples and scenarios show what procedures or functions are returned if the SQL_ATTR_METADATA_ID attribute is set to SQL_FALSE.

Suppose that you have the following stored procedures:

When you look for % or %%%%%%, you get all eight procedures.

When you look for %_ or _%, you get the following:

When you look for . or .% or %.% or SQLPROC%. or SQLPROC%.%, you get the following:

When you look for %BAR, you get the following:

When you look for .%BAR or %.%BAR, you get the following:

When you look for SQLPROC% or .SQLPROC%, you get the following:

E.11.3 Implementation of the ODBC SQL Syntax

If a comparison predicate has a parameter marker as the second expression in the comparison and the value of that parameter is set to SQL_NULL_DATA with SQLBindParameter, then the comparison fails. This is consistent with the null predicate syntax in ODBC SQL.

E.11.4 Implementation of Data Types

For programmers, the most important part of the implementation of the data types concerns the CHAR, VARCHAR, and VARCHAR2 data types.

For an fSqlType value of SQL_VARCHAR, SQLGetTypeInfo returns the Oracle Database data type VARCHAR2. For an fSqlType value of SQL_CHAR, SQLGetTypeInfo returns the Oracle Database data type CHAR.

E.12 Unicode Support

This section provide information about Unicode support. It contains the following topics:

E.12.1 Unicode Support Within the ODBC Environment

ODBC Driver Manager makes all ODBC drivers, regardless of whether or not they support Unicode, appear as if they are Unicode compliant. This allows ODBC applications to be written independent of the Unicode capabilities of underlying ODBC drivers.

The extent to which the Driver Manager can emulate Unicode support for ANSI ODBC drivers is limited by the conversions possible between the Unicode data and the local code page. Data loss is possible when the Driver Manager is converting from Unicode to the local code page. Full Unicode support is not possible unless the underlying ODBC driver supports Unicode. Oracle ODBC Driver provides full Unicode support.

E.12.2 Unicode Support in ODBC API

The ODBC API supports both Unicode and ANSI entry points using the W and A suffix convention. An ODBC application developer does not need to explicitly call entry points with the suffix. An ODBC application that is compiled with the UNICODE and _UNICODE preprocessor definitions will generate the appropriate calls. For example, a call to SQLPrepare will be compiled as SQLPrepareW.

The C data type, SQL_C_WCHAR, was added to the ODBC interface to allow applications to specify that an input parameter is encoded as Unicode or to request column data returned as Unicode. The macro SQL_C_TCHAR is useful for applications that need to be built as both Unicode and ANSI. The SQL_C_TCHAR macro compiles as SQL_C_WCHAR for Unicode applications and as SQL_C_CHAR for ANSI applications.

The SQL data types, SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR, have been added to the ODBC interface to represent columns defined in a table as Unicode. Potentially, these values are returned from calls to SQLDescribeCol, SQLColAttribute, SQLColumns, and SQLProcedureColumns.

Unicode encoding is supported for SQL column types NCHAR, NVARCHAR2, and NCLOB. In addition, Unicode encoding is also supported for SQL column types CHAR and VARCHAR2 if the character semantics are specified in the column definition.

Oracle ODBC Driver supports these SQL column types and maps them to ODBC SQL data types. The following table lists the supported SQL data types and the equivalent ODBC SQL data type:

Odbc Configuration File

E.12.3 SQLGetData Performance

The SQLGetData function allows an ODBC application to specify the data type to receive a column as after the data has been fetched. OCI requires Oracle ODBC Driver to specify the data type before it is fetched. In this case, Oracle ODBC Driver uses information about the data type of the column (as defined in the database) to determine how to best default to fetching the column through OCI.

If a column that contains character data is not bound by SQLBindCol, then Oracle ODBC Driver must determine if it should fetch the column as Unicode or as the local code page. The driver could always default to receiving the column as Unicode. However, this may result in as many as two unnecessary conversions. For example, if the data were encoded in the database as ANSI, then there would be an ANSI to Unicode conversion to fetch the data into Oracle ODBC Driver. If the ODBC application then requested the data as SQL_C_CHAR, then there would be an additional conversion to revert the data to its original encoding.

The default encoding of Oracle Database Client is used when fetching data. However, an ODBC application may overwrite this default and fetch the data as Unicode by binding the column or the parameter as the WCHAR data type.

E.12.4 Unicode Samples

Because Oracle ODBC Driver itself was implemented using TCHAR macros, it is recommended that ODBC application programs use TCHAR in order to take advantage of the driver.

The following examples show how to use TCHAR, which becomes the WCHAR data type if you compile with UNICODE and _UNICODE:

Example E-1 Connection to Database

To use this code, you only need to specify the Unicode literals for SQLConnect.

Example E-2 Simple Retrieval

The following example retrieves the employee names and the job tiles from the EMP table. With the exception that you must specify TCHAR compliant data to every ODBC function, there is no difference to the ANSI case. If the case is a Unicode application, then you must specify the length of the buffer to the BYTE length when you call SQLBindCol. For example, sizeof(ename).

Example E-3 Retrieval Using SQLGetData (Binding After Fetch)

This example shows how to use SQLGetData. There is no difference to the ANSI application in terms of Unicode-specific issues.

Example E-4 Simple Update

This example shows how to update data. The length of data for SQLBindParameter has to be specified with the BYTE length, even in the case of a Unicode application.

Example E-5 Update and Retrieval for Long Data (CLOB)

This example may be the most complicated case to update and retrieve data for long data, like CLOB, in Oracle Database. Because the length of data should always be the BYTE length, the expression lstrlen(TCHAR data)*sizeof(TCHAR) is needed to derive the BYTE length.

E.13 Performance and Tuning

This section contains the following topics:

E.13.1 General ODBC Programming Guidelines

Apply the following programming guidelines to improve the performance of an ODBC application:

• Enable connection pooling if the application will frequently connect and disconnect from a data source. Reusing pooled connections is extremely efficient compared to reestablishing a connection.

• Minimize the number of times a statement must be prepared. Where possible, use bind parameters to make a statement reusable for different parameter values. Preparing a statement once and running it several times is much more efficient than preparing the statement for every SQLExecute.

• Do not include columns in a SELECT statement of which you know the application will not retrieve; especially LONG columns. Because of the nature of the database server protocols, Oracle ODBC Driver must fetch the entire contents of a LONG column if it is included in the SELECT statement, regardless of whether the application binds the column or performs a SQLGetData operation.

• If you are performing transactions that do not update the data source, then set the SQL_ATTR_ACCESS_MODE attribute of the ODBC SQLSetConnectAttr function to SQL_MODE_READ_ONLY.

• If you are not using ODBC escape clauses, then set the SQL_ATTR_NOSCAN attribute of the ODBC SQLSetConnectAttr function or the ODBC SQLSetStmtAttr function to true.

• Use the ODBC SQLFetchScroll function instead of the ODBC SQLFetch function for retrieving data from tables that have a large number of rows.

E.13.2 Data Source Configuration Options

This section discusses the performance implications of the following ODBC data source configuration options:

• Enable Result Sets

  This option enables the support of returning result sets (for example, RefCursor) from procedure calls. The default is enabling the returning of result sets.

  Oracle ODBC Driver must query the database server to determine the set of parameters for a procedure and their data types in order to determine if there are any RefCursor parameters. This query incurs an additional network round trip the first time any procedure is prepared and executed.

• Enable LOBs

  This option enables the support of inserting and updating LOBs. The default is enabled.

  Oracle ODBC Driver must query the database server to determine the data types of each parameter in an INSERT or UPDATE statement to determine if there are any LOB parameters. This query incurs an additional network round trip the first time any INSERT or UPDATE is prepared and run.

  See Also:

  Oracle Database SecureFiles and Large Objects Developer's Guide for more information on LOBs

  Note:

  LOB data compression enables you to compress SecureFiles to gain disk, Input-Output, and redo logging savings. This reduces costs as compression utilizes space most efficiently and improves the performance of SecureFiles as compression reduces Input-Output and redo logging.

  LOB data encryption provides enhanced database security. While the encrypted data is available for random reads and writes, the data is more secure.

  Data compression and encryption consumes some additional memory.

• Bind TIMESTAMP as DATE

  Binds SQL_TIMESTAMP parameters as the appropriate Oracle Database data type. If this option is set to TRUE, then SQL_TIMESTAMP binds as the Oracle DATE data type. If this option is set to FALSE, then SQL_TIMESTAMP binds as the Oracle TIMESTAMP data type, which is the default.

• Enable Closing Cursors

  The SQL_CLOSE option of the ODBC function, SQLFreeStmt, is supposed to close associated cursors with a statement and discard all pending results. The application can reopen the cursor by running the statement again without doing a SQLPrepare again. A typical scenario for this would be an application that expects to be idle for a while but will reuse the same SQL statement again. While the application is idle, it may want to free up any associated server resources.

  The OCI, on which Oracle ODBC Driver is layered, does not support the functionality of closing cursors. Therefore, by default, the SQL_CLOSE option has no effect in Oracle ODBC Driver. The cursor and associated resources remain open on the database.

  Enabling this option causes the associated cursor to be closed on the database server. However, this results in the parse context of the SQL statement being lost. The ODBC application can run the statement again without calling SQLPrepare. However, internally, Oracle ODBC Driver must prepare and run the statement all over. Enabling this option has a severe performance impact on applications that prepare a statement once and run it repeatedly.

  This option should only be enabled if freeing the resources on the server is necessary.

• Fetch Buffer Size

  Set the Fetch Buffer Size (FetchBufferSize) in the odbc.ini file to a value specified in bytes. This value is the amount of memory needed that will determine how many rows of data Oracle ODBC Driver will pre-fetch at a time from an Oracle Database to the client's cache regardless of the number of rows the application program requests in a single query, thus improving performance.

  There will be an improvement in the response time of applications that typically fetch fewer than 20 rows of data at a time, particularly over slow network connections or from heavily loaded servers. Setting this too high can have an adverse effect on response time or consume large amounts of memory. The default is 64,000 bytes. You should choose an optimal value for the application.

  When the LONG and LOB data types are present, the number of rows pre-fetched by Oracle ODBC Driver is not determined by the Fetch Buffer Size. The inclusion of the LONG and LOB data types minimizes the performance improvement and could result in excessive memory use. Oracle ODBC Driver ignores the Fetch Buffer Size and only pre-fetches a set number of rows in the presence of the LONG and LOB data types.

See Also:

E.13.3 DATE and TIMESTAMP Data Types

If a DATE column in the database is used in a WHERE clause and the column has an index, then there can be an impact on performance. For example:

In this example, an index on the HIREDATE column could be used to make the query run quickly. However, because HIREDATE is a DATE value and Oracle ODBC Driver is supplying the parameter value as TIMESTAMP, the query optimizer of Oracle Database must apply a conversion function. To prevent incorrect results (as might happen if the parameter value had nonzero fractional seconds), the optimizer applies the conversion to the HIREDATE column resulting in the following statement:

However, this has the effect of disabling the use of the index on the HIREDATE column. Instead, the server performs a sequential scan of the table. If the table has many rows, then this can take a long time. As a workaround for this situation, Oracle ODBC Driver has the connection option to bind TIMESTAMP as DATE. When this option is enabled, Oracle ODBC Driver binds SQL_TIMESTAMP parameters as the Oracle DATE data type instead of the Oracle TIMESTAMP data type. This enables the query optimizer to use any index on the DATE columns.

Note:

E.14 Error Messages

When an error occurs, Oracle ODBC Driver returns the native error number, the SQLSTATE (an ODBC error code), and an error message. The driver derives this information both from errors detected by the driver and errors returned by Oracle Database.

Native Error

For errors that occur in the data source, Oracle ODBC Driver returns the native error returned to it by Oracle Database. When Oracle ODBC Driver or the Driver Manager detects an error, Oracle ODBC Driver returns a native error of zero.

SQLSTATE

For errors that occur in the data source, Oracle ODBC Driver maps the returned native error to the appropriate SQLSTATE. When Oracle ODBC Driver or the Driver Manager detects an error, it generates the appropriate SQLSTATE.

Error Message

For errors that occur in the data source, Oracle ODBC Driver returns an error message based on the message returned by Oracle Database. For errors that occur in Oracle ODBC Driver or the Driver Manager, Oracle ODBC Driver returns an error message based on the text associated with the SQLSTATE.

Error messages have the following format:

The prefixes in brackets ([ ]) identify the source of the error. The following table shows the values of these prefixes returned by Oracle ODBC Driver. When the error occurs in the data source, the vendor and ODBC-component prefixes identify the vendor and name of the ODBC component that received the error from the data source.

For example, if the error message does not contain the Ora prefix shown in the following format, then error is an Oracle ODBC Driver error and should be self-explanatory.

If the error message contains the Ora prefix shown in the following format, then it is not an Oracle ODBC Driver error.

Note:

If the error message text starts with the ORA- prefix, then you can obtain more information about the error in Oracle Database documentation.