Developing ALLBASE/SQL Applications

The following SQL statements can be embedded in your program:

The ALLBASE/SQL Reference Manual describes the complete syntax for each kind of SQL statement.

Some SQL statements can only be used in application programs. These include the cursor commands (DECLARE, OPEN, FETCH, REFETCH, UPDATE WHERE CURRENT, DELETE WHERE CURRENT, and CLOSE); the bulk commands (BULK SELECT and BULK INSERT); and the error handling commands (WHENEVER and SQLEXPLAIN).

Moreover, some of the statements have a different effect when used in an application program than when used interactively. For example, the SELECT command in ISQL may be used when you wish to retrieve multiple rows, but in an application program a simple SELECT that retrieves more than one row results in an error.

You must include the following instruction to the ALLBASE/SQL preprocessor in the global declarations area of each source file:

   EXEC SQL INCLUDE SQLCA;

This incorporates into your program a data structure for handling return codes from ALLBASE/SQL, including numbered error conditions. You can use the information in the sqlca (SQL Communications Area) to test and branch. Refer to Chapter 4, "Runtime Status Checking and the sqlca," for complete information about using the sqlca.

If your program includes dynamic query processing for the FETCH command with a USING DESCRIPTOR clause, add the INCLUDE SQLDA statement to your global declarations, and define the appropriate data buffer and format array. These elements are described fully in Chapter 8.

All other SQL commands may appear in any part of your program.

In addition to the normal variable declarations, the source file contains variable declarations for host variables. These can appear wherever declarations are legal. However, it is recommended that you include them at the beginning of the file, since they are translated into global variables by the preprocessor anyway. If more than one source file references the same set of variables, you must declare them separately in each file, and you must preprocess each file separately. See Chapter 2 for more details. Host variable declarations appear within a pair of SQL statments:

   EXEC SQL BEGIN DECLARE SECTION;

     .

     .

   EXEC SQL END DECLARE SECTION;

Host variables can appear both in the embedded SQL statements in your code and in ordinary C statements. When they appear in embedded SQL statements, you prefix them with a colon, as shown in this example:

   EXEC SQL SELECT PartName, SalesPrice

        INTO :PartName, :SalesPrice

        FROM PurchDB.Parts;

Host variables are treated fully in Chapter 3.

The skeleton program in Figure 1-2 illustrates the relationship between C constructs and embedded SQL commands in an application program. SQL commands may appear in the program at locations indicated by the comments.

.

/* STATIC VARIABLE DECLARATION PART */

sqlca Declaration

sqlda Declaration

.

/* Host Variable Declarations */

/* Host Variables can be global, local, or both */

.

/* main DECLARATION PART */

main(argc,argv)

/* Parameter Declaration */

{

.

/* Host Variable Declarations */

/* Host Variables can be local, or for called routines */



/* C statements, some containing SQL Commands */

EXEC SQL ...



int FunctionName();

.

}

/* FUNCTION DECLARATION PART */

FunctionName(parameter list)

/* Parameter Declaration */

  {

  .

  /* Host Variable Declarations */

  /* Host Variables can be local */



  /* C statements, some containing SQL Commands */

  EXEC SQL ...

  .

  }

Most SQL commands appear within C functions in which you establish DBEnvironment access and manipulate data in a database. Variable declarations should follow rules for coding C programs without SQL statements. In addition to functions for various kinds of database access, you should code a CONNECT function, a RELEASE function, an error handling function, and transaction management functions containing BEGIN WORK and COMMIT WORK statements.

You must follow some simple rules when embedding SQL statements in C code:

A sample source file appears at the end of this chapter. And more detailed explanation of coding for different types of embedded SQL statements appears in Chapters 4 through 8.

• Creates a modified source file.

• Stores sections in the DBEnvironment.

• Generates a file, SQLMSG, for preprocessing messages. Creates an installable module file.

The preprocessor translates embedded SQL statements into C language statements and comments out the SQL statements. Non-SQL statements in your code are not translated. As output, the preprocessor creates a modified source file which can then be compiled. The original source file is not changed.

The preprocessor also creates three include files, which contain variable declarations, type declarations, and external procedure declarations used by the preprocessor generated C code.

The modified source file contains include statements which direct the compiler to incorporate these include files at compile time.

The preprocessor also stores runtime instructions in the system catalog of the DBEnvironment you specify when preprocessing. These instructions are called sections and are stored in a module, which contains a section for each DML, DDL, or DCL statement in your program that can be completely defined before run time. A module is referenced in the system catalog of the DBEnvironment.

Not all SQL statements cause the preprocessor to store a section. Only statements that access data cause a section to be stored.

A section has three parts:

These elements are illustrated in Figure 1-3.

Figure 1-3 Components of a Stored Section

   EXEC SQL SELECT * FROM PurchDB.Parts

        WHERE PartNumber = :PartNumber;

During preprocessing, messages are written to the file sqlmsg. If preprocessing is successful, the file contains a statement indicating the name of the module stored in the DBEnvironment and the number of sections stored.

Figure 1-4 Ways of Compiling and Linking an ALLBASE/SQL C Program

• If the program uses a CONNECT command to start a DBE session, you need CONNECT authority and RUN or module OWNER authority to run the program.

• If the program uses a START DBE command to start the DBE session, you need DBA authority to run the program.