Database Operations in ODBC

ODBC Functions to Execute SQL Statements

The SQLExecute and SQLExecDirect ODBC functions are used to execute SQL statements.

SQLExecute: Executes a statement that has been prepared with SQLPrepare. After the application is done with the results, they can be discarded and SQLExecute can be run again using different parameter values.

This is typically used for DML statements with bind parameters, or statements that are being executed more than once.
SQLExecDirect: Prepares and executes a statement.

This is typically used for DDL statements or for DML statements that would execute only a few times and without bind parameters.

Refer to ODBC API reference documentation for details about these functions.

Steps to Prepare and Execute Queries and Work With Cursors in ODBC

There are ODBC functions used to prepare and execute queries and work with cursors.

Note:

In TimesTen, any operation that ends your transaction, such as a commit or rollback, closes all cursors associated with the connection.

In ODBC, a cursor is always associated with a result set. This association is made by the ODBC driver. The application can control cursor characteristics, such as the number of rows to fetch at one time, using SQLSetStmtAttr attributes documented in Attribute Support for ODBC 3.5 SQLSetStmtAttr and SQLGetStmtAttr. The steps involved in executing a query typically include the following.

Use SQLPrepare to prepare the SELECT statement for execution.
Use SQLBindParameter, if the statement has parameters, to bind each parameter to an application address. See SQLBindParameter Function.
Call SQLBindCol to assign the storage and data type for a column of results, binding column results to local variable storage in your application.
Call SQLExecute to execute the SELECT statement. See ODBC Functions to Execute SQL Statements.
Call SQLFetch to fetch the results. Specify the statement handle.
Call SQLFreeStmt to free the statement handle. Specify the statement handle and either SQL_CLOSE, SQL_DROP, SQL_UNBIND, or SQL_RESET_PARAMS.

Refer to ODBC API reference documentation for details on these ODBC functions. Examples are shown throughout this chapter and in the TimesTen sample applications. See About TimesTen Quick Start and Sample Applications.

Note:

By default (when connection attribute PrivateCommands=0), TimesTen shares prepared statements between connections, so subsequent prepares of the same statement on different connections execute very quickly.

Creating a Table in ODBC

You can create a table in ODBC.

This example creates a table, NameID, with two columns: CustID and CustName. The table maps character names to integer identifiers.

#include <timesten.h>
SQLRETURN rc;
SQLHSTMT hstmt;
...
rc = SQLExecDirect(hstmt, (SQLCHAR*)
     "CREATE TABLE NameID (CustID INTEGER, CustName VARCHAR(50))",
     SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
     ... /* handle error */

Preparing and Executing a Query in ODBC

This example prepares and executes a query.

Error checking has been omitted to simplify the example. In addition to ODBC functions mentioned previously, this example uses SQLNumResultCols to return the number of columns in the result set, SQLDescribeCol to return a description of one column of the result set (column name, type, precision, scale, and nullability), and SQLBindCol to assign the storage and data type for a column in the result set. These are all described in detail in ODBC API reference documentation.

#include <timesten.h>

SQLHSTMT hstmt;
SQLRETURN rc;
int i;
SQLSMALLINT numCols;
SQLCHAR colname[32];
SQLSMALLINT colnamelen, coltype, scale, nullable;
SQLULEN collen [MAXCOLS];
SQLLEN outlen [MAXCOLS];
SQLCHAR* data [MAXCOLS];

/* other declarations and program set-up here */

/* Prepare the SELECT statement */
rc = SQLPrepare(hstmt,
(SQLCHAR*) "SELECT * FROM EMP WHERE AGE>20",
SQL_NTS);
/* ... */

/* Determine number of columns in result rows */
rc = SQLNumResultCols(hstmt, &numCols);

/* ... */

/* Describe and bind the columns */
for (i = 0; i < numCols; i++) {
    rc = SQLDescribeCol(hstmt,
         (SQLSMALLINT) (i + 1),
         colname,(SQLSMALLINT)sizeof(colname), &colnamelen, &coltype, &collen[i],
         &scale, &nullable);

    /* ... */

   data[i] = (SQLCHAR*) malloc (collen[i] +1);  //Allocate space for column data.
   rc = SQLBindCol(hstmt, (SQLSMALLINT) (i + 1),
                   SQL_C_CHAR, data[i],
                   COL_LEN_MAX, &outlen[i]);

   /* ... */

}
/* Execute the SELECT statement */
rc = SQLExecute(hstmt);

/* ... */

/* Fetch the rows */
if (numCols > 0) {
  while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS ||
          rc == SQL_SUCCESS_WITH_INFO) {
    /* ... "Process" the result row */
  } /* end of for-loop */
  if (rc != SQL_NO_DATA_FOUND)
    fprintf(stderr,
            "Unable to fetch the next row\n");

/* Close the cursor associated with the SELECT statement */
  rc = SQLFreeStmt(hstmt, SQL_CLOSE);
}

