Raima Database Manager 11.0

(1)

1

Raima Database Manager 11.0

(2)

Trademarks

Raima Database Manager® (RDM®), RDM Embedded® and RDM Server® are trademarks of Raima Inc. and may be registered in the United States of America and/or other countries. All other names may be trademarks of their respective owners.

This guide may contain links to third-party Web sites that are not under the control of Raima Inc. and Raima Inc. is not responsible for the content on any linked site. If you access a third-party Web site mentioned in this guide, you do so at your own risk. Inclusion of any links does not imply Raima Inc. endorsement or acceptance of the content of those third-party sites.

Contents 3

Introduction 7

Advanced Topics 23

RDM ODBC API Reference 29

Return Codes ODBC 174

ODBC C Data Types 175

Registering RDM ODBC Driver 177

Glossary 183

(3)

Contents 3

SQLBindParameter 36 SQLCancel 38 SQLCloseCursor 40 SQLColAttribute 42 SQLColumns 45 SQLConnect 47 SQLCopyDesc 50 SQLDescribeCol 52 SQLDescribeParam 54 SQLDescribeStmt 56 SQLDisconnect 59 SQLDriverConnect 61 SQLEndTran 64 SQLExecDirect 66 SQLExecute 68 SQLExtendedTran 70 SQLFetch 74 SQLFetchScroll 76 SQLForeignKeys 78 SQLFreeHandle 80 SQLFreeStmt 82 SQLGetConnectAttr 84 SQLGetCursorName 86 SQLGetData 88 SQLGetDescField 90 SQLGetDescRec 92 SQLGetDiagField 94 SQLGetDiagRec 96 SQLGetEnvAttr 98

(5)

Contents 5 SQLGetFunctions 100 SQLGetInfo 102 SQLGetStmtAttr 104 SQLGetTypeInfo 106 SQLMoreResults 108 SQLNativeSql 110 SQLNumParams 112 SQLNumResultCols 114 SQLParamData 116 SQLPrepare 118 SQLPrimaryKeys 120 SQLProcedureColumns 122 SQLProcedures 124 SQLPutData 126 SQLRowCount 128 SQLSetConnectAttr 130 SQLSetCursorName 137 SQLSetDescField 139 SQLSetDescRec 141 SQLSetEnvAttr 143 SQLSetError 146 SQLSetStmtAttr 148 SQLSpecialColumns 155 SQLStatistics 158 SQLTables 160 SQLTransactStatus 162

Summary Listing of RDM SQL Server API Functions 164

rdmsqlserver_init 165

(6)

rdmsqlserver_stop 170

rdmsqlserver_term 172

Return Codes ODBC 174

ODBC C Data Types 175

Registering RDM ODBC Driver 177

Overview 177

Opening the ODBC Driver Manager 177

Configuring the default DSN entry 178

Working with Default Databases 178

Changing the Advanced Options 179

Adding a new DSN entry 180

Removing an existing DSN entry 181

Using the ODBC Driver 182

Glossary 183

(7)

Introduction 7

Introduction

RDM provides a separate ODBC API that allows SQL applications to be written using the industry-standard Open Data Base Connectivity C language interface to perform database operations using standard SQL. Appli-cations written using this API can be both directly-linked to the RDM database engine or can run as client pro-grams that access a RDM SQL server program (rdmsqlserver) running on a separate computer. Even though ODBC was developed by Microsoft for use on the Windows operating system, the RDM ODBC implementation is available on every OS platform that RDM supports.

All of the functions are prefixed with "SQL". ODBC data types and constant declarations are provided in the standard header files: sql.h for basic ODBC functionality; sqlext.h for basic and extended ODBC func-tionality, sqlrext.h for basic, extended, and RDM-specific functionality.

The RDM ODBC implementation is based on version 3.51 of the Microsoft ODBC specification.

Comparing the ODBC API with the Native RSQL API

The following table provides a mapping of the ODBC API functions with the RSQL API functions. Not all ODBC functions have an equivalent RSQL API function. Some, (e.g., SQLTables, SQLColumns, etc) are imple-mented in the RDM ODBC layer as select statements on built-in virtual system catalog tables which are

described later in this section. Also note that those functions that do have a RSQL API equivalent do not have the same function arguments. However, the basic operational approach (e.g., function calling sequence) that is used in an ODBC application is also needed in a RSQL application. ODBC API functions that are not listed do not have a RSQL API counterpart.

ODBC API Function RSQL Function Comments

SQLAllocHandle rsqlAllocConn rsqlAllocStmt

Allocation of connection and statement handles are made through separated functions. There is no environment handle.

SQLBindCol n/a Column result values are not bound but are returned by rsqlFetch or rsqlGetData.

SQLBindParameter rsqlBindParam

SQLCancel n/a Call rsqlCloseStmt to cancel statement processing. SQLCloseCursor rsqlCloseStmt

SQLColAttribute rsqlGetColDescr

SQLColumns n/a Database meta-data information is available by executing select statements on the appropriate syscat virtual tables.

SQLConnect n/a Connections are initiated when rsqlAllocConn is called. Data-bases are opened through calls to rsqlOpenDB or rsqlO-penCat.

SQLDescribeCol rsqlGetColDescr SQLDescribeParam rsqlGetParamDescr

SQLDescribeStmt rsqlGetStmtDescr SQLDescribeStmt is a Raima Inc. extension.

SQLDisconnect n/a Connections are closed when rsqlFreeConn is called. SQLEndTran rsqlTransCommit

rsqlTransRollback

SQLExecDirect rsqlExecDirect

(8)

ODBC API Function RSQL Function Comments SQLExecute rsqlExecute SQLExtendedTran rsqlTransStart rsqlTransSavepoint rsqlTransRelease rsqlTransCommit rsqlTransRollback

We believe that separate calls represent a better API design than a single call with a control variable

SQLFetch rsqlFetch Note that the rsqlFetch returns the column result values-no bound columns.

SQLForeignKeys n/a Database meta-data information is available by executing select statements on the appropriate syscat virtual tables.

SQLFreeHandle rsqlFreeConn rsqlFreeStmt SQLGetConnectAttr rsqlGetAutoCommit rsqlGetDateFormat rsqlGetDateSeparator rsqlGetDeferBlobMode rsqlGetReadOnlyTrmode

Not all ODBC connection attributes have a RDM equivalent. Not all RDM connection attributes have an ODBC equivalent.

SQLGetCursorName rsqlGetCursorName SQLGetData rsqlGetData SQLMoreResults rsqlMoreResults SQLNumParams rsqlGetNumParams SQLNumResultCols rsqlGetNumResultCols SQLPrepare rsqlPrepare

SQLPrimaryKeys n/a Database meta-data information is available by executing select statements on the appropriate syscat virtual tables.

SQLProcedures n/a Database meta-data information is available by executing select statements on the appropriate syscat virtual tables.

SQLPutData rsqlPutData SQLRowCount rsqlGetRowCount SQLSetConnectAttr rsqlSetAutoCommit rsqlSetDateFormat rsqlSetDateSeparator rsqlSetDeferBlobMode rsqlSetReadOnlyTrmode

