Syntax Driver

Why syntax is “driver.manage.window.maximize;”? Posted on: April 7, 2017 Last updated on: April 23, 2017 Comments: 37 Categorized in: Selenium Topics Written by: Amod Mahajan Last updated on April 23rd, 2017 at 03:28 pm.
JDBC drivers implement the defined interfaces in the JDBC API, for interacting with your database server. For example, using JDBC drivers enable you to open database connections and to interact with it by sending SQL or database commands then receiving results with Java. The Java.sql package that.
To display information for drivers only, type: sc.exe query type= driver To display information for drivers in the Network Driver Interface Specification (NDIS) group, type: sc.exe query type= driver group= NDIS Additional References. Command-Line Syntax Key.

Jdbc Driver Syntax
Syntax Of Loading Drivermanager In Java
Syntax Gecko Driver
Syntax For Drivermanager.getconnection

A 'Driver class' is often just the class that contains a main. In a real project, you may often have numerous 'Driver classes' for testing and whatnot, or you can build a main into any of your objects and select the runnable class through your IDE, or by simply specifying 'java classname.'

Oracle8i JDBC Developer's Guide and Reference
Release 8.1.5
A64685-01

Library

Product

Contents

Index

First Steps in JDBC

This section describes how to get up and running with the Oracle JDBC drivers. When using the Oracle JDBC drivers, you must include certain driver-specific information in your programs. This section describes, in the form of a tutorial, where and how to add the information. The tutorial guides you through creating code to connect to and query a database from the client.

To connect to and query a database from the client, you must provide code for these tasks:

Importing Packages
Registering the JDBC Drivers
Opening a Connection to a Database
Creating a Statement Object
Executing a Query and Returning a Result Set Object
Processing the Result Set
Closing the Result Set and Statement Objects
Closing the Connection

You must supply Oracle driver-specific information for the first three tasks, which allow your program to use the JDBC API to access a database. For the other tasks, you can use standard JDBC Java code as you would for any Java application.

Importing Packages

Regardless of which Oracle JDBC driver you use, you must include the following import statements at the beginning of your program.

You will need to add the following Oracle packages to your program when you want to access the extended functionality provided by the Oracle drivers. However, they are not required for the example presented in this section:

oracle.jdbc.driver.* and oracle.sql.*

Add these packages if you use any Oracle-specific extensions to JDBC in your program. For more information on Oracle extensions, see Chapter 4, 'Oracle Extensions'.

Registering the JDBC Drivers

You must provide the code to register your installed driver with your program. You do this with the static registerDriver() method of the JDBC DriverManager class. This class provides a basic service for managing a set of JDBC drivers.

Note:

Alternatively, you can use the forName() method of the java.lang.Class class to load the JDBC drivers directly. For example:

Class.forName ('oracle.jdbc.driver.OracleDriver');

However, this method is valid only for JDK-compliant Java virtual machines. It is not valid for Microsoft Java virtual machines.

Because you are using one of Oracle's JDBC drivers, you declare a specific driver name string to registerDriver(). You register the driver only once in your Java application.

Note:

If you are registering a Thin driver in an applet, you must enter a driver string that is different from the one used in these examples. For more information on registering a Thin driver for an applet, see 'Coding Applets'.

Opening a Connection to a Database

You open a connection to the database with the static getConnection() method of the JDBC DriverManager class. This method returns an object of the JDBC Connection class which needs as input a userid, password, connect string that identifies the JDBC driver to use, and the name of the database to which you want to connect.

Connecting to a database is a step where you must enter Oracle JDBC driver-specific information in the getConnection() method. If you are not familiar with this method, continue reading the 'Understanding the Forms of getConnection()' section below.

If you are already familiar with the getConnection() method, you can skip ahead to either of these sections, depending on the driver you installed:

'Opening a Connection for the JDBC OCI Driver'
'Opening a Connection for the JDBC Thin Driver'
Note:
The instructions in this section are specific to the client-side drivers only. To find out how to open a database connection using the server-side driver, see 'Server-Side Basics'.

Understanding the Forms of getConnection()

The getConnection() method is an overloaded method that you declare by the techniques described in these sections:

'Specifying a Database URL, Userid, and Password'
'Specifying a Database URL That Includes Userid and Password'
'Specifying a Database URL and Properties Object'
Note:
You do not have to specify the database name if there is a default connection. For more information about default connections, see 'Connecting to the Database with the Server Driver'.

Jdbc Driver Syntax

If you want to specify a database name in the connection, it must be in one of the following formats:

