 |
» |
|
|
|
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. pasex10a: Program Using Dynamic Commands of Unknown Type | |
Pasex10a contains the logic to determine whether or not a given
dynamic command is a query or non-query, and if it is a query,
it uses format array information
to parse the data buffer. A flow chart for this program appears in
Figure 10-7.
The runtime dialog and source code
are shown in Figures 10-8 and 10-9, respectively. 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 2048 bytes, including the semicolon, this
variable is declared 1 as String[2048]. 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.SqlNRow (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.SqlNRow, 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 DisplaySelect
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 each FETCH
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 following
message at 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 WORK
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 terminate
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.
Figure 10-7 Flow Chart of Program pasex10a
![[Flow Chart of Program pasex10a]](img/xbf0807a.gif)
![[Flow Chart of Program pasex10a]](img/xbf0807b.gif)
Figure 10-8 Runtime Dialog of Program pasex10a
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 (;).
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-9 Program pasex10a: Dynamic Commands of Unknown Format
(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)
(* 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[2048]; 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;
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
(* 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;
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 '../sampledb/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
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 *)
$PAGE $
begin
CurrentOffset := 1; 10
for x := 1 to SQLDA.Sqld do (* display column names *)
with SQLFmts[x] do
begin
write(SqlName, ' | '); 11
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;
end; (* end if SQLIndLen > 0 .. *)
if IsNull then
write('Column is NULL | ') 17
else
begin
(* Now 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,8: (* fixed-length character *)
begin
for j := 1 to SqlValLen do
write(OneColumn.CharData[j]);
write(' | ');
end;
3,9: (* 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;
otherwise writeln ('SQLType = ',sqltype1);
end; (* case statement *)
end; (* if IsNull *)
end; (* for i/with SQLFmts[i] ... *)
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
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 procedure *)
end; (* end if DynamicCommand *)
until DynamicCommand = '/'; (* end repeat *)
end; (* end of Describe procedure *)
$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;
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 & 0 then SqlStatusCheck;
end; (* end of if OPEN CURSOR is 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;
if ConnectDBE then 37
begin
Describe;
ReleaseDBE
end
else
writeln('Error: Cannot Connect to PartsDBE');
end. (* End of Program *)
|
pasex10b: Program Using Dynamic Queries of Known Format | |
In some applications, you may know the format of a
query result in advance, but want to dynamically preprocess
the query to create a program that does not have a permanently stored
module. Database administration
utilities that include system catalog queries often fall into this category
of application. In programs hosting dynamic queries having query results of known format,
you do not need to use the format array to parse the data
buffer. Because you know in advance the query result format,
you can pre-define an array having a complementary format and
read information from the array without having to determine
where data is and the format it has been returned in. An example of this type is program pasex10b.
A flowchart for pasex10b is shown in Figure 10-10,
the runtime dialog is in Figure 10-11, and
the source code is in Figure 10-12.
Pasex10b executes two dynamic queries with select lists known
at programming time. The program reads the SYSTEM.TABLE
view and the SYSTEM.COLUMN view in order to re-create
the SQL CREATE TABLE commands originally used to define tables in a
DBEnvironment. The CREATE TABLE commands are stored in a
file you name when you execute the program. Such a file
can be used as an ISQL command file in order to re-create the tables in some other DBEnvironment. The program first prompts 6 for the name of the file
in which to store the
table definitions. It purges 7 any file that exists by the same name. The program then prompts for a DBEnvironment name 8 .
The DBEnvironment name is used to build a CONNECT command in
host variable CmdLine 9 . The CONNECT command is executed
by using the EXECUTE IMMEDIATE command 10 . The program then prompts for an owner name 11 . If an owner name
is entered, it is upshifted 12 , then added to the WHERE clause in
the first dynamic query 14 :
CmdLine := 'SELECT OWNER, NAME, DBEFILESET, RTYPE FROM SYSTEM.TABLE'
+ ' WHERE TYPE = 0 AND OWNER = ''' + OwnerName + ''';';
|
This query retrieves a row for every table (TYPE = 0) having an owner name as specified in the variable OwnerName. Each row consists of four columns: the owner name, the table name, the name of the DBEFileSet with which the table is associated, and
the automatic locking mode. To obtain a definition of all tables in a DBEnvironment except those owned by SYSTEM, the user presses the carriage return
in response to the owner name prompt. In this case, the program uses the following form of the dynamic query 13 :
CmdLine := 'SELECT OWNER, NAME, DBEFILESET, RTYPE FROM SYSTEM.TABLE'
+ ' WHERE TYPE = 0 AND OWNER <> ''SYSTEM'';'
|
The PREPARE command 15 creates a temporary section named SelectCmd1
for the dynamic query from CmdLine. Then the program initializes the two SQLDA fields 16 needed
by the DESCRIBE command 17 . Because the number of
columns in the query result is known to be four at
programming time, SqlN is set to 4. Four of the
format array records will be needed, one per select list item. The program then declares and opens a cursor named TableList for
the dynamic query 18 . Before using the cursor to retrieve rows, the
program initializes the SQLDA 19 as follows: The SqlBufLen field is set to the size of the data buffer.
In this program, the data buffer for the first query is a packed array
of records named TableList 4 .
Note that each record in the array consists of four elements,
one for each item in the select list.
The elements are declared with types compatible with those in the
corresponding SYSTEM.TABLE columns.
The SqlRowBuf field is set to the address of the
data buffer.
The SqlNRow field is set to 300, defined in the
constant MaxNbrTables 1 . This number is the maximum number of
rows ALLBASE/SQL will return from the active set when the FETCH
command is executed.
After initializing the required fields in the SQLDA, the
program executes the
FETCH command 20 . Because the FETCH command is executed only once,
this program can re-create table definitions for a maximum of 300 tables. After the FETCH command is executed, the value in SQLCA.SQLERRD[3]
is saved in variable NumOfTables 21 . This value
indicates the number of rows ALLBASE/SQL returned to the data buffer.
NumOfTables is used
later as the final value of a counter 23 to control the number of
times the second dynamic query is executed; the second query must be
executed once for each table qualifying for the first query. After terminating the transaction that executes the first query 22 , the
program uses the STRMOVE procedure 24 to move CHAR values to string
variables so that other Pascal string procedures can be used
when formatting the CREATE TABLE commands and writing them to the
output file. The second query 26
retrieves information about
each column in each table qualifying for the first query.
This query contains a WHERE clause that
identifies an owner and table name:
CmdLine := 'SELECT COLNAME, LENGTH, TYPECODE, NULLS, PRECISION,'
+ ' SCALE FROM SYSTEM.COLUMN WHERE OWNER = '''
+ ' OwnerName + ''' AND TABLENAME = ''' + TableName + ''';';
|
These names are obtained from the Owner and Table values in the
TableList array 4 after trailing blanks are trimmed by
using the STRRTRIM function 25 . Note that trailing blanks are also
trimmed off the current TableList.FileSet value.
Trailing blanks are
removed from these three values so excess blanks do not appear when
the values are written to the file containing the table definition. After each version of the
second query is dynamically preprocessed 27 , the program
initializes two SQLDA fields 28 before executing the
DESCRIBE command 29 . Then
a cursor named
ColumnList is declared and opened 30 to operate on the active set.
Before fetching rows, the program initializes 31 the necessary SQLDA
values: The SqlBufLen field is set to the size of the data buffer.
The data buffer for the second query is a packed array
of records named
ColumnList 5 .
The SqlRowBuf field is set to the address of the
data buffer. The SqlNRow field is set to 255, defined in the
constant MaxNbrColumns 2 . This number is the maximum number of
rows ALLBASE/SQL will return from the active set when the FETCH
command is executed.
The FETCH command 32 is executed only once for each table that qualified
for the first query, since no more than 255 rows would ever qualify
for the query. The maximum number of columns any table
can have is 255. After the active set has been fetched into data buffer ColumnList,
a CREATE TABLE command for the table is written to the schema file 33 :
CREATE LockMode TABLE OwnerName.TableName,
(ColumnList.ColName[1] TypeInfo NullInfo,
ColumnList.ColName[2] TypeInfo NullInfo,
.
.
.
ColumnList.ColName[j] TypeInfo NullInfo) IN TableList.FileSet[i];
|
Most of the information needed to reconstruct the CREATE TABLE commands
is written directly from program variables. In three cases, however, data
returned from the system views must be translated: LockMode is generated in a CASE statement 34 based
on the value ALLBASE/SQL put in TableList.LockMode. The
SYSTEM.TABLE view stores the automatic locking mode for tables as
an integer from 1 through 3. The CASE statement equates these
codes with the expressions that must appear in the CREATE TABLE
command.
TypeInfo is generated in a CASE statement 35 based on the
value ALLBASE/SQL put in ColumnList.TypeCode. The SYSTEM.COLUMN
view stores the data type of each column as an integer from 0 through
5. The CASE statement equates these codes with the expressions that
must appear in the CREATE TABLE command.
NullInfo is generated from the null indicator ALLBASE/SQL returned
to ColumnList.Nulls 36 . A value of 0 indicates the column
cannot contain null values, and the program inserts NOT NULL
into the table definition.
After a CREATE TABLE command has been written for each qualifying
table, a COMMIT WORK command is executed 37 to release
locks on SYSTEM.COLUMN before the PREPARE command is re-executed and
before the DBE session terminates 38 . Figure 10-10 Flow Chart of Program pasex10b
![[Flow Chart of Program pasex10b]](img/xbf0810a.gif)
![[Flow Chart of Program pasex10b]](img/xbf0810b.gif)
In the runtime dialog shown in Figure 10-11, the name of the DBEnvironment
must be entered with upper and lower case as shown. The name of the
schema file and the name of the owner can be entered with either upper or lower case. Figure 10-11 Runtime Dialog of Program pasex10b
ALLBASE/SQL SCHEMA Generator for Tables X.00.00
Enter name of schema file to be generated > schm1
Enter name of DBEnvironment > ../sampledb/PartsDBE
Enter owner name or RETURN for all owners > PURCHDB
Generating SQL command to CREATE TABLE PURCHDB.INVENTORY
Generating SQL command to CREATE TABLE PURCHDB.ORDERITEMS
Generating SQL command to CREATE TABLE PURCHDB.ORDERS
Generating SQL command to CREATE TABLE PURCHDB.PARTS
Generating SQL command to CREATE TABLE PURCHDB.REPORTS
Generating SQL command to CREATE TABLE PURCHDB.SUPPLYPRICE
Generating SQL command to CREATE TABLE PURCHDB.VENDORS
$ more schm1
CREATE PUBLIC TABLE PURCHDB.INVENTORY
(PARTNUMBER CHAR( 16) NOT NULL,
BINNUMBER SMALLINT NOT NULL,
QTYONHAND SMALLINT,
LASTCOUNTDATE CHAR( 8),
COUNTCYCLE SMALLINT,
ADJUSTMENTQTY SMALLINT,
REORDERQTY SMALLINT,
REORDERPOINT SMALLINT) IN WAREHFS;
CREATE PUBLIC TABLE PURCHDB.ORDERITEMS
(ORDERNUMBER INTEGER NOT NULL,
ITEMNUMBER INTEGER NOT NULL,
VENDPARTNUMBER CHAR( 16),
PURCHASEPRICE DECIMAL(10, 2) NOT NULL,
ORDERQTY SMALLINT,
ITEMDUEDATE CHAR( 8),
RECEIVEDQTY SMALLINT) IN ORDERFS;
CREATE PUBLIC TABLE PURCHDB.ORDERS
(ORDERNUMBER INTEGER NOT NULL,
VENDORNUMBER INTEGER,
ORDERDATE CHAR( 8)) IN ORDERFS;
CREATE PUBLIC TABLE PURCHDB.PARTS
(PARTNUMBER CHAR( 16) NOT NULL,
PARTNAME CHAR( 30),
SALESPRICE DECIMAL(10, 2)) IN WAREHFS;
CREATE PUBLIC TABLE PURCHDB.REPORTS
(REPORTNAME CHAR( 20) NOT NULL,
REPORTOWNER CHAR( 20) NOT NULL,
.
.
.
|
Figure 10-12 Program pasex10b: Dynamic Queries of Known Format
(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)
(* This program generates an ISQL Command File that contains CREATE *)
(* TABLE commands for a particular DBEnvironment. This program must *)
(* be preprocessed; however, it does not need to be INSTALLed. *)
(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *)
Program pasex10b(input, output);
label
9999;
type
UXFile = packed array [1..100] of char;
FilePtr = UXFile;
const
MaxNbrTables = 300; 1
MaxNbrColumns = 255; 2
OK = 0;
mode = 0700;
var
EXEC SQL BEGIN DECLARE SECTION;
MsgLine : Packed Array [ 1..132] of char;
CmdLine : String[200];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
SchemaFile : Text;
FileName : FilePntr;
OwnerName : String[20];
TableName : String[20];
DBEFileSet : String[20];
ColumnName : String[20];
DBEName : String[50];
OneLine : String[80];
i,j : SmallInt;
Pos : Integer;
NumOfTables : SmallInt;
$PAGE $
SqlFmts : Array[1..6] of SQLFormat_Type; 3
TableList : Packed Array[1..MaxNbrTables] of Record 4
Owner : Packed Array[1..20] of char;
Table : Packed Array[1..20] of char;
FileSet : Packed Array[1..20] of char;
LockMode : SmallInt;
end;
ColumnList : Packed Array[1..MaxNbrColumns] of Record 5
ColName : Packed Array[1..20] of char;
Length : Integer;
TypeCode : SmallInt;
Nulls : SmallInt;
Precision : SmallInt;
Scale : SmallInt;
end;
$PAGE $
begin
writeln;
writeln('ALLBASE/SQL SCHEMA Generator for Tables X.00.00');
writeln;
prompt('Enter name of schema file to be generated > '); 6
readln(FileName);
rewrite(SchemaFile, FileName);
prompt('Enter name of DBEnvironment > '); 8
readln(DBEName);
CmdLine := 'CONNECT TO''' + DBEName + ''';'; 9
EXEC SQL EXECUTE IMMEDIATE : CmdLine; 10
if SQLCA.SQLCODE <> OK then
begin
writeln('Could not CONNECT to DBEnvironment.');
EXEC SQL SQLEXPLAIN : SQLMessage;
writeln(SQLMessage);
goto 9999;
end;
$PAGE $
prompt('Enter owner name or RETURN for all owners > '); 11
readln(OwnerName);
OwnerName := StrLTrim(StrRTrim(OwnerName));
(* Upshift OwnerName *)
for i := 1 to StrLen(OwnerName) do << 12 >>
if OwnerName[i] in ['a'..'z'] then
OwnerName[i] := chr(ord(OwnerName[i]) - ord('a') + ord('A'));
writeln;
if OwnerName = '' then
CmdLine:= 'SELECT OWNER,NAME,DBEFILESET,RTYPE FROM SYSTEM.TABLE' 13
+ ' WHERE TYPE = 0 AND OWNER <> ''SYSTEM'';'
else
CmdLine:= 'SELECT OWNER,NAME,DBEFILESET,RTYPE FROM SYSTEM.TABLE' 14
+ ' WHERE TYPE = 0 AND OWNER = ''' + OwnerName + ''';';
EXEC SQL PREPARE SelectCmd1 FROM :CmdLine; 15
if SQLCA.SQLCODE <> OK then
begin
writeln('Problem PREPARING the SELECT command.');
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
goto 9999;
end;
with SQLDA do (* set up SQLDA fields *) 16
begin
Sqln := 4; (* number of columns expected *)
SqlFmtArr := waddress(SQLFmts);
end;
EXEC SQL DESCRIBE SelectCmd1 INTO SQLDA; 17
$PAGE $
EXEC SQL DECLARE TableList CURSOR for SelectCmd1; 18
EXEC SQL OPEN TableList;
if SQLCA.SQLCODE <> OK then
begin
writeln('Problem opening TableList cursor.');
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
goto 9999;
end;
with SQLDA do 19
begin
SqlBufLen := SizeOf(TableList);
SqlRowBuf := Waddress(TableList);
SqlNRow := MaxNbrTables;
end;
(* Get Table List from SYSTEM.TABLE *)
EXEC SQL FETCH TableList USING DESCRIPTOR SQLDA; 20
if SQLCA.SQLCODE = 100 then
begin
writeln('No tables qualified.');
goto 9999;
end
else if SQLCA.SQLCODE <> OK then
begin
writeln('Problem encountered when reading SYSTEM.TABLE');
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
goto 9999;
end;
NumOfTables := SQLCA.SQLERRD[3]; 21
EXEC SQL COMMIT WORK; 22
$PAGE $
for i := 1 to NumOfTables do 23
with TableList[i] do
begin
OwnerName := '';
StrMove(20, Owner, 1, OwnerName,1); 24
OwnerName := StrRTrim(OwnerName);
TableName := '';
StrMove(20, Table, 1, TableName, 1);
TableName := StrRTrim(TableName);
DBEFileSet := '';
StrMove(20, FileSet, 1, DBEFileSet, 1);
DBEFileSet := StrRTrim(DBEFileSet); 25
write('Generating SQL command to CREATE TABLE ');
writeln(OwnerName, '.', TableName);
CmdLine:='SELECT COLNAME, LENGTH, TYPECODE, NULLS, PRECISION,' 26
+ ' SCALE FROM SYSTEM.COLUMN WHERE OWNER = '''
+ OWNERNAME + ''' AND TABLENAME = ''' + TableName + ''';';
EXEC SQL PREPARE SelectCmd2 FROM :CmdLine; 27
if SQLCA.SQLCODE <> OK then
begin
writeln('Problem PREPARING the SELECT #2 command.');
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
goto 9999;
end;
with SQLDA do (* set up SQLDA fields *) 28
begin
Sqln := 6; (* number of columns expected *)
SqlFmtArr := waddress(SQLFmts);
end;
EXEC SQL DESCRIBE SelectCmd2 INTO SQLDA; 29
$PAGE $
EXEC SQL DECLARE ColumnList CURSOR for SelectCmd2; 30
EXEC SQL OPEN ColumnList;
if SQLCA.SQLCODE <> OK then
begin
writeln('Problem opening cursor #2.');
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
goto 9999;
end;
with SQLDA do 31
begin
SqlBufLen := SizeOf(ColumnList);
SqlRowBuf := Waddress(ColumnList);
SqlNRow := MaxNbrColumns;
end;
(* Get Column List from SYSTEM.COLUMN *)
EXEC SQL FETCH ColumnList USING DESCRIPTOR SQLDA; 32
if SQLCA.SQLCODE <> OK then
begin
writeln('Problem encountered when reading SYSTEM.COLUMN');
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
goto 9999;
end;
$PAGE $
writeln(SchemaFile);
OneLine := 'CREATE '; 33
Pos := 8;
case LockMode of 34
1 : StrWrite(OneLine, Pos, Pos, 'PUBLICREAD ');
2 : StrWrite(OneLine, Pos, Pos, 'PRIVATE ');
3 : StrWrite(OneLine, Pos, Pos, 'PUBLIC ');
end; (* end case *)
StrWrite(OneLine, Pos, Pos, 'TABLE ', OwnerName, '.', TableName);
writeln(SchemaFile, OneLine);
OneLine := ' (';
Pos := 4;
for j := 1 to SQLCA.SQLERRD[3] do
with ColumnList[j] do
begin
ColumnName := '';
StrMove(20, ColName, 1, ColumnName, 1);
StrWrite(OneLine, Pos, Pos, ColumnName, ' ');
case TypeCode of 35
0 : if Length = 4 then
StrWrite(OneLine, Pos, Pos,
'INTEGER ')
else
StrWrite(OneLine, Pos, Pos,
'SMALLINT ');
2,8 : StrWrite(OneLine, Pos, Pos,
'CHAR(', Length:4, ') ');
3,9 : StrWrite(OneLine, Pos, Pos,
'VARCHAR(', Length:4, ') ');
4 : StrWrite(OneLine, Pos, Pos,
'FLOAT ');
5 : StrWrite(OneLine, Pos, Pos,
'DECIMAL(', Precision:2, ',', Scale:2, ') ');
otherwise StrWrite(OneLine, Pos, Pos, '****');
end; (* case *)
if Nulls = 0 then 36
OneLine := OneLine + 'NOT NULL'
else
OneLine := StrRTrim(OneLine);
if j <> SQLCA.SQLERRD[3] then
OneLine := OneLine + ','
else
OneLine := OneLine + ') IN ' + DBEFileSet + ';';
writeln(SchemaFile, OneLine);
OneLine := ' ';
Pos := 4;
end; (* for j := 1 to SQLCA.SQLERRD[3] *)
EXEC SQL COMMIT WORK; 37
end; (* for i := 1 to NumOfTables *)
9999:
EXEC SQL COMMIT WORK RELEASE; 38
close (SchemaFile,'SAVE');
end.
|
|