Not all ODBC connection attributes have a RDM equivalent. Not all RDM connection attributes have an ODBC equivalent.

SQLSetCursorName rsqlSetCursorName

SQLSetError rsqlSetErrorCallback SQLSetError is a Raima Inc. extension.

SQLSpecialColumns n/a Database meta-data information is available by executing select statements on the appropriate syscat virtual tables.

SQLTables n/a Database meta-data information is available by executing select statements on the appropriate syscat virtual tables.

SQLTransactStatus rsqlTransStatus

The advantage of using the native API instead of ODBC is that it is simpler and more efficient with a smaller foot-print. However, ODBC is available and can certainly be used if DBMS independence and/or use of a standard SQL API is needed.

(9)

Introduction 9

Basic ODBC Application Steps

An ODBC SQL C application program consists of a set of calls to the SQL API functions in a particular sequence as outlined below. 1. Set up and initialize your application’s use of the SQL API as follows. a. Call SQLAllocHandle to allocate your environment handle, which will be used in allocating your connection handle. b. Call SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, *ODBC_VERSION,0). This will set the version of ODBC that you want to use with your database c. Call SQLAllocHandle again, but this time you are allocating your connection handle (hCon in the example below) d. Call SQLConnect to connect to the database e. Call SQLAllocHandle a third time to allocate your statement handle. You can allocate as many statement handles as you need in your application. Typically, you will need a statement handle for each statement that will be compiled once but potentially executed multiple times. f. Call SQLExecDirect("Open *database name*") in order to open your database. g. Call SQLSet* functions such as SQLSetConnectAttr to set up specific database attributes and operational parameters. 2. Prepare your application to execute SQL statements as follows. a. Call SQLPrepare to compile each of the statements that will need to be executed by your appli-cation. b. Call SQLBindParameter to bind your application’s variables to any parameter markers that were specified in the SQL statements prepared in the prior step. 3. At this point your application is execution ready. That means that your application will … a. Call SQLExecute to execute the appropriate statements that implement the database access needs for each particular function. Alternatively you can call SQLExecDirect to both compile and execute a statement in a single call. Usually, you would only do this for statements that only need to be executed once. b. Possibly call SQLParamData and SQLPutData to process any needed data-at-exec blob param-eters specified in insert and update statements. c. Call transaction statements using SQLExtendedTran in order to start the transaction, commit the transaction, create a savepoint, or rollback a transaction. d. Call SQLFetch to retrieve the result rows from an executed select statement. You may also need to call SQLGetData to retrieve blob data results a block at a time. Alternatively, if the select is updateable, you may need to call SQLGetCursorName or SQLSetCursorName associated with a related positioned update or delete statement to change the current row returned from the call to SQLFetch. You will need to call SQLFreeStmt on a select for which you do not call SQLFetch through to the end of the result set. 4. When your application is ready to terminate you need to… a. Call SQLFreeStmt or SQLFreeHandle for each statement handle allocated in step 1e. b. Call SQLFreeHandle for each allocated connection which automatically closes all open data-bases, terminates the connection and frees the connection handle and all its associated dynamically

(10)

allocated memory.

c. Call SQLFreeHandle for your environment handle.

Hello World!

If you follow the basic procedure listed above you can easily create a simple database where you insert some data and the retrieve the data to be displayed back out. This can be seen with the common Hello World example. In the example below return codes are mostly ignored for simplicity. This will be ok because we know what the expected results are.

/********************************************************************* ** HELLO WORLD SQL ** --- ** This document describes the process to create a simple database, insert a record ** containing a text field, read the text field from database and print it out. **********************************************************************/ #include <stdio.h> #include "sqlrext.h" /* The RDM SQL API. */ #define SQL_EMPSTR ((SQLCHAR *) "") /* any string */ int main( int argc, const char *const *argv) { SQLRETURN iRet; /* holds return value, 0 is success */ SQLCHAR sz[32]; SQLHDBC hCon; /* connection handle */ SQLHSTMT hStmt; /* statement handle */ SQLHENV hEnv; /* environment handle */ SQLLEN iLen = 0; SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv); /* Allocates env han-dle */ SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0); SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hCon); /* Allocates connection handle */ /* Connects to server */ SQLConnect(hCon, SQL_EMPSTR, SQL_NTS, SQL_EMPSTR, SQL_NTS, SQL_EMPSTR, SQL_ NTS); /* connects to database*/ SQLAllocHandle(SQL_HANDLE_STMT, hCon, &hStmt); /* Allocates statement handle */ SQLExecDirect(hStmt, (SQLCHAR *) "open hello_worldODBC", SQL_NTS); /* Opens the database */ /* Create a Hello World record */ SQLAllocHandle(SQL_HANDLE_STMT, hCon, &hStmt); /* allocates the statement han-dle */ SQLExecDirect(hStmt, (SQLCHAR *) "INSERT INTO info(myChar) VALUES('Hello

(11)

Introduction 11 World!')", SQL_NTS); /* Commit the insert, transaction is started by the first insert statement. */ SQLEndTran(SQL_HANDLE_DBC,hCon,SQL_COMMIT); /* Query the database for the record created. */ SQLExecDirect(hStmt, (SQLCHAR *) "SELECT myChar FROM info",SQL_NTS); /* Bind SQL fields to program variables. */ SQLBindCol(hStmt,1,SQL_C_CHAR,sz,sizeof(sz),&iLen); /* Fetch data from database into program variables. */ SQLFetch(hStmt) ; printf("%s\n\n",sz); /* Free the SQL statement handle. */ (void) SQLFreeHandle(SQL_HANDLE_STMT,hStmt); /* Close the database. */ SQLDisconnect(hCon); /*lint !e534 */ /* Free the database handles. */ (void) SQLFreeHandle(SQL_HANDLE_DBC, hCon); (void) SQLFreeHandle(SQL_HANDLE_ENV, hEnv); return 0; }

(12)

Allocating Handles

ODBC lets an application manage interactions with data sources through ODBC handles. An application tells the ODBC driver to allocate a handle by calling SQLAllocHandle. There are four types of ODBC handles.

Environment Handle

An environment handle is the base handle that manages global resources and options for an application. An application can call SQLAllocHandle using SQL_HANDLE_ENV as the first argument in order to allocate an environment handle. An application must allocate an environment handle in order to access an RDM data source. Multiple environment handles can be allocated inside an application.

Connection Handle

A connection handle is the handle that manages resources and options specific to a connection to a data source. An application can call SQLAllocHandle using SQL_HANDLE_DBC as the first argument and an existing envi-ronment handle as the second in order to allocate a connection handle. An application must allocate a con-nection handle in order to access an RDM data source. An application can allocate multiple concon-nection handles on one environment handle in order to manage multiple connections to the same or different data sources. In a multi-thread environment, RDM ODBC requires that each thread have its own connection handle.

Statement Handle

