Chapter 3 Embedding SQL Commands

Table of Contents

In every ALLBASE/SQL Pascal program, you embed SQL commands in the declaration section and the procedure section of your program to:

1: Declare the SQL Communications Area (SQLCA).
2: Declare host variables.
3: Start a DBE session by connecting to the DBEnvironment.
4 5: Define transactions.
6: Implicitly check the status of SQL command execution.
7: Terminate the DBE session.
8: Define or manipulate data in the DBEnvironment.
9: Explicitly check the status of SQL command execution.
10: Obtain error and warning messages from the ALLBASE/SQL message catalog.

The program listing shown in Figure 3-1 illustrates where in a main program you can embed SQL commands to accomplish the activities listed above. In a subprogram, host variable declarations cannot be in the global declaration part.

This chapter is a high-level road map to the logical and physical aspects of embedding SQL commands in a program. It addresses the reasons for embedding commands to perform the above activities. It also gives general rules for how and where to embed SQL commands for these activities. First however, it shows a program containing commands for the basic SQL functions listed above. Then it describes the general rules that apply when you embed any SQL command, referring to the numbered statements in the program.

Figure 3-1 Sample Program pasex2

$Heap_Dispose ON$

$Heap_Compact ON$

Standard_Level 'HP_Pascal$

(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)

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

(* retrieve one row or tuple at a time.                        *)

(* BEGIN WORK is executed before the SELECT and a COMMIT WORK  *)

(* after the SELECT.  An indicator variable is also used for   *)

(* SalesPrice.                                                 *)

(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)



Program pasex2(input, output);



label

    1000,

    9999;



const

    OK           =      0;

    NotFound     =    100;

    DeadLock     = -14024;



var



    EXEC SQL INCLUDE SQLCA;   (* SQL Communication Area *)     1



          (* Begin Host Variable Declarations *)

    EXEC SQL  Begin Declare Section;                           2

    PartNumber       : packed array[1..16] of char;

    PartName         : packed array[1..30] of char;

    SalesPrice       : longreal;

    SalesPriceInd    : SQLIND;

    SQLMessage       : packed array[1..132] of char;

    EXEC SQL  End Declare Section;

          (* End Host Variable Declarations *)



    Abort             : boolean;



procedure SQLStatusCheck;  (* Procedure to Display Error Messages *)

   Forward;



(* Directive to set SQL Whenever error checking *)



$PAGE $



EXEC SQL Whenever SqlError GOTO 1000;


Procedure ConnectDBE;  (* Procedure to Connect to PartsDBE *)

begin



writeln('Connect to PartsDBE');

EXEC SQL CONNECT TO 'PartsDBE';                                3



end;  (* End of ConnectDBE Procedure *)





Procedure BeginTransaction;  (* Procedure to Begin Work *)

begin



writeln;

writeln('Begin Work');

EXEC SQL BEGIN WORK;                                           4



end;  (* End BeginTransaction Procedure *)





procedure EndTransaction;  (* Procedure to Commit Work *)

begin





writeln('Commit Work');

EXEC SQL COMMIT WORK;                                          5



end;  (* End EndTransaction Procedure *)





(* Directive to reset SQL Whenever error checking *)

EXEC SQL Whenever SqlError CONTINUE;                           6





procedure TerminateProgram;   (* Procedure to Release PartsDBE *)

begin



writeln('Release PartsDBE');

EXEC SQL COMMIT WORK RELEASE;                                  7



writeln;

writeln('Terminating Program');

Goto 9999;  (* Goto exit point of main program *)



end;  (* End TerminateProgram Procedure *)





$PAGE $


procedure DisplayRow;   (* Procedure to Display Parts Table Rows *)

begin

writeln;

writeln('Part Number: ', PartNumber);

writeln('Part Name:   ', PartName);

if SalesPriceInd < 0 then

   writeln('Sales Price is NULL')

else

   writeln('Sales Price: ', SalesPrice:10:2);



end;  (* End of DisplayRow *)



$PAGE $





procedure SelectData; (* Procedure to Query Parts Table *)

begin



repeat



writeln;

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

readln(PartNumber);

writeln;



if PartNumber[1] <> '/' then

  begin



  BeginTransaction;



  writeln('SELECT PartNumber, PartName, SalesPrice');

  EXEC SQL SELECT PartNumber, PartName, SalesPrice             8

            INTO :PartNumber,

                 :PartName,

                 :SalesPrice  :SalesPriceInd

             FROM PurchDB.Parts

            WHERE PartNumber = :PartNumber;



  if SQLCA.SQLWARN[0] in ['W','w'] then

     begin

     write('SQL WARNING has occurred. The following row');

     writeln('of data may not be valid.');

     end;


  case SQLCA.SQLCODE of                                        9

  OK           : DisplayRow;

  NotFound     : begin

                   writeln;

                   writeln('Row not found!');

                 end;

                 end;

  otherwise      begin

                   SQLStatusCheck;

                   end;



  end;  (* case *)



  EndTransaction;



end;  (* End if *)

until PartNumber[1] = '/';



end;      (* End of SelectData Procedure *)



procedure SQLStatusCheck;  (* Procedure to Display Error Messages *)

begin



Abort := FALSE;

if SQLCA.SQLCODE < DeadLock then Abort := TRUE;



repeat

EXEC SQL SQLEXPLAIN :SQLMessage;                               10

writeln(SQLMessage);

until SQLCA.SQLCODE = 0;



if Abort then

  begin



  TerminateProgram;



  end;



end;  (* End SQLStatusCheck Procedure *)


begin  (* Beginning of Program *)



write('Program to SELECT specified rows from ');

writeln('the Parts Table - PASEX2');

writeln;

writeln('Event List:');

writeln('  Connect to PartsDBE');

writeln('  Begin Work');

writeln('  SELECT specified row from Parts Table');

writeln('   until user enters "/" ');

writeln('  Commit Work');

writeln('  Disconnect from PartsDBE');

writeln;



ConnectDBE;

SelectData;

TerminateProgram;



(* Whenever Routine - Serious DBE Error *)

(* SQL Whenever SQLError Entry Point *)

1000:



  (* Begin *)

  SQLStatusCheck;

  TerminateProgram;

  (* End *)



(* Exit Point for the main program *)

9999:



end.   (* End of Program *)

Chapter 3 Embedding SQL Commands

Technical documentation

» Table of Contents

» Index