E Using Oracle ODBC Driver
This appendix provides information related to using Oracle ODBC Driver.
It contains the following sections:
See Also:
Oracle Database Installation Guide for your respective platform for the ODBC driver certification information
E.1 Oracle ODBC Features Not Supported
Oracle ODBC Driver does not support the following Oracle ODBC 3.0 features:
-
Interval data types
-
SQL_C_UBIGINT
andSQL_C_SBIGINT
C data type identifiers -
Shared connections
-
Shared environments
-
The
SQL_LOGIN_TIMEOUT
attribute ofSQLSetConnectAttr
-
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 |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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:
"DATE and TIMESTAMP Data Types" for information about DATE
and TIMESTAMP
data types related to performance and tuning
Floating Point Data Types
When connected to an Oracle Database 12c Release 2 (12.2) or later, 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:
Limited Data Type | Description |
---|---|
Literals |
Oracle Database limits literals in SQL statements to 4000 bytes. |
|
The Oracle limit for |
|
Oracle Database permits only a single long data column in each table. The long data types are |
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:
Keyword | Meaning | Value |
---|---|---|
|
ODBC data source name |
User-supplied name This is a mandatory keyword. |
|
TNS service name |
User-supplied name This is a mandatory keyword. |
|
User ID or user name |
User-supplied name This is a mandatory keyword. |
|
Password |
User-supplied name Specify This is a mandatory keyword. |
|
Database attribute |
|
|
Applications attributes |
|
|
Result sets |
|
|
Query timeout option |
|
|
Close cursor |
|
|
Batch autocommit mode |
|
|
Fetch buffer size |
User-supplied numeric value (specify a value in bytes of 0 or greater).The default is 60,000 bytes. |
|
Failover |
|
|
Failover retry count |
User-supplied numeric value. The default is 10. |
|
Failover delay |
User-supplied numeric value. The default is 10. |
|
LOB writes |
|
|
Force |
|
|
|
|
|
Schema field |
|
|
Set metadata ID default |
|
|
Disable |
|
|
Bind |
|
|
Numeric settings |
|
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
.Procedure call: {CALL Example1(?)} {CALL RSET.Example1(?)} Function Call: {? = CALL Example1(?)} {? = CALL RSET.Example1(?)}
-
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:{CALL RSET.Example2("Literal 1", "Literal 2")}
The following sample application shows how to return a result set by using Oracle ODBC Driver:
/*
* Sample Application using Oracle reference cursors through ODBC
*
* Assumptions:
*
* 1) Oracle Sample database is present with data loaded for the EMP table.
*
* 2) Two fields are referenced from the EMP table, ename and mgr.
*
* 3) A data source has been setup to access the sample database.
*
*
* Program Description:
*
* Abstract:
*
* This program demonstrates how to return result sets using
* Oracle stored procedures
*
* Details:
*
* This program:
* Creates an ODBC connection to the database.
* Creates a Packaged Procedure containing two result sets.
* Executes the procedure and retrieves the data from both result sets.
* Displays the data to the user.
* Deletes the package then logs the user out of the database.
*
*
* The following is the actual PL/SQL this code generates to
* create the stored procedures.
*
DROP PACKAGE ODBCRefCur;
CREATE PACKAGE ODBCRefCur AS
TYPE ename_cur IS REF CURSOR;
TYPE mgr_cur IS REF CURSOR;
PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2);
END;
/
CREATE PACKAGE BODY ODBCRefCur AS
PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2)
AS
BEGIN
IF NOT Ename%ISOPEN
THEN
OPEN Ename for SELECT ename from emp;
END IF;
IF NOT Mgr%ISOPEN
THEN
OPEN Mgr for SELECT mgr from emp where job = pjob;
END IF;
END;
END;
/
*
* End PL/SQL for Reference Cursor.
*/
/*
* Include Files
*/
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
/*
* Defines
*/
#define JOB_LEN 9
#define DATA_LEN 100
#define SQL_STMT_LEN 500
/*
* Procedures
*/
void DisplayError( SWORD HandleType, SQLHANDLE hHandle, char *Module );
/*
* Main Program
*/
int main()
{
SQLHENV hEnv;
SQLHDBC hDbc;
SQLHSTMT hStmt;
SQLRETURN rc;
char *DefUserName ="jones";
char *DefPassWord ="password";
SQLCHAR ServerName[DATA_LEN];
SQLCHAR *pServerName=ServerName;
SQLCHAR UserName[DATA_LEN];
SQLCHAR *pUserName=UserName;
SQLCHAR PassWord[DATA_LEN];
SQLCHAR *pPassWord=PassWord;
char Data[DATA_LEN];
SQLINTEGER DataLen;
char error[DATA_LEN];
char *charptr;
SQLCHAR SqlStmt[SQL_STMT_LEN];
SQLCHAR *pSqlStmt=SqlStmt;
char *pSalesMan = "SALESMAN";
SQLINTEGER sqlnts=SQL_NTS;
/*
* Allocate the Environment Handle
*/
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );
if (rc != SQL_SUCCESS)
{
printf( "Cannot Allocate Environment Handle\n");
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
/*
* Set the ODBC Version
*/
rc = SQLSetEnvAttr( hEnv,SQL_ATTR_ODBC_VERSION,(void *)SQL_OV_ODBC3,0);
if (rc != SQL_SUCCESS)
{
printf( "Cannot Set ODBC Version\n");
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
/*
* Allocate the Connection handle
*/
rc = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );
if (rc != SQL_SUCCESS)
{
printf( "Cannot Allocate Connection Handle\n");
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
/*
* Get User Information
*/
strcpy ((char *) pUserName, DefUserName );
strcpy ((char *) pPassWord, DefPassWord );
/*
* Data Source name
*/
printf( "\nEnter the ODBC Data Source Name\n" );
charptr = gets ((char *) ServerName);
/*
* User Name
*/
printf ( "\nEnter User Name Default [%s]\n", pUserName);
charptr = gets ((char *) UserName);
if (*charptr == '\0')
{
strcpy ((char *) pUserName, (char *) DefUserName );
}
/*
* Password
*/
printf ( "\nEnter Password Default [%s]\n", pPassWord);
charptr = gets ((char *)PassWord);
if (*charptr == '\0')
{
strcpy ((char *) pPassWord, (char *) DefPassWord );
}
/*
* Connection to the database
*/
rc = SQLConnect( hDbc,pServerName,(SQLSMALLINT) strlen((char *)pServerName),pUserName,(SQLSMALLINT) strlen((char *)pUserName),pPassWord,(SQLSMALLINT) strlen((char *)pPassWord));
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_DBC, hDbc, "SQLConnect");
}
/*
* Allocate a Statement
*/
rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
if (rc != SQL_SUCCESS)
{
printf( "Cannot Allocate Statement Handle\n");
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
/*
* Drop the Package
*/
strcpy( (char *) pSqlStmt, "DROP PACKAGE ODBCRefCur");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
/*
* Create the Package Header
*/
strcpy( (char *) pSqlStmt, "CREATE PACKAGE ODBCRefCur AS\n");
strcat( (char *) pSqlStmt, " TYPE ename_cur IS REF CURSOR;\n");
strcat( (char *) pSqlStmt, " TYPE mgr_cur IS REF CURSOR;\n\n");
strcat( (char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur,");
strcat( (char *) pSqlStmt, "Mgr IN OUT mgr_cur,pjob IN VARCHAR2);\n\n");
strcat( (char *) pSqlStmt, "END;\n");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLExecDirect");
}
/*
* Create the Package Body
*/
strcpy( (char *) pSqlStmt, "CREATE PACKAGE BODY ODBCRefCur AS\n");
strcat( (char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur,");
strcat( (char *) pSqlStmt, "Mgr IN OUT mgr_cur, pjob IN VARCHAR2)\n AS\n BEGIN\n");
strcat( (char *) pSqlStmt, " IF NOT Ename%ISOPEN\n THEN\n");
strcat( (char *) pSqlStmt, " OPEN Ename for SELECT ename from emp;\n");
strcat( (char *) pSqlStmt, " END IF;\n\n");
strcat( (char *) pSqlStmt, " IF NOT Mgr%ISOPEN\n THEN\n");
strcat( (char *) pSqlStmt, " OPEN Mgr for SELECT mgr from emp where job = pjob;\n");
strcat( (char *) pSqlStmt, " END IF;\n");
strcat( (char *) pSqlStmt, " END;\n");
strcat( (char *) pSqlStmt, "END;\n");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLExecDirect");
}
/*
* Bind the Parameter
*/
rc = SQLBindParameter(hStmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,JOB_LEN,0,pSalesMan,0,&sqlnts);
/*
* Call the Store Procedure which executes the Result Sets
*/
strcpy( (char *) pSqlStmt, "{CALL ODBCRefCur.EmpCurs(?)}");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLExecDirect");
}
/*
* Bind the Data
*/
rc = SQLBindCol( hStmt,1,SQL_C_CHAR,Data,sizeof(Data),&DataLen);
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLBindCol");
}
/*
* Get the data for Result Set 1
*/
printf( "\nEmployee Names\n\n");
while ( rc == SQL_SUCCESS )
{
rc = SQLFetch( hStmt );
if ( rc == SQL_SUCCESS )
{
printf("%s\n", Data);
}
else
{
if (rc != SQL_NO_DATA)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLFetch");
}
}
}
printf( "\nFirst Result Set - Hit Return to Continue\n");
charptr = gets ((char *)error);
/*
* Get the Next Result Set
*/
rc = SQLMoreResults( hStmt );
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLMoreResults");
}
/*
* Get the data for Result Set 2
*/
printf( "\nManagers\n\n");
while ( rc == SQL_SUCCESS )
{
rc = SQLFetch( hStmt );
if ( rc == SQL_SUCCESS )
{
printf("%s\n", Data);
}
else
{
if (rc != SQL_NO_DATA)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLFetch");
}
}
}
printf( "\nSecond Result Set - Hit Return to Continue\n");
charptr = gets ((char *)error);
/*
* Should Be No More Results Sets
*/
rc = SQLMoreResults( hStmt );
if (rc != SQL_NO_DATA)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLMoreResults");
}
/*
* Drop the Package
*/
strcpy( (char *) pSqlStmt, "DROP PACKAGE ODBCRefCur");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
/*
* Free handles close connections to the database
*/
SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
SQLDisconnect( hDbc );
SQLFreeHandle( SQL_HANDLE_DBC, hDbc );
SQLFreeHandle( SQL_HANDLE_ENV, hEnv );
printf( "\nAll Done - Hit Return to Exit\n");
charptr = gets ((char *)error);
return(0);
}
/*
* Display Error Messages
*/
void DisplayError( SWORD HandleType, SQLHANDLE hHandle, char *Module )
{
SQLCHAR MessageText[255];
SQLCHAR SQLState[80];
SQLRETURN rc=SQL_SUCCESS;
long NativeError;
SWORD RetLen;
SQLCHAR error[25];
char *charptr;
rc =
SQLGetDiagRec(HandleType,hHandle,1,SQLState,&NativeError,MessageText,255,&RetLen);
printf( "Failure Calling %s\n", Module );
if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
printf( "\t\t\t State: %s\n", SQLState);
printf( "\t\t\t Native Error: %d\n", NativeError );
printf( "\t\t\t Error Message: %s\n", MessageText );
}
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
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:
[[[server.][database].][owner_name].]procedure_name
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 section 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 Level 1 functions.
E.11.2 Implementation of ODBC API Functions
The following table describes how Oracle ODBC Driver implements specific functions:
Function | Description |
---|---|
|
|
|
|
|
If |
|
Refer to the information in the following row. |
All catalog functions |
If the |
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:
"BAR" "BARX" "XBAR" "XBARX" "SQLPROCTEST.BAR" "SQLPROCTEST.BARX" "SQLPROCTEST.XBAR" "SQLPROCTEST.XBARX"
When you look for %
or %%%%%%
, you get all eight procedures.
When you look for %_
or _%
, you get the following:
BAR BARX XBAR XBARX
When you look for .
or .%
or %.%
or SQLPROC%.
or SQLPROC%.%
, you get the following:
SQLPROCTEST.BAR SQLPROCTEST.BARX SQLPROCTEST.XBAR SQLPROCTEST.XBARX
When you look for %BAR
, you get the following:
BAR XBAR
When you look for .%BAR
or %.%BAR
, you get the following:
SQLPROCTEST.BAR SQLPROCTEST.XBAR
When you look for SQLPROC%
or .SQLPROC%
, you get the following:
nothing (0 rows)
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 provides 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 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 explicitly call entry points with the suffix. An ODBC application that is compiled with the UNICODE
and _UNICODE
preprocessor definitions generates the appropriate calls. For example, a call to SQLPrepare
compiles 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 must 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:
SQL Data Types | ODBC SQL Data Types |
---|---|
|
|
|
|
|
|
|
|
|
|
E.12.3 SQLGetData Performance
The SQLGetData
function allows an ODBC application to specify the data type to receive a column 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
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 must specify the Unicode literals for SQLConnect
.
HENV envHnd;
HDBC conHnd;
HSTMT stmtHnd;
RETCODE rc;
rc = SQL_SUCCESS;
// ENV is allocated
rc = SQLAllocEnv(&envHnd);
// Connection Handle is allocated
rc = SQLAllocConnect(envHnd, &conHnd);
rc = SQLConnect(conHnd, _T("stpc19"), SQL_NTS, _T("jones"), SQL_NTS, _T("password"), SQL_NTS);
.
.
.
if (conHnd)
SQLFreeConnect(conHnd);
if (envHnd)
SQLFreeEnv(envHnd);
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)
.
/* ** Execute SQL, bind columns, and Fetch. ** Procedure: ** ** SQLExecDirect ** SQLBindCol ** SQLFetch ** */ static SQLTCHAR *sqlStmt = _T("SELECT ename, job FROM emp"); SQLTCHAR ename[50]; SQLTCHAR job[50]; SQLINTEGER enamelen, joblen; _tprintf(_T("Retrieve ENAME and JOB using SQLBindCol 1.../n[%s]/n"), sqlStmt); // Step 1: Prepare and Execute rc = SQLExecDirect(stmtHnd, sqlStmt, SQL_NTS); // select checkSQLErr(envHnd, conHnd, stmtHnd, rc); // Step 2: Bind Columns rc = SQLBindCol(stmtHnd, 1, SQL_C_TCHAR, ename, sizeof(ename), &enamelen); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLBindCol(stmtHnd, 2, SQL_C_TCHAR, job, sizeof(job), &joblen); checkSQLErr(envHnd, conHnd, stmtHnd, rc); do { // Step 3: Fetch Data rc = SQLFetch(stmtHnd); if (rc == SQL_NO_DATA) break; checkSQLErr(envHnd, conHnd, stmtHnd, rc); _tprintf(_T("ENAME = %s, JOB = %s/n"), ename, job); } while (1); _tprintf(_T("Finished Retrieval/n/n"));
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.
/* ** Execute SQL, bind columns, and Fetch. ** Procedure: ** ** SQLExecDirect ** SQLFetch ** SQLGetData */ static SQLTCHAR *sqlStmt = _T("SELECT ename,job FROM emp"); // same as Case 1. SQLTCHAR ename[50]; SQLTCHAR job[50]; _tprintf(_T("Retrieve ENAME and JOB using SQLGetData.../n[%s]/n"), sqlStmt); if (rc != SQL_SUCCESS) { _tprintf(_T("Failed to allocate STMT/n")); goto exit2; } // Step 1: Prepare and Execute rc = SQLExecDirect(stmtHnd, sqlStmt, SQL_NTS); // select checkSQLErr(envHnd, conHnd, stmtHnd, rc); do { // Step 2: Fetch rc = SQLFetch(stmtHnd); if (rc == SQL_NO_DATA) break; checkSQLErr(envHnd, conHnd, stmtHnd, rc); // Step 3: GetData rc = SQLGetData(stmtHnd, 1, SQL_C_TCHAR, (SQLPOINTER)ename, sizeof(ename), NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLGetData(stmtHnd, 2, SQL_C_TCHAR, (SQLPOINTER)job, sizeof(job), NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); _tprintf(_T("ENAME = %s, JOB = %s/n"), ename, job); } while (1); _tprintf(_T("Finished Retrieval/n/n"));
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 Unicode application.
/* ** Execute SQL, bind columns, and Fetch. ** Procedure: ** ** SQLPrepare ** SQLBindParameter ** SQLExecute */ static SQLTCHAR *sqlStmt = _T("INSERT INTO emp(empno,ename,job) VALUES(?,?,?)"); static SQLTCHAR *empno = _T("9876"); // Emp No static SQLTCHAR *ename = _T("ORACLE"); // Name static SQLTCHAR *job = _T("PRESIDENT"); // Job _tprintf(_T("Insert User ORACLE using SQLBindParameter.../n[%s]/n"), sqlStmt); // Step 1: Prepare rc = SQLPrepare(stmtHnd, sqlStmt, SQL_NTS); // select checkSQLErr(envHnd, conHnd, stmtHnd, rc); // Step 2: Bind Parameter rc = SQLBindParameter(stmtHnd, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_DECIMAL, 4, // 4 digit 0, (SQLPOINTER)empno, 0, NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLBindParameter(stmtHnd, 2, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_CHAR, lstrlen(ename)*sizeof(TCHAR), 0, (SQLPOINTER)ename, lstrlen(ename)*sizeof(TCHAR), NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLBindParameter(stmtHnd, 3, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_CHAR, lstrlen(job)*sizeof(TCHAR), 0, (SQLPOINTER)job, lstrlen(job)*sizeof(TCHAR), NULL); checkSQLErr(envHnd, conHnd, stmtHnd, rc); // Step 3: Execute rc = SQLExecute(stmtHnd); checkSQLErr(envHnd, conHnd, stmtHnd, rc);
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.
/* ** Execute SQL, bind columns, and Fetch. ** Procedure: ** ** SQLPrepare ** SQLBindParameter ** SQLExecute ** SQLParamData ** SQLPutData ** ** SQLExecDirect ** SQLFetch ** SQLGetData */ static SQLTCHAR *sqlStmt1 = _T("INSERT INTO clobtbl(clob1) VALUES(?)"); static SQLTCHAR *sqlStmt2 = _T("SELECT clob1 FROM clobtbl"); SQLTCHAR clobdata[1001]; SQLTCHAR resultdata[1001]; SQLINTEGER ind = SQL_DATA_AT_EXEC; SQLTCHAR *bufp; int clobdatalen, chunksize, dtsize, retchklen; _tprintf(_T("Insert CLOB1 using SQLPutData.../n[%s]/n"), sqlStmt1); // Set CLOB Data { int i; SQLTCHAR ch; for (i=0, ch=_T('A'); i< sizeof(clobdata)/sizeof(SQLTCHAR); ++i, ++ch) { if (ch > _T('Z')) ch = _T('A'); clobdata[i] = ch; } clobdata[sizeof(clobdata)/sizeof(SQLTCHAR)-1] = _T('/0'); } clobdatalen = lstrlen(clobdata); // length of characters chunksize = clobdatalen / 7; // 7 times to put // Step 1: Prepare rc = SQLPrepare(stmtHnd, sqlStmt1, SQL_NTS); checkSQLErr(envHnd, conHnd, stmtHnd, rc); // Step 2: Bind Parameter with SQL_DATA_AT_EXEC rc = SQLBindParameter(stmtHnd, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_LONGVARCHAR, clobdatalen*sizeof(TCHAR), 0, (SQLPOINTER)clobdata, clobdatalen*sizeof(TCHAR), &ind); checkSQLErr(envHnd, conHnd, stmtHnd, rc); // Step 3: Execute rc = SQLExecute(stmtHnd); checkSQLErr(envHnd, conHnd, stmtHnd, rc); // Step 4: ParamData (initiation) rc = SQLParamData(stmtHnd, (SQLPOINTER*)&bufp); // set value checkSQLErr(envHnd, conHnd, stmtHnd, rc); for (dtsize=0, bufp = clobdata; dtsize < clobdatalen; dtsize += chunksize, bufp += chunksize) { int len; if (dtsize+chunksize<clobdatalen) len = chunksize; else len = clobdatalen-dtsize; // Step 5: PutData rc = SQLPutData(stmtHnd, (SQLPOINTER)bufp, len*sizeof(TCHAR)); checkSQLErr(envHnd, conHnd, stmtHnd, rc); } // Step 6: ParamData (temination) rc = SQLParamData(stmtHnd, (SQLPOINTER*)&bufp); checkSQLErr(envHnd, conHnd, stmtHnd, rc); rc = SQLFreeStmt(stmtHnd, SQL_CLOSE); _tprintf(_T("Finished Update/n/n")); rc = SQLAllocStmt(conHnd, &stmtHnd); if (rc != SQL_SUCCESS) { _tprintf(_T("Failed to allocate STMT/n")); goto exit2; } // Clear Result Data memset(resultdata, 0, sizeof(resultdata)); chunksize = clobdatalen / 15; // 15 times to put // Step 1: Prepare rc = SQLExecDirect(stmtHnd, sqlStmt2, SQL_NTS); // select checkSQLErr(envHnd, conHnd, stmtHnd, rc); // Step 2: Fetch rc = SQLFetch(stmtHnd); checkSQLErr(envHnd, conHnd, stmtHnd, rc); for(dtsize=0, bufp = resultdata; dtsize < sizeof(resultdata)/sizeof(TCHAR) && rc != SQL_NO_DATA; dtsize += chunksize-1, bufp += chunksize-1) { int len; // len should contain the space for NULL termination if (dtsize+chunksize<sizeof(resultdata)/sizeof(TCHAR)) len = chunksize; else len = sizeof(resultdata)/sizeof(TCHAR)-dtsize; // Step 3: GetData rc = SQLGetData(stmtHnd, 1, SQL_C_TCHAR, (SQLPOINTER)bufp, len*sizeof(TCHAR), &retchklen); } if (!_tcscmp(resultdata, clobdata)) { _tprintf(_T("Succeeded!!/n/n")); } else { _tprintf(_T("Failed!!/n/n")); }
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 frequently connects and disconnects 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 does not retrieve; especiallyLONG
columns. Because of the nature of the database server protocols, Oracle ODBC Driver must fetch the entire contents of aLONG
column if it is included in theSELECT
statement, regardless of whether the application binds the column or performs aSQLGetData
operation. -
If you are performing transactions that do not update the data source, then set the
SQL_ATTR_ACCESS_MODE
attribute of the ODBCSQLSetConnectAttr
function toSQL_MODE_READ_ONLY
. -
If you are not using ODBC escape clauses, then set the
SQL_ATTR_NOSCAN
attribute of the ODBCSQLSetConnectAttr
function or the ODBCSQLSetStmtAttr
function to true. -
Use the ODBC
SQLFetchScroll
function instead of the ODBCSQLFetch
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
orUPDATE
statement to determine if there are any LOB parameters. This query incurs an additional network round trip the first time anyINSERT
orUPDATE
is prepared and run.See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide for more information about 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
asDATE
Binds
SQL_TIMESTAMP
parameters as the appropriate Oracle Database data type. If this option is set toTRUE
, thenSQL_TIMESTAMP
binds as the OracleDATE
data type. If this option is set toFALSE
, thenSQL_TIMESTAMP
binds as the OracleTIMESTAMP
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 reuses 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 theodbc.ini
file to a value specified in bytes. This value is the amount of memory needed that determines how many rows of data Oracle ODBC Driver pre-fetches 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 is 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
andLOB
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 theLONG
andLOB
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 theLONG
andLOB
data types.
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 the performance. For example:
SELECT * FROM EMP WHERE HIREDATE = ?
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:
SELECT * FROM EMP WHERE TO_TIMESTAMP(HIREDATE) = ?
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.
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:
[vendor] [ODBC-component] [data-source] error-message
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] |
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.
[Oracle][ODBC]Error message text here
If the error message contains the Ora
prefix shown in the following format, then it is not an Oracle ODBC Driver error.
[Oracle][ODBC][Ora]Error message text here
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.