A statement handle is the handle that manages resources and options specific to a particular SQL statement operation. An application can call SQLAllocHandle using SQL_HANDLE_STMT as the first argument and an existing connection handle as the second in order to allocate a statement handle. An application must allocate a statement handle in order to execute SQL statements against an RDM data source. An application can allocate multiple statement handles on one connection handle in order to execute different SQL statements on one data source. Operations such as positioned update and delete require the use of multiple statement handles on one connection.

Descriptor Handle

A descriptor handle is the handle that manages the detailed characteristics and information both an application and the ODBC driver use internally. There are two types of descriptors: application descriptors and

imple-mentation descriptors. An application descriptor stores information an application maintains for its operations. An implementation descriptor stores information the ODBC driver uses internally. Both application and

imple-mentation descriptors have two subcategories: parameter descriptors and row descriptors. Together, there are four types of descriptor handles as below.

Application Parameter Descriptor APD An APD maintains information about a dynamic parameter an application uses when executing an SQL statement.

Application Row Descriptor ARD An ARD maintains information about a row/column returned to an application when it fetches data from a data source.

(13)

Introduction 13 Implementation Parameter Descriptor IPD An IPD maintains information about a dynamic parameter the

ODBC driver uses internally when an application executes an SQL statement.

Implementation Row Descriptor IRD An IRD maintains information about a row/column defined on a data source from which an application fetches data.

All the four descriptors are implicitly allocated by the ODBC driver. An application can explicitly allocate appli-cation descriptors (APD and ARD) by calling SQLAllocHandle using SQL_HANDLE_DESC as the first argu-ment and an existing connection handle as the second, and tell the ODBC drivers to use them instead of the implicitly-allocated descriptors. Descriptors explicitly allocated by an application cannot be used as imple-mentation descriptors (IPD and IRD).

The following example illustrates how to allocate ODBC handles using SQLAllocHandle. Example 1 Allocating handles

#include "sqlext.h" int main(void) { SQLRETURN rc; SQLHENV hEnv; SQLHDBC hDbc; /* Allocate an environment handle */ rc = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &hEnv); if (SQL_SUCCEEDED(rc)) { /* Allocate a connection handle */ rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc); if (SQL_SUCCEEDED(rc)) { /* Connect to the local data source. The code for connectToDataSource is shown in the next section */ rc = connectToDataSource(hDbc); if (SQL_SUCCEEDED(rc)) { /* Do some database operations */ rc = runDbOperations(hDbc); (void) SQLDisconnect(hDbc); } (void) SQLFreeHandle(SQL_HANDLE_DBC, hDbc); } (void) SQLFreeHandle(SQL_HANDLE_ENV, hEnv); } return 0; }

(14)

Connecting to a Data Source

An application will use the connection handle it allocated in order to connect to an RDM data source, by calling SQLConnect. In RDM ODBC, an RDM data source can be a transaction file server (TFServer) or a remote server process (RDMSQLServer) that accesses the TFServer. A connection to a TFServer is called "local" con-nection, where the RDM ODBC Driver uses the local RDM SQL engine directly linked to it in order to interact with the target TFServer. A connection to an RDMSQLServer is called "remote" connection as the RDM ODBC Driver uses its remote procedure call (RPC) layer to talk to an RDMSQLServer over TCP/IP, which has its own RDM SQL engine that in turn communicates with the target TFServer.

A typical RDM ODBC application is illustrated in Figure 1 below. The application will connect to the default TFS and the SQL processing will be performed within the application process.

Figure 1. Local Data Source

The same RDM ODBC application can connect to an external RDM SQL engine by providing the remote con-nection attributes. The SQL processing will be performed within the RDMSQLServer process and the results returned to the application. This client/server configuration would be used in configurations where: 1) the net-work speed is too slow for performing SQL engine access to the database; or, 2) the TFServer is supporting a database on a hardware architecture that is binary incompatible with the RDM ODBC API application hardware architecture (e.g., database running on a PowerPC device being accessed by a remote Windows desktop).

Figure 2. Remote Data Source See the rdmsqlserver section for more information.

(15)

Introduction 15

Choosing the Connection Type

An application can choose the type of connection it uses in two ways. First, it can explicitly set the connection type by setting the SQL_ATTR_RDM_CONN_TYPE connection attribute with a call to SQLSetConnectAttr before calling SQLConnect. If the application specifies SQL_CT_LOCAL, the RDM ODBC Driver will attempt to establish a local connection. If SQL_CT_REMOTE is specified, the RDM ODBC Driver will use a remote con-nection.

Alternatively, an application can let the RDM ODBC Driver choose the connection type implicitly by specifying the value for the two connection attributes: SQL_ATTR_RDM_REMOTE_NAME and SQL_ATTR_RDM_TFS_ NAME.

If the SQL_ATTR_RDM_REMOTE_NAME attribute is set to a non-null, non-empty value, the RDM ODBC Driver will use a remote connection.

If the SQL_ATTR_RDM_REMOTE_NAME attribute is not set or set to an empty string, and if the SQL_ATTR_RDM_ TFS_NAME attribute is set to a non-null, non-empty string, the RDM ODBC Driver will use a local connection. If neither the SQL_ATTR_RDM_REMOTE_NAME nor the SQL_ATTR_RDM_TFS_NAME attribute is set (and the connection type is not explicitly set with the SQL_ATTR_RDM_CONN_TYPE attribute), the RDM ODBC Driver will rely upon the value specified for the first argument of SQLConnect. In that case, if the value is a non-null, non-empty string, the RDM ODBC Driver will use a remote connection. Otherwise, a local connection will be used.

The SQL_ATTR_RDM_REMOTE_NAME and SQL_ATTR_TFS_NAME attributes are set to NULL by default. The SQL_ATTR_RDM_CONN_TYPE attribute is set to SQL_CT_UNKNOWN by default.

Setting the Data Source Name

In RDM ODBC, a data source name is the hostname of the computer where the target TFServer (for a local con-nection) or the RDMSQLServer (for a remote concon-nection) is running.

If the connection type is explicitly set to remote (that is, by setting the SQL_ATTR_RDM_CONN_TYPE connection attribute to SQL_CT_REMOTE), the value stored in the SQL_ATTR_RDM_REMOTE_NAME connection attribute will be used as the data source name. If the value in the SQL_ATTR_RDM_REMOTE_NAME attribute is not set, the default name ("localhost") will be used.

If the connection type is explicitly set to local (that is, by setting the SQL_ATTR_RDM_CONN_TYPE connection attribute to SQL_CT_LOCAL), the value stored in the SQL_ATTR_RDM_TFS_NAME connection attribute will be used as the data source name. If the value in the SQL_ATTR_RDM_TFS_NAME attribute is not set, the default name ("localhost") will be used.

If the connection type is not explicitly set (that is, the SQL_ATTR_RDM_CONN_TYPE attribute is set to SQL_CT_ UNKNOWN), the value stored in the SQL_ATTR_RDM_REMOTE_NAME attribute will be used as the remote data source name. If the SQL_ATTR_RDM_REMOTE_NAME attribute is not set, the value stored in the SQL_ATTR_ RDM_TFS_NAME attribute will be used as the local data source name. If neither value is set, the value specified for the first argument of SQLConnect will be used as the remote data source name. If that value is not set, "local-host" will be used as the local data source name.

