HP 3000 Manuals HP 3000 Manuals
Programs Using Dynamic Command Operations (continued)
Sample 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 applications.
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.
Program pasex10b, whose flow chart is shown in Figure 10-10 , whose
execution is illustrated in Figure 10-11 , and whose source code
appears in Figure 10-12 , 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 permanent ASCII 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 th 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 th 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 . After the RELEASE command is executed, the file
equations created within the program are reset 39 , and the program
terminates.
![[]](../../pic3k/M012400/FFN1333c50.gif)
Figure 10-10. Flow Chart of Program pasex10b
![[]](../../pic3k/M012400/FFN1343c50.gif)
Figure 10-10. Flow Chart of Program pasex10b (page 2 of 2)
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.
__________________________________________________________________
| |
| ALLBASE/SQL SCHEMA Generator for Tables X.00.00|
| |
| Enter name of schema file to be generated > SCHM1 |
| Enter name of DBEnvironment > 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 |
| :PRINT 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-11. Runtime Dialog of Program pasex10b
_____________________________________________________________________________
| |
| $Heap_dispose ON$ |
| $Heap_Compact ON$ |
| Standard_Level 'HP_Pascal$ |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *) |
| (* This program generates an ISQL Command File that will re-create *) |
| (* tables within a particular DBEnvironment. This program must be *) |
| (* preprocessed; however, it does not need to be INSTALLed. *) |
| (* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *) |
| |
| Program pasex10b(input, output); |
| |
| label |
| 9999; |
| |
| const |
| MaxNbrTables = 300; 1 |
| CR = chr(13); (* Carriage Return *) |
| MaxNbrColumns = 64; 2 |
| |
| OK = 0; |
| |
| var |
| |
| EXEC SQL BEGIN DECLARE SECTION; |
| SQLMessage : 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 : String[20]; |
| OwnerName : String[20]; |
| TableName : String[20]; |
| DBEFileSet : String[20]; |
| ColumnName : String[20]; |
| DBEName : String[50]; |
| OneLine : String[80]; |
| i,j : SmallInt; |
| Pos : SmallInt; |
| NumOfTables : SmallInt; |
| ErrorCode : SmallInt; |
| Parm : SmallInt; |
| |
| |
_____________________________________________________________________________
Figure 10-12. Program pasex10b: Dynamic Queries of Known Format
_________________________________________________________________________________
| |
| $PAGE $ |
| SqlFmts : Array[1..6] of SQLFormat_Type; 3 |
| |
| TableList : Packed Array[1..MaxNbrTables] of Packed 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 Packed Record 5 |
| ColName : Packed Array[1..20] of char; |
| Length : Integer; |
| TypeCode : SmallInt; |
| Nulls : SmallInt; |
| Precision : SmallInt; |
| Scale : SmallInt; |
| end; |
| |
| procedure Command; intrinsic; |
| |
| $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); |
| |
| CmdLine := 'PURGE ' + FileName + CR; 7 |
| Command(CmdLine, ErrorCode, Parm); |
| |
| CmdLine := 'FILE ' + FileName + ',NEW;DEV=DISC;REC=-80,16,F,ASCII'; |
| CmdLine := CmdLine + ';SAVE;NOCCTL' + CR; |
| Command(CmdLine, ErrorCode, Parm); |
| |
| if ErrorCode <> OK then |
| begin |
| writeln('Problem equating Schema file. Error Code=(',ErrorCode:1,')')|
| goto 9999; |
| end; |
| |
| rewrite(SchemaFile, FileName); |
| |
_________________________________________________________________________________
Figure 10-11. Program pasex10b: Dynamic Queries of Known Format (page 2 of 7)
_____________________________________________________________________________
| |
| prompt('Enter name of DBEnvironment > '); 8 |
| readln(DBEName); |
| |
| CmdLine := 'CONNECT TO ''' + DBEName + ''';'; |
| EXEC SQL EXECUTE IMMEDIATE :CmdLine; 9 |
| 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 s 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; |
| |
| |
_____________________________________________________________________________
Figure 10-11. Program pasex10b: Dynamic Queries of Known Format (page 3 of 7)
_____________________________________________________________________________
| |
| EXEC SQL DESCRIBE SelectCmd1 INTO SQLDA; 17 |
| $PAGE $ |
| |
| if SQLCA.SQLCODE <> OK then |
| begin |
| writeln('Problem DESCRIBING SELECT FROM SYSTEM.TABLE.'); |
| EXEC SQL SQLEXPLAIN :SQLMessage; |
| writeln(SQLMessage); |
| goto 9999; |
| end; |
| |
| $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; |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 10-11. Program pasex10b: Dynamic Queries of Known Format (page 4 of 7)
_____________________________________________________________________________
| |
| 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 |
| if SQLCA.SQLCODE <> OK then |
| begin |
| writeln('Problem DESCRIBING SELECT FROM SYSTEM.COLUMN.'); |
| EXEC SQL SQLEXPLAIN :SQLMessage; |
| writeln(SQLMessage); |
| goto 9999; |
| end; |
_____________________________________________________________________________
Figure 10-11. Program pasex10b: Dynamic Queries of Known Format (page 5 of 7)
_____________________________________________________________________________
| |
| $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; |
| |
_____________________________________________________________________________
Figure 10-11. Program pasex10b: Dynamic Queries of Known Format (page 6 of 7)
_____________________________________________________________________________
| |
| 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 : StrWrite(OneLine, Pos, Pos, |
| 'CHAR(', Length:4, ') '); |
| 3 : 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 |
| |
| CmdLine := 'RESET SCHEMDBE' + CR; 39 |
| Command(CmdLine, ErrorCode, Parm); |
| CmdLine := 'RESET ' + FileName + CR; |
| Command(CmdLine, ErrorCode, Parm); |
| writeln; |
| end. |
_____________________________________________________________________________
Figure 10-11. Program pasex10b: Dynamic Queries of Known Format (page 7 of 7)