WebDocs Home | Show Navigation Hide Navigation

Function Call Flow

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 “Writing an SQL Connection Program”.

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:

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.

1.Initialize SQL Connection.

2.Connect to database.

3.Set up SQL statement.

4.Open cursor with SSQL_NONSEL.

5.Test for errors.

6.Set up bound data (%SSC_EXECIO only).

7.Execute stored procedure.

8.Test for errors.

9.Process any returned data (%SSC_EXECIO only).

10.Loop until done.

11.Close cursor.

12.Release connection if no more operations.