(16)

Establishing a Connection

An application calls SQLConnect to establish a connection to an RDM data source. SQLConnect returns SQL_SUCCESS to let the application know a connection to the specified data source has been successfully estab-lished. SQLConnect returns SQL_ERROR when an error occurs, or SQL_SUCCESS_WITH_INFO if the oper-ation succeeds but the RDM ODBC Driver returned diagnostic informoper-ation that may include warnings. If SQLConnect returns a value other than SQL_SUCCESS, an application can call SQLGetDiagRec to retrieve the details of the diagnostics.

The following examples illustrate how an application can establish a connection to a data source. Example 2 Setting a remote connection

#include "sqlrext.h" /* RDM extension SQL header file */ SQLRETURN connectToDataSource( SQLHDBC hDbc) { SQLRETURN rc; /* Set connection type to remote */ rc = SQLSetConnectAttr(hDbc, SQL_ATTR_RDM_CONN_TYPE, (SQLPOINTER) SQL_CT_REMOTE, SQL_IS_UINTEGER); if (SQL_SUCCEEDED(rc)) { /* Since SQL_ATTR_RDM_REMOTE_NAME is not set, "localhost" will be used as the remote data source name. In this case, RDMSQLServer is running on the same computer as the application. */ rc = SQLConnect(hDbc, NULL, 0, NULL, 0, NULL, 0); } return rc; }

Example 3 Using SQL_ATTR_RDM_REMOTE_NAME

#include "sqlrext.h" /* RDM extension SQL header file */ SQLRETURN connectToDataSource( SQLHDBC hDbc) { SQLRETURN rc; /* This implicitly sets the connection type as local */ rc = SQLSetConnectAttr(hDbc, SQL_ATTR_RDM_TFS_NAME, (SQLPOINTER) "david-lnx", SQL_NTS); if (SQL_SUCCEEDED(rc)) { /* Since SQL_ATTR_RDM_CONN_TYPE is not set, "david-lnx" will be used as the local data source name. In this case, the TFServer is running on the computer whose hostname is "david-lnx" */

(17)

Introduction 17 rc = SQLConnect(hDbc, NULL, 0, NULL, 0, NULL, 0); } return rc; }

Example 4 Using SQLConnect

#include "sqlrext.h" /* RDM extension SQL header file */ SQLRETURN connectToDataSource( SQLHDBC hDbc) { SQLRETURN rc; /* Neither SQL_ATTR_RDM_CONN_TYPE, nor SQL_ATTR_RDM_REMOTE_NAME nor SQL_ATTR_TFS_NAME is set, the driver will use the value specified in the first argument of SQLConnect to determine the connection type and data source name. In this case, "tom-mac" will be used as the remote data source name, where the RDMSQLServer is running on the computer whose hostname is "tom-mac" */ rc = SQLConnect(hDbc, (SQLCHAR *) "tom-mac", SQL_NTS, NULL, 0, NULL, 0); return rc; }

An application can also establish a connection to a data source by using SQLDriverConnect. SQLDriv-erConnect accepts a connection string that may have the values for SQL_ATTR_RDM_REMOTE_NAME and SQL_ATTR_RDM_TFS_NAME embedded together. For details, see SQLDriverConnect in the ODBC API Function Reference section.

(18)

Executing an SQL Statement

Once a connection to a data source has been established, an application can execute SQL statements to interact with the databases managed by the data source. Executing an SQL statement involves the following steps.

Allocating a Statement Handle

Before executing an SQL statement, an application needs to allocate a statement handle by calling SQLAl-locHandle, passing SQL_HANDLE_STMT to the first argument and an existing connection handle to the sec-ond. A connection to a data source must already be established on the connection handle in order to allocate a statement handle on it.

Preparing an SQL Statement

An application can "prepare" an SQL statement before actually executing it. Preparing an SQL statement means the RDM ODBC Driver (and its underlying RDM SQL engine) parses the statement and stores the information necessary to execute it later. This process can also detect any errors that are in the statement, such as syntax errors and references to tables/columns that do not exist. An application can prepare a statement by calling SQLPrepare.

Executing an SQL Statement

Once an SQL statement has been prepared, an application can call SQLExecute to perform the actual execution of the statement.

A prepared statement can be executed multiple times without being prepared again. This is useful when execut-ing a statement with dynamic parameter bindexecut-ing. For details about dynamic parameter bindexecut-ing, see the Workexecut-ing with Parameters section.

If an application needs to execute the same SQL statement only once, the preparation and execution steps can be combined into a single call to SQLExecDirect. SQLExecDirect prepares and executes the specified SQL statement at once.

The following example illustrates how an application can allocate a statement handle and execute a statement that opens the NSFAWARDS database.

Example 5 Opening a database

#include "sqlrdm.h" SQLRETURN runDbOperations( SQLHDBC hDbc) { SQLRETURN rc; SQLHSTMT hStmt; /* Allocate a statement handle */

(19)

Introduction 19 rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt); if (SQL_SUCCEEDED(rc)) { rc = SQLExecDirect(hStmt, (SQLCHAR *) "OPEN nsfawards", SQL_NTS); (void) SQLFreeHandle(SQL_HANDLE_STMT, hStmt); } return rc; }

Committing or Rolling Back Transactions

Statements, such as INSERT, UPDATE and DELETE may make changes to the data stored in the data source. Those statements are called "data manipulation language (DML) statements." When executing a DML state-ment, RDM ODBC automatically starts a transaction so that the changes can be committed (i.e. saved) or rolled back (i.e. aborted). Microsoft ODBC specification says transactions are committed automatically with each DML statement by default (called "autocommit"). For RDM ODBC, the "autocommit" feature is turned off by default, so an application must manually commit or roll back a transaction by calling SQLEndTran. The autocommit feature can be turned on by setting the SQL_ATTR_AUTOCOMMIT connection attribute to SQL_AUTOCOMMIT_ON. The following examples illustrate how an INSERT statement can be executed including transaction processing. It attempts to insert a new row into the PERSON table of the NSFAWARDS database.

Example 6 Inserting a new row

#include "sqlrdm.h" SQLRETURN runDbOperations( SQLHDBC hDbc) { SQLRETURN rc; SQLHSTMT hStmt; const char *pszStmt = "INSERT INTO person VALUES " "'Houglum, Bill', 'M', '1'"; /* Allocate a statement handle */ rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt); if (SQL_SUCCEEDED(rc)) { rc = SQLExecDirect(hStmt, (SQLCHAR *) "OPEN nsfawards", SQL_NTS); if (SQL_SUCCEEDED(rc)) { /* Execute an INSERT statement */ rc = SQLExecDirect(hStmt, (SQLCHAR *) pszStmt, SQL_NTS); if (SQL_SUCCEEDED(rc)) { /* INSERT successful; commit the change */ (void) SQLEndTran(SQL_HANDLE_DBC, hDbc,

(20)

