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