Binding and Executing of SQL Statements

The processing of an SQL statement involves a series of steps.

  1. The binding process is the replacement of (variable) input values in the statement, in places indicated by bind placeholders.

    The input values are substituted into the SQL statement in place of the bind placeholders. These placeholders have the form ":number", or "%bindop(:number [,  parm]. . .)" where the number indicates which input value is to be substituted, and the bindop and parm strings indicate what meta-SQL binding function is to be performed.

    Note: There must be no spaces between the bindop and the left parenthesis.

    The following binding meta-SQL functions are used with record objects to substitute various forms of fieldnames and values into the SQL statement. The goal of these binding functions is to enable the writing of SQL and PeopleCode that can manipulate records without dependencies on the exact fields in the records.

    • %DTTM

    • %InsertValues

    • %KeyEqual

    • %KeyEqualNoEffDt

    • %List

    • %OldKeyEqual

    • %Table

    • %UpdatePairs

  2. The execution of an SQL statement is the carrying out of the operation of the statement by the database engine.

    This view of SQL statement processing is actually simpler than what actually occurs. In actual practice, the binding occurs in two distinct phases. The database engine (or its supporting routines) is aware of only the latter phase. For some operations, some database engines are able to delay the second stage of binding and the execution of an SQL statement, so the statement can be rebound and re-executed (with different input values). The advantage of this is that these bindings and executions can be accumulated and transmitted across the network to the database server, with several database operations being done in one network operation. This is sometimes referred to as bulk mode. Because the network time consumes most database time operations, the performance advantages of bulk mode can be significant.

    However, in bulk mode, individual operations might not be executed immediately by the database engine. The result is that the application might not see errors that arise until later operations are performed.

    SQL SELECT statements are not bound multiple times, rather the retrieved rows are accumulated and sent across the network many rows at a time, also decreasing the effect of network delays.