SQL_COMMIT); } else { /* INSERT failed; abort the change */ (void) SQLEndTran(SQL_HANDLE_DBC, hDbc, SQL_ROLLBACK); } } (void) SQLFreeHandle(SQL_HANDLE_STMT, hStmt); } return rc; }

(21)

Introduction 21

Retrieving the Result Set

SQL statements can return data from the data source. Those statements that do are called "queries." Queries can be executed in the same manner as the other SQL statements. However, an application must take additional steps to retrieve the data.

The data an application retrieves from the data source comes in one or more rows that in turn include one or more columns. This set of data is called "result set."

Retrieving the result set involves two steps.

Binding Columns

First, an application should tell RDM ODBC where it wants the result set to be stored. This step is called "binding columns." An application binds a column by specifying the pointer to a variable for each returned column through a call to SQLBindCol. An application can call SQLBindCol on each of the columns for which it needs to access the returned values.

Fetching Columns

After binding necessary columns, an application can call SQLFetch to retrieve the result set. SQLFetch retrieves the result set one row at a time by default. An application typically calls SQLFetch repeatedly until it returns SQL_NO_DATA, which indicates the end of the result set has been reached. Each call to SQLFetch puts the values of the current result row into the variables bound to the columns by SQLBindCol.

The following example illustrates how to retrieve all the rows stored in the PERSON table of the NSFAWARDS table.

Example 7 Retrieving rows from PERSON

#include "sqlrdm.h" SQLRETURN runDbOperations( SQLHDBC hDbc) { SQLRETURN rc; SQLHSTMT hStmt; const char *pszStmt = "SELECT * FROM person"; char szName[36]; char szGender[2]; char szJob[2]; SQLLEN cb1, cb2, cb; /* Allocate a statement handle */ rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt); if (SQL_SUCCEEDED(rc)) { /* Open the NSFAWARDS database first */ rc = SQLExecDirect(hStmt, (SQLCHAR *) "OPEN nsfawards", SQL_NTS);

(22)

if (SQL_SUCCEEDED(rc)) { /* Execute a SELECT statement */ rc = SQLExecDirect(hStmt, (SQLCHAR *) pszStmt, SQL_NTS); if (SQL_SUCCEEDED(rc)) { /* Bind all the 3 columns */ (void) SQLBindCol(hStmt 1, SQL_C_CHAR, szName, sizeof(szName), &cb1); (void) SQLBindCol(hStmt 2, SQL_C_CHAR, szGender, sizeof(szGender), &cb2); (void) SQLBindCol(hStmt 3, SQL_C_CHAR, szJob, sizeof(szJob), &cb3); do { /* Fetch as many rows as there are */ rc = SQLFetch(hStmt); if (SQL_SUCCEEDED(rc)) { if (cb1 != SQL_NULL_DATA) printf("Name = %s\n", szName); if (cb2 != SQL_NULL_DATA) printf("Gender = %s\n", szGender); if (cb3 != SQL_NULL_DATA) printf("Jobclass = %s\n", szJob); } } while (SQL_SUCCEEDED(rc)); /* This means fetch completed successfully */ if (rc == SQL_NO_DATA) rc = SQL_SUCCESS; } } (void) SQLFreeHandle(SQL_HANDLE_STMT, hStmt); } return rc; }

The last argument of SQLBindCol specifies the pointer to a variable where the ODBC drive will store the length of the returned column data. A value of SQL_NULL_DATA indicates that the returned column contains NULL.

(23)

Advanced Topics 23

Advanced Topics

Working with Parameters

Oftentimes an application needs to execute multiple SQL statements that are almost identical. For instance, an application inserting multiple rows into a table typically executes an INSERT statement differing only in the actual values to insert, as shown below.

Example 8 Multiple INSERT statements

INSERT INTO person VALUES 'Jackson, Michael', 'M', '1';

INSERT INTO person VALUES 'Swift, Taylor', 'F', '2';

INSERT INTO person VALUES 'Mercury, Freddie', 'F', '1';

Calling SQLExecDirect three times will get the job done, but it is not the most efficient way of handling the case as SQLExecDirect prepares and executes each statement. Since the syntax of the three statements is iden-tical, it saves an application time if the identical portion of the statements can be prepared just once, and the inser-tion of different values can be executed separately.

RDM ODBC provides that exact feature, called dynamic parameter binding. An application can prepare an SQL statement replacing the actual values with the symbols called parameter markers ('?'). Statements that include parameter markers are called parameterized statements.

Example 9 Statement with parameter markers

INSERT INTO person VALUES ?, ?, ?;

An application can perform parameter binding on each of the parameter markers of a parameterized statement by calling SQLBindParameter. SQLBindParameter lets an application specify a variable to store the value that will replace the parameter marker at statement execution time. This allows an application to dynamically change the values that go with a statement after the statement has been prepared and before it has been executed.

The following example inserts three rows into the PERSON table using dynamic parameter binding. Example 10 Dynamic parameter binding

#include "sqlrdm.h" typedef struct person { char name[36]; char gender[2]; char jobclass[2]; } PERSON; /* Three rows to insert into the PERSON table */ static const PERSON people[] = {

(24)

{"Jackson, Michael", "M", "1"}, {"Swift, Taylor", "F", "2"}, {"Mercury, Freddie", "M", "1"} }; SQLRETURN insertMultipleRows( SQLHDBC hDbc, SQLHSTMT hStmt) { SQLRETURN rc; const char *pszInsert = "INSERT INTO person VALUES ?, ?, ?"; SQLLEN cb1, cb2, cb3; uint16_t ii; /* Assumption: hStmt has already been allocated */ /* Prepare the statement once */ rc = SQLPrepare(hStmt, (SQLCHAR *) pszInsert, SQL_NTS); if (SQL_SUCCEEDED(rc)) { /* Bind parameters for the three parameter markers */ (void) SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 35, 0, szName, 0, &cb1); (void) SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 1, 0, szGender, 0, &cb2); (void) SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 1, 0, szJob, 0, &cb3); for (ii = 0; ii < RLEN(people); ii++) { /* Copy the values of the ii-th row into bound variables */ strcpy(szName, people[ii].name); strcpy(szGender, people[ii].gender); strcpy(szJobclass, people[ii].jobclass); cb1 = cb2 = cb3 = SQL_NTS; /* Execute the prepared statement using the variables that are just bound to the parameters */ rc = SQLExecute(hStmt); if (!SQL_SUCCEEDED(rc)) break; } if (SQL_SUCCEEDED(rc)) SQLEndTran(SQL_HANDLE_DBC, hDbc, SQL_COMMIT); else SQLEndTran(SQL_HANDLE_DBC, hDbc, SQL_ROLLBACK); } return rc; }

The last argument of SQLBindParameter can be used to specify the length of the actual data bound to a parameter. For fixed data types, the content of the argument is ignored. For character and binary data types, the

(25)

Advanced Topics 25 length should be expressed in the number of bytes, or SQL_NTS if the data is a null-terminated string. SQL_ NULL_DATA should be specified to indicate that NULL will be inserted into the corresponding column.

Fetching Multiple Rows at Once

