Skip Headers

Oracle® Database Lite Oracle Lite Client Guide Release 10.3 Part Number E12548-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

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