a Net8 keyword-value pair
a string of the form <host_name>:<port_number>:<sid> (Thin driver only)
a TNSNAMES entry (OCI driver only)

For information on how to specify a keyword-value pair or a TNSNAMES entry, see yourNet8 Administrator's Guide.

Specifying a Database URL, Userid, and Password

where the URL is of the form:

The following example connects user scott with password tiger to a database with SID orcl through port 1521 of host myhost, using the Thin driver.

If you want to use the default connection for an OCI driver, specify either:

For all JDBC drivers you can also specify the database with a Net8 keyword-value pair. The Net8 keyword-value pair substitutes for the TNSNAMES entry. The following example uses the same parameters as the preceding example, but in the keyword-value format:

Specifying a Database URL That Includes Userid and Password

where the URL is of the form:

The following example connects user scott with password tiger to a database using the OCI driver. In this case, however, the URL includes the userid and password, and is the only input parameter.

Specifying a Database URL and Properties Object

where the URL is of the form:

In addition to the URL, use an object of the standard Java Properties class as input. For example:

Oracle Extensions to Connection Properties Object

Oracle has defined several extensions to the connection properties that Oracle JDBC drivers support. For more information on this form of the getConnection() method and the Oracle extensions to the Properties object, see 'Oracle Extensions for Connection Properties'.

Opening a Connection for the JDBC OCI Driver

For the JDBC OCI driver, you can specify the database by a TNSNAMES entry. You can find the available TNSNAMES entries listed in the file tnsnames.ora on the client computer from which you are connecting. On Windows NT this file is located in [ORACLE_HOME]NETWORKADMIN. On UNIX systems, you can find it in /var/opt/oracle.

For example, if you want to connect to the database on host myhost as user scott with password tiger that has a TNSNAMES entry of MyHostString, enter:

Note that both the ':' and '@' characters are necessary.

For the JDBC OCI driver (as with the Thin driver), you can also specify the database with a Net8 keyword-value pair. This is less readable than a TNSNAMES entry but does not depend on the accuracy of the TNSNAMES.ORA file. The Net8 keyword-value pair also works with other JDBC drivers.

For example, if you want to connect to the database on host myhost that has a TCP/IP listener up on port 1521, and the SID (system identifier) is orcl, use a statement such as:

Opening a Connection for the JDBC Thin Driver

Because you can use the JDBC Thin driver in applets that do not depend on an Oracle client installation, you cannot use a TNSNAMES entry to identify the database to which you want to connect. You have to either:

explicitly list the host name, TCP/IP port and Oracle SID of the database to which you want to connect

use a keyword-value pair list
Note:
The JDBC Thin driver supports only the TCP/IP protocol.

For example, use this string if you want to connect to the database on host myhost that has a TCP/IP listener on port 1521 for the database SID (system identifier) orcl. You can logon as user scott, with password tiger:

You can also specify the database with a Net8 keyword-value pair. This is less readable than the first version, but also works with the other JDBC drivers.

