HP NonStop SQL/MP Programming Manual for C Abstract This manual documents the programming interface to HP NonStop™ SQL/MP for C and is intended for application programmers who are embedding SQL statements and directives in a C program. Product Version NonStop SQL/MP G06 and H01 Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs, H06.03 and all subsequent H-series RVUs, G06.00 and all subsequent G-series RVUs, and D46.
Document History Part Number Product Version Published 429847-002 NonStop SQL/MP G06 December 2003 429847-003 NonStop SQL/MP G06 December 2004 429847-004 NonStop SQL/MP G06 April 2005 429847-005 NonStop SQL/MP G06 February 2006 429847-007 NonStop SQL/MP G06 and H01 August 2010 429847-008 NonStop SQL/MP G06 and H01 August 2012
Legal Notices © Copyright 2012 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
HP NonStop SQL/MP Programming Manual for C Index Examples Figures Tables Legal Notices What’s New in This Manual xi Manual Information xi New and Changed Information xi About This Manual xv Who Should Read This Guide xv Related Manuals xv Notation Conventions xx HP Encourages Your Comments xxii 1.
2. Host Variables (continued) Contents 2.
4. Data Retrieval and Modification (continued) Contents 4.
6. Explicit Program Compilation (continued) Contents 6.
8. Program Invalidation and Automatic SQL Recompilation (continued) Contents 8. Program Invalidation and Automatic SQL Recompilation (continued) Run-Time Recompilation Errors 8-9 Preventing Automatic Recompilations 8-9 9.
. Dynamic SQL Operations (continued) Contents 10. Dynamic SQL Operations (continued) Allocate Memory for the SQLDA Structures and Names Buffers Allocate and Fill In Output Variables 10-33 Developing a Dynamic SQL Pathway Server 10-36 Dynamic SQL Sample Programs 10-37 Basic Dynamic SQL Program 10-37 Detailed Dynamic SQL Program 10-42 11.
C. Maximizing Local Autonomy Contents C. Maximizing Local Autonomy Using a Local Partition C-1 Using TACL DEFINEs C-2 Using Current Statistics C-2 Skipping Unavailable Partitions C-3 D.
Examples (continued) Contents Examples (continued) Example 10-8. Example A-1. Example D-1. Example D-2. Detailed Dynamic SQL Program 10-44 COPYLIB File for Sample Database A-3 Version 2 SQLDA Structure D-4 Version 1 SQLDA Structure D-6 Figures Figure i. Figure ii. Figure 6-1. Figure 6-2. Figure 6-3. Figure 6-4. Figure 7-1. Figure 8-1. Figure 10-1. Figure A-1.
Tables (continued) Contents Tables (continued) Table 9-1. Table 9-2. Table 9-3. Table 9-4. Table 9-5. Table 10-1. Table 10-2. Table 10-3. Table 10-4. Table 10-5. Table 10-6. Table 11-1. Table B-1. Table B-2. Table D-1. Table D-2. Table D-3.
Contents HP NonStop SQL/MP Programming Manual for C—429847-008 x
What’s New in This Manual Manual Information HP NonStop SQL/MP Programming Manual for C Abstract This manual documents the programming interface to HP NonStop™ SQL/MP for C and is intended for application programmers who are embedding SQL statements and directives in a C program. Product Version NonStop SQL/MP G06 and H01 Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs, H06.03 and all subsequent H-series RVUs, G06.
What’s New in This Manual Changes to the H06.21/J06.06 manual Changes to the H06.21/J06.06 manual Updated footnote about compiler version support under Table 6-1, C Compilers, on page 6-2. Changes to the G06.28 Manual Added a Note on page 2-5 about the nonsupport for unsigned long long data type. Changed the format of short output_file_number under SQLCADISPLAY on pages 5-4 and 5-20.
What’s New in This Manual Changes in the G06.26 Manual Corrected two field names in: Table 9-5 on page 9-17 Table D-1 on page D-2 Changes in the G06.
What’s New in This Manual Changes in the G06.
About This Manual This manual describes the NonStop SQL/MP programmatic interface for the HP implementation of the C language. Using this interface, a C program can access a NonStop SQL/MP database using embedded SQL statements and directives. Who Should Read This Guide This manual is intended for application programmers who are embedding SQL statements and directives in a C program.
Related Manuals About This Manual Table i describes the manuals in the HP NonStop SQL/MP library. Table i. NonStop SQL/MP Library Manual Description Introduction to NonStop SQL/MP Introduces the NonStop SQL/MP relational database management system. SQL/MP Reference Manual Describes the NonStop SQL/MP language elements, including expressions, functions, commands, statements, SQLCI utilities and commands, and report writer commands. This manual is the printed version of Online Help.
Related Manuals About This Manual Figure i shows the manuals in the NonStop SQL/MP library. Figure i. NonStop SQL/MP Library Introduction to NonStop SQL/MP SQL/MP Glossary Reference Manuals Guides SQL/MP Installation and Management Guide SQL/MP Messages Manual SQL/MP Reference Manual SQL/MP Version Management Guide Programming Manuals SQL/MP Query Guide SQL/MP Report Writer Guide SQL/MP Programming Manual for C SQL/MP Programming Manual for COBOL VST001.
Related Manuals About This Manual In addition to the NonStop SQL/MP library, program development, Guardian, and OSS manuals can be useful to a C programmer. They are shown in Figure ii and described in Table ii, Table iii on page xx, and Table iv on page xx. Figure ii.
Related Manuals About This Manual Table ii. Program Development Manuals Manual Description C /C++Programmer’s Guide Describes HP extensions to the C and C++ languages, including how to write applications that run in either the Guardian or OSS environments. nld and noft Manual Describes how to use the native link editor (nld) and the native object file tool (noft).
Notation Conventions About This Manual Table iii. Guardian Manuals Manual Description Guardian Programmer’s Guide Describes how to use Guardian procedure calls from an application to access operating system services. Guardian Procedure Calls Reference Manual Describes the syntax for Guardian procedure calls. Guardian Procedure Errors and Messages Manual Describes error codes, error lists, system messages, and trap numbers for Guardian system procedures.
General Syntax Notation About This Manual Computer type. Computer type letters within text indicate C and Open System Services (OSS) keywords and reserved words; enter these items exactly as shown. For example: SYSTYPEþOSS [ ] Brackets. Brackets enclose optional syntax items. For example: OUT [ list-file ] A group of items enclosed in brackets is a list from which you can choose one item or none.
HP Encourages Your Comments About This Manual Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: SQLCOMP / IN object-file [ , OUT [ list-file ] ] / If there is no space between two items, spaces are not permitted. In this example, there are no spaces permitted between the period and any other items: $process-name.#su-name i and o.
1 Introduction NonStop SQL/MP is the HP relational database management system (RDBMS) that uses SQL to define and manipulate data in an SQL/MP database. You can run SQL statements interactively by using the SQL/MP conversational interface (SQLCI) or programmatically by embedding SQL statements and directives in a host-language program written in COBOL, C, Pascal, or TAL. This manual describes the programmatic interface to NonStop SQL/MP for C programs.
Declaring and Using Host Variables Introduction Declaring and Using Host Variables A host variable is a C variable with a data type that corresponds to an SQL data type. You use host variables to provide communication between C and SQL statements and to receive data from a database or to insert data into a database. You declare host variables in a Declare Section in the variable declarations part of your program.
Embedding SQL/MP Statements and Directives Introduction Embedding SQL/MP Statements and Directives Table 1-1 lists the SQL/MP statements and directives you can embed in a C program. Table 1-1.
Calling SQL/MP System Procedures Introduction Example 1-1 shows an example of static SQL statements embedded in a C program: Example 1-1. Static SQL Statements in a C Program /* C variable declarations */ ... EXEC SQL BEGIN DECLARE SECTION; struct in_parts_struc /* host variables */ { short in_partnum; long in_price; char in_partdesc[19]; } in_parts; EXEC SQL END DECLARE SECTION; void insert_function(void) { ... in_parts.in_partnum = 4120; in_parts.in_price = 6000000; strcpy (in_parts.
Introduction Compiling and Executing a Host-Language Program Compiling and Executing a Host-Language Program The steps to compile and run a C program that contains embedded SQL statements are similar to the steps you follow for a C program that does not contain embedded SQL statements. You must perform only one extra step for a host-language program: compiling the embedded SQL statements using the SQL compiler. 1.
Dynamic SQL Introduction Dynamic SQL With static SQL statements, you code the actual SQL statement in the C source file. However, with dynamic SQL, a C program can construct, compile, and run an SQL statement at run time. You code a host variable as a placeholder for the dynamic SQL statement, which is usually unknown or incomplete until run time. A dynamic SQL statement requires some input, often from a user at a terminal, to construct the final statement.
SQL/MP Version Management Introduction SQL/MP Version Management Each product version update (PVU) of NonStop SQL/MP has an associated version number. The first two PVUs were version 1 (C10 and C20) and version 2 (C30). Version 300 SQL/MP began using a three-digit version number to allow for software product revisions (SPRs). A new version number is always greater than the previous number, but the new number might not follow a constant increment.
Introduction SQL/MP Version Management HP NonStop SQL/MP Programming Manual for C—429847-008 1-8
2 Host Variables A host variable is a data item you can use in both C statements and NonStop SQL/MP statements to allow communication between the two types of statements. A host variable appears as a C identifier and can be any C data item declared in a Declare Section that has a corresponding SQL/MP data type as shown in Table 2-1 on page 2-3 and Table 2-2 on page 2-4. However, a host variable cannot be the name or identifier (the left part) of a #define directive.
Coding Host Variable Names Host Variables Place a Declare Section with the C variable declarations. You can specify more than one Declare Section in a program, if necessary, but you cannot nest Declare Sections. Do not place a Declare Section within a C structure declaration. Specify the C #include directive in a Declare Section to copy declarations from another file. However, do not use the SQL SOURCE directive. Use either C or SQL comment statements in a Declare Section.
Using Corresponding SQL and C Data Types Host Variables Using Corresponding SQL and C Data Types Table 2-1 and Table 2-2 on page 2-4 list the corresponding SQL and C data types. Table 2-1.
Using Corresponding SQL and C Data Types Host Variables Table 2-2.
Using Corresponding SQL and C Data Types Host Variables Note. C programs that contain an embedded SQL/MP code do not support the use of unsigned long long C variables even if that data type is not used for the SQL query. C programs containing unsigned long long C variables outside the EXEC SQL statements cannot be compiled in the Guardian and OSS environments. A workaround is to use the PC cross compiler.
Specifying Host Variables in SQL Statements Host Variables Specifying Host Variables in SQL Statements Use this syntax to specify a host variable in an SQL statement. You must precede the host variable name with a colon (:). The colon causes the C compiler to handle the name as a host variable. To use a pointer as a host variable in SQL statements, place the colon before the asterisk.
Declaring and Using Host Variables Host Variables TYPE AS specifies that the host variable will have the specified date-time (DATETIME, DATE, TIME, or TIMESTAMP) or INTERVAL data type. If a host variable must contain date-time or INTERVAL values, define it as a character data type. To cause NonStop SQL/MP to handle the host variable as a scaled value, either use the SETSCALE function or define the variable as C data type fixed.
Fixed-Length Character Data Host Variables SHIPMENTS table and appends a null terminator to the prod_desc array before printing the data: EXEC SQL BEGIN DECLARE SECTION; short prod_num; char prod_desc[11]; EXEC SQL END DECLARE SECTION; ... EXEC SQL SELECT prod_num, prod_desc INTO :prod_num, :prod_desc FROM =shipments WHERE prod_num > min_num; ...
Variable-Length Character Data Host Variables char prod_desc[6]; /* use for 5-character column */ EXEC SQL END DECLARE SECTION; strcpy(prod_desc, "abc"); /* Copy 3 characters and /* null terminator ...
Structures Host Variables To use a field as a host variable in an SQL statement, refer to the field by using the structure name: EXEC SQL SELECT empid, empname INTO :employee_info.empid, :employee_info.empname ...
Decimal Data Types Host Variables Decimal Data Types Use the DECIMAL data type for ASCII numeric data. Because a decimal string is actually a fixed-length character string that contains only ASCII digits, considerations for fixed-length character strings also apply to decimal strings. Follow these guidelines when you use character arrays as host variables for DECIMAL data: Declare a decimal array one byte larger than the number of digits you expect to store in the array.
Fixed-Point Data Types Host Variables To select database values into host variables To refer to values stored in the database for comparisons The SETSCALE function has this syntax: SETSCALE (:host-variable [ [ INDICATOR ] :indicator-variable ] , scale ) host-variable is an integer host variable. indicator-variable is an indicator variable associated with the host variable. scale specifies the scale of host-variable.
Date-Time and INTERVAL Data Types Host Variables When you use the INVOKE directive for a column with a scaled data type, the C compiler generates a comment that shows the scale of the column. For example, for price with data type NUMERIC (8,2), INVOKE generates the following: long price; /* scale is 2 */ These examples use the =parts DEFINE to represent the PARTS table. The first example inserts a new row with the value 98.34 in the PARTS.
Host Variables Date-Time and INTERVAL Data Types Table 2-3.
Date-Time and INTERVAL Data Types Host Variables INTERVAL values are represented as character strings with a separator between the values of the fields (year-month or day-time). An extra byte is generated at the beginning of the INTERVAL string for a sign. The default representations for DATE and INTERVAL values are shown in these examples.
Date-Time and INTERVAL Data Types Host Variables Example—Creating DATETIME and INTERVAL Data Types Example 2-1. Creating Valid DATETIME and INTERVAL Data Types #include #include #include
Using Indicator Variables for Null Values Host Variables Using Indicator Variables for Null Values A null value in an SQL column indicates that the value is either unknown for the row or is not applicable to the row. A program inserts a null value or tests for a null value using an indicator variable. An indicator variable is a 2-byte integer variable associated with the host variable that sets or receives the actual column value.
Retrieving Rows With Null Values Host Variables else display_result(); ... Retrieving Rows With Null Values You can use an indicator variable to insert null values into a database or to test for a null value after you retrieve a row. However, you cannot use an indicator variable set to –1 in a WHERE clause to retrieve a row that contains a null value. In this case, NonStop SQL/MP does not find the row and returns an sqlcode of 100, even if a column actually contains a null value.
Host Variables Advantages of Using an INVOKE Directive To run an INVOKE directive, a process started by the program must have read access to the invoked tables or views during C compilation. For details, see Required Access Authority on page 7-1. The CHAR_AS_STRING and CHAR_AS_ARRAY options of the SQL pragma affect the INVOKE directive as follows: The CHAR_AS_STRING option (the default) causes INVOKE to generate character data types with an extra byte for a null terminator.
C Structures Generated by the INVOKE Directive Host Variables Example 2-2. CREATE TABLE Statements CREATE TABLE \NEWYORK.$DISK1.SQL.
C Structures Generated by the INVOKE Directive Host Variables These INVOKE directives are coded in a C source file: EXEC EXEC EXEC EXEC SQL SQL SQL SQL BEGIN DECLARE SECTION; INVOKE \newyork.$disk1.sql.typesc1 AS typesc1_struc; INVOKE \newyork.$disk2.sql.typesc1 AS typesc2_struc; END DECLARE SECTION; Example 2-3. Structures Generated by the INVOKE Directive (page 1 of 2) /* Record Definition for table \NEWYORK.$DISK1.SQL.
Host Variables Using Indicator Variables With the INVOKE Directive Example 2-3. Structures Generated by the INVOKE Directive (page 2 of 2) char type_time[9]; char type_timestamp[27]; char type_interval[7]; short type_char_null_ok_i; char type_char_null_ok[11]; short type_num_null_ok_i; short type_num_null_ok; }; /* Record Definition for table \NEWYORK.$DISK1.SQL.
Using Indicator Variables With the INVOKE Directive Host Variables PREFIX and SUFFIX Clauses The PREFIX and SUFFIX clauses cause INVOKE to generate an indicator variable name derived from the column name and the prefix or suffix.
Using INVOKE With SQLCI Host Variables Using INVOKE With SQLCI You can also run the INVOKE directive interactively through SQLCI to create host variable declarations in a copy file. For example, this INVOKE directive generates a C copy file from the DEPT table: >> INVOKE =dept FORMAT C TO copylib (deptrec); ... Using INVOKE with SQLCI provides less program independence than embedding INVOKE in your program, because you must re-create the host variable declarations if the referenced table changes.
Treatment in C Statements Host Variables Treatment in C Statements A C statement treats a host variable declared with the CHARACTER SET clause as if the host variable had been declared without the clause. A C statement also treats the host variable length as the specified length multiplied by the number of bytes per character plus the null terminator if the SQL pragma specifies the CHAR_AS_STRING option (the default).
VARCHAR Data Type Host Variables HP NonStop SQL/MP Programming Manual for C—429847-008 2-26
3 SQL/MP Statements and Directives For a detailed description, including the syntax, of all SQL/MP statements and directives, see the SQL/MP Reference Manual. This section includes: Embedding SQL Statements Finding Information on page 3-3 Embedding SQL Statements Use this syntax to embed a NonStop SQL/MP statement or directive in a C source file. EXEC SQL sql-statement-or-directive ; sql-statement-or-directive is any SQL statement or directive shown in Table 3-1 on page 3-3.
SQL/MP Statements and Directives Placing Statements and Directives FROM =customer WHERE custnum = :find_this_customer ; Placing Statements and Directives Place SQL statements and directives and C compiler pragmas in a C source file. SQL Pragma To use embedded SQL statements and directives in a C program, you must specify the SQL pragma before any SQL or C statements (except comment statements).
Finding Information SQL/MP Statements and Directives Anywhere in the Program You can use these directives anywhere in a C program: WHENEVER directives SQL SOURCE directive CONTROL directives Finding Information Table 3-1 lists SQL/MP statements and directives you can embed in a C program and indicates where each statement or directive is documented. Table 3-1.
Finding Information SQL/MP Statements and Directives Table 3-1. Summary of SQL/MP Statements and Directives (page 2 of 4) Statement or Directive Manual * Description Data Definition Language (DDL) Statements ALTER CATALOG SQLRM Alters the security attributes of a catalog. ALTER COLLATION SQLRM Alters the security attributes of a collation; renames a collation.
Finding Information SQL/MP Statements and Directives Table 3-1. Summary of SQL/MP Statements and Directives (page 3 of 4) Statement or Directive Manual * Description Data Manipulation Language (DML) Statements CLOSE SQLRM SQLPM/C Terminates a cursor. DECLARE CURSOR SQLRM SQLPM/C Defines a cursor. DELETE SQLRM SQLPM/C Deletes rows from a table or view. FETCH SQLRM SQLPM/C Retrieves a row from a cursor. INSERT SQLRM SQLPM/C Inserts rows into a table or view.
Finding Information SQL/MP Statements and Directives Table 3-1. Summary of SQL/MP Statements and Directives (page 4 of 4) Statement or Directive Manual * Description Data Status Language (DSL) Statements GET CATALOG OF SYSTEM SQLRM Returns the name of a local or remote system catalog. GET VERSION SQLRM SQLPM/C Returns the version of a catalog, collation, index, table, or view; also returns the version of the SQL/MP system software.
Finding Information SQL/MP Statements and Directives Table 3-2 summarizes the C compiler pragmas that apply to a C program containing embedded SQL statements and directives. For a description of all C compiler pragmas, see the C/C++ Programmer’s Guide. Table 3-2. C Compiler Pragmas for SQL/MP Pragma Manual* Description SQL SQLPM/C CPG Indicates to the C compiler that a program contains embedded SQL statements and directives.
SQL/MP Statements and Directives HP NonStop SQL/MP Programming Manual for C—429847-008 3-8 Finding Information
4 Data Retrieval and Modification You can access data in an SQL/MP database using this Data Manipulation Language (DML) statements in a C program: Simple data manipulations––SELECT (single-row), INSERT, UPDATE, and DELETE statements Cursor operations––DECLARE CURSOR, OPEN, FETCH, and CLOSE statements where the cursor contains a SELECT, UPDATE, or DELETE statement Topics include: Opening and Closing Tables and Views on page 4-2 Single-Row SELECT Statement on page 4-4 Multirow SELECT
Opening and Closing Tables and Views Data Retrieval and Modification Table 4-1. SQL/MP Statements for Data Retrieval and Modification (page 2 of 2) SQL/MP Statement Description UPDATE statement with a cursor Updates the values in one or more columns in a set of rows, one row at a time. Use when you need to test a column value in a row before you update the row. DELETE statement without a cursor Deletes a single row or a set of rows from a table or protection view.
Data Retrieval and Modification Recovering From SQL Error 8204 1. A program accesses a table or view using one or more static DML statements (SELECT, INSERT, UPDATE, or DELETE) or a static cursor. The SQL executor opens the table or view for the program. 2. Any locks associated with the statements in Step 1 are released (for example, because the transaction ended).
Data Retrieval and Modification Single-Row SELECT Statement To recover from SQL error -8204 for a simple DML statement, a program might need to abnormally terminate the transaction and restart the operation from its beginning. Because some DDL changes can invalidate a DML statement, the SQL executor might first need to recompile the DML statement to use the new definition of the changed table or view. In some cases, the similarity check can prevent recompilation.
Using a Column Value to Select Data Data Retrieval and Modification NonStop SQL/MP returns these values to sqlcode after a SELECT statement: sqlcode Value Description 0 The SELECT statement was successful. 100 No rows qualified for the SELECT statement specification. <0 An error occurred; sqlcode contains the error number. >0 (¦100) A warning occurred; sqlcode contains the warning number. For more information about sqlcode, see Section 9, Error and Status Reporting.
Using a Primary Key Value to Select Data Data Retrieval and Modification :customer.postcode FROM sales.customer WHERE customer.custnum = :find_this_customer BROWSE ACCESS; /* Process data returned by the SELECT statement */ ... } int main(void) { EXEC SQL WHENEVER NOT FOUND CALL :not_found_function; find_record(); ...
Data Retrieval and Modification Simple Example Simple Example In this example, the search is performed on one column, which is the primary key of the table. For example, a cursor SELECT to retrieve all the columns in the EMPLOYEE table by primary key. The WHERE clause in this example selects on a primary key value. This means that the SQL compiler can choose the primary index as the access path so that each FETCH statement returns the next row in primary key sequence.
INSERT Statement Data Retrieval and Modification Do not code this request with this WHERE clause: WHERE LAST_NAME > :LAST-LNAME AND FIRST_NAME > :LAST-FNAME This clause does not retrieve names with the same last name as :LAST-LNAME and a first name greater than :LAST-FNAME. Also, do not code this request with this WHERE clause: WHERE ( (LAST_NAME = :LAST-LNAME AND FIRST_NAME > :LAST-FNAME) OR LAST_NAME > :LAST-LNAME) This clause would produce the correct results, but very slowly.
Data Retrieval and Modification Inserting a Single Row Inserting a Single Row This INSERT statement inserts a row (JOBCODE and JOBDESC columns) into the JOB table: EXEC SQL BEGIN DECLARE SECTION; short hv_jobcode; /* host variables */ char hv_jobdesc[18]; ... EXEC SQL END DECLARE SECTION; ... void insert_job(void) { /* Set the values of hv_jobcode and hv_jobdesc */ ... EXEC SQL INSERT INTO persnl.job (jobcode, jobdesc) VALUES (:hv_jobcode, :hv_jobdesc) ; ...
Data Retrieval and Modification Inserting a Timestamp Value Inserting a Timestamp Value This example inserts a timestamp value into tablet.columna. The columna definition specifies the data type TIMESTAMP DEFAULT CURRENT. The example uses the JULIANTIMESTAMP system procedures and the SQL CONVERTTIMESTAMP function. To call system procedures, a program must include declarations from the cextdecs header file. #include ...
Updating a Single Row Data Retrieval and Modification NonStop SQL/MP returns these values to sqlcode after an UPDATE statement. sqlcode Value 0 Description The UPDATE statement was successful. 100 No rows were found on a search condition. <0 An error occurred; sqlcode contains the error number. >0 (¦100) A warning occurred; sqlcode contains the first warning number. The UPDATE statement updates rows in sequence.
Data Retrieval and Modification Updating Multiple Rows If the UPDATE operation fails, check for SQL error 8227, which indicates you attempted to update a row with an existing key (primary or unique alternate). 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. This example updates the SALARY column of all rows in the EMPLOYEE table where the SALARY value is less than hostvar_min_salary.
Deleting a Single Row Data Retrieval and Modification To run a DELETE statement, a process started by the program must have read and write access to the table or view and to tables or views specified in subqueries of the search condition. For details, see Required Access Authority on page 7-1. NonStop SQL/MP returns these values to sqlcode after a DELETE statement. sqlcode Value Description 0 The DELETE statement was successful. 100 No rows were found on a search condition.
Using SQL Cursors Data Retrieval and Modification Using SQL Cursors An SQL cursor is a named pointer that a host-language program (C, COBOL, Pascal, or TAL) can use to access a set of rows in a table or view, one row at time. Using a cursor, a program can process rows in the same way it might process records in a sequential file. The program can test the data in each row at the current cursor position and then if the data meets certain criteria, the program can display, update, delete, or ignore the row.
Data Retrieval and Modification Steps for Using a Cursor The SQL statements used in Example 4-1 are described in detail later in this section: DECLARE CURSOR Statement on page 4-18 OPEN Statement on page 4-19 FETCH Statement on page 4-20 Multirow SELECT Statement on page 4-21 UPDATE Statement on page 4-22 Multirow DELETE Statement on page 4-23 CLOSE Statement on page 4-24 For information about declaring host variables, see Section 2, Host Variables. Steps for Using a Cursor 1.
Process Access ID (PAID) Requirements Data Retrieval and Modification Process Access ID (PAID) Requirements To use an SQL cursor, a process started by the program must have the access authority shown in this table. NonStop SQL/MP checks this authority when the program opens the cursor. For details, see Required Access Authority on page 7-1.
Data Retrieval and Modification Cursor Stability Cursor Stability Cursor stability guarantees that a row at the current cursor position cannot be modified by another program. For NonStop SQL/MP to guarantee cursor stability, you must declare the cursor with the FOR UPDATE clause or specify the STABLE ACCESS option. In some cases, a program might be accessing a copy of a row instead of the actual row.
Data Retrieval and Modification DECLARE CURSOR Statement Do not open multiple cursors on a table if any of the cursors are used to update that table. DECLARE CURSOR Statement The DECLARE CURSOR statement names and defines a cursor and associates the cursor with a SELECT statement that specifies the rows to retrieve. A C program requires no special authorization to run a DECLARE CURSOR statement.
Data Retrieval and Modification OPEN Statement OPEN Statement The OPEN statement opens an SQL cursor. The OPEN operation orders and defines the set of rows in the result table and then positions the cursor before the first row. 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.
FETCH Statement Data Retrieval and Modification FETCH Statement The FETCH statement positions the cursor at the next row of the result table and transfers a value from each column in the row specified by the associated SELECT statement to the corresponding host variable. To run a FETCH statement, a process started by the program must have read access to tables or views associated with the cursor. For information about process access, see Required Access Authority on page 7-1.
Data Retrieval and Modification Multirow SELECT Statement WHERE partnum >= :parts_rec.partnum ORDER BY partnum BROWSE ACCESS; ... void list_func(void) { EXEC SQL OPEN list_by_partnum; EXEC SQL FETCH list_by_partnum INTO :parts_rec.partnum, :parts_rec.partdesc, :parts_rec.price, :parts_rec.qty_available; ... } Multirow SELECT Statement When used with a cursor, a SELECT statement can return multiple rows from a table or protection view, one row at a time.
Data Retrieval and Modification UPDATE Statement ... EXEC SQL OPEN get_name_address; ... /* Set values for begin_code and end_code. */ EXEC SQL FETCH get_name_address INTO :customer_row.custname, :customer_row.street, :customer_row.city, :customer_row.state :customer_row.postcode; ... /* Process the row values. */ EXEC SQL CLOSE get_name_address; } UPDATE Statement When used with a cursor, an UPDATE statement updates rows, one row at a time, in a table or protection view.
Data Retrieval and Modification Multirow DELETE Statement ... EXEC SQL OPEN get_by_partnum; ... /* Set values of the host variables. */ EXEC SQL FETCH get_by_partnum INTO ... ; ... /* Test the value(s) in the current row. */ /* Update the current row */ EXEC SQL UPDATE sales.parts SET parts.partdesc = :new_partdesc, parts.price = :new_price, parts.qty_available = :new_qty WHERE CURRENT OF get_by_partnum; ... /* Branch back to FETCH to get the next row.
CLOSE Statement Data Retrieval and Modification FROM sales.parts WHERE (partnum >= :parts.partnum); ... EXEC SQL OPEN get_by_partnum; EXEC SQL FETCH get_by_partnum ... ... ; /* Test the value(s) in the current row. */ /* Delete the current row */ EXEC SQL DELETE FROM sales.parts WHERE CURRENT OF get_by_partnum ; ... /* Branch back to FETCH the next row. */ EXEC SQL CLOSE get_by_partnum; CLOSE Statement The CLOSE statement closes an open SQL cursor.
Data Retrieval and Modification Using Foreign Cursors A reference to a foreign cursor contains two parts, a procedure name and a cursor name. This example references a foreign cursor, list_by_partnum, which is declared in the procedure update_inv: update_inv.list_by_partnum A foreign cursor reference can appear in an OPEN, FETCH, or CLOSE cursor statement. It references a cursor that is declared in another procedure, which is not necessarily in the same compile source file.
Data Retrieval and Modification } { /* describe input and output here */ exec sql close update_inv.
5 SQL/MP System Procedures Table 5-1 describes the NonStop SQL/MP system procedures, which are written in TAL, that a C program can call to return various SQL information. These procedures are listed alphabetically. Table 5-1. SQL/MP System Procedures Procedure Description To Return Error and Warning Information SQLCADISPLAY Writes to a file or terminal the error and warning messages that NonStop SQL/MP returns to the SQLCA structure.
Guardian System Procedures SQL/MP System Procedures Guardian System Procedures In addition to the procedures in Table 5-1 on page 5-1, a C program can also call the Guardian procedures described in Table 5-2 to return information about SQL objects and programs. For a detailed description of these procedures, see the Guardian Procedure Calls Reference Manual. Table 5-2.
SQL/MP System Procedures SQLCADISPLAY For the alternate SQL message files available on your node, ask your database administrator or service provider. You can add (or modify) the =_SQL_MSG_node DEFINE either interactively from TACL or SQLCI, or programmatically from a C program: From TACL or SQLCI, enter an ADD DEFINE (or ALTER DEFINE) command. Do not include a backslash (\) or a space before the node name. For example, this command adds a new DEFINE for the $SQL.MSG.
SQLCADISPLAY SQL/MP System Procedures #include void SQLCADISPLAY ( short *sqlca, [ short output_file_number, [ short output_record_length, [ short *sql_msg_file_number, [ short errors, [ short warnings, [ short statistics, [ short caller_error_loc, [ short internal_error_loc, [ char *prefix, [ short prefix_length, [ char *suffix, [ short suffix_length, [ short *detail_params ); ] ] ] ] ] ] ] ] ] ] ] ] ] /* /* /* /* /* /* /* /* /* /* /* /* /* /* i i i i:o i i i i i i i i i i */
SQL/MP System Procedures SQLCADISPLAY The SQLMSG file contains text in English. You can specify a different SQL message file with the =_SQL_MSG_node DEFINE. For more information, see SQL Message File on page 5-2. errors controls the display of error messages: Y Display all errors. N Display only the first error. B Display all errors but suppress this prefix: ERROR from subsystem [nn] The default is Y. warnings controls the display of warning messages: Y Display all warning messages.
SQL/MP System Procedures SQLCADISPLAY internal_error_loc controls the display of the system-code location where the first error in the SQLCA occurred: Y Display the location. N Suppress the display. The default is Y. prefix is a string that the program uses to precede each output line. The default is three asterisks and a space (*** ). prefix_length is the length of the prefix string for each output line. The length must be an integer from 1 to 15. If you include prefix, prefix_length is required.
SQL/MP System Procedures SQLCADISPLAY out_fcb_2 specifies the second output file control block if SIO is enabled. To use out_fcb_2, assign it a value greater than 0. The default is Enscribe I/O. Additional considerations for the SQLCADISPLAY procedure are: NonStop SQL/MP returns errors as negative numbers and warnings as positive numbers. Therefore, you might accordingly need to modify your program. If there is no text for an error number, NonStop SQL/MP displays this message: No error text found.
SQLCAFSCODE SQL/MP System Procedures *** Statistics: Rows accessed/affected: 10 *** Estimated cost: 100 SQLCAFSCODE The SQLCAFSCODE procedure returns either the first or the last error in the SQLCA structure that was set by the file system, disk process, or the operating system. If there was no such error, SQLCAFSCODE returns 0. If the SQLCA is full when an error occurs, the error is lost.
SQLCAGETINFOLIST SQL/MP System Procedures SQLCAGETINFOLIST The SQLCAGETINFOLIST procedure returns error or warning information that NonStop SQL/MP sets in the SQLCA structure. You specify a list of numbers, called item codes (shown in Table 5-4 on page 5-11), to specify the error or warning information, and SQLCAGETINFOLIST returns the information to a structure in your program.
SQL/MP System Procedures SQLCAGETINFOLIST item_list is an array of item codes that describes the information you want returned in the result structure. For a list of these item codes, see Table 5-4 on page 5-11. number_items is the number of items you specified in the item_list array. result is a structure you define to receive the requested information. The items are returned in the order you specified in item_list. Each item is aligned on a word boundary.
SQLCAGETINFOLIST SQL/MP System Procedures Table 5-3 lists the SQLCAGETINFOLIST error codes. Table 5-3. SQLCAGETINFOLIST Procedure Error Codes Error Code Description 8510 A required parameter is missing. 8511 The program specified an invalid item code. 8512 The program specified an invalid SQLCA structure. 8513 The program specified an SQLCA structure with a version more recent than the version of the SQLCAGETINFOLIST procedure. 8514 Insufficient buffer space is available.
SQLCAGETINFOLIST SQL/MP System Procedures Table 5-4. SQLCAGETINFOLIST Procedure Item Codes (page 2 of 2) Item Code Size (Bytes) Description 11 2 Maximum length, in bytes, of the system procedure that sets the first error or warning. 12 2 Actual length, in bytes, of the system procedure that sets the first error or warning. 13 (in item code 12) Location of the system procedure that sets the first error or warning. 14 2 Maximum length, in bytes, of the parameter buffer.
SQL/MP System Procedures SQLCAGETINFOLIST In Example 5-1, the SQLCAGETINFOLIST procedure returns the name of the function containing the SQL statement that produced one or more errors or warnings, the name length of the function, and the number of errors or warnings. To avoid coding the maximum length for the function name (err_warn.name_len in the example), call SQLCAGETINFOLIST with item code 7 (the actual length of the function name) and then call SQLCAGETINFOLIST again with a buffer of that size.
SQLCATOBUFFER SQL/MP System Procedures SQLCATOBUFFER The SQLCATOBUFFER procedure writes to a buffer the error or warning messages that NonStop SQL/MP returns to the program. This buffer is a structure declared in variable declarations in the program.
SQL/MP System Procedures SQLCATOBUFFER output_buffer_length is the length of output_buffer in bytes. This length must be: An integer value from output_record_length through 600 A multiple of output_record_length The minimum length recommended is 300 bytes. first_record_number is the ordinal number of the first error record (line) to be written to the output buffer. The procedure discards any error records with a lower number. The default is 1. The count of lines begins with 1.
SQL/MP System Procedures SQLCATOBUFFER errors controls the writing of error messages to the buffer: Y Write all errors. N Write only the first error. B Write all errors but suppress this prefix: ERROR from subsystem [nn] The default is Y. warnings controls the writing of warning messages to the buffer: Y Write all warning messages. N Write all warning messages. B Write all warnings but suppress this prefix: WARNING from subsystem [nn] The default is Y.
SQL/MP System Procedures SQLCATOBUFFER internal_error_loc controls the writing of the system-code location where the first error in the SQLCA occurred: Y Write the location. N Suppress the information. The default is Y. prefix is a string to precede each output line. The default is three asterisks and a space (*** ). prefix_length is the length of the prefix string for each output line. The length must be an integer from 1 to 15. If you include prefix, prefix_length is required.
SQL/MP System Procedures SQLGETCATALOGVERSION In this example, the SQLCAFSCODE procedure writes the error or warning messages to sql_msg_buffer, a buffer declared as 600 bytes: #include ... EXEC SQL INCLUDE SQLCA; ... { char sql_msg_buffer[600]; ... SQLCATOBUFFER ((short *) &sqlca, sql_msg_buffer, 600); } ... SQLGETCATALOGVERSION The SQLGETCATALOGVERSION procedure returns the version of a catalog.
SQL/MP System Procedures SQLGETOBJECTVERSION SQLGETOBJECTVERSION The SQLGETOBJECTVERSION procedure returns the version of an SQL object. SQLGETOBJECTVERSION returns zero after a successful operation or a nonzero value to indicate an error or warning condition. For a description of SQL errors, see the SQL/MP Messages Manual.
SQLSADISPLAY SQL/MP System Procedures #include short SQLGETSYSTEMVERSION ( [ short node_number ] , short *sql_version ); /* i */ /* o */ node_number is the node number of the system for which you are requesting information. The default is the local system. sql_version is the SQL/MP software version for the specified system. For information about versions of NonStop SQL/MP, see the SQL/MP Version Management Guide. Note.
SQLSADISPLAY SQL/MP System Procedures C compiler automatically declares the SQLCA structure if you specify the INCLUDE SQLCA directive. If you omit the SQLCA name, the display does not contain the procedure name and process name of the caller. output_file_number is the output file number. If you omit this value or set it to a negative value, SQLSADISPLAY displays information at your home terminal. NonStop SQL/MP ignores this parameter if detail_params specifies sequential I/O (SIO).
SQLSADISPLAY SQL/MP System Procedures Table 5-5 describes the elements of the SQLSADISPLAY display. Table 5-5. SQLSADISPLAY Procedure Display Elements Element Description \system.$vol.subvol.
6 Explicit Program Compilation This section describes the explicit compilation of a NonStop C program containing embedded SQL statements and directives in the Guardian, HP NonStop Open System Services (OSS), and PC host environments using TNS, TNS/R and TNS/E compilation tools. Note.
Explicit Program Compilation Explicit Program Compilation Table 6-1.
Explicit Program Compilation Explicit Program Compilation Figure 6-1. Explicit SQL Compilation of a C Program on TNS C Source File With Embedded SQL Statements Source File 1 Add required DEFINEs. Run the compiler. 2 C Compiler 3 Run the Binder program (if necessary). Binder Process Accelerator or OCA C Object File With SQL Source Statements (File code 100) Accelerated Object File 4 SQL Compiler Optionally run the Accelerator (TNS/R) or the OCA (TNS/E). 5 Run the SQL compiler.
Explicit Program Compilation Explicit Program Compilation Figure 6-2. Explicit SQL Compilation of a C Program on TNS/R C Source File with Embedded SQL Statements 2 1 Add required DEFINEs. Run the compiler. NMC Compiler 3 Run the linker (if necessary). nld or ld Process C Object File with SQL Source Statements (File Code 700) SQL Compiler 4 Run the SQL compiler.
Developing a C Program in the Guardian Environment Explicit Program Compilation Figure 6-3. Explicit SQL Compilation of a C Program on TNS/E C Source File with Embedded SQL Statements 2 1 Add required DEFINEs. Run the compiler. CCOMP Compiler 3 Run the linker (if necessary). eld Process C Object File with SQL Source Statements (File Code 800) SQL Compiler 4 Run the SQL compiler.
Explicit Program Compilation Using TACL DEFINEs in the Guardian Environment Using TACL DEFINEs in the Guardian Environment You can use TACL DEFINEs during the compilation of a C program containing embedded SQL statements and directives: To use DEFINEs, the TACL process DEFMODE attribute must be ON.
Explicit Program Compilation Specifying the SQL Pragma in the Guardian Environment Specifying the SQL Pragma in the Guardian Environment The SQL pragma indicates to the TNS C , the TNS/R NMC, or the TNS/E CCOMP compilers that a program contains embedded SQL statements or directives and specifies various options for processing the SQL statements or directives. You can specify the SQL pragma either in your primary C source file or as a compiler option in the implicit TACL RUN command for the TNS C compiler.
Explicit Program Compilation Specifying the SQL Pragma in the Guardian Environment SQLMAP directs the C compiler to include an SQL map in the listing. An SQL map contains: Each run-time data unit (RTDU), which is a region of the object file that contains both SQL source statements and object code. Section location table (SLT) index number. Source file name and number. Source file line number. The table is sorted first by RTDU name and then by the SLT index number.
Explicit Program Compilation Running the TNS C Compiler in the Guardian Environment Running the TNS C Compiler in the Guardian Environment To run the TNS C compiler in the Guardian environment, use the TACL RUN command: [ RUN ] C / IN source [, OUT list-file ] [ , run-option ]... / [ object ] [ ; compiler-option [ , compiler-option ]... ] source is the primary source file of the compilation unit.
Explicit Program Compilation Running the TNS/R NMC and TNS/E CCOMP Compiler in the Guardian Environment For more information about TNS C compiler pragmas or preprocessor symbols, see the C/C++ Programmer’s Guide for NonStop Systems. Running the TNS/R NMC and TNS/E CCOMP Compiler in the Guardian Environment To run the TNS/R NMC compiler in the Guardian environment, use the NMC command.
Explicit Program Compilation Binding SQL Program Files in the Guardian Environment pragma is any valid compiler pragma. NonStop SQL/MP supports Tandem floating-point format but not IEEE floatingpoint format. The floating-point format for TNS/R native compilation is Tandem by default. However, for TNS/E native compilation, the floating-point format is IEEE by default.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment Do not bind object files with functions that have the same name and contain embedded SQL statements. The SQL compiler uses the function name as the run-time data unit (RTDU) name. Therefore, when the SQL statement runs, functions with the same name generate ambiguous references that can cause run-time SQL errors.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment compilation to generate a report on the execution plans for DML statements and DEFINEs used by the program. Note. The Accelerator and the Object Code Accelerator (OCA) invalidate an SQL program file. If you plan to run the Accelerator or OCA on a program file, run it before you explicitly SQL compile the program.
Running the SQL Compiler in the Guardian Environment Explicit Program Compilation Entering the SQLCOMP Command To run the SQL compiler in the Guardian environment, enter the SQLCOMP command at the TACL prompt or from an OBEY command file by using this syntax. (For information about running the SQL compiler using the c89 utility in the OSS environment, see Developing a C Program in the OSS Environment on page 6-28.) SQLCOMP / IN object-file [ , OUT [ list-file ] ] [ , run-option] [ , run-option ]...
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment [\system.]external-file \system is an optional system name. external-file is one of these Guardian names: [$volume-name.][subvolume-name.]disk-file-name $device-name $device-number $process-name $spooler-collector-name[.#spooler-location-name] list-file can also be a class SPOOL DEFINE name. If list-file does not exist, the SQL compiler creates it. If list-file already exists, the SQL compiler appends the new output to it.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment [ EXPLAIN ] [ [ PLAN ] ] [ [ DEFINES [ file-name ] [, OBEYFORM ] ] ] [ ] [ NOEXPLAIN ] controls whether the SQL compiler invokes the EXPLAIN utility. EXPLAIN PLAN invokes the EXPLAIN utility to generate an EXPLAIN listing of the optimized execution plans determined by the SQL compiler for the DML statements in the program. EXPLAIN PLAN is the default EXPLAIN option.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment NOOBJECT directs the compiler to perform checking functions and to generate an EXPLAIN listing if you have also specified the EXPLAIN option but to not produce SQL object code. RECOMPILE | NORECOMPILE specifies whether the program should be automatically recompiled, if necessary, during program execution.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment sensitive and SQL valid. The program retains its existing execution plans. If the program was not previously SQL compiled, the operation fails with SQL error 2115. The CATALOG option is the only other SQLCOMP option you can specify with the REGISTERONLY ON option. If you specify an option other than CATALOG, the operation fails with SQL error 2111.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment CHECK INVALID PROGRAM (the default) specifies that the SQL executor should automatically recompile all SQL statements in an invalid program or a program that references changed DEFINEs (if NORECOMPILE is not specified). The SQL executor does not attempt to execute any plans in the program without recompiling them.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment COMPILE { PROGRAM [ STORE SIMILARITY INFO ] } { INVALID PLANS } { INOPERABLE PLANS } determines which SQL statements are compiled during an explicit SQL compilation. You can direct the SQL compiler to use the similarity check to determine if a statement’s execution plan from a previous compilation is operable. The SQL compiler then recompiles only the statements that fail the similarity check.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment If the program has not been previously compiled or if the program does not contain similarity information, the COMPILE INVALID PLANS option directs the SQL compiler to compile all SQL statements in the program. COMPILE INOPERABLE PLANS directs the SQL compiler to explicitly compile these SQL statements: Statements with inoperable plans (invalid plans that fail the similarity check).
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment For more information, see the UPDATE STATISTICS statement in the SQL/MP Reference Manual. Using a PARAM Command You can use a TACL PARAM command to specify the BINSERV program and the swap-file subvolume the SQL compiler uses for explicit SQL compilations. Use the following syntax to enter a PARAM command before you run the SQL compiler.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment SQL Compiler Messages The SQL compiler issues messages for error and warning conditions. An error can prevent successful compilation of a program file, but a warning does not. For a description of all SQL compiler messages, see the SQL/MP Messages Manual. Error Conditions An error condition results from an invalid reference to an SQL object in an SQL statement.
SQL Program File Format Explicit Program Compilation Insufficient information. The SQL compiler does not have enough information to determine the validity of a statement. For example, an unavailable table might not exist, or it might reside on an unavailable remote node. (This situation always occurs for a program that both creates and refers to a table. The table, of course, does not exist when the program is explicitly SQL compiled.) Unresolved DEFINEs.
SQL Compiler Listings Explicit Program Compilation SQL Compiler Listings The SQL compiler writes all SQL statements in the program file to the listing (or OUT) file. If an error or warning occurs, the compiler includes a message after the statement that caused the problem. For DML statements, the compiler also includes the estimated cost of processing the statement, which is a positive integer indicating the relative cost. The larger the integer, the more CPU time and disk access time required.
SQL Compiler Listings Explicit Program Compilation Example 6-1. Sample SQL Compiler Listing (page 2 of 2) 133 134 135 136 *** Statistics: Estimated cost: 1 SQL - SLT Index = 0, Run-Unit INSERT INTO =PARTLOC VALUES (:partloc_rec.loc_code, :partloc_rec.partnum, :partloc_rec.qty_on_hand) = do_add_to_parts 149 INSERT INTO =PARTS 150 VALUES (:parts_rec.partnum, 151 :parts_rec.partdesc, 152 SETSCALE (:parts_rec.price,2) 153 :parts_rec.
SQL Compiler Listings Explicit Program Compilation EXPLAIN PLAN Report The EXPLAIN PLAN report, which applies only to DML statements, indicates the strategy for executing a DML statement and includes optimized access paths, joins, and sorts. The EXPLAIN PLAN report generates a plan for a statement containing subqueries in separate query plans, including one for the statement itself and one for each subquery. This report numbers the query plans in each statement in the order they appear.
Developing a C Program in the OSS Environment Explicit Program Compilation When you issue an OBEY command to run the file shown in the next example, ensure that the DEFINE mode (DEFMODE) is ON, and the DEFINE class is MAP. The INFO DEFINE format uses the same format as the INFO DEFINE command. This example shows an INFO DEFINE format report. In an actual report, each guardianname and define-name would be replaced by the actual name.
Using TACL DEFINEs in the OSS Environment Explicit Program Compilation For information on how to compile and link programs in the OSS environment, see the c89 (1) reference pages online or in the Open Systems Services Shell and Utilities Reference Manual. If you are migrating a program from the TNS environment to the TNS/R or the TNS/E environment, see the TNS/R Native Application Migration Guide and the H-Series Application Migration Guide.
Explicit Program Compilation Using the c89 Utility in the OSS Environment To alter an existing DEFINE, use the add_define utility and specify all DEFINE attributes and their new values. In this situation, the add_define utility essentially adds a new DEFINE with the same name in place of the old DEFINE. Using the c89 Utility in the OSS Environment The c89 utility is the OSS driver for the C and C++ compilation systems.
Explicit Program Compilation Using the c89 Utility in the OSS Environment For more information about the C compiler, see the C/C++ Programmer’s Guide for NonStop Systems. Running the Binder Program, nld, ld, or eld Utility In the TNS environment, use the TNS c89 utility to run the Binder (BIND) program to combine multiple object files into one target object file.
Explicit Program Compilation Using the c89 Utility in the OSS Environment Running the SQL Compiler Use the c89 utility to run the SQL compiler (sqlcomp) to explicitly compile embedded SQL statements in the C object file. The SQL compiler validates the program file for execution and registers the program in the PROGRAMS and USAGES catalog tables using its Guardian ZYQ name.
Using the c89 Utility in the OSS Environment Explicit Program Compilation The input object file also determines the environment where the resulting SQL program file resides after explicit SQL compilation. If the program file resides in the Guardian environment, the SQL compiler uses the OSS path name format. The OSSFILE column in the PROGRAMS table indicates the environment where the file resides (Y=OSS, N=Guardian).
Using the c89 Utility in the OSS Environment Explicit Program Compilation Where mode is: legacy Directs the compiler to connect using the legacy (unencrypted) mode. secure_quiet Directs the compiler to connect using the secure (encrypted) mode. If a secure connection cannot be established, the compiler uses the legacy mode. This option does not generate any diagnostics. secure_warn Directs the compiler to connect using the secure (encrypted) mode.
Explicit Program Compilation Developing a C Program in a PC Host Environment secure_err If the environment variable is set to any of the previous values, these values are used by the compiler to set the connection mode. If the environment variable is set to any other value, the compiler returns an error. If both the -Wsqlconnect option is specified and the environment variable is set, the value specified in the option overrides the value set in the environment variable.
Explicit Program Compilation Static SQL Statements Use of execution-time name resolution to resolve names in execution plans when the SQL statement executes rather than during explicit SQL compilation or at SQL load time CONTROL TABLE controls these performance-related options for accessing tables and views: Selection of access paths, join methods, join sequences, and lock types Selection of block buffering and block splitting algorithms Action to take for locked data or unavailable partit
Explicit Program Compilation Dynamic SQL Statements In this example, the CONTROL EXECUTOR directive specifies parallel evaluation when the program runs the first FETCH statement for the cursor. EXEC SQL CONTROL EXECUTOR PARALLEL EXECUTION ON; EXEC SQL DECLARE list_customers_with_orders CURSOR FOR SELECT CUSTOMER.CUSTNUM, CUSTOMER.CUSTNAME FROM =CUSTOMER, =ORDERS WHERE CUSTOMER.CUSTNUM = ORDERS.CUSTNUM STABLE ACCESS; This example varies the wait time for cursors that access the PARTS table.
Explicit Program Compilation Using Compatible Compilation Tools A dynamic CONTROL directive affects only dynamic SQL statements prepared after the CONTROL directive in execution order, except as noted. Note.
Explicit Program Compilation SQL Program Files The HOSV and its relationship to the C compiler and SQL compiler are described next. For more information about the PFV and PCV, see the SQL/MP Version Management Guide. The C compiler generates the HOSV and stores the value in the object file. If multiple object files are bound together into a single target object file, the HOSV of the target object file is the newest (maximum) HOSV of the individual object files.
Explicit Program Compilation HP NonStop SQL/MP Programming Manual for C—429847-008 6-40 SQL Program Files
7 Program Execution This section describes the execution of a NonStop C program containing embedded SQL statements and directives in the OSS environment. The section provides details about the required access permissions, the TACL DEFINES used, and the steps to run the TACL RUN command. It further explains how to run a program at low PIN and how to determine compatibility with the SQL executor.
Using TACL DEFINEs Program Execution temporarily gain a controlled subset of another user’s privileges. For more information about PROGID programs, see the Security Management Guide. Using TACL DEFINEs Before running an SQL program file, you can specify TACL DEFINE, PARAM, or ASSIGN commands. For information about PARAM and ASSIGN commands, see the TACL Reference Manual.
Entering the TACL RUN Command Program Execution Entering the TACL RUN Command To run an SQL program file from a TACL process, use the TACL RUN (or RUND to invoke the INSPECT program) command. You can enter a RUN command either explicitly or implicitly using this syntax. [ RUN[D] ] program-file [/ [ ,run-option ].../ [ argument ]... RUN runs the program file without invoking the Inspect debugger. RUND runs the program file under the control of the Inspect symbolic debugger.
Running a Program at a Low PIN Program Execution Running a Program at a Low PIN The operating system identifies a process (a running program) by a unique process identification number (PIN). In displays and printouts, a PIN usually appears after the number of the processor where the process is running. For example, the operating system identifies a process in processor 4 with PIN 195 as 4,195. The operating system supports an architectural limit of 65,535 concurrent processes per processor.
Interactive Commands Program Execution If an SQL program was written (or converted) to run at a high PIN, you usually want the program to run at a high PIN because more high PINs are available, and it frees the low PINs for processes that cannot run at a high PIN. In some cases, however, you might need to run a program at a low PIN.
Pathway Environment Program Execution If a C program must run an SQL program programmatically at a low PIN, consider these situations: The C creator program was not written (or converted) to run at a high PIN. The SQL program runs at a low PIN by default, even if it was written (or converted) to run at a high PIN. The C creator program was written (or converted) to run at a high PIN and to create a high PIN process. The SQL program was also written (or converted) to run at a high PIN.
Program Execution Determining Compatibility With the SQL Executor Determining Compatibility With the SQL Executor The PFV of an SQL program indicates the oldest version of the SQL executor that can run the program. During SQL compilation, the SQL compiler writes the PFV in the program’s file label. Then, at run time, the SQL executor checks the PFV, and if the executor version is the same as or later than the PFV, it runs the program. Otherwise, the executor returns an error.
Program Execution Determining Compatibility With the SQL Executor HP NonStop SQL/MP Programming Manual for C—429847-008 7-8
8 Program Invalidation and Automatic SQL Recompilation Program Invalidation A NonStop SQL program file can be valid or invalid. A valid program can run without SQL recompilation using its current execution plans. An invalid program is subject to SQL recompilation (depending on options such as the similarity check) because of changes either to the program file itself or to an SQL object it references.
Program Invalidation and Automatic SQL Recompilation Causes of Program Invalidation Causes of Program Invalidation Program invalidation is caused by certain operations performed on the program file and by DDL operations that alter an SQL object that the program references.
Program Invalidation and Automatic SQL Recompilation Causes of Program Invalidation Changing a collation, which includes dropping and then re-creating the collation, renaming a collation, or changing a DEFINE that points to a collation Executing an UPDATE STATISTICS statement with the RECOMPILE option for a table (RECOMPILE is the default option) Restoring a table, including an underlying table of a protection or shorthand view, using the RESTORE program with the SQLCOMPILE OFF option specified Ch
Program Invalidation and Automatic SQL Recompilation File-Label and Catalog Inconsistencies File-Label and Catalog Inconsistencies Because NonStop SQL/MP records SQL validity in both the program’s file label and in the PROGRAMS catalog table, inconsistencies can occur. An invalid program file is sometimes recorded as valid in the catalog, or a valid program file is recorded as invalid in the catalog. Consider these situations: A program file is not accessible to the SQL catalog manager.
Program Invalidation and Automatic SQL Recompilation Automatic SQL Recompilation ALTER TABLE statement to move or split partitions (including a simple move, one-way split, or two-way split) ALTER TABLE...DROP PARTITION statement ALTER INDEX...DROP PARTITION statement (if the similarity check is enabled for the base table) ALTER INDEX statement to move or split index partitions CREATE INDEX statement UPDATE STATISTICS...
Program Invalidation and Automatic SQL Recompilation Causes of Automatic Recompilation Automatic SQL recompilation performs these functions: Uses the current description of the database to determine the most efficient access path for each referenced database object Maximizes database availability and node autonomy by generating a new execution plan at run time Allows a program to reference database objects that did not exist during explicit SQL compilation Allows a program to use a new set of DE
Program Invalidation and Automatic SQL Recompilation Causes of Automatic Recompilation Changed DEFINEs If the values of the DEFINEs used in the program at SQL load time differ from the values of the DEFINEs used for explicit SQL compilation, the SQL executor forces the automatic recompilation of the program or statement using the new DEFINE values. (For a dynamic SQL statement, the SQL compiler uses the current set of DEFINEs when the PREPARE or EXECUTE IMMEDIATE statement runs.
Causes of Automatic Recompilation Program Invalidation and Automatic SQL Recompilation 4. The SQL executor invokes the SQL compiler to recompile the SELECT statement using the current TAB definition. This recompilation does not modify the PROG program file on disk, it changes only the copy of PROG in memory. Figure 8-1. Timestamp Check Processor (CPU) Memory PROG Execution Plans ... SELECT ... FROM TAB ... TAB Redefinition Timestamp ... TAB File Label Redefinition Timestamp ...
Program Invalidation and Automatic SQL Recompilation Run-Time Recompilation Errors Run-Time Recompilation Errors If automatic SQL recompilation is successful, the SQL statement runs. However, if recompilation fails, the SQL executor returns compilation errors or warnings as follows: Recompilation of a single statement. The SQL executor returns error information to the SQLCODE variable and the SQLCA structure (if declared). Recompilation of an entire program.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations Specifying the CHECK INOPERABLE PLANS Option To direct the SQL executor to use the similarity check for a program, specify the CHECK INOPERABLE PLANS option when you explicitly compile the program as shown in the next example: SQLCOMP /IN sqlprog,OUT $s.#sqlist/ CHECK INOPERABLE PLANS For the complete syntax of the CHECK option, see Section 6, Explicit Program Compilation.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations table-name or view-name is the Guardian name or DEFINE name of the table or protection view. The name cannot be a shorthand view. For the ALTER TABLE statement with the SIMILARITY CHECK clause, table-name cannot be an SQL catalog table. SIMILARITY CHECK ENABLE | DISABLE enables or disables the similarity check for the specified table or protection view. DISABLE is the default.
Preventing Automatic Recompilations Program Invalidation and Automatic SQL Recompilation INOPERABLE PLANS option. Otherwise, the similarity check fails, and automatic recompilation occurs. RUN-TIME-TABLE can be the same table as COMPILE-TIME-TABLE, a modified version of COMPILE-TIME-TABLE, or a different table altogether. Note. The similarity check does not support parallel execution plans. Tables are not considered similar if they are specified in a query that uses a parallel execution plan.
Preventing Automatic Recompilations Program Invalidation and Automatic SQL Recompilation identical attributes. However, if a statement uses a SELECT list containing an asterisk (*), RUN-TIME-TABLE must have the same number of columns as COMPILE-TIME-TABLE. Similarity Rules for Protection Views The similarity check does not support shorthand views. The similarity rules for protection views are: A protection view is never similar to a table or other SQL object.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations Statement Similarity Check Results SELECT columna FROM table1 WHERE EXISTS (SELECT [DISTINCT] * FROM table2) TABLE1 = Pass TABLE2 = Fail INSERT INTO table1 (SELECT [DISTINCT] * FROM table2) TABLE1 = Fail TABLE2 = Fail SELECT table1.*,table2.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations Collations You do not have to explicitly enable the similarity check for a collation, because collations always have the similarity check implicitly enabled. Two collations are similar only if they are equal. NonStop SQL/MP uses the CPRL_COMPAREOBJECTS_ procedure to compare the two collations.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations HP NonStop SQL/MP Programming Manual for C—429847-008 8-16
9 Error and Status Reporting This section describes error and status reporting after the execution of a NonStop SQL statement or directive in a C program. For information about the SQL descriptor area (SQLDA), see Section 10, Dynamic SQL Operations.
Error and Status Reporting Using the INCLUDE STRUCTURES Directive Use this syntax for the INCLUDE STRUCTURES directive: INCLUDE STRUCTURES { structure-spec } structure-spec is: { [ ALL ] VERSION version } { { SQLCA | SQLSA | SQLDA } VERSION version }... { SQLSA VERSION CURRENT { { SQLCA | SQLSA } [ EXTERNAL ] } } ALL VERSION specifies the same version for all three SQL structures (SQLCA, SQLSA, and SQLDA). { SQLCA | SQLSA | SQLDA } VERSION specify the SQLCA, SQLSA, or SQLDA structure, respectively.
Error and Status Reporting Generating Structures With Different Versions { SQLCA | SQLSA } [ EXTERNAL ] specifies that the structures are declared as external, making it possible to share them among modules of an object file. No space is allocated for an external SQLCA or SQLSA declaration. You must specify one occurrence of the formal declaration of SQLCA and SQLSA somewhere in the program using the INCLUDE SQLCA and INCLUDE SQLSA directive without the EXTERNAL option.
Error and Status Reporting Returning Error and Warning Information The C external declaration generated by the INCLUDE SQLCA EXTERNAL directive is: extern struct SQLCA_TYPE sqlca; The C external declaration generated by the INCLUDE SQLSA EXTERNAL directive is: extern struct SQLSA_TYPE sqlsa; Returning Error and Warning Information NonStop SQL/MP provides these methods that you can use to process errors and warnings in a program: Checking the sqlcode variable Using the WHENEVER directive Checking
Checking the sqlcode Variable Error and Status Reporting Example 9-1. Checking the sqlcode Variable EXEC SQL INCLUDE STRUCTURES ALL VERSION 315; /* Variable declarations: */ EXEC SQL BEGIN DECLARE SECTION; struct { short in_partnum; long in_price; char in_partdesc[19]; }in_parts_rec; EXEC SQL END DECLARE SECTION; /* Include the SQLCA for detailed error information */ EXEC SQL INCLUDE SQLCA; /* Include sqlcode for simple error checking */ short sqlcode; ...
Using the WHENEVER Directive Error and Status Reporting Using the WHENEVER Directive The WHENEVER directive specifies an action that a program takes depending on the results of subsequent DML, DCL, and DDL statements. WHENEVER provides tests for these conditions: An error occurred. A warning occurred. No rows were found. When you specify a WHENEVER directive, the C compiler inserts statements that perform run-time checking after an SQL statement using the sqlcode variable.
Error and Status Reporting Using the WHENEVER Directive Determining the Scope of a WHENEVER Directive The order in which WHENEVER directives appear in the listing determines their scope. Some considerations follow: A WHENEVER directive remains in effect until another WHENEVER directive for the same condition appears. To execute a different routine when an error occurs, specify a new WHENEVER directive with a different CALL routine.
Error and Status Reporting Using the WHENEVER Directive Avoiding Infinite Loops To avoid an infinite loop if the error handling code generates errors or warning, you can disable the WHENEVER directive within the error handling procedure. An infinite loop can occur in these situations: The SQLERROR condition runs a statement that generates an error. The SQLWARNING condition runs a statement that generates a warning. The NOT FOUND condition runs a statement that generates a NOT FOUND condition.
Error and Status Reporting Using the WHENEVER Directive Example 9-2. Enabling and Disabling the WHENEVER Directive EXEC SQL WHENEVER SQLERROR CALL :error_handler; void fred(short i, short j, short k) { EXEC SQL SELECT ...; EXEC SQL SELECT ...; EXEC SQL SELECT ...; } void ginger(short i, short j, short k) { EXEC SQL SELECT ...; EXEC SQL SELECT ...; EXEC SQL SELECT ...; } /* reset SQLERROR checking while in error handler */ EXEC SQL WHENEVER SQLERROR; void error_handler(void) { EXEC SQL SELECT...
Using the WHENEVER Directive Error and Status Reporting Example of Using WHENEVER Directives The code in Example 9-3 inserts two column values into the PARTS table and checks for errors and warnings using WHENEVER directives. Within the INSERT statement, the WHENEVER SQLERROR directive is processed first. This directive has a higher precedence, although the WHENEVER SQLWARNING directive is specified first in the source code. Example 9-3.
Using the WHENEVER Directive Error and Status Reporting Example 9-3. Using the WHENEVER Directive (page 2 of 2) int main(void) { /* Begin TMF transaction: */ EXEC SQL BEGIN WORK; in_parts_rec.in_partnum = 4120; in_parts_rec.in_price = 6000000; strcpy (in_parts_rec.in_partdesc,"V8 DISK OPTION"); /* Blank pad in_partdesc. "V8 DISK OPTION" occupies /* positions 0 through 13; start blank padding at /* position 14: for (ix = 14; ix < ; ix++) in_parts_rec.in_partdesc[ix] = ' '; ...
Returning Information From the SQLCA Structure Error and Status Reporting Returning Information From the SQLCA Structure NonStop SQL/MP returns run-time information, including errors and warnings, for the most recently run SQL statement to the SQL communications area (SQLCA). The SQLCA structure can contain up to seven error or warning codes (in any combination) that might be returned by a single SQL statement or directive.
Error and Status Reporting Returning Performance and Statistics Information Returning Performance and Statistics Information NonStop SQL/MP returns performance and statistics information to the SQL statistics area (SQLSA) after the execution of these DML statements: An INSERT, UPDATE, or DELETE statement A SELECT statement with the INTO clause for a host variable An OPEN, CLOSE, or FETCH statement for a cursor operation that has a SELECT statement specified in the DECLARE CURSOR statement For dyn
Using the SQLSA Structure Error and Status Reporting Use the SQLSADISPLAY system procedure to write information from the SQLSA structure to a file or terminal. For information about SQL system procedures, see Section 5, SQL/MP System Procedures. A new statement resets the SQLSA structure fields. If you are using a value elsewhere in your program, you might need to save the value immediately after the statement runs (or declare more than one SQLSA structure).
Error and Status Reporting Using the SQLSA Structure \ Example 9-4.
Error and Status Reporting Using the SQLSA Structure Example 9-5.
Using the SQLSA Structure Error and Status Reporting Table 9-5. SQLSA Structure Fields (page 1 of 2) Field Name Description eye_catcher Identification field. Set eye_catcher to SQLSA_EYE_CATCHER. version Current product version of SQLSA. (Subsequent NonStop SQL/MP PVUs can change this value.) dml Structure for the return of statistics after the execution of a DML statement. num_tables Number of tables accessed by a DML statement; maximum is 16.
Using the SQLSA Structure Error and Status Reporting Table 9-5. SQLSA Structure Fields (page 2 of 2) Field Name Description input_num Number of input parameters in the prepared statement. input_names_len Length of the buffer required to contain names of input parameters. output_num Number of output variables (host variables or SELECT columns) in the prepared statement. output_names_len Length of buffer required to contain names of output variables. name_map_len Reserved.
10 Dynamic SQL Operations Dynamic SQL allows a host-language program to construct, compile, and run all or part of an SQL statement at run time. A dynamic SQL program uses a character host variable as a placeholder for the SQL statement, which is usually unknown or incomplete until run time. To construct the dynamic SQL statement in the host variable, the program usually requires some input from a user at a terminal or workstation.
Dynamic SQL Statements Dynamic SQL Operations Dynamic SQL Statements You can perform most of the same operations using dynamic SQL statements that you can perform with static SQL statements, including DDL, DML, and DCL statements and SQL cursors. Table 10-1 summarizes the dynamic SQL statements that you can use in a C program. Table 10-1. Dynamic SQL Statements Statement Description DESCRIBE INPUT Returns information about input parameters associated with a prepared SQL statement.
Dynamic SQL Operations Dynamic SQL Features Dynamic SQL Features SQLDA Structure, Names Buffer, and Collation Buffer NonStop SQL/MP uses the SQLDA structure to return information about input parameters and output variables in dynamic SQL statements.
Dynamic SQL Operations SQLDA Structure, Names Buffer, and Collation Buffer names-buffer is the SQLDA names buffer; it must follow the conventions for a C identifier. max-name-length is the maximum number of bytes you expect in a parameter name or column name to be returned in a DESCRIBE or DESCRIBE INPUT statement. A qualified column name can be from 1 to 30 bytes long and is in this format: table-name.column-name A parameter name is an SQL identifier with a maximum of 30 bytes.
SQLDA Structure, Names Buffer, and Collation Buffer Dynamic SQL Operations Table 10-2 describes the C identifiers generated by an INCLUDE SQLDA directive. Always use the symbolic names rather than the actual values because the values can change in a new RVU. Table 10-2. C Identifiers Generated by the INCLUDE SQLDA Directive Name Value Description SQLDA_EYE_CATCHER D1 Eye-catcher value. Use this identifier to initialize the eye_catcher field in the SQLDA structure: strncpy(sqlda.
SQLDA Structure, Names Buffer, and Collation Buffer Dynamic SQL Operations Table 10-3. SQLDA Structure Fields (page 2 of 2) Field Name Description data_len The value depends on the data type: Fixed-length character Number of bytes in the string. Variable-length character Maximum number of bytes in the string. Decimal numeric Bits 0:7 specify the decimal scale. Bits 8:15 specify the byte length of the item. Binary numeric Bits 0:7 specify the decimal scale.
Dynamic SQL Operations SQLDA Structure, Names Buffer, and Collation Buffer Example 10-1 shows a version 315 SQLDA structure, names buffer, and collation buffer. For version 1 and 2 SQLDA structures, see Appendix D, Converting C Programs. Example 10-1.
Dynamic SQL Operations SQLDA Structure, Names Buffer, and Collation Buffer Using Declarations for the SQLDA Structure HP provides declarations in the sqlh file that you can use for the SQLDA data_type and precision fields. Use the #include directive to copy these declarations into a C program. Table 10-4 describes the declarations and values for the SQLDA data_type field. Table 10-4.
Dynamic SQL Operations SQLDA Structure, Names Buffer, and Collation Buffer Table 10-4.
Dynamic SQL Operations SQLDA Structure, Names Buffer, and Collation Buffer Table 10-5 describes the declarations and values for the ranges of date-time and INTERVAL data types for the SQLDA data_len field. Table 10-5.
Input Parameters and Output Variables Dynamic SQL Operations Determining Character Set IDs From the precision Field Table 10-6 describes the character-set values that NonStop SQL/MP returns to the SQLDA precision field for CHAR and VARCHAR data types and the character-set declarations in the sqlh file that you can use in a C program: Table 10-6. SQLDA Character-Set IDs Value Declaration Description 0 _SQL_CHARSETID_UNKNOWN A single-byte unknown character set.
Dynamic SQL Operations Input Parameters and Output Variables statement with an input SQLDA structure to get information about the input parameters and obtain pointers to the input values. NonStop SQL/MP returns data to a program through output variables. Output variables are user-specified areas in the program. Output variables can be host variables or individual data buffers to which the program (through the SQLDA structure) contains pointers.
Dynamic SQL Operations Input Parameters and Output Variables A PREPARE statement prepares the statement in exec_statement from the host variable update_statement: PREPARE exec_statement FROM :update_statement; ... To supply values for the UPDATE statement at run time, the program uses the two host variables host_var1 and host_var2: EXECUTE exec_statement USING :host_var1, :host_var2; The value stored in host_var1 is used for both instances of the parameter named ?a.
Input Parameters and Output Variables Dynamic SQL Operations 4. The program enters a loop to prompt the user to supply values for successive execution of the statement: /* Beginning of loop */ /* Prompt the user for a value using the parameter /* name from the names buffer ...
Input Parameters and Output Variables Dynamic SQL Operations Some examples of entries in the names buffer are: Complete Entry Individual Entry Part Description |04|ABCD| |0000000000000100| |ABCD| 2-byte length 4-character string with value = 4 4-character string |06|ABCDE | |0000000000000110| |ABCDE | 2-byte length 4-character string with value = 6 5-character string padded with 1 trailing blank |00| | |0000000000000000| | | 2-byte length with value = 0 Null string Note.
Dynamic SQL Operations Null Values Example 10-2.
Dynamic SQL Operations Null Values If you want all your parameters and output variables to handle null values, your program should access ind_ptr every time it accesses var_ptr. Handling Null Values in Input Parameters A program uses an indicator parameter to indicate that a null value was entered for a parameter.
Dynamic Allocation of Memory Dynamic SQL Operations Null Values and the Names Buffer If your program processes indicator parameters, the names of the indicator parameters are included in the names buffer after DESCRIBE INPUT runs. The ind_ptr field points to the length field for the first indicator parameter name in the names buffer. This behavior is parallel to that of var_ptr after DESCRIBE INPUT or DESCRIBE.
Dynamic SQL Operations Dynamic Allocation of Memory provide names for the SQLDA and names buffer as shown: EXEC SQL INCLUDE SQLDA (dummy_da, 1, dummy_namesbuf, 1); The INCLUDE directive generates the structure templates sqlda_type and sqlvar_type, which you can later use to allocate the memory. You might set up the pointers that will eventually point to that memory.
Using Dynamic SQL Cursors Dynamic SQL Operations Using Dynamic SQL Cursors Dynamic SQL statements use cursors to process SELECT statements in the same way static SQL statements use cursors. The program reads rows from a table, one by one, and sends the column values to output data buffers specified in the program. These paragraphs provide some points to consider when you use cursors.
Dynamic SQL Operations Using Dynamic SQL Cursors Using cursors with a USING DESCRIPTOR Clause If the program is handling parameters entered at run time, use the USING DESCRIPTOR clause with the OPEN statement to provide the parameter values to SQL from an input location in the program’s variable declarations. The input SQLDA describes the input location for each parameter.
Dynamic SQL Operations Using Dynamic SQL Cursors Example 10-3 shows the use of statement and cursor host variables. The program in this example is a server that does repetitive processing using a restricted set of operations. For example, the program might handle a SELECT statement for which the user can enter any of three different WHERE clauses. When the server is started, you might run the PREPARE, DESCRIBE INPUT, DESCRIBE, and DECLARE CURSOR statements once for each possible version of the statement.
Developing a Dynamic SQL Program Dynamic SQL Operations Developing a Dynamic SQL Program Specify the SQL Pragma Specify the SQL pragma to indicate to the SQL compiler that your program contains embedded SQL statements. For information about the SQL pragma, see Section 6, Explicit Program Compilation.
Dynamic SQL Operations Specify the INCLUDE STRUCTURES Directive Specify the INCLUDE STRUCTURES Directive Specify the INCLUDE STRUCTURES directive to indicate the version of SQL structures you plan to use: EXEC SQL INCLUDE STRUCTURES ALL VERSION 315; For more information about the INCLUDE STRUCTURES directive, see Section 9, Error and Status Reporting.
Dynamic SQL Operations Read and Compile the SQL Statement a. Check the data_type field and, if necessary, adjust the data type so that the C program can handle and reset data_len accordingly. b. Allocate an amount of memory equal to the data_len field for the parameter. c. Set the var_ptr field to point to the memory. If you are not allocating memory dynamically, declare a variable for each input parameter value, and put the address of the variable in var_ptr.
Dynamic SQL Operations Process the Output Variables 1. Get the length of the output names buffer from sqlsa.u.prepare.output_names_len. 2. Call the allocate_sqlda function to allocate memory for the output SQLDA and the output names buffer, if needed. 3. Initialize the SQLDA header fields (SQLDA_EYE_CATCHER is defined by the C compiler): *output_sqlda_ptr.eye_catcher = SQLDA_EYE_CATCHER; *output_sqlda_ptr.num_entries = sqlsa.output_num; 4.
Dynamic SQL Operations Perform the Database Request and Display the Values Perform the Database Request and Display the Values Assign a name to the cursor host variable: char cursor_name[11]; strncpy (cursor_name, "c1", 2); If the statement is a SELECT statement, follow these steps: 1. Declare a cursor for the statement: EXEC SQL DECLARE :cursor_name CURSOR FOR :statement_name; 2. Begin a TMF transaction: EXEC SQL BEGIN WORK; 3.
Perform the Database Request and Display the Values Dynamic SQL Operations 3. End the TMF transaction: EXEC SQL COMMIT WORK; 4. Call the free function to deallocate the memory for the SQLDA structures and names buffers and for the values. After the input statement is dynamically compiled with the PREPARE statement, the SQLSA structure contains this information: The input_num field is the number of input parameters in the statement.
Dynamic SQL Operations Allocate Memory for the SQLDA Structures and Names Buffers Allocate Memory for the SQLDA Structures and Names Buffers To allocate memory for the SQLDA structures and names buffers for the input and output variables, use the malloc function. The malloc(n) function allocates a block of memory, n bytes long, and returns the address of that block. The function returns a pointer to void, which is compatible with any pointer type.
Dynamic SQL Operations Allocate Memory for the SQLDA Structures and Names Buffers Example 10-4 shows the allocate_sqlda function, which is also called to allocate the output SQLDA structure. This function initializes the eye_catcher and ind_ptr fields. Example 10-4.
Dynamic SQL Operations Allocate Memory for the SQLDA Structures and Names Buffers Allocate Memory for the Values After the descriptions of input parameters and output variables are specified, the program must allocate space for the actual values. The user might enter these values for input parameters, or the system might return them for columns (output variables). These paragraphs describe how to handle input parameters.
Dynamic SQL Operations Allocate Memory for the SQLDA Structures and Names Buffers Example 10-5 allocates memory for input parameter values. You can use the same code later to allocate memory for output variables. Example 10-5.
Dynamic SQL Operations Allocate and Fill In Output Variables The program can now prompt the user for the input parameter values, set the pointer to the first SQLVAR element in the input SQLDA, and read through the SQLVAR array, storing each value the user enters into the appropriate position in memory.
Allocate and Fill In Output Variables Dynamic SQL Operations EMPNAME CATHERINE WILLIAMS EMPNUM EMPNAME 1890 RICHARD SMITH You can also display the column names as headings (similar to SQLCI) by executing this loop for output_num iterations: 1. Get the length of the column name. 2. Advance to the name. 3. Display the name with some blank space. 4. Advance to the next length field.
Dynamic SQL Operations Allocate and Fill In Output Variables Example 10-6. Displaying Output (page 2 of 2) num_entries = output_sqlda_ptr->num_entries; for (i=0; i < num_entries; i++) { /* /* /* /* /* /* /* /* Position output_namesbuf_ptr to the length prefix in */ the names buffer, store the length in name_len, move */ the pointer past the prefix and onto a name, and store*/ the column name in name_array.
Dynamic SQL Operations Developing a Dynamic SQL Pathway Server Developing a Dynamic SQL Pathway Server Follow these guidelines to develop a C server that interfaces with Pathway and uses dynamic SQL statements. Except for constructing the SQL statement, these steps are not unique to servers using NonStop SQL/MP. You perform these steps in addition to the tasks you would perform for any dynamic SQL program. 1.
Dynamic SQL Operations Dynamic SQL Sample Programs If possible, avoid having fields in your requester or server messages that contain an odd number of bytes. There are some subtle differences in the way SCREEN COBOL and C generate fields in records when fields contain an odd number of bytes. Also, some C functions generate a null byte terminator for character strings. If your server contains a message with null terminators, the message will not match the one sent from the SCREEN COBOL requester.
Dynamic SQL Operations Basic Dynamic SQL Program SET DEFINE CLASS MAP ADD DEFINE =EMPLOYEE, FILE PERSNL.
Basic Dynamic SQL Program Dynamic SQL Operations Example 10-7. Basic Dynamic SQL Program (page 1 of 4) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 /* This program finds the average salary for employees /* according to criteria established by the user.
Dynamic SQL Operations Basic Dynamic SQL Program Example 10-7.
Dynamic SQL Operations Basic Dynamic SQL Program Example 10-7. Basic Dynamic SQL Program (page 3 of 4) 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 /* set DATA_TYPE to long: osqlda.sqlvar[0].data_type = _SQLDT_32BIT_U; */ /* set data_len to 4 bytes; leave scale as 0 in /* upper byte of data_len: osqlda.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-7.
Dynamic SQL Operations Detailed Dynamic SQL Program Defines a buffer to store output variables, with storage for column values of different data types. Defines a buffer to store input parameters, with storage for parameter values of different data types. Prepares the SQL statement and assigns it a statement name. (Note: statement and cursor host variables are not used in this program.
Detailed Dynamic SQL Program Dynamic SQL Operations The commented program listing appears in Example 10-8. Example 10-8.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8.
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8. Detailed Dynamic SQL Program (page 5 of 22) 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 /* display first 38 characters of data */ printf( "%-40s %.
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8. Detailed Dynamic SQL Program (page 8 of 22) 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 len_ptr = (short *) sqlda->sqlvar[i].
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8.
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8. Detailed Dynamic SQL Program (page 12 of 22) 664 { while ( (c = getchar()) != '\n' ) 665 { /* consume the input */ } 666 return (-1); /* array too small */ 667 } 668 } 669 } /* end: while loop */ 670 671 /* out of while loop only at terminator char.
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8. Detailed Dynamic SQL Program (page 14 of 22) 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 sqlda->sqlvar[i].data_len = sqlda->sqlvar[i].
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Detailed Dynamic SQL Program Dynamic SQL Operations Example 10-8.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8. Detailed Dynamic SQL Program (page 19 of 22) 1095 if (in_numvars > 0) 1096 if ( (sda_i = allocate_sqlda( in_numvars )) == NULL ) 1097 { 1098 printf ("\n**** Error: Memory allocation failure for input sqlda.\n"); 1099 printf ( " Process stopped.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8. Detailed Dynamic SQL Program (page 20 of 22) 1155 } 1156 } 1157 1158 /***************************************************************/ 1159 /* Input parameter values from terminal */ 1160 /* Initialize SQLDA var-ptr to point to input data buffer */ 1161 /***************************************************************/ 1162 if ( setupvarbuffers( sda_i ) != 0 ) 1163 { printf( "**** Error: Problem in allocating input param buffers.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8.
Dynamic SQL Operations Detailed Dynamic SQL Program Example 10-8. Detailed Dynamic SQL Program (page 22 of 22) 1272 printf ("\n"); fflush( stdout ); 1273 SQLCADISPLAY ((int *) &sqlca); /* display errors */ 1274 exec sql close C1; /* close cursor */ 1275 exec sql rollback work; 1276 goto enter_input; 1277 } 1278 } /* end: select stmt case */ 1279 else 1280 { /*****************************************************/ 1281 /* Not a SELECT statement.
Dynamic SQL Operations Detailed Dynamic SQL Program HP NonStop SQL/MP Programming Manual for C—429847-008 10-66
11 Character Processing Rules (CPRL) Procedures A C program can call character processing rules (CPRL) procedures to process these collation objects: SQL collation––A NonStop SQL/MP object with file code 941 generated by the CREATE COLLATION statement Collation object––A Guardian file with file code 199 generated by the NLCP compiler Table 11-1 summarizes the CPRL system procedures. These procedures are listed alphabetically. Table 11-1.
cextdecs Header File Character Processing Rules (CPRL) Procedures Table 11-1.
CPRL_ARE_ Character Processing Rules (CPRL) Procedures CPRL_ARE_ The CPRL_ARE_ procedure determines if all characters in a string are in the character class defined by the specified CPRL. You can also call CPRL_ARE_ to scan a string for the first character not in a specific character class.
Character Processing Rules (CPRL) Procedures CPRL_AREALPHAS_ exceptcharaddr is set as follows: If the call is successful, all scanned characters are in the character class defined by the specified SQL collation or collation object, and exceptcharaddr is set as follows: exceptcharaddr = inputstring + inputstringlength If –6 is returned, the first character in inputstring not in the specified character class was found; exceptcharaddr is set to the address of this character.
CPRL_ARENUMERICS_ Character Processing Rules (CPRL) Procedures exceptcharaddr is set as follows: If the call is successful, all the scanned characters are in the ALPHAS character class, and exceptcharaddr is set as follows: exceptcharaddr = address(inputstring) + inputstringlength If –6 is returned, the first character in inputstring that is not in the ALPHAS character class was found; exceptcharaddr is set to the address of this character.
CPRL_COMPARE1ENCODED_ Character Processing Rules (CPRL) Procedures exceptcharaddr is set as follows: If the call is successful, all the scanned characters are numeric characters, and exceptcharaddr is set as follows: exceptcharaddr = address(inputstring) + inputstringlength If –6 is returned, the first nonnumeric character in inputstring was found; exceptcharaddr is set to the address of this character. For other error codes, exceptcharaddr is set to an invalid address.
CPRL_COMPARE_ Character Processing Rules (CPRL) Procedures string1length is the number of bytes in string1 to be compared. string2 is an array containing the second string to be compared. string2 is assumed to be in original (not encoded) form. string2length is the length of string2.
CPRL_COMPAREOBJECTS_ Character Processing Rules (CPRL) Procedures The CPRL_COMPARE_ procedure returns these values: Code 0 Description The operation was successful. –2 The SQL collation or collation object is invalid. –4 The version of the SQL collation or collation object is not supported. string1 is an array containing the first string to be compared. string1length is the length in bytes of string1. string2 is an array containing the second string to be compared.
CPRL_DECODE_ Character Processing Rules (CPRL) Procedures The CPRL_COMPAREOBJECTS_ procedure returns these values: Code 0 Description The operation was successful; the SQL collations or collation objects are equal. –2 The SQL collation or collation object is invalid. –4 The version of the SQL collation or collation object is not supported. –21 The collations in the two specified SQL collations or collation objects do not match.
CPRL_DOWNSHIFT_ Character Processing Rules (CPRL) Procedures encodedstring is an array containing the data to be decoded. encodedstringlength is the number of bytes in encodedstring to be decoded. decodedstring is an array in which CPRL_DECODE_ returns the decoded string. Overlapping encodedstring and decodedstring causes unpredictable results. decodedstringmaxlength specifies the maximum length of decodedstring. decodedstringlength is the number of bytes of encodedstring that were decoded.
CPRL_ENCODE_ Character Processing Rules (CPRL) Procedures inputstring is an array in which CPRL_UPSHIFT_ returns the downshifted string. inputstringlength is the number of bytes to be downshifted in inputstring. shiftedstring is an array in which CPRL_DOWNSHIFT_ returns the downshifted string. The values for inputstring and shiftedstring can be equal, but other values can cause unpredictable results.
CPRL_GETALPHATABLE_ Character Processing Rules (CPRL) Procedures The CPRL_ENCODE_ procedure returns these values: Code 0 Description The operation was successful. –2 The SQL collation or collation object is invalid. –4 The version of the SQL collation or collation object is not supported. –20 The user-specified buffer is not large enough to receive the returned string. decodedstring is an array containing data to be encoded.
Character Processing Rules (CPRL) Procedures CPRL_GETCHARCLASSTABLE_ The CPRL_GETALPHATABLE_ procedure returns these values: Code 0 Description The operation was successful. –2 The SQL collation or collation object is invalid. –4 The version of the SQL collation or collation object is not supported. array is a 256-byte array specified by the user.
CPRL_GETDOWNSHIFTTABLE_ Character Processing Rules (CPRL) Procedures array is a 256-byte array specified by the user. If the call is successful, CPRL_GETCHARCLASSTABLE_ sets each byte in array as follows: 1 The corresponding character code in the SQL collation or collation object is in the character class specified by classname. 0 The corresponding character code in the SQL collation or collation object is not in the specified character class. If the call is unsuccessful, array is not modified.
CPRL_GETFIRST_ Character Processing Rules (CPRL) Procedures cprladdr is a pointer to the SQL collation or collation object. CPRL_GETFIRST_ The CPRL_GETFIRST_ procedure finds the first string of a specified length according to an SQL collation or collation object. This procedure replaces the practice of using a string of hexadecimal zeros to generate the first string of a specified length, which does not work correctly for nonbinary collating sequences.
CPRL_GETLAST_ Character Processing Rules (CPRL) Procedures CPRL_GETLAST_ The CPRL_GETLAST_ procedure finds the last string of a specified length according to an SQL collation or collation object. This procedure replaces the practice of using a string of binary ones to generate the last string of a specified length, which does not work correctly for nonbinary collating sequences.
Character Processing Rules (CPRL) Procedures CPRL_GETNEXTINSEQUENCE_ CPRL_GETNEXTINSEQUENCE_ The CPRL_GETNEXTINSEQUENCE_ procedure finds the next string after a specified string according to an SQL collation or collation object. This procedure replaces the practice of adding 1 to the least significant character of a string to find the next greater string, which does not work correctly for nonbinary collating sequences.
CPRL_GETNUMTABLE_ Character Processing Rules (CPRL) Procedures nextstringlength specifies the number of bytes of nextstring that were scanned. (If CPRL_GETNEXTINSEQUENCE_ is successful, nextstringlength and nextstringmaxlength are equal.) CPRL_GETNEXTINSEQUENCE_ pads nextstring with blanks up to nextstringmaxlength, and nextstringlength is the length of nextstring up to the point where the blank begin (nextstringlength should also be the same as inputstringlength).
CPRL_GETSPECIALTABLE_ Character Processing Rules (CPRL) Procedures CPRL_GETSPECIALTABLE_ The CPRL_GETSPECIALTABLE_ procedure extracts SPECIALS character class information from an SQL collation or collation object, if the SPECIALS character class exists. If the SPECIALS character class does not exist, CPRL_GETSPECIALTABLE_ creates it. In this case, characters are considered SPECIALS if they are not ALPHAS or NUMERICS.
CPRL_GETUPSHIFTTABLE_ Character Processing Rules (CPRL) Procedures CPRL_GETUPSHIFTTABLE_ The CPRL_GETUPSHIFTTABLE_ procedure extracts upshift information from an SQL collation or collation object. #include short CPRL_GETUPSHIFTTABLE_ ( char *array ,long cprladdr ); /* o */ /* i */ The CPRL_GETUPSHIFTTABLE_ procedure returns these values: Code 0 Description The operation was successful. –2 The SQL collation or collation object is invalid.
CPRL_INFO_ Character Processing Rules (CPRL) Procedures The CPRL_INFO_ procedure returns these values: Code 0 Description The operation was successful. –2 The SQL collation or collation object is invalid. –4 The version of the SQL collation or collation object is not supported. –20 The user-specified buffer is not large enough to receive the returned string. cprladdr is a pointer to the SQL collation or collation object. cprlsize is the length in bytes of the SQL collation or collation object.
CPRL_READOBJECT_ Character Processing Rules (CPRL) Procedures CPRL_READOBJECT_ The CPRL_READOBJECT_ procedure reads a collation object from a Guardian disk file (file code 199) into a user-specified buffer. CPRL_READOBJECT_ does not read SQL collations (file code 941) generated by a CREATE COLLATION statement.
CPRL_UPSHIFT_ Character Processing Rules (CPRL) Procedures filename is the Guardian file name in external format containing the collation object. The file code for filename must be 199. filenamelength is the length in bytes of filename. cprladdr is the address of the collation object if 0 (zero) is returned. Otherwise, cprladdr is set to an invalid address.
Character Processing Rules (CPRL) Procedures CPRL_UPSHIFT_ shiftedstringmaxlength specifies the maximum length of shiftedstring; it must be greater than or equal to inputstringlength. shiftedstringlength specifies the length of the upshifted string returned in shiftedstring. cprladdr is a pointer to the SQL collation or collation object.
A SQL/MP Sample Database This appendix describes the NonStop SQL/MP sample database included on the product site update tape (SUT). Many examples in this manual (in addition to other SQL/MP manuals) refer to this sample database. You can create your own copy of the sample database and access it using SQLCI commands or by embedding SQL statement in a host-language program. The sample database includes the PERSNL, SALES, and INVENT subvolumes.
SQL/MP Sample Database Figure A-1 shows the names of columns and tables and the relations between the tables in the sample database. Figure A-1.
SQL/MP Sample Database Example A-1 shows the COPYLIB file containing the record descriptions of the sample database tables. This file was generated using INVOKE directives executed from SQLCI. For example, this INVOKE directive generates the DEPT table: INVOKE PERSNL.DEPT FORMAT C TO COPYLIB (DEPT); For more information about SQLCI, see the SQL/MP Reference Manual. For a description of the SUPPKANJ table, see the ZTSQLMSG.DOCUMENT file. Example A-1.
SQL/MP Sample Database Example A-1. COPYLIB File for Sample Database (page 2 of 3) #pragma SECTION CUSTOMER /* Record Definition for \SYS1.$VOL1.SALES.CUSTOMER /* Definition current at 09:54:03 - 11/10/96 struct customer_type { unsigned short custnum; char custname[19]; char street[23]; char city[15]; char state[13]; char postcode[11]; char credit[3]; }; #pragma SECTION ORDERS /* Record Definition for \SYS1.$VOL1.SALES.
SQL/MP Sample Database Example A-1. COPYLIB File for Sample Database (page 3 of 3) #pragma SECTION PARTSUPP /* Record Definition for \SYS1.$VOL1.INVENT.PARTSUPP */ /* Definition current at 09:54:09 - 11/10/96 */ struct partsupp_type { unsigned short partnum; unsigned short suppnum; long partcost; /* scale is 2 */ unsigned long qty_received; }; /* */ /* Inventory (INVENT) */ /* */ #pragma SECTION SUPPLIER /* Record Definition for \SYS1.$VOL1.INVENT.
SQL/MP Sample Database HP NonStop SQL/MP Programming Manual for C—429847-008 A-6
B Memory Considerations This appendix describes the NonStop SQL internal data structures generated in a C program and the memory considerations for these structures.
Memory Considerations Using the SQLMEM Pragma Using the SQLMEM Pragma For programs that use the large-memory model and are compiled on TNS systems, the SQLMEM pragma specifies where in memory the C compiler should place the SQL internal data structures. Use this syntax for the SQLMEM pragma: SQLMEM { USER | EXT } USER causes the C compiler to allocate the SQL data structures in the user data space, which is the global area addressable with 16 bits.
Estimating Memory Requirements Memory Considerations These structures are shared by all SQL statements and directives in a program: Structure Bytes Description SQLCA 430 Count once if you specify the INCLUDE SQLCA directive. SQLSA 838 or 1790 Count once if you specify the INCLUDE SQLSA directive. A version 330 or later SQLSA structure is 1790 bytes; older SQLSA structures are 838 bytes. Use Table B-2 to estimate the memory used by each SQL statement and directive. Table B-2.
Memory Considerations Avoiding Memory Stack Overflows The system allocates real memory in 16 KB pages. If an SQL statement uses only part of a page, the system allocates the entire page. Therefore, the real memory used by embedded SQL statements can be larger than the figures shown in Table B-2 on page B-3. A program can encounter memory problems in these situations: The program contains a large number of embedded SQL statements.
Memory Considerations Avoiding Memory Stack Overflows If a stack overflow occurs, the executor traps (trap number 3), sends a message to the EMS collector process ($0), and then abends. You can read the EMS event log for this message. For a description of the SQL/MP messages sent to the $0 process, see the SQL/MP Messages Manual. If a call to the file system causes a stack overflow, the SQL executor returns SQL error 8003 and file-system error 22.
Memory Considerations Avoiding Memory Stack Overflows HP NonStop SQL/MP Programming Manual for C—429847-008 B-6
C Maximizing Local Autonomy This appendix describes about the local autonomy in the NonStop SQL/MP network-distributed database. Topics include: Using a Local Partition Using TACL DEFINEs on page C-2 Using Current Statistics on page C-2 Skipping Unavailable Partitions on page C-3 Local autonomy in a network-distributed database ensures that a program can access data on the local node, regardless of the availability of remote SQL objects.
Maximizing Local Autonomy Using TACL DEFINEs The next example uses the concept of maximizing local autonomy. The parts table is a partitioned table that resides on these nodes: \NEWYORK The first partition contains all rows in which PARTS.PARTNUM (the primary key) is less than 5000. \PARIS The second partition contains all rows in which PARTS.PARTNUM is 5000 or greater. An index on the PARTDESC column of table PARTS is named IXPART.
Maximizing Local Autonomy Skipping Unavailable Partitions Skipping Unavailable Partitions Use the SKIP UNAVAILABLE PARTITION option of the CONTROL TABLE directive to cause NonStop SQL/MP to skip a partition that is not available and to open the next available partition that satisfies the search condition of a query. (NonStop SQL/MP also returns warning message 8239 to the SQLCA structure.
Maximizing Local Autonomy Skipping Unavailable Partitions HP NonStop SQL/MP Programming Manual for C—429847-008 C-4
D Converting C Programs A C program developed for NonStop SQL/MP version 1 or version 2 software can run on SQL/MP version 300 (or later) software without any changes to its embedded SQL statements or directives. However, to use new SQL features, you must modify and recompile the program. Note. A D20 (or later) C compiler requires that a C program comply to the ISO/ANSI C standard. For information about converting a program to follow this standard, see the C/C++ Programmer’s Guide.
Generating SQLDA Structures Converting C Programs Table D-1 lists the changes to the SQL data structures and the changes that occurred with each version of NonStop SQL/MP. Table D-1.
Converting C Programs Generating a Version 300 (or Later) SQLDA Structure Generating a Version 300 (or Later) SQLDA Structure To convert a program that generates a version 1 or version 2 SQLDA structure to generate a version 300 (or later) SQLDA structure, follow these steps: 1. If necessary, remove the RELEASE1 or RELEASE2 option from the SQL compiler directive or from the INCLUDE SQLDA directive.
Generating a Version 2 SQLDA Structure Converting C Programs Example D-1 shows a version 2 SQLDA structure. Example D-1.
Generating a Version 2 SQLDA Structure Converting C Programs Table D-2. Version 2 SQLDA Structure Fields (page 2 of 2) Field Name Description data_len data_len depends on the data type: Fixed-length character Number of bytes in the string. Variable-length character Maximum number of bytes in the string. Decimal numeric Bits 0:7 contain the decimal scale. Bits 8:15 contain the byte length of the item. Binary numeric Bits 0:7 contain the decimal scale.
Generating a Version 1 SQLDA Structure Converting C Programs Generating a Version 1 SQLDA Structure If you are converting a program to use the INCLUDE STRUCTURES directive, but you require a version 1 SQLDA structure, follow these steps: 1. If necessary, remove the RELEASE1 option from the SQL compiler directive or the INCLUDE SQLDA directive. The C compiler returns an error if you specify the RELEASE1 option and the INCLUDE STRUCTURES directive. 2.
Generating a Version 1 SQLDA Structure Converting C Programs Table D-3. Version 1 SQLDA Structure Fields (page 2 of 2) Field Name Description data_type Data type of the parameter or output variable. For the table of data type values, see Section 10, Dynamic SQL Operations. data_len data_len depends on the data type: Fixed-length character Number of bytes in the string. Variable-length character Maximum number of bytes in the string. Decimal numeric Bits 0:7 contain the decimal scale.
Planning for Future PVUs Converting C Programs Planning for Future PVUs If you are converting a C program developed for NonStop SQL/MP version 1 or version 2 software to use version 300 (or later) features and to run on NonStop SQL/MP version 300 (or later) software, consider making these changes in the program for compatibility with future NonStop SQL/MP PVUs.
Index A Accelerator effect on SQL validity 8-2 running on object file 1-5, 8-2 running on program file 6-13, 6-14 Access authority DELETE statement 4-23 FETCH statement 4-20 OPEN statement 4-19 SELECT statement 4-21 SQL compilation requirements 6-13 SQL cursor 4-16, 4-18 UPDATE statement 4-22 Access path EXPLAIN utility 6-16, 6-27 local autonomy 8-6 RECOMPILE option 6-17 SQL compiler function 6-13 unavailable 8-8 valid programs 8-1 ADD command, Binder program 6-12 ADD CONSTRAINT statement, program invalidat
C Index Binder program (continued) SQL compiler 6-22 STRIP command 6-12 BINSERV option, PARAM command 6-22 BROWSE ACCESS with SELECT statement 4-4 BUFFERED attribute, similarity check rules 8-12 BUILD command, Binder program 6-12 C C comments, Declare Section 2-2 C compiler determining version 6-36, 9-3 OSS environment 6-30 pragmas RUNNABLE 6-9, 6-12 SQL 3-2, 6-7, 10-23 SQLMEM 3-7, B-2, B-5 SYSTYPE 6-9 XMEM 10-18, B-5 XVAR B-5 RUN command arguments 7-3 WHENEVER directive pseudocode 9-6 C language compile
C Index Character processing rules (CPRL) procedures (continued) CPRL_GETNUMTABLE_ 11-18 CPRL_GETSPECIALTABLE_ 11-19 CPRL_GETUPSHIFTTABLE_ 11-20 CPRL_INFO_ 11-20 CPRL_READOBJECT_ 11-22 CPRL_UPSHIFT_ 11-23 CHAR_AS_ARRAY option, SQL pragma 2-7, 6-7 CHAR_AS_STRING option, SQL pragma 6-7 CHECK clause, SQL compiler 6-18 CHECK option syntax 8-10 CLEARONPURGE attribute, similarity check rules 8-12 CLOSE statement 4-24 CLOSE TABLES option, FREE RESOURCES statement 4-2, 4-24 Closing tables and views 4-2 COBOL as h
D Index CREATE CONSTRAINT statement 4-3 CREATE INDEX statement NO INVALIDATE option 8-3 program invalidation 8-5 Creation timestamp, similarity check rules 8-12 C-series Guardian operating system 7-5 CURRENTDEFINES, SQL compiler option 6-15 Cursor operations CLOSE statement (dynamic) 10-27 CLOSE statement (static) 4-24 DECLARE CURSOR statement 4-18 DELETE statement 4-23 dynamic SQL cursors declaration 10-27 description of 10-20 opening 10-27 FETCH statement 4-15, 4-20 foreign cursors 4-24 guidelines 4-22,
D Index Debugging FORCE option 6-16, 6-23 RUND command 7-3 Decimal data type as host variable 2-11 Declarations, SQLDA SQLDA_EYE_CATCHER 10-5 SQLDA_HEADER_LEN 10-5 SQLDA_NAMESBUF_OVHD_LEN 105 SQLDA_SQLVAR_LEN 10-5 DECLARE CURSOR statement 4-18 Declare Section 1-2, 2-1 dec_to_longlong C routine 2-11 DEFAULTS DEFINE See =_DEFAULTS DEFINE, TACL DEFINE format, EXPLAIN report 6-27 DEFINES option EXPLAIN utility 6-16 SQL compiler 6-16 DEFINEs, TACL automatic recompilation 8-7 INVOKE directive 2-19 local autonom
E Index E Embedded SQL statements advantages 1-1 description 1-3 in C source file 1-3, 3-1 overview 1-1 Empty section in SQL program 6-21 EMS collector process B-5 END DECLARE SECTION directive 1-2, 2-1 Enscribe database memory use by program B-2 utilities protection for SQL objects 8-1 Enscribe I/O 5-6, 5-21 Error and status reporting description 1-5, 9-1 display format control 5-5, 5-15 SQL procedures 5-1 SQLCADISPLAY procedure 5-3 SQLCAFSCODE procedure 5-8 SQLCATOBUFFER procedure 5-14 sqlcode 9-4 SQLMS
G Index FILEINFO command FUP 8-1 SQLCI 8-1 File-system errors SQLCADISPLAY procedure 5-3 SQLCAFSCODE procedure 5-8 SQLCATOBUFFER procedure 5-14 FILE_GETINFOBYNAME_ procedure 5-2 FILE_GETINFOLISTBYNAME_ procedure 5-2 FILE_GETINFOLIST_ procedure 5-2 FILE_GETINFO_ procedure 5-2 First error flag, SQLCAFSCODE procedure 5-8 Fixed-length character data, host variable declaration 2-7 Fixed-point numeric data, host variable declaration 2-11 Flag, SQL object file 8-1 FOR UPDATE OF clause, UPDATE statement 4-22 FORC
J Index Indicator variable aggregate function 9-9 definition 2-1 host variable 2-6 INVOKE directive 2-22 PREFIX and SUFFIX clauses 2-23 ind_ptr field, initializing 10-29 INFO DEFINE format, EXPLAIN report 6-27 info_define OSS utility 6-29 Inoperable execution plan 8-10 Input host variable 2-1 Input parameter, dynamic SQL 10-11, 10-24 Insert operation of timestamp value 4-10 INSERT statement description 4-8 null values 2-17, 4-9 scale for numeric data 2-12, 2-13 Inspect program, RUND command 7-3 INTERVAL d
N Index Modifying data DELETE statement 4-12, 4-23 UPDATE statement 4-10 Moving SQL files, effect on SQL validity 8-2 Multirow operation DELETE statement 4-13 SELECT statement 4-21 UPDATE statement 4-12 N Names buffer determining length 10-7 indicator parameters 10-18 using with parameter 10-14 Naming conventions for host variables 2-2 Native mode C compiler (NMC) Guardian environment 6-10 SQL pragma 6-7 SQLMEM pragma 3-7, B-2 NEWPROCESS procedure B-5 NO INVALIDATE option, CREATE INDEX statement 8-3 NOEX
P Index Open System Services (OSS) Accelerator 6-31 Binder program 6-31 C compilation 6-30 c89 utility 6-30 program development 6-28 shell escape character 6-29 SQL compiler 6-32 TACL DEFINEs 6-29 version considerations 6-28 Open tables, SQL 8-7 Opening tables and views 4-2 Operable execution plan 8-10 Operating system, Guardian SQLCADISPLAY procedure 5-3 SQLCAFSCODE procedure 5-8 SQLCATOBUFFER procedure 5-14 Optimized execution plan EXPLAIN PLAN report 6-27 SQL compiler function 6-13 statistics requireme
Q Index PREPARE statement dynamic SQL compilation 10-25 PREPARE statement, SQL compilation errors 6-23 Primary key in SELECT statement 4-6 Procedures See SQL/MP system procedures process access ID (PAID) DECLARE CURSOR statement 4-18 DELETE statement 4-23 FETCH statement 4-20 OPEN statement 4-19 privileges 7-1 SELECT statement 4-21 SQL cursor requirements 4-16 UPDATE statement 4-22 Process file segment (PFS) 7-2 Process identification numbers (PIN) description 7-4 high and low 7-4 Processes, concurrent 7-
S Index Redefinition timestamp program invalidation 8-5 similarity check rules 8-12 REGISTERONLY clause, SQL compiler 6-17 Relational database management system (RDBMS) 1-1 Release 1, SQL/MP catalog 5-18 object 5-19 system software 5-20 Release 2, SQL/MP catalog 5-18 object 5-19 system software 5-20 RELEASE1 option in SQL pragma 6-8 RELEASE2 option in SQL pragma 6-8 RENAME statement, effect on SQL validity 8-3 REPLY procedure 10-36 Requester, SCREEN COBOL 10-36 RESTORE operation and lost open error 4-3 RE
S Index Sequential I/O (SIO) procedures SQLCADISPLAY 5-3 SQLCATOBUFFER 5-14 SERIALWRITES attribute, similarity check rules 8-12 Set operation automatic recompilation 8-3 DELETE statement 4-13 UPDATE statement 4-12 SETSCALE function 2-5, 2-11 set_define OSS utility 6-29 show_define OSS utility 6-29 Similarity check ALTER TABLE statement 8-13 description 8-9 for collations 8-15 rules for protection views 8-13 rules for tables 8-11 SIMILARITYCHECK column, TABLES table 8-11 SIMILARITYINFO column, PROGRAMS tab
S Index SQLCA structure automatic SQL recompilation errors 8-9 description 9-12 FETCH statement 4-20 INSERT statement 4-8 SQLCADISPLAY procedure 5-3 SQLCAFSCODE procedure 5-8 SQLCAGETINFOLIST procedure 5-9 SQLCATOBUFFER procedure 5-14 UPDATE statement 4-11 SQLCADISPLAY procedure description 5-3 example 1-4 SQLCA structure 9-12 SQLCAFSCODE procedure description 5-8 SQLCA structure 9-12 SQLCAGETINFOLIST procedure description 5-9 SQLCA structure 9-12, D-1 SQLCATOBUFFER procedure description 5-14 SQLCA struct
S Index SQLSA structure (continued) dynamic SQL statement statistics 9-13 fields 9-14, 9-17 INCLUDE SQLSA directive 9-13 SQLSADISPLAY procedure 5-20 static SQL statement statistics 9-13 SQLSADISPLAY procedure 5-20 SQL/MP database overview 1-1 sample A-1 version management 1-7 SQL/MP directives BEGIN DECLARE SECTION 1-2, 2-1 coding 3-1 description 1-3 END DECLARE SECTION 1-2, 2-1 INCLUDE SQLCA 5-4, 9-12 INCLUDE SQLDA 10-3, 10-24 INCLUDE SQLSA 9-13, 10-24 INCLUDE STRUCTURES 9-1, D-1 INVOKE 2-19 placing in s
T Index SQL/MP system procedures description 5-1 SQLCADISPLAY data conversion 2-5 example 1-4 syntax 5-3 SQLCAFSCODE 5-8, 9-12 SQLCAGETINFOLIST 9-12, D-1 SQLCATOBUFFER 9-12 SQLGETCATALOGVERSION 5-18 SQLGETOBJECTVERSION 5-19 SQLGETSYSTEMVERSION 5-19 SQLSADISPLAY 5-20 Stability, SQL cursor 4-17 Stack space requirements B-4 Statements See SQL/MP statements Statistics local autonomy C-2 optimized execution plan 6-23 similarity check rules 8-12 SQL compilation 6-21 SQLCADISPLAY procedure 5-3 SQLCAGETINFOLIST p
U Index Table, SQL (continued) maximizing local autonomy for partitions C-1 open time and automatic SQL recompilation 8-7 SELECT statement 4-4 similarity check rules 8-12 UPDATE statement 4-10 Table, SQL, updating statistics 6-21 Tandem Advanced Command Language (TACL) DEFINEs catalog name 6-6, 6-15 CLASS CATALOG 6-6, 6-15 description 7-2 maximizing local autonomy C-2 OSS environment 6-29 RECOMPILE clause 6-17 SQL compilation 6-15, 6-24 SQL compiler 6-6 SQL program file 7-2 HIGHPIN run option 7-5 PARAM co
V Index UPDATE STATISTICS statement effect on program invalidation 8-3 effect on SQL validity 8-3 error 8204 4-3 maximizing local autonomy C-2 RECOMPILE option, program invalidation 8-5 UPDATE STATISTICS statement, SQL compiler 6-21 UPDATE WHERE CURRENT clause for a cursor 10-21 USAGES table CHECK INOPERABLE PLANS option 8-5 SQL compiler access 6-13 SQL compiler entries 8-1 unrecorded program dependencies 6-23 USER option, SQLMEM pragma B-2 USING DESCRIPTOR clause FETCH statement 10-12 for a cursor 10-21
X Index X XMEM pragma 10-18, B-5 XVAR pragma B-5 Z ZZBInnnn object file 6-9 Special Characters " (double quotes) in SQL statements 3-1 #define C directive 2-1 #include C directive 2-2, 11-2 $0 collector process B-5 $RECEIVE file 10-36 $SYSTEM.SYSTEM.
