Looking at an Embedded SQL Source Program

In every ALLBASE/SQL C program, you embed SQL commands in the declaration part and the procedure part of your program to carry out specific tasks. The program listing shown in Figure 1-8 “Program cex2: Using Simple SELECT” illustrates where in a program you can embed SQL commands to accomplish the tasks listed below. Figure 1-7 “Runtime Dialog of Program cex2” shows the runtime dialog for the program.

1

Declare the SQL Communications Area (sqlca).

The sqlca is an ALLBASE/SQL data structure that contains current information about a program's DBE session. Every ALLBASE/SQL C program must contain an sqlca declaration in the global declaration section. Use the following command:

	EXEC SQL INCLUDE SQLCA;

2

Declare host variables.

All host variables used in a program must be declared in a declaration part. You can put more than one such declaration section in a program, but all host variables must be declared between the BEGIN and END DECLARE SECTION commands.

3

Display error and warning messages from the ALLBASE/SQL message catalog.

You can display messages for any errors encountered in execution as shown in the SQLStatusCheck function. Complete details about error and message handling are presented in the chapter, "Runtime Status Checking and the sqlca."

4

Start a DBE session.

In most application programs, you embed the CONNECT command to start a DBE session. This command must be executed before you can access the DBEnvironment

5

Check the status of SQL command execution.

Your program should check for the success or failure of execution of each SQL command, as shown in the example program.

6

Terminate the DBE session.

You use the RELEASE command or the RELEASE option of the COMMIT WORK command to end a DBE session.

7 and

Define transactions.

8

You define transactions in a program to control concurrency and consistency in your database access. The transaction is bounded by the BEGIN WORK and COMMIT WORK or ROLLBACK WORK commands. When a COMMIT WORK is successfully executed, all operations performed by the transaction it ends are permanently committed to the DBEnvironment

9

Define or manipulate data in the DBEnvironment.

Nearly all programs access data in one or more databases. The SELECT command shown in the example program retrieves the row from PurchDB.Parts that contains a part number matching the value in the host variable named in the WHERE clause. Note, indicator variables such as SalesPriceInd are discussed in the chapter, "Host Variables," and data manipulation is presented fully in Chapters 5 through 8.

Figure 1-7 Runtime Dialog of Program cex2



Program to SELECT specified rows from the Parts Table - cex2



Event List:

 CONNECT TO ../sampledb/PartsDBE

 BEGIN WORK

 SELECT specified row from Parts Table

  until user enters a '/'

 COMMIT WORK

 RELEASE from ../sampledb/PartsDBE



Connect to ../sampledb/PartsDBE



Enter Part Number within Parts Table or '/' to STOP> 1243-P-01



Begin Work

SELECT PartNumber, PartName, SalesPrice



Row not found!



Commit Work



Enter Part Number within Parts Table or "/" to STOP> 1323-D-01



Begin Work

SELECT PartNumber, PartName, SalesPrice



Part Number:  1323-D-01

Part Name:    Floppy Diskette Drive

Sales Price:    200.00



Commit Work



Enter Part Number within Parts Table or "/" to STOP> /



Release ../sampledb/PartsDBE

Figure 1-8 Program cex2: Using Simple SELECT

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

/* This program illustrates the use of SQL's SELECT command to     */

/* retrieve one row or tuple of data at a time.                    */

/* BEGIN WORK is executed before the SELECT and a COMMIT WORK      */

/* is executed after the SELECT. An indicator variable is also     */

/* used for SalesPrice.                                            */

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */



typedef int boolean;



char    response[2];

boolean Abort;



#include <stdio.h>



#define    OK                 0

#define    NotFound         100

#define    MultipleRows  -10002

#define    DeadLock      -14024

#define    FALSE              0

#define    TRUE               1



EXEC SQL INCLUDE SQLCA;   /* SQL Communication Area */           1 



          /* Begin Host Variable Declarations */

    EXEC SQL BEGIN DECLARE SECTION;                              2 

    char       PartNumber[17];

    char       PartName[31];

    double     SalesPrice;

    sqlind     SalesPriceInd;

    char       SQLMessage[133];

    EXEC SQL END DECLARE SECTION;                                2 

          /* End Host Variable Declarations */







int SQLStatusCheck()  /* Function to Display Error Messages */

