HP NonStop SQL/MX Release 3.1 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 3.1 Supported Release Version Updates (RVUs) This publication supports J06.12 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.3 August 2010 663854-001 NonStop SQL/MX Release 3.
Legal Notices Copyright 2011 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 Release 3.1 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.
Contents 1. Introduction (continued) 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.
Contents 5. Simple and Compound Statements (continued) 5. Simple and Compound Statements (continued) Using PROTOTYPE Host Variables as Table Names 5-17 6.
Contents 7. Static Rowsets (continued) 7.
Contents 9. Dynamic SQL (continued) 9. Dynamic SQL (continued) Deallocate the Prepared Statement 9-7 Using EXECUTE IMMEDIATE 9-7 Setting Default Values Dynamically 9-8 10.
Contents 11. Dynamic SQL Cursors (continued) 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.
Contents 13. Exception Handling and Error Conditions (continued) 13.
Contents 15. C/C++ Program Compilation (continued) 15.
Contents 16. COBOL Program Compilation (continued) 16.
Contents A. C Sample Programs (continued) A.
Contents Examples (continued) 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.
Contents Figures (continued) 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.
Contents Tables (continued) Tables (continued) Table 16-1. Table 17-1. Table 17-2. Table 17-3. HP NonStop COBOL Compilers for Embedded SQL/MX Programs 16-24 File Naming Conventions 17-1 Preprocessor Interpretation of SQLMX_PREPROCESSOR_VERSION Environment Variable and -m and -x Options 17-8 Module Management Naming 17-12 HP NonStop SQL/MX Release 3.
Contents HP NonStop SQL/MX Release 3.
What’s New in This Manual Manual Information HP NonStop SQL/MX Release 3.1 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 3.1 Supported Release Version Updates (RVUs) This publication supports J06.12 and all subsequent J-series RVUs and H06.
What’s New in This Manual New and Changed Information Updated the contents in OSS-Hosted SQL/MX C/C++ Preprocessor on page 15-19 Updated the contents in Windows-Hosted SQL/MX C/C++ Preprocessor on page 15-25. Updated the contents in Compiling Embedded Module Definitions on page 15-35. Updated the contents in Compiling a Module Definition File on page 15-39. Updated the contents in Module File Errors on page 15-71. Minor changes across Section 15, C/C++ Program Compilation.
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.
About This Manual Related Documentation 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.
About This Manual Related Documentation 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 HP NonStop SQL/MP to SQL/MX Database and Application Migration Guide Describes how to migrate databases and applications from SQL/MP to SQL/MX. NonStop NS-Series Database Migration Guide Describes how to migrate NonStop SQL/MX, NonStop SQL/MP, and Enscribe databases and applications to HP Integrity NonStop NS-series systems.
About This Manual Examples in This Manual Support Center (BSC). For more information about downloading online help, see the SQL/MX Installation and Management Guide. 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.
About This Manual Hypertext Links Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: 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.
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).
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.
Introduction Embedding SQL Statements 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.
Introduction Declaring and Using Host Variables 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.
Introduction Using Host Variables 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.
Introduction Declaring and Using Static SQL Cursors 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.
Introduction Declaring and Using Static SQL Cursors 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.
Introduction Using Dynamic SQL 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.
Introduction Using Dynamic SQL Cursors 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.
Introduction Declaring a Rowset 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 ...
Introduction Processing Exception Conditions 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.
Introduction Using GET DIAGNOSTICS 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.
Introduction Compiling and Building an Application 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.
Introduction General Instructions 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.
Introduction SQL/MX Compiler 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 Statements Embedded SQL Declarations and 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 Statements Embedded SQL Declarations and 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 Statements Embedded SQL Declarations and 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 Release 3.
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.
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; C Host Variable Data Types /* 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.
Host Variables in C/C++ Programs Numeric Host Variables 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.
Host Variables in C/C++ Programs Using Corresponding SQL and C Data Types Table 3-1.
Host Variables in C/C++ Programs Extended Host Variable Data Types and Generated C Data Types Table 3-2.
Host Variables in C/C++ Programs Extended Host Variable Data Types and Generated C Data Types Table 3-4.
Host Variables in C/C++ Programs Data Conversion 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 Variable-Length Character Data INSERT test1.len = 3; Strcpy(test1.val,"abc"); EXEC SQL INSERT INTO t1 VALUES(:test1); UPDATE EXEC SQL UPDATE t1 SET name = :test1 WHERE name = 'xyz'; Generating Structures Instead of Using Null-Terminated Strings Prior to SQL/MX Release 1.8, all C/C++ VARCHAR columns were interpreted as nullterminated strings. Beginning with SQL/MX Release 1.
Host Variables in C/C++ Programs Numeric Data 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); ... In contrast to char data, for VARCHAR data, you do not need to insert blanks following the data up to the null terminator. Example: Using a Structure This example is the same as the previous one except that the preprocessor option -a is used.
Host Variables in C/C++ Programs Numeric Data , or blank), and the last character is reserved for the null terminator. If the scale is greater than zero, declare the char array three characters larger than the number of digits. The extra character is reserved for the decimal point. Within a SELECT INTO or FETCH statement, use the SQL/MX CAST function in the select list to convert the numeric value from the database to the CHAR data type.
Host Variables in C/C++ Programs Numeric Data 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. To do so, assign the data to host variables with the same data type as the database data type (that is, NUMERIC, PICTURE 9’s, or DECIMAL) as shown in Table 3-1 on page 3-8. Table 3-5 lists the extended SQL C data types NUMERIC and PICTURE 9’s COMP.
Host Variables in C/C++ Programs Numeric Data the application must use binary_64_s=1237000. The scale is implicit and must be handled in the application.
Host Variables in C/C++ Programs Numeric Data For static queries in embedded SQL programs, the input and output depend on the type of host variables that are declared.
Host Variables in C/C++ Programs Date-Time and Interval Data exists for single-precision Tandem REAL and FLOAT in IEEE floating-point format, the conversion to IEEE DOUBLE preserves the precision and the exponent. If you want a small floating-point data type with a smaller exponent and less storage, consider declaring the host variables as float. If you want more exponent and a larger precision, consider declaring the host variables as double or float(p).
Host Variables in C/C++ Programs Date-Time and Interval Data TIMESTAMP) host variable. For the required number of digits for DATE, TIME, or TIMESTAMP values, see Table 3-3 on page 3-9. If your C program performs string operations on the date-time host variable, you must append a null terminator to the output string before processing it because the date-time data types are internally processed as C character strings.
Host Variables in C/C++ Programs Date-Time and Interval Data Example If a database has a BILLINGS table that consists of the CUSTNUM and BILLING_DATE columns, this example inserts a customer number and date-time value into that table: C EXEC SQL BEGIN DECLARE SECTION; struct billing_rec { unsigned short hv_custnum; DATE hv_billing_date; ... } bill; ... EXEC SQL END DECLARE SECTION; ... bill.hv_billing_date[10]='\0'; ... EXEC SQL INSERT INTO billings VALUES (:bill.hv_custnum, :bill.hv_billing_date); ...
Host Variables in C/C++ Programs Date-Time and Interval Data WHERE custnum = :hv_this_customer; ... bill.hv_billing_date[5]='\0'; Inserting or Updating SQL/MP DATETIME Values Not Equivalent to DATE, TIME, or TIMESTAMP To insert or update nonstandard SQL/MP DATETIME values that are not equivalent to DATE, TIME, or TIMESTAMP, format a C character string in the desired display format for a date, time, or timestamp.
Host Variables in C/C++ Programs Date-Time and Interval Data 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-36 and Example 3-2 on page 3-37. 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.
Host Variables in C/C++ Programs INVOKE and Floating-Point Host Variables 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 <<"-----------"<
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.
Host Variables in COBOL Programs nn COBOL Host Variable Data Types 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.
Host Variables in COBOL Programs Using Corresponding SQL and COBOL Data Types 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.
Host Variables in COBOL Programs Using Corresponding SQL and COBOL Data Types 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).
Host Variables in COBOL Programs Using Corresponding SQL and COBOL Data Types 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.
Host Variables in COBOL Programs Data Conversion 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.
Host Variables in COBOL Programs Date-Time and Interval Data 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. ...
Host Variables in COBOL Programs Date-Time and Interval Data 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. ...
Host Variables in COBOL Programs Using Indicator Variables in a COBOL Program 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.
Host Variables in COBOL Programs Using Indicator Variables With the INVOKE Directive 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.
Simple and Compound Statements Selecting a Column With Date-Time or INTERVAL Data Type 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. HP NonStop SQL/MX Release 3.
Simple and Compound Statements Using PROTOTYPE Host Variables as Table Names HP NonStop SQL/MX Release 3.
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.
Static SQL Cursors Steps for Using a Static SQL Cursor 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.
Static SQL Cursors Steps for Using a Static SQL Cursor 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.
Static SQL Cursors Process the Retrieved Values 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.
Static SQL Cursors Cursor Position 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.
Static Rowsets Using 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.
Static Rowsets Considerations for Rowset Size 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.
Static Rowsets Specifying Rowset Arrays 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.
Static Rowsets Using Rowset Arrays for Input ... 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).
Static Rowsets Using Rowset Arrays for Output 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.
Static Rowsets Selecting Rows Into Rowset Arrays 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.
Static Rowsets Selecting Rows Into Rowset Arrays .... } 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.
Static Rowsets Selecting Rows Into Rowset Arrays 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.
Static Rowsets Selecting Rows Into Rowset Arrays 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.
Static Rowsets Selecting Rowsets With a Cursor 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. ...
Static Rowsets Selecting Rowsets With a Cursor 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.
Static Rowsets Inserting Rows From Rowset Arrays 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]...
Static Rowsets Inserting Rows From Rowset Arrays 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. ...
Static Rowsets Inserting Rows From Rowset Arrays 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.
Static Rowsets Updating Rows by Using Rowset Arrays 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.
Static Rowsets Updating Rows by Using Rowset Arrays 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.
Static Rowsets Deleting Rows by Using Rowset Arrays 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. ...
Static Rowsets Specifying Size and Row ID for Rowset Arrays 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.
Static Rowsets Specifying Size and Row ID for Rowset Arrays using the rowset-derived table syntax presented in Selecting From Rowset-Derived Tables With a Cursor on page 7-32.
Static Rowsets Limiting the Size of the Input Rowset 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.
Static Rowsets Limiting the Size of the Input Rowset When Declaring a Cursor 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.
Static Rowsets Limiting the Size of the Output Rowset 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.
Static Rowsets Using the Index Identifier 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.
Static Rowsets Using the Index Identifier ... 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.
Static Rowsets Using the Index Identifier 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.
Static Rowsets Specifying Rowset-Derived Tables } ... 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. ...
Static Rowsets Using Rowset-Derived Tables in DML Statements :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.
Static Rowsets Selecting From Rowset-Derived Tables 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.
Static Rowsets Selecting From Rowset-Derived Tables 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. ...
Static Rowsets Selecting From Rowset-Derived Tables With a Cursor ... ***** 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. ...
Static Rowsets Selecting From Rowset-Derived Tables With a Cursor 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.
Static Rowsets Inserting Rows From Rowset-Derived Tables 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.
Static Rowsets Limiting the Size of a Rowset-Derived Table 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. ...
Static Rowsets Inserting Null 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.
Static Rowsets Updating Rows by Using Rowset-Derived Tables 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.
Static Rowsets Deleting Rows by Using Rowset-Derived Tables 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 Release 3.
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 Release 3.
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.
Dynamic SQL Statements for Dynamic SQL With Arguments 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.
Dynamic SQL Steps for Using Dynamic SQL With Argument Lists 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; ..
Dynamic SQL Declare a Host Variable for the Dynamic SQL Statement 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. .
Dynamic SQL Move the Statement Into the Host Variable 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.
Dynamic SQL Set Explicit Input Values 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.
Dynamic SQL Deallocate the Prepared Statement 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.
Dynamic SQL Setting Default Values Dynamically 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 Release 3.
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.
Dynamic SQL Cursors Steps for Using a Dynamic SQL Cursor 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.
Dynamic SQL Rowsets Setting the Rowset-Specific Descriptor Fields 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.
Dynamic SQL Rowsets Setting the Rowset-Specific Descriptor Fields 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.
Exception Handling and Error Conditions Declaring SQLSTATE Declaring SQLSTATE In a C program, declare SQLSTATE as a char array of 6 bytes (char[6]), within the Declare section. In a COBOL program, declare SQLSTATE of type PIC (5) within the Declare section. 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]; ...
Exception Handling and Error Conditions SQL/MX SQLSTATE Values Table 13-1.
Exception Handling and Error Conditions Using SQLSTATE Table 13-2.
Exception Handling and Error Conditions Checking the SQLCODE Variable ... /* Process the SQL error. */ } /* end process_sqlstate */ 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-13. Example COBOL EXEC SQL BEGIN DECLARE SECTION END-EXEC.
Exception Handling and Error Conditions Declaring SQLCODE and SQLSTATE Both If the SQLCODE or SQLSTATE is missing or incorrect, the following behavior is observed: If SQLCODE or SQLSTATE is missing or incorrect, the preprocessor will issue a warning for SQLCODE, SQLSTATE, or both depending on the argument for the -w option. If the SQLCODE or SQLSTATE is missing in a scope, warning 13085 will be issued when the first embedded SQL statement within the scope is encountered.
Exception Handling and Error Conditions Declaring SQLCODE and SQLSTATE Table 13-3.
Exception Handling and Error Conditions Declaring SQLCODE and SQLSTATE Table 13-4.
Exception Handling and Error Conditions Declaring SQLCODE and SQLSTATE The following embedded application a.sql is used to explain the behavior of the -w option with various arguments: #include
Exception Handling and Error Conditions mxsqlc SQLCODE Values a.sql -c a.cpp -m a.mdf -w both *** WARNING[13086] SQLSTATE is not declared of type char[6] inside the Declare Section. *** WARNING[13025] Warning(s) near line 8. *** WARNING[13085] SQLCODE is not declared inside the Declare Section. *** WARNING[13025] Warning(s) near line 11. *** WARNING[13085] SQLCODE is not declared inside the Declare Section. *** WARNING[13085] SQLSTATE is not declared inside the Declare Section.
Exception Handling and Error Conditions SQL/MX Exception Condition Messages EXEC SQL INSERT INTO sales.job (jobcode,jobdesc) VALUES (:new_jobcode,:new_jobdesc); if (SQLCODE == 0) { printf ("\nValues were inserted!"); EXEC SQL COMMIT WORK; } else process_sqlcode(); Note. You must declare SQLCODE anywhere in your program or declare SQLSTATE within a Declare section. If you do not verify SQLSTATE, you must not declare SQLSTATE because query execution requires string processing to return the SQLSTATE value.
Exception Handling and Error Conditions Viewing the SQL Messages Viewing the SQL Messages To view a list of all SQL messages, see the appropriate messages manual. The message key is a sequential SQL/MX message number that is returned automatically by NonStop SQL/MX when an error condition occurs. For example, this error message might be displayed within your application development tool while it is preparing an embedded SQL program: *** ERROR[1000] A syntax error occurred.
Exception Handling and Error Conditions Using the WHENEVER Statement Using the WHENEVER Statement The WHENEVER declaration specifies an action that a program takes, depending on the results of subsequent SQL statements. When you specify WHENEVER, the SQL/MX preprocessor generates statements in your program that perform run-time checking using the SQLSTATE variable after each SQL statement executes. The generated statements check for these conditions: NOT FOUND condition: No data was found.
Exception Handling and Error Conditions Precedence of Multiple WHENEVER Declarations Precedence of Multiple WHENEVER Declarations When more than one WHENEVER declaration applies to an SQL statement, NonStop SQL/MX processes the conditions in order of precedence: 1. NOT FOUND 2. SQLERROR 3. SQL_WARNING For example, an SQL error and an SQL warning can occur for the same statement, but the error condition has a higher precedence and is processed first.
Exception Handling and Error Conditions Saving and Restoring SQLSTATE or SQLCODE Example This example enables and disables the WHENEVER directive: C EXEC SQL WHENEVER SQLERROR GOTO end_prog; /* enables action */ ... end_prog: EXEC SQL WHENEVER SQLERROR CONTINUE; /* disables action */ ...
Exception Handling and Error Conditions Declaring SQLSTATE or SQLCODE in an Error Routine FETCH and SQLCODE is checked, the value is equal to the original value returned from FETCH. Example This example uses WHENEVER SQLERROR PERFORM to save and restore SQLSTATE: COBOL EXEC 01 01 01 SQL BEGIN DECLARE SECTION END-EXEC. sqlstate pic x(5). saved-sqlstate pic x(5). hv-num pic s9(9) comp. ... EXEC SQL END DECLARE SECTION END-EXEC. ... EXEC SQL WHENEVER SQLERROR PERFORM sqlerrors END-EXEC. ... sqlerrors.
Exception Handling and Error Conditions Accessing and Using the Diagnostics Area ... EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6]; ... EXEC SQL END DECLARE SECTION; ... EXEC SQL WHENEVER SQLERROR CALL sql_error; ... EXEC SQL OPEN get_by_partnum; EXEC SQL FETCH get_by_partnum INTO :hv_partnum,:hv_partdesc,:hv_price,:hv_qty_available; ...
Exception Handling and Error Conditions Using the GET DIAGNOSTICS Statement execution of an SQL statement within the transaction. Use the SET TRANSACTION statement to set the size of the diagnostics area. To access the information in the diagnostics area, use the GET DIAGNOSTICS statement. The diagnostics area consists of: Statement information: Header area consisting of information on the SQL statement as a whole.
Exception Handling and Error Conditions Getting Statement and Condition Items Examples C COBOL /* Set new_jobcode and new_jobdesc host variables. */ EXEC SQL INSERT INTO sales.job (jobcode,jobdesc) VALUES (:new_jobcode,:new_jobdesc); ... if (strcmp(SQLSTATE, SQLSTATE_OK) == 0) { printf ("\nValues were inserted!"); EXEC SQL COMMIT WORK; } else get_diagnostics(); ... void get_diagnostics(void) { EXEC SQL GET DIAGNOSTICS :num = NUMBER; ...
Exception Handling and Error Conditions Special SQL/MX Error Conditions :hv-msgtext = MESSAGE_TEXT END-EXEC. ... DISPLAY "Condition: " i DISPLAY "Table : " hv-tabname DISPLAY "Column : " hv-colname DISPLAY "SQLSTATE : " hv-sqlstate DISPLAY "SQLCODE : " hv-sqlcode DISPLAY "MESSAGE : " hv-msgtext END-PERFORM. * Process the SQL error ... 1000-GET-DIAGNOSTICS-END.
Exception Handling and Error Conditions Recovering From the Lost Open Error Open Error (8574), as well as other recompilation errors, to the program. For more information on similarity checks and automatic recompilation, see Section 8, Name Resolution, Similarity Checks, and Automatic Recompilation.
Exception Handling and Error Conditions Recovering From the Lost Open Error HP NonStop SQL/MX Release 3.
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.
Transaction Management C Steps for Ensuring Data Consistency 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.
C/C++ Program Compilation Compiling Embedded SQL C/C++ Programs With Embedded Module Definitions 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-23 or 15-30.
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-34 and Compiling Embedded Module Definitions on page 15-35. mxCompileUserModule sqlprog.exe 9. The SQL/MX compiler produces the SQL/MX module.
C/C++ Program Compilation Compiling Embedded SQL C/C++ Programs With Module Definition Files 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-34 and Compiling a Module Definition File on page 15-39. mxcmp sqlprog.m 9.
C/C++ Program Compilation Preprocessor Functions Windows workstation. For information, see the SQL/MX Installation and Management Guide. The syntax for using the preprocessor in each environment appears under Syntax for the OSS-Hosted SQL/MX C/C++ Preprocessor on page 15-20 and Syntax for the Windows-Hosted SQL/MX C/C++ Preprocessor on page 15-27. Preprocessor Functions The preprocessor processes C/C++ and SQL source statements.
C/C++ Program Compilation Preprocessor Functions The following is the content of the generated module file: ALLOCATE STATIC INPUT DESCRIPTOR SQLMX_DEFAULT_STATEMENT_1_0_IVAR FOR STATEMENT SQLMX_DEFAULT_STATEMENT_1 (CHARACTER(20) NOT NULL); --------- STATEMENT INDEX 0 --------PROCEDURE SQLMX_DEFAULT_STATEMENT_1 ("a" CHARACTER(20)) INTO t1 (val) values(:"a"); INSERT The pragma, #pragma SQL CHAR_AS_STRING – SQL/MX Release 3.1 supports the CHAR_AS_STRING pragma in the embedded SQL file.
C/C++ Program Compilation Preprocessor Functions If you specify the -I option, the preprocessor expands the nested #include files. The -I option supports a maximum nesting limit of 200 levels. While processing nested #include files, circular inclusion of include files is detected, and the preprocessor issues warning 13089, and comments the #include line in the output source file. By default, the preprocessor processes only the #include files on OSS, that have a .mxh extension.
C/C++ Program Compilation Preprocessor Functions If the -I option is specified, the contents of a1.mxh, a2.mxh, and a3.mxh are included in the output source file: a1.sql | +-->a1.mxh | +-->a2.mxh | +-->a3.mxh The following scenarios explain the behavior of the nested #include files: In case 1, while processing nested #include files, the #include file a1.mxh which is included circularly is not processed, and a warning 13089 is returned by the preprocessor.
C/C++ Program Compilation Preprocessor Functions If the file incl.mxh contains: #pragma section sect1 EXEC SQL BEGIN DECLARE SECTION; 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; this construct: #include "incl.mxh (sect1, sect3)" NOLIST is expanded to: /* #include "incl.
C/C++ Program Compilation Preprocessor Functions 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. Example: The contents of the mine.h and mine3 files are not included in the output source file. The content of the ../includes/mine2 file is processed and written to the output source file: #include "mine.h" #pragma MXH #include "..
C/C++ Program Compilation Preprocessor Functions This #define directive: #define MAX 255 EXEC SQL BEGIN DECLARE SECTION; char mystr [MAX-1]; EXEC SQL END DECLARE SECTION; is expanded to: #define MAX 255 EXEC SQL BEGIN DECLARE SECTION; char mystr [/*MAX-1*/ 254]; EXEC SQL END DECLARE SECTION; C #line Directive The preprocessor generates #line directives in the C/C++ annotated source file so that the user, during debugging, is directed to the input source line number and file name instead of the prepro
C/C++ Program Compilation Preprocessor Functions Host Variable Declarations The preprocessor checks each host variable declaration (that is, a variable declared between BEGIN DECLARE SECTION and END DECLARE SECTION) to ensure that the variable uses a valid data type. For valid host-variable data types, see Table 3-1 on page 3-8 and Table 3-4 on page 3-10. The preprocessor parses INVOKE as a valid embedded SQL statement within a host variable declaration section.
C/C++ Program Compilation Preprocessor Output Preprocessor Output C/C++ Annotated Source File for Embedded Module Definitions The SQL/MX C/C++ preprocessor processes a C/C++ source file, such as sourcefile.sql, and generates one annotated source file (source-file.c in C or source-file.cpp in C++) as its output file. The annotated source file contains the embedded module definitions.
C/C++ Program Compilation Preprocessor Output creates a module definition file in your current directory that contains embedded SQL statements. The preprocessor writes the header of the module definition file as: MODULE module-name NAMES ARE ISO88591; TIMESTAMP DEFINITION ( creation_timestamp ); source-file 'source-file location'; You can specify module-name by using the MODULE directive in your embedded SQL C/C++ program. For example: EXEC SQL MODULE TX015.SQLPP.
C/C++ Program Compilation OSS-Hosted SQL/MX C/C++ Preprocessor variable references are stored in the same order in which they appear in the SQL statement. OSS-Hosted SQL/MX C/C++ Preprocessor You can compile and run an embedded SQL C/C++ program in the OSS environment on a NonStop system. Although you cannot compile and run such a program in the Guardian environment, you can use an OSS pass-through command in the Guardian environment.
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 name of the SQL/MX C/C++ source file (for example, sqlprog.sql) 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. For more information, see INVOKE and Date-Time and Interval Host Variables (SQL/MX Release 1.8 Applications) on page 3-33.
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 OSS-Hosted SQL/MX C/C++ Preprocessor MODULE directive by 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.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor sqlstate issues a warning if SQLSTATE is undeclared or not declared as char SQLSTATE[6] in the Declare section. both issues warnings if either or both SQLCODE and SQLSTATE are undeclared or not declared as long SQLCODE and char SQLSTATE[6] respectively in the Declare section. -O replaces Guardian DEFINE in the #include directive, in the OSS file format.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor For example, to select the SQL/MX C/C++ preprocessor in the C:\PROGRA~1\HPSQL-~1\ directory, set MXSQLC from the Windows command line: set MXSQLC=C:\PROGRA~1\HPSQL-~1\mxsqlcnt.dll You can also set the environment variable in the Windows system properties.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor Syntax for the Windows-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]] [ -s system-name or IP-address] [ -r ODBC-listener ] [ -y NSK-username ] [ -z NSK-password ] [ -h ] [ -i pathname ] [ -x ] [ -g {moduleGroup[=module-group-specification-string] |moduleTableSet[=module-tableset-specificationstring] |moduleVer
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ 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 C/C++ source file (for example, sqlprog.sql) 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.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor 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-23. 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 Specifying the search locations of the module files on page 17-13. The maximum size for the moduleTableSet attribute is 31 characters.
C/C++ Program Compilation Windows-Hosted SQL/MX C/C++ Preprocessor 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. -I processes the nested #include files. -w handles warnings for SQLCODE and SQLSTATE declarations.
C/C++ Program Compilation Running the C/C++ Compiler and Linker Running the C/C++ Compiler and Linker The HP NonStop C/C++ compilers translate source code into machine language that is specific to a particular NonStop system architecture. The type of C/C++ compiler that you use to compile your SQL/MX program determines the NonStop system and environment where you can run the program. Note. TNS/R native compilation tools are available on systems running H06.05 or later RVUs.
C/C++ Program Compilation Running the SQL/MX Compiler Table 15-1. HP NonStop C/C++ Compilers for Embedded SQL/MX Programs Compiler Compiler Operating Environment Program Execution Environment c89 OSS or Guardian environment on a NonStop system running a G-series RVU OSS environment on a NonStop system running H06.
C/C++ Program Compilation Compiling Embedded Module Definitions Generates executable code for the execution plans (if the SQL objects in the statement are present at compile time) and creates a module in the user-specified local application directory, user-specified Guardian or OSS location(s) or both, application DLL location(s), or in the global /usr/tandem/sqlmx/USERMODULES directory.
C/C++ Program Compilation Compiling Embedded Module Definitions -e directs mxCompileUserModule to generate a warning rather than an error if a table or class MAP DEFINE in an SQL statement does not exist during explicit SQL/MX compilation. To find errors in a program during explicit SQL/MX compilation, omit the -e option. If you are using late name resolution and want to use a table or DEFINE that does not exist during explicit SQL/MX compilation, include the -e option.
C/C++ Program Compilation Compiling Embedded Module Definitions modulegroup=grp moduletableset=tgt moduleversion=ver The -d modulecatalog, moduleschema, modulegroup, moduletableset, and moduleversion options are similar to the mxsqlc -g modulecatalog, moduleschema, modulegroup, moduletableset, and moduleversion options because you use them to externally qualify simple module names. These options are not CONTROL QUERY DEFAULT settings (however, all other -d attr=value pairs are).
C/C++ Program Compilation Compiling Embedded Module Definitions location(s) or to the global /usr/tandem/sqlmx/USERMODULES directory. For more information, see Module Management Naming on page 17-9. Each module-name consists of: [[catalog.]schema.]module[MODULEGROUP=group] [MODULETABLESET=target][MODULEVERSION=version] If catalog and schema are omitted, their default value settings can be supplied with -d MODULECATALOG=catalog or -d MODULESCHEMA=schema.
C/C++ Program Compilation MXCMP Environment Variable These settings affect statement recompilation at execution time: mxCompileUserModule -d AUTOMATIC_RECOMPILATION=ON \ -d SIMILARITY_CHECK=ON sqlprog.o The following command compiles the embedded module definition and places the module file in the user-specified OSS location, /usr/mymodules: mxcompileusermodule -g moduleLocal=/usr/mymodules 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 user-specified local application directory, user-specified Guardian or OSS location(s) or both, application DLL location(s), or in the global /usr/tandem/sqlmx/USERMODULES directory.
C/C++ Program Compilation Compiling a Module Definition File If these conditions are not met, an error is generated, and no module is created. 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.
C/C++ Program Compilation c89 Utility: Using One Command for All Compilation Steps The following command compiles the module definition and places module file in the user specified Guardian location, /G/data01/mymod: mxcmp -g moduleLocal=/G/data01/mymod sqlprog.
C/C++ Program Compilation c89 Utility: Using One Command for All Compilation Steps listing Directs the SQL/MX preprocessor to write its diagnostic messages to a file named file.eL (where file is the name of the primary source file). 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.
C/C++ Program Compilation -Wmxcmp[={"args" | args}] c89 Utility: Using One Command for All Compilation Steps Invokes the SQL/MX compiler. If compiling with embedded module definitions, invokes mxCompileUserModule. If compiling with separate module definition files, invokes mxcmp. Cannot be specified with -Wsql, -Wsqlcomp, -Wmigrate, or -WIEEE_FLOAT. You can use either or both warn or verbose args, separated by commas without space between them.
C/C++ Program Compilation c89 Utility: Using One Command for All Compilation Steps -WmoduleGroup[=[string]] Directs the SQL/MX preprocessor to group all an application’s module files. The moduleGroup is embedded in the module files’ names and enables the use of OSS wildcard file specification patterns to manage the files. For more information, see Grouping on page 17-23. -WmoduleTableSet[=[string]] Directs the SQL/MX preprocessor to use the module management targeting feature.
C/C++ Program Compilation c89 Utility: Using One Command for All Compilation Steps SQL/MX preprocessor (MXSQLC) and the SQL/MX compiler (MXCMP and MXCMPUM). For more information on c89 environment variables, see the Open System Services Shell and Utilities Reference Manual. SQL/MX Preprocessing Use the -Wsqlmx[=args] command to invoke the SQL/MX preprocessor. When -Wsqlmx is specified, the c89 utility also searches /usr/tandem/sqlmx/include for header files for the C/C++ compilers.
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.
C/C++ Program Compilation c89 Examples With Module Definition Files 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-64 OSS-to-Guardian File Naming on page 15-64 Steps for Building an SQL/MX C Application in the Guardian Environment on page 15-65 Using a TACL Macro to Build a C Guardian Application on page 15-66 Steps for Building an SQL/MX C++ Application in the Guardian Environment on page 15-67 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 Error 8809 error occurs if module files are deleted from the base directory of the application, the /usr/tandem/sqlmx/USERMODULES directory, user-specified Guardian or OSS location(s) or both, or the application DLL location(s).
C/C++ Program Compilation Debugging a Program Error 8400 The CLASS attribute of the DEFINE is not correct. Error 8400 occurs if the Define =_MX_MODULE_SEARCH_PATH CLASS type is not SEARCH DEFINE. This variable is used to locate and load the module file. Ensure that Define =_MX_MODULE_SEARCH_PATH is specified correctly and restart the embedded SQL program. Module File Naming In application development, avoid the use of delimited identifiers that contain dots (.
C/C++ Program Compilation Displaying Query Execution Plans the EXPLAIN function for a DML statement in a module. For more information on the EXPLAIN function, see the SQL/MX Reference Manual and the SQL/MX Query Guide. Note. If there is no EXPLAIN output for a statically compiled application, the GENERATE_EXPLAIN default attribute might have been turned off during compilation. In this case, verify that GENERATE_EXPLAIN is on and recompile the application.
C/C++ Program Compilation Displaying Query Execution Plans You can use the following query from MXCI to determine the module’s statement names and associated SQL queries, substituting the actual value of your module name in place of 'CAT.SCH.MYMOD'.
C/C++ Program Compilation Displaying Query Execution Plans Wild Card (%) Instead of specifying a statement pattern, use the percent sign (%) to represent all the DML statements in the module. The percent sign (%) must be placed within single quotes: '%' For information on how to interpret the output of the EXPLAIN function, see the SQL/MX Query Guide. HP NonStop SQL/MX Release 3.
C/C++ Program Compilation Displaying Query Execution Plans HP NonStop SQL/MX Release 3.
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 SQL/MX Release 3.1 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.
COBOL Program Compilation Compiling Embedded SQL COBOL Programs With Embedded Module Definitions 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.
COBOL Program Compilation Compiling Embedded SQL COBOL Programs With Module Definition Files 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 With embedded module definitions, you must successfully compile the output from the preprocessor before you can SQL compile the embedded module definition. 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.
COBOL Program Compilation Preprocessor Functions ?SOURCE =cobdef2 (section1) The OSS-hosted SQL/MX COBOL preprocessor resolves the DEFINE =cobdef1 and the file mapped by DEFINE is processed if the -O command-line option is specified. 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 Program Compilation Preprocessor Output Scans the statement for host variables (indicated by a colon) and ensures that each host variable is declared within the current scope of the program. Converts the SQL statement to a COBOL comment in the COBOL annotated source file.
COBOL Program Compilation Preprocessor Output 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 and Date-Time and Interval Host Variables (SQL/MX Release 1.8 Applications) on page 4-23. -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.
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 the moduleCatalog option is not set, the preprocessor emits the output 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 This example creates a self-contained, annotated output source file that contains an embedded module definition: mxsqlco sqlprog.ecob -c sqlprog.cbl \ -g moduleGroup=INVENTORY -g moduleVersion=V2 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.
COBOL Program Compilation Windows-Hosted SQL/MX COBOL Preprocessor If MXSQLCO is not set, the cross compiler invokes the latest version of the SQL/MX COBOL preprocessor installed on the system. An IDE is used. The IDE invokes c89, which uses MXSQLCO to select an alternative version. Some of the IDEs are: Enterprise Tool Kit (ETK)—plug-in for Microsoft Visual studio The environment variable MXSQLCO must be set before starting ETK.
COBOL Program Compilation Windows-Hosted SQL/MX COBOL Preprocessor -c COBOL-output-file is the name of the output preprocessed annotated source file that contains COBOL statements and embedded SQL statements converted to comments. This file is the input file for the COBOL compiler. The default is source-file.cbl, where source-file is the name of the SQL/MX COBOL source file (for example, sqlprog.ecob) without the file extension.
COBOL Program Compilation Windows-Hosted SQL/MX COBOL Preprocessor -q directs the preprocessor to accept SQL string literals delimited by double quotes in addition to single quotes. If you specify -q, you cannot use SQL delimited identifiers. -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.
COBOL Program Compilation Windows-Hosted SQL/MX COBOL Preprocessor regular or delimited identifiers. (See the description of regular and delimited identifiers in the SQL/MX Reference Manual.) If you supply more than one value for any attribute, only the final value is used. For information on the length of the module name, see Module Name Length on page 17-12. moduleGroup sets the moduleGroup attribute to group an application’s module files logically by sharing the same name prefix.
COBOL Program Compilation Running the COBOL Compiler and Linker [ -Q {[invokeCatalog=catalog-name] | [invokeSchema=schema-name] }] specifies the catalog name and schema name qualifiers for objects inside the invoke clause. If you use this option, specify one of the attributes— invokeCatalog or invokeSchema. If you want to specify both the attributes, repeat the -Q option for each attribute. invokeCatalog sets the catalog for unqualified objects inside the invoke clause as catalogname.
COBOL Program Compilation Running the COBOL Compiler and Linker 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-30 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 If these conditions are not met, an error is generated, and no module is created. If you do not specify -g moduleLocal[=] but set MXCMP_PLACES_LOCAL_MODULES ON, you must be in the same directory as the application executable when you invoke mxCompileUserModule.
COBOL Program Compilation Compiling Embedded Module Definitions application-file is the OSS path name of an object file that contains embedded module definitions. The OSS directory: Must exist and be accessible. Otherwise, an error is returned, and no module is created. 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.
COBOL Program Compilation MXCMP Environment Variable to mxcmp for SQL compilation. Each compilation of a selected module definition either succeeds or fails just like any mxcmp invocation. An mxcmp compilation failure does not affect preceding or following mxcmp invocations. In particular, an mxcmp compilation failure does not prevent mxCompileUserModule from proceeding with the mxcmp compilation of the next selected module definition.
COBOL Program Compilation Compiling a Module Definition File environment variable. This environment variable is used by the ecobol or nmcobol utility and enables you to direct ecobol or nmcobol to use another version of the mxCompileUserModule utility.
COBOL Program Compilation Compiling a Module Definition File -g moduleGlobal specifies that the module is placed globally in the /usr/tandem/sqlmx/USERMODULES directory. -g moduleLocal[=OSSdir] directs mxcmp to place the module in the named OSS directory. The OSS directory can be either a Guardian or an OSS location in the OSS format. If the OSS directory is omitted, the module is created in the current directory.
COBOL Program Compilation ecobol or nmcobol Utility: Using One Command for All Compilation Steps The static SQL/MX compiler provides backward compatible behavior. If the SQLMX_PREPROCESSOR_VERSION environment variable is set to 800, mxcmp behaves just like SQL/MX Release 1.8. Otherwise, mxcmp supports all SQL/MX Release 2.x features and command-line options. For more information, see Influencing Module Management Behavior on page 17-9.
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-23. -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. HP NonStop SQL/MX Release 3.
COBOL Program Compilation ecobol and nmcobol Examples With Module Definition Files 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 output file, but fail to SQL compile the module definition file that the preprocessor 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.
COBOL Program Compilation Understanding and Avoiding Common Run-Time Errors execute. For more information on delimited identifiers, see the SQL/MX Reference Manual. HP NonStop SQL/MX Release 3.
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-72. HP NonStop SQL/MX Release 3.
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 and Module Management Program Files 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[=OSSdir] mxcmp -g moduleGlobal | -g moduleLocal[=OSSdir] For additional details about setting the SQL/MX compiler options, see Compiling Embedded Module Definitions on page 15-35 and 16-25 and Compiling a Module Definition File on page 15-39 and 16-30.
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-35 and 16-25.
Program and Module Management Generating modules in a user-specified location 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 Specifying the search locations for the module files mxCompileUserModule -g moduleGlobal | -g moduleLocal=[OSSdir] mxcmp -g moduleGlobal | -g moduleLocal=[OSSdir] Specifying the search locations for the module files The modules placed in the user-specified locations are loaded by the embedded SQL program using Define =_MX_MODULE_SEARCH_PATH or the OSS environment variable _MX_MODULE_SEARCH_PATH. Guardian DEFINE The Guardian DEFINE class must be SEARCH.
Program and Module Management Managing Modules Module search sequence SQL/MX will search for the module file in the following locations, in the specified order: 1. The location of the program executable. 2. Locations specified in the OSS environment variable, _MX_MODULE_SEARCH_PATH, followed by the locations specified in Define =_MX_MODULE_SEARCH_PATH. 3. Each of the application DLL locations. 4. The system global module directory, /usr/tandem/sqlmx/USERMODULES. Note.
Program and Module Management Influencing Module Management Behavior Influencing Module Management Behavior You might have static SQL/MX application build scripts that require that the SQL preprocessor always generate separate module definition files. By using the environment variable SQLMX_PREPROCESSOR_VERSION=800 and preprocessor options -x and -m, you can instruct the SQL preprocessor to generate module definition files.
Program and Module Management How Modules Are Named To accomplish these tasks, you need not change the C, C++, or COBOL source file or rely strictly on the MODULE directive or on module naming defaults. Instead, you can specify the catalog and schema and the target, version, and group attributes for the module names during SQL preprocessing. The external naming of the module files during preprocessing influences the targeting, versioning, and grouping properties of an application.
Program and Module Management How Modules Are Named In this example, no default catalog or schema are in effect and no module directive exists. SQLMX_DEFAULT_CATALOG_.SQLMX_DEFAULT_SCHEMA_.SQLMX_DEFAULT_MODUL E_2119439730011160670 (on PC) GROUP.USER.
Program and Module Management How Modules Are Named Syntax for the Windows-Hosted SQL/MX COBOL Preprocessor on page 16-19 Note. To fully understand the effects of using or not using each of the module management attributes, see Table 17-3 on page 17-13. Module Name Length Figure 17-1 shows the limits for module name length. In summary: You are not required to choose identifiers that use the maximum length. The maximum lengths are not additive.
Program and Module Management Effect of Module Management Naming For embedded SQL COBOL programs, the limit on a fully qualified three-part module name is 160-characters because COBOL restricts nonumeric literals to at most 160 characters. For more information, see the HP COBOL85 for NonStop Systems Manual. Examples This example is invalid: cat.sch.grp.modname.target.ver However, this example is valid: cat.sch.
Program and Module Management Targeting Using the Guardian or OSS DEFINE name To specify the search locations of the module files using the Guardian or OSS DEFINE name, complete the following steps: 1. Enter the Guardian or OSS DEFINE name. For example, _MX_MODULE_SEARCH_PATH. 2. Ensure that the DEFINE class is SEARCH. You can specify the module file locations of SEARCH using SUBVOL0-20 or RELSUBVOL0-20 or both attributes.
Program and Module Management Effect of the Target Attribute file, use class MAP DEFINEs for database object names and apply compile-time name resolution (or PROTOTYPE host variables and build into your application the logic to set these variables to their proper values at run time) to build applications that target different sets of database objects without changing the source code.
Program and Module Management Targeting Example for C: Using ModuleTableSet (MTSS) 2. Invoke the preprocessor, specifying a module-tableset-specificationstring with the -g option: mxsqlc empcnt.sql -c empcnt.c -m empcnt.m \ -g moduleTableSet=TEST 3. The previous preprocessor step produces a pure C file (empcnt.c) and a module definition file (empcnt.m). The c89 utility compiles and links the C file, producing an executable empcnt.exe, and the SQL/MX compiler compiles empcnt.m.
Program and Module Management Targeting Example for C: Using Build Subdirectory After building the application for both sets of tables, you can execute, in any order, either compiled executable (empcnt_test.exe or empcnt_prod.exe) with its compiled SQL. When empcnt_test.exe is run for the first set of tables, you must set up DEFINEs =DEPT to reference \TEST.$DATA.HR1.DEPT and =EMPLOYEE to reference \TEST.$DATA.HR1.EMPLOYEE. When empcnt_prod.exe is run, you must set up DEFINE’s =DEPT to reference \PROD.
Program and Module Management Targeting Example for COBOL: Using ModuleTableSet (MTSS) -obey /usr/lib/libc.obey \ /usr/lib/crtlmain.o\ ./$TableSet/empcnt.o \ -l zcplsrl \ -l zcrtlsrl \ -l zcresrl \ -l zcplosrl \ -l ztlhgsrl \ -l ztlhosrl \ -Bdynamic \ -l zclisrl \ -o ./$TableSet/empcnt.exe /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 Targeting Example for COBOL: Using ModuleTableSet (MTSS) tables is for a test environment, and the other set of tables is for a production environment. In a source file (empcnt.ecbl), the application’s SQL statements are coded with class MAP DEFINEs: EXEC SQL DECLARE COUNT_EMP_BY_DEPT CURSOR FOR SELECT D.DEPT_NUM, COUNT(E.EMP_NUM) FROM =DEPT AS D, =EMPLOYEE AS E WHERE D.DEPT_NUM = E.EMP_DEPTNUM GROUP BY D.DEPT_NUM END-EXEC.
Program and Module Management Targeting Example for COBOL: Using a Build Subdirectory 3. The previous step again produces a pure COBOL file (empcnt.cbl) and a module definition file (empcnt.m). Note. These two files (empcnt.cbl and empcnt.m) overwrite the COBOL and module definition files the first time the application is built because mxsqlco does not check for the existence of identically named files before it writes its output files. You can specify different output file names to avoid this situation.
Program and Module Management Versioning Set up class MAP DEFINEs. Then set up an OSS environment variable, TableSet, to supply both a module-tableset-specification-string and the name of a subdirectory to which the intermediate files (including the” beforelink object file) and executable can be written. export TableSet=TEST add_define =DEPT class=MAP file=\\TEST.\$DATA.HR1.DEPT add_define =EMPLOYEE class=MAP file=\\TEST.\$DATA.HR1.EMPLOYEE Invoke the shell script empcnt.sh.
Program and Module Management Versioning source file, you would change the source code according to the new version but keep the same MODULE directive. Versioning and targeting differ in that versioning typically requires you to make minor changes to the source code of the application. The version attribute is necessary if you want two or more versions of the application (and its module files) to coexist on the same NonStop system.
Program and Module Management Grouping If the environmental variable is set both for V1 and for V2, after the build script is run for each version, an executable exists in 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.^EMP_CNT_MODULE^^V2 Versioning Example: COBOL Set Up COBOL In this example, an environment variable ThisVersion is set, and a build script is invoked: mxsqlco empcnt.
Program and Module Management Grouping Use meaningful names for grouping your module file. Grouping Example: C INVENTORY modules C In this example, a C application is built from two modules: reports.sql and utils.sql. mxsqlc names the C and module definition file output file according to default rules. At this point, INVENTORY is used as the group name for all the modules in an inventory application, enabling the module files to be referred to by group name. mxsqlc reports.
Program and Module Management Grouping nmcobol is used to compile the pure COBOL files (reports.cbl and utils.cbl), and nld links the compiled object files. The last step is to build the application using mxcmp to compile the module definition files, reports.m and 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.
Program and Module Management HP NonStop SQL/MX Release 3.
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.
C Sample Programs C Using a Static SQL Cursor 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
C Sample Programs Using Argument Lists in Dynamic SQL 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.
C Sample Programs Using SQL Descriptor Areas With DESCRIBE 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.
C Sample Programs Using a Dynamic SQL Cursor 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.
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.
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.
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.
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.
C Sample Programs Using SQL Descriptors to Select UCS2 Data 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 Release 3.
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.
C++ Sample Program Ensuring Data Consistency 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.
COBOL Sample Programs Ensuring Data Consistency 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. **************************************************************** HP NonStop SQL/MX Release 3.
COBOL Sample Programs Using Argument Lists in Dynamic SQL 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.
COBOL Sample Programs Using a Dynamic SQL Cursor 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 Release 3.
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
Index C 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
Index D 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-
Index D 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
Index D 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
Index E 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
Index F 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
Index I 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
Index L 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
Index M 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
Index O 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
Index Q 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
Index S 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
Index S 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,
Index T 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
Index W 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 -
