18.2.2 Referencing data

This subsection explains how to reference data by way of an example. The example provided here uses a cursor to retrieve rows. The following figure shows how to retrieve rows using a cursor.

Figure 18‒5: How to retrieve rows using a cursor

The processing steps are explained below.

Organization of this subsection

(1) Allocating a statement handle
(2) Preprocessing an SQL statement
(3) Acquiring the number of retrieval result columns
(4) Acquiring information about the retrieval result columns
(5) Associating the retrieval result columns
(6) Executing the SQL statement (opens a cursor)
(7) Using the cursor to retrieve rows
(8) Converting the retrieved data
(9) Closing the cursor
(10) Releasing the statement handle

(1) Allocating a statement handle

Before you execute an SQL statement, you must use a_rdb_SQLAllocStmt() to allocate a statement handle. When a statement handle is allocated successfully, a_rdb_RC_SQL_SUCCESS is returned. The following shows an example of statement handle allocation.

Example of statement handle allocation

void  *hStmt ;                           /* Statement handle address */
 
/* Allocate a statement handle */
rtnc = a_rdb_SQLAllocStmt(hCnct,
                          &hStmt,
                          NULL) ;

For details about a_rdb_SQLAllocStmt(), see 19.4.1 a_rdb_SQLAllocStmt() (allocate a statement handle).

To Page Top

(2) Preprocessing an SQL statement

Next, you allocate the statement handle acquired in (1) Allocating a statement handle to an SQL statement. To do this, you use a_rdb_SQLPrepare() to preprocess the SQL statement. The following shows an example of preprocessing a SELECT statement.

Example of preprocessing a SELECT statement

/* Preprocess a SELECT statement */
rtnc = a_rdb_SQLPrepare(hCnct,
                        hStmt,
                        "SELECT C1,C2,C3 FROM T1",
                        NULL) ;

For details about a_rdb_SQLPrepare(), see 19.4.14 a_rdb_SQLPrepare() (preprocess an SQL statement).

To Page Top

(3) Acquiring the number of retrieval result columns

If the number of retrieval result columns (the number of columns that are output as the retrieval results) is not known at the time of application program creation, such as when SQL statements will be executed dynamically, you use a_rdb_SQLNumResultCols() to acquire the number of retrieval result columns. The following shows an example of acquiring the number of retrieval result columns.

Example of acquiring the number of retrieval result columns

/* Acquire the number of retrieval result columns */
rtnc = a_rdb_SQLNumResultCols(hCnct,
                              hStmt,
                              &colCount,    /* Number of columns */
                              NULL) ;

For details about a_rdb_SQLNumResultCols(), see 19.4.13 a_rdb_SQLNumResultCols() (acquire the number of retrieval result columns).

To Page Top

(4) Acquiring information about the retrieval result columns

If column information, such as the column names, data types, and data lengths, is not known at the time of application program creation, such as when SQL statements will be executed dynamically, you use a_rdb_SQLDescribeCols() to acquire information about the retrieval result columns. You can acquire the following information by using a_rdb_SQLDescribeCols():

Column names of the retrieval result columns
Data types of the retrieval result columns
Maximum numbers of elements in the retrieval result columns
Data lengths of the retrieval result columns

The following shows an example of acquiring information about the retrieval result columns.

Example of acquiring information about the retrieval result columns

/* Acquire information about the retrieval result columns */
rtnc = a_rdb_SQLDescribeCols(hCnct,
                             hStmt,
                             colCount,       /* Number of retrieval result columns */
                             &(colInf[0]),   /* Information return area for all retrieval result columns */
                             NULL) ;

For details about a_rdb_SQLDescribeCols(), see 19.4.6 a_rdb_SQLDescribeCols() (acquire information about the retrieval result columns).

To Page Top

(5) Associating the retrieval result columns

You must associate the retrieval result columns with the area for storing values retrieved from the retrieval result columns. When you use a_rdb_SQLFetch() to manipulate the cursor, the values in the retrieval result columns are stored automatically in the associated area.

You use a_rdb_SQLBindCols() to associate retrieval result columns. The following shows an example of associating retrieval result columns.

Example of associating retrieval result columns

/* Associate retrieval result columns */
rtnc = a_rdb_SQLBindCols(hCnct,
                         hStmt,
                         colCount,           /* Number of retrieval result columns */
                         &(colInf[0]),       /* Assigned storage area for all columns */
                         NULL) ;

For details about a_rdb_SQLBindCols(), see 19.4.3 a_rdb_SQLBindCols() (associate retrieval result columns).

To Page Top

