9 ODBC Drivers

The following sections describe the support for ODBC and samples:

Section 9.1, "Supported ODBC Drivers for Oracle Database Lite"
Section 9.2, "Executing the ODBC Examples"

Note:

A sample for using ODBC to access the Oracle Lite database is in the <ORACLE_HOME>\Mobile\Sdk\samples\odbc\win32\c_samples directory.

9.1 Supported ODBC Drivers for Oracle Database Lite

The Microsoft Open Database Connectivity (ODBC) interface is a procedural, call-level interface for accessing any SQL database, and is supported by most database vendors. It specifies a set of functions that allow applications to connect to the database, prepare and execute SQL statements at runtime, and retrieve query results.

Oracle Database Lite supports Level 3 compliant ODBC 2.0 and the ODBC 3.5 drivers through Oracle Database Lite ODBC drivers with the following restrictions:

ODBC 2.0 driver: The default driver for all Oracle Database Lite components. This will be installed by default, unless otherwise configured.
ODBC 3.5 driver : If you want to use the ODBC 3.5 driver, you must configure the ODBC.INI file to use the olod3540.dll as the ODBC driver. Configure the ODBC.INI file by executing the ODBC administrator. On Windows, you can modify the ODBC driver either with the odbcad32 command-line tool or by executing the ODBC Administrator GUI tool by clicking Control Panel -> Administrative Tools -> Data Sources (ODBC).

If your application uses the ODBC 3.5 driver, link with the olod3540.lib, which is the ODBC 3.5 driver library. The data sources that use the ODBC 3.5 driver for each connection must specify the correct library in the Driver32 field.

If you want to use the Visual Studio 2005 built-in features to design database applications, then you must use the ODBC 3.5 driver.

For more information on ODBC, see the following:

Microsoft ODBC documentation.
Using ODBC within a stored procedure, as described in Section 13.6.2, "Using Stored Procedures to Return Multiple Rows".

9.2 Executing the ODBC Examples

The ODBC examples are located in <ORACLE_HOME>\Mobile\Sdk\Samples and must be compiled using a C++ complier. To build them, use nmake.

There are five ODBC examples: odbctbl, odbcview, odbcfunc, odbctype, and long. You use the POLITE DSN to execute these examples. The POLITE DSN is automatically created during the Mobile Development Kit installation.

The first four examples have their own output windows listing the activity log. Closing the current example window causes the next example to be run. The output displayed in the example windows is also printed in the following log files: odbctbl.log, odbcview.log, odbcfunc.log, odbctype.log. The long example output is collected in the output file: long.out.

The following sections describe the functionality of the samples:

Section 9.2.1, "ODBCTBL"
Section 9.2.2, "ODBCVIEW"
Section 9.2.3, "ODBCFUNC"
Section 9.2.4, "ODBCTYPE"
Section 9.2.5, "LONG"

9.2.1 ODBCTBL

This is an ODBC SQL table example, which shows how to manipulate tables using the ODBC API. It creates the EMP table with columns ID, NAME, START_DATE, SALARY. After creation, it populates this table with data, performs an update on the salary column, selectively deletes some rows, then selects from the resulting table and shows the results of the fetch operation. At the end, the EMP table is dropped.

9.2.2 ODBCVIEW

This is an ODBC SQL view example, which demonstrates how to manipulate views using the ODBC API. It creates the EMP table and the HIGH_PAID_EMP view, selecting the full name (using the CONCAT scalar function), HIRE_DATE and SALARY from the EMP table. Then, the example populates the EMP table and selects from the HIGH_PAID_EMP view to show the populated data. The salary column of EMP is updated, some rows are delete, and a select from HIGH_PAID_EMP is issued to demonstrate how the changes are reflected in the view. Finally, the view and the table are dropped.

9.2.3 ODBCFUNC

This is an ODBC SQL scalar functions example, which shows you how to use scalar functions in the ODBC API. It creates table EMP, populates it with the data, then performs a select on ID, FULL_NAME from EMP. When it calculates the full name, it uses the ODBC scalar function CONCAT—with last and first names as arguments. The example updates the table, converting the last name to uppercase and first name to lowercase for IDs less than three using ODBC scalar functions UCASE and LCASE. The new data is selected and displayed again. Finally, the table EMP is dropped.

9.2.4 ODBCTYPE

This is ODBC SQL types example, which shows you how to manipulate different data types using the ODBC API. This test creates the EMP table, populates it with data, selects all the rows and displays the result. However, the columns are bound differently from the previous tests. First, it calls SQLNumResultCols to find the number of result columns. Then, for each result column, it calls SQLDescribeCol to retrieve all of the information about that column, such as column name, column name length, column type, column length, column scale, and so on. This information is used to bind the column. Thus, you can see how you can retrieve the type information from the database using the ODBC API.

9.2.5 LONG

This example exercises the basic read/write functions of SQL LONG VARCHAR. It first drops, then creates the LONG_DATA table with one LONG VARCHAR column and inserts the data into the table. For each row the data is put in frames, where each frame represents a buffer of long varchar data (of length 4096). The example uses SQLParamData and SQLPutData to send each frame to populate the row. Then, issues a select to fetch the rows and read long varchar data from the table. For each row, the data is also read in frames, using SQLGetData until SQL_NO_DATA_FOUND is returned. These actions are logged into the long.out file.