Committing Changes to the Database in ODBC

You can either autocommit or manually commit changes to the database. You can also disable autocommit and manually commit.

Autocommit is enabled by default (according to the ODBC specification), so that any DML change you make, such as an update, insert, or delete, is committed automatically. It is recommended, however, that you disable this feature and commit (or roll back) your changes explicitly. Use the SQL_AUTOCOMMIT option in a SQLSetConnectOption call to accomplish this:

rc = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);

With autocommit disabled, you can commit or roll back a transaction using the SQLTransact ODBC function, such as in the following example to commit:

rc = SQLTransact(henv, hdbc, SQL_COMMIT);

Refer to ODBC API reference documentation for details about these functions. Refer to Transaction Overview in Oracle TimesTen In-Memory Database Operations Guide.

Note:

Autocommit mode applies only to the top-level statement executed by SQLExecute or SQLExecDirect. There is no awareness of what occurs inside the statement, and therefore no capability for intermediate autocommits of nested operations.
All open cursors on the connection are closed upon transaction commit or rollback in TimesTen.
The SQLRowCount function can be used to return information about SQL operations. For UPDATE, INSERT, and DELETE statements, the output argument returns the number of rows affected. See Retrieving Information About Cache Groups. Refer to ODBC API reference documentation for general information about SQLRowCount and its arguments.

This example prepares and executes a statement to give raises to selected employees, then manually commits the changes. Assume autocommit has been previously disabled.

update_example(SQLHDBC hdbc)
{
  SQLCHAR*      stmt_text;
  SQLHSTMT      hstmt;
  SQLINTEGER    raise_pct;
  char          hiredate_str[30];
  SQLLEN        hiredate_len;
  SQLLEN        numrows;
 
  /* allocate a statement handle */
  SQLAllocStmt(hdbc, &hstmt);
 
  /* prepare an update statement to give raises to employees hired before a
   * given date */
  stmt_text = (SQLCHAR*)
    "update employees "
    "set salary = salary * ((100 + :raise_pct) / 100.0) "
    "where hire_date < :hiredate";
  SQLPrepare(hstmt, stmt_text, SQL_NTS);
 
  /* bind parameter 1 (:raise_pct) to variable raise_pct */
  SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
                   SQL_DECIMAL, 0, 0, (SQLPOINTER)&raise_pct, 0, 0);
 
  /* bind parameter 2 (:hiredate) to variable hiredate_str */
  SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                   SQL_TIMESTAMP, 0, 0, (SQLPOINTER)hiredate_str,
                   sizeof(hiredate_str), &hiredate_len);
 
  /* set parameter values to give a 10% raise to employees hired before
   * January 1, 1996. */
  raise_pct = 10;
  strcpy(hiredate_str, "1996-01-01");
  hiredate_len = SQL_NTS;
 
  /* execute the update statement */
  SQLExecute(hstmt);
 
  /* print the number of employees who got raises  */
  SQLRowCount(hstmt, &numrows);
  printf("Gave raises to %d employees.\n", numrows);
 
  /* drop the statement handle */
  SQLFreeStmt(hstmt, SQL_DROP);

  /* commit the changes */
  SQLTransact(henv, hdbc, SQL_COMMIT);

}