HP 3000 Manuals HP 3000 Manuals
Programs Using Dynamic Command Operations
The rest of this chapter contains sample programs that illustrate the use
of dynamic preprocessing techniques for commands. There are two complete
programs:
* Program pasex10a, which contains statements for executing any
dynamic command (non-query or query with unknown format).
* Program pasex10b, which contains statements for executing dynamic
queries of known format.
For each program, there is a description of the code, a display of the
runtime dialog with user input, and a listing.
Sample Program Using Dynamic Commands of Unknown Format
Programs that host queries having query result formats unknown at
programming time must use format array information to parse the data
buffer. Figure 10-7 illustrates the logic for one such program,
pasex10a. The runtime dialog and source code for this program are shown
in Figure 10-8 and Figure 10-9 , respectively.
Program pasex10a starts a DBE session 37 in the sample database in
function ConnectDBE 4 , then executes the procedure named Describe 23 .
This procedure:
* Initializes the two SQLDA fields 24 that must be set before
executing the DESCRIBE command: SQLDA.SQLN (the number of
elements in the format array) and SQLDA.SQLFMTARR (the address of
the format array). The number of elements in the format array is
defined in the constant NbrFmtRecords, set to 1024 in this program
to accommodate the maximum number of columns in any query result.
* Calls procedure GetCommand 25 and processes commands accepted
from the user in that procedure until the user enters a slash (/).
Procedure GetCommand 21 accepts SQL commands into the host variable
named DynamicCommand. Since the maximum allowable dynamic command is
1024 bytes, including the semicolon, this variable is declared ( 1 ) as
String[1024]. GetCommand concatenates multiple lines of user input by
accepting each line into a local variable, DynamicClause and adding it to
the contents of DynamicCommand until the user enters a semicolon; the
string function STRPOS is used to detect this character.
After SQL command entry is complete, control returns to procedure
Describe 23 , which:
* Starts a transaction, in function BeginTransaction 6 .
* Executes the PREPARE 26 and DESCRIBE 27 commands.
* Examines the SQLDA.SQLD field (number of columns in query result)
to determine whether the dynamic command is a query 28 . If this
value is 0, the command is not a query and procedure NonQuery 29
is invoked to execute the command. If the SQLDA.SQLD value is not
0, procedure Query 30 is invoked to execute the command.
Note that the FORWARD directive 22 is used for procedures NonQuery and
Query, just prior to Describe. These procedures cannot be fully declared
until after procedure Describe. You must name a dynamic command (in the
PREPARE command) before you reference it (in the EXECUTE or DECLARE
CURSOR commands). In this program, the PREPARE command is executed in
procedure Describe, which calls both NonQuery and Query.
Procedure Query:
* Displays the number of columns in the query result, by using the
value ALLBASE/SQL assigned to SQLDA.SQLD when the DESCRIBE command
was executed 31 .
* Declares and opens a cursor for the dynamic query 32 .
* Initializes the three SQLDA fields that must be set before
executing the FETCH command 33 : SQLDA.SQLBUFLEN (the size of
the data buffer), SQLDA.NROW (the number of rows to put into the
data buffer with each FETCH), and SQLDA.SQLROWBUF (the address of
the data buffer).
Note that to set SQLDA.NROW, the program divides the row length
into the data buffer size to determine how many rows can fit into
the data buffer 34 .
* Executes the FETCH command 35 and calls procedure Display Select
36 until the last row in the active set has been fetched. When
no more rows are available to fetch, ALLBASE/SQL sets
SQLCA.SQLCODE to 100, defined as a constant named EOF in this
program.
Procedure DisplaySelect 8 parses the data buffer after ea operation and
displays rows:
* The procedure keeps track of the beginning of each row by using a
local variable, CurrentOffset, as a pointer. CurrentOffset is
initialized to 1 10 at the beginning of procedure DisplaySelect.
* Column headings are written from the SQLName field of each format
array record 11 . The loop that displays the headings uses the
SQLDA.SQLD value (the number of columns in the query result) as
the final value of a format array record counter (x).
* The first through last column values in each row are examined and
displayed in a loop. The loop uses the SQLDA.SQLRROW value (the
number of rows fetched) as the final value of a row counter 12 .
The loop also uses the SQLDA.SQLD value (the number of select list
items) as the final value of a column counter 13 .
* The SqlIndLen field of each column's format array record is
examined 14 to determine whether a null value might exist.
* If a column can contain null values, SqlIndLen is greater than
zero, and the procedure must examine the indicator variable to
determine whether a value is null. A local variable,
NullIndOffset, is used to keep track of the first byte of the
current indicator variable 15 .
* Any null indicator can be located by adding the current value of
SqlNOf to the current value of CurrentOffset. SqlNOf is the
format array record field that contains the byte offset of a null
indicator from the beginning of a row. Recall that CurrentOffset
keeps track of the beginning of a row.
* The Pascal ORD function and NullIndOffset are used to determine
whether the indicator variable contains zeros 16 . If it does,
the value is null, and the procedure displays the message Column
is NULL 17 .
* If a value is not null, it is moved 18 from the data buffer to
OneColumn.CharData. The starting location of a value in the
STRMOVE procedure is computed by adding the current value of
SqlVOf to the current value of CurrentOffset. SqlVOf is the
format array record field that contains the byte offset of a value
from the beginning of a row. The number of bytes to move is the
value stored in SqlValLen. OneColumn.CharData is one of the
variations of a variant record, GenericColumnType 9 .
* GenericColumnType is used to write data values. This variant
record has a record definition describing a format for writing
data of each of the ALLBASE/SQL data types. The record variation
used depends on the value of SqlType 19 , the format array record
field describing the data type of a select list item. In the case
of DECIMAL data, a function named BCDToString 2 converts the
binary coded decimal (BCD) information in the data buffer into
ASCII format for display purposes.
* After each value in a row is displayed, CurrentOffset is
incremented by SQLDA.SqlRowLen 20 to point to the beginning of
the next row.
When the dynamic command has been completely processed, procedure Query
calls the EndTransaction procedure 7 to process a COMMIT command. Thus
each dynamic query hosted by this program is executed in a separate
transaction.
To determine whether each SQL command executed successfully, the program
examines the value of SQLCA.SQLCODE after SQL commands are executed.
Procedure SQLStatusCheck 3 is invoked to display one or more messages
from the ALLBASE/SQL message catalog. Any other action taken depends on
the SQL command:
* If the CONNECT command fails, function ConnectDBE 4 sets the
ConnectDBE flag to FALSE, then calls procedure SQLStatusCheck.
Then the program terminates.
* If the BEGIN WORK command fails, function BeginTransaction 6
calls SQLStatusCheck to display messages, then calls ReleaseDBE 5
to end the DBE session. The program then terminates because
procedure Describe 23 sets DynamicCommand to a slash.
* If other SQL commands fail, procedure SQLStatusCheck terminates
the program whenever the error is serious enough to return an
SQLCA.SQLCODE less than -14024.
![[]](../../pic3k/M012400/FFN1173c50.gif)
Figure 10-7. Flow Chart of Program pasex10a
![[]](../../pic3k/M012400/FFN1183c50.gif)
Figure 10-7. Flow Chart of Program pasex10a (page 2 of 2)
____________________________________________________________________________
| |
| Pascal program illustrating dynamic command processing. |
| |
| Event List: |
| Connect to PartsDBE |
| Prompt for any SQL command |
| Begin Work |
| Prepare |
| Describe |
| If command is a non-query command, EXECUTE it |
| Otherwise execute the following: |
| Declare |
| Open |
| Fetch |
| Close |
| Commit Work |
| Repeat the above ten steps |
| Release PartsDBE |
| |
| Connect to PartsDBE |
| Connect to PartsDBE |
| |
| You may enter any SQL command or "/" to STOP the program. |
| The command can be continued on the next line. The command |
| must be terminated with a semicolon (;). |
| |
| Enter SQL command/clause > |
| |
| > SELECT * FROM PURCHDB.PARTS WHERE SALESPRICE = 2000; |
| |
| Begin Work |
| Prepare |
| Describe |
| Query SQL command. |
| |
| Number of columns: 3 |
| |
| PARTNUMBER | PARTNAME | SALESPRICE | |
| 1343-D-01 | Winchester Drive | 2000.00 ||
| |
| Row not found or no more rows |
| |
| Commit Work |
| |
| You may enter any SQL command or "/" to STOP the program. |
| The command can be continued on the next line. The command |
| must be terminated with a semicolon (;). |
| |
| |
____________________________________________________________________________
Figure 10-8. Runtime Dialog of Program pasex10a
__________________________________________________________________
| |
| Enter SQL command/clause > |
| |
| > DELETE FROM PURCHDB.PARTS WHERE PARTNUMBER = '1343-D-01';|
| |
| Begin Work |
| Prepare |
| Describe |
| Non Query SQL command. |
| Execute |
| Non-Query Command Executed Successfully. |
| |
| Commit Work |
| |
| You may enter any SQL command or "/" to STOP the program. |
| The command can be continued on the next line. The command|
| must be terminated with a semicolon (;). |
| |
| Enter SQL command/clause > |
| |
| > SELECT * FROM PURCHDB.PARTS WHERE SALESPRICE = 2000; |
| |
| Begin Work |
| Prepare |
| Describe |
| Query SQL command. |
| |
| Number of columns: 3 |
| |
| Row not found or no more rows |
| |
| Commit Work |
| |
| You may enter any SQL command or "/" to STOP the program. |
| The command can be continued on the next line. The command|
| must be terminated with a semicolon (;). |
| |
| Enter SQL command/clause > |
| |
| > / |
| Release PartsDBE |
| |
| |
| |
| |
| |
| |
| |
| |
__________________________________________________________________
Figure 10-8. Runtime Dialog of Program pasex10a (page 2 of 2)
_____________________________________________________________________________
| |
| $Heap_Dispose ON$ |
| $Heap_Compact ON$ |
| Standard_Level 'HP_Pascal$ |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *) |
| (* This program illustrates dynamic preprocessing of SQL commands *) |
| (* including SELECT commands using the DESCRIBE command. *) |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *) |
| |
| Program pasex10a (input, output); |
| type |
| |
| (* Nibbles and BCDType are data types needed for decimal type *) |
| Nibbles = 0..15; |
| BCDType = packed array [1..20] of Nibbles; |
| |
| Const |
| NotFound = 100; |
| OK = 0; |
| DeadLock = -14024; |
| |
| (* NbrFmtRecords is number of columns expected in a dynamic SELECT. *)|
| NbrFmtRecords = 1024; |
| EOF = 100; |
| MaxDataBuff = 2500; |
| |
| Var |
| |
| (* Begin Host Variable Declarations *) |
| EXEC SQL BEGIN DECLARE SECTION; |
| SQLMessage : packed array[1..132] of char; |
| (* DynamicCommand is a String that will hold the dynamic command. *) |
| DynamicCommand : String[1024]; 1 |
| EXEC SQL END DECLARE SECTION; |
| (* End Host Variable Declarations *) |
| |
| EXEC SQL INCLUDE SQLCA; |
| |
| (* SQLDA is the SQL DESCRIBE Area used by the DESCRIBE command. *) |
| EXEC SQL INCLUDE SQLDA; |
| |
| (* Each record in SQLFmts will hold information about each column |
| * in a dynamic SELECT. *) |
| SQLFmts : array[1..NbrFmtRecords] of SqlFormat_Type; |
| (* DataBuffer is the buffer containing retrieved data as a result |
| * of a dynamic SELECT. *) |
| DataBuffer : packed array[1..MaxDataBuff] of char; |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format
_____________________________________________________________________________
| |
| Abort : boolean; |
| $PAGE $ |
| (* Procedure BCDToString converts a decimal field in the "DataBuffer" |
| * buffer to its decimal presentation. Other input parameters are |
| * the Length, precision and Scale. The input decimal field is passed|
| * via "DataBuffer" and the output String is passed via "result". |
| *) |
| procedure BCDToString (DataBuffer : BCDType; Length : SmallInt; 2 |
| Precision : SmallInt; Scale : SmallInt; |
| var Result : String); |
| const |
| hexd = '0123456789ABCDEF'; (* Hexadecimal digits #001*) |
| ASCIIZero = ord('0'); |
| PlusSign = 12; |
| MinusSign = 13; |
| UnSigned = 14; |
| var |
| i, |
| DecimalPlace, |
| PutPos, |
| DataEnd, |
| DataStart : Integer; |
| done : boolean; |
| |
| begin |
| DataEnd := (Length*2) - 1; |
| DataStart := (DataEnd - Precision) + 1; |
| Result := StrRpt (' ',StrMax(Result)); |
| DecimalPlace := Precision-Scale; |
| |
| (* convert decimal to character String *) |
| if DecimalPlace = 0 then |
| begin |
| |
| Result[1] := '.'; |
| PutPos := 2; |
| |
| end |
| else |
| PutPos := 1; |
| for i := DataStart to DataEnd do |
| begin |
| |
| |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 2 of 12)
_____________________________________________________________________________
| |
| (* convert each Nibble into a character *) |
| Result[PutPos] := chr(ASCIIZero + DataBuffer[i]); |
| if PutPos = DecimalPlace then |
| begin |
| PutPos := succ(PutPos); |
| Result[PutPos] := '.'; |
| end; |
| PutPos := succ(PutPos); |
| end; |
| $PAGE $ |
| (* convert leading zeroes to spaces *) |
| Result := StrLTrim(StrRTrim(Result)); |
| i := 1; |
| done := False; |
| while (i <= StrLen(Result)) AND (not done) do |
| if Result[i] <> '0' then |
| done := True |
| else |
| begin |
| Result[i] := ' '; |
| i := succ(i); |
| end; |
| (* trim spaces from result *) |
| Result := StrLTrim(Result); |
| if Result = '' then |
| Result := '0' |
| else |
| begin |
| if Result[1] = '.' then |
| (* place a zero at the left of the decimal point *) |
| StrInsert('0', Result, 1); |
| (* insert sign *) |
| case DataBuffer[DataEnd + 1] of |
| PlusSign : StrInsert(' ', Result, 1); |
| MinusSign: StrInsert('-', Result, 1); |
| end; (*case*) |
| end; (*else*) |
| end; (*BCDToString*) |
| $PAGE $ |
| procedure SQLStatusCheck; (*Procedure to Display Error Messages*) 3 |
| begin |
| Abort := FALSE; |
| if SQLCA.SQLCODE < DeadLock then Abort := TRUE; |
| repeat |
| EXEC SQL SQLEXPLAIN :SQLMessage; |
| writeln(SQLMessage); |
| until SQLCA.SQLCODE = 0; |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 3 of 12)
_____________________________________________________________________________
| |
| if Abort then |
| begin |
| EXEC SQL COMMIT WORK RELEASE; |
| halt; |
| end; |
| end; (* End SQLStatusCheck Procedure *) |
| |
| function ConnectDBE: boolean;(* Function to Connect to PartsDBE *) 4 |
| begin |
| |
| writeln('Connect to PartsDBE'); |
| EXEC SQL CONNECT TO 'PartsDBE'; |
| |
| ConnectDBE := TRUE; |
| if SQLCA.SQLCODE <> OK then |
| begin |
| ConnectDBE := FALSE; |
| SQLStatusCheck; |
| end; (* End if *) |
| end; (* End of ConnectDBE Function *) |
| |
| procedure ReleaseDBE; (* Procedure to Release PartsDBE *) 5 |
| begin |
| |
| writeln('Release PartsDBE'); |
| EXEC SQL RELEASE; |
| |
| if SQLCA.SQLCODE <> OK then SQLStatusCheck; |
| |
| end; (* End ReleaseDBE Function *) |
| $PAGE $ |
| |
| function BeginTransaction: boolean; (* Function to Begin Work *) 6 |
| begin |
| |
| writeln; |
| writeln('Begin Work'); |
| EXEC SQL BEGIN WORK; |
| if SQLCA.SQLCODE <> OK then |
| begin |
| |
| BeginTransaction := FALSE; |
| SQLStatusCheck; |
| ReleaseDBE; |
| end |
| |
| |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 4 of 12)
_____________________________________________________________________________
| |
| else |
| BeginTransaction := TRUE; |
| |
| end; (* End BeginTransaction Function *) |
| |
| |
| procedure EndTransaction; (* Procedure to Commit Work *) 7 |
| begin |
| |
| writeln; |
| writeln('Commit Work'); |
| EXEC SQL COMMIT WORK; |
| if SQLCA.SQLCODE <> OK then SQLStatusCheck; |
| end; (* End Transaction Procedure *) |
| $PAGE $ |
| (* Procedure DisplaySelect deblocks the result of the dynamic |
| * SELECT in "DataBuffer". *) |
| procedure DisplaySelect; 8 |
| const |
| MaxColSize = 3996; |
| |
| type |
| |
| GenericColumnType = record 9 |
| case SmallInt of |
| 0 : (CharData : packed array[1..MaxColSize] of char); |
| 1 : (VarCharData : String[MaxColSize]); |
| 2 : (IntegerData : Integer); |
| 3 : (SmallIntData : SmallInt); |
| 4 : (FloatData : LongReal); |
| 5 : (DecimalData : BCDType); |
| end; |
| var |
| CurrentOffset : SmallInt; |
| NullIndOffset : SmallInt; |
| OneColumn : GenericColumnType; |
| DecString : string[20]; |
| IsNull : Boolean; |
| n,i,j,x : SmallInt; (* local loop counters *) |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 5 of 12)
_____________________________________________________________________________
| |
| $PAGE $ |
| begin |
| |
| CurrentOffset := 1; 10 |
| |
| for x := 1 to SQLDA.Sqld do (* display column names *) |
| with SQLFmts[x] do |
| begin |
| if SqlType = 5 then { Decimal data } |
| n := SqlValLen*2 |
| else |
| n := SqlValLen; |
| if SqlValLen < strlen(SqlName) then |
| write(SqlName:n) 11 |
| else |
| write(SqlName); |
| if strlen(SqlName) < SqlValLen then |
| for j := strlen(SqlName) to SqlValLen - 1 |
| do write(' '); |
| write(' | '); |
| end; |
| writeln; |
| |
| for n:= 1 to SQLDA.SqlRRow do (* for each FETCHed row *) 12 |
| begin |
| |
| for i:=1 to SQLDA.Sqld do (* for each column in a FETCHed row *) 13 |
| with SQLFmts[i] do |
| begin |
| |
| (* Check to see if this column has the value NULL. This is done *) |
| (* by checking the NULL indicator in the buffer. This indicator *) |
| (* appears after the data value for this column. *) |
| |
| IsNull := False; |
| if SqlIndLen > 0 then 14 |
| begin |
| |
| NullIndOffset := CurrentOffset + SqlNOf; 15 |
| |
| if (ord(DataBuffer[NullIndOffset]) = 0) 16 |
| AND (ord(DataBuffer[NullIndOffset+1]) = 0) then |
| IsNull := False |
| else |
| IsNull := True; |
| |
| |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 6 of 12)
_____________________________________________________________________________
| |
| end; (* end if SQLIndLen > 0 .. *) |
| |
| if IsNull then |
| write('Column is NULL | ') 17 |
| else |
| begin |
| (* Bring down the actual value of this column. *) |
| |
| StrMove(SqlValLen, DataBuffer, 18 |
| CurrentOffset + SqlVOf, OneColumn.CharData, 1); |
| |
| $PAGE $ |
| case SqlType of 19 |
| 0: (* Integer number *) |
| case SqlValLen of |
| 2: write(OneColumn.SmallIntData, ' | '); |
| 4: write(OneColumn.IntegerData, ' | '); |
| end; |
| 2: (* fixed-length character *) |
| begin |
| for j := 1 to SqlValLen do |
| write(OneColumn.CharData[j]); |
| write(' | '); |
| end; |
| 3: (* variable-length char *) |
| begin |
| write(OneColumn.VarCharData, ' | '); |
| end; |
| 4: (* floating point *) |
| begin |
| write(OneColumn.FloatData, ' | '); |
| end; |
| 5: (* Packed decimal *) |
| begin |
| |
| BCDToString(OneColumn.DecimalData, SqlValLen, |
| SqlPrec, SqlScale, DecString); |
| write(DecString:SqlValLen*2, ' | '); |
| |
| end; |
| end; (* case statement *) |
| |
| end; (* if IsNull *) |
| |
| end; (* for i/with SQLFmts[i] ... *) |
| |
| |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 7 of 12)
______________________________________________________________________________
| |
| CurrentOffset := CurrentOffset + SQLDA.SqlRowLen; 20 |
| |
| writeln; |
| |
| end; (* for n := ... *) |
| |
| writeln; |
| |
| end; (* end of DisplaySelect *) |
| |
| $PAGE $ |
| procedure GetCommand; 21 |
| |
| var |
| DynamicClause : String[80]; |
| Pos : SmallInt; |
| |
| begin |
| |
| writeln; |
| writeln('You may enter any SQL command or "/" to STOP the program.'); |
| writeln('The command can be continued on the next line. The command');|
| writeln('must be terminated with a semicolon (;).'); |
| writeln; |
| writeln('Enter SQL command/clause >'); |
| writeln; |
| DynamicCommand := ''; |
| repeat |
| prompt('> '); |
| readln(DynamicClause); |
| if DynamicClause <> '/' then |
| begin |
| DynamicCommand := DynamicCommand + ' ' + DynamicClause; |
| Pos := StrPos(DynamicClause, ';'); |
| if Pos <> 0 then DynamicClause := '/'; |
| end |
| else |
| DynamicCommand := '/'; |
| until DynamicClause = '/'; (* end repeat *) |
| end; (* end of GetCommand procedure *) |
| $PAGE $ |
| procedure NonQuery;forward; 22 |
| procedure Query;forward; |
| procedure Describe; (* Describe Procedure *) 23 |
| |
| |
______________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 8 of 12)
_____________________________________________________________________________
| |
| begin |
| |
| with SQLDA do (* set up SQLDA fields *) |
| begin |
| Sqln := NbrFmtRecords; (* number of columns expected *) 24 |
| SqlFmtArr := waddress(SQLFmts); |
| end; |
| |
| repeat |
| GetCommand; 25 |
| |
| if DynamicCommand <> '/' then |
| begin |
| |
| if BeginTransaction then |
| begin |
| writeln('Prepare'); |
| EXEC SQL PREPARE CMD1 FROM :DynamicCommand; 26 |
| if SQLCA.SQLCODE <> OK then |
| begin |
| SqlStatusCheck; |
| EndTransaction; |
| end |
| else |
| begin |
| writeln('Describe'); |
| EXEC SQL DESCRIBE CMD1 INTO SQLDA; 27 |
| if SQLCA.SQLCODE <> OK then |
| begin |
| SqlStatusCheck; |
| EndTransaction; |
| end |
| else |
| begin |
| if SQLDA.Sqld = 0 then NonQuery 28 |
| else Query; |
| end; (* end if SQLCA.SQLCODE <> OK after DESCRIBE *) |
| end; (* end if SQLDA.SQLCODE <> OK after PREPARE *) |
| end (* end if BeginTransaction *) |
| else (* BeginTransaction failed; force *) |
| DynamicCommand := '/'; (* logical end to Describe proc.*) |
| |
| end; (* end if DynamicCommand *) |
| until DynamicCommand = '/'; (* end repeat *) |
| |
| end; (* end of Describe procedure *) |
| |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 9 of 12)
_____________________________________________________________________________
| |
| $PAGE $ |
| procedure NonQuery; 29 |
| begin |
| |
| writeln ('Non Query SQL command.'); |
| writeln ('Execute'); |
| EXEC SQL EXECUTE CMD1; |
| if SQLCA.SQLCODE <> OK then |
| begin |
| SqlStatusCheck; |
| EXEC SQL ROLLBACK WORK; |
| end |
| else |
| begin |
| writeln ('Non-Query Command Executed Successfully.'); |
| EndTransaction; |
| end; |
| |
| end; (* end of NonQuery procedure *) |
| |
| |
| |
| $PAGE $ |
| procedure Query; 30 |
| |
| var |
| RowLength : SmallInt; |
| i : SmallInt; |
| |
| begin |
| writeln ('Query SQL command.'); |
| writeln; |
| writeln('Number of columns: ',SQLDA.Sqld:2); 31 |
| writeln; |
| |
| EXEC SQL DECLARE CURSOR1 CURSOR FOR CMD1; 32 |
| EXEC SQL OPEN CURSOR1; |
| if SQLCA.SQLCODE <> OK then SQLStatusCheck |
| else |
| begin |
| with SQLDA do |
| begin |
| SqlBufLen := sizeof(DataBuffer); 33 |
| |
| SqlNRow := SqlBufLen DIV SqlRowLen; 34 |
| SqlRowBuf := waddress(DataBuffer); |
| end; |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 10 of 12)
_____________________________________________________________________________
| |
| while SQLCA.SQLCODE = 0 do |
| begin |
| EXEC SQL FETCH CURSOR1 USING DESCRIPTOR SQLDA; 35 |
| if SQLCA.SQLCODE <> OK then |
| begin |
| if SQLCA.SQLCODE = EOF then |
| writeln('Row not found or no more rows') |
| else |
| SQLStatusCheck; |
| end |
| else |
| DisplaySelect; 36 |
| end; (* end of while SQLCA.SQLCODE = 0 *) |
| |
| EXEC SQL CLOSE CURSOR1; |
| if SQLCA.SQLCODE <> OK then SqlStatusCheck; |
| end; (* end of OPEN CURSOR OK *) |
| |
| EndTransaction; |
| |
| end; (* end of Query procedure *) |
| |
| |
| |
| $PAGE $ |
| begin (* Beginning of Program *) |
| |
| writeln('Pascal program illustrating dynamic command processing.'); |
| writeln; |
| writeln('Event List:'); |
| writeln(' Connect to PartsDBE'); |
| writeln(' Prompt for any SQL command '); |
| writeln(' Begin Work'); |
| writeln(' Prepare '); |
| writeln(' Describe '); |
| writeln(' If command is a non-query command, EXECUTE it'); |
| writeln(' Otherwise execute the following:'); |
| writeln(' Declare '); |
| writeln(' Open '); |
| writeln(' Fetch '); |
| writeln(' Close '); |
| writeln(' Commit Work'); |
| writeln(' Repeat the above ten steps'); |
| writeln(' Release PartsDBE'); |
| writeln; |
| |
| |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 11 of 12)
_____________________________________________________________________________
| |
| if ConnectDBE then 37 |
| begin |
| |
| Describe; |
| ReleaseDBE |
| |
| end |
| |
| else |
| writeln('Error: Cannot Connect to PartsDBE'); |
| |
| end. (* End of Program *) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 10-9. Program pasex10a: Dynamic Commands of Unknown Format (page 12 of 12)