This document describes the Open Database Connectivity (ODBC) support provided in Oracle Database Lite 10g for the Palm OS Platform. Topics include:
For the Palm OS platform, Oracle Database Lite 10g supports a subset of the ODBC 3.0 application programming interface standard. Using the ODBC API, applications can access data stored in Oracle Database Lite from your Palm handheld device.
The Oracle Database Lite ODBC library supports the Dynamic SQL model, in which applications can construct SQL statements at runtime and execute them directly on the handheld device.
The supported ODBC API functions are listed in Table C-1.
| Function | Description |
|---|---|
| SQLAllocConnect | Allocates memory for a connection handle using the specified environment. |
| SQLAllocEnv | Allocates memory for an environment handle. |
| SQLAllocHandle | A generic function for allocating environment, connection, and statement handles. |
| SQLAllocStmt | Allocates memory for a statement handle using the specified connection. |
| SQLFreeConnect | Disconnects from the connected database using the specified handle, and frees the handle. |
| SQLFreeEnv | Frees the specified handle. Uncommitted transactions associated with the handle are rolled back. |
| SQLFreeHandle | A generic handle to free environment, connection, and statement handles. |
| SQLFreeStmt | Frees the specified statement handle and its associated temporary memory. |
| SQLConnect | Connects to a database and saves information about the connection in the provided connection handle. |
| SQLDisconnect | Disconnects and closes a previously connected database. |
| SQLBindParameter | Binds a data buffer to a parameter marker in a SQL statement. |
| SQLPrepare | Compiles a SQL statement and stores the information in the provided statement handle. |
| SQLExecDirect | Compiles and executes the specified SQL statement. |
| SQLExecute | Executes the prepared SQL using SQLPrepare. |
| SQLFetch | Reads in a row of data from the result set. After calling the function, the cursor is positioned to the next row to be read. |
| SQLBindCol | Binds a buffer to a column in the result set. |
| SQLDescribeCol | Retrieves information about a column of the result set. |
| SQLError | Extracts details about the last error associated to the provided handles. |
| SQLGetData | Reads in a single column from the current row into the specified buffer. |
| SQLNumResultCols | Returns the number of columns in the result set. |
| SQLRowCount | Returns the number of rows affected by a SQL SELECT, UPDATE, or DELETE statement. |
| SQLTransact | Requests a commit or rollback for all active operations on all statements associated with an environment. |
Allocates memory for a connection handle using the specified environment, hEnv.
Syntax
RETCODE SQLAllocConnect( hEnv, hDbc )
Arguments
The arguments for SQLAllocConnect are listed in Table C-2:
Table C-2 SQLAllocConnect Arguments
| Type | Name | Description |
|---|---|---|
| SQLHENV | hEnv |
Environment handle. If set to NULL, creates a new environment. |
| SQLHDBC* | hDbc |
Pointer to a connection handle where the routine stores the address of the newly allocated memory. |
Usage Note
This function is supported for backward compatibility with ODBC 2.0. New applications should be coded using the function SQLAllocHandle and the handle type SQL_HANDLE_DBC. Internally, SQLAllocConnect calls SQLAllocHandle.
Returns
SQLAllocConnect returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified environment handle.
SQLAllocEnv allocates memory for an environment handle.
To share a single transaction for different connections and statement handles, pass in the same environment handle to SQLAllocConnection, SQLAllocStmt, or SQLAllocHandle. This way, the new handles inherit, and share, the same environment handle. When these handles are freed, the actual connections and transaction are not freed. The resources are not released until the original environment handle is freed.
Syntax
RETCODE SQLAllocEnv( hEnv )
Arguments
The arguments for SQLAllocEnv are listed in Table C-3:
Table C-3 SQLAllocEnv Arguments
| Type | Name | Description |
|---|---|---|
| SQLHENV* | hEnv |
Pointer to an environment handle. |
Usage Note
This function is supported for backward compatibility with ODBC 2.0. New applications should be coded using the function SQLAllocHandle and the handle type SQL_HANDLE_ENV. Internally, SQLAllocEnv calls SQLAllocHandle.
Returns
SQLAllocEnv returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError and pass in NULL as the handle parameter.
SQLAllocHandle is a generic function for allocating environment, connection, and statement handles.
This function replaces the old allocation functions for each individual handle types (SQLAllocEnv, SQLAllocConnection, and SQLAllocStmt).
A transaction table (new OKAPI environment) is created for each new environment handle. To share a single transaction for different connections and statement handles, pass in the same environment handle to SQLAllocHandle as the inputHandle argument. This way, the new handles inherit and share the same environment handle. When these handles are freed, the actual connections and transaction are not freed. The resources are not released until the original environment handle is freed. You can also share a connection using the same method.
Syntax
RETCODE SQLAllocHandle( handleType, inputHandle, outputHandle )
Arguments
The arguments for SQLAllocHandle are listed in Table C-4:
Table C-4 SQLAllocHandle Arguments
| Type | Name | Description |
|---|---|---|
| SQLSMALLINT | handleType |
The type of handle to allocate. See the following "Usage Note" for more information. |
| SQLHANDLE | inputHandle |
The handle to base on the new handle. This is either an environment or connection handle.
To create a new handle from scratch, pass in NULL. |
| SQLHANDLE* | outputHandle |
Pointer to the storage for the newly create handle. |
An application allocates different handles to use with different API functions. The handle provides a context for each function. The supported handle types are listed in Table C-5:
| Handle | Type | Description |
|---|---|---|
| Environment | SQL_TYPE_ENV | Environment handles are used to create an environment. Each environment contains generic information that allows you to access the database. A new transaction is associated with a newly-created environment handle. |
| Connection | SQL_TYPE_DBC | A connection handle is used to open a connection to a specific Oracle Database Lite. Connections can be based on the same environment handle, hence sharing the same transaction across multiple database connections. However, a maximum of eight connections can share a single environment. |
| Statement | SQL_TYPE_STMT | The statement handle contains information about the compiled SQL statement and its result sets. |
Returns
SQLAllocHandle returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the inputHandle argument.
SQLAllocStmt allocates memory for a statement handle using the specified connection, hDbc.
Syntax
RETCODE SQLAllocStmt( hDbc, hStmt )
Arguments
The arguments for SQLAllocStmt are listed in Table C-6:
Table C-6 SQLAllocStmt Arguments
| Type | Name | Description |
|---|---|---|
| SQLHDBC | hDbc |
The connection handle to creating the new handle. |
| SQLHSTMT* | hStmt |
Pointer to a statement handle. |
Usage Note
This function is supported for backward compatibility with ODBC 2.0. New applications should be coded using the function SQLAllocHandle, and the handle type SQL_HANDLE_STMT. Internally, SQLAllocStmt calls SQLAllocHandle.
Returns
SQLAllocStmt returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified connection handle.
SQLFreeConnect disconnects from the connected database using the specified handle, and frees the handle.
Syntax
RETCODE SQLFreeConnect(hDbc )
Arguments
The arguments for SQLFreeConnect are listed in Table C-7:
Table C-7 SQLFreeConnect Arguments
| Type | Name | Description |
|---|---|---|
| SQLHDBC | hDbc |
The connection handle to free. |
Usage Note
This function is deprecated and is replaced by the new generic function SQLFreeHandle.
Returns
SQLFreeConnect returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified environment handle.
SQLFreeEnv frees the specified handle. Uncommitted transactions associated with the handle are rolled back.
Syntax
RETCODE SQLFreeEnv( hEnv )
Arguments
The arguments for SQLFreeEnv are listed in Table C-8:
Note
This function is deprecated and is replaced by the new generic function SQLFreeHandle.
Returns
SQLFreeEnv returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified environment handle.
SQLFreeHandle is a generic function to free environment, connection, and statement handles.
The argument handleType is not used, because the handle internally contains information about how it is last used and therefore how it should be freed.
Syntax
RETCODE SQLFreeHandle( handleType, handle )
Arguments
The arguments for SQLFreeHandle are listed in Table C-9:
Table C-9 SQLFreeHandle Arguments
| Type | Name | Description |
|---|---|---|
| SQLSMALLINT | handleType |
The type of handle to free. |
| SQLHANDLE | handle |
The handle to free. |
Returns
SQLFreeHandle returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified handle.
SQLFreeStmt frees the specified statement handle and its associated temporary memory.
Syntax
RETCODE SQLFreeStmt( hStmt, Option )
Arguments
The arguments for SQLFreeStmt are listed in Table C-10:
Table C-10 SQLFreeStmt Arguments
| Type | Name | Comments |
|---|---|---|
| SQLHSTMT | hStmt |
Statement handle to free. |
| SQLUSMALLINT | Option |
Use the value SQL-DROP to free handle. SQL-CLOSE is ignored. |
Usage Note
This function is deprecated and is replaced by the new generic function SQLFreeHandle.
Returns
SQLFreeStmt returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified environment handle.
SQLConnect connects to a database and saves information about the connection in the provided connection handle. The handle must be previously allocated using the SQLAllocateHandle function.
Syntax
RETCODE SQLConnect( hConn, dbName, dbNameLen, userName, userNameLen, auth, authLen )
Arguments
The arguments for SQLConnect are listed in Table C-11:
Table C-11 SQLConnect Arguments
| Type | Name | Description |
|---|---|---|
| SQLHDBC | hConn |
Newly allocated connection handle. If passed a connection handle that is in use, the function closes the existing connection. |
| SQLCHAR* | dbName |
Name of the database to connect to. |
| SQLSMALLINT | dbNameLen |
Length of the database name. |
| SQLCHAR* | userName |
This argument is not currently supported and is ignored. |
| SQLSMALLINT | userNameLen |
This argument is not currently supported and is ignored. |
| SQLCHAR* | auth |
Database encryption password. |
| SQLSMALLINT | authLen |
Database encryption password length. |
Returns
SQLConnect returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified connection handle.
SQLDisconnect disconnects and closes a previously connected database.
If the environment used to make the connection is not committed before the connection is closed, committing afterwards fails.
Syntax
RETCODE SQLDisconnect( hDbc )
Arguments
The arguments for SQLDisconnect are listed in Table C-12:
Table C-12 SQLDisconnect Arguments
| Type | Name | Description |
|---|---|---|
| SQLHDBC | hDbc |
Handle of connection to be disconnected. |
Returns
SQLDisconnect returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified connection handle.
SQLBindParameter binds a data buffer to a parameter marker in a SQL statement. Parameter markers are denoted by "?" in the SQL statement.
Syntax
RETCODE SQLBindParameter( hStmt, paramNo, paramType, cType, sqlType, colDef, scale, value, valueMaxSize, valueSize )
Arguments
The arguments for SQLBindParameter are listed in Table C-13:
Table C-13 SQLBlindParameter Arguments
| Type | Name | Description |
|---|---|---|
| SQLHSTMT | hStmt |
Statement handle. |
| SQLUSMALLINT | paramNo |
The number of the parameter marker to bind to. Starts from 1, counted from left to right. |
| SQLSMALLINT | paramType |
The parameter type. Currently, only SQL_PARAM_INPUT is supported. |
| SQLSMALLINT | cType |
The C datatype of the parameter. |
| SQLSMALLINT | sqlType |
The SQL datatype of the parameter. |
| SQLUINTEGER | colDef |
The precision of the parameter. |
| SQLSMALLINT | scale |
The scale of the parameter. |
| SQLPOINTER | value |
Pointer to the buffer where the parameter value is stored. |
| SQLINTEGER | valueMaxSize |
The size of the parameter buffer. |
| SQLINTEGER* | valueSize |
Actual size of the parameter value. |
Returns
SQLBindParameter returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR.
SQLPrepare compiles a SQL statement and stores the information in the provided statement handle.
Syntax
RETCODE SQLPrepare( hStmt, statement, statementLen )
Arguments
The arguments for SQLPrepare are listed in Table C-14:
Table C-14 SQLPrepare Arguments
| Type | Name | Description |
|---|---|---|
| SQLHSTMT | hStmt |
Statement handle. |
| SQLCHAR* | statement |
SQL statement string. |
| SQLINTEGER | statementLen |
Length of the SQL statement string. |
Returns
SQLPrepare returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified statement handle.
SQLExecDirect compiles and executes the specified SQL statement.
Syntax
RETCODE SQLExecDirect( hStmt, statement, statementLen )
Arguments
The arguments for SQLExecDirect are listed in Table C-15:
Table C-15 SQLExecDirect Arguments
| Type | Name | Description |
|---|---|---|
| SQLHSTMT | hStmt |
Statement handle. |
| SQLCHAR* | statement |
SQL statement string. |
| SQLINTEGER | statementLen |
Length of the SQL statement string. |
Returns
SQLExecDirect returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified statement handle.
SQLExecute executes the prepared SQL using SQLPrepare.
Syntax
RETCODE SQLExecute( hStmt )
Arguments
The arguments for SQLExecute are listed in Table C-16:
Returns
SQLExecute returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified statement handle.
SQLFetch reads in a row of data from the result set. After calling the function, the cursor is positioned to the next row to be read.
Application can call SQLGetData to read in the columns of the read-in row.
If the application called SQLBindCol to bind columns, SQLFetch stores data from the row in the specified buffers.
Syntax
RETCODE SQLFetch( hStmt )
Arguments
The arguments for SQLFetch are listed in Table C-17:
Returns
SQLFetch returns SQL_SUCCESS if a new row of data is read successfully.
If there are no more rows to be read, SQLFetch returns SQL_NO_DATA_FOUND.
If an error occurs, the function returns SQL_ERROR. To find out specifics about an error, the application can call SQLError with the specified statement handle.
SQLBindCol binds a buffer to a column in the result set. The buffer is updated when SQLFetch is called. New columns from the result set are then read in.
SQLBindCol can be called after or before the statement is prepared and executed, as long as it is called before SQLFetch is called.
Syntax
RETCODE SQLBindCol( hStmt, columnNo, targetType, targetValue, targetSize, actualSize )
Arguments
The arguments for SQLBindCol are listed in Table C-18:
Table C-18 SQLBindCol Arguments
| Type | Name | Description |
|---|---|---|
| SQLHSTMT | hStmt |
Statement handle. |
| SQLUSMALLINT | columnNo |
The number of the column of the result set to bind to. |
| SQLSMALLINT | targetType |
The C datatype of the buffer. |
| SQLPOINTER | targetValue |
Pointer to buffer to hold the column data. |
| SQLINTEGER | targetSize |
Size of the buffer in bytes. |
| SQLINTEGER* | actualSize |
Pointer buffer to hold the size of the data read. Can pass in NULL if you do not want the information. |
Returns
SQLBindCol returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified statement handle.
SQLDescribeCol retrieves information about a column of the result set.
Syntax
RETCODE SQLDescribeCol( hStmt, columnNo, columnName, columnNameMaxLen, datatype, columnNameLen, columnSize, decimalDigits, nullable )
Arguments
The arguments for SQLDescribeCol are listed in Table C-19:
Table C-19 SQLDescribeCol Arguments
| Type | Name | Description |
|---|---|---|
| SQLHSTMT | hStmt |
Statement handle. |
| SQLUSMALLINT | columnNo |
The number of the column in the result. |
| SQLCHAR* | columnName |
Pointer to string buffer to store the returned name of the column. |
| SQLSMALLINT | columnNameMaxLen |
Size of the string buffer. |
| SQLSMALLINT | *columnNameLen |
Returned size of the column name in bytes. |
| SQLSMALLINT* | dataType |
Returned SQL datatype. |
| SQLUINTEGER* | columnSize |
Returned size of the column. |
| SQLSMALLINT* | decimalDigits |
Returned precision of the column. |
| SQLSMALLINT* | nullable |
Set to 1 if column is nullable, or 0 if it is not. |
Returns
SQLDescribeCol returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified statement handle.
SQLError extracts details about the last error associated with the provided handles.
Syntax
RETCODE SQLError( hEnv, hConn, hStmt, sqlState, nativeError, messageText, messageMaxSize, messageLength)
Arguments
The arguments for SQLError are listed in Table C-20:
| Type | Name | Description |
|---|---|---|
| SQLHENV | hEnv |
Environment handle. |
| SQLHDBC | hConn |
Database handle. |
| SQLHSTMT | hStmt |
Statement handle. |
| SQLCHAR* | sqlState |
Pointer to string buffer to store the returned SQLSTATE. |
| SQLINTEGER* | nativeError |
Native error code. |
| SQLCHAR* | messageText |
Error message text. |
| SQLSMALLINT | messageMaxSize |
Size of buffer passed in. |
| SQLSMALLINT* | messageLen |
Length of returned message text. |
Returns
SQLError returns SQL_SUCCESS if it can retrieve information related to the last error. If there were no errors associated with the specified handle, the function returns SQL_NO_DATA_FOUND.
SQLGetData reads in a single column from the current row into the specified buffer. The routine attempts to convert the data to the target buffer's type.
Syntax
RETCODE SQLGetData( hStmt, columnNo, targetType, targetValue, targetSize, actualSize )
Arguments
The arguments for SQLGetData are listed in Table C-21:
Table C-21 SQLGetData Arguments
| Type | Name | Description |
|---|---|---|
| SQLHSTMT | hStmt |
Statement handle. |
| SQLUSMALLINT | columnNo |
The number of the column. |
| SQLSMALLINT | targetType |
The type of the buffer target Value. |
| SQLPOINTER | targetValue |
Pointer to the buffer to store the result column data. |
| SQLINTEGER | targetSize |
Size of the buffer. |
| SQLINTEGER* | actualSize |
Actual number of bytes read into the specified buffer. |
Returns
SQLGetData returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified statement handle.
SQLNumResultCols returns the number of columns in the result set.
Syntax
RETCODE SQLNumResultCols( hStmt, columnCount )
Arguments
The arguments for SQLNumResultCols are listed in Table C-22:
Table C-22 SQLNumResultCols Arguments
| Type | Name | Description |
|---|---|---|
| SQLHSTMT | hStmt |
Statement handle. |
| SQLSMALLINT* | columnCount |
Pointer to buffer to store the returned number of columns in the result set. |
Returns
SQLNumResultCols returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified statement handle.
SQLRowCount returns the number of rows affected by a SQL SELECT, UPDATE, or DELETE statement.
Syntax
RETCODE SQLRowCount( hStmt, rowCount )
Arguments
The arguments for SQLRowCount are listed in Table C-23:
Table C-23 SQLRowCount Arguments
| Type | Name | Description |
|---|---|---|
| SQLHSTMT | hStmt |
Statement handle. |
| SQLINTEGER* | rowCount |
Number of rows in the result set. |
Returns
SQLRowCount returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified statement handle.
SQLTransact requests a commit or rollback for all active operations on all statements associated with an environment.
Syntax
RETCODE SQLTransact( hEnv, hDbc, completionType )
Arguments
The arguments for SQLTransact are listed in Table C-24:
Table C-24 SQLTransact Arguments
| Type | Name | Description |
|---|---|---|
| SQLHENV | hEnv |
Environment handle. |
| SQLHDBC | hDbc |
Connection handle. Not used. |
| SQLUSMALLINT | completionType |
The transaction action, which could be either SQL_COMMIT or SQL_ROLLBACK. |
Returns
SQLTransact returns SQL_SUCCESS if it is successful. Otherwise, it returns SQL_ERROR. To find out the specifics about an error, the application can call SQLError with the specified environment handle.