SQLFetch retrieves one row of the result set at a time. It can be a significant network overhead for an appli-cation accessing the data source through a remote server. To reduce network traffic, RDM ODBC provides a way to tell its driver to retrieve the specified number of rows at a time. An application can call SQLSetStmtAttr and set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows to retrieve with a single call to SQLFetch, as shown in Example 11. If the value of the attribute is 0 (as is by default), SQLFetch

retrieves one row at a time.

When an application fetches multiple rows at a time, the variable it binds to a column must be an array whose car-dinality should match the number of rows to retrieve. For instance, if 10 rows are to be fetched at a time, the var-iable bound to an INTEGER column must be declared as "int16_t var[10]".

When one call to SQLFetch returns multiple rows, the return code of SQLFetch itself cannot represent the potentially different statuses of the retrieved rows. For instance, one of the 10 rows retrieved may have its data truncated. In a situation like that, where some (but not all) returned rows contain errors or warnings, SQLFetch returns SQL_SUCCESS_WITH_INFO. An application can call SQLGetDiagRec to retrieve all the errors and warnings contained in the result set.

An application can also tell the ODBC driver to store the statuses of all result rows into an array specified by the application. It can be done by setting the

SQL_ATTR_ROW_STATUS_PTR statement attribute to the pointer to the array of SQLUSMALLINTs. The car-dinality of the array should match the number of result rows to retrieve at once.

Finally, an application can set the SQL_ATTR_ROWS_FETCHED_PTR statement attribute to the pointer to an SQLULEN variable to have the number of returned rows stored in that variable.

The following example illustrates how to retrieve 10 rows at a time from the PERSON table. Example 11 Retrieving multiple rows

#include "sqlrdm.h" #define ROW_ARRAY_SIZE 10 /* Fetch 10 rows at a time */ SQLRETURN runDbOperations( SQLHDBC hDbc) { SQLRETURN rc; SQLHSTMT hStmt; const char *pszStmt = "SELECT * FROM person"; char szName[ROW_ARRAY_SIZE][36]; char szGender[ROW_ARRAY_SIZE][2]; char szJob[ROW_ARRAY_SIZE][2]; SQLLEN cb1[ROW_ARRAY_SIZE], cb2[ROW_ARRAY_SIZE], cb3[ROW_ARRAY_SIZE];

(26)

SQLULEN ucbFetchedRows; SQLULEN ii; /* Allocate a statement handle */ rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt); if (SQL_SUCCEEDED(rc)) { /* Open the NSFAWARDS database first */ rc = SQLExecDirect(hStmt, (SQLCHAR *) "OPEN nsfawards", SQL_NTS); if (SQL_SUCCEEDED(rc)) { /* Specify to retrieve 10 rows at a time */ (void) SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) ROW_ARRAY_SIZE, 0); /* Specify the storage for the # of fetched rows */ (void) SQLSetStmtAttr(hStmt, SQL_ATTR_ROWS_FETCHED_PTR, (SQLPOINTER) &ucbFetchedRows, 0); /* Execute a SELECT statement */ rc = SQLExecDirect(hStmt, (SQLCHAR *) pszStmt, SQL_NTS); if (SQL_SUCCEEDED(rc)) { /* Bind all the 3 columns; note that the length argument (arg. 5) is the size of one element of the bound variable */ (void) SQLBindCol(hStmt 1, SQL_C_CHAR, szName, sizeof(szName[0]), cb1); (void) SQLBindCol(hStmt 2, SQL_C_CHAR, szGender, sizeof(szGender[0]), &cb2); (void) SQLBindCol(hStmt 3, SQL_C_CHAR, szJob, sizeof(szJob[0]), &cb3); while ((rc = SQLFetch(hStmt)) != SQL_NO_DATA) { /* ucbFetchedRows = # of rows */ for (ii = 0; ii < ucbFetchedRows; ii++) { if (cb1[ii] != SQL_NULL_DATA) printf("Name = %s\n", szName[ii]); if (cb2[ii] != SQL_NULL_DATA) { printf("Gender = %s\n", szGender[ii]); } if (cb3[ii] != SQL_NULL_DATA) { printf("Jobclass = %s\n", szJob[ii]); } } } } } (void) SQLFreeHandle(SQL_HANDLE_STMT, hStmt);

(27)

Advanced Topics 27 } return rc; }

With an ODBC driver that supports scrollable cursors, an application can call SQLFetchScroll to scroll the result set back and forth. The RDM ODBC Driver only supports forward-only cursors. While it provides

SQLFetchScroll, the function only accepts SQL_FETCH_NEXT as the fetch type. It means SQLFetch and SQLFetchScroll currently work the same way in RDM ODBC.

Retrieving Data in Parts

Binding columns using SQLBindCol allows an application to retrieve the content of a given column in one shot. It works well when the column contains fixed-length data, such as an integer, floating-point number and

date/time where the size of the data is known before fetching the column.

On the other hand, it may not always be efficient, desired or even possible to retrieve the whole content of a col-umn that contains string or binary data. In that case, an application can retrieve the data in parts by calling SQLGetData. SQLGetData works similarly to SQLBindCol except that SQLGetData is called after the row is fetched, not before. This allows an application to call SQLGetData on the same column multiple times in order to retrieve the column data by the size specified with the function. For instance, if an application calls SQLGet-Data with the size of data set to 100 on a column that contains a 800-character string, it will need to call the func-tion 8 times on that column in order to read in the whole data.

SQLGetData returns SQL_SUCCESS_WITH_INFO (SQLSTATE "01S02" – data truncated) if there is more data to be retrieved on that column. When it has retrieved the last chunk of data, it returns SQL_SUCCESS. An application can use this mechanism to detect the last read of the data. If called after the last part of the data has been retrieved, SQLGetData returns SQL_NO_DATA.

The following example illustrates how an application can retrieve a character data in parts using SQLGetData.

#include "sqlrdm.h" #define MAX_FETCH_SIZE 100 SQLRETURN getCharData( SQLHSTMT hStmt) { SQLRETURN rc; char szData[100]; /* Assumption; hStmt is already allocated */ rc = SQLExecDirect(hStmt, (SQLCHAR *) "SELECT col1 FROM vartable", SQL_NTS); if (SQL_SUCCEEDED(rc)) { while ((rc = SQLFetch(hStmt)) != SQL_NO_DATA) { do {

(28)

rc = SQLGetData(hStmt, 1, SQL_C_CHAR, szData, MAX_FETCH_SIZE, NULL); if (SQL_SUCCEEDED(rc)) printf("%s", szData); } while (rc == SQL_SUCCESS_WITH_INFO); printf("\n"); } } return rc; }

This mechanism can only be used to retrieve character or binary data in parts. If called on a fixed-length column more than once, SQLGetData returns SQL_NO_DATA except for the first call.

(29)

RDM ODBC API Reference

The topics in this section describe each ODBC function in alphabetical order. Each function is defined as a C pro-gramming language function. Descriptions include the following:

l Purpose

l Conformance

l Syntax

l Arguments

l Return values

