Database Reference
In-Depth Information
OCIStmtPrepare2
(svc, (OCIStmt **)&stm, err, sql, strlen(sql), NULL, 0, OCI_NTV_SYNTAX,
OCI_DEFAULT);
OCIDefineByPos
(stm, &def, err, 1, val, sizeof(val), SQLT_STR, 0, 0, 0, OCI_DEFAULT);
for (i=1 ; i<=10000 ; i++)
{
OCIBindByPos(stm, &bnd, err, 1, &i, sizeof(i), SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT);
OCIStmtExecute(svc, stm, err, 0, 0, 0, 0, OCI_DEFAULT);
if (r = OCIStmtFetch2(stm, err, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT) == OCI_SUCCESS)
{
// do something with data...
}
}
OCIStmtRelease
(stm, err, NULL, 0, OCI_DEFAULT);
OCI not only enables full control of the cursors but also supports client-side statement caching. To use it, it's
necessary only to enable statement caching and use the
OCIStmtPrepare2
and
OCIStmtRelease
functions (as the
previous examples do). Cursors are added to the cache when the
OCIStmtRelease
function is called. Then, when
a new cursor is created through the
OCIStmtPrepare2
function, the cache is consulted to find out whether a SQL
statement with the same text is present in it.
Different methods exist to enable statement caching. Basically, though, it's only a matter of specifying it when
the session is opened or retrieved from a pool. For example, if a nonpooled session is opened through the
OCILogon2
function, it's necessary to specify the
OCI_LOGON2_STMTCACHE
value as the mode.
OCILogon2(env, err, &svc, username, strlen(username), password, strlen(password),
dbname, strlen(dbname),
OCI_LOGON2_STMTCACHE
)
By default, the size of the cache is 20. The following code snippet shows how to change it to 50 by setting the
OCI_ATTR_STMTCACHESIZE
attribute on the service context. Note that setting this attribute to 0 disables
statement caching.
ub4 size =
50
;
OCIAttrSet(svc, OCI_HTYPE_SVCCTX, &size, 0,
OCI_ATTR_STMTCACHESIZE
, err);
The C code examples provided in this section are excerpts from the
ParsingTest1.c
,
ParsingTest2.c
, and
ParsingTest3.c
files implementing test case 1, 2, and 3, respectively.
JDBC
java.sql.Statement
is the basic class provided by JDBC to execute SQL statements. As shown in Table
12-1
, it's
not unlikely that parsing problems will arise when using it. In fact, it doesn't support bind variables, reutilization of
cursors, and client-side statement caching. Basically, it's possible to implement only test case 1 with it. The following
code snippet demonstrates this:
sql = "SELECT pad FROM t WHERE val = ";
for
(int i=0 ; i<10000; i++)
{
statement = connection.
createStatement
();
resultset = statement.
executeQuery
(sql + Integer.toString(i));
if (resultset.
next
())
