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. |
A
llocates 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.