Database Operations in ODBC
There are basic methods for using ODBC in TimesTen.
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 withSQLPrepare
. After the application is done with the results, they can be discarded andSQLExecute
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 theSELECT
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 theSELECT
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 eitherSQL_CLOSE
,SQL_DROP
,SQL_UNBIND
, orSQL_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
orSQLExecDirect
. 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. ForUPDATE
,INSERT
, andDELETE
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 aboutSQLRowCount
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); }