All of the following APIs conform to the ODBC 3.51 specification unless otherwise noted. RDM specific exten-sions to the APIs will be documented in the Diagnostics or Comments sections of the page. If function does not contain a Diagnostics or Comments section, the MSDN documentation applies to that specific function.

Unicode API Functions

The RDM ODBC API supports both ANSI and Unicode versions of all functions that accept pointers to character strings or SQLPOINTER in their arguments. The Unicode functions are implemented as functions with a suffix of "W", such as SQLExecDirectW and SQLGetInfoW.

Function Reference Pages

This section contains topics for the following functions: Function Name Description

SQLAllocHandle Allocates an environment, connection, statement, or descriptor handle

SQLBindCol Binds application data buffers to columns in the result set.

SQLBindParameter Binds a buffer to a parameter marker in an SQL statement.

SQLCancel Cancels the processing on a statement.

SQLCloseCursor Closes a cursor that has been opened on a statement and discards pending results.

SQLColAttribute Returns descriptor information for a column in a result set. Descriptor information is returned as a character string, a 32-bit descriptor-dependent value, or an integer value.

SQLColumns Returns the list of column names in specified tables. The driver returns this infor-mation as a result set on the specified StatementHandle.

SQLConnect Establishes connections to RDM databases via RDM ODBC.

SQLCopyDesc Copies descriptor information from one descriptor handle to another.

SQLDescribeCol Retrieves the basic result data set for a column

SQLDescribeParam Retrieves description of a parameter marker

SQLDescribeStmt Retrieves the type of the SQL statement executed on the handle.

SQLDisconnect Closes the connection associated with a specified handle

SQLDriverConnect Establishes connections to RDM databases via RDM ODBC. Accepts more con-nection options than SQLConnect.

(30)

Function Name Description

SQLExecDirect Prepares and executes an SQL statement

SQLExecute Executes a previously prepared SQL statement

SQLExtendedTran Performs a transaction operation

SQLFetch Fetches data from a bound column to an application variable

SQLFetchScroll Fetches the specified rowset of data from the result set and returns data for all bound columns.

SQLForeignKeys SQLForeignKeys can return a list of foreign keys in the specified table (columns in the specified table that refer to primary keys in other tables) or a list of foreign keys in other tables that refer to the primary key in the specified table. The driver returns each list as a result set on the specified statement.

SQLFreeHandle Frees resources associated with a specific environment, connection, statement, or descriptor handle.

SQLFreeStmt Stops processing associated with a specific statement, closes any open cursors associated with the statement, discards pending results, or, optionally, frees all resources associated with the statement handle.

SQLGetConnectAttr Returns the current setting of a connection attribute.

SQLGetCursorName Retrieves the cursor name of an SQL statement handle

SQLGetData Retrieves data for a single column in the result set. It can be called multiple times to retrieve variable-length data in parts.

SQLGetDescField Returns the current setting or value of a single field of a descriptor record.

SQLGetDescRec Returns the current settings or values of multiple fields of a descriptor record. The fields returned describe the name, data type, and storage of column or parameter data.

SQLGetDiagField Retrieves current field value of a status record

SQLGetDiagRec Retrieves current values of several common fields of a status record

SQLGetEnvAttr Returns the current setting of an environment attribute.

SQLGetFunctions Returns information about whether a driver supports a specific ODBC function.

SQLGetInfo Returns general information about the driver and data source associated with a con-nection.

SQLGetStmtAttr Returns the current setting of a statement attribute.

SQLGetTypeInfo Returns information about data types supported by the data source.

SQLMoreResults Determines whether more results are available on a statement containing SELECT, UPDATE, INSERT, or DELETE statements and, if so, initializes proc-essing for those results.

SQLNativeSql Returns the SQL string as modified by the driver. SQLNativeSql does not execute the SQL statement.

SQLNumParams Determines the number of parameters in a prepared statement

SQLNumResultCols Returns the number of columns in a result set.

SQLParamData SQLParamData is used together with SQLPutData to supply parameter data at statement execution time, and with SQLGetData to retrieve streamed output parameter data.

SQLPrepare Prepares an SQL string for execution.

SQLPrimaryKeys Returns the column names that make up the primary key for a table. The driver returns the information as a result set. This function does not support returning pri-mary keys from multiple tables in a single call.

(31)

RDM ODBC API Reference 31 Function Name Description

SQLProcedureColumnsReturns the parameter and column information about the specified procedures.

SQLProcedures Returns the list of procedure names stored in a specific data source. Procedure is a generic term used to describe an executable object, or a named entity that can be invoked using input and output parameters.

SQLPutData Allows an application to send data for a parameter or column to the driver at state-ment execution time. This function can be used to send character or binary data values in parts to a column with a character, binary, or data source–specific data type (for example, parameters of the SQL_LONGVARBINARY or SQL_LONG-VARCHAR types).

SQLRowCount Gets row count in a table following an INSERT, UPDATE, or DELETE

SQLSetConnectAttr Sets attributes that govern aspects of connections.

SQLSetCursorName Associates a cursor name with an active statement.

SQLSetDescField Sets the value of a single field of a descriptor record.

SQLSetDescRec Sets multiple descriptor fields that affect the data type and buffer bound to a column or parameter data.

SQLSetEnvAttr Sets attributes that govern aspects of environments.

SQLSetError Registers user-defined status/error handling functions.

SQLSetStmtAttr Sets attributes related to a statement

SQLSpecialColumns Returns the information about the set of columns that uniquely identifies a row in the table.

SQLStatistics Retrieves a list of statistics about a single table and the indexes associated with the table. The driver returns the information as a result set.

SQLTables Returns the list of table, catalog, or schema names, and table types, stored in a spe-cific data source.

(32)

SQLAllocHandle

Allocates an environment, connection, statement, or descriptor handle.

Conformance

Version Introduced: ODBC 3.0 Standards Compliance: ISO 92

Syntax

SQLRETURN SQL_API SQLAllocHandle( SQLSMALLINT HandleType, SQLHANDLE InputHandle, SQLHANDLE *OutputHandle)

Arguments

HandleType (input) The type of handle to be allocated by SQLAllocHandle. Must be one of the fol-lowing values:

l SQL_HANDLE_ENV

l SQL_HANDLE_DBC

l SQL_HANDLE_STMT

l SQL_HANDLE_DESC

InputHandle (input) The input handle in whose context the new handle is to be allocated. If Han-dleType is SQL_HANDLE_ENV, this is SQL_NULL_HANDLE. If HanHan-dleType is SQL_HANDLE_DBC, this must be an environment handle, and if it is SQL_HAN-DLE_STMT or SQL_HANDLE_DESC, it must be a connection handle.

OutputHandlePtr (output) Pointer to a buffer in which to return the handle to the newly allocated data structure.

Required Headers

#include "sql.h"

Libraries

Library Name Description

rdmrdbc11 RDM ODBC API Library

See Library Naming Conventions section for full library name and a list of library dependencies.

Returns

(33)

RDM ODBC API Reference 33 SQL_SUCCESS Function completed successfully. The application calls