(6) Executing the SQL statement (opens a cursor)

You use a_rdb_SQLExecute() to execute the preprocessed SQL statements. Specify in the argument of a_rdb_SQLExecute() the statement handle for the SQL statement that is to be executed. If the SQL statement executes successfully, the value a_rdb_RC_SQL_SUCCESS is returned and a cursor opens. The following shows an example of executing an SQL statement.

Example of executing an SQL statement

/* Execute an SQL statement */
rtnc = a_rdb_SQLExecute(hCnct,
                        hStmt,
                        NULL) ;

For details about a_rdb_SQLExecute(), see 19.4.9 a_rdb_SQLExecute() (execute a preprocessed SQL statement).

To Page Top

(7) Using the cursor to retrieve rows

You use a_rdb_SQLFetch() to retrieve rows by using the cursor that has been opened by executing the SQL statement. If row retrieval is successful, the value a_rdb_RC_SQL_SUCCESS is returned. The following shows an example of using a cursor to retrieve rows.

Example of using a cursor to retrieve rows

/* Using cursor to retrieve rows */
rtnc = a_rdb_SQLFetch(hCnct,
                      hStmt,
                      NULL) ;

For details about a_rdb_SQLFetch(), see 19.4.10 a_rdb_SQLFetch() (fetch a row).

To Page Top

(8) Converting the retrieved data

If the fetched data's SQL data type is DECIMAL, BINARY, VARBINARY, DATE, TIME, or TIMESTAMP, you can use a CLI function to convert it to character string data supported by C or C++. The following shows an example of converting DECIMAL-type data.

Example of data conversion

#define PRECISION 6
#define SCALE 3
 
unsigned char data_decimal[4];
char data_char[PRECISION+4];
 
/* Convert DECIMAL-type data */
rtnc = a_rdb_CNV_DECIMALchar(data_decimal,  /* Start address of output data */
                            PRECISION,      /* Precision of output data */
                            SCALE,          /* Scaling of output data */
                            data_char,      /* Address of area for storing converted data */
                            (PRECISION+4),  /* Length of area for storing converted data */
                            NULL);

To convert DECIMAL-type data, you use a_rdb_CNV_DECIMALchar(). For details about a_rdb_CNV_DECIMALchar(), see 19.5.9 a_rdb_CNV_DECIMALchar() (convert DECIMAL-type data).
To convert BINARY-type data, you use a_rdb_CNV_BINARYchar(). For details about a_rdb_CNV_BINARYchar(), see 19.5.7 a_rdb_CNV_BINARYchar() (convert BINARY-type data).
To convert VARBINARY-type data, you use a_rdb_CNV_VARBINARYchar(). For details about a_rdb_CNV_VARBINARYchar(), see 19.5.12 a_rdb_CNV_VARBINARYchar() (convert VARBINARY-type data).
To convert DATE-type data, you use a_rdb_CNV_DATEchar(). For details about a_rdb_CNV_DATEchar(), see 19.5.8 a_rdb_CNV_DATEchar() (convert DATE-type data).
To convert TIME-type data, you use a_rdb_CNV_TIMEchar(). For details about a_rdb_CNV_TIMEchar(), see 19.5.10 a_rdb_CNV_TIMEchar() (convert TIME-type data).
To convert TIMESTAMP-type data, you use a_rdb_CNV_TIMESTAMPchar(). For details about a_rdb_CNV_TIMESTAMPchar(), see 19.5.11 a_rdb_CNV_TIMESTAMPchar() (convert TIMESTAMP-type data).

To Page Top

(9) Closing the cursor

You use a_rdb_SQLCloseCursor() to close the cursor. The following shows an example of closing the cursor.

Example of closing the cursor

/* Close the cursor */
rtnc = a_rdb_SQLCloseCursor(hCnct,
                            hStmt,
                            NULL) ;

For details about a_rdb_SQLCloseCursor(), see 19.4.5 a_rdb_SQLCloseCursor() (close the cursor).

To Page Top

(10) Releasing the statement handle

You use a_rdb_SQLFreeStmt() to release the allocated statement handle. The following shows an example of releasing the statement handle.

Example of releasing the statement handle

/* Release the statement handle */
rtnc = a_rdb_SQLFreeStmt(hCnct,
                         hStmt,
                         NULL) ;

Important: The statement handle is released any time COMMIT or ROLLBACK is executed. In such a case, do not execute a_rdb_SQLFreeStmt().

For details about a_rdb_SQLFreeStmt(), see 19.4.11 a_rdb_SQLFreeStmt() (release a statement handle).

To Page Top