900 Series HP 3000 Computer Systems ALLBASE/SQL Advanced Application Programming Guide ABCDE HP Part No. 36216-90100 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. Edition Date Software Version First Edition April 1994 36216-02A.G0.
ALLBASE/SQL Manuals Title ALLBASE/NET User's Guide ALLBASE/SQL Advanced Application Programming Guide ALLBASE/SQL C Application Programming Guide ALLBASE/SQL COBOL Application Programming Guide ALLBASE/SQL Database Administration Guide ALLBASE/SQL FORTRAN Application Programming Guide ALLBASE/SQL Message Manual ALLBASE Pascal Application Programming Guide ALLBASE/SQL Performance and Monitoring Guidelines ALLBASE/SQL Reference Manual HP ALLBASE/QUERY User's Guide HP PC API User's Guide for ALLBASE/SQL and I
Preface This ALLBASE/SQL Advanced Application Programming Guide for MPE/iX is the rst edition of a new manual targeted for experienced ALLBASE/SQL application programmers using ALLBASE/SQL on HP 3000 systems. It describes product enhancements for ALLBASE/SQL Release F through the present release. This guide replaces the release speci c ALLBASE/SQL application programming bulletins.
To facilitate information lookup, one chapter is devoted to each type of functionality. The index is task as well as reference oriented. The following topics are included in this manual: Chapter 1, \Using the Preprocessor," presents complete syntax for Full Preprocessing Mode, Static Conversion Mode, Syntax Checking Mode, and POSIX invocation for the C preprocessor. Chapter 2, \Flagging Non-Standard SQL with the FIPS Flagger," discusses ALLBASE/SQL agging for the FIPS 127.1 standard.
What's New in this Release The following table highlights the new or changed functionality in this release, and shows you where each feature is documented. New Features in ALLBASE/SQL Release G.0 Feature (Category) Description Documented in . . . Stored procedures (Usability) Provides additional stored procedure functionality for application programs. Allows declaration of a procedure cursor and fetching of multiple rows within a procedure to applications. New statement: ADVANCE.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . New SQLGEN GENERATE parameters (Usability) Generates SQL statements necessary to recreate modi ed access plans for module sections. New syntax for GENERATE: DEFAULTSPACE, MODOPTINFO, PARTITION, PROCOPTINFO, SPACEAUTH. ALLBASE/SQL Database Administration Guide , \SQLGEN Commands" appendix.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) High Availability Description Provides a collection of features to keep systems available nonstop including: Partial STORE and RESTORE, Partial rollforward recovery, DBEFiles in di erent groups (MPE/iX), detaching and attaching database objects, CHECKPOINT host variable, changing log les, console messages logged to a le, generating fewer log records by using TRUNCATE TABLE to delete rows, and new system catalog information.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) New and changed SQLUtil commands for increased availability (High Availability) Description Documented in . . . Adds support for high availability ALLBASE/SQL Database Administration Guide , \SQLUtil" appendix. and System Management Intrinsics. Intended for non-stop, continuously available operations. New SQLUtil commands: ATTACHFILE, CHANGELOG, DETACHFILE, RESTORE PARTIAL, STORE PARTIAL, STOREINFO, STOREONLINE PARTIAL, WRAPDBE.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . Optimizer enhancement (Performance) ALLBASE/SQL Performance and Uses a more e cient algorithm that signi cantly reduces the time Monitoring Guidelines , \Optimization" in \Basic Concepts in ALLBASE/SQL to generate the access plan. Performance." Access plan modi cation (Performance) Allows modi cation of access plans for stored section to optimize performance. View the plan with SYSTEM.SETOPTINFO.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) I/O performance improvement (Performance) Description Optimizes I/O for initial load, index build, serial scans, internal data restructuring, le activity, pseudo mapped les and temporary les. See the following features for new and changed syntax. Documented in . . . ALLBASE/SQL Reference Manual , \SQL Statements." Deletes all rows in a speci ed table ALLBASE/SQL Reference Manual , TRUNCATE TRUNCATE TABLE in \SQL Statements.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . Modi ed SET options (Performance) Provides better performance for LOADs and UNLOADs. Specify bu er size, status reporting for LOAD/UNLOAD or exclusive lock for data table. AUTOSAVE row limit increased to 2147483647. New and changed SET options: LOAD BUFFER, LOAD ECHO, AUTOLOCK, AUTOSAVE.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Increased memory for MPE/iX (HP-UX shared memory allocation is unchanged) (Performance) Increases memory up to 50,000 data bu er pages and 2,000 run time control block pages. Increases the limits signi cantly allowing allocation of enough data bu er pages to keep the entire DBEnvironment in memory if desired for performance.
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 comamnd 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. Using the Preprocessor Full Preprocessing Mode . . . . . . . . . . . . . . . . . . . . Full Preprocessing Mode Syntax Speci cation . . . . . . . . . Full Preprocessing Mode for C Applications . . . . . . . . . Full Preprocessing Mode for COBOL Applications . . . . . . Full Preprocessing Mode for FORTRAN Applications . . . . . Full Preprocessing Mode for Pascal Applications . . . . . . . Parameters . . . . . . . . . . . . . . . . . . . . . . . . . Description . . . . . . . . . . . . . . . . . . .
2. Flagging Non-Standard SQL with the FIPS Flagger Coding Tips . . . . . . . . . . . . . . . . . Setting the ANSI Compiler Directive . . . . . Identifying Non-Standard Features . . . . . . . Understanding Implicit Updatability . . . . . . Declaring the SQLCA . . . . . . . . . . . . . Secondary References to Non-Standard SQL . . . Host Variable Data Type Declarations . . . . . . Host Variable Name Length Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Dynamic Processing . . . . . . . . . . . . . . . . . . . . Using Host Variables to Pass Parameter Values . . . . . . . . . . Using Dynamic Procedure Parameters . . . . . . . . . . . . . . Returning a Return Status Code . . . . . . . . . . . . . . . . Testing SQLCODE and SQLWARN0 on Return from a Procedure . . Checking for All Errors and Warnings . . . . . . . . . . . . . Returning Output Values . . . . . . . . . . . . . . . . . . . Additional Error and Message Handling . . . . . . . . . . . . .
9. Programming with Indicator Variables in Expressions 10. Analyzing Queries with GENPLAN 11. Using the VALIDATE Statement 12. Corrections to the BCDToString Example Program Routine Correcting the C Language Program . . . . . . . . . . . . . . . . . . Correcting the Pascal Language Program . . . . . . . . . . . . . . . .
Tables 2-1. 2-2. 2-3. 2-4. 2-5. 2-6. 2-7. 4-1. 4-2. 4-3. 4-4. 4-5. 4-6. 4-7. 4-8. 4-9. 5-1. 5-2. ANSI Compiler Directives for Flagging Non-Standard Syntax . . . . . . . ALLBASE/SQL Non-Standard Programming Features . . . . . . . . . . ALLBASE/SQL FIPS 127.1 Compliant Data Type Declarations for C . . . ALLBASE/SQL FIPS 127.1 Compliant Data Type Declarations for COBOL ALLBASE/SQL FIPS 127.1 Compliant Data Type Declarations for FORTRAN . . . . . . . . . . . . . . . . . . . . . . . . . . .
1 Using the Preprocessor This chapter details complete syntax for each ALLBASE/SQL preprocessing mode, for each supported programming language (C, COBOL, FORTRAN, and Pascal). Related coding techniques are also discussed. Main topics include: Preprocessing in Full Preprocessing Mode. Preprocessing in Static Conversion Mode. Preprocessing in Syntax Checking Mode. POSIX Preprocessor Invocation.
Full Preprocessing Mode for COBOL Applications RUN8PSQLPAS.PUB.SYS;INFO= "DBEnvironmentName 9 MODULE(ModuleName) > > > > > > > > OWNER ( OwnerName ) > > > > > > > > FLAGGER ( FlaggerName ) > > > > > > > > > > ANSI > > > > > > > > = < NOINSTALL INSTALL(DBEFileSetName) |...|)]" [( 8 9 > > > > > PRESERVE = > < > > > > DROP > > > > REVOKE > > > > : ; > > > > NODROP > > > > > > > > WARN > > > > ; : NOWARN Full Preprocessing Mode for FORTRAN Applications RUN8PSQLFOR.PUB.
ModuleName OwnerName FlaggerName assigns a name to the stored module. Module names must follow the rules governing ALLBASE/SQL basic names as described in the ALLBASE/SQL Reference Manual . If a module name is not speci ed, the preprocessor uses the PROGRAM-ID (for a C application) or the SourceFileName (for other languages) in upper case as the module name. This module name is stored in the module le, SQLMOD. associates the stored module with a User@Account , a class name, or a group name.
PRESERVE REVOKE NODROP NOWARN WARN THREAD Is speci ed when the program being preprocessed already has a stored module and you want to preserve existing RUN authorities for that module. If not speci ed, PRESERVE is assumed. PRESERVE cannot be speci ed unless DROP is also speci ed. 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.
log space. Refer to the ALLBASE/SQL Database Administration Guide for additional information on log space management, such as using the START DBE NEWLOG statement to increase the size of the log le. During preprocessing, system catalog pages accessed for embedded statements are locked. In multiuser mode, other DBE sessions accessing the same objects may have to wait, so there is potential for a deadlock. You have several options to use to attempt to avoid a deadlock situation.
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. To store sections in a speci ed DBEFileSet, the module owner must have SECTIONSPACE authority on the DBEFileSet. DBEnvironment CONNECT authority can be explicitly granted.
Example of Full Preprocessing of a C Application :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.
DBEnvironment Access in Full Preprocessing Mode When you invoke the preprocessor in full preprocessing mode, and specify an ALLBASE/SQL DBEnvironment, the preprocessor starts a DBE session for that DBEnvironment when preprocessing begins and terminates that session when preprocessing is completed. : RUN PSQLC.PUB.SYS; INFO="DBEnvironment (MODULE (ModuleName))" When the preprocessor terminates its DBEnvironment session, it issues a COMMIT WORK statement if it encountered no errors.
Before running the preprocessor, equate SQLIN to the name of the le containing the application you want to preprocess: :FILE SQLIN = InFile Run the preprocessor for each DBEnvironment and module, as in the following example in C: :RUN PSQLC.PUB.SYS;INFO="DBEnvironment1 (MODULE (ModuleName1))" After running the preprocessor, save and rename the output les so that they will not be overwritten.
Static Conversion Mode The DYNAMIC preprocessor command line option provides a means of converting static SQL statements to dynamic statements with little or no change to existing source code. This is termed static conversion processing and provides the exibility of running an application with either static processing or dynamic processing. In addition, an already dynamic application can be preprocessed without having to specify a DBEnvironment name in the command line.
insert a #include \SQLVAR" wherever local variables are required to compile and utilize application threads successfully. Description The complete set of ALLBASE/SQL syntax can be preprocessed in static conversion mode with the following execeptions: Any DECLARE CURSOR statements must be located in an executable portion of the code. In the C language, for example, you would declare the cursor within curly brackets following any data declaration statements within a given block.
Syntax Checking Mode In syntax checking mode, the preprocessor checks SQL syntax only. This mode can be useful as you develop the SQL portions of your applications. Preprocessing is quickest in this mode. In addition, you can start debugging your SQL statements before the DBEnvironment itself is in place. Syntax Checking Mode Syntax Specification Syntax Checking Mode for C Applications 3 2 FLAGGER (FlaggerName) 5)" 4 :RUN PSQLC.PUB.
Description The preprocessor does not access a DBEnvironment when it is run in this mode. When performing syntax only checking, the preprocessor does not convert embedded SQL statements into constructs for the programming language being used. Therefore the modi ed source code le does not contain preprocessor generated calls to ALLBASE/SQL external procedures. The include and installable module les are created, but are incomplete.
Example of Syntax Checking of a C Application :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.
POSIX Preprocessor Invocation You can invoke the C preprocessor in any preprocessing mode, using POSIX command line options as described in the following sections. The preprocessor generates output les as POSIX byte-stream les (compatible with UNIX) using the naming conventions documented in the ALLBASE/SQL application programming guides for HP-UX. An existing DBEnvironment name can be speci ed in the preprocessor command line in hierarchical le system (HFS) format.
Language File Name Extension -o OwnerName -m ModuleName -f FlaggerName C SourceFileName .c COBOL SourceFileName .cbl FORTRAN SourceFileName .f Pascal SourceFileName .p associates the stored module with a User@Account , a class name, or a group name. You can specify an owner name for the module only if you have DBA authority in the DBEnvironment where the module is to be stored. If not speci ed, the owner name is your log-on User@Account .
DECLARE CURSOR, or PREPARE statement within the preprocessed application. deletes any module currently stored in the DBEnvironment by the ModuleName and OwnerName speci ed in the command string. If not speci ed, any module having these names is not dropped, and existing RUN authorities for that module are preserved. is speci ed when the program being preprocessed already has a stored module and you want to revoke existing RUN authorities for that module.
\Understanding Implicit Updatability" and \Declaring the SQLCA" in this manual. For C applications only; used to support threaded applications. When speci ed, the C preprocessor will not generate the #include \SQLVAR" line at the beginning of SQLOUT. The user must insert a #include \SQLVAR" wherever local variables are required to compile and utilize application threads successfully. -t POSIX Syntax Checking Mode for C Applications 2 3 -i SourceFileName 6 -p Modi edSourceFileName 7 7 6 7| . . .
2 Flagging Non-Standard SQL with the FIPS Flagger The United States government has adopted ANSI X3.135-1989, Database Language SQL, as the database language to be used by all federal departments and agencies. This SQL standard, known as Federal Information Processing Standard 127.1 (FIPS 127.1), requires that all syntax and processing that does not conform to the standard be agged.
Setting the ANSI Compiler Directive When you invoke the preprocessor with the FLAGGER option, your application must contain an ANSI mode compiler directive in order for the compiler to detect non-standard statements. The following table lists the appropriate directive for each language: Table 2-1. ANSI Compiler Directives for Flagging Non-Standard Syntax Language Directive C ccxl . . .
Understanding Implicit Updatability The FIPS 127.1 standard does not allow a FOR UPDATE clause in a DECLARE CURSOR statement. In this standard, updatability of a cursor rests solely on the cursor de nition. In contrast, ALLBASE/SQL default processing (i.e., agging is not in e ect) of a DECLARE CURSOR statement having no FOR UPDATE clause is to allow neither update nor delete on any column in the cursor de nition.
Host Variable Data Type Declarations The following tables list valid ALLBASE/SQL host variable data types for each supported language. FIPS 127.1 standards compliant data types are indicated with a YES. ALLBASE/SQL extensions to the standard are indicated with a NO. Table 2-3. ALLBASE/SQL FIPS 127.1 Compliant Data Type Declarations for C ALLBASE/SQL DATA TYPES FIPS 127.
Table 2-4. ALLBASE/SQL FIPS 127.1 Compliant Data Type Declarations for COBOL SQL DATA TYPES FIPS 127.1 Compliant? COBOL DATA DESCRIPTION ENTRIES CHAR(n) YES 01 DATA-NAME PIC X(n ). VARCHAR(n) NO 01 GROUP-NAME . 49 LENGTH-NAME PIC S9(9) COMP. 49 VALUE-NAME PIC X(n ). BINARY NO 01 DATA-NAME PIC X(n ). VARBINARY(n) NO 01 GROUP-NAME . 49 LENGTH-NAME PIC S9(9) COMP. 49 VALUE-NAME PIC X(n ). SMALLINT NO 01 DATA-NAME PIC S9(4) COMP. INTEGER YES 01 DATA-NAME PIC S9(9) COMP.
Table 2-5. ALLBASE/SQL FIPS 127.1 Compliant Data Type Declarations for FORTRAN SQL DATA TYPES FIPS 127.
Table 2-6. ALLBASE/SQL FIPS 127.1 Compliant Data Type Declarations for Pascal SQL DATA TYPES FIPS 127.1 Compliant? PASCAL TYPE DESCRIPTION CHAR(1) NO DataName : char; CHAR(n) NO DataName : array [1..n ] of char; or YES DataName : packed array [1..n ] of char; VARCHAR( n ) NO DataName : string[ n ]; BINARY(1) NO DataName : char; BINARY(n) NO DataName : array [1..n ] of char;or NO DataName : packed array [1..
Host Variable Name Length Standards The following table lists the standards compliant length for a host variable name in each supported language and the maximum ALLBASE/SQL limit. Table 2-7. FIPS 127.1 Compliant Host Variable Name Lengths FIPS 127.
3 Comparing Static and Dynamic SQL An ALLBASE/SQL application can contain both static and dynamic SQL statements. A static statement involves the preparation and storing of a section at preprocessing time and the execution of that stored section at run time. A dynamic statement involves the preparation and execution of a section at runtime. Some statements do not require a section, and they are also classi ed as dynamic.
Comparing Static and Dynamic Applications A static application is one that is preprocessed in full preprocessing mode and contains at least one static SQL statement. This means that a module is generated at preprocessing time, and this module must be installed in any DBEnvironment in which the application is running. An application containing only dynamic SQL statements is termed a dynamic application. Such an application does not require a module.
Converting a Static Application to a Dynamic Application There are two ways of converting a static application to a dynamic application. One method is to change the source code to contain the required dynamic SQL statements. (The ALLBASE/SQL application programming guides provide complete information about coding with dynamic SQL statements.) The second method is to preprocess the application using static conversion mode.
4 Using Parameter Substitution in Dynamic Statements When your application uses dynamic processing, parameter substitution o ers added exibility and improved performance. Although you can use this technique in any dynamic processing application involving prepared sections, it could be most useful for applications where the same SQL statement type must be re-executed multiple times using a di erent set of parameter values each time.
Examples in C of Preparing a Statement with Dynamic Parameters The following example uses a string as a parameter of the PREPARE statement: EXEC SQL PREPARE CMD1 FROM 'INSERT INTO PurchDB.Parts (PartNumber,PartName) VALUES (?,?);'; In the following example, a host variable is used: In the declare section, declare a character array host variable large enough to hold the string plus one byte for a delimiting ASCII 0: EXEC SQL BEGIN DECLARE SECTION; .. . char DynamicCmdLine[81]; .. .
STRING TEMP1 DELIMITED BY SIZE TEMP2 DELIMITED BY SIZE INTO DYNAMICCMD. Prepare the statement: EXEC SQL PREPARE CMD1 FROM :DYNAMICCMD END-EXEC. Examples in FORTRAN of Preparing a Statement with Dynamic Parameters The following example uses a string as a parameter of the PREPARE statement: EXEC SQL PREPARE CMD1 FROM 'INSERT INTO PurchDB.
In the following example, a host variable is used: In the declare section, declare a host variable large enough to hold the string: EXEC SQL BEGIN DECLARE SECTION; .. . DynamicCmdLine:string[80]; .. . EXEC SQL END DECLARE SECTION; .. . Assign the string to the host variable: DynamicCmdLine := 'INSERT INTO PurchDB.
Restrictions The examples below are shown to clarify the conditions under which dynamic parameters cannot be used. The following locations are not valid: In any select list. In any statement that is not dynamically preprocessed with the PREPARE statement. As both operands of a single, arithmetic operator or comparison operator. The following example is not valid: SELECT * FROM PurchDB.Parts WHERE SalesPrice > (? * ?) As the operand of a minus sign or a null predicate.
Programming with Dynamic Parameters Depending on the purpose of your application, there is a broad spectrum of scenarios in which dynamic parameters could be useful. You might know almost all the elements of a statement at coding time, including the statement type and what dynamic parameters are required. At the opposite extreme, a program might be required to handle a completely unknown SQL statement containing dynamic parameters.
.. . Accept a variable indicating which of a set of statements the user has chosen. Prepare the dynamic command for this statement: NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN PREPARE CMD FROM 'UPDATE PurchDB.
One di erence between the use of the SQLDA for input data versus output data involves the Sqln and Sqld elds. Sqln is set by your program prior to issuing the DESCRIBE statement and represents the maximum number of 48 byte format array records allowed by the program. When using the DESCRIBE OUTPUT speci cation, you tell ALLBASE/SQL to load each format record with information for each select list item in the currently prepared statement (if it is a query).
If you know nothing about an SQL statement until run time, de ne an sqlda type structure for output to determine if the statement is a query or not. If it is a query, ALLBASE/SQL loads the related sqlformat type structure with the format of the query result (one 48 byte element per select list item). You must also de ne an sqlda type structure for input in case the statement contains dynamic parameters.
11. Close the cursor and commit work. 12. If the prepared statement is not a query, use the EXECUTE statement with the USING clause to pass in dynamic parameter values. Table 4-3.
Table 4-4.
I Used for input. O Used for output. R Used for DESCRIBE RESULT and ADVANCE. 1 When you describe for output, use sqlnrow to specify the number of rows to fetch into the data bu er. When you describe for input, use sqlnrow to specify the number of rows you have loaded into the data bu er (Always set to one for a non-bulk statement.). 2 Data is loaded into the format array when a DESCRIBE or ADVANCE statement executes.
Table 4-5.
I Used for input. O Used for output. R Used for DESCRIBE RESULT and ADVANCE. 1 When you describe for output, use sqlnrow to specify the number of rows to fetch into the data bu er. When you describe for input, use sqlnrow to specify the number of rows you have loaded into the data bu er (Always set to one for a non-bulk statement.). 2 Data is loaded into the format array when a DESCRIBE or ADVANCE statement executes.
Table 4-6.
Table 4-6.
Table 4-7.
Table 4-7. Fields in a Format Array Record in Pascal (continued) Field Name Meaning of Field Pascal Data Type sqlnof byte o set of null indicator from the beginning of a row, dependent on the value of sqlindlen Integer sqlname de ned name of column or, for computed expression, EXPR Packed Array [1..
Example in C Using Output and Input Data Buffers Suppose you have designed an application that builds a SELECT statement. A user can enter any valid DBEnvironment name, table name, column name, and a column value to be used as a lter in the WHERE clause. The application builds the appropriate query and displays the query result. Your application prepares this SELECT statement and describes it for output.
char SearchValue[1024]; EXEC SQL END DECLARE SECTION; Declare the SQL communications area. EXEC SQL INCLUDE SQLCA; The sqldain record contains information about the sqlfmtsin format array and the DataBu erIn variable. sqlda_type sqldain; The sqldaout record contains information about the sqlfmtsout format array and the DataBu erOut variable. sqlda_type sqldaout; The sqlfmtsin format array describes the dynamic parameters in the WHERE clause of the SELECT statement.
char DataBufferOut[MaxDataBuff]; .. . /********************************************************************/ main() /********************************************************************/ { Prompt the user for the database environment used in the CONNECT statement. sprintf (DBEName,""); sprintf (DynamicCommand,""); Prompt ("DBEnvironment name",DBEName); After prompting the user for the table name and the column name, move the SELECT statement into the DynamicCommand variable.
sqlfmtsin array. sqldain.sqln = NbrFmtRecords; sqldain.sqlfmtarr = sqlfmtsin; The sqldaout.sqln variable is assigned the number of records in the sqlfmtsout array and the sqldaout.sqlfmtarr variable is assigned the address of the sqlfmtsout array. sqldaout.sqln = NbrFmtRecords; sqldaout.sqlfmtarr = sqlfmtsout; if (BeginTransaction()) { Prepare the dynamic SELECT statement. At this point the value of the dynamic parameter is still unde ned. EXEC SQL PREPARE CMD1 FROM :DynamicCommand; if (sqlca.
} } /* End if BeginTransaction */ } /* End of Prepare function */ /********************************************************************/ int Fetch() /********************************************************************/ { short i; ConvertType ConvertedSearch; Declare the cursor for the SELECT statement. EXEC SQL DECLARE CURSOR1 CURSOR FOR CMD1; Prompt the user for the search value, which will be assigned to the dynamic parameter in the WHERE clause of the SELECT statement. The sqlfmtsin[0].
if (sqlfmtsin[0].sqltype == SQLINT) { INT or SMALLINT columns generate the same data type value in sqltype, but must be distinguished because they have di erent lengths. If sqlvallen is equal to the size of an integer variable, then the data type is INT. if (sqlfmtsin[0].sqlvallen == sizeof(ConvertedSearch.IntegerData)) SQL INT data type. ConvertedSearch.IntegerData = atoi(SearchValue); else Otherwise, the column data type is SMALLINT. ConvertedSearch.
sqldaout.sqlnrow = ((sqldaout.sqlbuflen) / (sqldaout.sqlrowlen)); The sqldaout.sqlrowbuf variable is assigned the address of DataBu erOut. sqldaout.sqlrowbuf = (int) DataBufferOut; Fetch rows into DataBu erOut until no more rows are found. do { EXEC SQL FETCH CURSOR1 USING SQL DESCRIPTOR sqldaout; if (sqlca.sqlcode == 100) printf ("Warning: No more rows qualify for this operation\n"); else if (sqlca.
while (i++ <= n) dest[subd++] = source[subs++]; } /* End of StrMove function */ /********************************************************************/ int Prompt (displaystr,inputstr) /********************************************************************/ char *displaystr, *inputstr; { printf("Enter %s: ",displaystr); gets(inputstr); } /* End of Prompt function */ .. . You could enhance the above pseudocode by coding an application for the following scenario.
Note When host variables are used, EXECUTE statement syntax di ers for a BULK INSERT statement and an INSERT statement. Example in C Using a BULK INSERT .. . boolean boolean .. . OrdersOk; ConnectDBE(); sqlca_type sqlca; /* SQL communication area. */ De ne a host variable array to hold dynamic parameter values.
main() { /*Specify main functions. if (ConnectDBE()) { /* If the application is successfully */ /* connected to a DBEnvironment, proceed. */ OrdersOk = TRUE; */ BeginTransaction(); PrepareIt(); CreateOrders(); InsertNew(); if (OrdersOk) CommitWork(); /* If there were no errors in processing, */ /* data is committed to the database. */ TerminateProgram(); } } /* End if. /* End of main program. .. .
Count rows as they are loaded into the NewOrders array up to a maximum of 25: for (i = 0; i <= NumberOfRows; i++){ Read a le record or accept a row of data from the user into the appropriate host variables. Load host variable data into the bulk insert array. NewOrders[i].NewOrderNumber = OrderNumber; NewOrders[i].NewVendorNumber = VendorNumber; NewOrders[i].NewVendorNumberInd = VendorNumberInd; strcpy (NewOrders[i].NewOrderDate,OrderDate); NewOrders[i].NewOrderDateInd = OrderDateInd; } } /* End for.
Example in COBOL Using a BULK INSERT . . . WORKING-STORAGE SECTION. * SQL communication area. * EXEC SQL INCLUDE SQLCA END-EXEC. * Host variables for input data. * EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 ORDERNUMBER PIC S9(9) COMP. 01 VENDORNUMBER PIC S9(9) COMP. 01 VENDORNUMBERIND SQLIND. 01 ORDERDATE PIC X(8). 01 ORDERDATEIND SQLIND. De ne a host variable array to hold dynamic parameter values.
PERFORM A200-CONNECT-DBENVIRONMENT THRU A200-EXIT. PERFORM B100-PREPARE-IT THRU B100-EXIT. PERFORM C100-CREATE-ORDERS THRU C100-EXIT UNTIL DONE. PERFORM D100-BULK-INSERT THRU D100-EXIT. PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. A100-EXIT. EXIT. . . . Use the PREPARE statement to preprocess the dynamic statement, in this case, from a string: B100-PREPARE-IT. MOVE 1 to I. MOVE SPACES TO DONE-FLAG. MOVE SPACES TO NEWORDERS. PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT.
from an interactive user or from a le (In this case, it is an interactive user.): C100-CREATE-ORDERS. DISPLAY ' '. DISPLAY 'You can specify as many as 25 line items.'. DISPLAY ' '.
ELSE COMPUTE I = I + 1. C200-EXIT. EXIT. Execute the prepared CMD command specifying the array where data for the BULK INSERT is located: D100-BULK-INSERT. DISPLAY ' '. MOVE I TO NUMBEROFROWS. MOVE 1 TO STARTINDEX. MOVE 1 to I. DISPLAY 'BULK INSERT INTO PurchDB.OrderItems'. NNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL EXECUTE CMD USING :NEWORDERS, :STARTINDEX, :NUMBEROFROWS END-EXEC.
Example in Pascal Using a BULK INSERT .. . De ne a host variable array to hold dynamic parameter values. Be sure each host variable data type matches (or is compatible with) its ALLBASE/SQL default data type: EXEC SQL BEGIN DECLARE SECTION; NewOrders of record NewOrderNumber NewVendorNumber NewVendorNumberInd NewOrderDate NewOrderDateInd end; : array[1..25] : : : : : integer; integer; sqlind; packed array[1..
Use the PREPARE statement to preprocess the dynamic statement, in this case, from a string: procedure PrepareIt; begin NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL PREPARE CMD from 'BULK INSERT INTO PurchDB.Orders VALUES (?,?,?);'; if SQLCA.
end; end; (* End for. (* End procedure CreateOrders. *) *) Execute the prepared CMD command specifying the array where data for the BULK INSERT is located: procedure InsertNew; begin NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL EXECUTE CMD USING :NewOrders, :StartIndex, :NumberOfRows; if SQLCA.
Using Default Data Types with Dynamic Parameters When the PREPARE statement executes, ALLBASE/SQL assigns a default data type to any dynamic parameter in the dynamic section that is created. Depending on how you provide dynamic parameter data to the database, one of the following occurs. If you are using the DESCRIBE INPUT statement, the default data type information is loaded into the related format array.
UPDATE PurchDB.Parts SET SalesPrice = ? WHERE PartNumber = '12345' Used in the VALUES clause of an INSERT statement, its data type is assumed to be that of the inserted column. In the example below, both dynamic parameters are assumed to be of character data type because both PartNumber and PartName are de ned as such in the database. INSERT INTO PurchDB.Parts (PartNumber, PartName) VALUES(?,?) The following examples are syntactically correct but have no or little value semantically.
When a dynamic parameter is used in a non-assignment operation and the default data type is determined to be REAL, ALLBASE/SQL promotes it to FLOAT for better performance and data accuracy. Therefore, the assumed data type for a non-assignment operation is never REAL. Note Dynamic Parameter Formats In addition to default data types, dynamic parameters have default data formats as shown in the table below: Table 4-8.
Conversion of Actual Data Types to Default Data Types Your application provides a dynamic parameter value at run time by using either a host variable or a set of ALLBASE/SQL data structures and a data bu er. (These coding techniques are further discussed in the section in this document, \Programming with Dynamic Parameters.
5 Using Procedures in Application Programs This chapter describes the use of procedures in application programs. It highlights features that are available only within application programs and, in particular, methods of passing information between an application and a procedure. The nal section compares the use of application code to procedure code to accomplish the same task.
Note, it is recommended that you handle procedure statement errors and warnings within the procedure. Using Cursors with Procedures Two types of cursors are available in ALLBASE/SQL: A select cursor is one declared for a SELECT statement within either an application or a procedure. It is a pointer used to indicate the current row in a set of rows retrieved by a SELECT statement. A select cursor opened in an application program cannot be accessed within the procedure.
Procedures with Multiple Row Result Sets of Different Formats The following example shows a procedure de nition followed by an excerpt from an application program that uses a procedure cursor: EXEC SQL CREATE PROCEDURE InventoryReport (option INTEGER, qty INTEGER OUTPUT) AS BEGIN IF option = 1 THEN SELECT PartNumber FROM PurchDB.Inventory; ELSEIF option = 2 THEN SELECT DISTINCT BinNumber FROM PurchDB.Inventory; ELSE SELECT PartNumber, BinNumber, QtyOnHand FROM PurchDB.
while (sqlca.sqlcode == 0) { Fetch one or more rows from the current query result set. Use the same SQLDA as that speci ed in the ADVANCE statement. When the last row in the last result set has been fetched, ALLBASE/SQL sets SQLCA.SQLCODE to 100. When the last row in the current result set has been fetched, ALLBASE/SQL automatically advances to the next result set.
For a dynamic EXECUTE PROCEDURE statement, the DESCRIBE command with the OUTPUT option sets the sqld eld of the SQLDA to 0 and sets the sqlmproc eld to a non-zero value for a procedure having multiple row result sets. The sqloparm eld is set to the number of output parameters (including return status) in the EXECUTE PROCEDURE statement. The sqlfmtarr of the sqldaout is set to the data formats for the return status and output parameters of the procedure, if any.
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL ADVANCE InvRepCursor USING sqldaresult; if (sqlca.sqlcode != 0) { while (sqlca.sqlcode == 0) { Fetch as many rows from the current query result set as speci ed in SQLDA.sqlnrow. Specify the same SQLDA as speci ed in the ADVANCE statement. When the last row in the current result set has been fetched, ALLBASE/SQL sets SQLCA.SQLCODE to 100.
Dynamic Processing In this example, the prepared statement is an EXECUTE PROCEDURE statement with both INPUT and OUTPUT dynamic parameters. After using DESCRIBE INPUT and OUTPUT for cmd, it can be executed using input and output SQL descriptor areas, or input and output host variables.
For a dynamic EXECUTE PROCEDURE statement, the DESCRIBE command with the OUTPUT option sets the sqld eld of the SQLDA to 0 and sets the sqlmproc eld to a non-zero value for a procedure having multiple row result sets. The sqloparm eld is set to the number of output parameters (including return status) in the EXECUTE PROCEDURE statement. The sqlfmtarr of the sqldaout is set to the data formats for the return status and output parameters of the procedure, if any.
fetched, ALLBASE/SQL automatically advances to the next result set. When the last row in the last result set has been fetched, ALLBASE/SQL sets SQLCA.SQLCODE to 100. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN FETCH RepActCursor USING sqldaresult; Use number of columns and column format information to process the query result.
IF ::sqlcode = 0 THEN COMMIT WORK; RETURN 0; ELSE RETURN 1; ENDIF; END; The following example shows the execution of procedure ManufDB.Process12 using host variables to pass in the values for Operator, Shift, and FailureType: First, declare host variables. Note that the same names are used for host variables in the application program that were used in the parameter de nitions of the CREATE PROCEDURE statement.
A routine within the application program obtains values for Operator, Shift, and FailureType from a user who enters the data. If either Shift or Operator is null, the corresponding indicator variable is set to 01. Next, call the procedure to enter the failure information into the database: In C: In COBOL: EXEC SQL EXECUTE PROCEDURE ManufDB.Process12(:Operator :OperatorInd, :Shift :ShiftInd, :FailureType); EXEC SQL EXECUTE PROCEDURE ManufDB.
Table 5-2. When Dynamic Parameters are Passed Between an Application and a Procedure Type of Processing Input Parameter Values Output Parameter Values Cursor Processing OPEN CLOSE Non-Cursor Processing EXECUTE PROCEDURE EXECUTE PROCEDURE The following statement contains three dynamic procedure parameters: EXECUTE PROCEDURE ? = InventoryReport (?, ? OUTPUT) Note that a procedure return status is always an output (only) parameter.
NNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL EXECUTE PROCEDURE :Status = Process12(:OpName :OpNameInd, :Shift :ShiftInd, :FailureType); if(sqlca.sqlcode==0) { if(Status==0) printf("Failure type entered\n"); else printf("Failure type not entered. INSERT failed\n"); } A similar example in COBOL uses an integer host variable named RETCODE: NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN EXEC SQL EXECUTE PROCEDURE :RETCODE = PROCESS12 (:OPERATOR :OPERATORIND, :SHIFT :SHIFTIND, :FAILURETYPE) END-EXEC.
Checking for All Errors and Warnings The following type of routine is recommended on return from an EXECUTE PROCEDURE statement in C: while (sqlca.sqlcode < 0 || sqlca.sqlwarn[0]=='W') do { EXEC SQL SQLEXPLAIN :SQLMessage; printf("%s\n",SQLMessage); } The following is a similar routine in COBOL: IF SQLCODE IS NOT ZERO OR SQLWARN0 = "W" THEN PERFORM M100-DISPLAY-MESSAGE UNTIL SQLCODE IS ZERO AND SQLWARN0 <> "W". .. . M100-DISPLAY-MESSAGE. EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. DISPLAY SQLMESSAGE.
The following shows how the procedure is invoked from an application program: Host variables are declared. In this example, the host variable names are di erent from the parameter names used in the CREATE PROCEDURE statement. C Declarations: COBOL Declarations: EXEC SQL BEGIN DECLARE SECTION; char PartNo[16]; char Part[31]; int ReturnStatus; EXEC SQL END DECLARE SECTION; EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PARTNO PIC X(15). 01 PART PIC X(30). 01 RETCODE PIC S9(9) COMP.
Additional Error and Message Handling The use of procedures in application programming can result in errors and messages at preprocessing time and at run time. At preprocessing time, syntax errors appear in the SQLMSG le. These are like other syntax errors detected by the preprocessor. Here is an example: DBEnvironment = PartsDBE Module Name = RULEPROC CREATE PROCEDURE PurchDB.RemovePart (PartNum CHAR (16) not null) as begin delete from purchdb.
Messages from the Last SQL Statement Executed by the Procedure Inside a procedure, each SQL statement after the declaration of local variables is assigned a statement number, including all the control ow and status statements. Note that the following do not have statement numbers: BEGIN ENDIF ELSE ENDWHILE END When an SQL statement in a procedure causes the procedure to fail, a message indicating the statement number is loaded into the message bu er.
Inside procedure PurchDB.DelVendor: DELETE FROM PurchDB.Orders WHERE VendorNumber = :VendorNumber; RETURN ::sqlcode; Messages from Errors Caused by the RAISE ERROR Statement The RAISE ERROR statement provides a way of specifying your own error message numbers and text. This is very useful inside procedures that are triggered by rules, and it is also useful if you wish to build a set of error messages of your own.
For more information about RAISE ERROR, refer to the section \User De ned Messages" in the \Introduction" section of the ALLBASE/SQL Message Manual . Messages from the PRINT Statement Use the PRINT statement to store procedure messages in the SQL message bu er. PRINT is useful for presenting informational messages that do not generate an error code in the procedure and for debugging your procedure.
Comparing a Procedure and an Embedded SQL Application Imagine a data entry application which either updates prices or adds new parts to the Parts table in the sample DBEnvironment PartsDBE depending on whether the Part number is currently in the table. You could code this application using conventional embedded SQL programming.
If the part number is not in the table, add it. In C: elseif (sqlcode == 100) { EXEC SQL INSERT INTO PurchDB.Parts (PartNumber, SalesPrice) VALUES (:PartNumber, :InputPrice); if(sqlcode != 0) printf("Error on INSERT\n"); } In COBOL: IF SQLCODE IS 100 THEN EXEC SQL INSERT INTO PURCHDB.PARTS (PARTNUMBER,SALESPRICE) VALUES (:PARTNUMBER,:INPUTPRICE) END-EXEC. IF SQLCODE IS NOT ZERO THEN DISPLAY "ERROR ON INSERT." END-IF END-IF.
The following is the code that would be required in your application to execute the procedure: Declare host variables. In C: EXEC SQL BEGIN DECLARE SECTION char Number[17]; double Price; integer ReturnCode; EXEC SQL END DECLARE SECTION In COBOL: EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 NUMBER PIC X(16). 01 PRICE PIC S9(8)V9(2) COMP-3. 01 RETCODE PIC S9(4) COMP. EXEC SQL END DECLARE SECTION END-EXEC. Prompt for values for part number and input salesprice. Call the procedure to process the entry.
6 Using Data Integrity Features There are several features involving data integrity which are not discussed in the language speci c ALLBASE/SQL application programming guides. This chapter deals with them from a programming perspective under the following headings: Setting the Error Checking Level. Using Table Check Constraints. De ning and Dropping Table Constraints. De ning and Dropping View Constraints. Deferring Constraint Error Checking.
Using Table Check Constraints Check constraints check data against a de ned expression in an INSERT or UPDATE statement. You can de ne check constraints on any column of a table or on a view. For information about view constraints, see the section \De ning and Dropping View Constraints" later in this document. Use table check constraints when you want to test a column against a condition you de ne rather than against data in the database.
Defining and Dropping Table Constraints By using either the CREATE TABLE or the ALTER TABLE statement, you can add any type of table constraint (check, unique, or referential) on an existing column or a new column and you can drop any type of constraint.
Adding a Column to the Recreation Database You can use the ALTER TABLE statement to add one or more columns to a table. In the following example the ClubContact column is added to the Clubs table. The new column will contain the member name of the person designated as the contact for the club. The column's value cannot be null. ALTER TABLE RecDB.
Defining and Dropping View Constraints In contrast to table constraints, unique and referential constraints cannot be de ned on a view. A type of check constraint is available with the CREATE VIEW statement. The WITH CHECK OPTION ensures that modi cations made through an updatable view satisfy all conditions of the view de nition. The following example de nes a view check constraint named DateCheck based on the view de nition of the updatable view named RecDB.EventView: CREATE VIEW RecDB.
Deferring Constraint Error Checking With the SET CONSTRAINTS statement you can defer constraint error checking for any constraint type for all or part of a transaction. You can defer constraint error checking at any time, within or outside of a transaction. Note, however, that both error checking and constraint error checking are set to their defaults when a transaction ends.
Locating Constraint Errors When your transaction defers constraint checking, you can minimize the possibility of rollback due to constraint errors by setting constraint checking to IMMEDIATE just before the COMMIT WORK statement is executed. Then check sqlcode for constraint errors. If errors were encountered, either prompt the user to make corrections or use the trouble shooting templates below to locate the errors. Once all errors have been corrected, issue a COMMIT WORK statement.
Template for Multiple Column Referential Constraint Errors This template returns the values in rows where the referencing values in a multiple column referencial constraint with n columns match no referenced values: SELECT FROM WHERE AND . . . AND AND NOT EXISTS ForeignKeyColumn1, ForeignKeyColumn2, ForeignKeyTable ForeignKeyColumn1 IS NOT NULL ForeignKeyColumn2 IS NOT NULL . . .
NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN SET REFERENTIAL CONSTRAINTS DEFERRED UPDATE RecDB.
Set constraint error checking to IMMEDIATE. If the SET CONSTRAINTS IMMEDIATE statement succeeds, constraints are set to IMMEDIATE. If the SET CONSTRAINTS IMMEDIATE statement fails because of constraint errors, constraints remain deferred. No rollback occurs. NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN SET REFERENTIAL CONSTRAINTS IMMEDIATE Check the sqlcode eld of the sqlca.
7 Transaction Management with Multiple DBEnvironment Connections It is possible to establish a maximum of 32 simultaneous database connections. When your application must access more than one DBEnvironment, there is no need to release one before connecting to another. Performance is greatly improved by using this method rather than connecting to and releasing each DBEnvironment sequentially. This multi-connect functionality is available in either of two modes.
Preprocessing and Installing Applications The method of preprocessing and installing a static application in a DBEnvironment is described in detail in the ALLBASE/SQL application programming guides. This section relates speci cally to multi-connect applications. To avoid producing multiple modules for the same application, it is recommended that you preprocess an application once . Then install the resulting module in every DBEnvironment accessed by the application.
Locking and transaction management strategies should be considered when setting timeout values. Refer to the following section \Using Timeouts to Tune Performance" and to the \Programming for Performance" chapter in the ALLBASE/SQL application programming guides for more information. The following example illustrates how you can check for the occurrence of a timeout error: .. . CONNECT TO '../sampledb/PartsDBE' Check the sqlcode eld of the sqlca.
Using Timeouts to Prevent Undetectable Deadlocks and Infinite Waits When multi-transaction mode is in e ect across multiple DBEnvironments with multiple applications accessing the same DBEnvironments at the same time, it is possible that a deadlock that cannot be detected by ALLBASE/SQL could occur. This is known as an undetectable deadlock. In addition, when multi-transaction mode is used with multiple connections to the same DBEnvironment, an in nite wait can occur.
Using Timeouts to Tune Performance The rst connection that attaches to a DBEnvironment de nes a set of startup parameters for the DBEnvironment in shared memory. These parameters remain memory resident until the last connection to the DBEnvironment is terminated. ALLBASE/SQL associates each connection with a unique session ID.
DECLARE CURSOR FOR SELECT FROM WHERE AND SalesCursor PartNumber, InvoiceNumber, SalesDate, SalesAmount, CustomerNumber Owner.Sales PartNumber = :PartNumber SalesDate BETWEEN '1991-01-01' AND '1991-06-30' Connect to three DBEnvironments specifying a connection name for each connection. Set a timeout value following each current connection. CONNECT TO :PartsDBE AS :Parts Note that the following statement sets the Parts connection timeout to the maximum speci ed in the DBECon le.
Set the current connection to Sales. SET CONNECTION :Sales Prompt the user for a part number in the displayed range and accept the response into a host variable named PartNumber. OPEN SalesCursor Begin a second transaction that accesses SalesDBE. This transaction displays sales data for the rst six months of 1991 based on the PartNumber entered by the user. Here, for clarity, the range is hard coded. You could, however, use host variables to prompt the user for the lower and upper limits of a date range.
BULK SELECT INTO FROM WHERE AND InvoiceNumber, PartNumber, InvoiceDate, DueDate, DatePaid :AccountingArray, :StartIndex3, :NumberOfRows3 Owner.Accounting InvoiceNumber = :InvoiceNumber PartNumber = :PartNumber Test the sqlcode eld of the sqlca. If it equals -2825, a timeout has occurred, and the transaction was rolled back. Display a message and prompt the user to try again or exit the application. If they choose to try again, re-execute the transaction.
8 COBOL Preprocessor Enhancements The following COBOL preprocessor features are not discussed in the ALLBASE/SQL COBOL Application Programming Guide : Record Descriptions For Non-Bulk Queries. Host Variables Initialized With The VALUE Clause. Record Descriptions For Non-Bulk Queries Prior to this release, record descriptions were allowed only for host variables referenced in bulk queries. Host variables used in non-bulk queries can now be grouped together into logical records.
9 Programming with Indicator Variables in Expressions Prior to this release, host variable indicator variables could be speci ed with any output host variable and with some input host variables, including those used as parameters to a data/time input function. With this release, you can specify an indicator variable with any input host variable, and indicators can be present in an expression.
Set the QtyOnHandInd input indicator variable equal to -1. No matter what value is in the QtyOnHand input host variable, the QtyOnHand column is set to NULL. UPDATE PurchDB.Inventory SET QtyOnHand = :QtyOnHand :QtyOnHandInd WHERE PartNumber = :PartNumber NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN .. . You can include an indicator host variable in an expression.
10 Analyzing Queries with GENPLAN The GENPLAN statement can be useful in determining the way to write a SELECT, UPDATE, or DELETE statement for maximum performance. By issuing the GENPLAN statement in ISQL, you can see the optimizer's access plan for a given statement. Suppose you have written an application containing a query, as in the following example: SELECT INTO FROM WHERE AND PartName, VendorNumber, UnitPrice :PartName, :VendorNumber, :UnitPrice PurchDB.Parts p, PurchDB.SupplyPrice sp p.
11 Using the VALIDATE Statement When you run an application, ALLBASE/SQL automatically and transparently checks each section for validity before executing it. When a section is found to be invalid, ALLBASE/SQL revalidates it (if possible), then executes it. You may notice a slight delay as the revalidation takes place. To avoid the delay of runtime revalidation, you can re-preprocess the program or use the VALIDATE statement to revalidate the a ected modules prior to runtime.
Corrections to the BCDToString Example Program 12 Routine This chapter corrects the BCDToString routine in the \Using Dynamic Operations" chapter of the C and Pascal ALLBASE/SQL application programming guides. For each language, replacement pages are supplied. A ected lines of the program are highlighted for ease in noting the changes.
/* 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) + 1; fo
/* 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 */ NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN int getline(linebuff) /*Function to get a line of characters */ char linebuff[80]; { while (strlen(gets(linebuff)) ==0); } /* End
Correcting the Pascal Language Program The one replacement page for program pasex10a in chapter 10 of the \ALLBASE/SQL Pascal Application Programming Guide" appears on the next page.
Abort : boolean; $PAGE $ (* Procedure BCDToString converts a decimal field in the "DataBuffer" * buffer to its decimal presentation. Other input parameters are * the Length, precision and Scale. The input decimal field is passed * via "DataBuffer" and the output String is passed via "result".
Index A adding a column with ALTER TABLE, 6-4 adding a constraint with ALTER TABLE, 6-4 ALTER TABLE statement used to add a column, 6-4 used to add a constraint, 6-4 used to drop a constraint, 6-4 ANSI setting in full preprocessing mode, 1-3, 1-10 in POSIX full preprocessing mode, 1-16 in POSIX static conversion mode preprocessing, 1-17 in POSIX syntax checking mode preprocessing, 1-18 in syntax checking mode preprocessing, 1-12 authorization authorize once per session ag, 3-3 for full preprocessing mode,
use with static conversion mode preprocessing, 3-3 default data formats for dynamic parameters, 4-39 default data types use with dynamic parameters, 4-37 deferring constraint error checking for referential constraints, 6-6 for row level integrity, 6-6 introduction to, 6-6 de ning a constraint for a view, 6-5 de nition procedure cursor, 5-2 select cursor, 5-2 de nitions dynamic application, 3-2 dynamic parameter, 4-1 dynamic statement, 3-1 multi-connect functionality, 7-1 multi-transaction mode, 7-1 procedur
executing a procedure with a return status code, 5-13 host variable declaration for a procedure, 5-10 preparing a statement with dynamic parameters, 4-2 returning a built-in variable from a procedure, 5-17 using a BULK INSERT statement with dynamic parameters, 4-27 using data structures and data bu ers to process a prepared statement with dynamic parameters, 4-19 using the PRINT statement, 5-19 example in COBOL calling a procedure from an application, 5-11 checking for all errors and warnings on return from
and preprocessing, 1-5 and the FIPS agger, 2-3 explained, 2-3 indicator variables introduction to, 9-1 use in expressions, 9-2 in nite waits preventing with timeouts, 7-4 integrity constraints adding, 6-4 deferring, 6-6 de ning and dropping for a table, 6-3 de ning and dropping for a view, 6-5 dropping, 6-4 error checking defaults, 6-6 example of resolving circular referential constraints, 6-8 introduction to, 6-1 locating errors, 6-7 locating multiple column referential constraint errors, 6-8 locating mult
introduction to, 1-15 PREPARE statement use with dynamic parameters, 4-1 preprocessing DBEFileSetName overridden, 1-3 introduction to static conversion mode, 1-10 introduction to syntax checking mode, 1-12 POSIX preprocessor invocation, 1-15 preprocessing and installing applications with multi-connect functionality, 7-2 preprocessing messages generated for procedures, 5-16 preprocessing session , 1-4 preprocessor accessing multiple DBEnvironments, 1-8 and CONNECT, 1-4 and DBEFileSet authority, 1-5 and DBEnv
row level integrity de ned, 6-1 RUN authority in full preprocessing mode, 1-4 in POSIX full preprocessing mode, 1-17 runtime messages generated for procedures, 5-16 S sample database authorities, 1-6 secondary references to non-standard objects and the FIPS agger, 2-3 sections not stored during full preprocessing, 1-3 select cursor available functionality, 5-2 de ned, 5-2 session in preprocessing, 1-4 SET CONSTRAINTS statement used to defer constraint error checking, 6-6 used to detect constraint errors, 6
using SQLUtil to see DBECon le timeout values, 7-2 transaction slots related to a wait queue, 7-2 U undetectable deadlocks possible in multi-transaction mode, 7-4 preventing with timeouts, 7-4 updatability and the FIPS agger, 2-3 implicit, 2-3 UPDATE STATISTICS before preprocessing, 1-5 V VALIDATE statement introduction to, 11-1 not for sections never validated under F.
