HP 3000 Manuals HP 3000 Manuals
Sample Programs Using Dynamic Query Operations
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.
![[]](../../pic3k/M011754/FFN1063c50.gif)
Figure 8-7. Flow Chart of Program cex10a
![[]](../../pic3k/M011754/FFN1073c50.gif)
Figure 8-7. Flow Chart of Program cex10a (page 2 of 2)
____________________________________________________________________________
| |
| 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. |
____________________________________________________________________________
Figure 8-8. Run Time Dialog of Program cex10a
________________________________________________________________________
| |
| 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-8. Run Time Dialog of Program cex10a (page 2 of 2)
_____________________________________________________________________________
| |
| /* 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]; |
| |
| |
| |
_____________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format
________________________________________________________________________________
| |
| /* 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". */ |
| |
________________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 2 of 11)
_____________________________________________________________________________
| |
| 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); |
| |
_____________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 3 of 11)
_____________________________________________________________________________
| |
| /* 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 |
| |
| |
| |
_____________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 4 of 11)
_____________________________________________________________________________
| |
| 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 */ |
| |
| |
| |
_____________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 5 of 11)
_________________________________________________________________________________
| |
| /* 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; |
_________________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 6 of 11)
_______________________________________________________________________________
| |
| } /* 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 */ |
| |
| |
| |
_______________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 7 of 11)
_______________________________________________________________________________
| |
| } /* 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 */ |
| |
| |
_______________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 8 of 11)
________________________________________________________________________________
| |
| 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 */ |
| } |
| |
| |
| |
| |
________________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 9 of 11)
_____________________________________________________________________________
| |
| } /* 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) { |
_____________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 10 of 11)
_____________________________________________________________________________
| |
| 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 */ |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________________
Figure 8-9. Program cex10a: Dynamic Queries of Unknown Format (page 11 of 11)
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 .
![[]](../../pic3k/M011754/FFN1213c50.gif)
Figure 8-10. Flow Chart of Program cex10b
![[]](../../pic3k/M011754/FFN1223c50.gif)
Flow Chart of Program cex10b (page 2 of 2)
__________________________________________________________________
| |
| 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-11. Run Time Dialog of Program cex10b
________________________________________________________________________________
| |
| /* 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 |
| |
| |
| |
| |
| |
| |
| |
________________________________________________________________________________
Figure 8-12. Program cex10b: Dynamic Queries of Known Format
________________________________________________________________________________
| |
| 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; |
| } |
________________________________________________________________________________
Figure 8-12. Program cex10b: Dynamic Queries of Known Format (page 2 of 7)
______________________________________________________________________________
| |
| 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; |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
______________________________________________________________________________
Figure 8-12. Program cex10b: Dynamic Queries of Known Format (page 3 of 7)
_____________________________________________________________________________
| |
| 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'; |
| |
| |
_____________________________________________________________________________
Figure 8-12. Program cex10b: Dynamic Queries of Known Format (page 4 of 7)
_____________________________________________________________________________
| |
| 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; |
| } |
| |
_____________________________________________________________________________
Figure 8-12. Program cex10b: Dynamic Queries of Known Format (page 5 of 7)
________________________________________________________________________________
| |
| /* 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; |
________________________________________________________________________________
Figure 8-12. Program cex10b: Dynamic Queries of Known Format (page 6 of 7)
________________________________________________________________________________
| |
| 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"); |
| } |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
________________________________________________________________________________
Figure 8-12. Program cex10b: Dynamic Queries of Known Format (page 7 of 7)