SQLGet-DiagField to retrieve additional information from the header record. SQL_SUCCESS_WITH_INFO Function completed successfully, possibly with a nonfatal error

(warn-ing). The application calls SQLGetDiagRec or SQLGetDiagField to retrieve additional information.

SQL_ERROR Function failed. The application calls SQLGetDiagRec or SQLGet-DiagField to retrieve additional information. The contents of any out-put arguments to the function are undefined.

SQL_INVALID_HANDLE Function failed due to an invalid environment, connection, statement, or descriptor handle. This indicates a programming error. No additional information is available from SQLGetDiagRec or

SQLGet-DiagField. This code is returned only when the handle is a null pointer or is the wrong type, such as when a statement handle is passed for an argument that requires a connection handle.

Comments

This function allocates an environment, connection, or SQL statement handle and its associated resources. RDM does not require you to allocate an environment handle before allocating connection or statement handles, and doing so currently has no effect. The environment handle type is included only for ODBC compatibility.

(34)

SQLBindCol

Binds application data buffers to columns in the result set.

Conformance

Syntax

SQLRETURN SQL_API SQLBindCol( SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLSMALLINT TargetType, SQLPOINTER TargetValue, SQLLEN BufferLength, SQLLEN *StrLen_or_Ind)

Arguments

StatementHandle (input) Statement handle.

ColumnNumber (input) The column's location in the result data set. Columns are numbered sequentially from left to right, starting with 1, as they appear in the result data set.

TargetType (input) The data type of the value buffer that the column data being retrieved is to be stored in.

TargetValue (output) A pointer to a location in memory where the driver is to store column data when it is retrieved (fetched) from the result data set or where the application is to store col-umn data that is to be written to a data source with a positioned UPDATE or DELETE operation.

BufferLength (input) The size of the buffer.

StrLen_or_Ind (output) A pointer to a location in memory where this function is to store either the size of the data value associated with the column or a special indicator value associated with the column data.

Required Headers

#include "sql.h"

Libraries

(35)

Returns

Return Code Description

SQL_SUCCESS Function completed successfully. The application calls SQLGet-DiagField to retrieve additional information from the header record. SQL_SUCCESS_WITH_INFO Function completed successfully, possibly with a nonfatal error

SQLGet-DiagField. This code is returned only when the handle is a null pointer or is the wrong type, such as when a statement handle is passed for an argument that requires a connection handle.

Comments

This function binds application data buffers to columns in the result set. For more information, reference MSDN documentation for SQLBindCol.

(36)

SQLBindParameter

Binds a buffer to a parameter marker in an SQL statement.

Conformance

Version Introduced: ODBC 2.0 Standards Compliance: ODBC

Syntax

SQLRETURN SQLBindParameter( SQLHSTMT StatementHandle, SQLUSMALLINT ParameterNumber, SQLSMALLINT InputOutputType, SQLSMALLINT ValueType, SQLSMALLINT ParameterType, SQLULEN ColumnSize, SQLSMALLINT DecimalDigits, SQLPOINTER ParameterValuePtr, SQLLEN BufferLength, SQLLEN * StrLen_or_IndPtr)

Arguments

ParameterNumber (input) Parameter number, ordered sequentially in increasing parameter order, start-ing at 1.

InputOutputType (input) The type of the parameter. ValueType (input) The C data type of the parameter. ParameterType (input) The size of the buffer.

ColumnSize (input) The size of the column or expression of the corresponding parameter marker. DecimalDigits (input) The decimal digits of the column or expression of the corresponding parameter

marker. ParameterValuePtr (deferred

input)

A pointer to a buffer for the parameter's data.

BufferLength (input/output)Length of the ParameterValuePtr buffer in bytes. StrLen_or_IndPtr (deferred

input)

A pointer to a buffer for the parameter's length.

Required Headers

(37)

Libraries

Returns

SQLGet-DiagField. This code is returned only when the handle is a null pointer or is the wrong type, such as when a statement handle is passed for an argument that requires a connection handle. For more information, reference MSDN documentation for SQLBindParameter.

(38)

SQLCancel

Cancels the processing on a statement.

Conformance

Syntax

SQLRETURN SQL_API SQLCancel(

SQLHSTMT StatementHandle)

Arguments

Required Headers

#include "sql.h"

Libraries

Returns

SQLGet-DiagField. This code is returned only when the handle is a null pointer or is the wrong type, such as when a statement handle is

(39)

RDM ODBC API Reference 39 passed for an argument that requires a connection handle.

Comments

On RDM, SQLCancel supports the following type of processing. l A function on a statement that needs data.

The following types of processing are not supported due to the driver limitations. l A function running asynchronously on a statement.

l A function running on the statement on another thread.

(40)

SQLCloseCursor

Closes a cursor that has been opened on a statement and discards pending results.

Conformance

Syntax

SQLRETURN SQL_API SQLCloseCursor(

SQLHSTMT StatementHandle)

Arguments

Required Headers

#include "sql.h"

Libraries

Returns

SQLGet-DiagField. This code is returned only when the handle is a null pointer or is the wrong type, such as when a statement handle is

(41)

RDM ODBC API Reference 41 passed for an argument that requires a connection handle.

(42)

SQLColAttribute

Returns descriptor information for a column in a result set. Descriptor information is returned as a character string, a 32-bit descriptor-dependent value, or an integer value.

Conformance

Syntax

SQLRETURN SQLColAttribute ( SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber, SQLUSMALLINT FieldIdentifier, SQLPOINTER CharacterAttributePtr, SQLSMALLINT BufferLength, SQLSMALLINT * StringLengthPtr, SQLPOINTER NumericAttributePtr)

Arguments

ColumnNumber (input) The number of the record in the IRD from which the field value is to be retrieved. This argument corresponds to the column number of result data, ordered sequentially in increasing column order, starting at 1. Columns can be described in any order. Column 0 can be specified in this argument, but all values except SQL_DESC_TYPE and SQL_DESC_OCTET_LENGTH will return undefined values.

FieldIdentifier (input) The descriptor handle. This handle defines which field in the IRD should be que-ried (for example, SQL_COLUMN_TABLE_NAME).

CharacterAttributePtr (output) Pointer to a buffer in which to return the value in the FieldIdentifier field of the ColumnNumber row of the IRD, if the field is a character string. Otherwise, the field is unused. If CharacterAttributePtr is NULL, StringLengthPtr will still return the total number of bytes (excluding the null-termination character for character data) available to return in the buffer pointed to by

Char-acterAttributePtr.

BufferLength (input) If FieldIdentifier is an ODBC-defined field and CharacterAttributePtr points to a character string or binary buffer, this argument should be the length of racterAttributePtr. If FieldIdentifier is an ODBC-defined field and *Cha-racterAttributePtr is an integer, this field is ignored. If the

*CharacterAttributePtr is a Unicode string (when calling SQLColAttributeW), the BufferLength argument must be an even number. If FieldIdentifier is a driver-defined field, the application indicates the nature of the field to the Driver Manager by setting the BufferLength argument. BufferLength can have the fol-lowing values:

l If CharacterAttributePtr is a pointer to a pointer, BufferLength should