('jdbc:oracle:thin:@(description=(address=(host=myhost)(protocol=tcp)

Syntax Of Loading Drivermanager In Java

Note:

If you are writing a connection statement for an applet, you must enter a connect string that is different from the one used in these examples. For more information on connecting to a database with an applet, see 'Coding Applets'.

Creating a Statement Object

Once you connect to the database and, in the process, create your Connection object, the next step is to create a Statement object. The createStatement() method of your JDBC Connection object returns an object of the JDBC Statement class. To continue the example from the previous section where the Connection object conn was created, here is an example of how to create the Statement object:

Note that there is nothing Oracle-specific about this statement; it follows standard JDBC syntax.

Executing a Query and Returning a Result Set Object

To query the database, use the executeQuery() method of your Statement object. This method takes a SQL statement as input and returns an object of the JDBC ResultSet class.

To continue the example, once you create the Statement object stmt, the next step is to execute a query that populates a ResultSet object with the contents of the ENAME (employee name) column of a table of employees that is named EMP:

Again, there is nothing Oracle-specific about this statement; it follows standard JDBC syntax.

Note:

The JDBC drivers actually return an OracleResultSet object, but into a standard ResultSet output variable. If you want to use Oracle extensions to process the result set, then you must cast the output to OracleResultSet. This is further discussed in 'Classes of the oracle.jdbc.driver Package'.

Processing the Result Set

Once you execute your query, use the next() method of your ResultSet object to iterate through the results. This method loops through the result set row by row, detecting the end of the result set when it is reached.

To pull data out of the result set as you iterate through it, use the various getXXX() methods of the ResultSet object, where XXX corresponds to a Java datatype.

For example, the following code will iterate through the ResultSet object rset from the previous section, and will retrieve and print each employee name:

Once again, this is standard JDBC syntax. The next() method returns false when it reaches the end of the result set. The employee names are materialized as Java Strings.

Closing the Result Set and Statement Objects

You must explicitly close the ResultSet and Statement objects after you finish using them. This applies to all ResultSet and Statement objects you create when using the Oracle JDBC drivers. The drivers do not have finalizer methods; cleanup routines are performed by the close() method of the ResultSet and Statement classes. If you do not explicitly close your ResultSet and Statement objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.

For example, if your ResultSet object is rset and your Statement object is stmt, close the result set and statement with these lines:

When you close a Statement object that a given Connection object creates, the connection itself remains open.

Closing the Connection

You must close your connection to the database once you finish your work. Use the close() method of the Connection class to do this. For example, if your Connection object is conn, close the connection with this statement:

Top

Library

Product

Contents

Index

Note:

Oracle ODBC Driver is supported only for the Linux x86, Linux Itanium, and Solaris SPARC 64 platforms.

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

G.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.

String Functions	Numeric Functions	Time, Date, and Interval Functions
`BIT_LENGTH`	`ACOS`	`CURRENT_DATE`
`CHAR_LENGTH`	`ASIN`	`CURRENT_TIME`
`CHARACTER_LENGTH`	`ATAN`	`CURRENT_TIMESTAMP`
`DIFFERENCE`	`ATAN2`	`EXTRACT`
`OCTET_LENGTH`	`COT`	`TIMESTAMPDIFF`
`POSITION`	`DEGREES` `RADIANS` `RAND` `ROUND`

G.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.

G.3 Limitations on Data Types

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

Limited Data Type	Description
Literals	Oracle Database limits literals in SQL statements to 4000 bytes.
`SQL_LONGVARCHAR` and `SQL_WLONGVARCHAR`	The Oracle limit for `SQL_LONGVARCHAR` data, where the column type is `LONG`, is 2,147,483,647 bytes. The Oracle limit for `SQL_LONGVARCHAR` data, where the column type is `CLOB`, is 4 gigabytes. The limiting factor is the client workstation memory.
`SQL_LONGVARCHAR` and `SQL_LONGVARBINARY`	Oracle Database permits only a single long data column in each table. The long data types are `SQL_LONGVARCHAR` (`LONG`) and `SQL_LONGVARBINARY` (`LONG RAW`). Oracle recommends that you use `CLOB` and `BLOB` columns instead. There is no restriction on the number of `CLOB` and `BLOB` columns in a table.

G.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.

Keyword	Meaning	Value
`DSN`	ODBC data source name	User-supplied name This is a mandatory keyword.
`DBQ`	TNS service name	User-supplied name This is a mandatory keyword.
`UID`	User ID or user name	User-supplied name This is a mandatory keyword.
`PWD`	Password	User-supplied name Specify `PWD=;` for an empty password. This is a mandatory keyword.
`DBA`	Database attribute	`W` implies write access `R` implies read-only access
`APA`	Applications attributes	`T` implies that thread safety is to be enabled `F` implies that thread safety is to be disabled
`RST`	Result sets	`T` implies that result sets are to be enabled. `F` implies that result sets are to be disabled.
`QTO`	Query timeout option	`T` implies that query timeout is to be enabled. `F` implies that query timeout is to be disabled.
`CSR`	Close cursor	`T` implies that close cursor is to be enabled. `F` implies that close cursor is to be disabled.
`BAM`	Batch autocommit mode	`IfAllSuccessful` implies commit only if all statements are successful (old behavior). `UpToFirstFailure` implies commit up to first failing statement. This is ODBC version 7 behavior. `AllSuccessful` implies commit all successful statements.
`FBS`	Fetch buffer size	User-supplied numeric value (specify a value in bytes of 0 or greater).The default is 60,000 bytes.
`FEN`	Failover	`T` implies failover is to be enabled. `F` implies failover is to be disabled.
`FRC`	Failover retry count	User-supplied numeric value. The default is 10.
`FDL`	Failover delay	User-supplied numeric value. The default is 10.
`LOB`	LOB writes	`T` implies LOBs are to be enabled. `F` implies LOBs are to be disabled.
`FWC`	Force `SQL_WCHAR` support	`T` implies `Force SQL_WCHAR` is to be enabled. `F` implies `Force SQL_WCHAR` is to be disabled.
`EXC`	`EXEC` syntax	`T` implies `EXEC` Syntax is to be enabled. `F` implies `EXEC` Syntax is to be disabled.
`XSM`	Schema field	`Default` implies that the default value is to be used. `Database` implies that the Database Name is to be used. `Owner` implies that the name of the owner is to be used.
`MDI`	Set metadata ID default	T implies that the default value of `SQL_ATTR_METADATA_ID` is `SQL_TRUE`. `F` implies that the default value of `SQL_ATTR_METADATA_ID` is `SQL_FALSE`.
`DPM`	Disable `SQLDescribeParam`	`T` implies that `SQLDescribeParam` is to be disabled. `F` implies that `SQLDescribeParam` is to be enabled.
`BTD`	Bind `TIMESTAMP` as `DATE`	T implies that `SQL_TIMESTAMP` is to be bound as Oracle `DATE`. `F` implies that `SQL_TIMESTAMP` is to be bound as Oracle `TIMESTAMP`.
`NUM`	Numeric settings	`NLS` implies that the Globalization Support numeric settings are to be used (to determine the decimal and group separator).

G.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.

G.6 Linking ODBC Applications

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

G.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.

G.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.

G.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:

G.10 Enabling EXEC Syntax

If the syntax of your 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.

G.11 Supported Functionality

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

G.11.1 API Conformance

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

G.11.2 Implementation of ODBC API Functions

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

Function	Description
`SQLConnect`	`SQLConnect` requires only a `DBQ`, user ID, and password.
`SQLDriverConnect`	`SQLDriverConnect` uses the `DSN`, `DBQ`, `UID`, and `PWD` keywords.
`SQLSpecialColumns`	If `SQLSpecialColumns` is called with the `SQL_BEST_ROWID` attribute, it always returns the ROWID column.
`SQLProcedures` and `SQLProcedureColumns`	Refer to the information in the following row.
All catalog functions	If the `SQL_ATTR_METADATA_ID` statement attribute is set to `SQL_TRUE`, then a string argument is treated as an identifier argument, and its case is not significant. In this case, the underscore (_) and the percent sign (%) are treated as the actual character, and not as a search pattern character. In contrast, if this attribute is set to `SQL_FALSE`, then it is either an ordinary argument or a pattern value argument and is treated literally, and its case is significant.

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:

G.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.

G.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.

G.12 Unicode Support

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

G.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.

G.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.

Syntax Gecko Driver

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.

SQL Data Types	ODBC SQL Data Types
`CHAR`	`SQL_CHAR` or `SQL_WCHAR`
`VARCHAR2`	`SQL_VARCHAR` or `SQL_WVARCHAR`
`NCHAR`	`SQL_WCHAR`
`NVARCHAR2`	`SQL_WVARCHAR`
`NCLOB`	`SQL_WLONGVARCHAR`

G.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, 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, there would be an additional conversion to revert the data back 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.

G.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 G-1 Connection to Database

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

Example G-2 Simple Retrieval

The following example retrieves the employee names and the job tiles from the EMP table. With the exception that you need to 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 have to specify the length of the buffer to the BYTE length when you call SQLBindCol. For example, sizeof(ename).

Example G-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 G-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 G-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.

G.13 Performance and Tuning

This section contains the following topics:

G.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.

G.13.2 Data Source Configuration Options

Syntax For Drivermanager.getconnection

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.
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 your 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.

G.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:

This option is intended only for use with Microsoft Access or other similar programs that bind DATE columns as TIMESTAMP columns. It should not be used when there are actual TIMESTAMP columns present or when data loss may occur. Microsoft Access runs such queries using whatever columns are selected as the primary key.

G.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.

Error Source	Prefix	Value
Driver Manager	[vendor] [ODBC-component] [data-source]	[unixODBC] [Driver Manager] Not applicable
Oracle ODBC Driver	[vendor] [ODBC-component] [data-source]	[ORACLE] [Oracle ODBC Driver] Not applicable
Oracle Database	[vendor] [ODBC-component] [data-source]	[ORACLE] [Oracle ODBC Driver] [Oracle OCI]

Error Source

Prefix

Value

Driver Manager

[vendor]

[ODBC-component]

[data-source]

[unixODBC]

[Driver Manager]

Not applicable

Oracle ODBC Driver

[vendor]

[ODBC-component]

[data-source]

[ORACLE]

[Oracle ODBC Driver]

Not applicable

Oracle Database

[vendor]

[ODBC-component]

[data-source]

[ORACLE]

[Oracle ODBC Driver]

[Oracle OCI]

For example, if the error message does not contain the Ora prefix shown in the following format, the 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:

Although the error message contains the Ora prefix, the actual error may originate from one of several sources.

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