Function call flows

The following diagrams illustrate the function call flow for each of the primary SQL commands that you might use in your SQL Connection program:

• Simple query
• Multirow query
• Simple atomic update
• Bulk update
• Insert
• Stored procedure

For information on example programs listed for these function call sequences, see SQL Connection sample programs.

Simple query

The following illustrates a function call sequence for a simple query. For examples, see the example programs exam_fetch.dbl and exam_fetch_update.dbl (which includes %SSC_REBIND).

Initialize SQL Connection. Connect to database. Set up SQL statement. Open cursor with SSQL_SELECT and SSQL_STANDARD. Test for errors. Define destination variables. While rows are available, fetch data with %SSC_MOVE. At end of query, if new identical query, use %SSC_OPEN or %SSC_REBIND. Close cursor. Release connection if no more operations.

Multirow query

The following illustrates a function call sequence for a multirow query. For an example, see the example program exam_multirow_fetch.dbl.

Initialize SQL Connection. Connect to database. Set up SQL statement. Open cursor with SSQL_SELECT and SSQL_STANDARD. Test for errors. Define destination variables or use arrays for %SSC_DEFINE. Fetch data with %SSC_MOVE. If not end of file (EOF), process returned data for each returned row and get more data. (The number of rows returned is available as the row_count argument for %SSC_MOVE.) Rebind if required. End transaction by committing it or by rolling it back if there’s an error. Release connection if no more operations.

Simple atomic update

The following illustrates a function call sequence for a simple atomic update. For an example, see the example program exam_fetch_update.dbl.

Initialize SQL Connection. Connect to database. Start transaction with %SSC_COMMIT. Set up SELECT statement. Open cursor with SSQL_SELECT and SSQL_POSITION, binding variables as necessary. Define variables. Set up update statement with %SSC_SQLLINK, binding variables for update. Fetch data with %SSC_MOVE. Execute update with SSQL_STANDARD. Close cursor. End transaction by committing it or by rolling it back if there’s an error. Release connection if no more operations.

Bulk update

The following illustrates a function call sequence for a bulk update.

Initialize SQL Connection. Connect to database. Start transaction with %SSC_COMMIT. Set up SQL statement. Open cursor with SSQL_SELECT and SSQL_POSITION, binding variables as necessary. Define variables. Set up update statement with %SSC_SQLLINK, binding variables as necessary. Fetch data with %SSC_MOVE. Execute update statement with %SSC_EXECUTE (SSQL_STANDARD). If there are more rows, fetch data (%SSC_MOVE) and then execute update (%SSC_EXECUTE) in loop. Close cursor with soft close. End transaction (%SSC_COMMIT). If you want to rebind the SELECT statement, start a transaction, rebind, and loop through the first %SSC_MOVE, %SSC_EXECUTE, %SSC_MOVE, %SSC_SCLOSE, and %SSC_COMMIT to end the transaction. Close cursor with soft close (%SSC_SCLOSE). Release connection if no more operations (%SSC_RELEASE).

Insert

The following illustrates a function call sequence for an insert. For an example, see the example program exam_create_table.dbl.

Initialize SQL Connection. Connect to database. Start transaction with %SSC_COMMIT. Set up SQL statement. Open cursor with SSQL_NONSEL. Test for errors. Set up data. Execute statement with SSQL_STANDARD. Loop until done. End transaction by committing it or by rolling it back if there’s an error. Close cursor. Release connection if no more operations.

Stored procedure

The following illustrates a function call sequence for a stored procedure. For examples, see the following example programs: stp_mysql.dbl, stp_odbc.dbl, stp_ora.dbl, stp_sqlsrv1.dbl, stp_sqlsrv2.dbl. Note the following:

• To fetch data from a SQL Server result set, use a combination of %SSC_OPEN with %SSC_MOVE (rather than %SSC_EXECIO or %SSC_EXECUTE) as illustrated in Multirow query and the stp_sqlsrv1.dbl and stp_sqlsrv2.dbl example programs.
• With stored procedures, transactions typically take place inside the stored procedure, not outside with %SSC_COMMIT.

  Initialize SQL Connection. Connect to database. Set up SQL statement. Open cursor with SSQL_NONSEL. Test for errors. Set up bound data (%SSC_EXECIO only). Execute stored procedure. Test for errors. Process any returned data (%SSC_EXECIO only). Loop until done. Close cursor. Release connection if no more operations.