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:

For information on example programs listed for these function call sequences, see 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).

  1. Initialize SQL Connection.
  2. Connect to database.
  3. Set up SQL statement.
  4. Open cursor with SSQL_SELECT and SSQL_STANDARD.
  5. Test for errors.
  6. Define destination variables.
  7. While rows are available, fetch data with %SSC_MOVE.
  8. At end of query, if new identical query, use %SSC_OPEN or %SSC_REBIND.
  9. Close cursor.
  10. 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.

  1. Initialize SQL Connection.
  2. Connect to database.
  3. Set up SQL statement.
  4. Open cursor with SSQL_SELECT and SSQL_STANDARD.
  5. Test for errors.
  6. Define destination variables or use arrays for %SSC_DEFINE.
  7. Fetch data with %SSC_MOVE.
  8. 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.)
  9. Rebind if required.
  10. End transaction by committing it or by rolling it back if there’s an error.
  11. 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.

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

 

Bulk update

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

  1. Initialize SQL Connection.
  2. Connect to database.
  3. Start transaction with %SSC_COMMIT.
  4. Set up SQL statement.
  5. Open cursor with SSQL_SELECT and SSQL_POSITION, binding variables as necessary.
  6. Define variables.
  7. Set up update statement with %SSC_SQLLINK, binding variables as necessary.
  8. Fetch data with %SSC_MOVE.
  9. Execute update statement with %SSC_EXECUTE (SSQL_STANDARD).
  10. If there are more rows, fetch data (%SSC_MOVE) and then execute update (%SSC_EXECUTE) in loop.
  11. Close cursor with soft close.
  12. End transaction (%SSC_COMMIT).
  13. 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.
  14. Close cursor with soft close (%SSC_SCLOSE).
  15. 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.

  1. Initialize SQL Connection.
  2. Connect to database.
  3. Start transaction with %SSC_COMMIT.
  4. Set up SQL statement.
  5. Open cursor with SSQL_NONSEL.
  6. Test for errors.
  7. Set up data.
  8. Execute statement with SSQL_STANDARD.
  9. Loop until done.
  10. End transaction by committing it or by rolling it back if there’s an error.
  11. Close cursor.
  12. 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: