SQL procedures in Embedded SQL

Part V. The SQL Anywhere Programming Interfaces

Chapter 34. The Embedded SQL Interface
SQL procedures in Embedded SQL

Simple procedures

Database procedures can be both created and called from Embedded SQL. A CREATE PROCEDURE statement can be embedded just like any other DDL statement. A CALL statement can also be embedded or it can be prepared and executed. Here is a simple example of both creating and executing a stored procedure in Embedded SQL:

     EXEC SQL CREATE PROCEDURE pettycash( IN amount DECIMAL(10,2) )
     BEGIN
     
         UPDATE account
         SET balance = balance - amount
         WHERE name = 'bank';
     
         UPDATE account
         SET balance = balance + amount
         WHERE name = 'pettycash expense';
     
     END;
     EXEC SQL CALL pettycash( 10.72 );

If you wish to pass host variable values to a stored procedure or retrieve the output variables, you will have to prepare and execute the CALL statement. The next example illustrates the use of host variables. Note the use of both the USING and INTO clause on the execute statement.

     EXEC SQL BEGIN DECLARE SECTION;
         double    hv_expense;
         double    hv_balance;
     EXEC SQL END DECLARE SECTION;
     
     EXEC SQL CREATE PROCEDURE pettycash(
                 IN expense     DECIMAL(10,2),
                 OUT endbalance DECIMAL(10,2) )
         BEGIN
             UPDATE account
             SET balance = balance - expense
             WHERE name = 'bank';
     
             UPDATE account
             SET balance = balance + expense
             WHERE name = 'pettycash expense';
     
             SET endbalance = ( SELECT balance FROM account
                                      WHERE name = 'bank' );
         END;
     
     EXEC SQL PREPARE S1 FROM 'CALL pettycash( ?, ? )';
     
     EXEC SQL EXECUTE S1 USING :hv_expense INTO :hv_balance;

Procedures with result sets

Database procedures can also contain SELECT statements. The procedure is declared using a RESULT clause to specify the number, name and types of the columns in the result set. Note that result set columns are different from output parameters. For procedures with result sets, the CALL statement can be used in place of a SELECT statement in the cursor declaration:

     EXEC SQL BEGIN DECLARE SECTION;
         char    hv_name[100];
     EXEC SQL END DECLARE SECTION;
     
     EXEC SQL CREATE PROCEDURE female_employees()
         RESULT( name char(50) )
         BEGIN
             SELECT emp_fname || emp_lname FROM employee
             WHERE sex = 'f';
         END;
     
     EXEC SQL PREPARE S1 FROM 'CALL female_employees()';
     
     EXEC SQL DECLARE C1 CURSOR FOR S1;
     EXEC SQL OPEN C1;
     for(;;) {
         EXEC SQL FETCH C1 INTO :hv_name;
         if( SQLCODE != SQLE_NOERROR ) break;
         printf( "%s\\n", hv_name );
     }
     EXEC SQL CLOSE C1;

In this example, the procedure has been invoked with an OPEN statement rather than an EXECUTE statement. The OPEN statement causes the procedure to execute until it reaches a SELECT statement. At this point, C1 is a cursor for the SELECT statement within the database procedure. You can use all forms of the FETCH command (backward and forward scrolling) until you are finished with it. The CLOSE statement terminates execution of the procedure. Note that if there had been another statement following the SELECT in the procedure, it would not have been executed. In order to execute statements following a SELECT, use the RESUME cursor-name command. The RESUME command will either return the warning SQLE_PROCEDURE_COMPLETE or it will return SQLE_NOERROR indicating there is another cursor. The next example illustrates a two select procedure:

     EXEC SQL CREATE PROCEDURE people()
     RESULT( name char(50) )
     BEGIN
     
         SELECT emp_fname || emp_lname
         FROM employee;
     
         SELECT fname || lname
         FROM customer;
     END;
     
     EXEC SQL PREPARE S1 FROM 'CALL female_employees()';
     
     EXEC SQL DECLARE C1 CURSOR FOR S1;
     EXEC SQL OPEN C1;
     while( SQLCODE == SQLE_NOERROR ) {
         for(;;) {
             EXEC SQL FETCH C1 INTO :hv_name;
             if( SQLCODE != SQLE_NOERROR ) break;
             printf( "%s\\n", hv_name );
         }
         EXEC SQL RESUME C1;
     }
     EXEC SQL CLOSE C1;

Dynamic cursors for CALL statements

These examples have used static cursors. Full dynamic cursors can also be used for the CALL statement (see "Dynamic SELECT statement").

The DESCRIBE statement works fully for procedure calls. A DESCRIBE OUTPUT will produce an SQLDA having a description for each of the result set columns. If the procedure does not have a result set, then the SQLDA will have a description for each INOUT or OUT parameter to the procedure. A DESCRIBE INPUT statement will produce an SQLDA having a description for each IN or INOUT parameter to the procedure.

DESCRIBE ALL is a new form of the DESCRIBE statement that will describe IN, INOUT, OUT and RESULT set parameters. DESCRIBE ALL uses the indicator variables in the SQLDA to provide additional information. DT_PROCEDURE_IN and DT_PROCEDURE_OUT are bits that are set in the indicator variable when a CALL statement is described. DT_PROCEDURE_IN indicates an IN or INOUT parameter and DT_PROCEDURE_OUT indicates an INOUT or OUT parameter. Procedure RESULT columns will have both bits clear. After a describe OUTPUT, these bits can be used to distinguish between statements that have result sets (need to use OPEN, FETCH, RESUME, CLOSE) and statements that do not (need to use EXECUTE). See "DESCRIBE statement" for a complete description of this functionality.

Multi-Threaded or Reentrant Code

You can use Embedded SQL statements in multi-threaded or reentrant code. However, if you use a single connection, you are restricted to one active request per connection. In a multi-threaded application, you should not use the same connection to the database on each thread unless you use a semaphore to control access.

There are no restrictions on using separate connections on each thread that wishes to use the database. The SQLCA is used by the runtime library to distinguish between the different thread contexts. Thus, each thread wishing to use the database must have it's own SQLCA. Any given database connection will only be accessible from one SQLCA.

When to use multiple SQLCAs

You can use the multiple SQLCA support in any of the supported Embedded SQL environments, but it is only required in reentrant code.

The following list details the environments where multiple SQLCAs must be used:

Multi-threaded OS/2, Windows NT or Windows 95 application An OS/2 or Windows NT application can have multiple threads using Embedded SQL. If both threads use the same SQLCA, a context switch can cause both threads to be using the SQLCA at the same time. Each thread must have its own SQLCA. This can also happen when you have a DLL that uses Embedded SQL and is called by more than one thread in your application.
Windows DLL A Windows DLL has only one data segment. While the database engine is processing a request from one application, it may yield to another application that makes a request to the database engine. If your DLL uses the global SQLCA, both applications will be using it at the same time. Each Windows application must have its own SQLCA.
Windows NT, Windows 95 or OS/2 DLL with one data segment An OS/2 or Windows NT DLL can be created with only one data segment or one data segment for each application. If your DLL has only one data segment, you cannot use the global SQLCA for the same reason a Windows DLL cannot use the global SQLCA. Each application must have its own SQLCA.

Single SQLCA can handle multiple connections
You do not need to use multiple SQLCAs to connect to more than one database or have more than one connection to a single database.

Using multiple SQLCAs

To manage multiple SQLCAs in your application,

You must use the command line switch on the SQL preprocessor that generates reentrant code ("-r"). The reentrant code is a little larger and a little slower because statically initialized global variables cannot be used. However, these affects are minimal.
Each SQLCA used in your program must be initialized with a call to db_init and cleaned up at the end with a call to db_fini.

Caution
Failure to call db_fini for each db_init on NetWare can cause the database server to fail, and the NetWare file serve to fail.
The Embedded SQL statement SET SQLCA ("SET SQLCA statement") is used to tell the SQL preprocessor to use a different SQLCA for database requests. Usually, a statement such as: EXEC SQL SET SQLCA 'task_data->sqlca'; is used at the top of your program or in a header file to set the SQLCA reference to point at task specific data. This statement does not generate any code and thus has no performance impact. It changes the state within the preprocessor so that any reference to the SQLCA will use the given string.

Connection Management with Multiple SQLCAs

Each SQLCA can have one unnamed connection. Each SQLCA has an active or current connection (see "SET CONNECTION statement"). All operations on a given database connection must use the same SQLCA that was used when the connection was established.

Record locking
Operations on different connections are subject to the normal record locking mechanisms and may cause each other to block and possibly to deadlock. For information on locking, see the chapter "Using Transactions and Locks".