{

Abort = FALSE;

if (sqlca.sqlcode < DeadLock)

   Abort = TRUE;

do {

  EXEC SQL SQLEXPLAIN :SQLMessage;                               3 

  printf("\n");

  printf("%s\n",SQLMessage);

  } while (sqlca.sqlcode != 0);



if (Abort) {

  EndTransaction();

  ReleaseDBE();

  }

}  /* End SQLStatusCheck Function */





boolean ConnectDBE()  /* Function to Connect to ../sampledb/PartsDBE */

{

boolean ConnectDBE;



ConnectDBE = TRUE;



printf("\n Connect to ../sampledb/PartsDBE";



EXEC SQL CONNECT TO '../sampledb/PartsDBE';                      4 



if (sqlca.sqlcode != OK) {

  ConnectDBE = FALSE;

  SQLStatusCheck();                                              5 

  }  /* End if */

return (ConnectDBE);

}  /* End of ConnectDBE Function */





int ReleaseDBE()   /* Function to Release ../sampledb/PartsDBE */

{

printf("\n");

printf("\n Release ../sampledb/PartsDBE");

printf ("\n);

EXEC SQL RELEASE;                                                6 



if (sqlca.sqlcode != OK) SQLStatusCheck();

}  /* End ReleaseDBE Function */







boolean BeginTransaction()    /* Function to Begin Work */

{

boolean BeginTransaction;

BeginTransaction = TRUE;



printf("\n");

printf("\n Begin Work");

EXEC SQL BEGIN WORK;                                             7 



if (sqlca.sqlcode != OK) {

  BeginTransaction = FALSE;

  SQLStatusCheck();                                              5 

  ReleaseDBE();

  }  /* End if */

return (BeginTransaction);

}  /* End BeginTransaction Function */





int EndTransaction()  /* Function to Commit Work */

{

printf("\n");

printf("\n Commit Work");

EXEC SQL COMMIT WORK;                                            8 



if (sqlca.sqlcode != OK) SQLStatusCheck();                       5 

}  /* End EndTransaction Function */





int DisplayRow()   /* Function to Display Parts Table Rows */

{

  printf("\n");

  printf("\n Part Number:   %s\n",   PartNumber);

  printf(" Part Name:     %s\n",   PartName);



  if (SalesPriceInd < 0) {

    printf(" Sales Price:   is NULL \n");

    }

  else

    printf(" Sales Price: %10.2f\n", SalesPrice);

}  /* End of DisplayRow */





int Select()    /* Function to Query Parts Table */

{



do {

  printf("\n");

  printf("\n Enter Part Number within Parts Table or '/' to STOP > ");

  scanf("%s",PartNumber);



  if (PartNumber[0] != '/') {



    BeginTransaction();



    printf("\n SELECT PartNumber, PartName, SalesPrice");

    EXEC SQL SELECT PartNumber, PartName, SalesPrice             9 

              INTO :PartNumber,

                   :PartName,

                   :SalesPrice :SalesPriceInd

              FROM  PurchDB.Parts

             WHERE  PartNumber = :PartNumber;



    if ((sqlca.sqlwarn[0] == 'W') || (sqlca.sqlwarn[0] == 'w')) {

      printf("\n SQL WARNING has occurred. The following row");

      printf("\n    of data may not be valid!");

      }

    if (sqlca.sqlcode == OK) {

      DisplayRow();

      }

    else if (sqlca.sqlcode == NotFound) {

      printf("\n Row not found!");

     }

    else if (sqlca.sqlcode == MultipleRows) {

      printf("\n WARNING: More than one row qualifies!");

      }

    else {

      SQLStatusCheck();                                          5 

}

    EndTransaction();

  }

}   /* End do */

while (PartNumber[0] != '/');



}/* End of Select Function */







main()          /* Beginning of program */

{



printf("\n Program to SELECT specified rows from");

printf("\n  the Parts Table - cex2");

printf("\n");

printf("\n Event List:");

printf("\n  CONNECT TO ../sampledb/PartsDBE");

printf("\n  BEGIN WORK");

printf("\n  SELECT the specified row from the Parts Table");

printf("\n    until the user enters a '/'");

printf("\n  COMMIT WORK");

printf("\n  RELEASE from ../sampledb/PartsDBE");

printf("\n");



if (ConnectDBE())  {

  Select();

  ReleaseDBE();

  }

else

  printf("\n Error: Cannot Connect to ../sampledb/PartsDBE!\n");

}  /* End of Program */

Looking at an Embedded SQL Source Program

Technical documentation

» Table of Contents

» Index