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”

                         

Simple query

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.

For examples, see

exam_fetch.dbl

exam_fetch_update.dbl
(for %SSC_REBIND example)

See “Writing an SQL Connection Program” for information on these programs.

 

Multirow query

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.

For an example, see exam_multirow_fetch.dbl. See “Writing an SQL Connection Program” for information on this program.

 

Simple atomic update

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.

For an example, see exam_fetch_update.dbl. See “Writing an SQL Connection Program” for information on this program.

 

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

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.

For an example, see exam_create_table.dbl. See “Writing an SQL Connection Program” for information on this program.

 

Stored Procedure

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. For examples, see

stp_mysql.dbl

stp_odbc.dbl

stp_ora.dbl

stp_sqlsrv1.dbl

stp_sqlsrv2.dbl

See “Writing an SQL Connection Program” for information on these programs.

 

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 the diagram in “Multirow query” and the stp_sqlsrv1.dbl and stp_sqlsrv2.dbl example programs.

Note that with stored procedures, transactions typically take place inside the stored procedure, not outside with %SSC_COMMIT.