HP NonStop SQL/MX Programming Manual for C and COBOL Abstract This manual explains how to use embedded SQL for HP NonStop™ SQL/MX for C, C++, and COBOL. In NonStop SQL/MX, a C, C++, or COBOL program uses embedded SQL/MX statements to access HP NonStop SQL/MP and SQL/MX databases. Product Version NonStop SQL/MX Release 2.3 (SPR identifier for SQL/MX Release 2.3: H23) Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs and H06.
Document History Part Number Product Version Published 523627-003 NonStop SQL/MX Releases 2.0 and 2.1 June 2005 540434-001 NonStop SQL/MX Releases 2.0, 2.1, and 2.2 February 2006 544617-001 NonStop SQL/MX Release 2.3 August 2007 544617-002 NonStop SQL/MX Release 2.3 February 2010 544617-003 NonStop SQL/MX Release 2.
Legal Notices © Copyright 2010 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
HP NonStop SQL/MX Programming Manual for C and COBOL Index Examples Figures Tables Legal Notices What’s New in This Manual xvii Manual Information xvii New and Changed Information About This Manual xix Audience xix Organization xix Related Documentation xxi Examples in This Manual xxiv Notation Conventions xxiv Change Bar Notation xxvii HP Encourages Your Comments xvii xxvii 1. Introduction Referencing Database Object Names 1-1 NonStop SQL/MX Release 2.x 1-1 NonStop SQL/MX Release 1.
1. Introduction (continued) Contents 1. Introduction (continued) Processing Exception Conditions 1-10 Checking SQLSTATE 1-10 Using WHENEVER 1-10 Using GET DIAGNOSTICS 1-11 Ensuring Data Consistency 1-11 Compiling and Building an Application 1-12 Processing With Embedded Module Definitions Processing With Module Definition Files 1-12 General Instructions 1-13 SQL/MX Host Language Preprocessor 1-13 SQL/MX Compiler 1-14 Host Language Compiler 1-14 Program and Module Management 1-14 1-12 2.
Contents 3. Host Variables in C/C++ Programs (continued) 3.
Contents 4. Host Variables in COBOL Programs (continued) 4.
5. Simple and Compound Statements (continued) Contents 5. Simple and Compound Statements (continued) Using PROTOTYPE Host Variables as Table Names 5-17 6.
7. Static Rowsets (continued) Contents 7.
9. Dynamic SQL (continued) Contents 9. Dynamic SQL (continued) Deallocate the Prepared Statement 9-7 Using EXECUTE IMMEDIATE 9-7 Setting Default Values Dynamically 9-8 10.
11. Dynamic SQL Cursors (continued) Contents 11. Dynamic SQL Cursors (continued) Initialize the Dynamic Input Parameters 11-5 Open the Cursor 11-5 Retrieve the Values 11-5 Process the Retrieved Values 11-6 Fetch the Next Row 11-6 Close the Cursor and Deallocate the Prepared Statement Using Date-Time and INTERVAL Data Types 11-7 Standard Date-Time Example 11-7 Interval Example 11-8 Nonstandard SQL/MP DATETIME Example 11-9 Dynamic SQL Cursors Using Descriptor Areas 11-10 11-6 12.
. Exception Handling and Error Conditions (continued) Contents 13.
15. C/C++ Program Compilation (continued) Contents 15.
16. COBOL Program Compilation (continued) Contents 16.
A. C Sample Programs (continued) Contents A.
Examples (continued) Contents Examples (continued) Example A-5. Example A-6. Example A-7. Example A-8. Example A-9. Example A-10. Example A-11. Example B-1. Example C-1. Example C-2. Example C-3. Example C-4. Example C-5.
Figures (continued) Contents Figures (continued) Figure 16-4. Figure 17-1. ecobol or nmcobol Generating Module Definition Files Module Name Length 17-11 16-41 Tables Table 2-1. Table 2-2. Table 2-3. Table 2-4. Table 3-1. Table 3-2. Table 3-3. Table 3-4. Table 3-5. Table 3-6. Table 3-7. Table 3-8. Table 4-1. Table 4-2. Table 4-3. Table 4-4. Table 4-5. Table 12-1. Table 13-1. Table 13-2. Table 13-3. Table 15-1. Table 15-2.
Tables (continued) Contents Tables (continued) Table 16-1. Table 17-1. Table 17-2. Table 17-3.
Contents HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 xvi
What’s New in This Manual Manual Information HP NonStop SQL/MX Programming Manual for C and COBOL Abstract This manual explains how to use embedded SQL for HP NonStop™ SQL/MX for C, C++, and COBOL. In NonStop SQL/MX, a C, C++, or COBOL program uses embedded SQL/MX statements to access HP NonStop SQL/MP and SQL/MX databases. Product Version NonStop SQL/MX Release 2.3 (SPR identifier for SQL/MX Release 2.3: H23) Supported Release Version Updates (RVUs) This publication supports J06.
What’s New in This Manual • • • • • • Changes to the H06.20/J06.09 Manual Updated the description of the -h option on page 15-19 and on page 15-26. Added the -Q option under the following sections: ° Syntax for the OSS-Hosted SQL/MX C/C++ Preprocessor on page 15-17 and its description on page 15-21 ° Syntax for the Windows-Hosted SQL/MX C/C++ Preprocessor on page 15-23 and its description on page 15-27 Updated the description of Windows-Hosted SQL/MX C/C++ Preprocessor on page 15-21.
About This Manual This manual describes the NonStop SQL/MX programmatic interface for the ANSI C and COBOL languages. It also includes some C++ language constructs. With this interface, an application can access a database by using embedded SQL statements. Throughout this manual, references to NonStop SQL/MX Release 2.x indicate SQL/MX Release 2.3, and subsequent releases until otherwise indicated in a replacement publication.
Organization About This Manual Section 8, Name Resolution, Similarity Checks, and Automatic Recompilation Describes late name resolution, similarity check, and automatic SQL recompilations. Section 9, Dynamic SQL Introduces dynamic SQL and describes how to write dynamic SQL applications that prepare and execute statements with dynamic input parameters. Section 10, Dynamic SQL With Descriptor Areas Describes how to write dynamic SQL applications by using descriptor areas.
Related Documentation About This Manual Related Documentation This manual is part of the HP NonStop SQL/MX library of manuals, which includes: Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Quick Start Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual Installation Guides SQL/MX Installation and Management Guide NSM/web Installation Guide Connectivity Manuals SQL/MX Connectivity Service Manual Migration Guides SQL/MX Database and Application Migration Guide
Related Documentation About This Manual Introductory Guides SQL/MX Comparison Guide for SQL/MP Users Describes SQL differences between NonStop SQL/MP and NonStop SQL/MX. SQL/MX Quick Start Describes basic techniques for using SQL in the SQL/MX conversational interface (MXCI). Includes information about installing the sample database. Reference Manuals SQL/MX Reference Manual Describes the syntax of SQL/MX statements, MXCI commands, functions, and other SQL/MX language elements.
About This Manual Related Documentation Data Management Guides SQL/MX Data Mining Guide Describes the SQL/MX data structures and operations to carry out the knowledge-discovery process. SQL/MX Report Writer Guide Describes how to produce formatted reports using data from an SQL/MX database. DataLoader/MX Reference Manual Describes the features and functions of the DataLoader/MX product, a tool to load SQL/MX databases.
Examples in This Manual About This Manual These manuals are part of the SQL/MP library of manuals and are essential references for information about SQL/MP Data Definition Language (DDL) and SQL/MP installation and management: Related SQL/MP Manuals SQL/MP Reference Manual Describes the SQL/MP language elements, expressions, predicates, functions, and statements. SQL/MP Installation and Management Guide Describes how to plan, install, create, and manage an SQL/MP database.
About This Manual General Syntax Notation This data type is described under Interval Data Type on page 3-2. General Syntax Notation The following list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words; enter these items exactly as shown. Items not enclosed in brackets are required. For example: MAXATTACH lowercase italic letters. Lowercase italic letters indicate variable items that you supply.
About This Manual General Syntax Notation braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example: LISTOPENS PROCESS { $appl-mgr-name } { $process-name } ALLOWSU { ON | OFF } | Vertical Line. A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example: INSPECT { OFF | ON | SAVEABEND } … Ellipsis.
About This Manual Change Bar Notation Change Bar Notation Change bars are used to indicate substantive differences between this manual and its preceding version. Change bars are vertical rules placed in the right margin of changed portions of text, figures, tables, examples, and so on. Change bars highlight new or revised information. For example: The message types specified in the REPORT clause are different in the COBOL85 environment and the Common Run-Time Environment (CRE).
About This Manual HP Encourages Your Comments HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 xxviii
1 Introduction NonStop SQL/MX is a relational database management system (RDBMS) that uses SQL:1999 to define and manipulate data in an SQL/MX database. SQL-92 is the current ANSI version of SQL (structured query language). To access data, you execute SQL statements interactively by using the SQL/MX conversational interface (MXCI) or programmatically by embedding SQL statements in a host program written in ANSI C/C++ or COBOL.
Embedding SQL Statements Introduction SQLMP ALIAS statement within your application to create the needed mappings from logical to physical names: CREATE SQLMP ALIAS catalog.schema.name [\node.]$volume.subvol.filename When this statement is executed, a mapping is inserted as a row in the MPALIAS table. Examples of the CREATE SQLMP ALIAS statement appear in the SQL/MX Reference Manual.
Declaring and Using Host Variables Introduction modify the eld options to link the application to a DLL instead of an executable. For more information, see the DLL Programmer’s Guide for TNS/E Systems. For efficient management of module files, SQL/MX allows the modules to be located with the corresponding DLLs. The module files are managed in the following sequence: 1. When the application is executed, SQL/MX automatically identifies the location of all the DLLs loaded by the application. 2.
Using Host Variables Introduction In this example, HV_THIS_CUSTOMER and HV_CUSTNAME are host variables: COBOL EXEC 01 01 EXEC SQL BEGIN DECLARE SECTION END-EXEC. HV-THIS-CUSTOMER PIC 9(4) COMP. HV-CUSTNAME PIC X(18). SQL END DECLARE SECTION END-EXEC. Using Host Variables When you specify a host variable in an SQL statement, you must precede the host variable name with a colon (:). In a 3GL statement, you do not need the colon.
Declaring and Using Static SQL Cursors Introduction Examples In these C examples, a semicolon (;) ends an embedded SQL statement. In a COBOL program, the keyword END-EXEC ends an embedded SQL statement. • Single-row SELECT statement EXEC SQL SELECT custname INTO :hv_custname FROM sales.customer WHERE custnum = :hv_this_customer; C The result of the SELECT is placed into a host variable.
Declaring and Using Static SQL Cursors Introduction rows of the result table one at a time by using the cursor. Finally, after processing the rows, you release the result table when you close the cursor. Examples In these C examples, a semicolon (;) ends an embedded SQL statement. In a COBOL program, the keyword END-EXEC ends an embedded SQL statement. • DECLARE CURSOR EXEC SQL DECLARE get_customer CURSOR FOR SELECT custname, street, city, state, postcode FROM persnl.
Using Dynamic SQL Introduction • CLOSE statement EXEC SQL CLOSE get_customer; The CLOSE statement releases the result table established by OPEN for the cursor. See also Section 6, Static SQL Cursors. Using Dynamic SQL A static SQL statement is embedded in a host program and known at the time the host program is preprocessed. A dynamic SQL statement is either prepared dynamically with the PREPARE statement or executed through the EXECUTE IMMEDIATE statement.
Using Dynamic SQL Cursors Introduction • Deallocate resources held by the compiled statement and the SQL descriptor areas. See Section 9, Dynamic SQL. Using Dynamic SQL Cursors A dynamic SQL program that uses dynamic cursors typically includes declarations and statements to: • • • • • • Declare a host variable as a place to store the dynamic cursor specification. Prepare the cursor specification. Declare the cursor. Open the cursor. Retrieve the values at the cursor position. Close the cursor.
Declaring a Rowset Introduction Declaring a Rowset You declare a host variable array, along with its dimension, with the SQL Declare Section. A rowset array is declared for each column in a query. Each rowset array contains as many elements as are contained in the rowset. Example In this example, hvarray_jobcode and hvarray_jobdesc are host variable arrays to be used in a rowset: C EXEC SQL ROWSET ROWSET ...
Processing Exception Conditions Introduction Processing Exception Conditions Your host language program can detect exception conditions and diagnostics information after the execution of each SQL statement. For more details, see Section 13, Exception Handling and Error Conditions. To process exception conditions and diagnostics information: • • • Check the SQLSTATE variable. Use the WHENEVER declaration. Use the GET DIAGNOSTICS statement.
Using GET DIAGNOSTICS Introduction If the SELECT statement results in an SQLSTATE value of 02000 (no data) condition, the named error routine is executed. Using GET DIAGNOSTICS After the execution of an SQL statement, NonStop SQL/MX places information on exception conditions into the diagnostics area. The diagnostics area has a size limit, which is a positive integer that specifies the maximum number of conditions placed into the area during the execution of the statement.
Compiling and Building an Application Introduction Alternatively, you can commit changes automatically at the end of each SQL statement by using SET TRANSACTION AUTOCOMMIT ON at the beginning of your program. The default for embedded SQL is AUTOCOMMIT OFF. If you exit a program without using either of these methods, any uncommitted changes are automatically rolled back. See also Section 14, Transaction Management.
General Instructions Introduction C/C++ or COBOL and SQL statements and generates two files: an annotated source file and a module definition file (source-file.m) that contains the SQL source statements. You compile the source file with the host language compiler, and you compile the module definition file with the SQL/MX compiler (mxcmp). A module definition file is not created unless you choose the appropriate preprocessor options.
SQL/MX Compiler Introduction • • When the preprocessor recognizes an EXEC SQL, it finds the corresponding terminating semicolon (;) for C/C++ programs or the terminating keywords ENDEXEC for COBOL programs. For each embedded SQL statement, the preprocessor scans the statement to find host variable references and parses the statement to determine the required CLI calls.
2 Embedded SQL Statements You can access an SQL/MX database by embedding SQL statements in your host language program.
Embedded SQL Statements • • • Placement of SQL Statements SQL statements can contain SQL comments. SQL comments begin with a double hyphen (--) and end with the end of the line. Embedded SQL uses the continuation character of the language in which you are programming. SQL statements can contain host language comments: ° C comments have the form: /* ... */. The comment is not restricted to one line. ° COBOL comments have the form: * ...
Embedded SQL Statements • • Host Variable Declarations Code INVOKE directives that generate structure descriptions of tables or views within the Declare Section. Use END DECLARE SECTION to mark the end of the Declare Section. For a list of SQL statements allowed in the SQL Declare Section, see Table 2-2 on page 2-6. For detailed information on each statement and the proper syntax, see the SQL/MX Reference Manual.
Embedded SQL Statements Nonexecutable SQL Statements } }; // end of jobsql class definition Nonexecutable SQL Statements You can place a specific set of static SQL statements anywhere in an embedded C, C++, or COBOL program. However, these statements affect only the compilation of the static SQL statements that they precede. For a list of these static SQL declarations and statements, see Table 2-3 on page 2-7.
Embedded SQL Statements • • • • • • • Executable SQL Statements Diagnostics statement Data Definition Language (DDL) statements Data Manipulation Language (DML) statements Transaction control statements Object naming statements Data Control Language (DCL) statements Utilities (UPDATE STATISTICS) For a list of executable SQL statements, see Table 2-4 on page 2-8.
Embedded SQL Statements Embedded SQL Declarations and Statements time as an embedded SQL statement. The member function references host variables that are class data members. Embedded SQL Declarations and Statements These tables list all the SQL declarations and statements that you can embed in a 3GL program: • • • • Table 2-1 on page 2-6 describes the MODULE directive, which you should place at the beginning of a 3GL program.
Embedded SQL Declarations and Statements Embedded SQL Statements Table 2-3. Nonexecutable SQL Statements Statement Description Catalog and Schema Declarations DECLARE CATALOG* Sets default catalog for unqualified schema names in static SQL statements within a compilation unit. DECLARE SCHEMA* Sets default schema for unqualified object names in static SQL statements within a compilation unit.
Embedded SQL Declarations and Statements Embedded SQL Statements Table 2-4. Executable SQL Statements (page 1 of 5) Statement Description SQL Statements That Process Dynamic SQL ALLOCATE CURSOR Allocates an SQL cursor. DECLARE CURSOR Specifies a dynamic cursor in a host program and associates the name of the cursor with a query expression that specifies the rows to be retrieved by using the cursor. ALLOCATE DESCRIPTOR Allocates an input or output SQL descriptor area.
Embedded SQL Statements Embedded SQL Declarations and Statements Table 2-4. Executable SQL Statements (page 2 of 5) Statement Description Data Definition Language (DDL) Statements (continued) ALTER TRIGGER Changes the definition of an trigger. CREATE CATALOG * Defines a catalog. CREATE INDEX * Creates an index based on one or more columns in a table. CREATE PROCEDURE Defines an existing Java method as an SPJ in NonStop SQL/MX. CREATE SCHEMA Defines a schema.
Embedded SQL Declarations and Statements Embedded SQL Statements Table 2-4. Executable SQL Statements (page 3 of 5) Statement Description Data Manipulation Language (DML) Statements CLOSE Closes a cursor. DELETE Deletes rows from a table or view. FETCH Retrieves a row by using a cursor. INSERT Inserts rows into a table or view. OPEN Opens a cursor. SELECT Retrieves data from tables and views. UPDATE Updates values in columns of a table or view. BEGIN...
Embedded SQL Statements Embedded SQL Declarations and Statements Table 2-4. Executable SQL Statements (page 4 of 5) Statement Description Object Naming Statements (continued) SET MPLOC* Sets default Guardian volume and subvolume for unqualified physical object names in dynamic SQL statements that are prepared after this statement is executed. The NAMETYPE must be set to NSK for this command to work. CONTROL QUERY DEFAULT* Modifies the content of the SYSTEM_DEFAULTS table for the current process.
Embedded SQL Statements Considerations for Embedding DDL and DML Statements Table 2-4. Executable SQL Statements (page 5 of 5) Statement Description Utilities UPDATE STATISTICS Updates information on the content of a table and its indexes. Stored Procedures (SPJ) CALL Statement The CALL statement invokes a stored procedure in Java (SPJ) and can be embedded in a C, C++, or COBOL program. Both static and dynamic CALL statements are supported.
Embedded SQL Statements • • ANSI Compliance and Portability CONTROL QUERY SHAPE, which forces execution plans by modifying the operator tree for a prepared statement CONTROL TABLE, which specifies a performance-related option for DML accesses to a table or view For the syntax of CONTROL statements, see the SQL/MX Reference Manual. ANSI Compliance and Portability If program portability is important, note that CONTROL statements are SQL/MX extensions to the ANSI standard.
Embedded SQL Statements CONTROL, Flow Control Scope, and Dynamic SQL programs influence that CONTROL statement directives have on dynamic and static SQL statements. Note. As a rule, dynamic CONTROL statements apply to dynamic SQL statements that are prepared after the CONTROL statement is executed. If your program uses a PREPARE once and EXECUTE many strategy, the program must execute the CONTROL statement prior to the PREPARE statement.
Embedded SQL Statements CONTROL, Flow Control Scope, and Dynamic SQL programs The preceding static CONTROL QUERY statement does not apply to this dynamic statement when the user enters this dynamic SQL statement, and the default TABLELOCK strategy (SYSTEM) is used. The second time through the loop, the user enters: CONTROL QUERY DEFAULT TABLELOCK 'ON'; When the user enters this dynamic CONTROL statement, it is executed dynamically.
Embedded SQL Statements CONTROL, Flow Control Scope, and Dynamic SQL programs HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 2- 16
3 Host Variables in C/C++ Programs Host variables are data items declared in a host application program and used in both host language statements and embedded SQL statements. They provide communication between SQL statements and the host language statements. An input host variable transfers data from a host language program to an SQL/MX database, and an output host variable transfers data from a database to the program.
C Host Variable Data Types Host Variables in C/C++ Programs struct employee { unsigned short empnum; char first_name[16]; char last_name[21]; unsigned short deptnum; unsigned short jobcode; } employee_info; ... EXEC SQL END DECLARE SECTION; /* structure */ C Host Variable Data Types You must explicitly declare all host variables used in SQL statements. A host variable used in an SQL statement must be declared in an SQL Declare Section prior to the first use of the host variable in an SQL statement.
Host Variables in C/C++ Programs Character Host Variables variables with the KANJI or KSC5601 character set in an SQL/MX application only to access KANJI or KSC5601 columns in an SQL/MP table. C-identifier specifies that the variable can hold a fixed-length character (or code_units) string. The maximum length of the string is specified in the length field. The length l + 1 is required and must be enclosed in square brackets. The length l corresponds to the length of the column value.
Host Variables in C/C++ Programs Date-Time and Interval Host Variables The length l + 1 is required and must be enclosed in square brackets. The length l corresponds to the maximum length of the column value. To allow for the null terminator, add 1 (one) to the length for the declaration of the host variable. C-initial-value is a valid string literal in C/C++. The initial value should be of the same type as the C array type translated by the SQL/MX preprocessor.
Numeric Host Variables Host Variables in C/C++ Programs TIMESTAMP [(n)] specifies the data type of target host variable for a date-time column that, without the optional n precision, contains a timestamp in the external form: yyyy-mm-dd hh:mm:ss The n precision is a positive integer that specifies the number of digits in the fractional seconds, as shown in bold text: yyyy-mm-dd hh:mm:ss.msssss The default for precision is 6, and the maximum is 6.
Host Variables in C/C++ Programs Floating-Point Host Variables PIC[TURE] [S]9(l-s)V9(s) COMP is the same as {[signed] | unsigned} NUMERIC (p, s). {[signed] | unsigned} DECIMAL(l, s) specifies the data type of a target host variable for a column of one of these SQL data types: DECIMAL (l, s) [SIGNED|UNSIGNED] PIC[TURE] [S] 9(l-s) V9(s) DISPLAY [SIGN IS LEADING] The length l corresponds to the number of digits in the column value. The scale s corresponds to the scale of the column value.
Host Variables in C/C++ Programs Using Corresponding SQL and C Data Types float specifies the data type of a target host variable for a column of this SQL data type: REAL Tandem floating-point REAL data type is stored with 22 bits of precision and 9 bits of exponent. The precision corresponds to the precision of the column value. IEEE floating-point format REAL is stored in 4 bytes with 23 bits of binary precision and 8 bits of exponent.
Using Corresponding SQL and C Data Types Host Variables in C/C++ Programs Table 3-1.
Extended Host Variable Data Types and Generated C Data Types Host Variables in C/C++ Programs Table 3-2.
Host Variables in C/C++ Programs Extended Host Variable Data Types and Generated C Data Types Table 3-4.
Data Conversion Host Variables in C/C++ Programs Table 3-4.
Host Variables in C/C++ Programs Specifying Host Variables in SQL Statements NonStop SQL/MX converts data between signed and unsigned numeric types and between numeric types with different precisions. Note that if a signed numeric type has a negative value, it cannot be converted. If assignment would result in a loss of significant digits, NonStop SQL/MX returns a data exception condition in SQLSTATE. For a description of SQLSTATE values, see Table 13-1 on page 13-2.
Host Variables in C/C++ Programs Using Host Variables in a C/C++ Program indicator_variable is an indicator variable of exact numeric data type. You must declare the indicator variable as type short in C. You must precede indicator_variable with a colon (:) in an SQL statement. If data returned in the host variable is null, the indicator variable is less than zero. If character data returned is truncated, the indicator variable is set to the length of the string in the database.
Host Variables in C/C++ Programs Fixed-Length Character Data Guidelines for Revising KANJI/KSC5601 Character Set Host Variables • • • • Use the character set clause CHARACTER SET IS KANJI or CHARACTER SET IS KSC5601. The encoding for KANJI is the double-byte subset of the Shift-JIS, with no check on code points performed by NonStop SQL/MX. For the best results, use the bigendian byte order to denote a KANJI character.
Host Variables in C/C++ Programs Fixed-Length Character Data Example A database contains a PRODUCTS table that consists of the PROD_NUM and PROD_DESC columns. The product number is defined to be the primary key. This example selects character data and appends a null terminator to the hv_prod_desc array before printing the data: C EXEC SQL BEGIN DECLARE SECTION; unsigned NUMERIC (4) find_num; unsigned NUMERIC (4) hv_prod_num; char hv_prod_desc[11]; EXEC SQL END DECLARE SECTION; ...
Host Variables in C/C++ Programs Variable-Length Character Data void blank_pad(char *buf, size_t size); ... int main() { EXEC SQL BEGIN DECLARE SECTION; unsigned NUMERIC (4) hv_prod_num; char hv_prod_desc[11]; EXEC SQL END DECLARE SECTION; ...
Host Variables in C/C++ Programs Variable-Length Character Data Example This example uses a declaration for an SQL column up to 20 characters in length: C EXEC SQL BEGIN DECLARE SECTION; VARCHAR last_name[21]; EXEC SQL END DECLARE SECTION; ... /* 20-character last name */ Follow the guidelines outlined in Fixed-Length Character Data on page 3-14 for handling the null terminator when you declare and use VARCHAR arrays as host variables for variable-length string literals.
Host Variables in C/C++ Programs Numeric Data example uses a VARCHAR declaration for an SQL column up to 11 characters in length: C EXEC SQL BEGIN DECLARE SECTION; unsigned NUMERIC (4) hv_prod_num; VARCHAR hv_prod_desc[11]; EXEC SQL END DECLARE SECTION; ... strcpy(hv_prod_desc, "abc"); /* Copy 3 characters */ hv_prod_desc[3]='\0'; ... EXEC SQL INSERT INTO products (prod_num, prod_desc) VALUES (:hv_prod_num, :hv_prod_desc); ...
Host Variables in C/C++ Programs Numeric Data When you use char arrays as host variables for NUMERIC, DECIMAL, or PICTURE 9’s data, use the SQL/MX CAST function to convert the value: • • • If you are converting NUMERIC, PICTURE 9’s, or DECIMAL data to a C char array and the scale is zero, declare the char array two characters larger than the number of digits you expect to store in the array. The first character is the sign (+, – , or blank), and the last character is reserved for the null terminator.
Numeric Data Host Variables in C/C++ Programs • Within an INSERT or UPDATE statement, use the SQL/MX CAST function to convert the character data to the desired SQL data type. The character set string can be associated with either ISO88591 or UCS2 character set. Assigning Numeric Data to Corresponding Data Types You can perform C arithmetic operations on SQL columns of NUMERIC, PICTURE 9’s, or DECIMAL data type.
Host Variables in C/C++ Programs • Numeric Data When you declare a fixed-point value (NUMERIC), NonStop SQL/MX translates the value to 'long long' in the C language. The 'long long' data type has no scale. In the next example, when the value is assigned using host variable binary_64_s, the host variable value is 1273. The decimal point is implicit, so the actual value interpreted by SQL is 1.273. To place a value of 1273 into the table, the application must use binary_64_s=1237000.
Numeric Data Host Variables in C/C++ Programs If you use NonStop SQL/MX to perform computations on a floating-point column in an SQL/MP table, the result is in IEEE format. For dynamic queries in embedded SQL programs, the default is Tandem floating-point format. For static queries in embedded SQL programs, the input and output depend on the type of host variables that are declared.
Date-Time and Interval Data Host Variables in C/C++ Programs representation. Despite this conversion, the precision of Tandem floating-point data types are maintained correctly in IEEE floating-point format. A Tandem REAL or FLOAT with precision between 1 and 22 cannot be converted to IEEE REAL because the Tandem exponent will not fit in an IEEE REAL data type.
Date-Time and Interval Data Host Variables in C/C++ Programs Selecting Standard Date-Time Values To retrieve standard date-time values (DATE, TIME, or TIMESTAMP, or the SQL/MP DATETIME equivalents) from the database, declare a date-time (DATE, TIME, or TIMESTAMP) host variable. For the required number of digits for DATE, TIME, or TIMESTAMP values, see Table 3-3 on page 3-9.
Host Variables in C/C++ Programs Date-Time and Interval Data Inserting or Updating Standard Date-Time Values To insert or update standard date-time values (DATE, TIME, or TIMESTAMP, or the SQL/MP DATETIME equivalents) in the database, format the date-time values in the desired display format for a date, time, or timestamp. Within an INSERT or UPDATE statement, use the DATE, TIME, or TIMESTAMP data type.
Host Variables in C/C++ Programs Date-Time and Interval Data } bill; ... EXEC SQL END DECLARE SECTION; ... EXEC SQL SELECT custnum, CAST(billing_date AS CHAR(5)) INTO :bill.hv_custnum, :bill.hv_billing_date FROM billings WHERE custnum = :hv_this_customer; ... bill.
Date-Time and Interval Data Host Variables in C/C++ Programs For example, if a table in the database has this column definition: AGE INTERVAL YEAR(2) TO MONTH The host variable representation for 36 years, 7 months, is: Sign + Year 3 6 Separator − Month 0 Null 7 An INTERVAL host variable is represented as a seven-character string, including five characters—with a hyphen (-) as the field separator—plus a character for the sign and a character (empty space) for a null terminator.
Host Variables in C/C++ Programs Host Variables in C Structures Example A database contains a BILLINGS table consisting of the CUSTNUM, START_DATE, BILLING_DATE, and TIME_BEFORE_PMT columns. This example updates date-time and interval values: C EXEC SQL BEGIN DECLARE SECTION; struct billing_rec { unsigned short hv_custnum; DATE hv_start_date; DATE hv_billing_date; INTERVAL DAY(3) hv_time_before_pmt; } bill; ... EXEC SQL END DECLARE SECTION; ... bill.hv_start_date[10]='\0' bill.
Host Variables in C/C++ Programs Host Variables as Data Members of a C++ Class Host Variables as Data Members of a C++ Class You can include an SQL Declare Section within a class definition to use a data member of a class as a host variable. Example This example uses a class named jobsql, containing the declarations of the memhv_jobcode and memhv_jobdesc host variables.
Host Variables in C/C++ Programs Inserting Null Inserting Null To insert values into columns that allow null with an INSERT or UPDATE statement, you must set the indicator variable to a value less than zero for null or zero for a nonnull value before executing the statement. Example This example uses a statement that inserts values into the ODETAIL table. The columns UNIT_PRICE and QTY_ORDERED allow null. To insert null, declare and use an indicator variable: C EXEC SQL BEGIN DECLARE SECTION; ...
Host Variables in C/C++ Programs Retrieving Rows With Nulls Retrieving Rows With Nulls To retrieve a row that contains null, use the NULL predicate in the WHERE clause. You cannot use an indicator variable set to –1 in a WHERE clause to retrieve a row that contains null. If you do, NonStop SQL/MX does not find the row and returns a NOTFOUND exception even if a column actually contains null. Example This example retrieves rows that have nulls from the EMPLOYEE table using a cursor.
Host Variables in C/C++ Programs Using the INVOKE Directive For further information, see Example 3-1 on page 3-35 and Example 3-2 on page 3-36. • Program readability and maintenance: The INVOKE statement creates host variables using the same names as column names in the table or view. Using the INVOKE Directive To execute an INVOKE directive for a table or view, you must have SELECT privileges on all applicable columns when you run the SQL/MX C preprocessor.
INVOKE and Floating-Point Host Variables Host Variables in C/C++ Programs INVOKE and Floating-Point Host Variables For floating-point columns, INVOKE generates a structure that uses the Tandem or IEEE floating-point format depending on whether the -o preprocessor option is used during compilation. Table 3-8 lists the C declarations that INVOKE generates for each type of floating-point column and shows the effect of the -o option on the floating-point format and on the execution of the application.
Host Variables in C/C++ Programs C Data Types Generated by INVOKE If your application uses INVOKE to generate floating-point host variables for SQL/MX columns, you should use the -o option. The -o option overrides the use of Tandem floating point and uses IEEE floating point instead. The -o option is particularly important for SQL/MX DOUBLE columns. If the -o option is not used, INVOKE generates Tandem double host variables for SQL/MX DOUBLE columns, which are in IEEE format.
Host Variables in C/C++ Programs C Data Types Generated by INVOKE Example 3-1 shows the CREATE TABLE statement that generates the SQLCDATA table. Example 3-1. CREATE TABLE Statement CREATE TABLE samdbcat.mysch.
Host Variables in C/C++ Programs C Data Types Generated by INVOKE Example 3-2 shows the structure generated by this INVOKE directive (with CHAR AS STRING): EXEC SQL INVOKE sqlcdata AS sqlc_types_struct; struct sqlc_types_struct sqlc_types; Example 3-2.
Host Variables in C/C++ Programs Using Indicator Variables With the INVOKE Directive Using Indicator Variables With the INVOKE Directive The INVOKE directive automatically generates a two-character indicator variable with data type short for each host variable that corresponds to a column that allows null. The name of the indicator variable is the same name as the corresponding column, plus a suffix and a prefix.
Host Variables in C/C++ Programs Using Indicator Variables With the INVOKE Directive Example A database contains an EMPTBL table consisting of the columns EMPNUM, FIRST_NAME, LAST_NAME, and HIRE_DATE. The columns FIRST_NAME and HIRE_DATE allow null. This example uses an INVOKE statement with the NULL STRUCTURE clause: C EXEC SQL BEGIN DECLARE SECTION; EXEC SQL INVOKE emptbl AS emptbl_rec NULL STRUCTURE; struct emptbl_rec emptbl_rec1, emptbl_rec2; ...
Host Variables in C/C++ Programs C Example of Using INVOKE C Example of Using INVOKE The next example declares and uses host variable names and indicator variable names and shows: • • • A host variable declaration using INVOKE that includes indicator variables. The structure is declared as global so that any function can reference the host variables. A host variable indicator variable used in the SELECT statement.
Host Variables in C/C++ Programs Character Set Examples Example 3-3. C INVOKE (page 2 of 2) /* Initialize the host variables in the WHERE clause. */ ... EXEC SQL SELECT ordernum, partnum, unit_price, qty_ordered INTO :odetail_rec.ordernum, :odetail_rec.partnum, :odetail_rec.unit_price INDICATOR :odetail_rec.unit_price_i, :odetail_rec.qty_ordered INDICATOR :odetail_rec.qty_ordered_i FROM sales.odetail WHERE ordernum = :in_ordernum AND partnum = :in_partnum; ... if (odetail_rec.
Host Variables in C/C++ Programs Selecting From a UCS2 Character Set Into a VARCHAR Host Variable primary key(pnum) ); Selecting From a UCS2 Character Set Into a VARCHAR Host Variable This example selects from a UCS2 character set into a VARCHAR host variable: EXEC SQL WHENEVER SQLERROR CALL handle_error; //select VARCHAR ucs2 column to VARCHAR UCS2 host variables with the same length void select_varchar2varchar_ucs2() { EXEC SQL BEGIN DECLARE SECTION; char CHARACTER SET IS UCS2 hv_empnum[4]; VARCHAR CHA
Host Variables in C/C++ Programs Selecting From an ISO88591 Character Set Into a UCS2 Host Variable int hv_cnt = 0; EXEC SQL END DECLARE SECTION; hv_empnum[3] = '\0'; EXEC SQL declare curs01 cursor for select * from staff_uc order by empnum; EXEC SQL open curs01; EXEC SQL fetch curs01 into :hv_empnum,:hv_empname, :hv_grade,:hv_city; while (strcmp(SQLSTATE, SQLSTATE_OK) == 0) { // process the output values :hv_empnum, :hv_empname, // :hv_grade, and :hv_city EXEC SQL FETCH curs01 INTO :hv_empnum,:hv_empname
Host Variables in C/C++ Programs Selecting From an ISO88591 Character Set Into a UCS2 Host Variable where city <> :in_hv_city order by pnum; EXEC SQL open curs02; EXEC SQL fetch curs02 INTO :out_hv_pnum,:out_hv_pname,:out_hv_ptype; while (strcmp(SQLSTATE,SQLSTATE_OK) == 0) { // processthe output EXEC SQL fetch curs02 into :out_hv_pnum,:out_hv_pname,:out_hv_ptype; cout <<"-----------"<
Host Variables in C/C++ Programs Selecting From an ISO88591 Character Set Into a UCS2 Host Variable HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 3- 44
4 Host Variables in COBOL Programs Host variables are data items declared in a host application program and used in both host language statements and embedded SQL statements. They provide communication between SQL statements and the host language statements. An input host variable transfers data from a host language program to an SQL/MX database, and an output host variable transfers data from a database to the program.
Host Variables in COBOL Programs COBOL Host Variable Data Types 02 CITY PIC X(14). ... EXEC SQL INVOKE SALES.PARTS AS SALES-REC END-EXEC. EXEC SQL END DECLARE SECTION END-EXEC. COBOL Host Variable Data Types You must explicitly declare all host variables used in SQL statements. A host variable used in an SQL statement must be declared in an SQL Declare Section prior to the first use of the host variable in an SQL statement.
COBOL Host Variable Data Types Host Variables in COBOL Programs nn column-name. mm LEN PIC S9(4) COMP. mm VAL [char-set] PIC X(l) specifies the data type of a target host variable for a column of the SQL data type: VARCHAR(l) The length l corresponds to the maximum length of the column value. The level numbers are indicated by nn and mm. The level number nn can be any level in the range 01 to 49, where mm is a greater level than nn. LEN specifies the actual length of the character item in VAL.
Host Variables in COBOL Programs COBOL Host Variable Data Types precision is the number of digits allowed in period 1. If period2 is SECOND, it can have a fractional-precision up to 6 digits. The fractional-precision is the number of digits of precision after the decimal point. The default for leadingprecision is 2, and the default for fractional-precision is 6.
Using Corresponding SQL and COBOL Data Types Host Variables in COBOL Programs PIC[TURE] 9(l-s)V9(s) DISPLAY specifies the data type of a target host variable for a column of one of these SQL data types: DECIMAL (l, s) UNSIGNED PIC[TURE] 9(l-s) V9(s) DISPLAY l length; number of digits in the column value s scale of the column value l-s number of digits in the integral part of the column value For the corresponding SQL and COBOL host variable data types, see Table 4-1.
Using Corresponding SQL and COBOL Data Types Host Variables in COBOL Programs Table 4-1. COBOL Character Host Variables and Their SQL Data Type Equivalents and COBOL Translations (page 2 of 2) COBOL Host Variable SQL/MX Equivalent Data Type Translated COBOL Declaration 01 column-name. 03 LEN PIC S9(4) COMP. 03 VAL [char-set] PIC X(l). CHAR [ACTER] VARYING(l) VARCHAR [ACTER](l) 01 column-name. 03 LEN PIC S9(4) COMP. 03 VAL [char-set] PIC X(l). DATE DATE PIC[TURE] X(l).
Using Corresponding SQL and COBOL Data Types Host Variables in COBOL Programs Table 4-2. Corresponding SQL, COBOL Host Variable Data Types, and Translated COBOL Declarations for NUMERIC, DECIMAL, PIC, SMALLINT, LARGEINT, and Date-Time Data Types (page 1 of 2) SQL Data Type COBOL Host Variable Data Type Translated COBOL Declaration NUMERIC (1 to 4,s) SIGNED PIC[TURE] S9(4-s)V9(s) COMP.1 PIC[TURE] S9(4-s)V9(s) COMP.1 NUMERIC (1 to 4,s) UNSIGNED PIC[TURE] COMP.1 PIC[TURE] 9(4-s)V9(s) COMP.
Data Conversion Host Variables in COBOL Programs Table 4-2. Corresponding SQL, COBOL Host Variable Data Types, and Translated COBOL Declarations for NUMERIC, DECIMAL, PIC, SMALLINT, LARGEINT, and Date-Time Data Types (page 2 of 2) SQL Data Type COBOL Host Variable Data Type Translated COBOL Declaration LARGEINT PIC[TURE] S9(18) COMP.1 PIC[TURE] S9(18) COMP.1 FLOAT (1 to 22 bits) REAL Not supported. Not supported. FLOAT (23 to 54 bits) DOUBLE PRECISION Not supported. Not supported.
Host Variables in COBOL Programs Specifying Host Variables in SQL Statements Converting Numeric Types Values of data types NUMERIC, DECIMAL, PICTURE 9’s, INTEGER, SMALLINT, FLOAT, REAL, and DOUBLE PRECISION are numbers and are all mutually comparable and mutually assignable. NonStop SQL/MX converts data between signed and unsigned numeric types and between numeric types with different precision.
Host Variables in COBOL Programs Using Host Variables in a COBOL Program {OF | IN} record-name is an optional clause that specifies a level 01 item. The variable-name or indicator_variable must be qualified by the record or group name only if the host variable name or indicator variable name is not unique within the program. This clause is an SQL/MX extension. INDICATOR is a keyword that can precede indicator_variable. indicator_variable is an indicator variable of exact numeric data type.
Host Variables in COBOL Programs Fixed-Length Character Data Host variable source code for storing KANJI characters in SQL/MX Release 1.8: A PIC X(100). Host variable source code for storing KANJI characters in SQL/MX Release 2.x: A CHARACTER SET KANJI PIC X(100). Guidelines for Revising KANJI/KSC5601 Character Set Host Variables Follow these guidelines when rewriting an application that contains KANJI or KSC5601 character sets for SQL/MX Release 2.
Host Variables in COBOL Programs Variable-Length Character Data Variable-Length Character Data Use a group item with two data items to declare a host variable for variable length character data (SQL VARCHAR data type) as: nn group-name. mm LEN PIC S9(4) COMP. mm VAL PIC X(len). The group-name must follow COBOL naming conventions. The level numbers are indicated by nn and mm. The level number nn can be any level in the range 01 to 49, and mm is a greater level than nn.
Host Variables in COBOL Programs Date-Time and Interval Data COMP and DISPLAY Data If you specify COMP or BINARY, the value is stored as a binary integer with an implied decimal point. If you omit COMP or BINARY, DISPLAY is the default, and the digits are stored as ASCII characters. Sign, Number of Digits, and Scale The S specifies a signed variable. If you omit S, the variable is unsigned. The 9(integer) specifies integer number of digits; integer must be positive. The V designates a decimal position.
Date-Time and Interval Data Host Variables in COBOL Programs format you want and retrieving the value by using the DATEFORMAT function. See the DATEFORMAT function in the SQL/MX Reference Manual.
Host Variables in COBOL Programs Date-Time and Interval Data ... EXEC SQL END DECLARE SECTION END-EXEC. ... EXEC SQL SELECT custnum, billing_date INTO :HV-CUSTNUM, :HV-BILLING-DATE FROM billings WHERE custnum = :HV-THIS-CUSTOMER END-EXEC. ... Inserting or Updating Standard Date-Time Values To insert or update standard date-time values (DATE, TIME, or TIMESTAMP, or the SQL/MP DATETIME equivalents) in the database, format the date-time values in the desired display format for a date, time, or timestamp.
Host Variables in COBOL Programs Date-Time and Interval Data DATETIME MONTH TO DAY data type, which has no equivalent in SQL/MX. This example selects the SQL/MP DATETIME value: COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. ... 01 BILLINGS-REC. 02 HV-CUSTNUM PIC 9(4) COMP. 02 HV-BILLING-DATE PIC X(10). ... EXEC SQL END DECLARE SECTION END-EXEC. ... EXEC SQL SELECT custnum, CAST(billing_date AS CHAR(10)) INTO :HV-CUSTNUM, :HV-BILLING-DATE FROM billings WHERE custnum = :HV-THIS-CUSTOMER END-EXEC. ...
Date-Time and Interval Data Host Variables in COBOL Programs INTERVAL Representation Interval values are represented as character strings, with a separator between the values of the fields (year-month or day-time). An extra character is generated at the beginning of the interval string for a sign.
Host Variables in COBOL Programs Using COBOL Data Description Clauses Example A database contains a BILLINGS table consisting of the CUSTNUM, START_DATE, BILLING_DATE, and TIME_BEFORE_PMT columns. This example updates date-time and interval values: COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. ... 01 BILLINGS-REC. 02 HV-CUSTNUM PIC 9(4) COMP. 02 HV-START-DATE PIC X(10). 02 HV-BILLING-DATE DATE. 02 HV-TIME-BEFORE-PMT INTERVAL DAY(3). EXEC SQL END DECLARE SECTION END-EXEC. ...
Using Indicator Variables in a COBOL Program Host Variables in COBOL Programs Table 4-4. Interpretation of COBOL Data Description Clauses (page 2 of 2) SYNC The clause is ignored. USAGE The USAGE options correspond to SQL data types: • • • VALUE COMPUTATIONAL (COMP) or BINARY to SQL type NUMERIC or PICTURE 9’s COMP to an integer type (SMALLINT, INTEGER, or LARGEINT). DISPLAY to SQL type CHARACTER (for PIC X) or DECIMAL (for PIC 9). The INDEX and PACKED-DECIMAL options are not allowed.
Host Variables in COBOL Programs Testing for Null or a Truncated Value MOVE -1 TO RETIRE-IND. EXEC SQL INSERT INTO persnl.retirees VALUES (:NEW-EMPNUM,:RETIRE-DATE INDICATOR :RETIRE-IND) END-EXEC. ... This example uses the NULL keyword instead of an indicator variable to insert the null value: ... EXEC SQL INSERT INTO persnl.retirees VALUES (:NEW-EMPNUM, NULL) END-EXEC.
Host Variables in COBOL Programs Retrieving Rows With Nulls COBOL Example 4-1. Null Test Example EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PRODUCT-REC. 02 PRODNUM PIC 9(5) COMP. 02 TIMESTAMP-SHIPPED PIC X(26). 02 SHIP-IND PIC S9(4) COMP. ... * Variable for selecting the product number: 01 MIN-PRODNUM PIC 9(5) COMP. EXEC SQL END DECLARE SECTION END-EXEC. * Variable for displaying the timestamp or NULL: 01 VALUE-DISPLAY PIC X(26) VALUE SPACES. ...
Host Variables in COBOL Programs Creating COBOL Host Variables Using INVOKE Example To retrieve rows that have null salaries from the EMPLOYEE table using a cursor, specify the NULL predicate in the WHERE clause in the associated SELECT statement when you declare the cursor: COBOL * Declare a cursor to find rows with null salaries. EXEC SQL DECLARE get_null_salary CURSOR FOR SELECT empnum, first_name, last_name, deptnum, jobcode, salary FROM employee WHERE salary IS NULL END-EXEC. ... PROCEDURE DIVISION.
Host Variables in COBOL Programs • Using the INVOKE Directive Program readability and maintenance: The INVOKE statement creates host variables using the same names as column names in the table or view. Using the INVOKE Directive To execute an INVOKE directive for a table or view, you must have SELECT privileges to all applicable columns when you run the SQL/MX COBOL preprocessor.
Host Variables in COBOL Programs COBOL Record Descriptions Generated by INVOKE Example 4-2 shows the CREATE TABLE statement that generates table SQLCOB_DATA. Example 4-2.
Host Variables in COBOL Programs COBOL Record Descriptions Generated by INVOKE Example 4-3 shows the record generated by this INVOKE directive: EXEC SQL INVOKE sqlcob_data AS SQLCOB-TYPES-REC END-EXEC. Example 4-3. COBOL Record Description Generated by INVOKE (page 1 of 2) * Record Definition for table SQLCOB_DATA 01 SQLCOB-TYPES-REC. 02 SYSKEY-I PIC S9(4) 02 SYSKEY PIC S9(18) 02 TYPE-CHAR PIC X(10). 02 TYPE-CHAR-NULL-I PIC S9(4) 02 TYPE-CHAR-NULL PIC X(10). 02 TYPE-UCS2-CHAR PIC X(20).
Host Variables in COBOL Programs COBOL Record Descriptions Generated by INVOKE Example 4-3. COBOL Record Description Generated by INVOKE (page 2 of 2) 02 TYPE-DEC9-U 02 TYPE-DEC18-S 02 TYPE-PIC4-S 02 TYPE-PIC4-U 02 TYPE-PIC9-S 02 TYPE-PIC9-U 02 TYPE-PIC18-S 02 02 02 02 TYPE-SMALL-S TYPE-SMALL-U TYPE-SMALL-NULL-I TYPE-SMALL-NULL 02 TYPE-INT-S PIC 02 TYPE-INT-U 02 TYPE-LARGE-S 02 TYPE-DATE 02 TYPE-TIME-6 02 TYPE-TIMESTAMP-6 02 TYPE-INTERVAL PIC 9(7)V9(2) DISPLAY. PIC S9(16)V9(2) DISPLAY SIGN SEPARATE.
Using Indicator Variables With the INVOKE Directive Host Variables in COBOL Programs Table 4-5. Changes Made by INVOKE in Generated Host Variables Column or Data Type Description of Change Underscore (_) within a name Converts underscores to hyphens (-). For example, the column name CITY_STREET becomes CITYSTREET. Underscore (_) at the end of a name Truncates the underscore so that the resulting name does not end in a hyphen. For example, the column name HOME_ becomes HOME.
Host Variables in COBOL Programs Using Indicator Variables With the INVOKE Directive Example A table named cob_table has the columns empnum and empname. The column empname can be null. This example uses an INVOKE statement with both the PREFIX and SUFFIX clauses: COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. EXEC SQL INVOKE cob_table PREFIX BEG- SUFFIX -END END-EXEC. ... EXEC SQL END DECLARE SECTION END-EXEC.
Host Variables in COBOL Programs COBOL Example of Using INVOKE COBOL Example of Using INVOKE Example 4-4 on page 4-30 declares and uses host variable names and indicator variable names and shows: • • • A host variable declaration with INVOKE that specifies the suffix -I for indicator variables. The invoked record declaration is included as a comment in the example. A host variable indicator variable used in the SELECT statement.
Host Variables in COBOL Programs Character Set Examples Example 4-4. INVOKE Example COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. EXEC SQL INVOKE sales.odetail AS ODETAIL-REC SUFFIX -I END-EXEC. * Record Description ************************************ * 01 ODETAIL-REC. * 02 ORDERNUM PIC 9(6) COMP. * 02 PARTNUM PIC 9(4) COMP. * 02 UNIT-PRICE-I PIC S9(4) COMP. * 02 UNIT-PRICE PIC S9(6)V9(2) COMP. * 02 QTY-ORDERED-I PIC S9(4) COMP. * 02 QTY-ORDERED PIC 9(5) COMP. ... EXEC SQL END DECLARE SECTION END-EXEC.
Host Variables in COBOL Programs Selecting From a UCS2 Character Set Into a VARCHAR Host Variable Selecting From a UCS2 Character Set Into a VARCHAR Host Variable This example selects from a UCS2 character set into a VARCHAR host variable with the same length: EXEC SQL BEGIN DECLARE SECTION END-EXEC. ... 01 hv-empnum PIC X(3) CHARACTER SET IS UCS2. 01 hv-empname. 03 len PIC S9(4) COMP. 03 val PIC X(20) CHARACTER SET UCS2. 01 hv-city. 03 len PIC S9(4) COMP. 03 val PIC X(15) CHARACTER SET UCS2. ...
Host Variables in COBOL Programs Fetching From a UCS2 Character Set into a VARCHAR Host Variable .... * select UCS2 columns into host variable EXEC SQL declare cursor_fetch_ucs2_varchar cursor for select * from staff_uc order by empnum END-EXEC. EXEC SQL open cursor_fetch_ucs2_varchar END-EXEC. EXEC SQL fetch cursor_fetch_ucs2_varchar into :hv-empnum,:hv-empname, :hv-grade,:hv-city END-EXEC.
5 Simple and Compound Statements You can access data in an SQL database without a cursor by using simple SQL/MX Data Manipulation Language (DML) statements: Simple DML Statement Description Single-Row SELECT Statement Retrieves a single row (or rowset) from a table or view and places the specified column values in host variables. With a cursor, use the DECLARE CURSOR declaration and the FETCH statement. INSERT Statement Inserts one or more rows into a table or view. Use for all INSERT operations.
Simple and Compound Statements Single-Row SELECT Statement Single-Row SELECT Statement A single-row SELECT statement retrieves a single row of data from one or more tables or views and places the column values in corresponding host variables. Use this general syntax: SELECT column [,column]... INTO :hostvar [,:hostvar]... FROM table-name WHERE search-condition For complete syntax, see the SELECT statement in the SQL/MX Reference Manual. The search condition is specified so that one row is selected.
Selecting a Column With Date-Time or INTERVAL Data Type Simple and Compound Statements 02 CUSTNUM PIC 9(4) COMP. 02 CUSTNAME PIC X(18). 02 STREET PIC X(22). 02 CITY PIC X(14). 02 STATE PIC X(12). 02 POSTCODE PIC X(10). 01 FIND-THIS-CUSTOMER PIC 9(4) COMP. EXEC SQL END DECLARE SECTION END-EXEC. PROCEDURE DIVISION. 0000-BEGIN. EXEC SQL WHENEVER NOT FOUND PERFORM 4000-NOT-FOUND END-EXEC. ... * Accept input value for host variable in WHERE clause. ...
Simple and Compound Statements INSERT Statement EXEC SQL END DECLARE SECTION; ... EXEC SQL SELECT projcode, projdesc, start_date INTO :hv_projcode, :hv_projdesc, :hv_start_date FROM samdbcat.persnl.project WHERE projcode = 1000; ...
Simple and Compound Statements Inserting Rows statement to transfer the values from the host variables to the table. Use this general syntax: INSERT INTO table-name (column [,column]...) VALUES (:hostvar [,:hostvar]...) For complete syntax, see the INSERT statement in the SQL/MX Reference Manual. To execute an INSERT statement, a program must have INSERT privileges for each column in the table receiving the data. After an INSERT statement executes, NonStop SQL/MX returns a value to the SQLSTATE variable.
Simple and Compound Statements Inserting Null Example COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 JOB. 02 HV-JOBCODE PIC 9(4) COMP. 02 HV-JOBDESC PIC X(18). 01 SQLSTATE PIC X(5). EXEC SQL END DECLARE SECTION END-EXEC. ... PROCEDURE DIVISION. ... * Move values to HV-JOBCODE and HV-JOBDESC. ... EXEC SQL INSERT INTO persnl.job (jobcode, jobdesc) VALUES (:HV-JOBCODE, :HV-JOBDESC) END-EXEC. ... Inserting Null You can insert a row of data with a null column.
Simple and Compound Statements Inserting a Date-Time Value void blank_pad(char *buf, size_t size) { size_t i; i = strlen(buf); if (i < size) memset(&buf[i], ' ', size - i); buf[size] = '\0'; } /* end blank_pad */ Example This statement uses the NULL keyword instead of an indicator variable: C EXEC SQL INSERT INTO persnl.employee VALUES (:emp.empnum, :emp.first_name, :emp.last_name, :emp.deptnum, :emp.
Simple and Compound Statements Inserting an Interval Value nonstandard SQL/MP DATETIME columns that are not equivalent to DATE, TIME, or TIMESTAMP, use the CAST function to insert a row with a date-time value. Standard Date-Time Example This example inserts a new row into the PROJECT table, including a timestamp value in the SHIP_TIMESTAMP column: COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 HV-TIMESTAMP TIMESTAMP. ... EXEC SQL END DECLARE SECTION END-EXEC. ...
Simple and Compound Statements Searched UPDATE Statement INTERVAL DAY TO SECOND(4) hv_day_time; ... EXEC SQL END DECLARE SECTION; ... ... /* Initialize host variables for new row */ strcpy(hv_year_month, "63-04"); strcpy(hv_day_time, "25:08:14:12.0000"); ... EXEC SQL INSERT INTO RETIREES (..., AGE, LAST_TIMECARD) VALUES(..., :hv_year_month, :hv_day_time); ... COBOL Interval Example A table includes a column with a year-month interval and a column with a day-time interval.
Simple and Compound Statements Updating a Single Row To execute an UPDATE statement, a program must have UPDATE privileges on each column being updated in the table. After an UPDATE statement executes, NonStop SQL/MX returns a value to the SQLSTATE variable. If no rows were found satisfying the search condition, the value of SQLSTATE is 02000 (no data). If a data exception occurs during the update process, the value of SQLSTATE is 22xxx.
Simple and Compound Statements Updating Multiple Rows EXEC SQL END DECLARE SECTION END-EXEC. ... PROCEDURE DIVISION. ... EXEC SQL UPDATE sales.orders SET deliv-date = :UPDATE-DATE WHERE ordernum = :UPDATE-ORDERNUM FOR READ COMMITTED ACCESS END-EXEC. Updating Multiple Rows If you do not need to check a value in a row before you update the row, use a single UPDATE statement to update multiple rows in a table.
Simple and Compound Statements Searched DELETE Statement WHERE jobcode = :SET-TO-NULL END-EXEC. Searched DELETE Statement The searched DELETE statement deletes one or more rows from a table. The selection of the rows to be deleted is based on a search condition. If you delete all rows from a table, the table still exists until it is deleted by a DROP TABLE statement.
Simple and Compound Statements Compound Statements Examples This example deletes all rows (or employees) from the EMPLOYEE table specified by the deptnum_to_delete host variable (which is entered by a user): C EXEC SQL DELETE FROM persnl.employee WHERE deptnum = :deptnum_to_delete; This example deletes all suppliers from the PARTSUPP table who charge more than TERMINAL-MAX-COST for a terminal.
Simple and Compound Statements Compound Statements For complete syntax, see the Compound (BEGIN ... END) statement in the SQL/MX Reference Manual. Although you cannot use cursors in compound statements, you can use rowsets to retrieve multiple rows from database tables. You cannot embed C/C++ or COBOL commands within a compound statement. You cannot use a compound statement within trigger actions.
Simple and Compound Statements Assignment Statement 09-end-compound. IF SQLSTATE = SQLSTATE-OK * Commit the change EXEC SQL COMMIT WORK END-EXEC ELSE * Roll back the change EXEC SQL ROLLBACK WORK END-EXEC. NonStop SQL/MX supports the use of host variables but does not allow for local variables declared within the BEGIN and END keywords. You can use SELECT statements inside compound statements only if each SELECT retrieves at most one row (or rowset) result.
Simple and Compound Statements IF Statement Example This example retrieves order information from the database and then updates the quantity on hand for that part number.
Simple and Compound Statements Using PROTOTYPE Host Variables as Table Names (ORDERNUM, ORDER_DATE, DELIV_DATE, SALESREP, CUSTNUM) VALUES (:hv_new_ordernum, :hv_orderdate, :hv_delivdate, :hv_salesrep, :hv_custnum); SELECT CUSTNUM, CUSTNAME, STREET, CITY, STATE, POSTCODE INTO :hv_custnum, :hv_custname, :hv_street, :hv_city, :hv_state, :hv_postcode FROM SALES.
Simple and Compound Statements Using PROTOTYPE Host Variables as Table Names After you declare a host variable for the table name, you can specify it within an embedded SQL statement by using the PROTOTYPE clause. For the syntax, see PROTOTYPE Host Variables For SQL/MP and SQL/MX Objects on page 8-4. You must initialize the value of the PROTOTYPE host variable before the execution of the embedded SQL statement. Example This example selects like columns from multiple tables that are specified dynamically.
Simple and Compound Statements Using PROTOTYPE Host Variables as Table Names ACCEPT hv-this-jobcode. ... EXEC SQL SELECT jobcode, jobdesc INTO :hv-jobcode, :hv-jobdesc FROM :hv-tablename PROTOTYPE 'samdbcat.persnl.job' WHERE jobcode = :hv-this-jobcode END-EXEC. The columns that you select from a dynamically specified table must be the same as the columns in the table specified in the PROTOTYPE clause during static compilation. Otherwise, NonStop SQL/MX returns an error.
Simple and Compound Statements Using PROTOTYPE Host Variables as Table Names HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 5- 20
6 Static SQL Cursors In NonStop SQL/MX, a mechanism called a cursor allows an application program to select and then retrieve a set of rows one row at a time. Each row in the set satisfies the criteria in the search condition of the SELECT statement that specifies the cursor. NonStop SQL/MX builds a result table to hold all the rows retrieved by executing the SELECT statement and then uses a cursor to make rows from the result table available to your program.
Steps for Using a Static SQL Cursor Static SQL Cursors Note. Using a cursor can sometimes degrade performance. A cursor requires OPEN, FETCH, and CLOSE statements, which increase the number of messages between the HP NonStop Distribution Service (DS) and the HP NonStop Data Access Manager (DAM). Consider not using a cursor if a single-row retrieval is sufficient. Steps for Using a Static SQL Cursor Figure 6-1 shows the steps presented within the complete C program.
Steps for Using a Static SQL Cursor Static SQL Cursors Figure 6-2 shows the steps presented within the complete COBOL program. These steps are executed in the sample program Example C-1 on page C-1. COBOL Figure 6-2. Using a Static SQL Cursor in a COBOL Program 1 ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 HOSTVAR 9(4) COMP. ... EXEC SQL END DECLARE SECTION END-EXEC. ... 2 EXEC SQL DECLARE sql_cursor CURSOR FOR SELECT column_1, column_2, ..., column_n FROM catalog.schema.
Static SQL Cursors Declare Required Host Variables Declare Required Host Variables In an SQL Declare Section, declare any host variables you specify in the query expression of the DECLARE CURSOR declaration: • • Before the DECLARE CURSOR declaration Within the same scope as the SQL statements that refer to them Declare the Cursor Use the DECLARE CURSOR declaration to name a cursor and associate it with a query expression. You can specify a row order for the result table of the query expression.
Static SQL Cursors Initialize the Host Variables ORDER BY postcode FOR READ ONLY; Example This example declares an updatable cursor named get_by_partnum that accesses the PARTS table. The query expression specifies all part numbers greater than or equal to the host variable named min-partnum: C EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 min-partnum pic 9(4) COMP. ... EXEC SQL END DECLARE SECTION END-EXEC. ...
Static SQL Cursors Retrieve the Values The OPEN statement must execute before any FETCH statements for the cursor and within the scope of all other SQL statements that refer to the cursor, including DECLARE CURSOR, FETCH, UPDATE, DELETE, and CLOSE statements. The OPEN statement does not acquire any locks unless a sort is necessary to order the selected rows. (The FETCH statement acquires any locks associated with a cursor.
Static SQL Cursors Process the Retrieved Values ... void list_func(void) { EXEC SQL OPEN list_by_partnum; EXEC SQL FETCH list_by_partnum INTO :parts_rec1.partnum, :parts_rec1.partdesc, :parts_rec1.price, :parts_rec1.qty_available; ... } Example COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 parts-rec1. 02 hv-partnum pic 9(4) COMP. 02 hv-partdesc pic x(18). 02 hv-price pic s9(6)v9(2) COMP. 02 hv-qty-available pic s9(5) COMP. 01 parts-rec2. ... 01 min-partnum pic 9(4) COMP.
Process the Retrieved Values Static SQL Cursors • Save the values in an array and process them later. After you process a row, retrieve the next row by using the FETCH statement. Continue executing this loop until you have processed all rows specified by the query expression. After all rows have been processed, SQLSTATE is 02000, and SQLCODE is 100. Positioned UPDATE Statement Use the positioned UPDATE statement to update a row in a table at the current cursor position.
Static SQL Cursors Process the Retrieved Values SET price = :new_price, qty_available = :new_qty WHERE CURRENT OF get_by_partnum; ... /* Branch back to retrieve the next row. */ EXEC SQL CLOSE get_by_partnum; Positioned DELETE Statement Use the positioned DELETE statement to delete a row in a table at the current cursor position. You can delete multiple rows, one row at a time. Before you delete a row, you can also test one or more column values if necessary.
Static SQL Cursors Fetch the Next Row EXEC SQL DELETE FROM sales.parts WHERE CURRENT OF get_by_partnum END-EXEC. * Branch back to retrieve the next row. ... EXEC SQL CLOSE get_by_partnum END-EXEC. Fetch the Next Row In Retrieve the Values on page 6-6, the FETCH statement positions the cursor at the next row of the result table and transfers the values defined in the query expression of the DECLARE CURSOR statement to the corresponding host variables.
Static SQL Cursors Close the Cursor 01 SQLSTATE-OK PIC X(5) VALUE "00000". ... EXEC SQL OPEN get_by_partnum END-EXEC. ... EXEC SQL FETCH get_by_partnum ... END-EXEC. PERFORM UNTIL SQLSTATE = SQLSTATE-NODATA * Test the value(s) in the current row IF ... EXEC SQL DELETE FROM sales.parts WHERE CURRENT OF get_by_partnum END-EXEC. END-IF * Get the next row EXEC SQL FETCH get_by_partnum ... END-EXEC. END-PERFORM. ... EXEC SQL CLOSE get_by_partnum END-EXEC.
Static SQL Cursors Using Date-Time and INTERVAL Data Types Using Date-Time and INTERVAL Data Types If a column in the select list of a cursor specification has an INTERVAL or standard date-time (DATE, TIME, or TIMESTAMP, or the SQL/MP DATETIME equivalents) data type, use the INTERVAL or date-time type.
Static SQL Cursors Nonstandard SQL/MP DATETIME Example Nonstandard SQL/MP DATETIME Example This example shows a typical context for a nonstandard date-time input parameter, DATETIME MONTH TO DAY (mm-dd), for a cursor specification: C EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6]; unsigned NUMERIC (4) hv_projcode; char hv_projdesc[19]; char hv_start_date[6]; char in_start_date[6]; EXEC SQL END DECLARE SECTION;...
Static SQL Cursors Using Floating-Point Data Types /* Initialize the value in the WHERE clause. */ printf("Enter minimum estimated number of days: "); scanf("%s", in_est_complete); /* Open the cursor using this value. */ EXEC SQL OPEN get_project; /* Fetch the first row of the result table. */ EXEC SQL FETCH get_project INTO :hv_projcode,:hv_projdesc,:hv_est_complete; while (strcmp (SQLSTATE, SQLSTATE_NODATA) != 0) { /* Process the row in some way. */ ... /* Fetch the next row of the result table.
Cursor Position Static SQL Cursors • You can specify IN EXCLUSIVE MODE for the SELECT statement in the cursor specification so that NonStop SQL/MX does not have to escalate the lock when an UPDATE or DELETE executes. Otherwise, if you do not specify IN EXCLUSIVE MODE and your program is reading records accessed by another cursor defined with IN EXCLUSIVE MODE, your program must wait for access. Cursor Position Cursor position is similar to record position in a sequential file.
Static SQL Cursors Cursor Sensitivity If your program is accessing a copy of a row instead of the actual row, the cursor points to a copy of the data, and the data is concurrently available to other programs. For more information, see the SQL/MX Reference Manual. Cursor Sensitivity The ANSI standard defines three types of cursor sensitivity: INSENSITIVE, SENSITIVE, and ASENSITIVE.
7 Static Rowsets The traditional cursor model in SQL is inefficient for applications retrieving large numbers of rows because too much time is used retrieving one row at time. However, the SQL/MX extension rowsets enable the SQL cursor to return more than one row at a time, greatly reducing the number of calls made to both the database system and the network.
Using Rowsets Static Rowsets Using Rowsets The two ways of using rowsets in SQL queries are: • • Direct use. You can place host variable rowset arrays anywhere a scalar host variable is placed in an SQL query. Rowset-derived tables. Given a rowset, a construct is provided that creates an in-memory table. A rowset-derived table resulting from this construct is a table of several columns (one column for each array of the rowset) and rowset size tuples or rows. A rowset is analogous to an in-memory table.
Considerations for Rowset Size Static Rowsets To specify a host variable array as a part of a rowset, use this syntax: ROWSET [rowset-size] variable-specification rowset-size specifies the dimension of the host variable array that is a part of the rowset. The size immediately follows the ROWSET keyword and must be enclosed in square brackets [ ]. The size is an unsigned integer. variable-specification is the data type and name of a host variable.
Specifying Rowset Arrays Static Rowsets • • The total rowset size should not exceed the physical memory of the client computer to avoid fragmentation while accessing a rowset array. The rowset size should not be less than the number of rows that need to be accessed simultaneously. For example, a screen-based application should use a rowset size that is a multiple of the number of rows displayed on the screen.
Using Rowset Arrays for Input Static Rowsets ... EXEC SQL END DECLARE SECTION; ... EXEC SQL SELECT first_name, last_name, salary INTO :hva_first_name, :hva_last_name, :hva_salary INDICATOR :hva_salary_indicator FROM persnl.employee; COBOL ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 sqlstate pic x(5). 01 rs. 02 ROWSET[200] hvafirstname pic x(15). 02 ROWSET[200] hvalastname pic x(20). 02 ROWSET[200] hvasalary pic 9(8)v9(2) comp. 02 ROWSET[200] hvasalaryindicator pic 9(5).
Using Rowset Arrays for Output Static Rowsets Using Rowset Arrays for Output Use array host variables for output in the INTO clause of SELECT and FETCH statements. Use rowset arrays for output to retrieve multiple rows from the result table by executing a single SQL statement. When more than one array host variable is used as output in an SQL statement, the output arrays might not be of uniform size. In this situation, the number of output rows retrieved is equal to the size of the smallest output array.
Selecting Rows Into Rowset Arrays Static Rowsets Use this general syntax: SELECT column [,column]... INTO :hostvar-array [,:hostvar-array]... FROM table-name [,table-name]... [WHERE search-condition] [GROUP BY column [,column]...] [HAVING search-condition] [ORDER BY column [,column]...] For complete syntax, see the SELECT statement in the SQL/MX Reference Manual. Note. Data mining operations—SAMPLE, SEQUENCE BY, and TRANSPOSE—are not supported for operations with rowsets.
Selecting Rows Into Rowset Arrays Static Rowsets .... } COBOL ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 sqlstate pic x(5). 01 numrows pic 9(9) comp. 01 rs. 02 ROWSET[100] hvadeptnum pic 9(4) comp. 02 ROWSET[100] hvafirstname pic x(15). 02 ROWSET[100] hvalastname pic x(20). EXEC SQL END DECLARE SECTION END-EXEC. 01 i pic s9(4) comp. ... EXEC SQL SELECT first_name, last_name, deptnum INTO :hvafirstname, :hvalastname, :hvadeptnum FROM employee ORDER BY deptnum, last_name, first_name END-EXEC.
Selecting Rows Into Rowset Arrays Static Rowsets At the beginning of the execution of an SQL statement, the diagnostics area is emptied. When the statement executes, NonStop SQL/MX places information on completion or exception conditions into this area.
Static Rowsets Selecting Rows Into Rowset Arrays INTO :hva_projcode, :hva_projdesc, :hva_start_date FROM persnl.project WHERE start_date <= DATE '1998-12-01'; ... EXEC SQL GET DIAGNOSTICS :numrows = ROW_COUNT; ... for (i = 0; i < numrows; i++) { hva_projdesc[i][18] = '\0'; hva_start_date[i][10] = '\0'; printf("\nProject: %hu, %s, Started: %s", hva_projcode[i], hva_projdesc[i], hva_start_date[i]); ... /* Process the row in some way. */ .... } COBOL ... EXEC SQL BEGIN DECLARE SECTION END-EXEC.
Selecting Rows Into Rowset Arrays Static Rowsets Example This example selects the EMPNUM and SALARY columns of all rows in the EMPLOYEE table where the (JOBCODE, DEPTNUM) value is equal to one of the set of values in the hva_jobcode and hva_deptnum host variable arrays. An input value set is composed of array elements from the hva_jobcode and hva_deptnum host variable arrays with identical index numbers.
Selecting Rowsets With a Cursor Static Rowsets Move 9000 TO hvadeptnum(2) Move 300 TO hvajobcode(3) Move 1000 TO hvadeptnum(3) Move 400 TO hvajobcode(4) Move 1000 TO hvadeptnum(4) Move 500 TO hvajobcode(5) Move 3000 TO hvadeptnum(5) EXEC SQL SELECT empnum, salary INTO :hvaempnum, :hvasalary INDICATOR :hvasalaryindicator FROM employee WHERE jobcode = :hvajobcode AND deptnum = :hvadeptnum END-EXEC. ...
Selecting Rowsets With a Cursor Static Rowsets IF (SQLCODE != 0) { printf("GET DIAGNOSTICS operation failed." " SQLCODE = %ld\n", SQLCODE); return(SQLCODE); } for (i = 0; i < numrows; i++) { printf("Project Code = %s\t Project Description = %s\t Start Date = %s\n", hva_projcode[i], hva_projdesc[i], hva_start_date[i]); } } } /* Close the cursor */ EXEC SQL CLOSE rowset_cursor ; ... COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 SQLCODE pic s9(9) comp. 01 numrows pic 9(9) comp. 01 rs.
Inserting Rows From Rowset Arrays Static Rowsets Inserting Rows From Rowset Arrays The INSERT statement using rowsets inserts multiple rows into a table from host variable arrays. To insert data, a program moves the new values to the array of host variables that have been declared as rowsets and then executes an INSERT statement to transfer the values from the host variable arrays to the table. Use this general syntax: INSERT INTO table-name [(column [,column]...
Inserting Rows From Rowset Arrays Static Rowsets Move "PROGRAM MANAGER" to hvaprojdesc[2] Move 300 to hvajobcode[3] Move "QUALITY SUPERVISOR" to hvaprojdesc[3] Move 400 to hvajobcode[4] Move "TECHNICAL OFFICER" to hvaprojdesc[4] Move 500 to hvajobcode[5] Move "EXECUTIVE OFFICER" to hvaprojdesc[5] EXEC SQL INSERT INTO job (jobcode, jobdesc) VALUES (:hvajobcode, :hvajobdesc) END-EXEC. ...
Inserting Rows From Rowset Arrays Static Rowsets ROWSET [100] unsigned NUMERIC (4) hva_salary; ROWSET [100] short hva_salary_indicator; ... EXEC SQL END DECLARE SECTION; long i; ... /* Populate the host variable arrays in some way. */ ... /* Store -1 in the indicator array for the first 50 input values. */ for (i = 0; i < 50; i++) hva_salary_indicator[i] = -1; /* Store 0 in the indicator array for the next 50 input values.
Updating Rows by Using Rowset Arrays Static Rowsets INSERT INTO employee VALUES ( :hvaempnum, :hvafirstname, :hvalastname, :hvadeptnum, :hvajobcode, :hvasalary INDICATOR :hvasalaryindicator) END-EXEC. ... Inserting a Timestamp Value You do not need to use the CAST function when inserting a TIMESTAMP value.
Updating Rows by Using Rowset Arrays Static Rowsets Use this general syntax: UPDATE table-name SET set-clause [,set-clause]... WHERE search-condition set-clause The expression in a set-clause can contain array host variables. When array host variables are present in the search-condition, two alternatives exist for the set-clause expression: • • Scalar host variables only. In this case, all matching rows are updated with identical values, obtained by evaluating the scalar expression.
Deleting Rows by Using Rowset Arrays Static Rowsets hva_jobcode[2] = 300; hva_jobcode[3] = 400; hva_jobcode[4] = 500; ... EXEC SQL UPDATE persnl.employee SET salary = salary * :hv_inc WHERE jobcode = :hva_jobcode; ... COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 sqlstate pic x(5). 01 numrows pic 9(9) comp. 01 rs. 01 hva-multiplier pic 9(4) comp. 02 ROWSET[5] hvajobcode pic 9(4) comp. 02 ROWSET[5] hvainc pic 9(4)v99 comp. EXEC SQL END DECLARE SECTION END-EXEC. ...
Specifying Size and Row ID for Rowset Arrays Static Rowsets Example This example deletes all rows from the JOB table specified by the hva_jobcode host variable array: C EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6]; ROWSET[5] unsigned NUMERIC (4) hva_jobcode; ... long numrows; EXEC SQL END DECLARE SECTION; ... /* Populate the rowset in some way. */ hva_jobcode[0] = 100; hva_jobcode[1] = 200; hva_jobcode[2] = 300; hva_jobcode[3] = 400; hva_jobcode[4] = 500; ... EXEC SQL DELETE FROM persnl.
Specifying Size and Row ID for Rowset Arrays Static Rowsets using the rowset-derived table syntax presented in Selecting From Rowset-Derived Tables With a Cursor on page 7-32.
Limiting the Size of the Input Rowset Static Rowsets Limiting the Size of the Input Rowset When you are inserting rows from a rowset, you must limit the input size to only the rows that have been populated with data. Example This example inserts multiple rows (JOBCODE and JOBDESC columns) from host variable arrays into the JOB table.
Limiting the Size of the Input Rowset When Declaring a Cursor Static Rowsets values (:hvajobcode, :hvajobdesc) END-EXEC. ... Limiting the Size of the Input Rowset When Declaring a Cursor When you are declaring a cursor to fetch rows from the database, you can limit the size of the input rowset. Use this general syntax in the cursor declaration when you limit rowset size.
Limiting the Size of the Output Rowset Static Rowsets 02 ROWSET[10] hva_jobcode pic 9(4) comp. 02 ROWSET[10] hva_jobdesc pic x(18). 02 input_size pic 9(4) comp EXEC SQL END DECLARE SECTION END-EXEC. … EXEC SQL DECLARE C1 CURSOR FOR ROWSET FOR INPUT SIZE :input_size SELECT jobdesc FROM persnl.job WHERE jobcode = :hva_jobcode END-EXEC. Move 3 TO input_size ***** Populate first 3 rows of input rowset.
Using the Index Identifier Static Rowsets EXEC SQL END DECLARE SECTION END-EXEC. ... Move 5 to outputsize EXEC SQL ROWSET FOR OUTPUT SIZE :outputsize SELECT jobcode, jobdesc INTO :hvajobcode, :hvajobdesc FROM job END-EXEC. ... Using the Index Identifier Use the index (or row) identifier to indicate which row of the input rowset array in the WHERE clause caused a row to be part of the output rowset array.
Using the Index Identifier Static Rowsets ... EXEC SQL ROWSET FOR KEY BY row_id SELECT empnum, row_id INTO :hva_empnum, :hva_row_id FROM persnl.employee WHERE jobcode = :hva_jobcode; ... EXEC SQL GET DIAGNOSTICS :numrows = ROW_COUNT; ... for (i = 0; i < numrows; i++) { printf("\nEmp Nbr: %hu", hva_empnum[i]); printf("\nRow Id: %hu", hva_row_id[i]); } ... COBOL ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 sqlstate pic x(5). 01 rs. 02 ROWSET[5] hvajobcode pic 9(4) comp.
Using the Index Identifier Static Rowsets • Three rows with jobcode equal to 500 and row identifier value 4 for C and 5 for COBOL The jobcode equal to 350 does not exist in the sample database. As a result, the row identifier equal to 2 for C and 3 for COBOL does not occur in the output of the program. The row identifier values in the COBOL example are greater, by a value of 1, than their corresponding values in the C example.
Specifying Rowset-Derived Tables Static Rowsets } ... COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 sqlstate pic x(5). 01 rs. 02 ROWSET[5] hvajobcode pic 9(4) comp. 02 ROWSET[100] hvarowcount pic 9 comp. 02 ROWSET[100] hvarowid pic s9(4) comp. 01 numrows pic 9(9) comp. EXEC SQL END DECLARE SECTION END-EXEC. 01 i pic s9(4) comp. ...
Using Rowset-Derived Tables in DML Statements Static Rowsets :array-name [,:array-name]... specifies a set of host variable arrays. Each array-name can be used like a column in the rowset-derived table. Each array-name can be any valid host language identifier with a data type that corresponds to an SQL data type. Precede each array-name with a colon (:) within an SQL statement.
Selecting From Rowset-Derived Tables Static Rowsets A SELECT statement that contains a rowset-derived table within the FROM clause handles its input data as a join of the other table references with the rowset-derived table. Example In this example, the ODETAIL table is joined with a rowset using a rowset-derived table.
Selecting From Rowset-Derived Tables Static Rowsets Move 2403 TO hvapartnum(3) Move 5103 TO hvapartnum(4) Move 6301 TO hvapartnum(5) EXEC SQL SELECT od.partnum, COUNT(*) INTO :hvaodpartnum, :hvapartnumcount FROM odetail od, ROWSET(:hvapartnum) AS rs(partnum) WHERE od.partnum = rs.partnum GROUP BY od.partnum END-EXEC. *****Process the counts in some way***** PERFORM VARYING i FROM 1 BY 1 UNTIL i < 5 display "Part Nbr: " hvaodpartnum(i) display "COUNT: " hvapartnumcount(i) END-PERFORM. ...
Selecting From Rowset-Derived Tables With a Cursor Static Rowsets ... ***** Populate the rowset in some way ***** Move 244 TO hvapartnum(1) Move 2001 TO hvapartnum(2) Move 2403 TO hvapartnum(3) Move 5103 TO hvapartnum(4) Move 6301 TO hvapartnum(5) EXEC SQL SELECT partnum INTO :rowidpartnum FROM ROWSET(:hvapartnum) KEY BY row_id AS rs(partnum, row_id) WHERE row_id+1 = 5 END-EXEC. ***** Process the selected element of the table in some way **** Display "Part Nbr " rowidpartnum. ...
Selecting From Rowset-Derived Tables With a Cursor Static Rowsets hva_ordernum[2] = 2403; hva_ordernum[3] = 5103; ... /* Specify number of valid input values */ num_inputvalues = 4; /* Declare cursor C1 for select operation */ EXEC SQL DECLARE C1 CURSOR FOR SELECT od.partnum, rs.rowid FROM sales.odetail od, ROWSET :num_inputvalues(:hva_ordernum) KEY BY rowid AS rs(ordernum, rowid) WHERE od.ordernum = rs.
Inserting Rows From Rowset-Derived Tables Static Rowsets END-EXEC EXEC SQL OPEN C1 END-EXEC **** fetch rows from table **** perform until sqlcode not = 0 EXEC SQL FETCH C1 INTO :hvaodpartnum, :rowid END-EXEC **** Process the output rows in some way **** end-perform EXEC SQL CLOSE C1 END-EXEC ... Inserting Rows From Rowset-Derived Tables Use the INSERT statement and rowset-derived tables to insert multiple rows into a table from a query that retrieves from the derived table.
Limiting the Size of a Rowset-Derived Table Static Rowsets AS rs(jobcode, jobdesc); ... COBOL ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 sqlstate pic x(5). 01 rs. 02 ROWSET[5] hvajobcode pic 9(4) comp. 02 ROWSET[5] hvajobdesc pic x(18). EXEC SQL END DECLARE SECTION END-EXEC. ...
Inserting Null Static Rowsets 01 sqlstate pic x(5). 01 rs. 02 ROWSET[10] hvajobcode pic 9(4) comp. 02 ROWSET[10] hvajobdesc pic x(18). EXEC SQL END DECLARE SECTION END-EXEC. ... ***** Populate the first five rows in some way ***** EXEC SQL INSERT INTO persnl.job SELECT jobcode, jobdesc FROM ROWSET 5(:hvajobcode, :hvajobdesc) AS rs(jobcode, jobdesc) END-EXEC. ...
Static Rowsets Updating Rows by Using Rowset-Derived Tables :hva_salary INDICATOR :hva_salary_indicator) AS rs(empnum,first_name,last_name,deptnum,jobcode,salary); ... COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 SQLSTATE pic x(5). 01 rs. 02 ROWSET[100] hvaempnum pic 9(4) comp. 02 ROWSET[100] hvafirstname pic x(15). 02 ROWSET[100] hvalastname pic x(20). 02 ROWSET[100] hvadeptnum pic 9(4) comp. 02 ROWSET[100] hvajobcode pic 9(4) comp. 02 ROWSET[100] hvasalary pic 9(4) comp.
Updating Rows by Using Rowset-Derived Tables Static Rowsets Example This example updates the SALARY column of all rows in the EMPLOYEE table where the jobcode value is equal to one of the values in the hva_jobcode host variable array. The UPDATE statement is executed for each matching job code: C EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6]; ROWSET[5] unsigned NUMERIC (4) hva_jobcode; ... EXEC SQL END DECLARE SECTION; ... /* Input the salary increment. */ ... /* Populate the rowset in some way.
Deleting Rows by Using Rowset-Derived Tables Static Rowsets Deleting Rows by Using Rowset-Derived Tables Use a rowset-derived table in a DELETE statement to indicate which rows are to be deleted from the database table. In this case, the values of the rowset are generated from a subquery placed in the WHERE clause of the DELETE statement.
Static Rowsets Deleting Rows by Using Rowset-Derived Tables HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 7- 40
8 Name Resolution, Similarity Checks, and Automatic Recompilation This section covers the following: • • Name Resolution on page 8-1 Similarity Checks and Automatic Recompilation on page 8-8 Name Resolution In a C, C++, and COBOL program, you can use SQL/MX statements to query both SQL/MP and SQL/MX database objects. This subsection explains how to refer to SQL/MP and SQL/MX database objects in an C, C++, and COBOL source file and how the object names are resolved during SQL compilation and run time.
Name Resolution, Similarity Checks, and Automatic Recompilation Table and View Name References ANSI Names for SQL/MX Objects You can use only an ANSI logical name for SQL/MX tables or views in an SQL statement: EXEC SQL SELECT jobcode, jobdesc INTO :hv_jobcode, hv_jobdesc FROM newyork.persnl.job WHERE jobcode = :hv_this_jobcode; When you hard code a table name in the ANSI logical format, the logical name is tightly bound to a physical table when the SQL statement is compiled.
Name Resolution, Similarity Checks, and Automatic Recompilation Table and View Name References errors. To avoid these errors, create SQL/MP aliases separately before compiling the embedded SQL program. When you code a table name by using an SQL/MP alias, the logical name of the SQL/MP alias is tightly bound to a physical table when the SQL statement is compiled. To make the statement refer to some other physical table, you must alter the SQL/MP alias and then recompile the module.
Name Resolution, Similarity Checks, and Automatic Recompilation Table and View Name References PROTOTYPE Host Variables For SQL/MP and SQL/MX Objects Note. Before using PROTOTYPE host variables in OLTP applications, see OLT Optimization Considerations for DEFINE Names and PROTOTYPE Host Variables on page 8-5. When you use a PROTOTYPE host variable to refer to a table or view, the SQL statement is compiled using the definition of the table or view specified in the PROTOTYPE clause.
Name Resolution, Similarity Checks, and Automatic Recompilation Precedence of Object Name Qualification The SQL/MX compiler uses this name during preprocessing and explicit compilation of the embedded DML statement. The table or view name defined in the PROTOTYPE clause must be visible on the system where the compilation is performed. This table or view name should also be fully qualified so that default catalog and schema names are not used.
Name Resolution, Similarity Checks, and Automatic Recompilation Compile-Time Name Resolution for SQL/MP Objects For more information on the SYSTEM_DEFAULTS table, see the SQL/MX Reference Manual. Compile-Time Name Resolution for SQL/MP Objects Compile-time name resolution is an SQL/MX extension you use to compile a module with statements that refer to SQL/MP tables or views with class MAP DEFINEs.
Name Resolution, Similarity Checks, and Automatic Recompilation Distributed Database Considerations Distributed Database Considerations The SQL statements in an embedded SQL program can refer to SQL/MX and SQL/MP database objects on remote nodes. Remote SQL/MX Objects To refer to remote SQL/MX database objects in an embedded SQL program, you need not change the database object names in the source code.
Name Resolution, Similarity Checks, and Automatic Recompilation • • Similarity Checks and Automatic Recompilation SQL/MP Object Names for an RDF Environment on page 8-8 SQL/MX Object Names for an RDF Environment on page 8-8 SQL/MP Object Names for an RDF Environment When referring to SQL/MP objects in an embedded SQL program, use class MAP DEFINEs or SQL/MP aliases. See DEFINE Names for SQL/MP Objects on page 8-3 and SQL/MP Aliases for SQL/MP Objects on page 8-2.
Name Resolution, Similarity Checks, and Automatic Recompilation Similarity Check recompile the SQL plan. For more information, see Automatic Recompilation on page 8-12.
Name Resolution, Similarity Checks, and Automatic Recompilation Similarity Check Controlling the Similarity Check By default, the similarity check is enabled for all DML statements. To disable the similarity check and force recompilation of an SQL/MX statement when a class MAP DEFINE value or table timestamp changes, use the CONTROL QUERY DEFAULT or CONTROL TABLE statement with the SIMILARITY_CHECK option.
Name Resolution, Similarity Checks, and Automatic Recompilation Similarity Check run time might not always match the compile-time value, causing the similarity check to fail.) • • • • Both tables must have the same number of key columns. Corresponding key columns must have the same: ° ° Position, offset, and column number Column attributes If a DML statement uses an index, the run-time table must have an index that has the same attributes as the index that was used by the compile-time table.
Name Resolution, Similarity Checks, and Automatic Recompilation Automatic Recompilation Automatic Recompilation Automatic recompilation is the run-time recompilation, invoked by the SQL/MX executor, of a DML statement in a module. During automatic recompilation, the SQL plan changes but is not written to the module. Instead, it is stored in the memory of the SQL/MX executor.
Name Resolution, Similarity Checks, and Automatic Recompilation Recommended Recompilation Settings for OLTP Programs To return recompilation warning messages directly to the program, use a CONTROL QUERY DEFAULT statement with the RECOMPILATION_WARNINGS option set to ON: CONTROL QUERY DEFAULT RECOMPILATION_WARNINGS 'ON'; If the similarity check fails and RECOMPILATION_WARNINGS is ON, the SQL/MX executor returns warning message 8579 to the program.
Name Resolution, Similarity Checks, and Automatic Recompilation Recommended Recompilation Settings for OLTP Programs HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 8- 14
9 Dynamic SQL Using the dynamic SQL statements of NonStop SQL/MX, programs can construct, compile, and execute an SQL statement at run time. With static SQL, you code the actual SQL statement in the source file and compile the statement during explicit SQL compilation. A static SQL program uses host variables to send and receive values. A dynamic SQL program, however, uses a character host variable as a placeholder for an SQL statement, which is usually unknown during explicit compilation.
Statements for Dynamic SQL With Arguments Dynamic SQL Statements for Dynamic SQL With Arguments Some of the dynamic SQL statements commonly used in programs are: PREPARE Prepares (compiles) a dynamic SQL statement for subsequent execution by an EXECUTE statement. DEALLOCATE PREPARE Deallocates a prepared statement and returns the system resources used by the statement and also allows reuse of the statement name. EXECUTE Executes a prepared dynamic SQL statement.
Steps for Using Dynamic SQL With Argument Lists Dynamic SQL and dynamic parameters. See the SQL/MX Reference Manual for information on setting CONTROL QUERY DEFAULT values. Steps for Using Dynamic SQL With Argument Lists Figure 9-1 shows the steps presented within the complete C program. These steps are executed in the sample program Example A-3 on page A-5. C Figure 9-1. Using Dynamic SQL in a C Program 1 ... EXEC SQL BEGIN DECLARE SECTION; char hv_sql_statement[256]; long in_value; ... hv_column1; ..
Declare a Host Variable for the Dynamic SQL Statement Dynamic SQL Figure 9-2 shows the steps presented within the complete COBOL program. These steps are executed in the sample program Example C-3 on page C-6. COBOL Figure 9-2. Using Dynamic SQL in a COBOL Program 1 ... EXEC SQL BEGIN DECLARE SECTION; 01 HV-SQL-STATEMENT PIC X(256). 01 IN-VALUE PIC 9(5) COMP. 01 HV-COLUMN1 ... 01 HV-COLUMN2 ... ... EXEC SQL END DECLARE SECTION; 2 3 4 5 6 * Construct the SQL statement and move to statement variable. .
Move the Statement Into the Host Variable Dynamic SQL Move the Statement Into the Host Variable Move a statement containing parameters into the statement host variable. For example, the host variable named hv_sql_statement might contain a statement of this form: SELECT EMPNUM, FIRST_NAME, LAST_NAME, SALARY FROM SAMDBCAT.PERSNL.EMPLOYEE WHERE EMPNUM = CAST(? AS NUMERIC(4) UNSIGNED) The parameter in this SELECT statement represents a value to be provided by the user.
Set Explicit Input Values Dynamic SQL The SQL identifier sqlstmt is the name of the prepared statement to be used in a subsequent EXECUTE statement. The host variable hv_sql_statement contains the dynamic SQL statement. Set Explicit Input Values If you have dynamic input parameters in your prepared statement, you must code the appropriate C statements to prompt the user to input values and then store these values in the appropriate host variables.
Deallocate the Prepared Statement Dynamic SQL Example This statement uses both input parameters and output variables: C EXEC SQL EXECUTE sqlstmt USING :in_empnum INTO :hv_empnum,:hv_firstname,:hv_lastname, :hv_salary INDICATOR :hv_salary_i; You must specify the indicator variable in the INTO argument list for columns that allow null. Deallocate the Prepared Statement When you are finished with the dynamic SQL statement, deallocate the resources used by the prepared statement.
Setting Default Values Dynamically Dynamic SQL Setting Default Values Dynamically Use the EXECUTE IMMEDIATE statement to set dynamic SQL default values; for example, to set the WARN attribute to the default value iud_nonaudited_index_maint. Examples C strcpy(hv_sql_statement,"control query default iud_nonaudited_index_maint 'warn'"); EXEC SQL EXECUTE IMMEDIATE :hv_sql_statement; COBOL move "control query default iud_nonaudited_index_maint 'warn'" to hv-sql-statement.
10 Dynamic SQL With Descriptor Areas Use dynamic SQL statements to construct, compile, and execute SQL statements during run time. Use the descriptor areas of NonStop SQL/MX to store information on each input parameter and output variable in a dynamic statement.
Dynamic SQL With Descriptor Areas SQL Descriptor Areas These statements are described on subsequent pages in this section. For the complete syntax of each statement, see the SQL/MX Reference Manual. SQL Descriptor Areas An SQL descriptor area consists of multiple item descriptor areas, together with a COUNT of the number of those item descriptor areas.
Dynamic SQL With Descriptor Areas Allocating an SQL Descriptor Area Allocating an SQL Descriptor Area Use the ALLOCATE DESCRIPTOR statement to allocate a named SQL descriptor area to store information necessary for the execution of dynamic SQL statements. If needed, allocate two descriptor areas: one for input parameters and one for output variables.
Dynamic SQL With Descriptor Areas Describing Input Parameters Use the DESCRIBE INPUT statement to set information in the descriptor area about the input parameters. Alternatively, you can use the SET DESCRIPTOR statement to set information explicitly in the descriptor area for individual input parameters. Describing Input Parameters All values in the item descriptor areas are initially undefined. You can use a DESCRIBE INPUT statement to set information on the input parameters in the descriptor area.
Dynamic SQL With Descriptor Areas Setting the Data Values of Input Parameters Example C COBOL strcpy (hv_sql_statement, "UPDATE employee" " SET salary = salary * 1.1" " WHERE jobcode = CAST(? AS NUMERIC(4) UNSIGNED)"); ... desc_max=1; EXEC SQL ALLOCATE DESCRIPTOR 'in_sqlda' WITH MAX :desc_max; ... EXEC SQL PREPARE sqlstmt FROM :hv_sql_statement; ... EXEC SQL DESCRIBE INPUT sqlstmt USING SQL DESCRIPTOR 'in_sqlda'; ... scanf("%hu",&in_jobcode); ...
Dynamic SQL With Descriptor Areas Setting Input Parameter Information Without DESCRIBE INPUT Setting Input Parameter Information Without DESCRIBE INPUT Use the SET DESCRIPTOR statement to describe the input parameters explicitly, without using DESCRIBE INPUT.
Dynamic SQL With Descriptor Areas Output Variables For multiple input parameters, set the individual descriptors in the order of their occurrence in the dynamic SQL statement. For a complete example of this method, see Example A-5 on page A-12. Output Variables NonStop SQL/MX returns data to a program through output variables. Output variables can be host variables or individual data buffers to which the program (through the SQL descriptor area) contains pointers.
Dynamic SQL With Descriptor Areas Consideration—Retrieving Multiple Values From a Large Buffer If you are retrieving the value of an output variable from the VARIABLE_DATA field in the descriptor area, the receiving host variable must be of a compatible data type and size for the information being retrieved. Retrieve the output values in the VARIABLE_DATA field by using the GET DESCRIPTOR statement within the COUNT loop and by testing on TYPE or NAME to assign the value to the host variable.
Dynamic SQL With Descriptor Areas C Consideration—Retrieving Multiple Values From a Large Buffer Example 10-1. C VARIABLE_POINTER Example (page 1 of 3) #include #include #include
Dynamic SQL With Descriptor Areas C Consideration—Retrieving Multiple Values From a Large Buffer Example 10-1.
Dynamic SQL With Descriptor Areas C Consideration—Retrieving Multiple Values From a Large Buffer Example 10-1.
Dynamic SQL With Descriptor Areas Steps for Using SQL Item Descriptor Areas Steps for Using SQL Item Descriptor Areas Figure 10-1 shows the steps presented within the complete C program. These steps are executed in the sample program Example A-4 on page A-8. C Figure 10-1. Using SQL Descriptor Areas in a C Program EXEC SQL BEGIN DECLARE SECTION; char hv_sql_statement[256]; long in_value; short num; /* Declare host variables for item descriptor values. long num_data; char char_data[100]; ...
Dynamic SQL With Descriptor Areas Steps for Using SQL Item Descriptor Areas Figure 10-2 shows the steps presented within the complete COBOL program. These steps are executed in the sample program Example C-4 on page C-9. COBOL Figure 10-2. Using SQL Descriptor Areas in a COBOL Program 1 EXEC SQL BEGIN DECLARE SECTION; 01 hv-sql-statement PIC X(256). 01 in-value PIC 9(5) COMP. 01 num PIC 9(4) COMP. * Declare host variables for item descriptor values. 01 num-data PIC S9(9) COMP. 01 char-data PIC X(100). .
Dynamic SQL With Descriptor Areas 4. 5. 6. 7. 8. 9.
Dynamic SQL With Descriptor Areas Prepare the SQL Statement Example C desc_max = 1; EXEC SQL ALLOCATE DESCRIPTOR 'in_sqlda' WITH MAX :desc_max; This descriptor area can hold only one dynamic input parameter in the prepared statement. Allocating the Output SQL Descriptor Area The number of item descriptor areas for the output SQL descriptor area must be large enough to hold all the columns of the table referred to in the prepared statement.
Dynamic SQL With Descriptor Areas Set Explicit Input Values Describing the Input Parameters Use the DESCRIBE INPUT statement to provide information for each dynamic input parameter in an item descriptor area, except for the actual VARIABLE_DATA and INDICATOR_DATA values.
Dynamic SQL With Descriptor Areas Set Explicit Input Values Setting the Parameter Values After user input, you can set the VARIABLE_DATA or VARIABLE_POINTER value in the item descriptor area. For limitations on using the VARIABLE_POINTER item, see using the VARIABLE POINTER in the SQL/MX Reference Manual.
Dynamic SQL With Descriptor Areas Execute the Prepared Statement ... EXEC SQL EXECUTE sqlstmt USING SQL DESCRIPTOR 'in_sqlda'; ... COBOL MOVE - "UPDATE employee" " SET salary = salary * 1.1" " WHERE jobcode = CAST(? AS NUMERIC(4) UNSIGNED)" " AND salary < CAST(? AS NUMERIC(8,2) UNSIGNED)" TO hv-sql-statement. ... MOVE 2 TO desc-max. EXEC SQL ALLOCATE DESCRIPTOR 'in_sqlda' WITH MAX :desc_max END-EXEC. ... EXEC SQL PREPARE sqlstmt FROM :hv-sql-statement END-EXEC.
Dynamic SQL With Descriptor Areas Get the Count and Descriptions of the Output Variables Example This statement uses both an input and output SQL descriptor area: C EXEC SQL EXECUTE sqlstmt USING SQL DESCRIPTOR 'in_sqlda' INTO SQL DESCRIPTOR 'out_sqlda'; Get the Count and Descriptions of the Output Variables When you have executed the prepared SQL statement, use the GET DESCRIPTOR statement to retrieve the output values from the output SQL descriptor area.
Dynamic SQL With Descriptor Areas Deallocate the Prepared Statement and the SQL Descriptor Areas :hv_jobcode = VARIABLE_DATA, :hv_jobcode_i = INDICATOR_DATA; if (hv_jobcode_i < 0) printf("\nJobcode is unknown"); else printf("\nJobcode is: %hu", hv_jobcode); } ... } /* end for */ ... /* process the item descriptor values */ COBOL * First, get the count of the number of output values. EXEC SQL GET DESCRIPTOR 'out_sqlda' :num = COUNT END-EXEC. * Second, get the i-th output values and save.
Dynamic SQL With Descriptor Areas Using SQL Descriptor Areas to Select SQL/MP KANJI and KSC5601 Data Example C EXEC SQL DEALLOCATE PREPARE sqlstmt; ... EXEC SQL DEALLOCATE DESCRIPTOR 'in_sqlda'; EXEC SQL DEALLOCATE DESCRIPTOR 'out_sqlda'; Using SQL Descriptor Areas to Select SQL/MP KANJI and KSC5601 Data See Example A-9 on page A-28 and Example A-10 on page A-30 for a detailed example.
Dynamic SQL With Descriptor Areas Using SQL Descriptor Areas to Retrieve ISO88591 Data to UCS2 Host Variables HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 10 -22
11 Dynamic SQL Cursors The dynamic SQL programs of NonStop SQL/MX use cursors to process multiple row SELECT statements in the same way that static SQL programs use cursors. The program reads rows from a result table, one by one, and sends the column values to output data buffers specified in the program. A static cursor is associated with an actual query expression (for example, a SELECT statement), and a dynamic cursor is associated with a statement prepared for the cursor specification.
Dynamic SQL Cursors Steps for Using a Dynamic SQL Cursor Steps for Using a Dynamic SQL Cursor Figure 11-1 shows the steps presented within the complete C program. These steps are executed in the sample program Example A-6 on page A-15. C Figure 11-1. Using a Dynamic SQL Cursor in a C Program 1 ... EXEC SQL BEGIN DECLARE SECTION; short hostvar; char curspec[80]; ... EXEC SQL END DECLARE SECTION; ... 2 strcpy(curspec,"SELECT column1,column2,column3" " FROM catalog.schema.
Steps for Using a Dynamic SQL Cursor Dynamic SQL Cursors Figure 11-2 shows the steps presented within the complete COBOL program. These steps are executed in the sample program Example C-5 on page C-13. COBOL Figure 11-2. Using a Dynamic SQL Cursor in a COBOL Program 1 ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 HOSTVAR 9(4) COMP. 01 CURSPEC PIC X(80). ... EXEC SQL END DECLARE SECTION END-EXEC. ... 2 - MOVE "SELECT column1, column2, column3" " FROM catalog.schema.
Dynamic SQL Cursors Declare Required Host Variables Declare Required Host Variables In an SQL Declare Section, declare the host variable you specify as the statement name for the cursor specification within the DECLARE CURSOR statement. You must also declare host variables used in the OPEN statement for dynamic input parameters.
Dynamic SQL Cursors • • Initialize the Dynamic Input Parameters In listing order, before other SQL statements that refer to the cursor, including the OPEN, FETCH, DELETE, UPDATE, and CLOSE statements Within the scope of other SQL statements that refer to the cursor Initialize the Dynamic Input Parameters Initialize the dynamic input parameters you specified in the cursor specification in the DECLARE CURSOR declaration. You must initialize the host variables before you execute the OPEN statement.
Dynamic SQL Cursors Process the Retrieved Values Alternatively, use this general syntax when transferring values to an output descriptor area (that has been allocated and described): FETCH cursor-name USING SQL DESCRIPTOR descriptor-name For further information on using this method, see Dynamic SQL Cursors Using Descriptor Areas on page 11-10. Process the Retrieved Values After the FETCH statement returns the values to the host variables, your program can process the values.
Dynamic SQL Cursors Using Date-Time and INTERVAL Data Types Suppose that you are finished with the dynamic SQL statement that specifies the cursor. Deallocate the resources used by the prepared cursor specification. The module that contains the DEALLOCATE PREPARE statement must also contain a PREPARE statement for statement-name. DEALLOCATE PREPARE statement-name For complete syntax, see the DEALLOCATE PREPARE statement in the SQL/MX Reference Manual.
Dynamic SQL Cursors Interval Example " FROM samdbcat.persnl.project" " WHERE start_date <= CAST(? AS DATE)"; /* Prepare the cursor specification. */ EXEC SQL PREPARE cursor_spec FROM :curspec; /* Declare the dynamic cursor from the prepared statement. */ EXEC SQL DECLARE get_by_projcode CURSOR FOR cursor_spec; /* Initialize the parameter in the WHERE clause.
Dynamic SQL Cursors Nonstandard SQL/MP DATETIME Example * Declare the dynamic cursor from the prepared statement. EXEC SQL DECLARE get_by_projcode CURSOR FOR cursor_spec END-EXEC. * Initialize the parameter in the WHERE clause. DISPLAY "Enter the minimum estimated number of days: ". ACCEPT in-est-complete. * Open the cursor using the values of the dynamic parameter. EXEC SQL OPEN get_by_projcode USING :in-est-complete END-EXEC. * Fetch the first row of result from table.
Dynamic SQL Cursors Dynamic SQL Cursors Using Descriptor Areas /* Declare the dynamic cursor from the prepared statement. */ EXEC SQL DECLARE get_by_projcode CURSOR FOR cursor_spec; /* Initialize the parameter in the WHERE clause. */ printf("Enter the latest start date in the form mm-dd: "); scanf("%s", in_start_date); /* Open the cursor using the value of the dynamic parameter. */ EXEC SQL OPEN get_by_projcode USING :in_start_date; /* Fetch the first row of the result table.
12 Dynamic SQL Rowsets The dynamic SQL statements of NonStop SQL/MX can use rowsets to: • • Provide an array of input values in an INSERT, UPDATE, DELETE, or SELECT statement. Retrieve an array of output values in a FETCH statement.
Dynamic SQL Rowsets Preparing an SQL Statement With Dynamic Rowsets Data can be exchanged between a dynamic embedded SQL application and the database by using rowset-type host variables (as in static SQL) or by using the address of memory locations in application space, which have been suitably prepared to send data to or receive data from the database. Consequently, dynamic rowsets can be used without declaring rowset-type host variables in the DECLARE section.
Dynamic SQL Rowsets Specification of an Rowset Parameter in the PREPARE String to fetch one row at a time and multiple rows at a time by using dynamic rowsets in separate FETCH calls. Specification of an Rowset Parameter in the PREPARE String To use a rowset host variable for an input parameter, indicate its length by '?[positive-integer-constant]' in the PREPARE string. For example: ?[10] The length specifier must be a positive integer (> 0) and a constant. Otherwise, a parse error is raised.
Dynamic SQL Rowsets Matching Compile-Time Specified Length With Execution-Time Length Using the FOR INPUT SIZE and KEY BY Syntax Example This statement uses the FOR INPUT SIZE and KEY BY syntax with an array input parameter in the WHERE clause. The input size is always a scalar parameter, and its value must be less than the length of the input parameter array.
Dynamic SQL Rowsets Dynamic SQL With Argument Lists Dynamic SQL With Argument Lists If you use dynamic SQL with argument lists only and not descriptor areas, see the discussion Preparing an SQL Statement With Dynamic Rowsets on page 12-2. The next subsections apply only to dynamic SQL with descriptor areas. Rowset host variables can also be used in the USING and INTO clauses of the EXECUTE statement for input and output variables, respectively. In this case, you do not need to set any descriptor fields.
Dynamic SQL Rowsets Setting the Rowset-Specific Descriptor Fields set-descriptor-info is: COUNT = value-specification | ROWSET_SIZE = value-specification | VALUE item-number set-item-info [, set-item-info]... set-item-info is: descriptor-item-name = value-specification descriptor-item-name is: ROWSET_VAR_LAYOUT_SIZE | ROWSET_IND_LAYOUT_SIZE | other-descriptor-item-names The value-specification can be a literal or a host variable with exact numeric data type.
Setting the Rowset-Specific Descriptor Fields Dynamic SQL Rowsets set this descriptor field manually, use the sizeof function and make its value equal to sizeof (individual array element). Note. The SQL/MX extension TYPE -601 (character varying with length specified in the first two bytes) is a special case. If rowsets are to be used with this data type, the ROWSET_VAR_LAYOUT_SIZE field must not include the two bytes used to specify varying character length.
Setting the Rowset-Specific Descriptor Fields Dynamic SQL Rowsets Table 12-1. Minimum Values for ROWSET_VAR_LAYOUT_SIZE Descriptor Field (page 2 of 2) SQL Data Type ROWSET_VAR_LAYOUT_SIZE INT[EGER] SIGNED INT[EGER] UNSIGNED 4 LARGEINT 8 FLOAT (1 to 22 bits) REAL 4 FLOAT (23 to 54 bits) DOUBLE PRECISION 8 DATE 11 TIME, TIMESTAMP l+1 ** INTERVAL l+1 *** * All character format data is assumed to be null terminated.
Dynamic SQL Rowsets Exclusive Use of VARIABLE_POINTER and INDICATOR_POINTER If the value specified for this descriptor item field does not meet these guidelines, an execution time error of memory access violation or data type mismatch can result. Exclusive Use of VARIABLE_POINTER and INDICATOR_POINTER When you use rowset host variables to transfer data to and from a database, VARIABLE_DATA and INDICATOR_DATA descriptor item fields must not be used.
Dynamic SQL Rowsets Using the DESCRIBE INPUT Statement For the full description of the GET DESCRIPTOR Statement, including the otherdescriptor-item-names, see the SQL/MX Reference Manual. The variablename must be a host variable with exact numeric data type. Using the DESCRIBE INPUT Statement Use the DESCRIBE INPUT statement after PREPARE to fill rowset-specific descriptor fields with appropriate values. This strategy frees you from setting these descriptor fields manually before execution.
13 Exception Handling and Error Conditions In NonStop SQL/MX, a warning or error condition is also referred to as an exception. Your host language application program can detect these exceptions and can gather diagnostic information after the execution of each of the program’s SQL statements.
SQL:1999 SQLSTATE Values Exception Handling and Error Conditions Examples In a C program, you must include an extra character for the null terminator. Declare SQLSTATE within the Declare Section: C EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6]; ... EXEC SQL END DECLARE SECTION; In a COBOL program, declare SQLSTATE within the Declare Section: COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 SQLSTATE PIC X(5). ... EXEC SQL END DECLARE SECTION END-EXEC.
SQL/MX SQLSTATE Values Exception Handling and Error Conditions Table 13-1.
Using SQLSTATE Exception Handling and Error Conditions Table 13-2. Mapping of SQLCODE to SQL/MX-Defined SQLSTATE Values SQLCODE SQLSTATE Class Origin Subclass Origin Description 100 02000 ISO 9075 ISO 9075 No data n<0 X0yzz SQL/MX SQL/MX Error n > 0 (<> 100) 01yzz ISO 9075 SQL/MX Warning In Table 13-2, for the last two cases, the subclass abbreviated yzz is in one of the following ranges: W00 through W09, W0A through WZZ, X00 through X09, or X0A through XZZ.
Exception Handling and Error Conditions Checking the SQLCODE Variable This example checks the value of the SQLSTATE variable only after the UPDATE statement. To ensure your program is executing properly, you must check SQLSTATE after every SQL statement. For further information on how to do this without error checking after every statement, see Using the WHENEVER Statement on page 13-8. Example COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 SQLSTATE PIC X(5). ... EXEC SQL END DECLARE SECTION END-EXEC.
Exception Handling and Error Conditions • SQLCODE Values If you do not declare SQLCODE anywhere in your program but you do not declare SQLSTATE within a Declare Section, the language compiler returns error and warning messages. Note. You should declare SQLCODE (anywhere in your program) or declare SQLSTATE within a Declare Section. If you are not checking SQLSTATE, do not declare SQLSTATE because query execution requires string processing to return the SQLSTATE value.
Exception Handling and Error Conditions SQL/MX Exception Condition Messages ... void process_sqlcode(void) { printf("\nError or warning occurred! SQLCODE = %d",SQLCODE); ... /* Process the SQL error. */ } COBOL * Global COBOL declarations * Declare SQLCODE with data type PIC S9(9) COMP. 01 SQLCODE PIC S9(9) COMP. ... * Set new_jobcode and new_jobdesc host variables. EXEC SQL INSERT INTO sales.job (jobcode,jobdesc) VALUES (:new-jobcode,:new-jobdesc) END-EXEC.
Exception Handling and Error Conditions Accessing SQL Messages Within a Program SQL 1000 1000 42000 A syntax error occurred. Cause. Syntax was entered incorrectly. Effect. SQL is unable to prepare the statement. Recovery. Correct the syntax error and resubmit. The second number, if present, is the corresponding SQL:1999 SQLSTATE value. In this example, SQLSTATE 42000 is an ANSI value. Within MXCI, you can display text associated with a message number or SQLCODE value by using the ERROR command.
Exception Handling and Error Conditions • Precedence of Multiple WHENEVER Declarations SQL_WARNING condition: An SQL warning occurred. SQLSTATE does not indicate a no-data or an error condition. SQLCODE is greater than zero and not equal to 100. You must specify the WHENEVER declaration in your program before the SQL statements to which it applies.
Exception Handling and Error Conditions • Enabling and Disabling the WHENEVER Declaration A WHENEVER declaration remains in effect until another WHENEVER declaration for the same condition appears. If you want to execute a different routine when an error occurs, specify a new WHENEVER declaration with a different CALL routine.
Exception Handling and Error Conditions Saving and Restoring SQLSTATE or SQLCODE EXEC SQL OPEN get_by_partnum; ... EXEC SQL FETCH get_by_partnum INTO :hv_partnum,:hv_partdesc,:hv_price,:hv_qty_available; ... while (SQLCODE == 0) { if ( hv_qty_available < 1000 ) EXEC SQL UPDATE parts SET qty_available = qty_available + 100 WHERE CURRENT OF get_by_partnum; ... EXEC SQL FETCH get_by_partnum INTO :hv_partnum,:hv_partdesc,:hv_price,:hv_qty_available; } ...
Exception Handling and Error Conditions Declaring SQLSTATE or SQLCODE in an Error Routine PERFORM VARYING i FROM 1 BY 1 UNTIL i > hv-num MOVE SPACES TO hv-msgtxt EXEC SQL GET DIAGNOSTICS EXCEPTION :i :hv-sqlstate = RETURNED_SQLSTATE, :hv-msgtxt = MESSAGE_TEXT END-EXEC. DISPLAY "SQLSTATE: " hv-sqlstate DISPLAY "Message : " hv-msgtxt END-PERFORM. MOVE saved-sqlstate TO sqlstate. ... END PROGRAM Program-exF72.
Exception Handling and Error Conditions Accessing and Using the Diagnostics Area return 0; } /* end main */ ... void sql_error() { EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6]; long hv_num; unsigned short i; char hv_sqlstate[6]; VARCHAR hv_msgtxt[129]; EXEC SQL END DECLARE SECTION; EXEC SQL GET DIAGNOSTICS :hv_num = NUMBER; for (i = 1; i <= hv_num; i++) { EXEC SQL GET DIAGNOSTICS EXCEPTION :i :hv_sqlstate = RETURNED_SQLSTATE, :hv_msgtxt = MESSAGE_TEXT; ...
Exception Handling and Error Conditions Getting Statement and Condition Items The statement-info is defined as: target = stmt-item-name [,target = stmt-item-name]... The condition-info is defined as: EXCEPTION condition-number target = condtn-item-name [,target = condtn-item-name]... The target is a host variable that receives the requested diagnostics information. target must have the same data type as the stmt-item-name or condtn-itemname you are requesting.
Exception Handling and Error Conditions Special SQL/MX Error Conditions ... /* Process the SQL error. */ } ... } /* end get_diagnostics */ COBOL * Set new-jobcode and new-jobdesc host variables. ... EXEC SQL INSERT INTO sales.job (jobcode,jobdesc) VALUES (:new-jobcode,:new-jobdesc) END-EXEC. ... IF SQLSTATE = SQLSTATE_OK DISPLAY "Values were inserted!" EXEC SQL COMMIT WORK END-EXEC. ELSE PERFORM 1000-GET-DIAGNOSTICS. ... STOP RUN. 1000-GET-DIAGNOSTICS. EXEC SQL GET DIAGNOSTICS :num = NUMBER ...
Exception Handling and Error Conditions Occurrences of the Lost Open Error operation invalidates the open held by the program to change the structure of the table or view and gains exclusive access to the table or view. A program could also lose its open on a table or view when a network or hardware interruption occurs.
14 Transaction Management A transaction, which is a set of database changes that must be completed as a group, is the basic recoverable unit in case of a failure or transaction interruption. The typical order of events is: 1. Transaction is started. 2. Database changes are made. 3. Transaction is committed if the database changes are made successfully.
Steps for Ensuring Data Consistency Transaction Management C Figure 14-1. Coding Transaction Control Statements in a C Program 2 /* Set attributes for the transaction. */ EXEC SQL SET TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE, DIAGNOSTICS SIZE 10; ... 3 EXEC SQL BEGIN WORK; ... 4 EXEC SQL UPDATE ... ; ... 5 if (strcmp(SQLSTATE, SQLSTATE_OK) == 0) { /* Test if OK. */ ... ... EXEC SQL COMMIT WORK; /* Commit database changes. */ } else { ...
Transaction Management Declaring Required Variables Figure 14-2. Coding Transaction Control Statements in a COBOL Program 5 COBOL 6 7 * Test if database change is ok. IF SQLSTATE = SQLSTATE-OK ... * Commit database change. EXEC SQL COMMIT WORK END-EXEC. ELSE ... * Roll back database change. EXEC SQL ROLLBACK WORK END-EXEC. END-IF. ... For more information, see: 1. 2. 3. 4. 5. 6. 7.
Transaction Management Setting Attributes for Transactions Example The attributes include access mode and isolation level, which affect the degree of concurrent data access: C ... EXEC SQL SET TRANSACTION READ ONLY, ISOLATION LEVEL READ UNCOMMITTED, DIAGNOSTICS SIZE 10; ... The last attribute shown in this example, the diagnostics size, is an estimate of the number of exception conditions you might expect as a result of executing an SQL statement within the transaction.
Transaction Management Setting Attributes for Transactions Isolation Level Setting The isolation level specifies the level of data consistency defined for the transaction and the degree of concurrency the transaction has with other transactions that use the same data. The isolation level of a transaction can be READ UNCOMMITTED, READ COMMITTED, or SERIALIZABLE (or REPEATABLE READ). For more information, see the SQL/MX Reference Manual.
Transaction Management Starting a Transaction Precedence of Transaction Isolation Levels NonStop SQL/MX determines the transaction isolation level, based on these settings, in order of precedence, from highest to lowest: 1. If you specify an access option explicitly in a DML statement, the SQL/MX compiler compiles the statement with the access option. This access option overrides the isolation level of any containing transactions. 2.
Transaction Management Processing Database Changes In a program, you might use a loop when updating or deleting rows in the result set of a cursor. In a looping UPDATE or DELETE (either searched or positioned), NonStop SQL/MX commits changes as they occur within the loop when autocommit is on. To ensure database consistency when autocommit is on, issue BEGIN WORK before the loop starts (or before declaring the cursor) and issue COMMIT WORK after all changes have been made within the loop.
Transaction Management Committing Database Changes if No Errors Occur However, the diagnostics area provides more information than the SQLSTATE variable because condition information is stored for each exception condition that occurs during execution of an SQL statement. Note. The results of executing an SQL statement overlay the results of the previous SQL statement in the diagnostics area. Therefore, test for exception conditions after the execution of each statement within your transaction.
15 C/C++ Program Compilation This section describes how to develop and execute a C/C++ program that contains embedded SQL statements.
C/C++ Program Compilation Compiling SQL/MX Applications and Modules Compiling SQL/MX Applications and Modules NonStop SQL/MX Release 2.x provides two methods of compiling embedded SQL C/C++ programs and creating modules. Both methods create an identical module file. The first method described, using embedded module definitions, is the default and preferred method.
Compiling Embedded SQL C/C++ Programs With Embedded Module Definitions C/C++ Program Compilation Figure 15-1. Compiling Embedded SQL C/C++ Programs With Embedded Module Definitions 1 SQL/MX SQL/MX C/C++ C/C++Source SourceFile File sqlprog.sql sqlprog.sql SQL/MX C/C++ Preprocessor (mxsqlc) 2 3 C/C++ C/C++Annotated Annotated Source SourceFile File sqlprog.c sqlprog.cor or sqlprog.cpp sqlprog.
C/C++ Program Compilation Compiling Embedded SQL C/C++ Programs With Embedded Module Definitions 2. Preprocess the application’s embedded SQL source files by using the SQL/MX C/C++ preprocessor mxsqlc. See Running the SQL/MX C/C++ Preprocessor on page 15-8. mxsqlc sqlprog.sql -c sqlprog.c In this step, set optional module specification strings and moduleCatalog and moduleSchema default settings by using the -g option. See 15-20 or 15-26.
C/C++ Program Compilation Compiling Embedded SQL C/C++ Programs With Embedded Module Definitions 7. The linker produces the application’s executable file, sqlprog.exe. 8. SQL compile one, some, or all of the application’s embedded module definitions in the executable file by using mxCompileUserModule. See Running the SQL/MX Compiler on page 15-30 and Compiling Embedded Module Definitions on page 15-30. mxCompileUserModule sqlprog.exe 9. The SQL/MX compiler produces the SQL/MX module.
Compiling Embedded SQL C/C++ Programs With Module Definition Files C/C++ Program Compilation Compiling Embedded SQL C/C++ Programs With Module Definition Files Figure 15-2 shows how a C/C++ program with separate module definition files is compiled. The application’s embedded SQL source file is called sqlprog.sql. Figure 15-2. Compiling Embedded SQL C/C++ Programs With Module Definition Files SQL/MX SQL/MX C/C++ C/C++Source SourceFile File sqlprog.sql sqlprog.
C/C++ Program Compilation Compiling Embedded SQL C/C++ Programs With Module Definition Files 1. Create the C or C++ source files that contain embedded SQL statements (sqlprog.sql). 2. Preprocess the application’s embedded SQL source files by using the SQL/MX C/C++ preprocessor mxsqlc. See Running the SQL/MX C/C++ Preprocessor on page 15-8. mxsqlc sqlprog.sql -c sqlprog.c -m sqlprog.
C/C++ Program Compilation Creating Modules: From Development to Production 8. SQL compile the application’s module definition file by using the SQL/MX compiler (mxcmp). See Running the SQL/MX Compiler on page 15-30 and Compiling a Module Definition File on page 15-34. mxcmp sqlprog.m 9.
C/C++ Program Compilation Preprocessor Functions The syntax for using the preprocessor in each environment appears under Syntax for the OSS-Hosted SQL/MX C/C++ Preprocessor on page 15-17 and Syntax for the Windows-Hosted SQL/MX C/C++ Preprocessor on page 15-23. Preprocessor Functions The preprocessor processes C/C++ and SQL source statements. C/C++ Source Statements The preprocessor writes each C/C++ source statement to the C/C++ annotated source file.
C/C++ Program Compilation • Preprocessor Functions The contents of the mine.h and mine2 files are included if -h is specified, and NOLIST is ignored: #include "mine.h" #include "../includes/mine2" NOLIST • The contents of file mapped by DEFINE =cdef1 are included, if -h is specified: #include "=cdef1" As with any #include file inclusion, you must ensure that implementation of conditional compilation does not result in repeated file inclusion.
C/C++ Program Compilation Preprocessor Functions int a1; EXEC SQL END DECLARE SECTION; #pragma section sect2 EXEC SQL BEGIN DECLARE SECTION; int a2; EXEC SQL END DECLARE SECTION; #pragma section sect3 EXEC SQL BEGIN DECLARE SECTION; int a3; EXEC SQL END DECLARE SECTION; C #Pragma MXH and #Pragma NOMXH Directive The preprocessor processes all the user header files (for example, #include "file1.h") , that are within the pragma directives MXH and NOMXH, regardless of the header file extension.
C/C++ Program Compilation Preprocessor Functions This #define directive: #define SQL_Control_Table(defname) EXEC SQL CONTROL TABLE defname TABLELOCK ‘OFF’; SQL_Control_Table(fldrenty); SQL_Control_Table(postact); SQL_Control_Table(permdeny); is expanded to: EXEC SQL CONTROL TABLE fldrenty TABLELOCK 'OFF'; EXEC SQL CONTROL TABLE postact TABLELOCK 'OFF'; EXEC SQL CONTROL TABLE permdeny TABLELOCK 'OFF'; The preprocessor also expands #define directives that occur within host variable parameters.
C/C++ Program Compilation Preprocessor Functions name. The C/C++ comment must use only one line and must immediately precede the SQL statement. For example, this comment names the SQL statement (INSERT) and provides comment text (“insert ten rows”): /* SQL statement_name= INSERT insert ten rows */ EXEC SQL INSERT INTO ...
C/C++ Program Compilation Preprocessor Output Use the preprocessor to embed SQL anywhere in the C/C++ source file. However, the preprocessor determines in which part of the source file the embedded SQL is located and issues warnings if an embedded SQL statement is not placed correctly. See Placement of SQL Statements on page 2-2. At the end of processing the embedded SQL C/C++ source file, the preprocessor checks the status of static cursors: • • Cursors accessed and not opened return an error message.
Preprocessor Output C/C++ Program Compilation The C/C++ annotated source file consists of: Header Contains the declarations within the CLI functions and data structures. Body Contains the embedded SQL C/C++ source file translated into C/C++ statements. The preprocessor encloses each embedded SQL statement with C comment delimiters and follows the commented statement with a CLI call that invokes the executor at run time to execute the statement.
C/C++ Program Compilation OSS-Hosted SQL/MX C/C++ Preprocessor program is synchronized with the version of the module file. This strategy prevents, for example, the executable program from being altered and rebuilt without rebuilding the module file. For more information, see Understanding and Avoiding Some Common Run-Time Errors on page 15-65. The ISO88591 character set is the default character set for CHAR or VARCHAR data types for NonStop SQL/MX.
C/C++ Program Compilation OSS-Hosted SQL/MX C/C++ Preprocessor Syntax for the OSS-Hosted SQL/MX C/C++ Preprocessor mxsqlc sql-file [ -c output-file ] [ -m module-def-file ] [ -e ] [ -n ] [ -a ] [ -l list-file ] [ -p ] [ -o ] [ -t timestamp ] [ -d flag[=value]] [ -h ] [ -i pathname ] [ -x ] [ -g {moduleGroup[=module-group-specification-string] |moduleTableSet[=module-tableset-specificationstring] |moduleVersion[=module-version-specificationstring] |moduleCatalog[=module-catalog-name] |moduleSchema[=module-
C/C++ Program Compilation OSS-Hosted SQL/MX C/C++ Preprocessor -e generates CHARACTER data types for date-time data types. This behavior is compatible with NonStop SQL/MX Release 1.8. For more information, see INVOKE and Date-Time and Interval Host Variables (SQL/MX Release 1.8 Applications) on page 3-32. -n directs the preprocessor to automatically append a null terminator to all host variable character strings before they are fetched into.
C/C++ Program Compilation OSS-Hosted SQL/MX C/C++ Preprocessor -t timestamp provides a creation timestamp that the preprocessor writes to the C/C++ annotated source file (and the module definition file if the -x or -m preprocessor option or the SQLMX_PREPROCESSOR_VERSION=800 environment variable is used). The timestamp value overrides the operating system timestamp. For example, you can specify these timestamp values: -t "2005-10-26 09:01:20.00" -t 2005-10-26.12:0000.
C/C++ Program Compilation OSS-Hosted SQL/MX C/C++ Preprocessor -g {moduleGroup[=module-group-specification-string] |moduleTableSet[=module-tableset-specification-string] |moduleVersion[=module-version-specification-string] |moduleCatalog[=module-catalog-name] |moduleSchema[=module-schema-name] } specifies the arguments for qualifying the name given to the compiled module file. If you use this option, you must supply at least one of the five module management attributes.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor SQL/MX Reference Manual. The maximum size for the moduleCatalog attribute is 128 characters. moduleSchema sets the moduleSchema attribute if the input sql-file does not have a MODULE directive or its MODULE directive does not specify a schema name. The moduleSchema can contain a catalog name.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor files are installed in the C:\Program Files\HP SQL-MX C Preprocessors directory. Use either the command shell or the Korn shell to run the preprocessor with the RUN command. You can also use ETK to build a C or C++ program. For more information, see Building a C/C++ Program With Embedded SQL Statements on Windows on page 15-47. You can install multiple versions of the SQL/MX C/C++ preprocessors.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor The environment variable MXSQLC must be set before starting ETK. ° Enterprise Plugins for Eclipse (EPE)—plug-in for Eclipse When Eclipse is used, MXSQLC is set by EPE based on the value of the preprocessor installation location.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor source-file.c, where source-file is the name of the SQL/MX C/C++ source file (for example, sqlprog.sql) without the file extension. -m module-def-file is the name of the output module definition file, which is the input file for the SQL/MX compiler. The default is source-file.m, where source-file is the name of the SQL/MX C/C++ source file (for example, sqlprog.sql) without the file extension.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor type REAL, this option causes the invoked structure to be of type DOUBLE. For more information, see INVOKE and Floating-Point Host Variables on page 3-33. By default, the c89 compiler (TNS/E targeted compilation) defaults to IEEE_float and will invoke the -o option when it calls the preprocessor. If you want your program to use Tandem_float, use the c89 -Wtandem_float option to compile the mxsqlc-generated annotated source file.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor -z NSK-password is the password for the user name for the NonStop system. This option is required if you use INVOKE. -h enables the processing of files specified in the user #include directive regardless of their extension. The default action is to ignore these files. -i pathname specifies a directory path to be searched for a file specified in an #include directive. The source path is searched first.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor Grouping on page 17-21. The maximum size for the moduleGroup attribute is 31 characters. moduleTableset sets the moduleTableSet attribute to use the module management targeting feature. You can create different sets of module files that can be used against different sets of tables. For more information, see Targeting on page 17-12. The maximum size for the moduleTableSet attribute is 31 characters.
Running the C/C++ Compiler and Linker C/C++ Program Compilation invokeSchema sets the schema for unqualified objects inside the invoke clause as schemaname. If a schema is specified using the Control Query Default Schema or Declare Schema, this attribute has no effect. The maximum size of the invokeSchema attribute is 128 characters. Example—mxsqlc Run the SQL/MX C/C++ preprocessor using the mxsqlc command. This C++ example creates an annotated source file and module definition file: mxsqlc sqlprog.
Running the C/C++ Compiler and Linker C/C++ Program Compilation Table 15-1.
C/C++ Program Compilation Running the SQL/MX Compiler Steps on page 15-37, the OSS reference pages, or the Open System Services Shell and Utilities Reference Manual.
C/C++ Program Compilation Compiling Embedded Module Definitions Deploying a C Application With Embedded Module Definitions and Module Definition Files on page 15-50. Command-Line Syntax To invoke mxCompileUserModule, at an OSS prompt, enter: mxCompileUserModule [ -e] [ -v] [-g {moduleGlobal | moduleLocal}] [-d compiler-attribute-name=compiler-attribute-value] ... application-file ["{"module-name[, module-name]..."}"] module-name is: [[catalog.]schema.
C/C++ Program Compilation Compiling Embedded Module Definitions module with its application. For more information, see Generating Locally or Globally Placed Modules on page 17-3. -d compiler-attribute-name=compiler-attribute-value specifies default attribute settings for compilation and existing settings for the module name.
C/C++ Program Compilation • • Compiling Embedded Module Definitions Must not specify a Guardian subvolume (/G/...) or a remote directory in an Expand network (/E/...). Must not exceed 1024 characters. module-name is the fully qualified name of an embedded module definition. This option names the generated module that is written to the user-specified local application directory or to the global /usr/tandem/sqlmx/USERMODULES directory. For more information, see Module Management Naming on page 17-8.
C/C++ Program Compilation MXCMP Environment Variable Examples—mxCompileUserModule • This command compiles the embedded module definition: mxCompileUserModule sqlprog.exe • This command places the module file in the same OSS directory as the application executable: mxCompileUserModule -g moduleLocal sqlprog.o • These settings affect statement recompilation at execution time: mxCompileUserModule -d AUTOMATIC_RECOMPILATION=ON \ -d SIMILARITY_CHECK=ON sqlprog.
C/C++ Program Compilation Compiling a Module Definition File generate module definition files as done in SQL/MX Release 1.8 and previous releases. To compile a module definition file, use the SQL/MX compiler mxcmp command on the module definition (.m) file. The SQL/MX compiler places a compiled user module file in the global /usr/tandem/sqlmx/USERMODULES directory or in the user-specified local application directory.
C/C++ Program Compilation Compiling a Module Definition File If you do not specify -g moduleLocal=OSSdir but set MXCMP_PLACES_LOCAL_MODULES ON, you must be in the same directory as the application executable when you invoke mxcmp. Otherwise, mxcmp writes the module in the current directory, and you will need to move the module to the global USERMODULES directory or co-locate the module with its application. For more information, see Generating Locally or Globally Placed Modules on page 17-3.
c89 Utility: Using One Command for All Compilation Steps C/C++ Program Compilation c89 Utility: Using One Command for All Compilation Steps In the OSS environment, the c89 utility provides the interface to C/C++ compilation components, including the SQL/MX C/C++ preprocessor, the native C and C++ compilers, native C run-time library, and the native object file linker (eld or nld). nld is available on systems running H06.05 or later RVUs.
C/C++ Program Compilation c89 Utility: Using One Command for All Compilation Steps preprocess_only Runs the SQL/MX preprocessor only. process_includes Processes one level of #include files. noansi_varchars Directs the SQL/MX preprocessor to turn off generation of ANSI varchar data. null_terminate Automatically appends a null terminator to all host variable character strings before they are fetched into.
C/C++ Program Compilation c89 Utility: Using One Command for All Compilation Steps warn Directs the SQL/MX compiler to return a warning rather than an error if a table does not exist at compile time. verbose Directs the SQL/MX compiler to display summary information, in addition to error and warning messages. -Wmxcmp_querydefault= compiler_attribute_name= compiler_attribute_value [,compiler-attributevalue...] Directs the SQL/MX compiler to issue the control query default setting at the command line.
c89 Utility: Using One Command for All Compilation Steps C/C++ Program Compilation -WmoduleTableSet[=[string]] Directs the SQL/MX preprocessor to use the module management targeting feature. Create different sets of module files that can be used against different sets of tables. For more information, see Targeting on page 17-12. -WmoduleVersion[=[string]] Allows multiple versions of an application’s module files to coexist while keeping the same MODULE directive in each version.
C/C++ Program Compilation c89 Utility: Using One Command for All Compilation Steps SQL/MX preprocessor works, see OSS-Hosted SQL/MX C/C++ Preprocessor on page 15-16. Compiling C/C++ Statements Use the c89 utility to compile the C/C++ statements in a preprocessed file to create an object (.o) file. The c89 utility determines which compiler to use based on the file extension you use (.ec or .sql for embedded SQL/MX C programs, .ecpp for embedded SQL/MX C++ programs, and .c for embedded SQL/MP C programs).
C/C++ Program Compilation c89 Examples With Embedded Module Definitions c89 Examples With Embedded Module Definitions Figure 15-3 shows how the c89 utility compiles a C/C++ program with embedded module definitions. Figure 15-3.
C/C++ Program Compilation • c89 Examples With Embedded Module Definitions This command preprocesses, compiles, links, and SQL compiles a single C source file named sqlprog.ec: c89 -Wsqlmx -Wmxcmp -o sqlprog.exe sqlprog.ec The c89 utility invokes the preprocessor, mxsqlc, which uses the file sqlprog.ec as input and produces one file, sqlprog.c, which is a C annotated source file that contains embedded module definitions. The c89 utility then compiles and links sqlprog.
C/C++ Program Compilation ° c89 Examples With Embedded Module Definitions For example, this eld command links sqlprog1.o and sqlprog2.o to create an executable file named sqlprog.exe: eld /usr/lib/ccplmain.o sqlprog1.o sqlprog2.o \ -o sqlprog.exe -lzcredll -lzcrtldll -lzosskdll \ -lzicnvdll -lzclidll For more information on eld, see the eld Manual. ° For example, this nld command links sqlprog1.o and sqlprog2.o to create an executable file named sqlprog.exe: nld sqlprog1.o sqlprog2.o -o sqlprog.
c89 Examples With Module Definition Files C/C++ Program Compilation c89 Examples With Module Definition Files Figure 15-4 shows how the c89 utility compiles a C/C++ program with module definition files. Figure 15-4.
C/C++ Program Compilation c89 Examples With Module Definition Files sqlprog.c to produce the executable file, sqlprog.exe. The SQL/MX compiler command -Wmxcmp processes the module definition file sqlprog.m with the SQL/MX compiler, mxcmp, to produce the module. • This command preprocesses several C++ source files and compiles them, but it does not link the results: c89 -c -Wsqlmx -Wsqlmxadd=-x file1.eC file2.ecc file3.
C/C++ Program Compilation Examples of Building and Deploying Embedded SQL C/C++ Programs Examples of Building and Deploying Embedded SQL C/C++ Programs The examples in this subsection use SQL/MP tables and SQL/MX Release 2.x. Building a C/C++ Program With Embedded SQL Statements on Windows You can build a C or C++ program by using the ETK product which is available on Microsoft Windows. Although the following example does not use ETK components, the ETK product is easy to use to create programs.
C/C++ Program Compilation Building a C/C++ Program With Embedded SQL Statements on Windows mxsqlc sqlprog1.sql -c sqlprog1.cpp -s pecan \ -y finance.tomr -z abc123 \ -g moduleCatalog=FINANCE -g moduleSchema=WINDEV mxsqlc sqlprog2.sql -c sqlprog2.cpp -s pecan \ -y finance.tomr -z abc123 \ -g moduleCatalog=FINANCE -g moduleSchema=WINDEV These commands create two annotated source files (sqlprog1.cpp and sqlprog2.
C/C++ Program Compilation Developing a Native C/C++ Program With Embedded SQL/MX Statements on OSS Developing a Native C/C++ Program With Embedded SQL/MX Statements on OSS This example illustrates how to use OSS tools to build a C or C++ program that has two or more source files containing embedded SQL/MX statements into a selfcontained application executable file. Suppose that your schema is named OSSDEV under the catalog named FINANCE. 1. Create C or C++ source files (for example, sqlprog1.
C/C++ Program Compilation Building and Deploying a C Application With Embedded Module Definitions and Module Definition 3. Run the C/C++ compiler to compile the annotated source files into object files: c89 -Wversion2 -I /usr/tandem/sqlmx/include -c sqlprog1.cpp c89 -Wversion2 -I /usr/tandem/sqlmx/include -c sqlprog2.cpp 4. Run the native linker to build a self-contained OSS executable file named sqlprog. • For TNS/E native applications, use the eld utility: eld -verbose /usr/lib/ccplmain.o sqlprog1.
C/C++ Program Compilation Building and Deploying a C Application With Embedded Module Definitions and Module Definition EXEC SQL DECLARE CURSOR m FOR SELECT * FROM =midcaps; int main() { ... } 2. Run the SQL/MX C/C++ preprocessor: mxsqlc sqlapp.sql -c sqlapp.cpp This command creates an annotated source file (sqlapp.cpp) that contains the SQL/MX CLI call translations of the embedded SQL statements and an extra C/C++ source construct that represents its module definition.
C/C++ Program Compilation Quick Builds and mxcmp Defaults in a One-File Application Deployment Quick Builds and mxcmp Defaults in a One-File Application Deployment Suppose that you are actively developing, testing, and debugging a new SQL/MX Release 2.x application that you have organized into three separate static SQL C/C++ source files.
C/C++ Program Compilation Quick Builds and mxcmp Defaults in a One-File Application Deployment These modules use the default mxsqlc option that generates the annotated output source files s1.c, s2.c, and s3.c. 3. Run the C/C++ compiler: cd /usr/meas c89 -Wversion2 -I /usr/tandem/sqlmx/include -c s1.c c89 -Wversion2 -I /usr/tandem/sqlmx/include -c s2.c c89 -Wversion2 -I /usr/tandem/sqlmx/include -c s3.c This step generates the object files s1.o, s2.o, and s3.o. 4. Run the native linker.
C/C++ Program Compilation Deploying a Static SQL Application to an RDF System 8. Repeat Step 1 to Step 7 until the program is ready for deployment or until you cannot find any more bugs, whichever comes first. Transfer the program to its target deployment NonStop system (for example, \batman): /usr/prod/sprog. 9.
C/C++ Program Compilation Deploying a Static SQL Application to an RDF System Table 15-2. Module Schemas and Export Files for RDF SQL Application Deployment Example (page 2 of 2) Development OSS directory for backup /usr/backup =debits DEFINE for primary $data07.ccards.debits =debits DEFINE for backup $data07.ccards.debits =credits DEFINE for primary $data17.ccards.credits =credits DEFINE for backup $data17.ccards.credits RDF Primary RDF Backup /usr/beta $plat.charges.buys $gold.cards.
C/C++ Program Compilation Deploying a Static SQL Application to an RDF System 5. On the development system \robin, set up a separate directory for building the RDF backup \indus version of the self-contained application executable. Set up DEFINEs, run the preprocessor, the C/C++ compiler, native linker, and SQL/MX compiler. • For TNS/E native applications: cd /usr/backup add_define =debits class=map \ file=\\robin.$data07.ccards.debits add_define =credits class=map \ file=\\robin.$data17.ccards.
C/C++ Program Compilation • Deploying a Static SQL Application to an RDF System For TNS/E native applications: cd /usr/primary add_define =debits class=map \ file=\\robin.$data07.ccards.debits add_define =credits class=map \ file=\\robin.$data17.ccards.credits mxsqlc nonstop1.sql -c nonstop1.cpp -g \ moduleSchema=telco.modules mxsqlc nonstop2.sql -c nonstop2.cpp -g \ moduleSchema=telco.modules c89 -I /usr/tandem/sqlmx/include -c nonstop1.cpp c89 -I /usr/tandem/sqlmx/include -c nonstop2.
C/C++ Program Compilation Building SQL/MX C/C++ Applications to Run in the Guardian Environment 9. Display and verify the compiled plans of the user modules for the RDF backup version: mxci select * from table(explain('comms.mods.nonstop1mod', '%')); select * from table(explain('comms.mods.nonstop2mod', '%')); 10. From the development system \robin, transfer the application executable nonstop.exe to the RDF backup system \indus (for example, into the /usr/beta directory). 11.
C/C++ Program Compilation Building SQL/MX Guardian Applications in the Guardian Environment Building SQL/MX Guardian Applications in the Guardian Environment • • • • • • Using the OSS Pass-Through Command on page 15-59 OSS-to-Guardian File Naming on page 15-59 Steps for Building an SQL/MX C Application in the Guardian Environment on page 15-60 Using a TACL Macro to Build a C Guardian Application on page 15-61 Steps for Building an SQL/MX C++ Application in the Guardian Environment on page 15-62 Using a
C/C++ Program Compilation Building SQL/MX Guardian Applications in the Guardian Environment Steps for Building an SQL/MX C Application in the Guardian Environment Use the next commands at a TACL prompt to preprocess, SQL compile, and compile and link an SQL/MX C program. 1. To make the source file in the Guardian environment accessible to an OSS process, enter this command, replacing myvol.
C/C++ Program Compilation Building SQL/MX Guardian Applications in the Guardian Environment 6. Execute the executable: TACL> run progexe Using a TACL Macro to Build a C Guardian Application Use a TACL macro file to combine and execute the commands. Use these sample TACL macros to customize your own script. In the samples, the source file is located in the Guardian environment and named progsql. Remember that the source file must be Guardian file code 101.
C/C++ Program Compilation Building SQL/MX Guardian Applications in the Guardian Environment == Execute the executable. run progexe Steps for Building an SQL/MX C++ Application in the Guardian Environment Use the next commands at a TACL prompt to preprocess, SQL compile, and compile and link an SQL/MX C++ program. 1. To make the source file in the Guardian environment accessible to an OSS process, enter this command, replacing myvol.
C/C++ Program Compilation Building SQL/MX Guardian Applications in the Guardian Environment == Call the NMCPLUS compiler to generate the object file. TACL> nmcplus/in progsrc,out progout/progo;version2 == Call the nld linker to generate an executable file. TACL> nld $system.system.crtlmain progo -o progexe -obey & $system.system.libcobey -lzclisrl -lzcplsrl & -lztlhsrl -noverbose 6.
C/C++ Program Compilation Building SQL/MX Guardian Applications in the OSS Environment ctoedit progcpp,progsrc == Call the NMCPLUS compiler to generate the object file. nmcplus/in progsrc,out progout/progo;version2 == Call the nld linker to generate an executable file. nld $system.system.crtlmain progo -o progexe -obey & $system.system.libcobey & -lzclisrl -lzcplsrl -lztlhsrl -noverbose == Execute the executable.
C/C++ Program Compilation Running the SQL/MX Program File the SQL statement, along with code to handle parameter passing and error processing. At run time, the calls to the CLI pass in a descriptor of the statement, which gives the statement name, the module name, and a module timestamp. The CLI begins processing each call by checking that it has the associated module in memory. If not, it uses the module name to find the correct module file in the application’s base directory.
C/C++ Program Compilation Understanding and Avoiding Some Common RunTime Errors Module File Errors Error 8809 Unable to open module file This error occurs if module files are deleted from the application’s base directory or from the /usr/tandem/sqlmx/USERMODULES directory and then the associated application is executed. This error can also occur if the named module file exists but is not readable.
C/C++ Program Compilation Debugging a Program Module File Naming In application development, avoid the use of delimited identifiers that contain dots (.) in the name of a module’s catalog and schema and in the module name itself. Delimited identifiers begin and end with double quotation characters (" "). However, quotation characters are removed when NonStop SQL/MX forms the three-part module name.
C/C++ Program Compilation Displaying Query Execution Plans Displaying the Query Execution Plan of One Statement To display the EXPLAIN output for a specific DML statement in a module, issue this statement in MXCI: SELECT * FROM TABLE(EXPLAIN('module-name', 'statement-pattern')); Module Name The module-name is the full name of a module, is case-sensitive, and must be placed within single quotes: 'CAT.SCH.
C/C++ Program Compilation Displaying Query Execution Plans as integer) as stmt_index, substring(description from position( statement: ' in description) + 11 for 9999) as stmt from table(explain('CAT.SCH.MYMOD','%')) where operator = 'ROOT' order by stmt_index; The query displays output similar to: MODULE_NAME STATEMENT_NAME STMT_INDEX STMT --------------------------------------------------------------CAT.SCH.MYMOD SQLMX_DEFAULT_STATEMENT_1 0 PROCEDURE C1 () INSERT INTO T VALUES(1); CAT.SCH.
C/C++ Program Compilation Displaying Query Execution Plans HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 15 -70
16 COBOL Program Compilation This section describes how to develop and execute a COBOL program that contains embedded SQL statements.
COBOL Program Compilation Compiling SQL/MX Applications and Modules Compiling SQL/MX Applications and Modules NonStop SQL/MX Release 2.x provides two methods of compiling embedded SQL COBOL programs and creating modules. Both methods create an identical module file. The first method described, using embedded module definitions, is the default and preferred method.
Compiling Embedded SQL COBOL Programs With Embedded Module Definitions COBOL Program Compilation Compiling Embedded SQL COBOL Programs With Embedded Module Definitions Figure 16-1 shows how a self-contained, single-file COBOL program is compiled using embedded module definitions. The application’s embedded SQL source file is called sqlprog.ecob. Figure 16-1. Compiling Embedded SQL COBOL Programs With Embedded Module Definitions 1 SQL/MX SQL/MX COBOL COBOLSource SourceFile File sqlprog.ecob sqlprog.
COBOL Program Compilation Compiling Embedded SQL COBOL Programs With Embedded Module Definitions automate the process. For information on using ecobol or nmcobol in this way, see ecobol or nmcobol Utility: Using One Command for All Compilation Steps on page 16-32. For more information on using ETK, see ETK online help. These steps correspond to the steps in Figure 16-1 on page 16-3. 1. Create the COBOL source file that contains embedded SQL statements (sqlprog.ecob). 2.
COBOL Program Compilation Compiling Embedded SQL COBOL Programs With Embedded Module Definitions mxCompileUserModule utility against all the object files rather than against the executable file. When you SQL compile against the object files, NonStop SQL/MX does not recompile each module for object files that are linked into more than one executable file. 6.
Compiling Embedded SQL COBOL Programs With Module Definition Files COBOL Program Compilation Compiling Embedded SQL COBOL Programs With Module Definition Files Figure 16-2 shows how a COBOL program with separate module definition files is compiled. The application’s embedded SQL source file is called sqlprog.ecob. Figure 16-2. Compiling Embedded SQL COBOL Programs With Module Definition Files SQL/MX SQL/MX COBOL COBOLSource Source File File sqlprog.ecob sqlprog.
COBOL Program Compilation Compiling Embedded SQL COBOL Programs With Module Definition Files 1. Create the COBOL source files that contain embedded SQL statements (sqlprog.ecob). 2. Preprocess the application’s embedded SQL source files by using the SQL/MX COBOL preprocessor mxsqlco. See Running the SQL/MX COBOL Preprocessor on page 16-9. mxsqlco sqlprog.ecob -c sqlprog.cbl -m sqlprog.
COBOL Program Compilation • Creating Modules: From Development to Production Running ecobol or nmcobol with object files as input to link them: ecobol -o sqlprog.exe -lzclidll sqlprog.o nmcobol -o sqlprog.exe -lzclisrl sqlprog.o • Running the eld or nld utility separately after compilation to resolve external references in ENTER statements and implicit invocations of COBOL run-time library routines that many COBOL statements cause: eld -lzcobdll -lzcredll -lzclidll -o sqlprog.exe sqlprog.
COBOL Program Compilation Running the SQL/MX COBOL Preprocessor You must diagnose host language errors in the source program before you can diagnose SQL errors in the source program. • With module definition files, you compile the source file and the module definition file at the same time. This method provides the opportunity to diagnose both host language errors and SQL errors in the source file concurrently.
COBOL Program Compilation Preprocessor Functions The OSS-hosted SQL/MX COBOL preprocessor resolves the DEFINE =cobdef1 and the file mapped by DEFINE is processed. Similarly, the file mapped by the DEFINE =cobdef2 is processed for section section1. The DEFINE used with a COBOL directive must be MAP DEFINE. The Windows-hosted COBOL preprocessor does not support Guardian DEFINEs. COBOL Comments The preprocessor ignores COBOL comments unless the comment specifies a name for an SQL statement.
COBOL Program Compilation • • • Preprocessor Output Converts the SQL statement to a COBOL comment in the COBOL annotated source file. Writes data structure initialization statements needed for arguments to the CLI procedure calls and writes the appropriate CLI procedure call or calls for the SQL statement immediately after the commented statement in the COBOL annotated source file. At run time, the calls invoke the SQL/MX executor to execute the procedure for the SQL statement within the module.
Preprocessor Output COBOL Program Compilation The COBOL annotated source file consists of: Header Contains the data structures. Body Contains the embedded SQL COBOL source file translated into COBOL statements. The preprocessor converts each embedded SQL statement to a COBOL comment by prefixing an asterisk (*) to the statement and follows the commented statement with a CLI call that invokes the executor at run time to execute the statement.
COBOL Program Compilation OSS-Hosted SQL/MX COBOL Preprocessor The ISO88591 character set is the default character set for CHAR or VARCHAR data types for NonStop SQL/MX. Procedures After writing to the header of the module definition file, the preprocessor writes procedures for executing SQL statements. A procedure consists of a name, a formal argument list, and an SQL statement as the body of the procedure. Each formal argument has a name and an SQL data type.
COBOL Program Compilation OSS-Hosted SQL/MX COBOL Preprocessor Syntax for the OSS-Hosted SQL/MX COBOL Preprocessor mxsqlco [ [ [ [ [ [ [ [ [ [ [ sql-file -c COBOL-output-file ] -m module-def-file ] -e ] -l list-file ] -a ] -f ] -t timestamp ] -q ] -d toggle || SETTOG ] -x ] -g {moduleGroup[=module-group-specification-string] |moduleTableSet[=module-tableset-specificationstring] |moduleVersion[=module-version-specificationstring] |moduleCatalog[=module-catalog-name] |moduleSchema[=module-schema-name] }] [
COBOL Program Compilation OSS-Hosted SQL/MX COBOL Preprocessor -l list-file is the name of the output list file that contains preprocessor error and warning messages. The default is source-file.lst, where source-file is the name of the SQL/MX COBOL source file (for example, sqlprog.ecob) without the file extension. -a indicates the ANSI fixed format for the source file. Output source is in the same format. If not specified, -f is the default. -f indicates the TANDEM free format for the source program.
COBOL Program Compilation OSS-Hosted SQL/MX COBOL Preprocessor -g {moduleGroup[=module-group-specification-string] |moduleTableSet[=module-tableset-specification-string] |moduleVersion[=module-version-specification-string] |moduleCatalog[=module-catalog-name] |moduleSchema[=module-schema-name] } specifies the arguments for qualifying the name given to the compiled module file. If you use this option, you must supply at least one of the five module management attributes.
COBOL Program Compilation OSS-Hosted SQL/MX COBOL Preprocessor MODULE directive using the default catalog naming rules described in the SQL/MX Reference Manual. The maximum size for the moduleCatalog attribute is 128 characters. moduleSchema sets the moduleSchema attribute if the input sql-file does not have a MODULE directive or its MODULE directive does not specify a schema name. The moduleSchema can contain a catalog name.
COBOL Program Compilation Windows-Hosted SQL/MX COBOL Preprocessor Windows-Hosted SQL/MX COBOL Preprocessor The Windows-hosted SQL/MX COBOL preprocessor is a DLL file named mxsqlcont.dll and is accompanied by a DLL loader named mxsqlco.exe. These files are installed in the C:\Program Files\HP SQL-MX COBOL Preprocessors directory. Use either the command shell or the Korn shell to run the preprocessor with the RUN command. You can install multiple versions of the SQL/MX COBOL preprocessors.
COBOL Program Compilation Windows-Hosted SQL/MX COBOL Preprocessor The environment variable MXSQLCO must be set before starting ETK. ° Enterprise Plugins for Eclipse (EPE)—plug-in for Eclipse When Eclipse is used, MXSQLCO is set by EPE based on the value of the preprocessor installation location.
COBOL Program Compilation Windows-Hosted SQL/MX COBOL Preprocessor -m module-def-file is the name of the output module definition file, which is the input file for the SQL/MX compiler. The default is source-file.m, where source-file is the name of the SQL/MX COBOL source file (for example, sqlprog.ecob) without the file extension. -e generates CHARACTER data types for date-time data types. This behavior is compatible with NonStop SQL/MX Release 1.8.
COBOL Program Compilation Windows-Hosted SQL/MX COBOL Preprocessor -d toggle || SETTOG defines toggles for use with conditional compilation. Toggles must be in the range of 1 through 15. If you specify SETTOG, all toggles are set to ON. -s system-name or IP-address is the node name or IP address of the NonStop system where the tables are found by INVOKE. This option is required if you use INVOKE. -r ODBC-listener is the NonStop system port to connect to for the ODBC listener process.
COBOL Program Compilation Windows-Hosted SQL/MX COBOL Preprocessor moduleGroup sets the moduleGroup attribute to group an application’s module files logically by sharing the same name prefix. The moduleGroup becomes embedded in the module file names as a common prefix and enables the use of OSS wildcard file specification patterns to manage the files. For more information, see Grouping on page 17-21. The maximum size for the moduleGroup attribute is 31 characters.
COBOL Program Compilation Running the COBOL Compiler and Linker invokeCatalog sets the catalog for unqualified objects inside the invoke clause as catalogname. If a catalog is specified using the Control Query Default Catalog or Declare Catalog, this attribute has no effect. The maximum size of the invokeCatalog attribute is 128 characters. invokeSchema sets the schema for unqualified objects inside the invoke clause as schemaname.
Running the COBOL Compiler and Linker COBOL Program Compilation Table 16-1.
COBOL Program Compilation Running the SQL/MX Compiler Using One Command for All Compilation Steps on page 16-32, the OSS reference pages, or the Open System Services Shell and Utilities Reference Manual.
COBOL Program Compilation Compiling Embedded Module Definitions Compiling a Module Definition File on page 16-29 and Combining Embedded Module Definitions and Module Definition Files on page 16-43. Command Line Syntax To invoke mxCompileUserModule, at an OSS prompt, enter: mxCompileUserModule [ -e] [ -v] [-g {moduleGlobal | moduleLocal}] [-d compiler-attribute-name=compiler-attribute-value] ... application-file ["{"module-name[, module-name]..."}"] module-name is: [[catalog.]schema.
COBOL Program Compilation Compiling Embedded Module Definitions module with its application. For more information, see Generating Locally or Globally Placed Modules on page 17-3. -d compiler-attribute-name=compiler-attribute-value specifies default attribute settings for compilation and existing settings for the module name.
COBOL Program Compilation • • Compiling Embedded Module Definitions Must not specify a Guardian subvolume (/G/...) or a remote directory in an Expand network (/E/...). Must not exceed 1024 characters. module-name is the fully qualified name of an embedded module definition. This option names the generated module that is written to the user-specified local application directory or to the global /usr/tandem/sqlmx/USERMODULES directory. For more information, see Module Management Naming on page 17-8.
COBOL Program Compilation MXCMP Environment Variable Examples—mxCompileUserModule • This command compiles the embedded module definition: mxCompileUserModule sqlprog.exe • This command places the module file in the same OSS directory as the application executable: mxCompileUserModule -g moduleLocal sqlprog.o • These settings affect statement recompilation at execution time: mxCompileUserModule -d AUTOMATIC_RECOMPILATION=ON \ -d SIMILARITY_CHECK=ON sqlprog.
COBOL Program Compilation Compiling a Module Definition File generate module definition files as done in SQL/MX Release 1.8 and previous releases. To compile a module definition file, use the SQL/MX compiler mxcmp command on the module definition (.m) file. The SQL/MX compiler places a compiled user module file in the global /usr/tandem/sqlmx/USERMODULES directory or in the user-specified local application directory.
COBOL Program Compilation Compiling a Module Definition File If you do not specify -g moduleLocal=OSSdir but set MXCMP_PLACES_LOCAL_MODULES ON, you must be in the same directory as the application executable when you invoke mxcmp. Otherwise, mxcmp writes the module in the current directory, and you will need to move the module to the global USERMODULES directory or co-locate the module with its application. For more information, see Generating Locally or Globally Placed Modules on page 17-3.
COBOL Program Compilation ecobol or nmcobol Utility: Using One Command for All Compilation Steps ecobol or nmcobol Utility: Using One Command for All Compilation Steps In the OSS environment, the ecobol or nmcobol utility provides the interface to COBOL compilation components, including the SQL/MX COBOL preprocessor, the native COBOL compiler, and the native object file linker (eld or nld). ecobol enables you to perform TNS/E native compilation and build an embedded SQL/MX program in a single command.
COBOL Program Compilation ecobol or nmcobol Utility: Using One Command for All Compilation Steps ecobol and nmcobol are installed in the /usr/bin directory. ecobol and nmcobol Utility Options for SQL/MX -Wsqlmx[={"args" | args}] Invokes the SQL/MX preprocessor prior to invoking the COBOL compiler. Cannot be specified with -Wsql or -Wsqlcomp.
COBOL Program Compilation ecobol or nmcobol Utility: Using One Command for All Compilation Steps -Wsqlmxadd=["args" | arg] Passes valid preprocessor commands (args) through to the SQL/MX preprocessor without change or validation. The preprocessor validates the syntax. -Wsettog=n Specifies a numeric toggle from 1 through 15 that is defined only during preprocessing. Ignored without warning if -Wsqlmx is not specified. Can be set more than once to set multiple toggles as either: • • -Wsettog=n,nn...
COBOL Program Compilation ecobol or nmcobol Utility: Using One Command for All Compilation Steps warn Directs the SQL/MX compiler to return a warning rather than an error if a table does not exist at compile time. verbose Directs the SQL/MX compiler to display summary information, in addition to error and warning messages. -Wmxcmp_querydefault= compiler-attribute-name= compiler-attribute-value[, compiler-attribute-value...
COBOL Program Compilation ecobol or nmcobol Utility: Using One Command for All Compilation Steps -WmoduleGroup[=[string]] Directs the SQL/MX preprocessor to group all of an application’s module files. The moduleGroup is embedded in the module files’ names and enables the use of OSS wild-card file specification patterns to manage the files. For more information, see Grouping on page 17-21. -WmoduleTableSet[=[string]] Directs the SQL/MX preprocessor to use the module management targeting feature.
COBOL Program Compilation ecobol or nmcobol Utility: Using One Command for All Compilation Steps In addition to the options for preprocessing and compiling SQL/MX components, ecobol and nmcobol supply SQL/MX environment variables that provide the path names for the SQL/MX preprocessor (MXSQLCO) and the SQL/MX compiler (MXCMP and MXCMPUM), in addition to the path name for definitions of the SQL call-level interface (SQLCLIO).
COBOL Program Compilation ecobol and nmcobol Examples With Embedded Module Definitions ecobol and nmcobol Examples With Embedded Module Definitions Figure 16-3 shows how the ecobol or nmcobol utility compiles a COBOL program with embedded module definitions. Figure 16-3.
COBOL Program Compilation • ecobol and nmcobol Examples With Embedded Module Definitions These commands preprocess, compile, link, and SQL compile a single COBOL source file: ecobol -Wsqlmx -Wmxcmp -o sqlprog.exe sqlprog.ecob nmcobol -Wsqlmx -Wmxcmp -o sqlprog.exe sqlprog.ecob The ecobol or nmcobol utility invokes the preprocessor, mxsqlco, which uses the file sqlprog.ecob as input and produces one file: sqlprog.cbl, which is a COBOL annotated source file that contains embedded module definitions.
COBOL Program Compilation ecobol and nmcobol Examples With Embedded Module Definitions required CLI procedure definitions to create an executable file named sqlprog.exe: eld -lzcobdll -lzcredll -lzclidll sqlprog1.o sqlprog2.o \ -o sqlprog.exe nld -lzcobsrl -lzcresrl -lzclisrl sqlprog1.o sqlprog2.o \ -o sqlprog.exe For more information on eld, see the eld Manual. For more information on nld, see the nld Manual.
ecobol and nmcobol Examples With Module Definition Files COBOL Program Compilation ecobol and nmcobol Examples With Module Definition Files Figure 16-4 shows how the ecobol or nmcobol utility compiles a COBOL program with module definition files. Figure 16-4.
COBOL Program Compilation • ecobol and nmcobol Examples With Module Definition Files These commands preprocess, compile, link, and SQL compile a single COBOL source file: ecobol -Wsqlmx -Wsqlmxadd=-x -Wmxcmp -o sqlprog.exe \ sqlprog.ecob sqlprog.m nmcobol -Wsqlmx -Wsqlmxadd=-x -Wmxcmp -o sqlprog.exe \ sqlprog.ecob sqlprog.m The ecobol or nmcobol utility invokes the preprocessor, mxsqlco, which uses the file sqlprog.ecob as input and produces two files: sqlprog.cbl and sqlprog.m. The file sqlprog.
COBOL Program Compilation Combining Embedded Module Definitions and Module Definition Files required CLI procedure definitions to create an executable file named sqlprog.exe: eld -lzcobdll -lzcredll -lzclidll sqlprog1.o sqlprog2.o \ -o sqlprog.exe nld -lzcobsrl -lzcresrl -lzclisrl sqlprog1.o sqlprog2.o \ -o sqlprog.exe For more information on eld, see the eld Manual. For more information on nld, see the nld Manual.
COBOL Program Compilation Building SQL/MX COBOL Applications to Run in the Guardian Environment RECOMPILATION_WARNINGS=ON,SIMILARITY_CHECK=ON" \ sqlutil.o sqlapp.ecbl -o sqlapp.exe nmcobol -Wsqlmx -WmoduleSchema=cobcat.cobsch -Wmxcmp \ -Wmxcmp_querydefault="AUTOMATIC_RECOMPILATION=ON,\ RECOMPILATION_WARNINGS=ON,SIMILARITY_CHECK=ON" \ sqlutil.o sqlapp.ecbl -o sqlapp.exe The ecobol or nmcobol command: • • • • Preprocesses the COBOL source file, sqlapp.ecbl, into an annotated source file, sqlapp.
COBOL Program Compilation Building SQL/MX Guardian Applications in the Guardian Environment resides in the Guardian environment, it runs as an OSS process and must be started in the OSS environment. To run the SQL/MX preprocessor and the SQL/MX compiler in the Guardian environment, use the OSS pass-through command by specifying the osh -c option. The osh -c option executes one command line at a time in the OSS environment.
COBOL Program Compilation Building SQL/MX Guardian Applications in the Guardian Environment 3. To invoke the SQL/MX compiler, which is an OSS process, enter an OSS passthrough command at a TACL prompt: TACL> osh -c "/G/system/system/mxcmp progm |tee templog" 4. Errors generated by the SQL/MX preprocessor or SQL/MX compiler are logged in the OSS file templog. To convert the error log to a Guardian file: TACL> purge proglog TACL> ctoedit templog,proglog 5. Run the Guardian COBOL compiler and linker.
COBOL Program Compilation Building SQL/MX Guardian Applications in the OSS Environment osh -c "mxsqlco progecob -c progcbl -m progm & ~|tee templog" == Call the SQL/MX compiler. osh -c "/G/system/system/mxcmp progm ~|tee -a templog" == Convert OSS text files (file code 180) to Guardian text files == (file code 101). sink [#purge proglog] ctoedit templog,proglog ctoedit progcbl,progsrc == Call the ECOBOL compiler to generate the object file. ecobol /in progsrc,out progout/progo; & consult $system.system.
COBOL Program Compilation Running an SQL/MX Application 2. Compile the COBOL source file by using the -Wsystype=guardian option of the OSS compiler utility: ecobol -Wsqlmx -Wmxcmp -Wsystype=guardian prog.ecob \ -o prog.exe nmcobol -Wsqlmx -Wmxcmp -Wsystype=guardian prog.ecob \ -o prog.exe 3. Copy the executable file, prog.exe, from an OSS directory to a Guardian volume and subvolume: cp prog.exe /G/myvol/mysubvol/progexe 4.
COBOL Program Compilation Running the SQL/MX Program File Security of the /usr/tandem/sqlmx/USERMODULES directory is very important. You should restrict access so that users cannot alter the query plans in the modules or remove modules. For information on securing modules, see the SQL/MX Installation and Management Guide. Running the SQL/MX Program File An SQL/MX program can run in the OSS or in the Guardian environment. You can use the GTACL command to start a Guardian program from OSS.
COBOL Program Compilation Understanding and Avoiding Common Run-Time Errors generates. If the SQL/MX compiler has previously compiled a different instance of the module definition file, a module exists whose name corresponds to the application module but has a mismatched timestamp. This error can also occur if you make a copy of an application executable, rebuild the application (thus overwriting the original instance of the application’s module file), and then execute the first copy of the application.
COBOL Program Compilation Displaying Query Execution Plans Displaying Query Execution Plans The EXPLAIN function is an SQL/MX extension that generates a result table describing an access plan for a DML statement, otherwise known as a query execution plan. Use the EXPLAIN function for a DML statement in a module. For more information, see Displaying Query Execution Plans on page 15-67.
COBOL Program Compilation Displaying Query Execution Plans HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 16 -52
17 Program and Module Management Developing SQL/MX applications requires both a flexible and controlled development environment. You need to be able to move program and module files from development to a test or production environment. You also need to have control over the development environment to effectively manage module files and modifications made to those files.
Program Files Program and Module Management Table 17-1. File Naming Conventions (page 2 of 2) File Naming Convention C++ preprocessed file source-file.C source-file.cc source-file.cpp source-file.cxx source-file.c++ COBOL preprocessed file source-file.cbl Executable program file source-file.exe Module definition file source-file.m SQL/MX module Use the MODULE directive to name a module.
Program and Module Management Managing Program Files Managing Program Files You probably develop, test, and debug applications on a development or test system and then move the applications to a production system for actual use. On the development system, you would typically test and tune applications by using a database modeled after the database on the production system.
Program and Module Management • Managing the Coexistence of Globally and Locally Placed Modules mxCompileUserModule -g moduleGlobal | -g moduleLocal • mxcmp -g moduleGlobal | -g moduleLocal=OSSdir For additional details about setting the SQL/MX compiler options, see Compiling Embedded Module Definitions on page 15-30 and 16-25 and Compiling a Module Definition File on page 15-34 and 16-29.
Program and Module Management Managing the Coexistence of Globally and Locally Placed Modules module in the current directory, and you will need to manually move the module to colocate it with its application. To generate globally placed modules in the /usr/tandem/sqlmx/USERMODULES directory on a case-by-case basis, use the -g moduleGlobal option, as shown next: mxCompileUserModule -g moduleGlobal app.exe For more information, see Compiling Embedded Module Definitions on page 15-30 and 16-25.
Program and Module Management Managing Modules The directory path in mxCompileUserModule -g moduleLocal dir/app.exe states both where the application is located and where to co-locate the module with the application. You can invoke mxcmp or mxCompileUserModule with the -g moduleLocal option from any OSS directory and automatically co-locate the module with the application. • Set the MXCMP_PLACES_LOCAL_MODULES attribute only in the SYSTEM_DEFAULTS table.
Program and Module Management • • • Module Management Behavior Securing the modules Checking module dependencies by using the DISPLAY USE OF command Removing modules For more information, see the SQL/MX Installation and Management Guide. Module Management Behavior By default, in SQL/MX Release 2.x, the SQL preprocessor generates a self-contained application executable file that contains embedded module definitions.
Module Management Naming Program and Module Management Table 17-2. Preprocessor Interpretation of SQLMX_PREPROCESSOR_VERSION Environment Variable and -m and -x Options SQLMX_PREPROCESSOR_ VERSION environment variable -m ? -x ? Module Definition File? Embedded Module Definition? * Is >=1200 or is not set at all No No No Yes Is >=1200 or is not set at all No Yes Yes in base.
Program and Module Management How Modules Are Named How Modules Are Named The SQL preprocessor generates a three-part module name based on the MODULE directive that you specify in the C, C++, or COBOL source file. For more information, see Section 15, C/C++ Program Compilation and Section 16, COBOL Program Compilation. The three parts of the module name are the catalog, schema, and module names, separated by periods. For example: CAT.SCH.
Program and Module Management How Modules Are Named schema and the target (or table set), version, and group attributes to the processed, three-part module name to create an externally qualified module name. The externally qualified module name consists of the three-part module name plus the module management attributes delimited by circumflex (^) characters: CAT.SCH.GRP^MOD^TABLESET^VER The preprocessor embeds the externally qualified module name in the module definition.
How Modules Are Named Program and Module Management \ Figure 17-1. Module Name Length 1023-character maximum length C/C++: 248-char. max. length COBOL: 160-char max. length 128-character maximum length /usr/tandem/sqlmx/USERMODULES/CatalogName.SchemaName.Group^ModuleName^Target^Version 128-character maximum length 128-character maximum length 31-character maximum length 128-character maximum length 31-character maximum length 31-character maximum length VST143.
Effect of Module Management Naming Program and Module Management Effect of Module Management Naming Module management features qualify the name for the module that is encoded in the module definition file and the C-annotated source file. By changing the name of the module in the module definition file, the name of the module-file is also changed. Table 17-3 lists the effects of combinations of group (MGSS), target or table set (MTSS), and version (MVSS) attributes on the module file name. Table 17-3.
Program and Module Management Effect of the Target Attribute Effect of the Target Attribute The SQL preprocessor checks for the presence of a Module TableSet Specification String (MTSS), which is a regular or delimited identifier that you specify with preprocessor options. For information on how to specify an MTSS, see Running the SQL/MX C/C++ Preprocessor on page 15-8 and Running the SQL/MX COBOL Preprocessor on page 16-9. For information on identifiers, see the SQL/MX Reference Manual.
Program and Module Management Targeting Example for C: Using ModuleTableSet (MTSS) If you want to rebuild the application to target a set of production files, you can do so without changing the source file (empcnt.sql): 1. Use the OSS add_define command to give =DEPT and =EMPLOYEE the table location to target a different set of files: add_define =DEPT class=MAP file=\\PROD.\$DATA.HR1.DEPT add_define =EMPLOYEE class=MAP \ file=\\PROD.\$DATA.HR1.EMPLOYEE 2.
Program and Module Management Targeting Example for C: Using Build Subdirectory that the compiled SQL plans are read from empcnt_test.exe module /usr/tandem/sqlmx/USERMODULES/CAT.SCH.^EMP_CNT_MODULE^TEST^. These plans refer to the test environment tables, \TEST.$DATA.HR1.DEPT and \TEST.$DATA.HR1.EMPLOYEE. However, the SQL/MX executor performs late name resolution (see Late Name Resolution on page 8-6).
Program and Module Management Targeting Example for COBOL: Using ModuleTableSet (MTSS) The shell script makes a subdirectory that is named from the environment variable $TableSet (which was set to TEST in the previous example). The script is written so that the intermediate C and module definition files are written into that subdirectory by the preprocessor and read from that subdirectory by the C compiler and mxcmp, respectively. (See references to $TableSet on the mxsqlc, c89, and mxcmp command lines.
Program and Module Management Targeting Example for COBOL: Using ModuleTableSet (MTSS) 1. Use the OSS add_define command to give =DEPT and =EMPLOYEE the desired values: add_define =DEPT class=MAP file=\\TEST.\$DATA.HR1.DEPT add_define =EMPLOYEE class=MAP file=\\TEST.\$DATA.HR1.EMPLOYEE 2. Invoke the preprocessor, specifying a module-tableset-specificationstring with the -g option: mxsqlco empcnt.ecbl -c empcnt.cbl -m empcnt.m \ -g moduleTableSet=TEST 3.
Program and Module Management Targeting Example for COBOL: Using a Build Subdirectory Because a module-tableset-specification-string is specified, the module file produced by the SQL/MX compiler is: /usr/tandem/sqlmx/USERMODULES/CAT.SCH.^EMP_CNT_MODULE^PROD^ After building the application for both sets of tables, the user can execute either compiled executable and its corresponding compiled SQL. When the empcnt_test.
Program and Module Management Versioning -g moduleTableSet=$TableSet nmcobol -o ./$TableSet/empcnt.exe \ -Wcobol=”CONSULT /usr/tandem/sqlmx/lib/sqlcli.o” \ -lzclisrl ./$TableSet/empcnt.cbl /G/system/system/mxcmp ./$TableSet/empcnt.m The shell script makes a subdirectory that is named from the environment variable $TableSet (which was set to TEST in the previous example).
Program and Module Management Versioning Versioning Guidelines You specify versioning by using the C/C++ or COBOL preprocessor option -g and setting the moduleVersion attribute to a Module-Version-SpecificationString (MVSS). This MVSS is embedded in the module name, which is used to name the module file. As with the targeting feature, you need to take steps to prevent the second version of the executable file from overwriting the first.
Program and Module Management Grouping If the environmental variable is set, once for V1 and once for V2, after the entire build script finishes twice, an executable exists in two subdirectories V1 and V2. These two module files will coexist: /usr/tandem/sqlmx/USERMODULES/CAT.SCH.^EMP_CNT_MODULE^^V1 /usr/tandem/sqlmx/USERMODULES/CAT.SCH.
Program and Module Management Grouping reports.o \ utils.o \ -l zcplsrl \ -l zcrtlsrl \ -l zcresrl \ -l zcplosrl \ -l ztlhgsrl \ -l ztlhosrl \ -Bdynamic \ -l zclisrl \ -o invrep /G/system/system/mxcmp reports.m /G/system/system/mxcmp utils.m After the application is built, these two module files exist: /usr/tandem/sqlmx/USERMODULES/CAT.SCH.INVENTORY^REPORTS^^ /usr/tandem/sqlmx/USERMODULES/CAT.SCH.
A C Sample Programs This appendix presents the steps shown in figures in previous sections as complete C programs. Using a Static SQL Cursor Example A-1 executes the steps shown in Figure 6-1 on page 6-2. C Example A-1.
Using a Static SQL Cursor C Sample Programs C Example A-1. Using a Static SQL Cursor (page 2 of 3) SQLSTATE[5]='\0'; SQLSTATE_OK[5]='\0'; SQLSTATE_NODATA[5]='\0'; printf("\n\nThis example uses a static cursor. \n\n"); EXEC SQL WHENEVER SQLERROR GOTO end_prog; EXEC SQL DECLARE CATALOG 'samdbcat'; EXEC SQL DECLARE SCHEMA 'sales'; /* Declare the static cursor.
C Sample Programs C Using a Static SQL Cursor Example A-1. Using a Static SQL Cursor (page 3 of 3) end_prog: EXEC SQL WHENEVER SQLERROR CONTINUE; if (strcmp(SQLSTATE, SQLSTATE_OK) == 0) printf("\nThe program completed successfully.
C Sample Programs Ensuring Data Consistency Ensuring Data Consistency Example A-2 executes the steps shown in Figure 14-1 on page 14-1. Example A-2. Using TMF to Ensure Data Consistency /* --------------------------------------------------------Description: Using TMF to ensure data consistency Statements: SET TRANSACTION BEGIN WORK Searched UPDATE COMMIT WORK ROLLBACK WORK WHENEVER ------------------------------------------------------------ */ #include #include
Using Argument Lists in Dynamic SQL C Sample Programs Using Argument Lists in Dynamic SQL Example A-3 executes the steps shown in Figure 9-1 on page 9-3. Example A-3. Using Argument Lists in Dynamic SQL (page 1 of 3) /* --------------------------------------------------------Description: Using Argument Lists Statements: PREPARE EXECUTE USING ARGUMENTS DEALLOCATE PREPARE WHENEVER GET DIAGNOSTICS ------------------------------------------------------------ */ #include #include
C Sample Programs Using Argument Lists in Dynamic SQL Example A-3. Using Argument Lists in Dynamic SQL (page 2 of 3) /* Move statement with input variable to statement variable. */ strcpy(hv_sql_stmt, "SELECT empnum, first_name," " last_name, salary" " FROM samdbcat.persnl.employee" " WHERE empnum = CAST(? AS NUMERIC(4) UNSIGNED)"); /* Prepare the statement. */ EXEC SQL PREPARE sqlstmt FROM :hv_sql_stmt; /* Initialize the input parameter in the WHERE clause.
C Sample Programs Using SQL Descriptor Areas in Dynamic SQL Example A-3.
Using SQL Descriptor Areas With DESCRIBE C Sample Programs Example A-4. Using SQL Descriptor Areas With DESCRIBE (page 1 of 4) /* --------------------------------------------------------Description: Using Descriptor Areas With DESCRIBE Statements: ALLOCATE DESCRIPTOR SELECT PREPARE DESCRIBE SET DESCRIPTOR EXECUTE GET DESCRIPTOR DEALLOCATE PREPARE DEALLOCATE DESCRIPTOR WHENEVER GET DIAGNOSTICS ------------------------------------------------------------ */ #include #include
C Sample Programs Using SQL Descriptor Areas With DESCRIBE Example A-4. Using SQL Descriptor Areas With DESCRIBE (page 2 of 4) SQLSTATE[5]='\0'; SQLSTATE_OK[5]='\0'; printf("\n\nThis example uses SQL descriptor areas. \n\n"); EXEC SQL WHENEVER SQLERROR GOTO end_prog; /* Initialize the output variables in the SELECT list. */ printf("Enter columns to be retrieved, separate by commas: \n"); gets(in_columns); /* Concatenate statement with input and output variables.
C Sample Programs Using SQL Descriptor Areas With DESCRIBE Example A-4. Using SQL Descriptor Areas With DESCRIBE (page 3 of 4) /* Get the count of the number of output values. */ EXEC SQL GET DESCRIPTOR 'out_sqlda' :hv_num = COUNT; /* Get the ith output value in NAME field and save */ /* in the correct host variable by testing on NAME.
C Sample Programs Using SQL Descriptor Areas With DESCRIBE Example A-4. Using SQL Descriptor Areas With DESCRIBE (page 4 of 4) else if (strncmp(sqlda_name,"SALARY",strlen("SALARY"))==0) { EXEC SQL GET DESCRIPTOR 'out_sqlda' VALUE :i :hv_salary = VARIABLE_DATA, :hv_salary_i = INDICATOR_DATA; if (hv_salary_i < 0) printf("\nSalary is unknown"); else printf("\nSalary is: %.2f", hv_salary/100.
C Sample Programs Using SQL Descriptor Areas Without DESCRIBE Using SQL Descriptor Areas Without DESCRIBE Example A-5 executes the steps shown in Figure 10-1 on page 10-12 but without the DESCRIBE statements and without the GET DESCRIPTOR statement (there are no output parameters). Instead of using DESCRIBE, the values of fields in the descriptor area are set explicitly by using the SET DESCRIPTOR statement. Example A-5.
C Sample Programs Using SQL Descriptor Areas Without DESCRIBE Example A-5. Using SQL Descriptor Areas Without DESCRIBE (page 2 of 3) /* Copy statement with input variables. */ strcpy(hv_sql_stmt, "UPDATE samdbcat.persnl.employee" " SET salary = salary * 1.1" " WHERE jobcode = CAST(? AS NUMERIC(4) UNSIGNED)" " AND last_name = ?"); /* Allocate the descriptor area for input parameters. */ desc_max=2; EXEC SQL ALLOCATE DESCRIPTOR 'in_sqlda' WITH MAX :desc_max; /* Prepare the statement.
C Sample Programs Using SQL Descriptor Areas Without DESCRIBE Example A-5. Using SQL Descriptor Areas Without DESCRIBE (page 3 of 3) if (strcmp(SQLSTATE, SQLSTATE_NODATA) == 0) printf("\nNo rows with Jobcode %d and Last in_jobcode, in_last_name); else if (strcmp(SQLSTATE, SQLSTATE_OK) == 0) printf("\nThe update is committed.\n"); EXEC SQL COMMIT WORK; /* Commit the } else { printf("\nThe update is rolled back.
Using a Dynamic SQL Cursor C Sample Programs Using a Dynamic SQL Cursor Using a Dynamic SQL Cursor Example A-6 executes the steps shown in Figure 11-1 on page 11-2 and uses host variable argument lists for the FETCH INTO statement. Example A-6.
C Sample Programs Using a Dynamic SQL Cursor Example A-6. Using a Dynamic SQL Cursor (page 2 of 3) SQLSTATE[5]='\0'; SQLSTATE_OK[5]='\0'; SQLSTATE_NODATA[5]='\0'; printf("\n\nThis example uses a dynamic cursor. \n\n"); EXEC SQL WHENEVER SQLERROR GOTO end_prog; strcpy(curspec,"SELECT partnum, partdesc, price, qty_available" " FROM samdbcat.sales.parts " " WHERE qty_available <= CAST(? AS NUMERIC(5))"); /* Prepare the cursor specification.
C Sample Programs Using a Dynamic SQL Cursor With Descriptor Area Example A-6. Using a Dynamic SQL Cursor (page 3 of 3) if (strcmp(SQLSTATE, SQLSTATE_OK) == 0) printf("\nThe program completed successfully.
C Sample Programs Using a Dynamic SQL Cursor With Descriptor Area Example A-7. Using a Dynamic SQL Cursor With Descriptor Areas (page 1 of 8) /*Description: Statements: Using a Dynamic Cursor With Desc Areas ALLOCATE DESCRIPTOR PREPARE DESCRIBE OUTPUT Dynamic DECLARE CURSOR OPEN FETCH USING DESCRIPTOR GET DESCRIPTOR CLOSE DEALLOCATE PREPARE DEALLOCATE DESCRIPTOR WHENEVER GET DIAGNOSTICS ------------------------------------------------------------ */ #include #include #include
C Sample Programs Using a Dynamic SQL Cursor With Descriptor Area Example A-7. Using a Dynamic SQL Cursor With Descriptor Areas (page 2 of 8) void run_dynTest(char *chstr) { char SQLSTATE_OK[6]="00000"; char SQLSTATE_NODATA[6]="02000"; EXEC SQL BEGIN DECLARE SECTION; VARCHAR in_curspec[256]; long desc_max; EXEC SQL END DECLARE SECTION; exec sql declare schema 'SQL12.mysch'; exec sql set schema 'SQL12.mysch'; printf("\n\nThis example uses a dynamic cursor with desc area.
C Sample Programs Using a Dynamic SQL Cursor With Descriptor Area Example A-7. Using a Dynamic SQL Cursor With Descriptor Areas (page 3 of 8) while (!strcmp (SQLSTATE, "00000") && strcmp(SQLSTATE,"02000")) { /* Process values in the fetched row. */ assign_to_hv(); /* Fetch the next row of the result table. */ EXEC SQL FETCH get_row INTO SQL DESCRIPTOR 'out_sqlda'; } /* Close the cursor. */ EXEC SQL CLOSE get_row; /* Deallocate the prepared statement and the SQLDAs.
C Sample Programs Using a Dynamic SQL Cursor With Descriptor Area Example A-7.
C Sample Programs Using a Dynamic SQL Cursor With Descriptor Area Example A-7. Using a Dynamic SQL Cursor With Descriptor Areas (page 5 of 8) printf (" Value: %f\n", hv_double); } else printf (" Value: %.
C Sample Programs Using a Dynamic SQL Cursor With Descriptor Area Example A-7.
C Sample Programs Using a Dynamic SQL Cursor With Descriptor Area Example A-7.
C Sample Programs Using a Dynamic SQL Cursor With Descriptor Area Example A-7.
C Sample Programs Using a Dynamic SQL Rowset Using a Dynamic SQL Rowset Example A-8 shows a dynamic embedded SQL program that uses descriptor areas. Example A-8.
C Sample Programs Using a Dynamic SQL Rowset Example A-8.
C Sample Programs Using SQL Descriptors to Select KANJI and KSC5601 Data Using SQL Descriptors to Select KANJI and KSC5601 Data DDL for KANJI and KSC4501 Table Columns Example A-9 creates SQL/MP tables that contain KANJI and KSC4501 columns and inserts data into those columns. Example A-9.
C Sample Programs Using SQL Descriptor Areas to Select SQL/MP KANJI and KSC5601 Data Example A-9.
C Sample Programs Using SQL Descriptor Areas to Select SQL/MP KANJI and KSC5601 Data Example A-10.
Using SQL Descriptor Areas to Select SQL/MP KANJI and KSC5601 Data C Sample Programs Example A-10.
Using SQL Descriptor Areas to Select SQL/MP KANJI and KSC5601 Data C Sample Programs Example A-10.
C Sample Programs Using SQL Descriptor Areas to Select SQL/MP KANJI and KSC5601 Data Example A-10.
C Sample Programs Using SQL Descriptor Areas to Select SQL/MP KANJI and KSC5601 Data Example A-10. Using SQL Descriptor Areas to Select SQL/MP KANJI and KSC5601 Data (page 5 of 5) { /* input in KANJI and output in KSC5601 .*/ strcpy(hv_sql_stmt, "select wordInKsc5601 FROM words WHERE \ wordInKanji = ? ;"); execute_one_statement(); /* input in KSC5601 and output in KANJI .
C Sample Programs Using SQL Descriptors to Select UCS2 Data Using SQL Descriptors to Select UCS2 Data Example A-11 selects UCS2 data from an SQL/MX table by using SQL descriptor areas. Example A-11.
Using SQL Descriptors to Select UCS2 Data C Sample Programs Example A-11.
C Sample Programs Using SQL Descriptors to Select UCS2 Data Example A-11.
C Sample Programs Using SQL Descriptors to Select UCS2 Data Example A-11. Using SQL Descriptors to Select UCS2 Data (page 4 of 5) if (j > 0) { // Get the type, character set name and length of the // input EXEC SQL GET DESCRIPTOR :in_sqlda VALUE :j :data_type = TYPE, :data_len = LENGTH, :charset_name = CHARACTER_SET_NAME; // // // if Set up the input value based on character set name. Use the relaxation feature to compare an UCS2 string with an ISO88591 or UCS2 column.
Using SQL Descriptors to Select UCS2 Data C Sample Programs Example A-11. Using SQL Descriptors to Select UCS2 Data (page 5 of 5) // Get the ith output value for (i=1; i<=degree; i++ ) { // Get the info about the output value. Assume it is CHARACTER data. EXEC SQL GET DESCRIPTOR :out_sqlda VALUE :i :data_type = TYPE, :charset_name = CHARACTER_SET_NAME; // Get and print out the output values. Use the relaxation feature // again to reuse the output host variable ":hv_output_in_UCS2" for // ISO88591 columns.
C Sample Programs Using SQL Descriptors to Select UCS2 Data HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 A -40
B C++ Sample Program This appendix presents the steps shown in Section 14, Transaction Management, as a C++ program. Ensuring Data Consistency Example B-1 executes the steps shown in Figure 14-1 on page 14-1. Example B-1.
Ensuring Data Consistency C++ Sample Program Example B-1. Using TMF to Ensure Data Consistency (page 2 of 2) // Member function to prompt for the job description void getdesc(){ cout << "Enter job description: " ; cin >> memhv_jobdesc; } // Member function to put the host variables into the table. // The host variables are referenced in member functions // defined within the same class.
C COBOL Sample Programs This appendix presents the steps shown in figures in previous sections as complete COBOL programs. Using a Static SQL Cursor Example C-1 executes the steps shown in Figure 6-2 on page 6-3. COBOL Example C-1.
COBOL Sample Programs COBOL Using a Static SQL Cursor Example C-1. Using a Static SQL Cursor (page 2 of 3) PROCEDURE DIVISION. START-LABEL. DISPLAY "This example uses a static cursor.". EXEC SQL WHENEVER SQLERROR GOTO sqlerrors END-EXEC. EXEC SQL DECLARE CATALOG 'samdbcat' END-EXEC. EXEC SQL DECLARE SCHEMA 'sales' END-EXEC. * Declare static cursor.
COBOL Sample Programs COBOL Using a Static SQL Cursor Example C-1. Using a Static SQL Cursor (page 3 of 3) * * Close the cursor. EXEC SQL CLOSE get_by_partnum END-EXEC. * Commit any changes. EXEC SQL COMMIT WORK END-EXEC. IF sqlstate = sqlstate-ok DISPLAY "The program completed successfully.". STOP RUN. **************************************************************** sqlerrors SECTION. **************************************************************** move sqlstate to sqlstate-save.
Ensuring Data Consistency COBOL Sample Programs Ensuring Data Consistency Example C-2 executes the steps shown in Figure 14-2 on page 14-2 Example C-2.
COBOL Sample Programs Ensuring Data Consistency Example C-2. Using TMF to Ensure Data Consistency (page 2 of 2) IF sqlstate = sqlstate-ok DISPLAY "The transaction is committed." EXEC SQL COMMIT WORK END-EXEC. ELSE DISPLAY "The transaction is rolled back." EXEC SQL ROLLBACK WORK END-EXEC. END-IF. STOP RUN. **************************************************************** END PROGRAM Program-exF92.
Using Argument Lists in Dynamic SQL COBOL Sample Programs Using Argument Lists in Dynamic SQL Example C-3 executes the steps shown in Figure 9-2 on page 9-4. Example C-3. Using Argument Lists in Dynamic SQL (page 1 of 3) *-------------------------------------------------------------* Description: Using Argument Lists * Statements: PREPARE * EXECUTE USING ARGUMENTS * DEALLOCATE PREPARE * WHENEVER * GET DIAGNOSTICS *-------------------------------------------------------------IDENTIFICATION DIVISION.
COBOL Sample Programs Using Argument Lists in Dynamic SQL Example C-3. Using Argument Lists in Dynamic SQL (page 2 of 3) * Move statement with input variable to statement variable. MOVE "SELECT empnum, first_name, last_name, salary" & " FROM samdbcat.persnl.employee" & " WHERE empnum = CAST(? AS NUMERIC(4) UNSIGNED)" TO hv-sql-stmt. * Prepare the statement. EXEC SQL PREPARE sqlstmt FROM :hv-sql-stmt END-EXEC. * Initialize the input parameter in the WHERE clause.
COBOL Sample Programs Using Argument Lists in Dynamic SQL Example C-3. Using Argument Lists in Dynamic SQL (page 3 of 3) **************************************************************** sqlerrors SECTION. **************************************************************** EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. IF sqlstate not = sqlstate-ok EXEC SQL GET DIAGNOSTICS :hv-num = NUMBER END-EXEC.
COBOL Sample Programs Using SQL Descriptor Areas in Dynamic SQL Using SQL Descriptor Areas in Dynamic SQL Example C-4 executes the steps shown in Figure 10-2 on page 10-13. Example C-4.
COBOL Sample Programs Using SQL Descriptor Areas in Dynamic SQL Example C-4. Using Descriptor Areas With DESCRIBE (page 2 of 4) PROCEDURE DIVISION. START-LABEL. DISPLAY "This example uses SQL descriptor areas". EXEC SQL WHENEVER SQLERROR GOTO sqlerrors END-EXEC. * Initialize the output variable in SELECT list. DISPLAY "Enter the columns to be retrieved," & " separated by commas: ". ACCEPT in-columns. DISPLAY hv-sql-stmt. * Allocate SQL descriptor area for input parameters. MOVE 1 TO hv-desc-max.
COBOL Sample Programs Using SQL Descriptor Areas in Dynamic SQL Example C-4. Using Descriptor Areas With DESCRIBE (page 3 of 4) * Execute the prepared statement using the SQL descriptor areas. EXEC SQL EXECUTE sqlstmt USING SQL DESCRIPTOR 'in_sqlda' INTO SQL DESCRIPTOR 'out_sqlda' END-EXEC. * Get the count of the number of output values. EXEC SQL GET DESCRIPTOR 'out_sqlda' :hv-num = COUNT END-EXEC. * Get the i-th output value in NAME field and save it.
COBOL Sample Programs Using SQL Descriptor Areas in Dynamic SQL Example C-4. Using Descriptor Areas With DESCRIBE (page 4 of 4) ELSE IF sqlda-name = "SALARY" EXEC SQL GET DESCRIPTOR 'out_sqlda' VALUE :i :hv-salary = VARIABLE_DATA END-EXEC. DIVIDE 100.0 INTO hv-salary GIVING hv-temp DISPLAY "Salary is: " hv-temp ELSE DISPLAY "Sqlda-name is " sqlda-name END-IF END-IF END-IF END-IF END-IF END-IF END-PERFORM. EXEC SQL DEALLOCATE PREPARE sqlstmt END-EXEC. EXEC SQL DEALLOCATE DESCRIPTOR 'in_sqlda' END-EXEC.
Using a Dynamic SQL Cursor COBOL Sample Programs Using a Dynamic SQL Cursor Example C-5 executes the steps shown in Figure 10-2 on page 10-13. Example C-5. Using a Dynamic SQL Cursor (page 1 of 3) *-------------------------------------------------------------* Description: Using a Dynamic SQL Cursor * Statements: PREPARE * Dynamic DECLARE CURSOR * OPEN * FETCH * CLOSE * WHENEVER * GET DIAGNOSTICS *-------------------------------------------------------------IDENTIFICATION DIVISION. PROGRAM-ID.
COBOL Sample Programs Using a Dynamic SQL Cursor Example C-5. Using a Dynamic SQL Cursor (page 2 of 3) PROCEDURE DIVISION. START-LABEL. DISPLAY "This example uses a dynamic cursor.". EXEC SQL WHENEVER SQLERROR GOTO sqlerrors END-EXEC. MOVE "SELECT partnum, partdesc, price, qty_available" & " FROM samdbcat.sales.parts" & " WHERE qty_available <= CAST(? AS NUMERIC(5))" TO curspec. * Prepare cursor specification. EXEC SQL PREPARE cursor_spec FROM :curspec END-EXEC.
COBOL Sample Programs Using a Dynamic SQL Cursor Example C-5. Using a Dynamic SQL Cursor (page 3 of 3) **************************************************************** sqlerrors SECTION. **************************************************************** EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. IF sqlstate not = sqlstate-ok EXEC SQL GET DIAGNOSTICS :hv-num = NUMBER END-EXEC.
COBOL Sample Programs Using a Dynamic SQL Cursor HP NonStop SQL/MX Programming Manual for C and COBOL —544617-003 C -16
Index A ALTER SQLMP ALIAS statement 8-3 Argument lists, within dynamic SQL examples of 9-3 rowsets 12-5 sample program, COBOL C-6 summary of statements 9-2 Assignment statement 5-15 Autocommit setting 14-4 Automatic recompilation 8-12 B BEGIN WORK statement 6-11 Buffers, with VARIABLE_POINTER 10-8 C C host variables 2-3 See also Host variables creating with INVOKE 3-31 data types 3-2 example of 3-28 C preprocessing directives #define 15-11 #include 15-9 #line 15-12 c89 utility examples of 15-42, 15-45 opt
C Index Compilation of modules embedded module definitions 15-30, 16-25 module definition files 15-34, 16-29 Compilation of programs description of 1-13 embedded module definitions 15-3, 16-3 examples build C application 15-50 build COBOL application 16-43 build C/C++ with SQL 15-47 deploy static SQL to RDF system 15-54 develop native C/C++ with SQL on OSS 15-49 quick builds and mxcmp defaults in one-file deployment 15-52 module definition files 15-6, 16-6 Compile-time name resolution 8-6 Compile-time spe
D Index C/C++ preprocessor (continued) running with mxsqlc command 15-4, 15-7 Windows-hosted 15-21 C/C++ program compilation 15-1 D Data consistency committing database changes 14-8 declaring host variables 14-3 default attributes for transactions 14-6 description of 1-11 error testing, examples of 14-7 examples of sample program, COBOL C-4 sample program, C++ B-1 grouping statements within transactions 14-7 rolling back database changes 14-8 starting transactions 14-6 steps for ensuring C 14-1 COBOL 14-
D Index DATE host variable description of 3-4, 4-3 format example 3-23, 4-13 Date-time data assigning to char array COBOL 4-15 C/C++ 3-25 casting COBOL 4-15, 4-16 C/C++ 3-25, 3-26 dynamic cursors 11-7 format COBOL 4-13 C/C++ 3-23 inserting from char array COBOL 4-16 C/C++ 3-26 inserting or updating COBOL 4-15 C/C++ 3-25 nonstandard SQL/MP DATETIME data types 3-25, 4-15 selecting COBOL 4-14 C/C++ 3-24 static cursors 6-12 static rowsets 7-9 Date-time examples dynamic cursors nonstandard SQL/MP DATETIME, C 1
D Index DELETE statement (continued) rowsets 7-19 syntax 5-12 DESCRIBE statement description of 12-10 INPUT form 10-4 OUTPUT form 10-7 Descriptor See also Diagnostics area allocating 10-3 deallocating 10-3 description of 10-1 input parameters 10-3 output variables description of 10-7 retrieving values of 10-7 setting input data values 10-4 setting input parameter 10-6 specifying output with dynamic cursor 11-10 using descriptor areas sample program, C A-17 sample program, COBOL C-9 Diagnostics area 13-13
E Index Dynamic SQL (continued) declaring host variable for statement 10-14 default value setting 9-8 describing input parameters 10-16 describing output parameters 10-16 description of 1-7, 9-1 executing the statement 10-18 getting COUNT of item descriptor areas 10-19 getting VALUE of output within SQL descriptor area 10-19 input parameters in 10-3 parameters 10-3 preparing the statement 9-5, 10-15 rowsets 12-1 setting INDICATOR for null within SQL descriptor area 10-17 setting values of input parameters
F Index Exception conditions checking SQLCODE 13-5 checking SQLSTATE 1-10, 13-1 description of 1-10 using GET DIAGNOSTICS 13-13 using WHENEVER 13-8 Exception declaration 2-7 Exception handling 13-1 EXECUTE IMMEDIATE statement, setting dynamic SQL default values with 9-8 F FETCH statement description of 6-6 examples of 6-6, 6-10, 7-12 loop processing 6-10 selecting rowsets 7-12 syntax for transferring values 11-5 Fixed-length character data declaring char array 3-14 inserting and updating 3-15 inserting f
I Index Host variables (continued) data types C 3-2 COBOL 4-2 date-time COBOL 4-3, 4-13 C/C++ 3-4, 3-23 declaring COBOL 4-1 C/C++ 3-1 dynamic cursors 11-4 examples 1-3 static rowsets 7-2 defined COBOL 4-1 C/C++ 3-1 extended data types 3-9 floating-point format 3-6 in C 2-3 in C++ 2-3 indicator variables C 3-12 COBOL 4-10 initializing host variables specified in DECLARE CURSOR 6-5 input, defined COBOL 4-1 C/C++ 3-1 interval COBOL 4-3, 4-13 C/C++ 3-4, 3-23 naming conventions C 3-12 COBOL 4-9 NCHAR 3-3 NCHAR
L Index INSERT statement inserting date-time nonstandard SQL/MP DATETIME value 5-8 standard date-time value 5-8 inserting from host variables 5-4, 7-14, 7-34 inserting from rowset arrays 7-14, 7-17 inserting from rowset-derived table 7-34 inserting interval values C 5-8 COBOL 5-9 inserting NULL C 5-6 COBOL 5-7 rowset arrays 7-15 rowset-derived table 7-36 inserting rows, C 5-5 inserting timestamp value 7-17 Inspect, for debugging 15-67 Interval data dynamic cursors 11-7, 11-8 inserting COBOL 4-17 C/C++ 3-2
M Index M MAP DEFINE 8-3 Mapping from logical to physical object names 1-2 MDFs See Module definition files (MDFs) Module compilation definition files 15-34, 16-29 embedded module definitions 15-30, 16-25 Module creation, SQL/MX methods COBOL 16-2 comparison of 1-12 C/C++ 15-2 Module definition files (MDFs) compiling COBOL 16-29 compiling C/C++ 15-34 generating 17-7 MODULE directive placement 2-2 Module file errors 15-66, 16-49 Module management See also Grouping, Targeting, and Versioning description of
O Index nmcobol utility (continued) options for SQL/MX 16-33 using to compile COBOL program in a single command 16-32 NULL inserting 3-30 inserting multiple rows with using indicator host variable array 7-15 using indicator host variable array in rowset-derived table 7-36 keyword instead of indicator variable 4-20 retrieving using NULL predicate 3-31, 4-21 testing for 3-30, 4-20 NULL STRUCTURE clause 3-37 Numeric host variables 3-5, 4-12 O OBEY command files, for creating database objects 2-12 Object nam
Q Index Preprocess in Guardian environment C 15-60 C++ 15-62 Preprocessor interpretation of environment variables, example of 17-7 OSS-hosted COBOL 16-9 C/C++ 15-8, 15-17 Windows-hosted COBOL 16-18 C/C++ 15-21 Program compilation C and C++ 15-2 COBOL 16-3, 16-6 C/C++ 15-6 embedded module definitions 15-3, 16-3 module definition files 15-6, 16-6 Program management description of 17-1 managing files 17-3 Program management, debugging 15-67 PROTOTYPE host variables example of 5-18 syntax 8-4 using as table n
S Index Rowset-derived tables available from static rowsets only 12-1 deleting rows by using 7-39 description of 7-28 inserting rows from 7-34 limiting the size of 7-35 updating rows by using 7-37 ROWSET_IND_LAYOUT_SIZE 12-8 ROWSET_SIZE 12-6 ROWSET_VAR_LAYOUT_SIZE description of 12-6 minimum values table 12-7 Run-time errors, avoiding 15-65 Run-time library, C++ default 15-29 S Schema declaration 2-7 Scope flow control 2-13 line order 2-13 Searched DELETE statement 5-12 Searched UPDATE statement 5-9 SELE
S Index SQL/MX compiler (continued) running COBOL 16-25 C/C++ 15-30 specifying alternate location 15-34, 16-29 SQL/MX conversational interface (MXCI) 1-1, 13-7 SQL/MX preprocessor C 15-8 COBOL 16-9 functions 15-9 invoking 15-40, 16-37 OSS-hosted, COBOL 16-13 OSS-hosted, C/C++ 15-16 Windows-hosted, COBOL 16-18 Windows-hosted, C/C++ 15-21 SQL/MX programs building and running in Guardian environment 15-58, 15-60, 15-62, 16-44, 16-45 compilation methods COBOL 16-2 C/C++ 15-2 description of 1-12 running 15-65,
T Index T Table names PROTOTYPE host variables 8-4 SQL/MP, DEFINE names 8-3 SQL/MP, Guardian names 8-2 SQL/MP, SQL/MP aliases 8-2 SQL/MX, logical names 8-2 TACL macro for C Guardian application 15-61 for COBOL Guardian application 16-46 for C++ Guardian application 15-63 Tandem floating-point format 3-6 Targeting C example using build subdirectory 17-15 using Module TableSet (MTSS) 17-13 COBOL example using build subdirectory 17-18 using Module TableSet (MTSS) 17-16 description of 17-12 effect of target a
W Index View names using DEFINEs 8-3 using host variables 8-4 Visual Inspect, for debugging 15-67 W Warning SQLCODE values 13-6 WHENEVER statement avoiding infinite loops 13-10 examples of 1-10, 13-9 NOT FOUND condition 13-8 precedence of conditions 13-9 scope of 13-9 SQLERROR condition 13-8 SQL_WARNING condition 13-9 using 1-10 Windows COBOL DLL file 16-18 COBOL preprocessor, syntax 16-19 C/C++ DLL file 15-21 C/C++ preprocessor, syntax 15-23 Z ZCLIDLL 16-5, 16-8 ZCLISRL 16-5, 16-8 Special Characters -
