 |
» |
|
|
|
The rest of this chapter contains sample programs that illustrate the
use of dynamic preprocessing techniques for queries. There are two
complete programs: cex10a, which contains statements for executing any dynamic command
(non-query or query with unknown format). cex10b, 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. cex10a: Program for 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 8-7 illustrates the logic
for one such program, cex10a. The run-time dialog and
source code for this
program are shown in Figures 8-8 and 8-9, respectively. Program cex10a executes
function ConnectDBE 4 to
invoke the CONNECT command 37 to
start a DBE session in the sample database.
It then executes the function named
Describe 23 . This function: 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 function GetCommand 25
and processes commands accepted
from the user in that function until the user enters a slash (/).
Function GetCommand 25
accepts SQL commands into the host
variable named DynamicCommand.
This variable is declared 1 as char DynamicCommand[2048]
to allow for a
dynamic command of up to 2048 bytes, including the semicolon.
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. After SQL command entry is complete, control returns to function
Describe 23 , which: Starts a transaction by executing
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 29 . If
this value is 0, the command is not a query
and function NonQuery 28
is invoked to execute the command.
If the sqlda.sqld value is not 0,
function Query 29 is invoked to
execute the command.
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 function Describe,
which calls both function NonQuery and function Query, which
follow after function Describe sequentially in the source code. Function Query 29 : 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 function
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.
Function DisplaySelect 36 parses the data buffer after
each
FETCH operation and displays the fetched rows: The function keeps track of the beginning of each row by
using a local variable, CurrentOffset, as a pointer.
CurrentOffset
is initialized to 0 10 at the beginning of function
DisplaySelect.
Column headings are written from the sqlfmts[x].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 sqlfmts[i].sqlindlen field of each column's format array record is
examined 14 to determine whether a null value may exist.
If a column can contain null values, sqlfmts[i].sqlindlen is greater
than zero, and the function 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.
Sqlfmts[i].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.
DataBuffer and NullIndOffset
are used to determine
whether or not a null value exists.
If a null value exists,
the function 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 function 30 is computed by adding
the current value of sqlfmts[i].sqlvof to the current value of
CurrentOffset. Sqlfmts[i].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
sqlfmts[i].sqlvallen.
OneColumn.CharData is one of the
variations of the variant record, GenericType 9 .
The GenericType type definition
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 sqlfmts[i].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,
function Query
calls function EndTransaction 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 each SQL command
is executed.
Function 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 Connect flag to FALSE, calls function
SQLStatusCheck, and
then terminates the program.
If the BEGIN WORK command fails, function BeginTransaction 6
calls SQLStatusCheck to display messages, then calls function
ReleaseDBE 5 to end the DBE session. The program then
terminates
because function Describe 23 sets DynamicCommand
to a slash 31 .
If other SQL commands fail,
function SQLStatusCheck terminates the program whenever
the error is serious enough to return an sqlca.sqlcode less than
-14024.
Figure 8-7 Flow Chart of Program cex10a
![[Flow Chart of Program cex10a]](img/xbf0807a.gif)
![[Flow Chart of Program cex10a]](img/xbf0807b.gif)
Figure 8-8 Run Time Dialog of Program cex10a
C program illustrating dynamic command processing -- cex10a
Event List:
CONNECT TO PartsDBE
Prompt for any SQL command
BEGIN WORK
PREPARE
DESCRIBE
If the command is a non-query command, execute it;
otherwise execute the following:
DECLARE CURSOR
OPEN Cursor
FETCH a row
CLOSE CURSOR
COMMIT WORK
Repeat the above ten steps until the user enters a /
RELEASE 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 your SQL command or clause >
> SELECT * FROM PURCHDB.PARTS WHERE SALESPRICE = 2000;
Begin Work
Prepare SELECT * FROM PURCHDB.PARTS WHERE SALESPRICE = 2000;
Describe
A Query SQL command was entered.
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 your SQL command or clause >
> DELETE FROM PURCHDB.PARTS WHERE PARTNUMBER = '1343-D-01';
Begin Work
Prepare DELETE FROM PURCHDB.PARTS WHERE PARTNUMBER = '1343-D-01';
Describe
A Non-Query SQL command was entered.
Execute
The 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 your SQL command or clause >
> SELECT * FROM PURCHDB.PARTS WHERE SALESPRICE = 2000;
Begin Work
Prepare SELECT * FROM PURCHDB.PARTS WHERE SALESPRICE = 2000;
Describe
A Query SQL command was entered.
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 your SQL command or clause > /
Release PartsDBE
|
Figure 8-9 Program cex10a: Dynamic Queries of Unknown Format
/* Program cex10a */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/* This program illustrates dynamic preprocessing of SQL commands */
/* including SELECT commands using the DESCRIBE command. */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
#include <stdio.h>
#include <malloc.h>
typedef int boolean;
#define NotFound 100
#define OK 0
#define DeadLock -14024
/* NbrFmtRecords is number of columns expected in a dynamic SELECT. */
#define NbrFmtRecords 1024
#define EndOF 100
#define MaxDataBuff 2500
#define MaxColSize 3996
#define MaxStr 132
#define TRUE 1
#define FALSE 0
/* Begin Host Variable Declarations */
EXEC SQL BEGIN DECLARE SECTION;
/* DynamicCommand is a String that will hold the dynamic command. */
char DynamicCommand[1023]; 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. */
sqlformat_type sqlfmts[NbrFmtRecords];
/* Nibbles and BCDType are data types needed for decimal type */
int Nibbles;
char BCDType[20];
/* DataBuffer is the buffer containing retrieved data as a result */
/* of a dynamic SELECT. */
char DataBuffer[MaxDataBuff];
boolean Abort;
struct SQLVarChar {
int Length;
char VarCharCol[MaxColSize];
};
main() /* Beginning of Program */
{
printf("\nC program illustrating dynamic command processing -- cex10a");
printf("\n");
printf("\nEvent List:");
printf("\n CONNECT TO PartsDBE");
printf("\n Prompt for any SQL command");
printf("\n BEGIN WORK");
printf("\n PREPARE");
printf("\n DESCRIBE");
printf("\n If command is a non-query command, EXECUTE it");
printf("\n Otherwise execute the following:");
printf("\n DECLARE CURSOR");
printf("\n OPEN Cursor");
printf("\n FETCH a row");
printf("\n CLOSE Cursor");
printf("\n COMMIT WORK");
printf("\n Repeat the above ten steps");
printf("\n RELEASE PartsDBE\n");
if (ConnectDBE()) { 4
Describe(); 23
ReleaseDBE();
printf("\n");
}
else
printf("\nError: Cannot Connect to PartsDBE");
printf("\n");
} /* End of Main Program */
/* Function BCDToString converts a binary field in the "DataBuffer" */
/* buffer to its ACSII representation. Input parameters are */
/* the Length, Precision and Scale. The input decimal field is passed */
/* via "DataBuffer" and the output String is passed via "result". */
int BCDToString(DataBuffer, Length, Precision, Scale, Result0) 2
char DataBuffer[];
short Length, Precision, Scale;
char Result0[];
{
#define hexd '0123456789ABCDEF'
#define ASCIIZero '0'
#define PlusSign 12
#define MinusSign 13
#define UnSigned 14
#define btod(d,i) ((i&1)?((d[i/2])&0xf):((d[i/2]>>4)&0xf))
int i;
int DecimalPlace;
int PutPos=0;
int DataEnd;
int DataStart;
boolean done;
char space[MaxStr];
char *Result;
Result = space;
DataEnd = (Length*2) - 2;
DataStart = (DataEnd - Precision);
for (i = 0; i < MaxStr; i++) Result[i] = '\0';
DecimalPlace = (Precision-Scale);
/* convert decimal to character String */
if (DecimalPlace == 0) Result[PutPos++] = '.';
/* convert each Nibble into a character */
for (i = DataStart; i <= DataEnd; i++) {
Result[PutPos] = ASCIIZero + btod(DataBuffer,i);
if (PutPos == DecimalPlace) Result[++PutPos] = '.';
PutPos++;
}
i = 0;
done = FALSE;
while (i<strlen(Result) & Result[i]=='0') ++Result;
if (Result[0] == '\0')
Result[0] = '0';
else {
/* place a zero at the left of the decimal point */
if (Result[0] == '.') StrInsert('0', Result);
/* insert sign */
switch (btod(DataBuffer,DataEnd + 1)) {
case PlusSign: StrInsert(' ', Result);
break;
case MinusSign: StrInsert('-', Result);
break;
default: break;
} /* End switch */
} /* End else */
strcpy(Result0, Result);
} /* End BCDToString */
int getline(linebuff) /*Function to get a line of characters */
char linebuff[80];
{
while (strlen(gets(linebuff)) ==0);
} /* End of function to get a line of characters */
int SQLStatusCheck() /* Function to Display Error Messages */ 3
{
Abort = FALSE;
if (sqlca.sqlcode < DeadLock) Abort = TRUE;
do {
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("\n");
printf("%s\n",SQLMessage);
} while (sqlca.sqlcode != 0);
if (Abort) {
EXEC SQL COMMIT WORK RELEASE;
DynamicCommand[0] = '/';
DynamicCommand[1] = '\0';
}
} /* End SQLStatusCheck Function */
int ConnectDBE() /* Function to Connect to PartsDBE */
{ 4
boolean Connect;
printf("\nConnect to PartsDBE");
EXEC SQL CONNECT TO 'PartsDBE'; 37
Connect = TRUE;
if (sqlca.sqlcode != OK) {
Connect = FALSE;
SQLStatusCheck();
} /* End if */
return(Connect);
} /* End of ConnectDBE Function */
int ReleaseDBE() /* Function to Release PartsDBE */ 5
{
printf("\nRelease PartsDBE");
EXEC SQL RELEASE;
if (sqlca.sqlcode != OK) SQLStatusCheck();
} /* End ReleaseDBE Function */
boolean BeginTransaction() /* Function to Begin Work */ 6
{
boolean BeginTransaction;
printf("\n");
printf("\nBegin Work");
EXEC SQL BEGIN WORK;
if (sqlca.sqlcode != OK) {
BeginTransaction = FALSE;
SQLStatusCheck();
ReleaseDBE(); 5
}
else
BeginTransaction = TRUE;
return(BeginTransaction);
} /* End BeginTransaction Function */
int EndTransaction() /* Function to Commit Work */ 7
{
printf("\n");
printf("\nCommit Work");
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != OK) SQLStatusCheck();
} /* End EndTransaction Function */
/* Function DisplaySelect deblocks the result of the dynamic */
/* SELECT in "DataBuffer". */
int DisplaySelect() 36
{
typedef union gt { 9
char CharData[MaxColSize];
char VarCharData[MaxColSize];
int IntegerData;
short SmallIntData;
double FloatData;
float DecimalData;
} GenericType;
short CurrentOffset;
short NullIndOffset;
GenericType OneColumn;
char DecString[20];
boolean IsNull;
short n,i,j,x; /* local loop counters */
CurrentOffset = 0; 10
for (x = 0; x < sqlda.sqld; x++) { /* display column names */
printf("%s | ",sqlfmts[x].sqlname); 11
}
printf("\n");
for (n = 0; n < sqlda.sqlrrow; n++) { /* for each FETCHed row */
12
for (i = 0; i < sqlda.sqld; i++) { /*for each column in a FETCHed row*/
13
/* 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 (sqlfmts[i].sqlindlen > 0) { 14
NullIndOffset = CurrentOffset + sqlfmts[i].sqlnof; 15
if ((DataBuffer[NullIndOffset] == '\0') & 16
(DataBuffer[NullIndOffset+1] == '\0')) {
IsNull = FALSE;
}
else
IsNull = TRUE;
} /* End if sqlfmts[i].sqlindlen > 0 .. */
if (IsNull) {
printf(" Column is NULL |"); 17
}
else {
/* Now bring down the actual value of this column. */
StrMove(sqlfmts[i].sqlvallen,DataBuffer, 18
CurrentOffset + sqlfmts[i].sqlvof, OneColumn.CharData, 0);
30
switch (sqlfmts[i].sqltype) { 19
case 0: /* Integer number */
switch (sqlfmts[i].sqlvallen) {
case 2: printf("%d | ",OneColumn.SmallIntData);
break;
case 4: printf("%d | ",OneColumn.IntegerData);
break;
} /* End switch statement */
break;
case 2: /* fixed-length character */
case 8: /* fixed-length native character */
for (j = 0; j < sqlfmts[i].sqlvallen; j++)
printf("%c",OneColumn.CharData[j]);
printf(" | ");
break;
case 3: /* variable-length char */
case 9: /* variable-length native char */
for (j = 4; j < sqlfmts[i].sqlvallen; j++)
printf("%s | ",OneColumn.VarCharData[j]);
printf(" | ");
break;
case 4: /* floating point */
printf("%f | ",OneColumn.FloatData);
break;
case 5: /* Packed decimal */ 2
BCDToString(OneColumn.CharData, sqlfmts[i].sqlvallen,
sqlfmts[i].sqlprec, sqlfmts[i].sqlscale, DecString);
printf("%s | ",DecString);
break;
default: printf("SQLType = %1s\n",sqlfmts[i].sqltype);
break;
} /* End switch statement */
} /* End if IsNull else */
} /* End for i/with sqlfmts[i] ... */
CurrentOffset = CurrentOffset + sqlda.sqlrowlen; 20
printf("\n");
} /* End for n = ... */
printf("\n");
} /* End of DisplaySelect function */
int GetCommand() 25
{
char DynamicClause[80];
short i;
printf("\n");
printf("\nYou may enter any SQL command or a '/' to STOP the program.");
printf("\nThe command can be continued on the next line. The command");
printf("\nmust be terminated with a semicolon.");
printf("\n");
printf("\nEnter your SQL command or clause ");
printf("\n");
DynamicCommand[0] = '\0'; /* @001 */
do {
printf("\n >");
getline(DynamicClause);
if (DynamicClause[0] != '/') {
strcat(DynamicCommand," ");
strcat(DynamicCommand,DynamicClause);
i = 0;
while (DynamicClause[i] != '\0'& DynamicClause[i++] != ';');
if (DynamicClause[i-1] == ';') {
DynamicClause[0] = '/'; /* @001 */
DynamicClause[1] = '\0'; /* @001 */
}
}
else {
DynamicCommand[0] = '/';
DynamicCommand[1] = '\0'; /* @001 */
}
} while (DynamicClause[0] != '/'); /* End do */ /* @001 */
} /* End of GetCommand function */
int Describe() /* Describe Function */ 23
{
/* set up SQLDA fields */ 24
sqlda.sqln = NbrFmtRecords; /* number of columns expected */
sqlda.sqlfmtarr = sqlfmts;
do {
GetCommand(); 25
if (DynamicCommand[0] != '/') { /* @001 */
if (BeginTransaction()) { 6
printf("\nPrepare");
printf("%s\n",DynamicCommand);
EXEC SQL PREPARE CMD1 FROM :DynamicCommand; 26
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
EndTransaction();
}
else {
printf("\nDescribe");
EXEC SQL DESCRIBE CMD1 INTO SQLDA; 27
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
EndTransaction();
}
else {
if (sqlda.sqld == 0) NonQuery(); 28
else
Query(); 29
} /* End if sqlca.sqlcode != OK after DESCRIBE */
} /* End if sqlca.sqlcode != OK after PREPARE */
} /* End if BeginTransaction */
else { /* BeginTransaction failed; */
31
DynamicCommand[0] = '/'; /* force logical to */ /* @001 */
DynamicCommand[1] = '\0'; /* Describe function */ /* @001 */
}
} /* End if DynamicCommand */
} while (DynamicCommand[0] != '/'); /* End do */ /* @001 */
} /* End of Describe function */
int NonQuery() 29
{
printf("\nA Non Query SQL command was entered.");
printf("\nExecute");
EXEC SQL EXECUTE CMD1;
if (sqlca.sqlcode != OK) {
SQLStatusCheck();
EXEC SQL ROLLBACK WORK;
}
else {
printf("\nThe Non-Query Command Executed Successfully.");
EndTransaction();
}
} /* End of NonQuery function */
int Query() 28
{
short RowLength;
short i;
printf("\nA Query SQL command was entered.");
printf("\n");
printf("\nNumber of columns: %2d",sqlda.sqld); 31
printf("\n");
EXEC SQL DECLARE CURSOR1 CURSOR FOR CMD1; 32
EXEC SQL OPEN CURSOR1;
if (sqlca.sqlcode != OK) SQLStatusCheck();
else {
sqlda.sqlbuflen = sizeof(DataBuffer); 33
sqlda.sqlnrow = ((sqlda.sqlbuflen) / (sqlda.sqlrowlen)); 34
sqlda.sqlrowbuf = DataBuffer;
while (sqlca.sqlcode == 0) {
EXEC SQL FETCH CURSOR1 USING DESCRIPTOR SQLDA; 35
if (sqlca.sqlcode != OK) {
if (sqlca.sqlcode == EndOF) {
printf("\nRow not found or no more rows!");
}
else
SQLStatusCheck();
}
else
DisplaySelect(); 36
} /* End of while sqlca.sqlcode = 0 */
EXEC SQL CLOSE CURSOR1;
if (sqlca.sqlcode != OK) SQLStatusCheck();
} /* End of if OPEN CURSOR is OK */
EndTransaction(); 7
} /* End of Query function */
int StrMove(n,s1,p1,s2,p2) 30
int n, p1, p2;
char s1[], s2[];
{
int i = 1;
while (i++ <= n)
s2[p2++] = s1[p1++];
} /* StrMove */
int StrInsert(c, string)
char c;
char *string;
{
char *temp;
temp = malloc(MaxStr);
strcpy(temp, string);
*string++ = c;
while ((*string++ = *temp++) != '\0');
} /* StrInsert */
|
cex10b: Program Using Dynamic Commands of Known Format | |
In some applications, you may know the format of a
query result in advance, but may still 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 a 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 in which it has been returned. Program cex10b, whose flow chart is shown in Figure 8-10, whose
execution is illustrated in Figure 8-11, and
whose source code appears in Figure 8-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 the tables in the
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 schema 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 9 in
host variable CmdLine. 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 :
sprintf(CmdLine,"SELECT OWNER, NAME, DBEFILESET, RTYPE FROM SYSTEM.TABLE\
WHERE TYPE = 0 AND OWNER = '%s';",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 enters ALL
in response to the owner name prompt. In this case, the program
uses the following form of the dynamic query 13 :
sprintf(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, sqlda.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 several sqlda fields 19 as follows: The sqlda.sqlnrow field is set to 300, as 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.
The sqlda.sqlbuflen field is set to the size of the data buffer.
In this program, the data buffer for the first query is a structure 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 their
corresponding SYSTEM.TABLE columns.
Note also that each element in the array is declared as the same size as its
corresponding column in the system table and not one character larger.
It is up to the program to insert the ASCII 0 null character in
the correct location to indicate the end of a character string.
The sqlda.sqlrowbuf field is set to the address of the
data buffer.
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[2]
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 StrCpy function 24 to move CHAR
values to char array
variables so that other C string functions 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:
sprintf(CmdLine,"SELECT COLNAME, LENGTH, TYPECODE, NULLS, PRECISION,\
SCALE FROM SYSTEM.COLUMN WHERE OWNER = '%s' AND TABLENAME = \
'%s';",OwnerName, TableName);
|
These names are obtained from the Owner and Table values in
the TableList array 4 . 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 the necessary sqlda
values 31 : The sqlda.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 sqlda.sqlbuflen field is set to the size of the data buffer.
The data buffer for the second query is a structure array
of records named
ColumnList 5 .
The sqlda.sqlrowbuf field is set to the address of the
data buffer.
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 because 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 34 :
CREATE LockMode TABLE OwnerName.TableName,
(ColumnList[1].ColName TypeInfo NullInfo,
ColumnList[2].ColName TypeInfo NullInfo,
.
.
. ColumnList[j].ColName TypeInfo NullInfo) IN TableList[i].FileSet;
|
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 switch statement 33 based
on the value ALLBASE/SQL put in TableList[i].LockMode. The
SYSTEM.TABLE view stores the automatic locking mode for tables as
an integer from 1 through 3. The switch statement equates these
codes with the expressions that must appear in the CREATE TABLE
command.
TypeCode is generated in a switch statement 35 based on the
value ALLBASE/SQL put in ColumnList[i].TypeCode. The SYSTEM.COLUMN
view stores the data type of each column as an integer from 0 through
5. The switch statement equates these codes with the expressions that
must appear in the CREATE TABLE command.
Nulls is generated from the null indicator ALLBASE/SQL returned
to ColumnList[i].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 with a COMMIT WORK RELEASE
command 38 . Figure 8-10 Flow Chart of Program cex10b
![[Flow Chart of Program cex10b]](img/xbf0810a.gif)
![[Flow Chart of Program cex10b]](img/xbf0810b.gif)
Figure 8-11 Run Time Dialog of Program cex10b
C program illustrating dynamic command processing -- cex10b
ALLBASE/SQL/MPE XL SCHEMA Generator for Tables
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,
.
.
.
|
Figure 8-12 Program cex10b: Dynamic Queries of Known Format
/* Program cex10b */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/* 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. */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
#include <stdio.h>
#include <ctype.h>
#define OK 0
#define MaxNbrTables 300 1
#define MaxNbrColumns 255 2
#define mode 0700
#define NbrFmtRecords 32
sqlca_type sqlca; /* SQL Communication Area */
sqlda_type sqlda; /* SQL Describe Area */
sqlformat_type sqlfmts[NbrFmtRecords]; /* declaration of format nodes */
char FileName[15];
char OwnerName[21];
char TableName[21];
char DBEFileSet[21];
char ColumnName[21];
char DBEName[128];
char OneLine[81];
int FileNum;
short i;
short j;
short NumOfTables;
struct {
char Owner[20];
char Table[20];
char FileSet[20];
short LockMode;
} TableList[MaxNbrTables]; 4
struct {
char ColName[20];
int Length;
short TypeCode;
short Nulls;
short Precision;
short Scale;
} ColumnList[MaxNbrColumns]; 5
/* Begin Host Variable Declarations */
EXEC SQL BEGIN DECLARE SECTION;
char CmdLine[200];
char SQLMessage[133];
EXEC SQL END DECLARE SECTION;
/* End Host Variable Declarations */
main() /* Beginning of Program */
{
printf("\n C program illustrating dynamic command processing -- cex10b");
printf("\n");
printf("\n ALLBASE/SQL/MPE XL SCHEMA Generator for Tables");
printf("\n");
printf("\n Event List:");
printf("\n Prompt for the name of the schema file to create");
printf("\n Prompt for the name of the DBEnvironmet");
printf("\n Prompt for the owner name");
printf("\n Generate schema file");
printf("\n");
printf("\n Enter name of schema file to be generated > "); 6
scanf("%s",FileName);
FileNum = unlink(FileName); 7
FileNum = creat(FileName,mode);
printf("\n Enter name of DBEnvironment > "); 8
scanf("%s",DBEName);
sprintf(CmdLine,"CONNECT TO '%s';",DBEName); 9
EXEC SQL EXECUTE IMMEDIATE :CmdLine; 10
if (sqlca.sqlcode != OK) {
printf("\n Could not CONNECT to DBEnvironment!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
printf("\n Enter database owner name or ALL for all owners > "); 11
scanf("%s",OwnerName);
for (i = 0; i <= strlen(OwnerName); i++) { /* Upshift OwnerName */
if (islower(OwnerName[i])) {
OwnerName[i] = toupper(OwnerName[i]); 12
}
}
response = "ALL";
if (strcmp(response,OwnerName) == 0) {
sprintf(CmdLine,"SELECT OWNER,NAME,DBEFILESET,RTYPE FROM SYSTEM.TABLE\
WHERE TYPE = 0 AND OWNER <> 'SYSTEM';"); 13
}
else {
sprintf(CmdLine,"SELECT OWNER,NAME,DBEFILESET,RTYPE FROM SYSTEM.TABLE\
WHERE TYPE = 0 AND OWNER = '%s';",OwnerName); 14
}
EXEC SQL PREPARE SelectCmd1 FROM :CmdLine; 15
if (sqlca.sqlcode != OK) {
printf("\n Problem PREPARING the SELECT #1 command!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
/* set up SQLDA fields */
sqlda.sqlfmtarr = sqlfmts; /* pointer to format nodes */ 16
sqlda.sqln = 4; /* number of columns expected */
EXEC SQL DESCRIBE SelectCmd1 INTO SQLDA; 17
if (sqlca.sqlcode != OK) {
printf("\n Problem describing SelectCmd1!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
EXEC SQL DECLARE TableList CURSOR for SelectCmd1; 18
if (sqlca.sqlcode != OK) {
printf("\n Problem declaring TableList cursor!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
EXEC SQL OPEN TableList; 18
if (sqlca.sqlcode != OK) {
printf("\n Problem opening TableList cursor!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
/* set up SQLDA fields */
sqlda.sqlnrow = MaxNbrTables; 19
sqlda.sqlbuflen = sizeof(TableList);
sqlda.sqlrowbuf = TableList;
/* Get Table List from SYSTEM.TABLE */
EXEC SQL FETCH TableList USING DESCRIPTOR SQLDA; 20
if (sqlca.sqlcode == 100) {
printf("\n No tables qualified!");
goto a9999;
}
else {
if (sqlca.sqlcode != OK) {
printf("\n Problem encountered when reading SYSTEM.TABLE!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
}
NumOfTables = sqlca.sqlerrd[2]; 21
EXEC SQL COMMIT WORK; 22
/* do loop for i */
for (i = 0; i < NumOfTables; i ++) { 23
TableList[i].Owner[19] = '\0';
TableList[i].Table[19] = '\0';
TableList[i].FileSet[19] = '\0';
sscanf(TableList[i].Owner,"%s",OwnerName); 24
sscanf(TableList[i].Table,"%s",TableName);
sscanf(TableList[i].FileSet,"%s",DBEFileSet);
printf("\n Generating SQL command to CREATE TABLE ");
printf("%s.%s",OwnerName,TableName);
sprintf(CmdLine,"SELECT COLNAME,LENGTH,TYPECODE,NULLS,PRECISION,\
SCALE FROM SYSTEM.COLUMN WHERE OWNER = '%s' AND TABLENAME =\
'%s';",OwnerName,TableName); 26
EXEC SQL PREPARE SelectCmd2 FROM :CmdLine; 27
if (sqlca.sqlcode != OK) {
printf("\n Problem PREPARING the SELECT #2 command!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
/* set up SQLDA fields */
sqlda.sqlfmtarr = sqlfmts; /* pointer to format nodes */ 28
sqlda.sqln = 6; /* number of columns expected */
EXEC SQL DESCRIBE SelectCmd2 INTO SQLDA; 29
if (sqlca.sqlcode != OK) {
printf("\n Problem describing SelectCmd2!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
EXEC SQL DECLARE ColumnList CURSOR for SelectCmd2; 30
if (sqlca.sqlcode != OK) {
printf("\n Problem declaring ColumnList Cursor!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
EXEC SQL OPEN ColumnList; 30
if (sqlca.sqlcode != OK) {
printf("\n Problem opening ColumnList cursor!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
/* set up SQLDA fields */
sqlda.sqlnrow = MaxNbrColumns; 31
sqlda.sqlbuflen = sizeof(ColumnList);
sqlda.sqlrowbuf = ColumnList;
/* Get Column List from SYSTEM.COLUMN */
EXEC SQL FETCH ColumnList USING DESCRIPTOR SQLDA; 32
if (sqlca.sqlcode != OK) {
printf("\n Problem encountered when reading SYSTEM.COLUMN!");
EXEC SQL SQLEXPLAIN :SQLMessage;
printf("%s\n",SQLMessage);
goto a9999;
}
switch (TableList[i].LockMode) { 33
case 1: sprintf(OneLine,"\nCREATE PUBLICREAD ");
break;
case 2: sprintf(OneLine,"\nCREATE PRIVATE ");
break;
case 3: sprintf(OneLine,"\nCREATE PUBLIC ");
break;
default: sprintf(OneLine,"\nUnrecognized Lock Mode ");
break;
} /* end switch */
sprintf(OneLine+strlen(OneLine),"TABLE %s.%s ",OwnerName,TableName);
write(FileNum,OneLine,strlen(OneLine)); 34
for (j = 0; j < sqlca.sqlerrd[2]; j++) {
ColumnList[j].ColName[19] = '\0';
strcpy(ColumnName,ColumnList[j].ColName);
if (j==0) sprintf(OneLine,"\n (%s ",ColumnName);
else sprintf(OneLine,"\n (%s ",ColumnName);
switch (ColumnList[j].TypeCode) { 35
case 0: if (ColumnList[j].Length == 4) {
sprintf(OneLine+strlen(OneLine),"INTEGER ");
}
else
sprintf(OneLine+strlen(OneLine),"SMALLINT ");
break;
case 2:
case 8: sprintf(OneLine+strlen(OneLine),"CHAR( %2d )",\
ColumnList[j].Length);
break;
case 3:
case 9: sprintf(OneLine+strlen(OneLine),"VARCHAR( %2d )",\
ColumnList[j].Length);
break;
case 4: sprintf(OneLine+strlen(OneLine),"FLOAT ");
break;
case 5: sprintf(OneLine+strlen(OneLine),"DECIMAL( %2d,%2d )",\
ColumList[j].Precision,ColumnList[j].Scale);
break;
default: sprintf(OneLine+strlen(OneLine)," **** ");
break;
} /* end switch */
if (ColumnList[j].Nulls == 0) { 36
sprintf(OneLine+strlen(OneLine)," NOT NULL");
}
if (j != sqlca.sqlerrd[2]-1) {
sprintf(OneLine+strlen(OneLine),",");
}
else {
sprintf(OneLine+strlen(OneLine),") IN %s;\n",TableList[i].FileSet
}
write(FileNum,OneLine,strlen(OneLine));
} /* for j = 1 to sqlca.sqlerrd[2] */
EXEC SQL COMMIT WORK; 37
} /* for i = 1 to NumOfTables */
a9999:
EXEC SQL COMMIT WORK RELEASE; 38
printf("\n");
}
|
|
