DATABASE 2 API REFERENCE PRECOMPILER APIS SUPPLEMENT VERSION 2 August 30, 1995 ---------------------------------------------------------------------- DATABASE 2 API REFERENCE PRECOMPILER APIS SUPPLEMENT This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties and any statements provided in this manual should not be interpreted as such. Order publications through your IBM representative or the IBM branch office serving your locality or by calling 1-800-879-2755. (C) COPYRIGHT INTERNATIONAL BUSINESS MACHINES CORPORATION 1993. ALL RIGHTS RESERVED. Note to U.S. Government Users -- Documentation related to restricted rights -- Use, duplication or disclosure is subject to restrictions set forth in GSA ADP Schedule Contract with IBM Corp. +--- NOTE -----------------------------------------------------------+ | | | Before using this information and the product it supports, be sure | | to read the general information under "Notices." | | | +--------------------------------------------------------------------+ ------------------------------------------------------------------------- Notices Any reference to an IBM licensed program in this publication is not intended to state or imply that only IBM's licensed program may be used. Any functionally equivalent product, program or service that does not infringe any of IBM's intellectual property rights may be used instead of the IBM product, program, or service. Evaluation and verification of operation in conjunction with other products, except those expressly designated by IBM, is the user's responsibility. IBM may have patents or pending patent applications covering subject matter in this document. The furnishing of this document does not give you any license to these patents. You can send license inquiries, in writing, to the IBM Director of Licensing, IBM Corporation, 500 Columbus Avenue, Thornwood, NY, 10594 USA. This publication may contain examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental. Licensees of this program who wish to have information about it for the purpose of enabling: (i) the exchange of information between independently created programs and other programs (including this one) and (ii) the mutual use of the information which has been exchanged, should contact: IBM Canada Limited Department 071 1150 Eglinton Ave. North York, Ontario M3C IH7 CANADA Such information may be available, subject to appropriate terms and conditions, including in some cases, payment of a fee. ------------------------------------------------------------------------- Chapter 1. Precompiler Customization An SQL precompiler examines source code and processes SQL statements to generate modified source code for your application program. This document explains how you can create your own precompiler to support additional features or languages, using the Precompiler Services interface provided by DB2. It is recommended that you read one or more of the following sections in the Application Programming Guide: "Programming in C and C++", "Programming in FORTRAN", or "Programming in COBOL", to become familiar with the use of the existing DB2 precompilers. As well, you should study some of the sample programs provided with DB2, and compare the contents of the precompiler output with the original source. This will help understand what the precompiler must do in different circumstances. 1.1 Designing a Precompiler To create an effective precompiler, you need to understand the tasks of several database manager services. The following is an overview of the precompilation process. You can then examine each interrelated service. The description of a processing model follows. Use this model to ensure that your precompiler is both efficient and complete. 1.1.1 Precompilation Process There are four participants in creating a successful application program: o The Application Programmer o The Precompiler o Precompiler Services o Runtime Services. Each has a distinct list of responsibilities. During the development process, these duties complement each other. This division provides three distinct benefits for your application development: o Programmers can make full use of the features of their preferred host language. o Programmers do not need to learn a new interface to access the DB2 kernel. o Additional languages can be added to the list of those supported by the database manager. The following steps describe what happens to an SQL statement from its creation to its execution. You can see what is required at each step of the process. 1. The programmer creates an application and embeds an SQL statement in the source code. 2. The precompiler identifies SQL elements in the source code. It processes the statement to prepare it for Precompiler Services. 3. Precompiler Services compiles the processed SQL statement. It stores the processed statement in a bind file and the compiled statement in a section of the package. Precompiler Services then defines the tasks required to successfully execute the SQL statement at run time. 4. The precompiler creates host language code, generally consisting of Runtime Services function calls to support these tasks and inserts this code in the modified source file. 5. When the application executes, the Runtime Services function calls communicate with the database manager to process the SQL statements. This procedure occurs for each SQL statement in the source being precompiled. During this process, the precompiler copies non-SQL code directly into the modified source file. The application programmer then compiles and links this modified file into the final form. Although this sequence sounds simple, writing a precompiler requires a significant design effort and a solid understanding of text translation techniques. The following explains the responsibilities of each participant in greater detail. 1.1.1.1 Application Programmer The application programmer has two responsibilities: o Construct correct SQL statements. See the SQL Reference for a discussion on constructing SQL statements. o Choose appropriate precompilation options. The precompiler must collect a number of options, values, and names from the user. The precompiler passes this data as parameters to Precompiler Services. Each of the necessary parameters are discussed as Precompiler Services and Runtime Services APIs are introduced. 1.1.1.2 Precompiler The precompiler has the following responsibilities: o Create necessary data structures o Translate the application source file into a modified source file o Process host variable declarations o Process SQL statements o Construct Runtime Services function calls. The precompiler is pivotal to simplifying application development for the application programmer. With a properly constructed precompiler, the application programmer does not need to create code for direct access to the database manager. The precompiler translates SQL requests to host-language calls. 1.1.1.3 Precompiler Services Precompiler Services has the following responsibilities: o Validate and compile SQL statements o Identify how each host variable is used in the SQL statement o Identify tasks required to support the SQL statement o Create a bind file and package. VALIDATE AND COMPILE SQL STATEMENTS Precompiler Services calls the database manager for a full syntactic and semantic check of the SQL statement. The kernel compiles the statement and stores it in a section of the package. IDENTIFY HOW EACH HOST VARIABLE IS USED IN THE SQL STATEMENT While compiling an SQL statement, Precompiler Services determines how all host variables are used (i.e., for input or for output, or as indicator variables). It provides a usage code for each host variable. IDENTIFY TASKS REQUIRED TO SUPPORT THE SQL STATEMENT After processing SQL statements, Precompiler Services creates a list of tasks called a task array. The precompiler converts tasks in this array to function calls in the application program. The precompiler constructs host language calls to allocate an SQLDA, for example. It then inserts these calls into the modified source file. The application program completes these tasks to execute the statement. CREATE A BIND FILE AND PACKAGE Precompiler Services processes each executable SQL statement into a separate section of the package. The sections are collectively referred to as a package. Each precompiled source module has its own package. If the application programmer chooses to defer binding (the act of creating a package in the database), the processed SQL statements are stored in a bind file. The DB2 bind utility uses the bind file to create the package at a later time. This allows an application to use different databases without additional precompilation, since the bind file can be bound multiple times against different databases. 1.1.1.4 Runtime Services Runtime Services APIs support communication between the application program and the database manager. Runtime Services has three responsibilities: o Initialize and validate the SQL Communication Area (SQLCA) and SQL Description Areas (SQLDA) o Manipulate SQLDAs o Provide a functional interface to the database manager. Runtime Services calls the database manager to process compiled SQL statements. It also modifies the input and output SQLDA and passes dynamic SQL statements to the database manager. 1.1.2 Processing Model The following text examines the responsibilities of the precompiler in detail. It also explores precompiler design and language considerations. 1.1.2.1 Precompiler Design There are several ways to structure a precompiler. Three approaches are discussed: o The Statement Oriented Model o The Compiler Model o The Hybrid Model. THE STATEMENT ORIENTED MODEL You can design a precompiler to process source code one line at a time. This statement-oriented model examines each line for host variable declarations and SQL statements. This design presents the precompile process in a way that is easy to understand. It may work best with line-oriented languages, such as FORTRAN. But the drawbacks are significant. This design demands too much text rescanning to be efficient. Multi-line constructs and streaming languages, such as C, are difficult to handle. THE COMPILER MODEL Another method is to create a precompiler that works like a compiler. The precompiler would use a parser and scanner to tokenize and process the input file. You could create production rules to identify and process host variable declarations and SQL statements. This model might require the precompiler to understand the complete host language syntax just for statement recognition. The precompiler would spend too much time processing non-SQL code. This is also not very efficient. THE HYBRID MODEL The hybrid model is a compromise between the other two. Ideally, the precompiler should copy non-SQL code directly into the modified source file. One solution is a state-based scanner. The scanner could understand various text modes such as plain text, comments and strings, and perform only minimal tokenization. It should only recognize SQL-related keywords in plain text areas (i.e., not in the middle of comments or strings.) The precompiler also needs to process SQL statements as they are found. The entire statement must be processed at once. With a statement-oriented scanner, the entire statement can be identified. But additional semantic actions must occur before the statement is processed. An intelligent scanner or a simplified parser could discern the changes needed. Depending on the host language, the precompiler may need to parse host variable declarations. Languages such as C have a complex syntax for variable declaration. You may have to process declarations and save contextual information as you go. Although this model begins to answer some important design considerations, it is not the only solution. Base your precompiler design on your understanding of the precompiling process and the requirements of your situation. 1.1.2.2 Language Considerations Your precompiler can be written in any language. Some languages offer more facilities to create an efficient precompiler. C is a good choice because it offers the following: o String manipulation o User-defined data structures o Indirect addressing using pointers o Dynamic allocation of storage. The most important consideration is the ability of the language to call Precompiler Services. The precompiler should be able to pass structures that are arrays of pointers and other data objects. If the language does not support pointers, you may have a difficult time. Precompiler Services also uses signed and unsigned data types. Consider these factors before you choose a language. 1.1.2.3 Precompiler Responsibilities The following outlines the tasks of a successful precompiler. This is a generic list. You may need to add or subtract from it as you see fit. These tasks can be completed as follows. o Create necessary data structures. The precompiler uses the following structures: - Program identification string (PID) - Option array - SQLCA - Host variable name array - Token ID array - Task array - SQL flagger diagnostics structure. o Translate source code into modified source code. The precompiler copies all non-SQL source code verbatim into the modified source file. It is important to maintain the integrity of the original application code. o Process host variables. To process host variables, the precompiler: - Detects host variable declarations - Determines SQL data type, variable length, and other information - Assigns unique token IDs to each host variable - Maintains the host variables in a symbol table - Declares the variables to Precompiler Services. Your precompiler stores information about each host variable. You need this information to generate function calls in the modified source file. o Process SQL statements. To process SQL statements, the precompiler: - Detects SQL statements - Includes the statement as a comment in the modified source file - Removes comments from the statement - Replaces EXEC SQL keywords and the statement terminator with blanks - Replaces non-blank white space with blanks - Detects host variables in the SQL statement - Places host variable IDs in the Token ID Array - Replaces SQL statement host variable names with blanks - Passes preprocessed SQL statements to Precompiler Services for compilation. By processing SQL statements into this form, the precompiler turns language-specific statements into language independent statements. Precompiler Services can then process each statement without knowing what host language is used. o Construct host language function calls. Once Precompiler Services compiles the SQL statement, it returns a sequence of functions and values to the precompiler in the task array. These values define the required calls. The precompiler inserts necessary Runtime Services function calls in the modified source file, based on the contents of the task array. 1.2 Writing a Precompiler The sequence of tasks a precompiler must perform are described in the following text. It does not discuss the user interface. You can design an interface appropriate for your needs. 1.2.1 Initialization Before the precompiler reads the first byte of program input, it should perform the following initialization tasks: INITIALIZE THE PRECOMPILER: Allocate the SQLCA, set the precompiler break handler, process command line arguments, open files, and set up the option array. A database connection must be established before calling Precompiler Services initialization (sqlainit). INITIALIZE PRECOMPILER SERVICES: Call sqlainit with initialization data and the option array. PROCESS RETURN DATA FROM SQLAINIT: Check the SQLCA, generate Program ID data in modified source file. Figure 1 shows the tasks performed by Precompiler Services. +--------------------------------------------------------------------+ | | | Precompiler || Precompiler Services | | ----------- || -------------------- | | || | | Define SQLCA || | | Install Break Handler || | | Process Command Line || | | Arguments || | | Open Source and Modified || | | Source || | | Prepare Option Array || | | CONNECT to database || | | Call sqlainit || | | || | | | | | | program name | | +---------- bind file name ----+ | | option array | | | V | | || | | || Validate Option Array | | || Open Package | | || Open Bindfile (if | | || required) | | || Build Program ID | | || | | | | | +---------- program ID -------+ | | | sqlca | | V | | || | | Check return code and || | | SQLCODE || | | Place program ID in || | | modified source || | | || | | | | | +--------------------------------------------------------------------+ Figure 1. Initialization Tasks 1.2.1.1 Defining an SQLCA All Precompiler Services APIs use the SQLCA to report completion codes and other diagnostic information to the calling program. Define and allocate an SQLCA before calling Precompiler Services. See the SQL Reference for the structure of the SQLCA. You should not alter the SQLCA structure or member names, as programmers may wish to access the SQLCA directly, according to instructions found in other IBM manuals. 1.2.1.2 Handling Interrupts Precompiling can take a significant amount of time. The application programmer may decide to terminate the process by interrupting the precompiler. Precompiler Services detects user-initiated interrupts and returns the SQLCODE SQLA_RC_CTRL_BREAK (-4994) to the precompiler when these interrupts occur. You may want to provide your own interrupt routine by installing a signal handler in the precompiler. Precompiler Services gets control when an interrupt occurs. If the precompiler has installed a signal handler, the Precompiler Services handler invokes it before terminating. NOTE: Install the signal handler before the first sqlainit call occurs. If the precompiler installs its handler at any time after the first sqlainit call, the results of an interrupt are unpredictable. Although Precompiler Services maintains its own interrupt handler, the precompiler must still call Precompiler Services after an interrupt has occurred to properly terminate the precompilation session. Call sqlafini with the termination option set to discard the package or bind file. After an interrupt, Precompiler Services rejects all calls except sqlafini. After sqlafini has completed, call sqlainit to initiate a new precompilation session. 1.2.1.3 Processing Command Line Arguments A precompiler requires the following information: SOURCE FILE NAME The name of the file containing the source program being precompiled. This is not sent to sqlainit but is required by any precompiler. MODIFIED SOURCE FILE NAME The name of the file that will be created by the precompiler. This is not sent to sqlainit but is required by any precompiler. BIND FILE NAME The name of the bind file to be produced, if any. This is generally based on the source file name, but can be any valid file name with an extension of ".bnd". DATABASE NAME A short identifier specifying the alias of the database against which this program is to be precompiled. The precompiler CONNECTs to this database before calling sqlainit. PACKAGE NAME The name of the package to be created. This is generally based on the source file name, but can be any valid short identifier. OPTIONS These specify the date and time format, isolation level and record blocking behavior, among others. See 'Preparing the Option Array' below. You can allow users to specify these items on the command line or from some other method. In any case, the precompiler is responsible for validating these items. 1.2.1.4 Opening Files The precompiler opens the input source file and the output modified source file. The precompiler should not attempt to open the bind file. Precompiler Services performs this function. 1.2.1.5 Preparing the Option Array The precompiler uses the option array to pass options to Precompiler Services. The array contains a header, followed by pairs of 4-byte integers. The header consists of two 4-byte integers. The first integer gives the number of option pairs allocated. The second gives the actual number of options used. If M is the required number of options, you need to allocate at least 8 * (M+1) bytes of storage for the option array. The remaining pairs of integers specify options and option values. Each pair represents one option specification from the precompiler. See the section on the sqlaprep API in the DB2 API Reference for a list of the options supported by the DB2 precompilers. Precompiler Services supports all the options listed there, except for the following, which are implemented by the various precompilers: BINDFILE (*) SQLCA MESSAGES SQLERROR NOLINEMACRO TARGET OPTLEVEL WCHARTYPE (**) PACKAGE (*) (*) BINDFILE and PACKAGE are implemented with different option values in Precompiler Services as compared to the sqlaprep API. See below. (**) WCHARTYPE is used only for C applications, and affects the contents of the sqla_runtime_info structure. See 1.4.2.2, "Runtime Information Structure" on page 2. The following two options interact to control the creation of bind files and packages: SQLA_BIND_FILE (3) with: value SQLA_CREATE_BIND_FILE (1): - a bind file is created with the name specified in the bind file argument to sqlainit. value SQLA_NO_BIND_FILE (0): - no bind file is created; the bind file argument to sqlainit is ignored. value SQLA_SQLERROR_CONTINUE (2): - similar to SQLA_CREATE_BIND_FILE, but should be used if the user has also requested 'SQLERROR CONTINUE' behavior (see the PRECOMPILE command in the Command Reference for details.) SQLA_ACCESS_PLAN (2) with: value SQLA_CREATE_PLAN (1): - a package is created with the name specified in the program_name argument to sqlainit. value SQLA_NO_PLAN (0): - no package is created; the program_name argument to sqlainit is ignored. value SQLA_SQLERROR_CONTINUE (2): - similar to SQLA_CREATE_PLAN, but should be used if the user has also requested 'SQLERROR CONTINUE' behavior (see the PRECOMPILE command in the Command Reference for details.) value SQLA_NO_PLAN_SYNTAX (3): - similar to SQLA_NO_PLAN, but should be used if the user requires a check of statement syntax, but no package or bind file creation. See the SYNTAX option under PREP in the Command Reference. Note that behavior of the SQL flagger (see the SQLA_FLAG_OPT option under sqlaprep in the DB2 API Reference) depends on the setting of the SQLA_BIND_FILE and SQLA_ACCESS_PLAN options. From the DB2 precompiler interface (i.e., the DB2 PREP command, or the sqlaprep API), specifying one of the variants of the SQLFLAG option alone will suppress bind file and package creation, and SQL statements will only be verified against the chosen DB2 for MVS syntax, and not against workstation DB2. This corresponds to SQLA_ACCESS_PLAN with SQLA_NO_PLAN, and SQLA_BIND_FILE with SQLA_NO_BIND_FILE, in Precompiler Services. (If desired, a package and/or bind file can be created during SQL flagging, simply by specifying the SQLA_ACCESS_PLAN and SQLA_BIND_FILE options with the appropriate values.) If, on the other hand, the user requires syntax checking against both DB2 for MVS and workstation DB2 without package or bind file creation, this can be achieved by combining the SQLA_FLAG_OPT option and the SQLA_NO_PLAN_SYNTAX value of the SQLA_ACCESS_PLAN option. 1.2.1.6 Initializing Precompiler Services Using sqlainit The precompiler initializes Precompiler Services by calling sqlainit. See 1.5.1.14, "INITIALIZE PRECOMPILER SERVICES" for a description of this API. 1.2.1.7 Testing the Return Code from sqlainit Precompiler Services passes any errors or warnings returned from sqlainit through the SQLCA, but first Precompiler Services attempts to validate the SQLCA itself. The return code from sqlainit shows whether the SQLCA address was valid and whether the structure is long enough to contain an SQLCA. The return code may be set to the following values: SQLA_CHECK_SQLCA (0) Check the sqlca.sqlcode element for the completion code. SQLA_SQLCA_BAD (-1) The address of the SQLCA passed to the function was not valid; the command was not processed. 1.2.1.8 Processing the Program ID The program ID is a string of alphanumeric data used to identify the program being precompiled, the userid performing the precompilation, the date and time when precompilation occurred, etc. On initialization, Precompiler Services creates a program ID and returns it to the precompiler. The precompiler generates the appropriate host language code to declare a character array in the modified source file and initialize it with the program ID. At run time, the variable that contains this data is used as an input parameter to the sqlastrt function. 1.2.1.9 Errors That Require Reinitialization When sqlainit is successful, Precompiler Services returns 0 to SQLCODE in the SQLCA. If it was not successful, you find an error code. If a fatal error occurs while executing any Precompiler Services API, terminate Precompiler Services with a call to sqlafini. Reinitialize with an sqlainit call before continuing. See 1.6, "Error Messages and Codes," which indicates which errors are considered fatal. 1.2.2 Source Processing The following describes tasks related to processing the input source file and generating the modified source file. 1.2.2.1 Copying Non-SQL Code Your precompiler should copy all non-SQL code directly into the modified source file. It is important to maintain the integrity of the application program. While copying the non-SQL code, the precompiler searches for the key words EXEC SQL. The precompiler only recognizes these key words if they appear on the same line, separated by one blank. You may want to relax these restrictions. These key words are part of the ANSI standard, however, you may wish to use some other way to identify SQL statements. Note that if the keyword pair EXEC SQL appears in a comment or a string, it should be ignored. Your precompiler should not recognize comments or string characters as valid key words. Make sure your logical scanning rules properly enter and exit all comment and string variations. 1.2.2.2 Precompiler Tasks for Host Variables When Precompiler Services compiles a BEGIN DECLARE SECTION statement, it tells the precompiler to begin processing host variables. The following describes what a precompiler should accomplish with host variables. The precompiler should copy host variable declarations directly into the modified source file. It also needs to record the host variable in a symbol table and register it with Precompiler Services. When the precompiler detects a host variable, it does the following: o Determines if the host variable is SQL compatible o Determines the SQL type o Adds information to the host variable symbol table o Calls Precompiler Services function sqlaalhv o Checks for successful completion. The precompiler continues processing host variables until an END DECLARE SECTION statement is detected. 1.2.2.2.1 ACCEPTABLE HOST VARIABLES: Only certain host variable data types are compatible with SQL columns. To be recognized as an SQL host variable, the variable must conform with the SQL variable declaration syntax of each host language. See the Application Programming Guide for examples of language-specific variable formats that are compatible with SQL. The precompiler determines if the declared host variable is acceptable. If not, the precompiler can issue an error, or perhaps ignore the declaration. It is up to the precompiler implementor to decide what host variable declaration syntax will be accepted. Typically, the precompiler accepts declarations that are valid in the target host language (e.g., C declarations in a C precompiler.) However, the precompiler may accept non-host language declarations and map them to proper host language in the modified source file. This may simplify parsing, or may provide a way for the user to express a semantic difference between host variables whose declarations would otherwise be syntactically identical in the host language. For example, C does not provide a way to indicate at declaration time that a character array does not contain a NUL terminator, that it should be treated as a fixed-length character string. A C precompiler could recognize some extra syntax and assign it to the SQL type 452 (fixed-length string) instead of type 460 (C NUL-terminated string). This fictional precompiler might successfully process the following: exec sql begin declare section; FIXED_CHAR a[10]; /* type 452 */ char b[10]; /* type 460 */ exec sql end declare section; and then give the modified source: /* exec sql begin declare section; */ /*FIXED_CHAR*/ char a[10]; /* type 452 */ char b[10]; /* type 460 */ /* exec sql end declare section; */ The 'pseudo-declaration' technique is used extensively for Large Object declarations. See "Large Objects" below for details. Figure 2 shows some examples of host variable declarations. In these examples, all three declarations are legal in C. The variables "number" and "mystruct1" would be valid with a basic C precompiler, since they are each recognized as an atomic SQL type. The variable "mystruct2" would only be valid to a more sophisticated precompiler implementing structure support. This feature allows the precompiler to recognize declarations of structures which are not themselves atomic SQL types, but which are composed of them. See 1.3.4, "Support for Structure Host Variables" for more details about structure support. ---------------------------------------------------------------------- EXEC SQL BEGIN DECLARE SECTION; short number; /* "number" is recognized as a */ /* SMALLINT host variable by */ /* the precompiler. */ struct { /* "mystruct1" is recognized as */ short number; /* a VARCHAR host variable by */ char mydata[30];/* the precompiler. */ } mystruct1; struct { /* "mystruct2" is NOT recognized */ char mydata[30];/* as a host variable by the */ short number; /* precompiler, unless structure */ } mystruct2; /* support is implemented. */ /* This will cause a precompilation error. */ EXEC SQL END DECLARE SECTION; ---------------------------------------------------------------------- Figure 2. C Language Host Variable Examples 1.2.2.2.2 DETERMINING SQL TYPE: Each acceptable host variable has a corresponding SQL data type. The precompiler writer must determine what host language declaration(s) is/are equivalent to each SQL type. The precompiler then recognizes those declarations and maps them to the appropriate SQL types. The SQL Reference lists all SQL types, and the Application Programming Guide shows how they are declared in the languages currently supported by the precompilers provided by DB2. 1.2.2.2.3 LARGE OBJECTS: DB2 supports Large Object (LOB) SQL types, which are essentially large-capacity character and byte strings. These are typically declared with the 'pseudo-declaration' mechanism mentioned earlier. The syntax "SQL TYPE IS " is used in these declarations, where "" is one of: o BLOB(size) o CLOB(size) o DBCLOB(size) o BLOB_LOCATOR o CLOB_LOCATOR o DBCLOB_LOCATOR o BLOB_FILE o CLOB_FILE o DBCLOB_FILE "size" is the size in bytes for BLOBs and CLOBs, and in double-byte characters for DBCLOBs. Refer to the SQL reference for details on the nature and use of LOB SQL types. These types are of particular concern to the precompiler writer because the pseudo declarations in the input source file must be replaced by equivalent declarations in the host language. Refer to the Application Programming Guide for information on how these declarations map to their equivalents in C, COBOL and FORTRAN. Note that the LOB and LOB file declarations map to structure declarations in the host language. The names of the structure members are generated by the precompiler, but must be predictable by the user, since the application must be able to reference them. For example, in C, exec sql begin declare section; static sql type is clob(100) foo; exec sql end declare section; maps to /* exec sql begin declare section; */ static /* sql type is clob(100) */ struct foo_t { unsigned long length; char data[100]; } foo; /* exec sql end declare section; */ In this example, "foo" becomes a structure name, with members "length" and "data". The precompiler could have named the members however it liked, but the application programmer must know what name to use, so that "foo.length", for example, can be referred to. Note that in languages like COBOL, where structure members are part of the global name space, the structure members should have names which make them uniquely identifiable, for example "FOO-LENGTH" and "FOO-DATA". The LOB file structure has more fields, but the principal is the same. The LOB locator declarations become simple 4-byte integer declarations in the host language. 1.2.2.2.4 RECORDING HOST VARIABLES: The precompiler assigns a unique 4-byte token ID to each declared host variable. The token ID may actually be a pointer or an array index, or anything else. For Precompiler Services, the only consideration is that the token ID be unique for each host variable. Precompiler Services refers to variables only by token ID, however, host variable names passed to Precompiler Services must also be unique. Variable names may be up to thirty characters in length. Precompiler Services uses host variable information to setup SQLVAR elements in SQLDA structures. Because of this, the precompiler must be able to provide the following information: o Token ID o Variable name length o Variable name o SQL type o Variable data length o Location code. You can only use various techniques to create a unique token ID and determine variable name length. The precompiler should maintain a host variable symbol table to provide a cross-reference between token IDs and variable names. Storing the variable name itself poses some additional problems. The name recorded must not contain any operators. If the variable is declared as "short *number" as in C, the name should be recorded as "number". Store the operator separately with the token ID as the key. The operators used with host variables must be able to be retrieved by the precompiler when the calls to Runtime Services are generated by the precompiler. NOTE: The name can use characters from the database manager's extended character set. A host language can allow characters that are not part of that extended set. Remove those characters and replace them with valid characters before sending the name to Precompiler Services. The precompiler should check the SQLCA before retrieving messages. Replace the original characters before displaying error or warning messages. Otherwise variable names with replacement characters may appear in your messages. The SQLCODEs which currently contain host variable names are SQL0104N, SQL0303N, SQL0307N, SQL0312N, SQL0324N and SQL4942N. Refer to 1.6, "Error Messages and Codes" for more details. Determine SQL data type and variable length from the host variable declaration. For non-graphic datatypes, variable length is the actual length of the host variable in bytes. For example, the length of a short integer is 2. For graphic datatypes, such as "PIC G(xx)" in COBOL, the length is the number of double-byte characters, not the number of bytes. For the DECIMAL datatype, length is determined by placing the declared precision of the variable in the lower-address byte of the length field, and the scale in the higher-address byte. In COBOL, a declaration like "PIC S9(7)V99 COMP-3" has precision 9 and scale 2. Assembled in a temporary short integer for calculating the length, this becomes: Offset 0 +----------------+ | precision = 9 | 1 +----------------+ | scale = 2 | +----------------+ If the above is again viewed as a short integer, it becomes a value of 9*256+2=2306 on "Big Endian" (non-Intel) systems, and 2*256+9=521 on "Little Endian" (Intel) systems. The location code tells Precompiler Services where the host variable was declared. Host variables found in an SQL statement, not previously declared in an SQL declare section, are assumed to be SQLDA structures for dynamic SQL statements. 1.2.2.2.5 REPORTING HOST VARIABLES THROUGH SQLAALHV: Once the above information has been determined, the precompiler calls Precompiler Services through the sqlaalhv API. This API provides Precompiler Services with the information it uses to compile SQL statements with host variables. See 1.5.1.2, "ADD HOST VARIABLE API" for a description of this API, its arguments, and valid completion codes. The return code from sqlaalhv reports the validity of the SQLCA structure. If it reports that the SQLCA is valid, check SQLCODE for the completion status of the sqlaalhv API. 1.2.2.2.6 PROCESSING HOST VARIABLES OUTSIDE THE DECLARE SECTION: Usually, the precompiler calls sqlaalhv when processing a host variable declaration. There is a special circumstance where the precompiler calls sqlaalhv while processing an SQL statement. When the precompiler finds an undeclared host variable identifier in an SQL statement, the precompiler assumes that the identifier represents an SQLDA structure. It should call sqlaalhv with location set to SQLA_SQL_STMT (1), and the sqltype and sqllen pointers set to NULL. If a second undeclared host variable is found in the statement, the precompiler must consider this an undeclared host variable error because only one SQLDA name can occur in a statement. 1.2.2.3 Processing SQL Statements A statement consists of all tokens found between the EXEC SQL keywords and the SQL statement terminator. This is exclusive of the begin and end tokens themselves, and any line continuation tokens, comments or other host language artifacts. 1.2.2.3.1 IDENTIFYING SQL STATEMENTS: The precompiler identifies statements by recognizing the EXEC SQL keyword pair at the beginning of an SQL statement. It returns to scanning text after the statement terminator has been found and the statement has been completely processed. SQL statement terminators may be language and product specific. As a guideline, the ANSI standard proposes the following terminators: LANGUAGE TERMINATOR C/C++ semicolon (;) PASCAL semicolon (;) COBOL END-EXEC keyword FORTRAN End of a line with no continuation. Using these rules, SQL statements embedded in C programs have the following syntax: EXEC SQL ; IN COBOL, SQL statements have this syntax: EXEC SQL END-EXEC Statement termination processing is affected somewhat by compound SQL. Refer to 1.3.1, "Compound SQL" for more information. SQL statements can span lines using continuation tokens available in the host language. In the C precompiler, statements span lines until they are terminated, according to usual C convention. Host language rules regarding token continuation should apply as well. 1.2.2.3.2 COPYING SQL STATEMENTS TO MODIFIED SOURCE: The precompiler can copy SQL statements into the modified source as comments, in a format appropriate for the host language. Copying the statements enhances the readability of the modified source. It helps the application programmer determine which changes were made to the source file. For example, the C precompiler copies statements like this: /* */ 1.2.2.3.3 PREPROCESSING SQL STATEMENTS: The precompiler preprocesses each SQL statement before sending it to Precompiler Services for compilation. The precompiler removes non-SQL constructs such as comments, host variable operators and names, and non-blank white space characters. The precompiler replaces this material with blanks, on a character-for-character basis. In this way, the position of statement tokens does not change, and the precompiler may be able to construct more meaningful diagnostic messages. Recognizing Quoted Strings: During the scan of an SQL statement string, the precompiler must recognize quoted strings. Quoted strings in SQL are delimited by apostrophes or quotation marks. If a string delimiter appears within a string, it must be doubled, that is '' or "". SQL syntax does not allow nesting of strings or mixing of string delimiters, ' or ". During statement preprocessing, ignore all characters appearing within quoted strings, and do not alter material there. Replacing Statement Comments: There are two methods for embedding comments within SQL statements. The first method uses comment delimiters native to the host language. For example, C uses /* */ as comment delimiters. Note that some types of host language comment introducers, such as '//' in C/C++ and '!' in FORTRAN (which can both start in mid-line) may conflict with SQL syntax. You should be very cautious about supporting such host language comment introducers within SQL statements. Supporting them elsewhere within the input source file is less risky. Besides language-specific comment delimiters, ANSI SQL also provides comment delimiters for use within SQL statements. The format for ANSI SQL style is a double dash (--). It is followed by a string of 0 or more characters and terminated by a line end. To assist portability, precompilers should support ANSI SQL style comments appearing within an SQL statement. This is the preferred method of including comments in SQL statements. Blank out all comments (including their delimiters) appearing within the SQL statement text before issuing a compile request for that statement. Precompiler Services treats comment delimiters as invalid syntax. Removing Host Variable Identifiers and Operators: Legal syntax for host variables varies across different languages. For example, the form :A-B has one meaning in C and a different meaning in COBOL. In C, the form suggests a host variable A minus column B [the hyphen (-) is an operator]. In COBOL, the form might identify a single host variable A-B (the hyphen is part of the identifier). When the precompiler finds a host variable in an SQL statement, it blanks out the host variable in the statement string, looks up the host variable's token ID in the host variable symbol table and places the token ID in the token ID array. The colon that precedes a host variable is left in the SQL statement. This shows Precompiler Services that a host variable occupies that space. There is a one-to-one correspondence between colons in the SQL statement and entries in the token array ID prepared by the precompiler. For example, assume that the precompiler is processing the following SQL statement in C (here, the slash (/) represents a newline and the period (.) represents a blank): EXEC SQL SELECT A,B,C INTO :VAR_A:IND_A,:VAR_B:IND_B,:VAR_C FROM T WHERE A > :HV1 AND B < :HV2 AND C = :HV3; Removing the statement delimiters gives: "...SELECT.A,B,C/ ...INTO.:VAR_A:IND_A,:VAR_B:IND_B,:VAR_C/ ...FROM.T/ ...WHERE.A.>.:HV1.AND.B.<.:HV2.AND.C.=.:HV3" Removing host variables gives: "...SELECT.A,B,C/ ...INTO.:.....:.....,:.....:.....,:...../ ...FROM.T/ ...WHERE.A.>.:....AND.B.<.:....AND.C.=.:..." Keep a list of all operators associated with each host variable found in an SQL statement. The precompiler restores the operators before using the host variables in calls to Runtime Services. For example, the C precompiler allows the * operator for host variables declared as pointers. If a * operator prefixes the host variable in the SQL statement, it must also be used in the generated runtime calls. If a DECLARE CURSOR FOR