Analyzing Dynamic SQL in CLI traces

CLI Traces also show how dynamic SQL is performed by the declaration and use of parameter markers in SQLPrepare() and SQLBindParameter(). This gives you the ability to determine at runtime what SQL statements will be performed.

The following trace entry shows the preparation of the SQL statement (a question mark (?) or a colon followed by a name (:name) denotes a parameter marker):

 SQLPrepare( hStmt=1:1, pszSqlStr=
   "select * from employee where empno = ?",
   cbSqlStr=-3 )
    ---> Time elapsed - +1.648000E-003 seconds 
( StmtOut="select * from employee where empno = ?" ) 
SQLPrepare( ) 
  <--- SQL_SUCCESS   Time elapsed - +5.929000E-003 seconds

The following trace entry shows the binding of the parameter marker as a CHAR with a maximum length of 7:

SQLBindParameter( hStmt=1:1, iPar=1, fParamType=SQL_PARAM_INPUT, 
fCType=SQL_C_CHAR, fSQLType=SQL_CHAR, cbColDef=7, ibScale=0,
 rgbValue=&00854f28, cbValueMax=7, pcbValue=&00858534 ) 
    ---> Time elapsed - +1.348000E-003 seconds 
SQLBindParameter( ) 
    <--- SQL_SUCCESS   Time elapsed - +7.607000E-003 seconds 

The dynamic SQL statement is now executed. The rbgValue="000010" shows the value that was substituted for the parameter marker by the application at run time:

SQLExecute( hStmt=1:1 ) 
    ---> Time elapsed - +1.317000E-003 seconds 
( iPar=1, fCType=SQL_C_CHAR, rgbValue="000010" - X"303030303130",
 pcbValue=6, piIndicatorPtr=6 ) 
    sqlccsend( ulBytes - 384 ) 
    sqlccsend( Handle - 14437216 ) 
    sqlccsend( ) - rc - 0, time elapsed - +1.915000E-003 
    sqlccrecv( ) 
    sqlccrecv( ulBytes - 1053 ) - rc - 0, time elapsed - +8.808000E-003 
SQLExecute( ) 
    <--- SQL_SUCCESS   Time elapsed - +2.213300E-002 seconds