900 Series HP 3000 Computer Systems ALLBASE/SQL C Application Programming Guide ABCDE HP Part No. 36216-90023 Printed in U.S.A.
The information contained in this document is subject to change without notice. Hewlett-Packard makes no warranty of any kind with regard to this material, including, but not limited to, the implied warranties of merchantability or tness for a particular purpose. Hewlett-Packard shall not be liable for errors contained herein or for direct, indirect, special, incidental or consequential damages in connection with the furnishing or use of this material.
Printing History The following table lists the printings of this document, together with the respective release dates for each edition. The software version indicates the version of the software product at the time this document was issued. Many product releases do not require changes to the document. Therefore, do not expect a one-to-one correspondence between product releases and document editions.
iv
Preface ALLBASE/SQL is a relational database management system for use on the HP 3000 Series 900 computer. ALLBASE/SQL (Structured Query Language) is the language you use to de ne and maintain data in an ALLBASE/SQL DBEnvironment. This manual presents the techniques of embedding ALLBASE/SQL within C language source code. This manual is intended as a learning tool and a reference guide for C programmers.
Conventions UPPERCASE In a syntax statement, commands and keywords are shown in uppercase characters. The characters must be entered in the order shown; however, you can enter the characters in either uppercase or lowercase. For example: COMMAND can be entered as any of the following: command Command COMMAND It cannot, however, be entered as: comm italics com_mand In a syntax statement or an example, a word in italics represents a parameter or argument that you must replace with the actual value.
Conventions (continued) [ ... ] In a syntax statement, horizontal ellipses enclosed in brackets indicate that you can repeatedly select the element(s) that appear within the immediately preceding pair of brackets or braces. In the example below, you can select parameter zero or more times. Each instance of parameter must be preceded by a comma: [,parameter][...
Contents 1. Getting Started with ALLBASE/SQL Programming in C Understanding ALLBASE/SQL Operations . . . . . . Using DML to Manipulate Data . . . . . . . . . . Using DDL to De ne Database Objects . . . . . . . Using DCL to Manage Security . . . . . . . . . . . Handling Transactions . . . . . . . . . . . . . . . Handling Errors . . . . . . . . . . . . . . . . . Dynamic and Non-Dynamic Operations . . . . . . . Understanding the Program Life Cycle . . . . . . . . Developing ALLBASE/SQL Applications . . . . .
Native Language Support . . . . . . . . . . . . . . . . . . . . . . Looking at an Embedded SQL Source Program . . . . . . . . . . . . . . 1-23 1-24 2. Using the Preprocessor Invoking the C Preprocessor . . . . . . . . Full Preprocessing Mode . . . . . . . . . Preprocessor Syntax I . . . . . . . . . . Parameters . . . . . . . . . . . . . . . Description . . . . . . . . . . . . . . . Authorization . . . . . . . . . . . . . Example . . . . . . . . . . . . . . . . Syntax Checking Mode . . . . . . . . .
INTEGER Data . . . . . . . . . . . . . . . FLOAT Data . . . . . . . . . . . . . . . . . Floating Point Data Compatibility . . . . . . BINARY Data . . . . . . . . . . . . . . . . Binary Data Compatibility . . . . . . . . . . Using the LONG Phrase with Binary Data Types Declaring Host Variables for BINARY Data . . Declaring Host Variables for VARBINARY Data Inserting and Updating VARBINARY Data . . Selecting and Fetching VARBINARY Data . . . DECIMAL Data . . . . . . . . . . . . . . .
Determining When More Than One Row Quali Detecting Log Full Condition . . . . . . . . Handling Out of Space Conditions . . . . . Checking for Authorizations . . . . . . . . 5. Simple Data Manipulation SQL Commands . . . . . . . . . . . . . . SELECT . . . . . . . . . . . . . . . . . INSERT . . . . . . . . . . . . . . . . . UPDATE . . . . . . . . . . . . . . . . DELETE . . . . . . . . . . . . . . . . Transaction Management for Simple Operations Sample Program Using Simple DML Commands . . . . . . . es . . .
8. Using Dynamic Operations Review of Preprocessing Events . . . . . . . . . . . . . . . Di erences between Dynamic and Non-Dynamic Preprocessing . Permanently Stored vs. Temporary Sections . . . . . . . . Examples of Non-Dynamic and Dynamic SQL Statements . . Why Use Dynamic Preprocessing? . . . . . . . . . . . . Passing Dynamic Commands to ALLBASE/SQL . . . . . . . Understanding the Types of Dynamic Operations . . . . . . . Preprocessing of Dynamic Non-Queries . . . . . . . . . . .
Changing a LONG Column with an UPDATE [WHERE CURRENT] Command Removing LONG Column Data with a DELETE [WHERE CURRENT] Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . Coding Considerations . . . . . . . . . . . . . . . . . . . . . . . . . File versus Random Heap Space . . . . . . . . . . . . . . . . . . . File Naming Conventions . . . . . . . . . . . . . . . . . . . . . . Considering Multiple Users . . . . . . . . . . . . . . . . . . . . . . Deciding How Much Space to Allocate and Where . . .
Figures 1-1. 1-2. 1-3. 1-4. 1-5. 1-6. 1-7. 2-1. 2-2. 2-3. 2-4. 2-5. 2-6. 2-7. 2-8. 2-9. 2-10. 2-11. 2-12. 3-1. 3-2. 3-3. 3-4. 3-5. 3-6. 3-7. 4-1. 4-2. 4-3. 5-1. 5-2. 5-3. 6-1. 6-2. 6-3. 6-4. 6-5. 6-6. 6-7. 7-1. 7-2. 7-3. Creating an ALLBASE/SQL C Application Program . . . . . . Skeleton ALLBASE/SQL C Program . . . . . . . . . . . . . Components of a Stored Section . . . . . . . . . . . . . . . Ways of Compiling and Linking an ALLBASE/SQL C Program . . Moving an Application to a Production System . . . . .
8-1. 8-2. 8-3. 8-4. 8-5. 8-6. 8-7. 8-8. 8-9. 8-10. 8-11. 8-12. 9-1. 10-1. 10-2. 11-1. 11-2. Creation and Use of a Program that has a Stored Module . . . . . . . Creation and Use of a Program that has no Stored Module . . . . . . . Procedure Hosting Dynamic Non-Query Commands . . . . . . . . . . Dynamic Query Data Structures and Data Assignment . . . . . . . . Format of the Data Bu er . . . . . . . . . . . . . . . . . . . . . Parsing the Data Bu er in cex10a . . . . . . . . . . . . . . . . . .
Tables 3-1. 3-2. 3-3. 3-4. 4-1. 8-1. 8-2. 9-1. 9-2. 10-1. 10-2. 11-1. 11-2. 11-3. 11-4. Data Type Declarations . . . . . . . . . . . . . . . . . . Program Element Declarations . . . . . . . . . . . . . . . ALLBASE/SQL Floating Point Column Speci cations . . . . . C Data Type Equivalency and Compatibility . . . . . . . . . sqlca Status Checking Fields . . . . . . . . . . . . . . . . SQLDA Fields . . . . . . . . . . . . . . . . . . . . . . Fields in a Format Array Record . . . . . . . . . . . . . .
1 Getting Started with ALLBASE/SQL Programming in C ALLBASE/SQL is a relational database management system that uses SQL statements to access data within an ALLBASE/SQL DBEnvironment. Embedding SQL statements in your C program and preprocessing with the SQL preprocessor allows programmatic access to this data. Embedded statements are ALLBASE/SQL commands in the ow of what would otherwise be C source code.
There are several kinds of SQL operations you can perform from application programs: Accessing data using data manipulation language (DML). Creating ALLBASE/SQL objects using data de nition language (DDL). Managing security with data control language (DCL). Handling transactions. Handling errors. Performing dynamic processing. Using DML to Manipulate Data You use DML to examine or modify the rows in the tables in a database. The chief DML command for creating queries is SELECT.
Using DCL to Manage Security You use DCL to create a security scheme for your databases. Control statements are used to initiate access to a DBEnvironment and to provide security on speci c database objects. You may nd it useful to assign permissions for newly created objects when the default permissions are not su cient for the needs of your application. You can control the ownership of objects in ALLBASE/SQL DBEnvironments at creation time or through the TRANSFER OWNERSHIP command.
Dynamic and Non-Dynamic Operations Whether you are using DML, DDL, or DCL operations, you can structure embedded SQL statements as either dynamic or non-dynamic. Commands are non-dynamic when the syntax of the entire command is known and preprocessed prior to run time. For many non-dynamic commands, ALLBASE/SQL can speed up database access by storing runtime instructions in the DBEnvironment at preprocessing time. The following is a non-dynamic query.
Understanding the Program Life Cycle Each ALLBASE/SQL application program undergoes a number of phases in its useful life: Development: In the development phase, you create C source code procedures and integrate them into applications. Database access is provided through embedded SQL statements which are translated by a preprocessor into C procedures. After preprocessing the source, you compile, test, and debug, until the application is satisfactory. Then you install the application on a production system.
Figure 1-1. Creating an ALLBASE/SQL C Application Program Creating Source Files Using an editor, you create one or more les containing C source code and SQL commands, which are said to be embedded in the program. You can use multiple source les, but each le containing embedded SQL statements must be separately preprocessed.
Using Embedded SQL The following SQL statements can be embedded in your program: Host variable declarations. INCLUDE SQLCA statements to let ALLBASE/SQL pass return codes to your program. A CONNECT command specifying the name of a DBEnvironment. BEGIN WORK and COMMIT WORK commands to delimit transactions (Note that if a transaction is not already started, any SQL command will automatically issue an implicit BEGIN WORK.). SELECT, INSERT, UPDATE, or DELETE commands; or cursor operations.
Declaring Host Variables In addition to the normal variable declarations, the source le contains variable declarations for host variables. These can appear wherever declarations are legal. However, it is recommended that you include them at the beginning of the le, since they are translated into global variables by the preprocessor anyway. If more than one source le references the same set of variables, you must declare them separately in each le, and you must preprocess each le separately.
. /* STATIC VARIABLE DECLARATION PART */ sqlca Declaration sqlda Declaration . /* Host Variable Declarations */ /* Host Variables can be global, local, or both */ . /* main DECLARATION PART */ main(argc,argv) /* Parameter Declaration */ { . /* Host Variable Declarations */ /* Host Variables can be local, or for called routines */ /* C statements, some containing SQL Commands */ EXEC SQL ... int FunctionName(); . } /* FUNCTION DECLARATION PART */ FunctionName(parameter list) /* Parameter Declaration */ { .
Rules of Syntax for Embedded SQL Statements You must follow some simple rules when embedding SQL statements in C code: Commands must be of appropriate size: An embedded SQL command has no maximum length. A dynamic SQL command within a host variable is limited only by the size of the host variable's declaration. A dynamic SQL command not within a host variable can be no longer than 2048 bytes. Use EXEC SQL as the pre x to each SQL statement.
Preprocessing the Source File After embedding SQL commands in the source code, preprocess it with the ALLBASE/SQL C preprocessor . Use the following command, which is described fully in Chapter 2: :RUN PSQLC.PUB.SYS; INFO="DBEnvironmentName (MODULE (ModuleName))" In addition to checking the syntax of your SQL statements, preprocessing also does the following: Creates a modi ed source le. Stores sections in the DBEnvironment. Generates a le, SQLMSG , for preprocessing messages.
Figure 1-3. Components of a Stored Section Stored Form of the SQL Command. In addition to translating each SQL statement into C code, the preprocessor stores a version of the statement in a section. This version of the statement is used at a later time if it becomes necessary to revalidate the section. ALLBASE/SQL also chooses the best available path for accessing the data referred to in the statement. This process is called optimization.
Changes in authorities of the module's owner. Alterations to tables accessed by the program. Deletions or creations of indexes. Updating a table's statistics. In general, ALLBASE/SQL invalidates a section whenever there is a chance that the existing access path to the data might have changed. Suppose you drop an index on a column that appears in a query. In such a case, the section which contains that query and any other sections which reference that column are marked invalid.
Figure 1-4. Ways of Compiling and Linking an ALLBASE/SQL C Program Caution 1-14 When correcting your programs during the compile process, be sure to edit the original source le , not the preprocessor output le. Note that when it encounters an error, the compiler returns line numbers for the modi ed source le , so you must extrapolate from these to the actual lines in your original source. Edit the original source, re-preprocess, then re-compile and re-link.
Running the Program Once the preprocessing and compile steps have completed without error, you can run the application. All the C constructs inserted by the preprocessor and the stored sections automatically handle database operations, including providing the application program with status information after each SQL command is executed. SQL commands that have a stored section are executed if the section is valid at run time or can be validated by ALLBASE/SQL at run time.
dynamic command (an SQL command entered by the user at run time) is executed only if the login of the user running the program has the authority to execute the entered command. Whoever runs the program must have either RUN authorization for the module or else be the OWNER or DBA. Granting authorizations is further described in Chapter 2 of this manual and in the ALLBASE/SQL Database Administration Guide .
Figure 1-5. Moving an Application to a Production System ISQL copies the module from the installable module le named SQLMOD into a DBEnvironment named SOMEDBE.SOMEGRP.SOMEACCT. During installation, ALLBASE/SQL marks each section in the module valid or invalid, depending on the current objects and authorities in SOMEDBE.SOMEGRP.SOMEACCT. To use the INSTALL command, you need to be able to start a DBE session in the DBEnvironment that will contain the new module.
Granting Module Owner Authorizations The original module owner is the DBEUserID of the person who preprocesses the program or, if speci ed, the DBEUserID used with the OWNER option in the preprocessor command line. At run time, embedded SQL commands are executed only if the original module owner has the authority to execute them. Therefore, you need to grant required authority to a user in the production DBEnvironment with the same name as the original module owner in the development environment.
isql=> CONNECT TO 'SOMEDBE.SOMEGRP.SOMEACCT'; isql=> GRANT CONNECT TO USER@ACCOUNT; isql=> COMMIT WORK; If you have module OWNER or DBA authority, you can grant RUN authority: isql=> CONNECT TO 'SOMEDBE.SOMEGRP.SOMEACCT'; isql=> GRANT RUN ON SOMEPROG TO USER@ACCOUNT; isql=> COMMIT WORK; Now USER.ACCOUNT can run program SOMEPROG: : HELLO USER.ACCOUNT . . : RUN SOMEPROG Refer to the ALLBASE/SQL Reference Manual for complete information on the GRANT command.
Managing Source Code You should carefully maintain the source code for each application program in case it should be necessary to re-preprocess and re-compile at a later time. In addition to C source, it is recommended that you keep copies of the ISQL command les used to create your DBEnvironments. This information is extremely useful when modifying existing code. Use SQLGEN to create schema les for storage once you have con gured the DBEnvironment on the production side.
Changing Program-Related Authorization Once a program is in production use, you may need to grant and revoke RUN and CONNECT authority as program users change. Revoking CONNECT authority requires DBA authorization: isql=> REVOKE CONNECT FROM OLDUSER@ACCOUNT; Revoking RUN authority requires either module OWNER or DBA authority: isql=> REVOKE RUN ON PGMR1@ACCOUNT.
Programming Under the MPE XL Operating System You must take certain characteristics of the MPE XL operating system into account as you code ALLBASE/SQL C applications. These include the following: Security Considerations. File Naming Conventions. Native Language Support. Security Considerations In order to preprocess and compile embedded SQL applications, you must have SF capability in the group where the preprocessing is done. You also need the ability to execute programs.
Native Language Support ALLBASE/SQL lets you manipulate databases in a wide variety of native languages in addition to the default language, known as NATIVE-3000. You can use either 8-bit or 16-bit character data, as appropriate for the language you select. In addition, you can always include ASCII data in any database, since ASCII is a subset of each supported character set. The collating sequence for sorting and comparisons is that of the native language selected.
Run the MPE XL utility program NLUTIL.PUB.SYS to determine which native languages are supported on your system.
Terminate the DBE session. 46 5 47 5 48 5 49 5 and You use the RELEASE command or the RELEASE option of the COMMIT WORK command to end a DBE session. De ne transactions. You de ne transactions in a program to control concurrency and consistency in your database access. The transaction is bounded by the BEGIN WORK and COMMIT WORK or ROLLBACK WORK commands.
Program to SELECT specified rows from the Parts Table - cex2 Event List: CONNECT to PartsDBE BEGIN WORK SELECT specified row from Parts Table until user enters a '/' COMMIT WORK RELEASE from PartsDBE Connect to PartsDBE Enter Part Number within Parts Table or '/' to STOP> 1243-P-01 Begin Work SELECT PartNumber, PartName, SalesPrice Row not found! Commit Work Enter Part Number within Parts Table or "/" to STOP> 1323-D-01 Begin Work SELECT PartNumber, PartName, SalesPrice Part Number: Part Name: Sales Price:
/* Program cex2 */ /* /* /* /* /* /* /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of SQL's SELECT command to retrieve one row or tuple of data at a time. BEGIN WORK is executed before the SELECT and a COMMIT WORK is executed after the SELECT. An indicator variable is also used for SalesPrice. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */ */ */ */ */ */ */ typedef int boolean; char response[2]; boolean Abort; #include
int SQLStatusCheck() /* Function to Display Error Messages */ { Abort = FALSE; if (sqlca.sqlcode < DeadLock) Abort = TRUE; do { EXEC SQL SQLEXPLAIN :SQLMessage; printf("\n"); printf("%s\n",SQLMessage); } while (sqlca.sqlcode != 0); 43 5 if (Abort) { EndTransaction(); ReleaseDBE(); } } /* End SQLStatusCheck Function */ boolean ConnectDBE() /* Function to Connect to PartsDBE { boolean ConnectDBE; */ ConnectDBE = TRUE; printf("\n connect to PartsDBE"); EXEC SQL CONNECT TO 'PartsDBE'); if (sqlca.
boolean BeginTransaction() { boolean BeginTransaction; BeginTransaction = TRUE; /* Function to Begin Work */ printf("\n"); printf("\n Begin Work"); EXEC SQL BEGIN WORK; 47 5 if (sqlca.sqlcode != OK) { BeginTransaction = FALSE; SQLStatusCheck(); ReleaseDBE(); } /* End if */ return (BeginTransaction); } /* End BeginTransaction Function */ int EndTransaction() /* Function to Commit Work */ { printf("\n"); printf("\n Commit Work"); EXEC SQL COMMIT WORK; if (sqlca.
int Select() { /* Function to Query Parts Table */ do { printf("\n"); printf("\n Enter Part Number within Parts Table or '/' to STOP > "); scanf("%s",PartNumber); if (PartNumber[0] != '/') { BeginTransaction(); printf("\n SELECT PartNumber, PartName, SalesPrice"); EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice :SalesPriceInd FROM PurchDB.Parts WHERE PartNumber = :PartNumber; if ((sqlca.sqlwarn[0] == 'W') || (sqlca.
main() { /* Beginning of program */ printf("\n Program to SELECT specified rows from"); printf("\n the Parts Table - cex2"); printf("\n"); printf("\n Event List:"); printf("\n CONNECT TO PartsDBE"); printf("\n BEGIN WORK"); printf("\n SELECT the specified row from the Parts Table"); printf("\n until the user enters a '/'"); printf("\n COMMIT WORK"); printf("\n RELEASE from PartsDBE"); printf("\n"); if (ConnectDBE()) { Select(); ReleaseDBE(); } else printf("\n Error: Cannot Connect to PartsDBE!\n"); } /* E
2 Using the Preprocessor This chapter shows how to use all the preprocessor's options, and it describes the inputs and outputs of the preprocessor command. Topics are: Invoking the C Preprocessor. Identifying Preprocessor Input. Identifying Preprocessor Output. Dealing with Preprocessor Errors. Looking at a Modi ed ALLBASE/SQL Source Program. Looking at Preprocessor Created INCLUDE Files.
Preprocessor Syntax I RUN PSQLC.PUB.SYS;INFO= "DBEnvironmentName [( |...|)]" 9 8 MODULE(ModuleName) > > > > > > > (OwnerName) > < OWNER 8 9= < DROP > > > > :: NODROP PRESERVE REVOKE = > > > ; ;> Parameters DBEnvironmentName ModuleName OwnerName DROP NODROP PRESERVE 2-2 Using the Preprocessor Identi es the DBEnvironment in which a module is to be stored. You may use a backreference to a le de ned in a le equation for this parameter. Assigns a name to the stored module.
REVOKE Is speci ed when the program being preprocessed already has a stored module and you want to revoke existing RUN authorities for that module. REVOKE cannot be speci ed unless DROP is also speci ed. Description 1. Before invoking the preprocessor in this mode when the program being preprocessed already has a stored module, ensure that the earlier version of the program is not being executed. 2.
Authorization To preprocess a program, you need DBA or CONNECT authority for the DBEnvironment speci ed in the preprocessor command line. You also need table and view authorities for the tables and views which the program will access at run time. DBEnvironment CONNECT authority can also be explicitly GRANTed. If you have DBECreator or DBA authority or module OWNER authority, you have CONNECT authority by default.
Example :FILE SQLIN=CEX2 :RUN PSQLC.PUB.SYS;INFO=& "PartsDBE (MODULE(CEX2) OWNER(OwnerP@SomeAcct) REVOKE DROP)" WED, OCT 25, 1991, 1:38 PM HP36216-02A.E1.02 C Preprocessor/3000 ALLBASE/SQL (C)COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991. ALL RIGHTS RESERVED. 0 ERRORS 1 WARNINGS END OF PREPROCESSING. END OF PROGRAM :EDITOR HP32501A.07.20 EDIT/3000 FRI, OCT 27, 1991, 10:17 AM (C)HEWLETT-PACKARD CO. 1990 /T SQLMSG;L ALL UNN FILE UNNUMBERED . . .
Syntax Checking Mode The following command only checks the syntax of the SQL commands embedded in the source code le. Preprocessor Syntax II RUN PSQLC.PUB.SYS;INFO="(SYNTAX)" Description 1. The preprocessor does not access a DBEnvironment when it is run in this mode. 2. When performing only syntax checking, the preprocessor does not convert the SQL commands into C statements. Therefore SQLOUT does not contain any preprocessor generated calls to ALLBASE/SQL external procedures. 3.
Example :FILE SQLIN=CEX2 :RUN PSQLC.PUB.SYS;INFO="(SYNTAX)" WED, OCT 25, 1991, 1:38 PM HP36216-02A.E1.02 C Preprocessor/3000 ALLBASE/SQL (C)COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991. ALL RIGHTS RESERVED. Syntax checked. 1 ERRORS 0 WARNINGS END OF PREPROCESSING. PROGRAM TERMINATED IN AN ERROR STATE. (CIERR 976) :EDITOR HP32501A.07.20 EDIT/3000 FRI, OCT 27, 1991, 9:35 AM (C) HEWLETT-PACKARD CO. 1985 /T SQLMSG;L ALL UNN FILE UNNUMBERED SQLIN . . . = CEX2.SOMEGRP.
DBEnvironment Access When you invoke the preprocessor in full preprocessing mode, you name an ALLBASE/SQL DBEnvironment. The preprocessor starts a DBE session for that DBEnvironment when preprocessing begins and terminates that session when preprocessing is completed. The preprocessor derives the name of the module from the source code le name unless you supply a di erent name when you invoke the preprocessor: : RUN PSQLC.PUB.
Note A program that accesses more than one DBEnvironment must do so in sequence since only one DBEnvironment can be accessed at a time. Such program design may adversely a ect performance and requires special consideration. To preprocess a program, or to use an already preprocessed ALLBASE/SQL application program, you must satisfy the authorization requirements for each DBEnvironment accessed. Compiling and Linking Figure 2-1 shows the process of compiling and linking an embedded SQL C program.
Using the Preprocessor UDCs Two UDC's for invoking the C preprocessor are provided with ALLBASE/SQL in the HPSQLUDC.PUB.SYS le: PC, illustrated in Figure 2-2, invokes the preprocessor in full preprocessing mode. You specify the source le name, a DBEnvironment name, and a name for SQLMSG (if you do not want preprocessor messages to go to $STDLIST). :PC SourceFileName,DBEnvironment The PC UDC uses the following preprocessor INFO string parameters: ModuleName is the name of the source le.
PC srcfile,dbefile,msgfile=$stdlist continue setvar _savefence hpmsgfence setvar hpmsgfence 2 continue purge !msgfile purge sqlout purge sqlmod purge sqlvar purge sqltype purge sqlextn setvar hpmsgfence _savefence deletevar _savefence file sqlin = !srcfile file sqlmsg = !msgfile; rec=-80,16,f,ascii file sqlout; disc=10000,32; rec=-80,16,f,ascii file sqlmod; disc=1023,10,1; rec=250,,f,binary file sqlvar; disc=2048,32; rec=-80,16,f,ascii file sqltype; disc=2048,32; rec=-80,16,f,ascii file sqlextn; disc=2048,3
PPC srcfile,dbefile,pgmfile,msgfile=$stdlist continue setvar _savefence hpmsgfence setvar hpmsgfence 2 continue purge !msgfile purge sqlout purge sqlmod purge sqlvar purge sqltype purge sqlextn setvar hpmsgfence _savefence deletevar _savefence file sqlin = !srcfile file sqlmsg = !msgfile; rec=-80,16,f,ascii file sqlout; disc=10000,32; rec=-80,16,f,ascii file sqlmod; disc=1023,10,1; rec=250,,f,binary file sqlvar; disc=2048,32; rec=-80,16,f,ascii file sqltype; disc=2048,32; rec=-80,16,f,ascii file sqlextn; di
The example in Figure 2-4 illustrates the use of PPC on an SQLIN that could be successfully preprocessed, but failed to compile because a C error exists in the le. In addition to generating an error message for the C error, the C compiler generates several warning messages. The warning messages are normal and will not cause runtime problems; they are due to the way the C preprocessor declares some of the variables in SQLVAR. :PPC CEX2,PARTSDBE,CEX2P WED, OCT 25, 1991, 1:38 PM HP36216-02A.E1.
The line number referenced in the compiler output messages is the C statement number in the compiler output listing .
Accessing Multiple DBEnvironments An alternative method of accessing more than one DBEnvironment from the same program would be to divide the program into separate compilable les. Each source le would access a DBEnvironment. In each le, start and terminate a DBE session for the DBEnvironment accessed. Then preprocess and compile each le separately. When you invoke the preprocessor, identify the DBEnvironment accessed by the source le being preprocessed.
Source File The source le is a le containing the source code of the C ALLBASE/SQL program with embedded SQL commands for one or more DBEnvironments. The default input lename is: SQLIN An alternative name can be speci ed by using the DROP option in the preprocessor command line, as explained earlier in this chapter. When parsing the source le, the C preprocessor ignores most C statements and C compiler directives in it.
Figure 2-5. Full Preprocessing Mode Input and Output Identifying Preprocessor Output As Figure 2-5 points out, running the C preprocessor in full preprocessing mode creates the following output les: Modi ed source le. Include les. SQL message le. Installable module le. Also, a module is stored in the DBEnvironment speci ed in the preprocessor invocation line. Each of these is described in the following sections.
Modified Source File As the C preprocessor parses the source le, it copies lines from it and any include les into the modi ed source le, inserts conditional compiler directives around the embedded SQL commands, and inserts information around each embedded SQL command. The default modi ed source le name is SQLOUT . Figure 2-9 illustrates the modi ed source le generated for the source le pictured in Figure 1-8. The shaded lines contain information generated by the C preprocessor.
Include Files There are three include les, which contain declarations and de nitions used by the C functions created by the preprocessor and inserted into the modi ed source code le: type include le: the name for this le, which contains type declarations, is: SQLTYPE variable include le: the name for this le, which contains variable declarations, is: SQLVAR externals include le: the name for this le, which contains external procedure declarations, is: SQLEXTN The sqlmodulename and ownername of sections
1. A banner: For Series 900 systems: HP36216-02A.E1.02 C preprocessor/3000 ALLBASE/SQL (C)COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991. ALL RIGHTS RESERVED. Banners are displayed when ISQL, SQLUtil, or a preprocessor is invoked. 2. A summary of the preprocessor invocation conditions: DBEnvironment = PARTSDBE.SOMEGRP.SOMEACCT Module Name = CEX2 3.
: print sqlmsg HP36216-02A.E1.02 C preprocessor/3000 ALLBASE/SQL (C)COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991. ALL RIGHTS RESERVED. DBEnvironment Module Name 32 ****** = PARTSDBE.SOMEGRP.SOMEACCT = CEX2 SalesPriceInd : SQLID | Unsupported type syntax for host variable. (DBERR 10933) SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice :SalesPriceInd FROM PurchDB.
As Figure 2-7 illustrates, the preprocessor can terminate with the warning message ****** ALLBASE/SQL warnings. (DBWARN 10602) when the name of an object in the source le does not match the name of any object in the system catalog. Although a section is stored for the semantically incorrect command, the section is marked as invalid and will not execute at run time if it cannot be validated. : ppc cex2,partsdbe,cex2p HP36216-02A.20.00.00 C preprocessor/3000 ALLBASE/SQL (C)COPYRIGHT HEWLETT-PACKARD CO.
Stored Module Containing Sections In full preprocessing mode, the preprocessor stores a module in the DBEnvironment you specify at preprocessing time. By default, the preprocessor uses the source le name as the name for the module it stores. You can specify a module name with the MODULE option of the preprocessor command.
OWNER: This column identi es the owner of the module. You can specify an owner name when you invoke the preprocessor,or the owner name will default to the login name associated with the preprocessing session. If you are supplying an owner name in a native language other than NATIVE-3000 (ASCII), be sure it is in the same language as that of the DBEnvironment. DBEFILESET: This column indicates the DBEFileSet which contains the DBEFile(s) which in turn contains the section(s).
isql=> SELECT NAME,OWNER,DBEFILESET,SECTION,TYPE,VALID FROM SYSTEM.SECTION; SELECT NAME,OWNER,DBEFILESET,SECTION,TYPE,VALID FROM SYSTEM.
The rst eleven rows in the query result shown in Figure 2-8 describe the sections stored for the system views. The next two rows describe the two views in the sample database: PurchDB.PartInfo and PurchDB.VendorStatistics. Views are always stored as invalid sections, because the run tree is always generated at run time. The remaining rows describe sections associated with two preprocessed programs. CEX2 contains only one section, for executing the SELECT command in the program illustrated in Figure 2-9.
Installable Module File When the preprocessor stores a module in the DBEnvironment you named at preprocessing time, it places a copy of the module in an installable module le. The module in this le can be installed into a DBEnvironment di erent from the DBEnvironment accessed at preprocessing time by using the INSTALL command in ISQL. In order to install the module, you need CONNECT or DBA authority in the target DBEnvironment. The installable module le is named SQLMOD.
Preprocessor Invocation Errors If the source le speci ed is currently being accessed, or if the source le named cannot be found, preprocessing terminates with the following messages, respectively: System error in opening input source file. (DBERR 10922) File open Error sqlin ERRORS (DBERR 10907) Processing terminated prematurely.
Sample Modified Source File The following modi ed source le is the result of preprocessing program cex2 (shown at the end of Chapter 1). In the listing, the preprocessor-added code is shaded for easy reference. The numbers in the gure are the same as the numbers shown for program cex2.
/* SQL Communication Area */ /* Begin Host Variable Declarations */ NNNNNNNNNNNNNNNNN #if 0 EXEC SQL BEGIN DECLARE SECTION; #endif NNNNNNNNNNNNNNNNNNNN char char double sqlind char 42 5 PartNumber[17]; PartName[31]; SalesPrice; SalesPriceInd; SQLMessage[133]; NNNNNNNNNNNNNNNNN #if 0 EXEC SQL END DECLARE SECTION; #endif NNNNNNNNNNNNNNNNNNNN 42 5 /* End Host Variable Declarations */ int SQLStatusCheck() /* Function to Display Error Messages */ { Abort = FALSE; if (sqlca.
boolean ConnectDBE)() /* Function to Connect to PartsDBE { boolean ConnectDBE; ConnectDBE = TRUE; printf("\n Connect to PartsDBE"); */ NNNNNNNNNNNNNNNNN #if 0 EXEC SQL CONNECT TO 'PartsDBE'; #endif { sqlvar1 = "00AE00005061727473444245202020202020202020202020\ 202020202020202020202020202020202020202020202020202020202020\ 202020202020202020202020202020202020202020202020202020202020\ 202020202020202020202020202020202020202020202020202020202020\ 202020202020202020202020202020202020"; sqlxconc(&sqlca,sqlvar1
printf("\n"); printf("\n Begin Work"); NNNNNNNNNNNNNNNNN #if 0 EXEC SQL BEGIN WORK; #endif { sqlvar3 = "00A6007F00110061"; sqlxconc(&sqlca,sqlvar3); } xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll if (sqlca.
int Select() /* Function to Query Parts Table */ { do { printf("\n"); printf("\n Enter Part Number within Parts Table or '/' to STOP > "); scanf("%s",PartNumber); if (PartNumber[0] != '/') { BeginTransaction(); printf("\n SELECT PartNumber, PartName, SalesPrice"); NNNNNNNNNNNNNNNNN #if 0 EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice :SalesPriceInd FROM PurchDB.Parts WHERE PartNumber = :PartNumber; #endif { sqltempv.rec2.
else { } } if ((sqlca.sqlwarn[0] == 'W') || (sqlca.sqlwarn[0] == 'w')) { printf("\n SQL WARNING has occurred. The following row"); printf("\n of data may not be valid!"); } if (sqlca.sqlcode == OK) { DisplayRow(); } else if (sqlca.sqlcode == NotFound) { printf("\n Row not found!"); } else if (sqlca.
Sample Preprocessor Generated Include Files Figures 2-10 through Figure 2-12 illustrate, respectively, the type, variable, and externals include les that correspond to the modi ed source le in Figure 2-9. Note that the preprocessor inserts the following three C compiler directives to reference the include les: #include "sqltype" #include "sqlvar" #include "sqlextn" These three directives are always inserted into the static or global declaration part of the program.
typedef char ownername_type[21]; typedef char modulename_type[21]; typedef short sqlind; typedef unsigned char sqlbinary; typedef int sqlvarbinary; typedef struct { int f1; int f2; } sqltid; typedef struct { int SQLREC1_FIELD1_LEN; char SQLREC1_FIELD1[133]; } SQLREC1; typedef struct { int SQLREC2_FIELD1_LEN; char SQLREC2_FIELD1[20]; } SQLREC2; typedef struct { int SQLREC3_FIELD1_LEN; char SQLREC3_FIELD1[20]; int SQLREC3_FIELD2_LEN; char SQLREC3_FIELD2[32]; double SQLREC3_FIELD3; sqlind SQLREC3_FIELD3_IND; }
int sqlerrl; char sqlerrm[256]; char sqlerrp[8]; int sqlerrd[6]; char sqlwarn[8]; char sqlext[8]; } sqlca_type; typedef struct { short sqlnty; short sqltype; short sqlprec; short sqlscale; int sqltotallen; int sqlvallen; int sqlindlen; int sqlvof; int sqlnof; char sqlname[20]; } sqlformat_type; typedef struct { char sqldaid[8]; int sqldabc; int sqln; int sqld; sqlformat_type *sqlfmtarr; int sqlnrow; int sqlrrow; int sqlrowlen; int sqlbuflen; int sqlrowbuf; } sqlda_type; typedef union { int dummy; SQLREC1 re
static ownername_type sqlowner = "JOANN@ACCT1 static modulename_type sqlmodname = "CEX2 int sqlindex; char *sqlvar1; char *sqlvar2; char *sqlvar3; char *sqlvar4; sqltempv_type sqltempv; Figure 2-11.
3 Host Variables Host variables are variables used to pass the following information between an application program and ALLBASE/SQL: Data values. Null value indicators. String truncation indicators. Bulk processing rows to process. Dynamic commands. Savepoint numbers. Messages from the ALLBASE/SQL message catalog. DBEnvironment names. All host variables used in a C program must be declared in declaration parts of the program.
To hold null value indicators in these data manipulation commands: SELECT INSERT FETCH REFETCH UPDATE UPDATE WHERE CURRENT In queries to indicate string truncation and the string length before truncation To identify the starting row and the number of rows to process in the INTO clause of the following commands: BULK SELECT BULK INSERT To pass dynamic commands at run time with the following commands: PREPARE EXECUTE IMMEDIATE To hold savepoint numbers, which are used in the following commands: SAVEPOINT R
Input and Output Host Variables Host variables can be used for input or for output: Input host variables provide data for ALLBASE/SQL. Output host variables contain data from ALLBASE/SQL. Be sure to initialize an input host variable before using it. When using cursor operations with the SELECT command, initialize the input host variables in the select list and WHERE clause before you execute the OPEN command.
In the INSERT, UPDATE, and UPDATE WHERE CURRENT commands, an indicator variable is an input host variable. The value you put in the indicator variable tells ALLBASE/SQL when to insert a null value in a column: >=0 <0 value is not null value is null The following SELECT command uses an indicator variable, PartNameInd, for data from the PartName column.
Bulk Processing Variables Bulk processing variables can be used with the BULK option of the SELECT or the INSERT command. When used with the BULK SELECT command, two input host variables may be named following the array name in the INTO clause to specify how ALLBASE/SQL should store the query result in the array: INTO :ArrayName [,:StartIndex [,:NumberOfRows]] The StartIndex value denotes at which array element the query result should start.
Declaring Host Variables Host variables may be declared wherever you can declare variables in C programs. For the purpose of this discussion, we de ne declaration part as the portion of a C program where variables having the scope of a le, a function, or a block can be declared. At run time, the scope of a host variable is the same as that of any other C variable declared in the same declaration part. At preprocessing time, however, all host variable declarations are treated as global declarations.
. . . EXEC SQL BEGIN DECLARE SECTION; . . Declarations for global host variables. . EXEC SQL END DECLARE SECTION; . . . int query() { . . . EXEC SQL BEGIN DECLARE SECTION; . . Declarations for local host variables. . EXEC SQL END DECLARE SECTION; . . . . EXEC SQL BEGIN DECLARE SECTION; . . Declarations for local host variables. . EXEC SQL END DECLARE SECTION; . . } . . . main() { . . . } Host Variables Figure 3-1.
Declaring Variables for Data Types Any variable can be used as a host variable. Table 3-1 summarizes C data declarations for host variables of each ALLBASE/SQL data type. Only the type descriptions shown in Table 3-1 are supported by the C preprocessor. Note in particular that the preprocessor does not support user-de ned data types. CHAR Data A CHAR column can be declared for character strings ranging from 1 to 3996 bytes.
Table 3-1. Data Type Declarations SQL DATA TYPES C DATA DECLARATIONS CHAR(1) char dataname ; CHAR(n) char dataname [n+1]; VARCHAR(n) SMALLINT char dataname [n+1]; * short dataname ; or short int dataname ; INTEGER int dataname ; or long int dataname ; or long dataname ; REAL float dataname ; FLOAT(1..24) float dataname ; FLOAT(25..
Table 3-2.
SMALLINT Data You can assign values ranging from -32,768 to +32,767 to a column de ned as SMALLINT. INTEGER Data You can assign values ranging from -2,147,483,648 to +2,147,483,647 to a column de ned as INTEGER. FLOAT Data ALLBASE/SQL o ers the option of specifying the precision of oating point data. You have the choice of a 4-byte or an 8-byte oating point number. (This conforms to ANSI SQL86 level 2 speci cations.) The keyword REAL and FLOAT(1) through FLOAT(24) speci cations map to a 4-byte oat.
BINARY Data As with other data types, use the CREATE TABLE or ALTER TABLE statement to de ne a binary or varbinary column. Up to 3996 bytes can be stored in such a column. BINARY data is stored as a xed length of left-justi ed bytes. It is zero padded up to the xed length you have speci ed. VARBINARY data is stored as a variable length of left-justi ed bytes. You specify the maximum possible length. (Note that CHAR and VARCHAR data is stored in the same manner except that CHAR data is blank padded.
In the following example, data from a binary column de ned with a length of 12 is selected into an sqlbinary host variable. .. . EXEC SQL BEGIN DECLARE SECTION; sqlbinary BinaryHV[12]; EXEC SQL END DECLARE SECTION; .. . EXEC SQL SELECT BinaryColumn INTO :BinaryHV FROM TableA; .. . Declaring Host Variables for VARBINARY Data. Host variables for VARBINARY data columns must be declared as sqlvarbinary, as in the following example: EXEC SQL BEGIN DECLARE SECTION; .. .
In order to pass information between an sqlvarbinary host variable and the database, a special format is used. The internal format of an sqlvarbinary host variable is illustrated below: Array Index Byte Offset 0 1 . .. m-1 0 4 (m-1)*4 m*4 +---------+----------------------------------+---------+ | Length | Data | +---------+----------------------------------+---------+ where: Length, in the rst four bytes, represents the actual data length.
Load the rst array element with the actual data length. Here we'll assume actual length to be 11 bytes. VarbinaryHV[0] = 11; Load the data in bbu , starting at the second element of the array. memcpy(&VarbinaryHV + 1, &bbuff, 11); EXEC SQL INSERT INTO TableA VALUES (:VarbinaryHV, . . . ); .. . Using a Union Structure Here is another method of loading the same host variable.
Load the data in bbu , into the data eld of the structure. memcpy(u1.s1.data, &bbuff, 11); Load the length and the data into the varbinary host variable. memcpy(&VarbinaryHV, &(u1.ubuff), sizeof(u1.ubuff)); EXEC SQL INSERT INTO TableA VALUES (:VarbinaryHV, . . . ); .. . After the successful execution of a SELECT or FETCH statement, the length of the data returned for any varbinary column is found in the rst element of the related host variable array. Selecting and Fetching VARBINARY Data.
DECIMAL Data The DECIMAL data type is not supported by ALLBASE/SQL C. The DECIMAL data type is compatible with an ALLBASE/SQL C double data type. When you use DECIMAL values in arithmetic operations and certain aggregate functions, the precision and scale of the result are functions of the precisions and scales of the values in the operation. Refer to the ALLBASE/SQL Reference Manual for a complete account of how to calculate the precision and scale of DECIMAL results.
When a table or column is de ned with the DEFAULT speci cation, you will not get an error if a column de ned as NOT NULL is not speci ed in the insert list of an INSERT command. Without the DEFAULT speci cation, if a column is de ned as NOT NULL, it must have some value inserted into it. However, if the column is de ned with the DEFAULT speci cation, it satis es both the requirement that it be NOT NULL and have some value, in this case, the default value.
Coding Considerations Any default value must be compatible with the data type of its corresponding column. For example, when the default is an integer constant, the column for which it is the default must be created with an ALLBASE/SQL data type of INTEGER, REAL, or FLOAT. In your application, you input or access data for which column defaults have been de ned just as you would data for which defaults are not de ned.
Declaring Variables for Compatibility Under the following conditions, ALLBASE/SQL performs data type conversion when executing SQL commands containing host variables under the following circumstances: When the data types of values transferred between your program and a DBEnvironment do not match. When data of one type is moved to a host variable of a di erent type. when values of di erent types appear in the same expression.
isql=> INFO PurchDB.OrderItems; Column Name Data Type (length) Nulls Allowed --------------------------------------------------------------------ORDERNUMBER Integer NO ITEMNUMBER Integer NO VENDPARTNUMBER Char (16) YES PURCHASEPRICE Decimal (10,2) NO ORDERQTY Smallint YES ITEMDUEDATE Char ( 8) YES RECEIVEDQTY Smallint YES For example, the query in Figure 3-2 produces a single-row query result. The declare section contains data types equivalent to or compatible with the data types in the PurchDB.
. EXEC SQL BEGIN DECLARE SECTION; double Discount ; double PurchasePrice ; short int OrderQty ; sqlind OrderQtyInd ; int OrderNumber ; . . EXEC SQL END DECLARE SECTION; . { . EXEC SQL SELECT PurchasePrice * :Discount , OrderQty, INTO :PurchasePrice, :OrderQty :OrderQtyInd FROM PurchDB.OrderItems WHERE OrderNumber = :OrderNumber . . } Figure 3-2. Declaring Host Variables for Single-Row Query Results The example in Figure 3-3 is similar to that in Figure 3-2.
. . EXEC SQL BEGIN DECLARE SECTION; double Discount; struct { double PurchasePrice; short int OrderQty; sqlind OrderQtyInd; int OrderNumber; } OrdersArray[26]; short int FirstRow; short int TotalRows; int LowValue; int HighValue; int GroupCriterion; . . EXEC SQL END DECLARE SECTION; . . { . . EXEC SQL BULK SELECT PurchasePrice * :Discount , OrderQty, OrderNumber INTO :OrdersArray, :FirstRow, :TotalRows FROM PurchDB.
String Data Conversion When ALLBASE/SQL stores the characters in a C string into a CHAR column, the nal ASCII 0 is removed and any remaining positions to the right are padded with spaces. Internally, when ALLBASE/SQL stores the characters in a C string to a VARCHAR column, it only stores the string up to but not including the ASCII 0. The length of the string is stored in a four-byte header in the front of each VARCHAR data type.
Numeric Data Conversion When you use numeric data of di erent types in an expression or comparison operation, data of the lesser type is converted into data of the greater type, and the result is expressed in the greater type. ALLBASE/SQL numeric types have the following precedence, from highest to lowest: 1. FLOAT 2. DECIMAL 3. INTEGER 4. SMALLINT The following example illustrates numeric type conversion: EXEC SQL BEGIN DECLARE SECTION; int Discount; int MaxPurchasePrice; . . .
Declaring Variables for Program Elements The following section discusses how to declare elements speci c to ALLBASE/SQL programs. In addition, Table 3-2 provides examples of these special elements. sqlca Array Every ALLBASE/SQL C program must have the SQL Communications Area (sqlca) declared in the global declaration part.
Bulk Processing Arrays When you declare a structure array for holding the results of a BULK SELECT or BULK FETCH operation, ensure that you declare the elds in the same order as in the select list. (For single-row query results, however, the order of declaration does not have to match the select list order.) In addition, each indicator variable eld must be declared in the declaration of the structure array immediately after the host variable eld it describes.
Savepoint Numbers Savepoint numbers are positive numbers ranging from 1 to 2,147,483,647. A host variable for holding a savepoint number should be declared as an integer. . . . EXEC SQL BEGIN DECLARE SECTION; int SavePoint1 ; . . . EXEC SQL END DECLARE SECTION; . . . { . . . EXEC SQL SAVEPOINT :Savepoint1 ; . . . EXEC SQL ROLLBACK WORK TO :Savepoint1 ; . . } Figure 3-5.
Messages from the Message Catalog The maximum size of a message catalog message is 256 bytes. Figure 3-6 illustrates how a host variable for holding a message might be declared. . . . EXEC SQL BEGIN DECLARE SECTION; char SQLMessage[256] . . . EXEC SQL END DECLARE SECTION; . . . { . . . EXEC SQL SQLEXPLAIN :SQLMessage ; printf("%s\n",SQLMessage); . . . } ; Figure 3-6.
DBEnvironment Name The maximum pathname (either relative or absolute) of a DBECon le is 128 bytes. The DBECon le name is the same as the DBEnvironment name. The name you store in this host variable does not have to be delimited by single quotation marks. EXEC SQL BEGIN DECLARE SECTION; char SomeDBE[128]; . . . EXEC SQL END DECLARE SECTION; . . . { printf("\n Enter DBEnvironment name> "); scanf("%s",SomeDBE); EXEC SQL CONNECT to :SomeDBE; . . . } Figure 3-7.
4 Runtime Status Checking and the sqlca This chapter examines the need for runtime status checking. It describes the sqlca and the conditions under which its data items are set by ALLBASE/SQL. It also gives several examples of implicit and explicit status checking, some of which use SQLEXPLAIN to display a status message. Examples of handling speci c status checking tasks are included under \Approaches to Status Checking.
When several errors or warnings occur, you can use SQLEXPLAIN to retrieve messages for all of them. Messages are available to your program in the order in which the errors or warnings occurred. When ALLBASE/SQL rolls back the current transaction, it does not continue to look for errors. This means that the last message retrieved will indicate the cause of the roll back. An example of this scenario is presented later in this chapter under \sqlcode.
For example, in the sample database (SampleDBE), each order is de ned by rows in two tables: one row in the PurchDB.Orders table and one or more rows in the PurchDB.OrderItems table. A transaction that deletes orders from the database has to delete all the rows for a speci c order from both tables to maintain data consistency. A program containing such a transaction should commit work to the database only if it is able to delete the row from the PurchDB.
Using the sqlca Every ALLBASE/SQL program must have the SQL Communications Area (sqlca) declared in the global declaration part. You can use the INCLUDE command to declare the sqlca: EXEC SQL INCLUDE SQLCA; When the C preprocessor parses this command, it inserts the following type de nition into the modi ed source le: sqlca_type sqlca; Optionally, you can use this type de nition in the global declaration part of your source le instead of using the INCLUDE command to declare the sqlca.
Table 4-1. sqlca Status Checking Fields FIELD NAME sqlca.sqlcode or sqlcode SET TO CONDITION 0 no error occurred during command execution less than 0 error, command not executed 100 sqlca.sqlerrd[2] no rows qualify for DML operation (does not apply to dynamic commands) number of rows put into output host variables data retrieval operation number of rows processed data change operation 0 error in single row data change operation 0 sqlcode equals 100 sqlca.sqlwarn[0] or sqlca.
sqlcode sqlcode can contain one of the following values: 0, when an SQL command executes without generating an error condition and without generating a no rows qualify condition. A negative number, when an error condition exists and an ALLBASE/SQL command cannot be executed.
To obtain all sqlcodes associated with the execution of an SQL command, you execute the SQLEXPLAIN command until sqlcode is 0: if (sqlca.sqlcode == 100) { printf("No rows qualified for this operation.\n"); } else if (sqlca.sqlcode < 0) SQLStatusCheck(); . . . int SQLStatusCheck() { do { EXEC SQL SQLEXPLAIN :SQLMessage; printf("%s\n",SQLMessage); } while (sqlca.sqlcode != 0); } The function named SQLStatusCheck is executed when sqlcode is a negative number.
sqlerrd[2] sqlca.sqlerrd[2] can contain one of the following values: 0, when sqlcode is 100 or when one of the following commands causes an error condition: INSERT UPDATE DELETE UPDATE WHERE CURRENT DELETE WHERE CURRENT If an error occurs during execution of INSERT, UPDATE, or DELETE, one or more rows may have been processed prior to the error. In these cases, you may want to either COMMIT WORK or ROLLBACK WORK, depending on the transaction.
sqlwarn[0] A W in sqlwarn[0], in conjunction with a 0 in sqlcode, indicates that the SQL command just executed caused a warning condition. Warning conditions ag unusual but not necessarily important conditions. For example, if a program attempts to submit an SQL command that grants an already existing authority, a message such as the following would be retrieved when SQLEXPLAIN is executed: User PEG already has DBA authorization.
If multiple warnings but no errors result when ALLBASE/SQL processes a command, sqlwarn[0] is set to W and remains set until the last warning message has been retrieved by SQLEXPLAIN or another SQL command is executed. In the following example, DisplayWarning is executed when this condition exists: if ((sqlca.sqlcode == 0) & (sqlca.sqlwarn[0] == 'W')) { do { DisplayWarning(); } while (sqlca.sqlwarn[0] == 'W'); } . . .
sqlwarn[2] A W in sqlwarn[2] indicates that at least one null value was eliminated from the argument set of an aggregrate function. For example: EXEC SQL SELECT MAX(OrderQty) INTO :MaxOrderQty FROM PurchDB.OrderItems; If any OrderQty values are null, the following occurs: sqlwarn[2] is set to W. sqlcode is set to 0. SQLEXPLAIN retrieves the message: NULL values eliminated from the argument of an aggregate function.
sqlwarn[6] When an error occurs that causes ALLBASE/SQL to roll back the current transaction, sqlwarn[6] is set to W. ALLBASE/SQL automatically rolls back transactions when sqlcode is equal to -4008, or equal to or less than -14024. When such errors occur, ALLBASE/SQL does the following: Sets sqlwarn[6] to W. Sets sqlwarn[0] to W. Sets sqlcode to a negative number. If you want to terminate your program any time ALLBASE/SQL has to roll back the current transaction, you can just test sqlwarn[6]. if (sqlca.
Approaches to Status Checking This section presents examples of how to use implicit and explicit status checking and to notify program users of the results of status checking. Implicit status checking is useful when control to handle warnings and errors can be passed to one prede ned point in the program. Explicit status checking is useful when you want to test for speci c sqlca values before passing control to one of several locations in your program.
SQLWARNING If WHENEVER SQLWARNING is in e ect, ALLBASE/SQL checks for a W in sqlwarn[0] after processing any SQL command except the following: BEGIN DECLARE SECTION DECLARE END DECLARE SECTION INCLUDE SQLEXPLAIN WHENEVER NOT FOUND If WHENEVER NOT FOUND is in e ect, ALLBASE/SQL checks for the value 100 in sqlcode after processing a SELECT or FETCH command. A WHENEVER command for each of these conditions can be in e ect at the same time.
int Procedure2() { EXEC SQL SQLCommand2; } int Procedure1() { EXEC SQL WHENEVER SQLERROR GOTO a2000; EXEC SQL SQLCommand1; } . . . { Procedure1(); Procedure2(); } EXEC SQL WHENEVER SQLERROR CONTINUE; The code that the preprocessor generates depends on the condition and action in a WHENEVER command.
As the previous example illustrates, you pass control to an exception-handling routine with a WHENEVER command, by using a GOTO statement with an alpha-numeric line label rather than a function name. Therefore after the exception-handling routine is executed, control cannot automatically return to the statement which invoked it. You must use another GOTO statement to explicitly pass control to a speci c point in your program: /* WHENEVER Routine -- SQL Error */ a2000: if ((sqlca.
Program Illustrating Implicit and Explicit Status Checking The program in Figure 4-1 contains ve WHENEVER commands to demonstrate implicit status checking. It also uses two explicit status checking routines. The WHENEVER command numbered 4 1 5 handles errors associated with the following commands: CONNECT BEGIN WORK COMMIT WORK RELEASE The WHENEVER command numbered 4 2 5 turns o the rst WHENEVER command.
/* Program cex5 */ /* /* /* /* /* /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */ This program illustrates the use of SQL's SELECT command to */ retrieve one row or tuple of data at a time. */ This programs is the same as cex2 with added status checking */ and deadlock routines. */ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */ typedef int boolean; char boolean boolean int response[2]; Abort; SQLCommandDone; TryCounter; #include
int SQLStatusCheck() /* Function to Display Error Messages */ { if ((sqlca.sqlcode == DeadLock) || (sqlca.sqlcode == NoMemory)) { if (TryCounter == TryLimit) { SQLCommandDone = TRUE; printf("\n Could not complete transaction. You may want to try again."); } else SQLCommandDone = FALSE; } else { Abort = FALSE; if ((sqlca.sqlwarn[6] = 'W') || (sqlca.sqlwarn[6] = 'w')) Abort = TRUE; } do { EXEC SQL SQLEXPLAIN :SQLMessage; printf("%s\n",SQLMessage); } while (sqlca.
boolean BeginTransaction() { boolean BeginTransaction; BeginTransaction = TRUE; /* Function to Begin Work */ printf("\n"); printf("\n Begin Work"); EXEC SQL BEGIN WORK; goto exit; a1000: SQLStatusCheck(); EndTransaction(); ReleaseDBE(); /* WHENEVER SQLERROR entry point 1 */ exit: return (BeginTransaction); } /* End BeginTransaction Function */ int EndTransaction() /* Function to Commit Work */ { printf("\n"); printf("\n Commit Work"); EXEC SQL COMMIT WORK; goto exit; a1000: SQLStatusCheck(); ReleaseDBE(
a1000: SQLStatusCheck(); EndTransaction(); /* WHENEVER SQLERROR entry point 1 */ exit: return(0); } /* End ReleaseDBE Function */ EXEC SQL WHENEVER SQLERROR CONTINUE; int DisplayRow() { 42 5 /* Function to Display Parts Table Rows */ printf("\n"); printf(" Part Number: printf(" Part Name: %s\n", PartNumber); %s\n", PartName); if (SalesPriceInd < 0) { printf("\n Sales Price: is NULL"); } else printf(" Sales Price: %10.
printf("\n SELECT PartNumber, PartName, SalesPrice"); EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice :SalesPriceInd FROM PurchDB.Parts WHERE PartNumber = :PartNumber; /* If no errors occur, set command done flag and display the row. */ SQLCommandDone = TRUE; DisplayRow(); EndTransaction(); } /* End if */ } /* End do */ while (PartNumber[0] != '/'); goto exit; a2000: /* WHENEVER SQLERROR entry point 2 */ if (sqlca.
main() { /* Beginning of program */ printf("\n Program to SELECT specified rows from"); printf("\n the Parts Table - cex5"); printf("\n"); printf("\n Event List:"); printf("\n CONNECT TO PartsDBE"); printf("\n BEGIN WORK"); printf("\n SELECT the specified row from the Parts Table"); printf("\n until the user enters a '/'"); printf("\n COMMIT WORK"); printf("\n RELEASE from PartsDBE"); printf("\n"); if (ConnectDBE()) { SQLCommandDone = TRUE; /* Initialize command done flag */ Select(); ReleaseDBE(); } el
Explicit Status Checking Techniques With explicit error handling, you invoke a function after explicitly checking sqlca values rather than using the WHENEVER command. The program in Figure 4-1 has already illustrated several uses of explicit error handling to: Isolate errors so critical that they caused ALLBASE/SQL to roll back the current transaction. Control the number of times SQLEXPLAIN is executed. Detect when more than one row quali es for the SELECT operation.
#define Deadlock #define OK #define NotFound #define MultipleRows #define NoMemory . . . int SelectActivity() { -14024 0 100 -10002 -4008 This function prompts for a number that indicates whether the user wants to SELECT, UPDATE, DELETE, or INSERT rows, then invokes a function that accomplishes the selected activity. The DONE ag is set when the user enters a slash. } . . . int InsertData() { Statements that accept data from the user appear here. EXEC SQL INSERT INTO PurchDB.
int UpdateData() { This function veri es that the row(s) to be changed exist, then invokes function DisplayUpdate to accept new data from the user. EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice FROM PurchDB.Parts WHERE PartNumber = :PartNumber; switch(sqlca.sqlcode) { case OK: . . .
int DeleteData() { This function veri es that the row(s) to be deleted exist, then invokes the function DisplayDelete to delete the row(s). EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice FROM PurchDB.Parts WHERE PartNumber = :PartNumber; switch(sqlca.
int SelectData() { Statements that prompt for a partnumber appear here. EXEC SQL SELECT PartNumber, PartName, SalesPrice INTO :PartNumber, :PartName, :SalesPrice FROM PurchDB.Parts WHERE PartNumber = :PartNumber; switch(sqlca.sqlcode) { case OK: . . } . .
else { Abort = FALSE; if (sqlca.sqlwarn[6] == 'W') { /* The transaction was rolled back due to other than deadlock or shared memory problems. */ Abort = TRUE; do { EXEC SQL SQLEXPLAIN :SQLMessage; printf("%s\n",SQLMessage); } while (sqlca.sqlcode != 0); } if (Abort) { TerminateProgram(); } else SQLCommandDone = TRUE; } } . if (SQLCommandDone) { . /* Prompt user for a part number. */ . TryCounter = 0; TryLimit = 3; . /* A transaction is started. */ .
Handling Deadlock and Shared Memory Problems A deadlock exists when two transactions need data that the other transaction already has locked. When a deadlock occurs, ALLBASE/SQL rolls back the transaction with the larger priority number. If two deadlocked transactions have the same priority, ALLBASE/SQL rolls back the newer transaction. An sqlcode of -14024 indicates that a deadlock has occurred: Deadlock detected.
In this case, you also need to test sqlcode to determine whether the operation executed without error. If sqlcode is negative, sqlerrd[2] contains the number of rows that could be successfully retrieved or inserted before an error occurred. If sqlcode is 0, sqlerrd[2] contains the total number of rows that ALLBASE/SQL put into or took from the host variable array. If, in a BULK SELECT operation, more rows qualify than the array can accommodate, sqlcode will be 0. Examples follow.
When using the BULK SELECT, BULK FETCH, or BULK INSERT commands, you can use the sqlerrd[2] value in several ways: If the command executes without error, to determine the number of rows retrieved into an output host variable array or inserted from an input host variable array. If the command causes an error condition, to determine the number of rows that could be successfully put into or taken out of the host variable array before the error occurred. BULK Operations.
#define #define #define OK 0 NotFound 100 MaximumRows 200 /*Begin Host Variable Declarations */ EXEC SQL Begin Declare Section; struct { char PartNumber[17]; char PartName[31]; double SalesPrice; } PartsTable[MaximumRows]; char SQLMessage[133]; EXEC SQL End Declare Section; /* End Host Variable Declarations */ sqlca : sqlca_type; int int /* SQL Communication Area */ i; NumberOfRows; int BulkSelect() { EXEC SQL BULK SELECT PartNumber, PartName, SalesPrice INTO :PartsTable FROM PurchDB.
. . . int DisplayTable() { if (sqlerrd[2] == MaximumRows) 42 5 { printf("\n"); printf("\nWARNING: There may be additional rows that qualify!"); } The column headings are displayed here. for (i = 0; i < sqlerrd[2]; i++) DisplayRow(); printf("\n"); } 43 5 int DisplayRow() { printf(PartNumber[i], "%s\n |"); printf(PartName[i], "%s\n |"); printf(SalesPrice[i], "%.
Detecting End of Scan Previous examples in this chapter have illustrated how an sqlcode of 100 can be detected and handled for data manipulation commands that do not use a cursor. When a cursor is being used, this sqlcode value can be used to determine when all rows in an active set have been fetched: int FetchRow() { EXEC SQL FETCH CURSOR1 INTO :PartNumber, :PartName, :SalesPrice; } . . . . . . switch(sqlca.
Determining When More Than One Row Qualifies If more than one row quali es for a non-BULK SELECT or FETCH operation, ALLBASE/SQL sets sqlcode to -10002. In the following example, when sqlcode is MultipleRows (de ned as -10002 in the declaration part), a status checking function is not invoked; instead a warning message is displayed: int UpdateData() { This function veri es that the row(s) to be changed exist, then invokes the function DisplayUpdate to accept new data from the user.
Detecting Log Full Condition When the log le is full, log space must be reclaimed before ALLBASE/SQL can process any additional transactions. Your program can detect the situation, and it can be corrected by the DBA. SQLEXPLAIN retrieves the following message: Log full. (DBERR 14046) In the following example, sqlcode is checked for a log full condition. If the condition is true, ALLBASE/SQL has rolled back the current transaction.
5 Simple Data Manipulation Simple data manipulation is a programming technique used to SELECT or INSERT a single row. It can also be used to INSERT, DELETE, or UPDATE one or more rows based on a speci c criterion . These types of data manipulation operations are considered simple because they can be done with SQL data manipulation commands that staisfy the following conditions: Do not contain the BULK option; therefore, the host variables used are not arrays, and data references are simpli ed.
Note that the GROUP BY, HAVING, and ORDER BY clauses are not necessary, since these clauses usually describe multiple-row query results. You may omit the WHERE clause from certain queries when the select list contains only aggregate functions: EXEC SQL SELECT AVG(SalesPrice) INTO :AvgSalesPrice FROM PurchDB.Parts; A WHERE clause may be used, however, to qualify the rows over which the aggregate function is applied: EXEC SQL SELECT INTO FROM WHERE AVG(SalesPrice) :AvgSalesPrice PurchDB.
When a column named in the WHERE clause has a unique index on it, you can omit testing for multiple-row query results. A unique index prevents the key column(s) from having duplicate values. The following index, for example, ensures that only one row will exist for any part number in PurchDB.Parts: CREATE UNIQUE INDEX PartNumIndex ON PurchDB.Parts (PartNumber) If a key column of a unique index can contain a null value , the unique index insures that no more than one null value can exist for that column.
To prohibit the multiple-row changes possible if multiple rows qualify for an UPDATE or DELETE operation. If multiple rows qualify for the SELECT operation, the UPDATE or DELETE command would not be executed. Alternatively, the user could be advised that multiple rows would be a ected and given a choice about whether to perform the change: The program prompts the user for an order number and a vendor part number in preparation for allowing the user to change the vendor part number.
INSERT In simple data manipulation, you use the INSERT command to either insert a single row or copy one or more rows into one table from another table. Use the following form of the INSERT command to insert a single row: INSERT INTO TableName (ColumnNames) VALUES (DataValues) You can omit ColumnNames when you provide values for all columns in the target table: EXEC SQL INSERT INTO PurchDB.
UPDATE In simple data manipulation, you use the UPDATE command to change data in one or more columns: UPDATE TableName SET Columname = ColumnValue [,...] WHERE SearchCondition As in the case of the DELETE command, if you omit the WHERE clause, the value of any column speci ed is changed in all rows of the table. If the WHERE clause is speci ed, all rows satisfying the search condition are changed, for example: EXEC SQL UPDATE PurchDB.
ContactNameInd = 0; DELETE In simple data manipulation, you use the DELETE command to delete one or more rows from a table: DELETE FROM TableName WHERE SearchCondition The WHERE clause speci es a SearchCondition that rows must meet to be deleted, for example: EXEC SQL DELETE FROM PurchDB.Orders WHERE OrderDate < :OrderDate; If the WHERE clause is omitted, all rows in the table are deleted.
The program displays the row, then prompts for another order number.
Because SELECT commands are often executed prior to a related UPDATE, DELETE, or INSERT command, you must decide whether to make each command a separate transaction or combine commands within one transaction: If you combine SELECT and DELETE operations within one transaction, when the DELETE command is executed, the row deleted is guaranteed to be the same row retrieved and displayed for the user.
In the case of some multi-command transactions, you must execute multiple data manipulation commands within a single transaction for the sake of logical data consistency: In the following example, the DELETE and INSERT commands are used in place of the UPDATE command to insert null values into the target table. EXEC SQL BEGIN WORK; The DELETE command is executed.
Sample Program Using Simple DML Commands The ow chart shown in Figure 5-1 summarizes the functionality of program cex7. This program uses the four simple data manipulation commands to operate on the PurchDB.Vendors table. A function menu determines whether to execute one or more SELECT, UPDATE, DELETE, or INSERT operations. Each execution of a simple data manipulation command is done in a separate transaction. The runtime dialog for program cex7 appears in Figure 5-2, and the source code in Figure 5-3.
The Update function 4 23 5 lets the user UPDATE the value of a column only if it contains a null value. The function prompts 4 24 5 for a vendor number or a 0. If a 0 is entered, the function menu is re-displayed. If a vendor number is entered, function BeginTransaction is executed 4 25 5. Then a SELECT command is executed 4 26 5 to retrieve data from PurchDB.Vendors for the vendor speci ed. The sqlca.
If the SELECT command can be successfully executed, the DisplayDelete function 4 39 5 is executed. This function executes function DisplayRow to display the row retrieved 4 32 5. Then the user is asked whether she wants to actually delete the row 4 33 5. If not, the transaction is terminated 4 43 5 and the user re-prompted for a vendor number or a 0. If so, the DELETE command 4 34 5 is executed before the transaction is terminated 4 43 5 and the user re-prompted.
Figure 5-1.
Figure 5-1.
Program for Simple Data Manipulation of Vendors Table - cex7 Connect to PartsDBE 1 2 3 4 . . . . . . . . . . . . . . . . SELECT UPDATE DELETE INSERT rows rows rows rows from with from into PurchDB.Vendors table null values in PurchDB.Vendors table PurchDB.Vendors table PurchDB.Vendors table Enter choice or 0 to STOP > 4 *** Function to INSERT rows into PurchDB.
*** Function to SELECT a row from the Vendors Table *** Enter Vendor Number to SELECT from Vendors Table or 0 for MENU > 9016 Begin Work SELECT * from PurchDB.Vendors VendorNumber: VendorName: ContactName: PhoneNumber: VendorStreet: VendorCity: VendorState: VendorZipCode: VendorRemarks 9016 Wolfe Works Stanley Wolfe 408 975 6061 7614 Canine Way San Jose CA 90016 is NULL Commit Work Enter Vendor Number to SELECT from Vendors Table or 0 for MENU > 0 1 2 3 4 . . . . . . . . . . . .
Enter Vendor Number to UPDATE within Vendors Table or 0 for MENU > 0 1 2 3 4 . . . . . . . . . . . . SELECT UPDATE DELETE INSERT rows rows rows rows from with from into PurchDB.Vendors table null values in PurchDB.Vendors table PurchDB.Vendors table PurchDB.Vendors table Enter choice or 0 to STOP > 3 *** Function to DELETE rows from PurchDB.Vendors *** Enter Vendor Number to DELETE from Vendors Table or 0 for MENU > 9016 Begin Work SELECT * from PurchDB.
/* Program cex7 */ /* /* /* /* /* /* /* /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of SQL's data manipulation commands for simple operations. It uses the UPDATE command with indicator variables to update any row in the Vendors table that contains null values. It also uses indicator variables in conjunction with SELECT and INSERT commands. The DELETE command is also illustrated.
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 */ { Abort = FALSE; if (sqlca.sqlcode < DeadLock) Abort = TRUE; do { EXEC SQL SQLEXPLAIN :SQLMessage; printf("%s\n",SQLMessage); } while (sqlca.
boolean BeginTransaction() { boolean BeginTransaction; printf("\n"); printf("\n Begin Work"); EXEC SQL BEGIN WORK; if (sqlca.sqlcode != OK) { BeginTransaction = FALSE; SQLStatusCheck(); ReleaseDBE(); } else BeginTransaction = TRUE; /* Function to Begin Work */ 4 11 5 44 5 return (BeginTransaction); } /* End BeginTransaction Function */ int EndTransaction() /* Function to Commit Work */ { printf("\n"); printf("\n Commit Work"); EXEC SQL COMMIT WORK; if (sqlca.
int DisplayRow() /* Function to Display Parts Table Rows */ { printf("\n"); printf("Vendor Number: %10d\n", VendorNumber); printf("Vendor Name: %s\n", VendorName); if (ContactNameInd != 0) { printf("Contact Name: } else printf("Contact Name: if (PhoneNumberInd != 0) { printf("Phone Number: } else printf("PhoneNumber: is NULL \n"); 4 13 5 48 5 %s\n", ContactName); is NULL \n"); 48 5 %s\n", PhoneNumber); printf("VendorStreet: %s\n", VendorStreet); printf("VendorCity: %s\n", VendorCity); printf("VendorS
printf("\n"); printf("\n SELECT * from PurchDB.Vendors"); EXEC SQL SELECT VendorNumber, VendorName, ContactName, PhoneNumber, VendorStreet, VendorCity, VendorState, VendorZipCode, VendorRemarks INTO :VendorNumber, :VendorName, :ContactName :ContactNameInd, :PhoneNumber :PhoneNumberInd, :VendorStreet, :VendorCity, :VendorState, :VendorZipCode, :VendorRemarks :VendorRemarksInd FROM PurchDB.Vendors WHERE VendorNumber = :VendorNumber; 4 12 5 switch(sqlca.
int DisplayUpdate() /* Display & Update row in Parts Table */ { DisplayRow(); if (AnyNulls) { if (ContactNameInd != 0) { printf("\n"); printf("\n Enter new Contact Name or 0 for NULL > "); getline(ContactName); } if (PhoneNumberInd != 0) { printf("\n"); printf("\n Enter new Phone Number of 0 for NULL > "); getline(PhoneNumber); } if (VendorRemarksInd != 0) { printf("\n"); printf("]\n Enter new Vendor Remarks or 0 for NULL > "); getline(VendorRemarks); } 4 27 5 4 18 5 4 19 5 4 20 5 4 19 5 4 20 5 4 19 5 4
int Update() /* Update a row within the Parts Table */ 4 23 5 { printf("\n"); printf("\n *** Function to UPDATE rows in PurchDB.Vendors ***"); printf("\n"); do { printf("\n Enter Vendor Number to UPDATE in Vendors Table or 0 for MENU >"); while (scanf("%d%*c", &VendorNumber) == 0); 4 24 5 if (VendorNumber != 0) { BeginTransaction(); 4 25 5 printf("\n"); printf("\n SELECT * from PurchDB.
EndTransaction(); 4 31 5 } /* End if */ } while (VendorNumber != 0); } /* End of Update Function */ int DisplayDelete() { /* Display & optionally Delete Rows */ DisplayRow(); printf("\n"); printf("\n Is it OK to DELETE this row (N/Y) ? > "); scanf("%s",response2); if ((response2[0] == 'Y') || (response2[0] == 'y')) { printf("\n"); printf("\n DELETE Row from PurchDB.Vendors Table"); EXEC SQL DELETE FROM PurchDB.Vendors WHERE VendorNumber = :VendorNumber; 4 39 5 4 32 5 4 33 5 4 34 5 if (sqlca.
printf("\n"); printf("\n SELECT * from PurchDB.Vendors"); EXEC SQL SELECT VendorNumber, VendorName, ContactName, PhoneNumber, VendorStreet, VendorCity, VendorState, VendorZipCode, VendorRemarks INTO :VendorNumber, :VendorName, :ContactName :ContactNameInd, :PhoneNumber :PhoneNumberInd, :VendorStreet, :VendorCity, :VendorState, :VendorZipCode, :VendorRemarks :VendorRemarksInd FROM PurchDB.Vendors WHERE VendorNumber = :VendorNumber; if (sqlca.sqlcode == OK) DisplayDelete(); else if (sqlca.
do { VendorNumber = 0; printf("\nEnter Vendor Number to INSERT into Vendors Table or 0 for MENU>"); while(scanf("%d%*c", &VendorNumber) == 0); 4 45 5 if (VendorNumber != 0) { printf("\n Enter Vendor Name > "); getline(VendorName); printf("\n Enter Contact Name or a 0 for NULL > "); getline(ContactName); 4 46 5 if (ContactName[0] == '0') ContactNameInd = -1; else ContactNameInd = 0; 4 47 5 printf("\n Enter Phone Number or a 0 for NULL > "); getline(PhoneNumber); 4 46 5 if (PhoneNumber[0] == '0') PhoneN
printf("\n"); printf("\n INSERT row into PurchDB.Vendors"); EXEC SQL INSERT INTO PurchDB.Vendors (VendorNumber, VendorName, ContactName, PhoneNumber, VendorStreet, VendorCity, VendorState, VendorZipCode, VendorRemarks) VALUES (:VendorNumber, :VendorName, :ContactName :ContactNameInd, :PhoneNumber :PhoneNumberInd, :VendorStreet, :VendorCity, :VendorState, :VendorZipCode, :VendorRemarks :VendorRemarksInd); if (sqlca.
do { printf("\n"); 4 52 5 printf("\n 1 . . .SELECT rows from PurchDB.Vendors Table"); printf("\n 2 . . .UPDATE rows with NULL values in PurchDB.Vendors Table"); printf("\n 3 . . .DELETE rows from PurchDB.Vendors Table"); printf("\n 4 . . .INSERT rows into PurchDB.
6 Processing with Cursors Processing with cursors gives you the option of operating on a multiple-row query result, one row at a time. The query result is referred to as an active set. You use a pointer called a cursor to move through the active set, retrieving a row at a time into host variables and optionally updating or deleting the row. Reporting applications may nd this technique useful.
DECLARE CURSOR The DECLARE CURSOR command names a cursor and associates it with a particular SELECT command: DECLARE CursorName [IN DBEFileSetName] CURSOR FOR SelectCommand [FOR UPDATE OF ColumnName [,ColumnName...] Note that the DECLARE CURSOR command has two optional clauses: The IN clause de nes the DBEFileSet in which the section generated by the preprocessor for this command is stored. If no IN clause is speci ed, le space in the SYSTEM DBEFileSet is used.
When performing cursor processing, the ORDER BY clause may be useful. In the previous example, the rows in the query result will be in order by ascending bin number to help the program user, who will be moving from bin to bin, taking a physical inventory. The DECLARE CURSOR command is actually a preprocessor directive. When the preprocessor parses this command, it stores a section in the target DBEnvironment. At run time, the section is not executed when the DECLARE CURSOR command is encountered.
The rst time you execute the FETCH command, the rst row in the query result becomes the current row. With each subsequent execution of the FETCH command, each succeeding row in the query result becomes the current row. After the last row in the query result has been fetched, ALLBASE/SQL sets sqlca.sqlcode to 100. ALLBASE/SQL also sets sqlca.sqlcode to 100 if no rows qualify for the active set. You should test for an sqlca.
UPDATE WHERE CURRENT This command can be used to update the current row when the SELECT command associated with the cursor does not contain one of the following: DISTINCT clause in the select list. Aggregate function in the select list. FROM clause with more than one table. ORDER BY clause. GROUP BY clause. The UPDATE WHERE CURRENT command identi es the active set to be updated by naming the cursor and the column(s) to be updated: UPDATE TableName SET ColumnName = ColumnValue [,...
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN AdjustmentQty = 0 WHERE CURRENT OF AdjustQtyOnHand; NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 6-6 Processing with Cursors
In the previous example, the order of the rows in the query result is not important. Therefore the SELECT command associated with the cursor AdjustQtyOnHand does not need to contain an ORDER BY clause and the UPDATE WHERE CURRENT command can be used.
DELETE WHERE CURRENT This command can be used to delete the current row when the SELECT command associated with the cursor does not contain one of the following: DISTINCT clause in the select list. Aggregate function in the select list. FROM clause with more than one table. ORDER BY clause. GROUP BY clause.
CLOSE When you no longer want to operate on the active set, you use the CLOSE command: CLOSE CursorName The CLOSE command frees up ALLBASE/SQL internal bu ers used to handle cursor operations. This command does not release any locks obtained since the cursor was opened; to release locks, you must terminate the transaction with a COMMIT WORK or a ROLLBACK WORK: The program opens a cursor and operates on the active set. After the last row has been operated on, the cursor is closed.
Transaction Management for Cursor Operations The time at which ALLBASE/SQL obtains locks during cursor processing depends on whether ALLBASE/SQL uses an index scan or a sequential scan to retrieve the query result. When a cursor is based on a SELECT command for which ALLBASE/SQL can use an index scan , locks are obtained when the FETCH command is executed.
Using KEEP CURSOR Cursor operations in an application program let you manipulate data in an active set associated with a SELECT command. The cursor is a pointer to a row in the active set. The KEEP CURSOR option of the OPEN command lets you maintain the cursor position in an active set beyond transaction boundaries. This means you can scan and update a large table without holding locks for the duration of the entire scan. You can also design transactions that avoid holding any locks around terminal reads.
Figure 6-1. Cursor Operation without the KEEP CURSOR Feature After the cursor is opened, successive FETCH commands advance the cursor position. Any exclusive locks acquired along the way are retained until the transaction ends. If you have selected the Cursor Stability option in the BEGIN WORK command, shared locks on pages that have not been updated are released when the cursor moves to a tuple on a new data page. Exclusive locks are not released until a COMMIT WORK, which also closes the cursor.
Figure 6-2. Cursor Operation Using KEEP CURSOR WITH LOCKS OPEN Command Using KEEP CURSOR WITH NOLOCKS The feature has the following e ects: A COMMIT WORK command does not close the cursor. Instead, it ends the current transaction and immediately starts another one. When you issue a COMMIT WORK, all locks associated with the cursor position are released. This means that another transaction may delete or modify the next tuple in the active set before you have the chance to FETCH it.
If another transaction deletes the current row, ALLBASE/SQL will return the next row. No error message is displayed. If another transaction deletes the table being accessed, the user will see the message TABLE NOT FOUND (DBERR 137). Figure 6-3 shows the e ect of KEEP CURSOR WITH NOLOCKS. Figure 6-3. Cursor Operation Using KEEP CURSOR WITH NOLOCKS KEEP CURSOR and BEGIN WORK ALLBASE/SQL automatically begins a transaction whenever you issue a command if a transaction is not already in progress.
KEEP CURSOR and COMMIT WORK When the KEEP CURSOR option of the OPEN command is activated for a cursor, COMMIT WORK may or may not release locks associated with the cursor depending on the setting of the WITH LOCKS/WITH NOLOCKS option. COMMIT WORK does not close cursors opened with the KEEP CURSOR option. COMMIT WORK does end the previous implicit transaction and starts an implicit transaction with the same isolation level as that speci ed with the BEGIN WORK command.
3. 4. 5. 6. statement. Create an SQLStatusCheck routine to display all error messages and RELEASE the DBEnvironment in the event of fatal errors. See the \Examples" section below. Use the COMMIT WORK command. If you do not COMMIT at this point, an aborted transaction will roll back all the OPEN statements, and you will lose the cursor positions. The COMMIT starts a new transaction and keeps the cursor positions. Use a loop to scan your data until all rows have been processed.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx /* First, Declare all your Kept and Non-Kept Cursors */ 7777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777 41 5 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Examples This code is intended as a guide; you will want to customize it for your speci c needs. The code illustrates status checking techniques with emphasis on deadlock detection. Four generalized code segments are presented: A status checking routine to be used in conjunction with the other code segments. Using a single kept cursor with locks. Using multiple cursors and cursor stability. Avoiding locks on terminal reads.
Common StatusCheck Procedure SQLStatusCheck () { /************************************************************/ /* Deadlock occurred: Set DeadLockFree to FALSE */ /************************************************************/ if (sqlca.sqlcode = -14024) { DeadLockFree = FALSE; /************************************************************/ /* If your program monopolizes CPU time by repeatedly */ /* reapplying a transaction, you could include a call */ /* to the XL PAUSE intrinsic at this point.
Single Cursor WITH LOCKS SQLSingleCursor () { /***************************************************************/ /* First, declare the cursor: */ /***************************************************************/ EXEC SQL DECLARE C1 CURSOR FOR SELECT PartName, FROM PurchDB.Parts WHERE SalesPrice > 500.
/*********************************************************/ /* Display qualified rows. SQLERRD[3] contains the */ /* number of qualified rows. */ /*********************************************************/ printf ("\n Part Name Sales Price\n"); for (i = 0; i < sqlca.sqlerrd[2]; i++) printf("%s %10.2f",PriceList[i].PartName,PriceList[i].
Multiple Cursors and Cursor Stability SQLMultiCursor () { /***************************************************************/ /* First, declare your cursors: */ /***************************************************************/ EXEC SQL DECLARE C1 CURSOR FOR SELECT BranchNo FROM Tellers WHERE TellerNo > 15000 FOR UPDATE OF Status; EXEC SQL DECLARE C2 CURSOR FOR SELECT BranchNo FROM Branches FOR UPDATE OF Credit; /***************************************************************/ /* Next, Open cursor C1.
/**********************************************************/ /* The following do while loop is executed once per */ /* deadlock. We FETCH again using Cursor C1, reopen */ /* Cursor C2, then start to fetch rows using C2. */ /* Note that there is a deadlock, and when the */ /* transaction is aborted, Cursor */ /* C2 is closed and Cursor C1 returns to the beginning */ /* of the transaction. Any work done by the current */ /* transaction on the database is undone.
if (EndofScan) { EndofScan = FALSE; EXEC SQL CLOSE C2; if (sqlca.sqlcode != 0) SQLStatusCheck(); else /* 4 */ { EXEC SQL UPDATE TELLERS SET Status = :NewStatus WHERE CURRENT OF C1; if (sqlca.sqlcode != 0) SQLStatusCheck(); else /* 5 */ { /**********************************************************/ /* Changes are committed and a new transaction begins. */ /* Cursor C1 still open; locks associated with the page */ /* pointed to by the cursor are kept.
Avoiding Locks on Terminal Reads SQLNoTermLock () { /***************************************************************/ /* First, declare the cursor: */ /***************************************************************/ EXEC SQL DECLARE C1 Cursor FOR SELECT PartNumber, SalesPrice FROM PurchDB.
/*********************************************************/ /* Verify that the value of PresentSalesPrice has not */ /* changed. If not, update with NewSalesPrice */ /*********************************************************/ do { DeadLockFree = TRUE; EXEC SQL SELECT SalesPrice INTO :SalesPrice FROM PurchDB.Parts WHERE PartNumber = :PartNumber; if (sqlca.sqlcode < 0) SQLStatusCheck(); if (sqlca.sqlcode = 100) printf("\n Part Number no longer in database. Not updated.
Sample Program Using Cursor Operations The ow chart in Figure 6-5 summarizes the functionality of program cex8. This program uses a cursor and the UPDATE WHERE CURRENT command to update column ReceivedQty in table PurchDB.OrderItems . The runtime dialog for cex8 appears in Figure 6-6, and the source code in Figure 6-7. The program rst executes function DeclareCursor 4 26 5, which contains the DECLARE CURSOR command 4 7 5. This command is a preprocessor directive and is not executed at run time.
Function DisplayUpdate then prompts whether the user wants to update the current ReceivedQty value 4 11 5. If so, the user is prompted for a new value. The value accepted is used in an UPDATE WHERE CURRENT command 4 12 5. If the user entered a zero, a null value is assigned to this column. The program then prompts whether to FETCH another row 4 13 5. If so, the FETCH command is re-executed.
Figure 6-5.
Program to UPDATE OrderItems Table via a CURSOR - cex8 Event List: CONNECT TO PartsDBE Prompt for Order Number BEGIN WORK OPEN CURSOR FETCH a row Display the retrieved row Prompt for new Received Quantity UPDATE row within OrderItems table FETCH the next row, if any, with the same Order Number Repeat the above five steps until there are no more rows CLOSE CURSOR End Transaction Repeat the above eleven steps until user enters 0 RELEASE the DBEnvironment Connect to PartsDBE Declare Cursor Enter OrderNumber or
Update the PurchDB.OrderItems table Do you want to see another row (Y/N)? > y Fetch the next row. Order Number: 30520 Item Number: 3 Vendor Part Number: 9135 Received Quantity: 3 Do you want to change ReceivedQty (Y/N)? > Do you want to see another row (Y/N)? > n y Fetch the next row.
/* Program cex8 */ /* /* /* /* /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of SQL's UPDATE WHERE CURRENT command using a cursor to update a single row at a time. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * typedef int boolean; boolean boolean boolean char int Abort; Done; DoFetch; response[2]; RowCounter; #include
int SQLStatusCheck() /* Function to Display Error Messages */ { 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) { ReleaseDBE(); } } /* End SQLStatusCheck Function */ boolean ConnectDBE() /* Function to Connect to PartsDBE */ 4 27 5 { boolean ConnectDBE; printf("\n Connect to PartsDBE"); EXEC SQL CONNECT TO 'PartsDBE'; 41 5 ConnectDBE = TRUE; if (sqlca.
boolean BeginTransaction() { boolean BeginTransaction; printf("\n"); printf("\n Begin Work"); /* Function to Begin Work */ EXEC SQL BEGIN WORK; 4 18 5 43 5 if (sqlca.sqlcode != OK) { SQLStatusCheck(); ReleaseDBE(); } } /* End BeginTransaction Function */ int EndTransaction() /* Function to Commit Work */ { printf("\n"); printf("\n Commit Work"); EXEC SQL COMMIT WORK; if (sqlca.
int DisplayRow() /* Function to Display Parts Table Rows */ { printf("\n"); printf("Order Number: %10d\n", OrderNumber); printf("Item Number: %10d\n", ItemNumber); printf("Vendor Part Number: %s\n", VendPartNumber); if (ReceivedQtyInd != 0) printf("Received Quantity: else printf("Received Quantity: 4 10 5 46 5 is NULL \n"); %5d\n", ReceivedQty); } /* End of DisplayRow */ int DeclareCursor() /* Function to Declare the Cursor */ { printf("\n"); printf("\n Declare the Cursor"); EXEC SQL DECLARE OrderReview
int CloseCursor() /* Function to Close the Declared Cursor */ { printf("\n"); printf("\n Close the Cursor"); EXEC SQL CLOSE OrderReview; if (sqlca.
if ((response[0] == 'N') || (response[0] == 'n')) { CloseCursor(); RollBack(); DoFetch = FALSE; } else { CloseCursor(); EndTransaction(); printf(RowCounter," %d\n rows were changed!"); DoFetch = FALSE; } } if (RowCounter == 0) { CloseCursor(); EndTransaction(); DoFetch = FALSE; } } 4 15 5 4 16 5 } /* End DisplayUpdate Function */ int FetchUpdate() /* Fetch a row to Update within OrderItems */ { 4 28 5 printf("\n"); printf("\n Enter an OrderNumber or a 0 to STOP > "); 4 17 5 scanf("%d",&OrderNumber); RowC
switch(sqlca.
main() /* Beginning of program */ { printf("\n Program to UPDATE the OrderItems table via a CURSOR - cex8"); printf("\n"); printf("\n Event list:"); printf("\n CONNECT TO PartsDBE"); printf("\n Prompt for an Order Number"); printf("\n BEGIN WORK"); printf("\n OPEN CURSOR"); printf("\n FETCH a row"); printf("\n Display the retrieved row"); printf("\n Prompt for new Received Quantity"); printf("\n UPDATE row within the OrderItems table"); printf("\n FETCH the next row, if any, with the same Order Number"); pr
7 BULK Table Processing BULK table processing is the programming technique you use to SELECT, FETCH, or INSERT multiple rows at a time . This chapter describes the following aspects of BULK processing: Variables Used in BULK Processing. SQL BULK Commands. Transaction Management for BULK Operations. Sample Program Using BULK Processing. Variables Used in BULK Processing Rows are retrieved into or inserted from host variables declared as an array of records.
You reference the name of the array in the BULK SQL command: EXEC SQL BEGIN DECLARE SECTION; struct { char PartNumber[17]; char PartName[31]; sqlind PartNameInd; } PartsArray[26]; double SalesPrice; EXEC SQL END DECLARE SECTION; . . . EXEC SQL BULK SELECT PartNumber, PartName INTO :PartsArray FROM PurchDB.
In the BULK SELECT example shown earlier, these two variables would be declared and referenced as follows: EXEC SQL BEGIN DECLARE SECTION; struct { char PartNumber[17]; char PartName[31]; sqlind PartNameInd; } PartsArray[26]; short int StartIndex ; short int NumberOfRows ; double SalesPrice; EXEC SQL END DECLARE SECTION; . . . EXEC SQL BULK SELECT PartNumber, PartName INTO :PartsArray, :StartIndex, :NumberOfRows FROM PurchDB.
SQL Bulk Commands The SQL commands used for BULK table processing are: BULK SELECT BULK FETCH BULK INSERT BULK SELECT The BULK SELECT command is useful when the maximum number of rows in the query result is known at programming time and when the query result is not too large. For example, this command might be used in an application that retrieves a query result containing a row for each month of the year.
. EXEC SQL BEGIN DECLARE SECTION; struct { short int CountCycle; int PartCount; } PartsPerCycle[15]; short int StartIndex; short int NumberOfRows; char LowBinNumber[16]; char HighBinNumber[16]; EXEC SQL END DECLARE SECTION; . . . int DisplayRows() { int i; for (i = 0; i < StartIndex; i++) { printf("CountCycle: %d\n", PartsPerCycle[i].CountCycle); printf("PartCount: %d\n", PartsPerCycle[i].PartCount); } /* end for */ } /* end of procedure DisplayRows */ . . .
printf("\n Enter a low bin number or 0 to STOP > "); scanf("%d",LowBinNumber); if (LowBinNumber != 0) { printf("\n Enter a high bin number > "); scanf("%d\n",HighBinNumber); EXEC SQL BULK SELECT CountCycle, COUNT(PartNumber) INTO :PartsPerCycle, :StartIndex, :NumberOfRows FROM PurchDB.
The variable MaximumRows is set to the number of records in the host variable array.
Main() { . . . MaximumRows = 25; . . . EXEC SQL BULK SELECT OrderNumber, VendorNumber INTO :OrdersArray FROM PurchDB.Orders; switch (sqlca.sqlcode) { case 0: if (sqlca.sqlerrd[2] == MaximumRows) { printf("\n There may be additional rows "); printf("\n that cannot be displayed."); } DisplayRows(); break; case 100: printf("\n No rows were found!"); break; default: if (sqlca.
BULK FETCH The BULK FETCH command is useful for reporting applications that operate on large query results or query results whose maximum size is unknown at programming time. The form of the BULK FETCH command is: BULK FETCH CursorName INTO ArrayName [,StartIndex [,NumberOfRows]] You use this command in conjunction with the following cursor commands: DECLARE CURSOR: de nes a cursor and associates with it a query.
short int DeliveryDays; sqlind DeliveryDaysInd; } SupplierBuffer[20]; EXEC SQL END DECLARE SECTION; typedef int boolean; boolean DoFetch; char Response[2]; #define TRUE 1 #define FALSE 0 . . EXEC SQL DECLARE SupplierInfo CURSOR FOR SELECT PartNumber, VendorName, DeliveryDays FROM PurchDB.Vendors, PurchDB.SupplyPrice WHERE PurchDB.Vendors.VendorNumber = PurchDB.SupplyPrice.
row fetched becomes the current row. When the last row in the active set has been fetched, ALLBASE/SQL sets sqlca.sqlcode to 100 the next time the BULK FETCH command is executed. BULK INSERT The BULK INSERT command is useful for multiple-row insert operations.
EXEC SQL BULK INSERT INTO PurchDB.Parts (PartNumber, PartName, SalesPrice) VALUES (:NewParts, :StartIndex, :NumberOfRows); . . } /* End of function BulkInsert */ int PartEntry() { . . The user is prompted for three column values, and the values are assigned to the appropriate record in the host variable array; then the array row counter (NumberOfRows) is incremented and the user asked whether s/he wants to specify another line item. . .
Transaction Management for BULK Operations Bulk processing, by using only one ALLBASE/SQL command to operate on multiple rows, provides a way of minimizing the time page or table locks are held. Locks are only held while moving rows between database tables and an array de ned by the program, and operations can be done while holding data in that array without holding locks against the database.
Sample Program Using BULK Processing The ow chart in Figure 7-1 summarizes the functionality of program cex9. This program creates orders in the sample DBEnvironment, PartsDBE. Each order is placed with a speci c vendor, to obtain one or more parts supplied by that vendor. An order consists of a row in table PurchDB.
so that when control returns to function CreateOrder , the user is again prompted for a vendor number. If the SELECT command fails, function SQLStatusCheck is invoked 4 13 5 to display any error messages 4 4 5. Then the COMMIT WORK command is executed, and the appropriate ags set to FALSE. If the vendor number is valid, program cex9 invokes function CreateHeader to create the order header 4 50 5.
Function ValidatePart starts a transaction 4 14 5. Then it executes a SELECT command 4 15 5 to determine whether the part number entered matches any part number known to be supplied by the vendor. If the part number is valid, the COMMIT WORK command is executed 4 16 5 and the PartOK ag set to TRUE.
Figure 7-1.
Program to Create an Order - cex9 Event List: CONNECT TO PartsDBE Prompt for VendorNumber Validate VendorNumber BEGIN WORK INSERT a row into PurchDB.Orders Prompt for a line item Validate the Vendor Part Number for each line item BULK INSERT rows into PurchDB.OrderItems Repeat the above six steps until the user enters a 0 RELEASE the DBEnvironment Connect to PartsDBE Enter a Vendor Number or a 0 to STOP> 9015 Begin Work Validating VendorNumber Commit Work Begin Work LOCK the PurchDB.Orders table.
Order Quantity > 5 Item Due Date (yyyymmdd) > 19860630 Do you want to specify another line item (Y/N)? > y You can specify as many as 25 line items. Enter data for ItemNumber: 2 Vendor Part Number > 9055 Begin Work Validating VendPartNumber Commit Work The vendor has no part with the number you specified! Do you want to specify another line item (Y/N)? > y You can specify as many as 25 line items.
The following order has been created: Order Number: 30524 Vendor Number: 9015 Order Date: 19860603 Item Number: Vendor Part Number: Purchase Price: Order Quantity: Item Due Date: Received Quantity: 1 9040 1500.00 5 19860630 is NULL Item Number: Vendor Part Number: Purchase Price: Order Quantity: Item Due Date: Received Quantity: 2 9050 345.00 2 19860801 is NULL Enter a Vendor Number or a 0 to STOP > 0 Release PartsDBE Figure 7-2.
/* Program cex9 */ /* /* /* /* * * * * * * * * * * * * * * * * * * * * * This program illustrates the use of SQL's command to insert mulitple rows or tuples * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */ BULK INSERT */ at a time.
struct { int OrderNumber2; int ItemNumber; char VendPartNumber[17]; double PurchasePrice; short OrderQty; char ItemDueDate[9]; short ReceivedQty; sqlind ReceivedQtyInd; } OrderItems[25]; char SQLMessage[133]; EXEC SQL END DECLARE SECTION; /* End Host Variable Declarations */ int SystemDate() /* Function to get the system date */ { 41 5 4 32 5 sec = time(0); printf("\n Calculating OrderDate"); ptr = localtime(&sec); sprintf(OrderDate, "%2s%2.2d%2.2d%2.
boolean ConnectDBE() /* Function to Connect to PartsDBE */ 4 54 5 { boolean ConnectDBE; printf("\n Connect to PartsDBE"); EXEC SQL CONNECT TO 'PartsDBE'; 45 5 ConnectDBE = TRUE; if (sqlca.sqlcode != OK) { ConnectDBE = FALSE; SQLStatusCheck(); } /* End if */ return (ConnectDBE); } /* End of ConnectDBE Function */ int ReleaseDBE() /* Function to Release PartsDBE */ { printf("\n Release PartsDBE"); EXEC SQL RELEASE; printf("\n"); 4 56 5 43 5 Done = TRUE; if (sqlca.
int EndTransaction() /* Function to Commit Work */ { printf("\n"); printf("\n Commit Work"); EXEC SQL COMMIT WORK; if (sqlca.sqlcode != OK) { SQLStatusCheck(); } 4 11 5 47 5 } /* End EndTransaction Function */ int RollBackWork() /* Function to RollBack work */ { printf("\n"); printf("\n RollBack Work"); EXEC SQL ROLLBACK WORK; if (sqlca.
default: } /* End switch */ SQLStatusCheck(); EndTransaction(); VendorOK = FALSE; HeaderOK = FALSE; ItemsOK = FALSE; break; 4 13 5 } /* End ValidateVendor Function */ \mf="Function" int ValidatePart() /* Function that ensures vendor part number is valid */ { 4 39 5 BeginTransaction(); 4 14 5 sscanf(OrderItems[i].VendPartNumber,"%s",PartSpecified); printf("\n Validating VendPartNumber"); EXEC SQL SELECT VendPartNumber INTO :PartSpecified FROM PurchDB.
default: SQLStatusCheck(); EndTransaction(); PartOK = FALSE; break; } /* End switch */ } /* End ValidatePart Function */ int DisplayHeader() /* Function to Display row from PurchDB.Orders */ { 4 45 5 printf("\n"); printf("\n The following order has been created.
int InsertRow() /* Function to insert row in PurchDB.Orders */ { printf("\n"); printf("\n INSERT into PurchDB.Orders"); EXEC SQL INSERT INTO PurchDB.Orders (OrderNumber, VendorNumber, OrderDate) VALUES (:OrderNumber1, :VendorNumber, :OrderDate); if (sqlca.sqlcode != 0) { SQLStatusCheck(); EndTransaction(); HeaderOK = FALSE; } else { EndTransaction(); HeaderOK = TRUE; } 4 33 5 4 22 5 4 23 5 4 24 5 } /* End of InsertRow Function */ int BulkInsert() /* Function to bulk insert into PurchDB.
if (sqlca.sqlcode != 0) { SQLStatusCheck(); RollBackWork(); ItemsOK = FALSE; } else { EndTransaction(); ItemsOK = TRUE; } 4 28 5 4 29 5 } /* End of BulkInsert Function */ int ComputeOrderNumber() /* Function to assign a number to an order */ { 4 36 5 EXEC SQL SELECT MAX(OrderNumber) 4 30 5 INTO :MaxOrderNumber FROM PurchDB.Orders; if (sqlca.
else ComputeOrderNumber(); 4 36 5 } /* End CreateHeader Function */ int ItemEntry() /* Function to put line items into OrderItems array */ { 4 46 5 i = counter1; OrderItems[i].OrderNumber2 = OrderNumber1; 4 37 5 OrderItems[i].ItemNumber = i; printf("\n"); printf("\n You can specify as many as 25 line items."); printf("\n"); printf("\n Enter data for ItemNumber: %d\n", OrderItems[i].ItemNumber); printf("\n"); printf("\n Vendor Part Number > "); 4 38 5 scanf("%s%*c",OrderItems[i].
int CreateOrderItems() /* Function to create line items */ { ItemsOK = FALSE; printf("\n"); printf("\n Do you want to specify line items (Y/N)? > "); scanf("%s%*c",response); if ((response[0] == 'N') || (response[0] == 'n')) { DoneItems = TRUE; DisplayHeader(); } else { counter1 = 1; do { ItemEntry(); } while (DoneItems == FALSE); if (counter1 != 1) { BulkInsert(); ItemsOK = TRUE; } } } /* End of CreateOrderItems Function */ 4 51 5 int CreateOrder() /* Function to create an order */ { printf("\n"); printf
main() /* Beginning of program */ { printf("\n Program to CREATE an Order - cex9"); printf("\n"); printf("\n Event list:"); printf("\n CONNECT TO PartsDBE"); printf("\n Prompt for a Vendor Number"); printf("\n Validate the Vendor Number"); printf("\n BEGIN WORK"); printf("\n INSERT a row into PurchDB.Orders"); printf("\n Prompt for a line item"); printf("\n Validate the Vendor Part Number for each line item"); printf("\n BULK INSERT rows into PurchDB.
8 Using Dynamic Operations Dynamic operations are used to execute SQL commands that are not preprocessed until run time. Such commands, known as dynamic SQL commands, are submitted to ALLBASE/SQL through several special SQL statements: PREPARE, DESCRIBE, EXECUTE, and EXECUTE IMMEDIATE. This chapter contrasts dynamic with non-dynamic operations and introduces the techniques used to handle dynamic operations from a program. It then focuses on dynamic non-queries and queries.
BEGIN DECLARE SECTION CLOSE CURSOR DECLARE CURSOR DELETE WHERE CURRENT DESCRIBE END DECLARE SECTION EXECUTE EXECUTE IMMEDIATE FETCH INCLUDE OPEN CURSOR PREPARE SQLEXPLAIN UPDATE WHERE CURRENT WHENEVER Dynamic commands that are not queries can be preprocessed at run time using the PREPARE and EXECUTE statements or the EXECUTE IMMEDIATE statement. Dynamic queries are preprocessed using the PREPARE and DESCRIBE commands in conjunction with the SQLDA or SQL Description Area and other data structures.
Figure 8-1. Creation and Use of a Program that has a Stored Module Programs that contain only SQL commands that do not have permanently stored sections can be executed against any DBEnvironment without the prerequisite of storing a module in the DBEnvironment. Figure 8-2 illustrates how you create and use programs in this category. Note that the program must still be preprocessed, in order to create compilable les and generate ALLBASE/SQL external procedure calls.
Figure 8-2. Creation and Use of a Program that has no Stored Module Examples of Non-Dynamic and Dynamic SQL Statements The following example shows an embedded SQL statement that is coded so as to generate a stored section before run time: EXEC SQL UPDATE STATISTICS FOR TABLE PurchDB.Parts; When you run the preprocessor on a source le containing this statement, a permanent section will be stored in the appropriate DBEnvironment.
In this case, the SQL statement is stored in a host variable which is passed to ALLBASE/SQL in the PREPARE statement at run time. A temporary section is then created and executed, and the section is not stored in the DBEnvironment. Why Use Dynamic Preprocessing? In some cases, it may not be desirable to preprocess an SQL command before run time: You may need to code an application that permits ad hoc queries requiring that SQL commands be entered by the user at run time.
To pass a dynamic command that cannot be completely de ned at programming time, you use a host variable declared as an array of char: char DynamicHostVar[2048]; . . EXECUTE IMMEDIATE :DynamicHostVar Understanding the Types of Dynamic Operations Dynamic operations in ALLBASE/SQL are of two major types: Dynamic Non-Queries: dynamic operations that do not retrieve rows from the database. Note that dynamic non-queries either do or do not require the use of sections at execution time.
table name and re-executing the UPDATE STATISTICS command releases any locks obtained and improves concurrency. If you do not know in advance whether a dynamic command will be a query or a non-query, you must use the PREPARE command to dynamically preprocess the command, the DESCRIBE command to distinguish between queries and non-queries, and the EXECUTE or EXECUTE IMMEDIATE command to execute the dynamic non-query.
Using PREPARE and EXECUTE Use the PREPARE command to create and store a temporary section for the dynamic command: PREPARE CommandName FROM CommandSource Because the PREPARE command operates only on sections, it can be used to dynamically preprocess only SQL commands executed by using sections. The DBE session management and transaction management commands can only be dynamically preprocessed by using EXECUTE IMMEDIATE.
Figure 8-4. Dynamic Query Data Structures and Data Assignment Though some speci c details di er depending on the query type, in general you handle all types of dynamic query as follows: De ne a host variable (or a string) to hold the SELECT statement to be used by the PREPARE command. The PREPARE command dynamically preprocesses the query.
bu er identi ed in the SQLDA. The SQLDA, the format array, and the data bu er are discussed later in this section under \Using the Dynamic Query Data Structures." Although you can fetch multiple rows with each execution of the FETCH command, you do not specify the BULK option when fetching rows that qualify for dynamic queries. Instead, you set a eld in the SQLDA as shown later in this chapter to communicate to ALLBASE/SQL how many rows to fetch.
Your error checking strategy might include routines to parse user input for an acceptable SELECT statement and/or routines to test speci c sqlca eld values and invoke SQLEXPLAIN. This error checking strategy may need to be modi ed, if the syntax of the SELECT statement has changed for a particular ALLBASE/SQL release.
Table 8-1.
Setting Up the Format Array You declare the format array as an array of records having the type SqlFormat Type: sqlformat_type sqlfmts[NbrFmtRecords]; You set the number of records in the format array (NbrFmtRecords in this example) to the largest number of select list items you expect.
Table 8-2. Fields in a Format Array Record FIELD NAME MEANING OF FIELD C DATA TYPE sqlnty reserved; always set to 110 short sqltype data type of column: short 0 1 2 3 4 5 8 9 10 11 12 13 14 = = = = = = = = = = = = = SMALLINT or INTEGER BINARY* CHAR* VARCHAR* FLOAT DECIMAL NATIVE CHAR * NATIVE VARCHAR * DATE* TIME* DATETIME* INTERVAL* VARBINARY* * Native CHAR or VARCHAR is what SQLCore uses internally when a CHAR or VARCHAR column is de ned with a LANG = ColumnLanguageName clause.
Setting Up the Data Buffer You use di erent approaches to setting up the data bu er depending on whether your dynamic query result has an unknown format or a known format. If the query result has an unknown format, you may not know the number of columns or their data types. If the query result has a known format , you know in advance the number of columns in the query result and the data type of each column.
struct { int strlen_col1; /* string length of column1 char column1[20]; short column2; int column3; short column3Ind; /* indicator variable */ } databuffer[MaxDataBuff]; */ When a column contains a VARCHAR data type, a 4 byte integer must be declared immediately before the variable for that column to hold the string length. The application itself needs to properly place the string terminator, ASCII 0, into the string.
#define NbrFmtRecords 1024 /* Columns expected */ . . sqlformat_type sqlfmts[NbrFmtRecords]; /* sqlfmts is the format array */ Use a host variable for the SELECT command, and pass it to ALLBASE/SQL in the PREPARE command: EXEC SQL BEGIN DECLARE SECTION; char DynamicCommand[2048]; EXEC SQL END DECLARE SECTION; . . . EXEC SQL PREPARE Cmd1 FROM :DynamicCommand; Initialize two sqlda elds, sqln and sqlfmtarr. sqln is set to the size of the format array, and sqlfmtarr is set to its address. . sqlda.
Execute the FETCH command. ALLBASE/SQL packs the data bu er with as many rows from the active set as you speci ed in SQLDA.SqlNRow. ALLBASE/SQL puts the rst select list value into the data bu er, starting at the rst byte of the format array and including any VARCHAR pre xes, ALLBASE/SQL null indicators for columns that can contain null values, and any alignment bytes provided by the C compiler. Then ALLBASE/SQL writes the second through last select list values for the rst row.
Note that the number of rows to retrieve with each execution of the FETCH command is speci ed in SQLDA.SqlNRow. As shown in the above example, you can calculate the number of rows that will t into the data bu er by dividing the row length (in bytes) into the number of bytes in the data bu er. Sqlrowlen, one of the SQLDA elds set by ALLBASE/SQL when you execute the DESCRIBE command, contains the number of bytes in each row. do { EXEC SQL FETCH Cursor1 USING DESCRIPTOR sqlda; DisplayRow(); } while (sqlca.
Figure 8-6. Parsing the Data Buffer in cex10a Program cex10a uses the following assignment to set the start of a row: CurrentOffset = CurrentOffset + SqlRowLen; To nd a null indicator, the program uses the following assignment: NullIndOffset = CurrentOffset + sqlfmts[i].SqlNOf; To move a data value into a variant record, cex10a uses the following statement: StrMove(sqlfmts[1].(SqlValLen, DataBuffer, CurrentOffset + sqlfmts[i].SqlVOf, OneColumn.
Preprocessing of Commands That May or May Not Be Queries You need special techniques to handle dynamic commands which may be either queries or non-queries. In a program that accepts both query and non-query SQL commands, you rst PREPARE the command, then use the DESCRIBE command in conjunction with the sqlda , the data structure that lets you identify whether a command is a query.
EXEC SQL BEGIN DECLARE SECTION; char DynamicCommand[2048]; EXEC SQL END DECLARE SECTION; char DynamicClause[80]; short int Pos; . . . .
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.
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 4 31 5. Declares and opens a cursor for the dynamic query 4 32 5. Initializes the three sqlda elds that must be set before executing the FETCH command 4 33 5: sqlda.sqlbu en (the size of the data bu er), sqlda.sqlnrow (the number of rows to put into the data bu er with each FETCH), and sqlda.sqlrowbuf (the address of the data bu er). Note that to set sqlda.
types. The record variation used depends on the value of sqlfmts[i].sqltype 4 19 5, the format array record eld describing the data type of a select list item. In the case of DECIMAL data, a function named BCDToString 4 2 5 converts the binary coded decimal (BCD) information in the data bu er into ASCII format for display purposes. After each value in a row is displayed, CurrentO set is incremented by sqlda.sqlrowlen 4 20 5 to point to the beginning of the next row.
Figure 8-7.
Figure 8-7.
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.
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.
/* Program cex10a */ /* /* /* /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This program illustrates dynamic preprocessing of SQL commands including SELECT commands using the DESCRIBE command. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */ */ */ */ #include #include typedef int boolean; #define #define #define NotFound OK DeadLock 100 0 -14024 /* NbrFmtRecords is number of columns expected in a dynamic SELECT.
/* DataBuffer is the buffer containing retrieved data as a result */ /* of a dynamic SELECT.
int BCDToString(DataBuffer, Length, Precision, Scale, Result0) 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 int int int int boolean char char 42 5 i; DecimalPlace; PutPos=0; DataEnd; DataStart; done; space[MaxStr]; *Result; Result = space; DataEnd = (Length*2) - 2; DataStart = (DataEnd - Precision); for (i
/* 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 */ 43 5 Abort = FALSE;
Connect = TRUE; if (sqlca.sqlcode != OK) { Connect = FALSE; SQLStatusCheck(); } /* End if */ return(Connect); } /* End of ConnectDBE Function */ int ReleaseDBE() /* Function to Release PartsDBE */ { printf("\nRelease PartsDBE"); EXEC SQL RELEASE; if (sqlca.sqlcode != OK) SQLStatusCheck(); 45 5 } /* End ReleaseDBE Function */ boolean BeginTransaction() { boolean BeginTransaction; printf("\n"); printf("\nBegin Work"); EXEC SQL BEGIN WORK; if (sqlca.
/* Function DisplaySelect deblocks the result of the dynamic */ /* SELECT in "DataBuffer". */ int DisplaySelect() { typedef union gt { char char int short double float } GenericType; short short GenericType char boolean short 4 36 5 49 5 CharData[MaxColSize]; VarCharData[MaxColSize]; IntegerData; SmallIntData; FloatData; DecimalData; CurrentOffset; NullIndOffset; OneColumn; DecString[20]; IsNull; n,i,j,x; /* local loop counters */ CurrentOffset = 0; 4 10 5 for (x = 0; x < sqlda.
} /* End if sqlfmts[i].sqlindlen > 0 .. */ if (IsNull) { printf(" Column is NULL |"); } else { 4 17 5 /* Now bring down the actual value of this column. */ StrMove(sqlfmts[i].sqlvallen,DataBuffer, 4 18 5 CurrentOffset + sqlfmts[i].sqlvof, OneColumn.CharData, 0); 4 30 5 switch (sqlfmts[i].sqltype) { 4 19 5 case 0: /* Integer number */ switch (sqlfmts[i].sqlvallen) { case 2: printf("%d | ",OneColumn.SmallIntData); break; case 4: printf("%d | ",OneColumn.
} /* End for i/with sqlfmts[i] ... */ CurrentOffset = CurrentOffset + sqlda.sqlrowlen; 4 20 5 printf("\n"); } /* End for n = ... */ printf("\n"); } /* End of DisplaySelect function */ int GetCommand() { char DynamicClause[80]; short i; 4 25 5 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.
int Describe() { /* Describe Function */ /* set up SQLDA fields */ sqlda.sqln = NbrFmtRecords; sqlda.sqlfmtarr = sqlfmts; 4 23 5 4 24 5 /* number of columns expected */ do { GetCommand(); if (DynamicCommand[0] != '/') { 4 25 5 /* @001 */ if (BeginTransaction()) { 46 5 printf("\nPrepare"); printf("%s\n",DynamicCommand); EXEC SQL PREPARE CMD1 FROM :DynamicCommand; if (sqlca.
} /* End if DynamicCommand */ } while (DynamicCommand[0] != '/'); /* End do */ /* @001 */ } /* End of Describe function */ int NonQuery() { 4 29 5 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.
if (sqlca.sqlcode == EndOF) { printf("\nRow not found or no more rows!"); } else SQLStatusCheck(); } else DisplaySelect(); } 4 36 5 /* End of while sqlca.sqlcode = 0 */ EXEC SQL CLOSE CURSOR1; if (sqlca.
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 bu er.
The program then declares and opens a cursor named TableList for the dynamic query 4 18 5. Before using the cursor to retrieve rows, the program initializes several sqlda elds 4 19 5 as follows: The sqlda.sqlnrow eld is set to 300 , as de ned in the constant MaxNbrTables 4 1 5. This number is the maximum number of rows ALLBASE/SQL will return from the active set when the FETCH command is executed. The sqlda.sqlbu en eld is set to the size of the data bu er.
The FETCH command 4 32 5 is executed only once for each table that quali ed for the rst 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 bu er ColumnList , a CREATE TABLE command for the table is written to the schema le 4 34 5: CREATE LockMode TABLE OwnerName.TableName, (ColumnList[1].ColName TypeInfo NullInfo, ColumnList[2].ColName TypeInfo NullInfo, . . . ColumnList[j].
Figure 8-10.
Flow Chart of Program cex10b (page 2 of 2) Using Dynamic Operations 8-45
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 Generating SQL Generating SQL Generating SQL Generating SQL Generating SQL Generating SQL :PRINT SCHM1 command command command command command command command to to to to to to to CREATE CREATE CREATE CREATE CREATE CREATE CREATE TABLE TABLE TABL
/* 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 #include
struct { char ColName[20]; int Length; short TypeCode; short Nulls; short Precision; short Scale; } ColumnList[MaxNbrColumns]; 45 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 Enter database owner name or ALL for all owners > "); scanf("%s",OwnerName); for (i = 0; i <= strlen(OwnerName); i++) { if (islower(OwnerName[i])) { OwnerName[i] = toupper(OwnerName[i]); } } 4 11 5 /* Upshift OwnerName */ 4 12 5 response = "ALL"; if (strcmp(response,OwnerName) == 0) { sprintf(CmdLine,"SELECT OWNER,NAME,DBEFILESET,RTYPE FROM SYSTEM.TABLE\ WHERE TYPE = 0 AND OWNER <> 'SYSTEM';"); 4 13 5 } else { sprintf(CmdLine,"SELECT OWNER,NAME,DBEFILESET,RTYPE FROM SYSTEM.
EXEC SQL DECLARE TableList CURSOR for SelectCmd1; if (sqlca.sqlcode != OK) { printf("\n Problem declaring TableList cursor!"); EXEC SQL SQLEXPLAIN :SQLMessage; printf("%s\n",SQLMessage); goto a9999; } 4 18 5 EXEC SQL OPEN TableList; if (sqlca.sqlcode != OK) { printf("\n Problem opening TableList cursor!"); EXEC SQL SQLEXPLAIN :SQLMessage; printf("%s\n",SQLMessage); goto a9999; } 4 18 5 /* set up SQLDA fields */ sqlda.sqlnrow = MaxNbrTables; sqlda.sqlbuflen = sizeof(TableList); sqlda.
sscanf(TableList[i].Owner,"%s",OwnerName); sscanf(TableList[i].Table,"%s",TableName); sscanf(TableList[i].FileSet,"%s",DBEFileSet); 4 24 5 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); EXEC SQL PREPARE SelectCmd2 FROM :CmdLine; if (sqlca.
/* set up SQLDA sqlda.sqlnrow sqlda.sqlbuflen sqlda.sqlrowbuf fields */ = MaxNbrColumns; = sizeof(ColumnList); = ColumnList; 4 31 5 /* Get Column List from SYSTEM.COLUMN */ EXEC SQL FETCH ColumnList USING DESCRIPTOR SQLDA; 4 32 5 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].
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) { sprintf(OneLine+strlen(OneLine)," NOT NULL"); } 4 36 5 if (j != sqlca.
9 Programming with Constraints This chapter explains the use of statement level integrity versus row level integrity. Also, methods of implementing schema level unique and referential integrity contraints in your database are highlighted. Integrity constraints allow you to have ALLBASE/SQL verify data integrity at the schema level. Thus you can avoid coding complex veri cation routines in application programs and avoid the increased execution time of additional queries.
Using Unique and Referential Integrity Constraints Any database containing tables with interdependent data is a good candidate for the use of integrity constraints. You can pro t from their use whether your data is volatile or stable in nature. For instance, your database might contain a table of employee and department data that is constantly changing, or it could contain a table of part number data that rarely changes even though it is frequently accessed.
Table 9-2. Constraint Test Matrix DML Operations UNIQUE or PRIMARY KEY Referenced Table Referencing Table [BULK] INSERT Must be unique in or Type 2 INSERT the table. UPDATE [WHERE CURRENT] DELETE [WHERE CURRENT] Must match a unique key in the referenced table. Must be unique in No foreign key can reference the unique key being the table. updated. Must match a unique key in the referenced table. No foreign key can reference the unique key being deleted.
Figure 9-1. Constraints Enforced on the Recreation Database Suppose you designed an application program providing a user interface to the recreation database. The interface gives choices for inserting, updating, and deleting data in any of the three tables. Your application is user friendly and guides the user with informational messages when their request is denied because it would violate data integrity.
Insert a Member in the Recreation Database The user chooses to insert a new member in the database. For this activity to complete, the foreign key (Club) which is being inserted into the Members table must exist in the primary key (ClubName) of the Clubs table. Execute subroutines to display and prompt for information needed in the Members table. Place user entered information in appropriate host variables. INSERT INTO RecDB.
Update an Event in the Recreation Database The user now wants to update information in the Events table. For this activity to complete, the SponsorClub and Coordinator being updated in the Events table must exist in the primary key composed of MemberName and Club in the Members table. Execute subroutines to display and prompt for information needed in the Events table. Place user entered information in appropriate host variables. UPDATE RecDB.
Delete a Club in the Recreation Database The user chooses to delete a club. For this activity to complete, no foreign key must reference the primary key (ClubName) that is being deleted. Execute subroutines to display and prompt for a ClubName in the Clubs table. Place user entered information in appropriate host variables. DELETE FROM RecDB.Clubs WHERE ClubName = :ClubName Check the sqlcode eld of the sqlca.
10 Programming with LONG Columns LONG columns in ALLBASE/SQL enable you to store a very large amount of binary data in your database, referencing that data via a table column name. You might use LONG columns to store text les, software application code, voice data, graphics data, facsimile data, or test vectors. You can easily SELECT or FETCH this data, and you have the advantages of ALLBASE/SQL's recoverability, concurrency control, locking strategies, and indexes on related columns.
General Concepts ALLBASE/SQL stores LONG column data in a database for later retrieval. LONG column data is not processed by ALLBASE/SQL. Any formatting, viewing, or other processing must be accomplished by means of your program. For example, you might use a graphics application to create an intricate graphic display (or set of graphic displays). You could then write a program in which you embed ALLBASE/SQL commands to store each graphics le in your database along with related data in a given row.
Figure 10-1. Flow of LONG Column Data and Related Information to the Database Figure 10-2.
Restrictions A LONG column can be referenced in a select list and/or a host variable declaration. Some restrictions do apply to LONG columns. However, related standard columns are not a ected by these restrictions. LONG columns cannot be used as follows: In a WHERE clause. In a type 2 INSERT command. Remotely through ALLBASE/NET. As hash or B-tree index key columns. In a GROUP BY, ORDER BY, DISTINCT, or UNION clause. In an expression. In a subquery. In aggregate functions (AVG, SUM, MIN, MAX).
The next command speci es that data for new LONG column, PartModule, be stored in PartPictureSet. ALTER TABLE PartsTable ADD PartModule LONG VARBINARY(70000) IN PartPictureSet NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN See the \BINARY Data" section of the \Host Variables" chapter for more information on using BINARY and VARBINARY data types in long columns.
Putting Data into a LONG Column with a [BULK] INSERT Command As with any column, use the INSERT command to initially put data into a LONG column. At the time of the insert, all input devices must be on the system in the locations you have speci ed. Should your insert operation fail, nothing is inserted, a relevant error message is returned to the program, and the transaction continues.
Example /* /* /* /* This code segment reads a data file into a host variable array, one line at a time. It parses the buffer and displays each record as it's read and loaded. Then a BULK INSERT to an ALLBASE/SQL database table is performed. Maximum number of records per BULK INSERT is 25. */ */ */ */ #include #define MAXSIZE 25 . . . /* PartNum is used to read in the PartNumber as a string.
int InsertRows() { /* function to insert rows in PartsTable */ StartIndex = 1; NumberOfRows = counter1; EXEC SQL BULK INSERT INTO PartsTable (PartName, PartNumber, PartPicture, PartModule) VALUES (:PartsTableRows, :StartIndex, :NumberOfRows); if (sqlca.sqlcode != 0) { SQLStatusCheck(); } } /* End of InsertRows Function */ /* Here you could accept data from the user or from a file. /* For this example, a file is used.
/* We read PartNumber as a string, but assign it as an integer. /* atoi converts the ascii to integer. PartsTableRows[i].PartNumber = atoi(PartNum); } } */ */ printf ("%10s %2d %2d %2d %30s %2d %30s %2d\n", PartsTableRows[i].PartName, PartsTableRows[i].PartNameInd, PartsTableRows[i].PartNumber, PartsTableRows[i].PartNumberInd, PartsTableRows[i].PartPictureIO, PartsTableRows[i].PartPictureInd, PartsTableRows[i].PartModuleIO, PartsTableRows[i].PartModuleInd); i++; /* end of initialize function */ . . .
Retrieving LONG Column Data with a [BULK] SELECT, FETCH, or REFETCH Command The following syntax represents the available subset when your select list includes one or more LONG columns. Remember, a LONG column can be referenced only in a select list and/or a host variable declaration. 8 * > > <2 3 9 > > =2 3 Owner. Table.* , ... CorrelationName.* > > > > ; : CorrelationName.ColumnName 39 2 3 3 82 2 Owner. FromTableName CorrelationName INTO HostVariableDeclaration FROM 2 3 , ...
Table 10-2.
Using LONG Columns with a BULK SELECT Command When you use the BULK SELECT command with LONG columns, should an error occur before completion of the BULK SELECT command, any operating system les written before the error occurred remain on the system, and LONG column descriptors written to a host variable array remain in the array. It is your responsibility to remove such les as appropriate.
Removing LONG Column Data with a DELETE [WHERE CURRENT] Command Syntax for the DELETE and DELETE WHERE CURRENT commands is unchanged for use with LONG columns. It is limited for the DELETE command in that a LONG column cannot be used in the WHERE clause. In the following example, any rows in PartsTable with the PartName of hammer are deleted. DELETE FROM PartsTable WHERE PartName = 'hammer' When LONG column data is deleted, the space it occupied in the DBEnvironment is released when your transaction ends.
Considering Multiple Users With multiple users reading the same LONG column data, it is preferable for each user to run the application in a local area. This can prevent le access problems. If several users must access the same data from the same group, you might want to use the wildcard option ($) and avoid using the overwrite option (!). Deciding How Much Space to Allocate and Where Remember to consider the space requirements of any DBEFileSet used for LONG column data.
11 Programming with ALLBASE/SQL Functions Seven functions can be used with date/time data types. These functions provide exibility for inputting and retrieving date/time data from the database. These functions can be used with a preprocessed application or with ISQL. This chapter outlines basic principles for using date/time functions in an application program. The following sections are included: Where Date/Time Functions Can Be Used. De ning and Using Host Variables with Date/Time Functions.
Where Date/Time Functions Can Be Used Use date/time functions, as you would an expression, in the DML operations listed below: Table 11-1.
As for host variables containing input and output data, de ne them to be CHAR or VARCHAR compatible with one exception. The TO INTEGER function requires an INTEGER compatible host variable for its output. Reference the chapter on de ning host variables for additional information about de ning a host variable to be compatible with a speci c ALLBASE/SQL data type. Note that the declarations relate to the default format speci cation for each date/time data type.
Examples of TO DATETIME, TO DATE, TO TIME, and TO INTERVAL Functions Imagine a situation in which users will be inputting and retrieving date/time data in formats other than the default formats. (Refer to the ALLBASE/SQL Reference Manual for default format speci cations.) The data is located in the TestData table in the manufacturing database. (Reference appendix C in the ALLBASE/SQL Reference Manual .
INSERT INTO MANUFDB.
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN (:BatchStamp, :BatchStamp-Format) 11-6 Programming with ALLBASE/SQL Functions
Example Using the SELECT Command The users are planning to select data from the TestData table based on the lab time interval between the start and end of a given set of tests. BEGIN DECLARE SECTION Declare input host variables (:BatchStamp, :BatchStamp-Format, LabTime, and LabTime-Format) to be compatible with data type CHAR or VARCHAR. END DECLARE SECTION . . .
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN WHERE BatchStamp = TO_DATETIME (:BatchStamp, :BatchStamp-Format) Using Date/Time Output Functions Specify the output format of any type of date/time column by using a date/time output function. Use an output function with any DML operation listed in Table 11-2 with one exception.
:TestEnd :TestEndInd, FROM ManufDB.TestData WHERE TO_DATETIME(:BatchStamp, :ItalianFormat) = :SpecifiedInput NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN Note the use of indicator variables in the above example. Because the TO CHAR function is used in the select list, no need exists to specify an indicator variable as part of the function.
Example TO INTEGER Function The TO INTEGER format speci cation is mandatory and di ers from that of other date/time functions in that it must consist of a single element only. See the ALLBASE/SQL Reference Manual for detailed format speci cations. Perhaps you are writing a management report that indicates the quarter of the year in which tests were performed. (As in the previous example, data is located in the TestData table in the manufacturing database.
Using the Date/Time ADD MONTHS Function This function allows you to add an integer number of months to a DATE or DATETIME column. Do so by indicating the number of months as a positive, negative, or unsigned integer value. (An unsigned value is assumed positive.) Also, you can specify the integer in a host variable of type INTEGER. The ADD MONTHS function can be used in both input and output operations as shown in Table 11-1.
When using the ADD MONTHS function, if the addition of a number of months (positive or negative) would result in an invalid day, the day eld is set to the last day of the month for the appropriate year, and a warning is generated indicating the adjustment. Program Example for Date/Time Data The example program shown in Figure 11-1 is based on the manufacturing database and the purchasing database that are a part of the sample database environment, PartsDBE.
Example Program cex9a /* Program cex9a */ /** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */ /*This program uses BULK FETCH and BULK INSERT commands to select all rows*/ /*from the Orders table (part of the sample DBEnvironment, PartsDBE), */ /*convert the order date column from the CHAR data type to the DATE data */ /*type default format, and write all Orders table information to another */ /*table called NewOrders table (created previously by you as described in */ /*this chapte
struct { int NewOrderNumber; int NewVendorNumber; sqlind NewVendorNumInd; char NewOrderDate[11]; /*Add a byte for end of char array.*/ sqlind NewOrderDateInd; } NewOrders[25]; short StartIndex2; short NumberOfRows2; char SQLMessage[133]; /*Add a byte for end of char array.
/*************************************************************************/ /* Beginning of program. */ /*************************************************************************/ main() { printf("Program to convert date from CHAR to DATE data type.\n"); printf("Event List:\n"); printf(" Connect to PartsDBE\n"); printf(" BULK FETCH all rows from Orders Table.\n"); printf(" Convert the date.\n"); printf(" BULK INSERT all fetched rows into NewOrders Table \n"); printf(" with converted date.
/*************************************************************************/ /* Function to release PartsDBE. */ /*************************************************************************/ int TerminateProgram() { /* Function to Release PartsDBE */ EXEC SQL RELEASE; } /* End TerminateProgram Function */ /*************************************************************************/ /*Function to display error messages and terminate the program when the */ /*transaction has been rolled back by ALLBASE/SQL.
SQLStatusCheck(); } /* End if */ return(rv); } /* End of ConnectDBE Function */ /*************************************************************************/ /*Function to begin the transaction with cursor stability specified. */ /*************************************************************************/ int BeginTransaction(){ EXEC SQL BEGIN WORK CS; if (sqlca.
switch (sqlca.sqlcode){ case OK: default: } } /* switch */ break; SQLStatusCheck(); RollBackWork(); OrdersOK = FALSE; DoneConvert = TRUE; /* End of Function InsertNew */ /*************************************************************************/ /*Function to convert OrderDate from CHAR to DATE data type and transfer */ /*data to an array in preparation for BULK INSERT into a new table.
/**************************************************************************/ /*Function to BULK FETCH Orders table data 25 rows at a time into an array*/ /**************************************************************************/ int FetchOld() { NumberOfRows = 25; StartIndex = 0; printf("BULK FETCH PurchDB.Orders\n"); EXEC SQL BULK FETCH OrdersCursor INTO :Orders, :StartIndex, :NumberOfRows; counter1 = sqlca.sqlerrd[2]; /* Set counter1 to number of rows fetched.*/ switch (sqlca.
Programming with TID Data Access Each row (tuple) in an ALLBASE/SQL table is stored at a database address on disk. This unique address is called the tuple identi er or TID. When using a SELECT statement, you can obtain the TID of any row. In turn, you can use this TID to specify the target row for a SELECT, UPDATE, or DELETE statement. TID functionality provides the fastest possible data access to a single row at a time (TID access) in conjunction with maximum coding exibility.
SELECT FROM WHERE AND TID(sp), TID(o) PurchDB.SupplyPrice sp, PurchDB.Orders o sp.VendorNumber = :VendorNumber o.VendorNumber = :VendorNumber Using the TID Function in a WHERE Clause When using the TID function in a WHERE clause, you provide an input parameter. For application programs, this parameter can be speci ed as a host variable, or a constant. The input parameter is a constant. For example: DELETE FROM PurchDB.
An SQLTID host variable consists of eight bytes of binary data and has the following format: Table 11-4. SQLTID Data Internal Format Content Byte Range Version Number 1 through 2 File Number 3 through 4 Page Number 5 through 7 Slot Number 8 The SQLTID version number is an optional input parameter. If not speci ed, the version number defaults to 0. If you do specify the version, it must always be 0. If a version other than 0 is speci ed, no rows will qualify for the operation.
DELETE FROM PurchDB.Parts WHERE TID() = :PartsTID AND PartName = 'Winchester Drive' However, in the next statement TID access would not be used: DELETE FROM PurchDB.Parts WHERE TID() = :PartsTID1 OR TID() = :PartsTID2 See the \Expressions" chapter of the ALLBASE/SQL Reference Manual for an explanation of the above and additional optimization criteria. Verifying Data that is Accessed by TID It is important to note that a TID in ALLBASE/SQL is unique and is valid until its related data is deleted.
update or delete it. By verifying the data in this way, you insure that it still exists and can determine whether or not it has changed from the time it was last presented to the user. Coding Strategies Suppose you are writing an application that will be executed by many simultaneous users in an online transaction processing environment. You want each user to be able to locate and update just a few rows in a table that is frequently accessed by many users.
When all user changes have been entered, use a loop to compare the previously fetched rows (in OrdersArray) with the same rows as they now exist in the database. Begin your transaction with the RR isolation level. No other transaction can access the locked data until this transaction ends, providing maximum data integrity. BEGIN WORK RR For each entry in NewOrdersArray, do the following: SELECT TID(), INTO FROM WHERE * :TIDvalue, :OrderNumber, :VendorNumber, :OrderDate PurchDB.
updates in the inner loop. This use of a single COMMIT WORK for the multiple updates in the inner loop reduces overhead.
De ne two arrays, one (PartsArray) to hold the qualifying rows of the Parts table and another (NewPartsArray) to hold the rows that the user wants to change. Be sure to de ne an element in each array to hold the TID value. Declare the cursor (BulkCursor) used by the BULK FETCH loads the PartsArray. 44 5 that DECLARE BulkCursor CURSOR FOR SELECT TID(), PartNumber, PartName, SalesPrice FROM PurchDB.Parts Declare the cursor (TidCursor) used to UPDATE on the TID value.
The following COMMIT WORK 4 5 5 statement commits the updates VerifyAndUpdate and releases the locks held. 4 11 5 in COMMIT WORK 45 5 End Loop CLOSE BulkCursor 46 5 The nal COMMIT WORK 4 7 5 statement ends the transaction started by the BEGIN WORK RC 4 2 5 . Any locks still held are released. COMMIT WORK Begin the VerifyAndUpdate routine. 47 5 48 5 Assign to HostPartTid the TID value in NewPartsArray.
Index A active set re-access, 6-9 ADD MONTHS function example with BULK SELECT, 11-11 syntax, 11-11 aggregate function simple data manipulation, 5-2 ALTER TABLE command syntax for LONG columns, 10-4 ANSI SQL1 level 2 specifying a default value, 3-17 ANSI SQL86 level 2 oating point data, 3-11 ANSI standards sqlcode, 4-4 arrays BULK SELECT, 3-5 character data, 3-8 declarations of, 3-26 in sqlda declaration, 8-15 referencing, 7-2 atomic operation de ned, 4-2 authorization changing, 1-21 dynamic preprocessing,
before ending a transaction, 6-9, 6-10 freeing bu er space with, 6-9 to re-access the active set, 6-9 with COMMIT WORK, 6-12 with KEEP CURSOR, 6-12 coding considerations for date/time functions, 11-11 for LONG columns, 10-13, 10-14 column speci cations for oating point data, 3-11 comments in SQL commands, 1-10 COMMIT WORK and revalidation of sections, 2-26 in transaction management, 5-7 issued by preprocessor, 2-8 with CLOSE, 6-12 with KEEP CURSOR, 6-12 compatibility of variables, 3-20 concurrency, 5-7 CONN
leading zeros required for input functions, 11-11 parameters for, 11-2 unspeci ed format elements default lled, 11-11 used to add a number of months, 11-11 used when inputting data, 11-3 used when retrieving data, 11-8 using host variables for format speci cations, 11-2 using host variables for input and output data, 11-2 using host variables with, 11-2 where to use ADD MONTHS, 11-11 where to use input functions, 11-3 where to use output functions, 11-8 where to use TO CHAR, 11-8 where to use TO DATE, 11-3
designing an application using statement level integrity, 9-3 detecting end of scan, 4-35 DML de ned, 1-2 DML operations used with date/time functions, 11-2 used with integrity constraints, 9-2 used with LONG columns, 10-1 DROP MODULE, 1-20, 1-21, 2-2 DROP option full preprocessing mode, 2-1 dynamically deleting data DELETE WHERE CURRENT command cannot be prepared, 8-10 error checking strategy, 8-10 dynamically updating data error checking strategy, 8-10 UPDATE WHERE CURRENT command cannot be prepared, 8-10
4-byte, 3-11 8-byte, 3-11 column speci cations, 3-11 compatibility, 3-11 REAL keyword, 3-11 format array declaration, 3-26 elds, 8-13 manditory declaration for dynamic query, 8-17 FOR UPDATE OF UPDATE WHERE CURRENT, 6-2, 6-5 full preprocessing mode preprocessor command, 2-1 G general rules skeleton program, 1-8 GOTO vs.
L language current language, 1-23 native language support, 1-23 logging, 2-3 LONG binary data compatibility, 3-12 de nition, 3-12 how stored, 3-12 LONG binary versus LONG varbinary data usage, 3-12 LONG column de nition in a table, 10-4 input and output speci cation , 10-5 with the LONG column I/O string, 10-5 LONG column descriptor contents of, 10-10 example declaration, 10-11 general concept, 10-2 how used, 10-10 introduction to, 10-5 LONG column I/O string general concept, 10-2 heap space input and outp
multiple warnings SQLEXPLAIN, 4-10 N naming conventions for LONG column les, 10-13 NATIVE-3000 de ned, 1-23 native language current language, 1-23 defaults, 1-23 message catalog, 1-15 native language data verifying column de nition, 3-24 native language support overview, 1-23 non-dynamic commands, 8-1 NULL as default data value, 3-18 null indicator su x data bu er, 8-15 null indicator variable in dynamic command, 8-15 null indicator variables and the INFO command, 3-20 NULL result of a dynamic fetch of a L
Q query dynamic data structures, 8-8 R REAL keyword oating point data, 3-11 RecDB database application design example maintenance menu, 9-4 example of deleting data, 9-7 example of error checking, 9-4 example of inserting data, 9-5 example of updating data, 9-6 integrity constraints de ned, 9-3 REFETCH command used with LONG columns, 10-10 restrictions integrity constraints, 9-2 LONG columns, 10-4 retrieving LONG column data with [BULK] SELECT, FETCH, or REFETCH commands, 10-10 REVOKE, 1-21 REVOKE option
sqlca elements of, 4-4 purpose, 4-4 sqlca.sqlcode introduction, 4-4 usage, 4-6 sqlca.sqlerrd[2] introduction, 4-4 usage, 4-8 sqlca.sqlwarn[0] introduction, 4-4 usage, 4-9 sqlca.sqlwarn[1] introduction, 4-4 usage, 4-10 sqlca.sqlwarn[2] introduction, 4-4 usage, 4-11 sqlca.sqlwarn[3] introduction, 4-4 usage, 4-11 sqlca.
syntax checking mode, 2-6 syntax for date/time functions ADD MONTHS, 11-11 input functions, 11-3 output functions, 11-8 TO CHAR, 11-8 TO DATE, 11-3 TO DATETIME, 11-3 TO INTEGER, 11-8 TO INTERVAL, 11-3 TO TIME, 11-3 syntax for LONG columns ALTER TABLE command, 10-4 CREATE TABLE command, 10-4 select list, 10-10 syntax within embedded SQL, 1-10 system catalog, 1-12 T temporary section, 8-8 terminal IO KEEP CURSOR, 6-16 TO CHAR function example with SELECT command, 11-8 syntax, 11-8 TO DATE function example wi
W warning message and sqlcode, 4-10 and sqlwarn[0], 4-10 warnings runtime handling, 4-2 WHENEVER components of, 4-13 duration of command, 4-7 for di erent conditions, 4-14 transaction roll back, 4-14 Index-11
