manualshive.com logo in svg

HP NonStop SQL/MP, Programming Manual

Share
Download
HP NonStop SQL/MP Programming Manual Download Page 83

Data Retrieval and Modification

HP NonStop SQL/MP Programming Manual for C—429847-008

4-15

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. Declare any host variables you plan to use with the cursor.

2. Name and define the cursor using a DECLARE CURSOR statement. Follow the 

conventions for an SQL identifier for the cursor name. The DECLARE CURSOR 
statement also associates the cursor with a SELECT statement that specifies the 
rows to retrieve.

3. Initialize any host variables you specified in the WHERE clause of the SELECT 

statement in the DECLARE CURSOR statement. 

4. Open the cursor using an OPEN statement. The OPEN statement determines the 

result table and sorts the table if the SELECT statement includes the ORDER BY 
clause. For audited tables or views, the OPEN statement also associates the 
cursor with a TMF transaction. 

5. Retrieve the column values from a row using the FETCH statement. The FETCH 

statement positions the cursor at the next row of the result table and transfers the 
column values defined in the associated SELECT statement to the corresponding 
host variables. The FETCH statement also locks each row according to the access 
specified by the SELECT statement. 

For audited tables or views, the FETCH statement must run within the same TMF 
transaction as the OPEN statement. 

6. Process the column values returned from the current row to host variables. For 

example, you might test a value and then delete or update the row. 

7. After you process the current row, branch back to the FETCH statement and 

retrieve the next row. Continue executing this loop until you have processed all 
rows specified by the associated SELECT statement (and 

sqlcode

 equals 100). 

8. Close the cursor using the CLOSE statement. The CLOSE statement releases the 

result table established by the OPEN statement. (The FREE RESOURCES 
statement also releases the result table.) 

Summary of Contents for NonStop SQL/MP

Page 1: ...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 subse quent H series RVUs G06 00 and all subsequent G series RVUs and D46 00 and all subse quent D series RVUs until otherwise indicated by its replacement publications Part Number Published 429847 008 Augus...

Page 2: ...02 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 ...

Page 3: ...Motif and Motif are trademarks of the Open Software Foundation Inc OSF MAKES NO WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE OSF shall not be liable for errors contained herein or for incidental consequential damages in connection with the furnishing performance or us...

Page 4: ......

Page 5: ...a C Program 1 1 Declaring and Using Host Variables 1 2 Embedding SQL MP Statements and Directives 1 3 Calling SQL MP System Procedures 1 4 Compiling and Executing a Host Language Program 1 5 Processing Errors Warnings and Status Information 1 5 Dynamic SQL 1 6 SQL MP Version Management 1 7 2 Host Variables Specifying a Declare Section 2 1 Coding Host Variable Names 2 2 Using Corresponding SQL and ...

Page 6: ...racter Set With a Host Variable 2 24 Treatment in C Statements 2 25 VARCHAR Data Type 2 25 3 SQL MP Statements and Directives Embedding SQL Statements 3 1 Coding Statements and Directives 3 1 Placing Statements and Directives 3 2 Finding Information 3 3 4 Data Retrieval and Modification Opening and Closing Tables and Views 4 2 Causes of SQL Error 8204 Lost Open Error 4 2 Recovering From SQL Error ...

Page 7: ...URSOR Statement 4 18 OPEN Statement 4 19 FETCH Statement 4 20 Multirow SELECT Statement 4 21 UPDATE Statement 4 22 Multirow DELETE Statement 4 23 CLOSE Statement 4 24 Using Foreign Cursors 4 24 5 SQL MP System Procedures Guardian System Procedures 5 2 cextdecs Header File 5 2 SQL Message File 5 2 SQLCADISPLAY 5 3 SQLCAFSCODE 5 8 SQLCAGETINFOLIST 5 9 SQLCATOBUFFER 5 14 SQLGETCATALOGVERSION 5 18 SQL...

Page 8: ...am in a PC Host Environment 6 33 Using CONTROL Directives 6 34 Static SQL Statements 6 34 Dynamic SQL Statements 6 36 Using Compatible Compilation Tools 6 36 C Compiler 6 36 SQL Compiler 6 36 SQL Program Files 6 37 7 Program Execution Required Access Authority 7 1 Using TACL DEFINEs 7 2 Entering the TACL RUN Command 7 3 Running a Program in the OSS Environment 7 3 Running a Program at a Low PIN 7 ...

Page 9: ...mation 9 13 Declaring the SQLSA Structure 9 13 Using the SQLSA Structure 9 13 10 Dynamic SQL Operations Uses for Dynamic SQL 10 1 Dynamic SQL Statements 10 2 Dynamic SQL Features 10 3 SQLDA Structure Names Buffer and Collation Buffer 10 3 Input Parameters and Output Variables 10 11 Null Values 10 16 Dynamic Allocation of Memory 10 18 Using Dynamic SQL Cursors 10 20 Developing a Dynamic SQL Program...

Page 10: ...n Codes 11 2 CPRL_ARE_ 11 3 CPRL_AREALPHAS_ 11 4 CPRL_ARENUMERICS_ 11 5 CPRL_COMPARE1ENCODED_ 11 6 CPRL_COMPARE_ 11 7 CPRL_COMPAREOBJECTS_ 11 8 CPRL_DECODE_ 11 9 CPRL_DOWNSHIFT_ 11 10 CPRL_ENCODE_ 11 11 CPRL_GETALPHATABLE_ 11 12 CPRL_GETCHARCLASSTABLE_ 11 13 CPRL_GETDOWNSHIFTTABLE_ 11 14 CPRL_GETFIRST_ 11 15 CPRL_GETLAST_ 11 16 CPRL_GETNEXTINSEQUENCE_ 11 17 CPRL_GETNUMTABLE_ 11 18 CPRL_GETSPECIALT...

Page 11: ...ata Types 2 16 Example 2 2 CREATE TABLE Statements 2 20 Example 2 3 Structures Generated by the INVOKE Directive 2 21 Example 4 1 Using a Static SQL Cursor in a C Program 4 14 Example 5 1 Example of the SQLCAGETINFOLIST Procedure 5 13 Example 5 2 Example of the SQLSADISPLAY Display 5 22 Example 6 1 Sample SQL Compiler Listing 6 25 Example 9 1 Checking the sqlcode Variable 9 5 Example 9 2 Enabling ...

Page 12: ...ple Database Relations A 2 Tables Table i NonStop SQL MP Library xvi Table ii Program Development Manuals xix Table iii Guardian Manuals xx Table iv Open System Services OSS Manuals xx Table 1 1 SQL MP Statements and Directives 1 3 Table 2 1 Corresponding SQL and C Character Data Types 2 3 Table 2 2 Corresponding SQL and C Numeric Date Time and INTERVAL Data Types 2 4 Table 2 3 Date Time and INTER...

Page 13: ...lds 9 17 Table 10 1 Dynamic SQL Statements 10 2 Table 10 2 C Identifiers Generated by the INCLUDE SQLDA Directive 10 5 Table 10 3 SQLDA Structure Fields 10 5 Table 10 4 SQLDA Data Type Declarations 10 8 Table 10 5 SQLDA Date Time and INTERVAL Declarations 10 10 Table 10 6 SQLDA Character Set IDs 10 11 Table 11 1 Character Processing Rules CPRL Procedures 11 1 Table B 1 SQL MP Data Structures B 1 T...

Page 14: ...Contents HP NonStop SQL MP Programming Manual for C 429847 008 x ...

Page 15: ...6 00 and all subsequent G series RVUs and D46 00 and all subse quent D series RVUs until otherwise indicated by its replacement publications Document History New and Changed Information Changes to the H06 25 J06 14 manual Added Wsqlconnect compiler option in Wsqlconnect on page 6 33 Added HP_NSK_CONNECT_MODE environment variable option in HP_NSK_CONNECT_MODE on page 6 34 Part Number Published 4298...

Page 16: ...rt 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 Updated the information in Section 6 Explicit Program Compilation with the information from the SQL Supplement for H series RVUs Corrected Example 9 5 on page 9 ...

Page 17: ...n page 4 19 for an OPEN CURSOR statement On page 4 20 for a FETCH statement On page 4 21 for a multirow SELECT statement On page 4 22 for an UPDATE statement with a cursor On page 4 23 for a DELETE statement with a cursor On page 6 21 for an UPDATE STATISTICS statement Added information about compiling NonStop C programs in the PC environment under Developing a C Program in a PC Host Environment o...

Page 18: ...What s New in This Manual HP NonStop SQL MP Programming Manual for C 429847 008 xiv Changes in the G06 26 Manual ...

Page 19: ...is intended for application programmers who are embedding SQL statements and directives in a C program The reader should be familiar with The C programming language NonStop SQL MP terms and concepts as described in the Introduction to NonStop SQL MP The HP NonStop operating system including either the Guardian or HP NonStop Open System Services OSS environment Related Manuals The related manuals t...

Page 20: ... numbers and messages returned by NonStop SQL the SQL file system and FastSort SQL MP Query Guide Describes how to retrieve and modify data in a NonStop SQL MP database and how to analyze and improve query performance SQL MP Version Management Guide Describes the rules governing version management for the NonStop SQL MP software catalogs objects messages programs and data structures SQL MP Install...

Page 21: ... NonStop SQL MP Library SQL MP Glossary Introduction to NonStop SQL MP Guides SQL MP Installation and Management Guide SQL MP Report Writer Guide SQL MP Version Management Guide Reference Manuals SQL MP Messages Manual Programming Manuals VST001 vsd SQL MP Reference Manual SQL MP Programming Manual for COBOL SQL MP Programming Manual for C SQL MP Query Guide ...

Page 22: ...als Guardian Manuals Guardian Procedures Calls Reference Manual Guardian Application Conversion Guide Guardian Programmer s Guide Guardian Procedure Errors and Messages Manual Open System Services OSS Manuals OSS Library Calls Reference Manual OSS Programmer s Guide OSS System Calls Reference Manual OSS Shell and Utilities Reference Manual C C Programmer s Guide Accelerator Manual Binder Manual nl...

Page 23: ...NS object code for the TNS R execution environment CRE Programmer s Guide Describes the Common Run Time Environment CRE and how to write and run mixed language programs CROSSREF Manual Describes the CROSSREF program which produces a cross reference listing of selected identifiers in an application Guardian TNS C Library Calls Reference Manual Describes the C run time library available to TNS and a...

Page 24: ...ardian 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 Guardian Application Conversion Guide Describes how to convert C COBOL Pascal TAL and TACL applications to use the extended features of the HP NonStop operating syste...

Page 25: ...tem The items in the list can be arranged either vertically with aligned braces on each side of the list or horizontally enclosed in a pair of braces and separated by vertical lines For example PAGE S BYTE S MEGABYTE S Vertical Line A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces For example RECOMPILEONDEMAND RECOMPILEALL Ellipsis An ellipsis imme...

Page 26: ...edure calls i and o are used as follows i Input parameter passes data to the procedure o Output parameter returns data to the calling program i o Input and output parameter both passes and returns data An example of the syntax for a procedure call is as follows include cextdecs SQLCAFSCODE short SQLCAFSCODE short sqlca i short first_flg i HP Encourages Your Comments HP encourages your comments con...

Page 27: ... You code a request to access the database using SQL statements The SQL MP optimizer then generates an efficient path to perform your request Insulation against database changes If a database administrator modifies an SQL MP database for example adds a column to a table the change does not affect the logic of your program Use of C statements to process data You can access the database using SQL st...

Page 28: ...ple host_variable1 host_variable2 number and name are host variables EXEC SQL BEGIN DECLARE SECTION int host_variable1 int host variable char host_variable2 19 char host variable struct host_variable_names long number long host variable char name 31 char host variable hv_names EXEC SQL END DECLARE SECTION The C compiler accepts the CHARACTER SET clause in a host variable declaration to associate a...

Page 29: ... STRUCTURES INCLUDE SQLCA INCLUDE SQLDA and INCLUDE SQLSA Data Definition Language DDL ALTER CATALOG ALTER COLLATION ALTER INDEX ALTER PROGRAM ALTER TABLE and ALTER VIEW COMMENT CREATE CATALOG CREATE COLLATION CREATE INDEX CREATE PROGRAM CREATE TABLE and CREATE VIEW DROP HELP TEXT UPDATE STATISTICS Data Manipulation Language DML DECLARE CURSOR OPEN FETCH SELECT INSERT UPDATE DELETE CLOSE Data Stat...

Page 30: ...The cextdecs header file contains source declarations for these procedures that you can include in a program This example calls the SQLCADISPLAY procedure by using all default parameters include cextdecs SQLCADISPLAY SQLCADISPLAY short sqlca Process information from the SQLCA structure For more information see Section 5 SQL MP System Procedures and Section 11 Character Processing Rules CPRL Proced...

Page 31: ... to validate the output SQL program file for execution 5 Run the SQL program file from a terminal using the TACL RUN or RUND command or from a process using a system procedure such as NEWPROCESS or PROCESS_CREATE_ Version 315 or later SQL MP software supports the development of C programs containing embedded SQL statements in both the Guardian and OSS environments For more information see Section ...

Page 32: ...nt that inserts information into the PARTS table from information entered by a user At run time the program prompts a user for information to build the INSERT statement The user enters this information in the INTEXT variable INSERT INTO vol5 sales parts partnum price partdesc VALUES 4120 60000 00 V8 DISK OPTION The program moves the statement to the host variable OPERATION The program has declared...

Page 33: ...r program is compatible For example a version 2 table might use the date time data types or allow null values in a column A version 2 table is compatible with version 2 and version 315 SQL MP software but it is not compatible with version 1 software This manual includes this version information Using compatible versions of the C compiler SQL compiler and SQL executor to compile and run a program U...

Page 34: ...Introduction HP NonStop SQL MP Programming Manual for C 429847 008 1 8 SQL MP Version Management ...

Page 35: ...s a two byte integer variable also declared in a Declare Section that is associated with a host variable An indicator variable indicates whether a column contains or can contain a null value A null value means that a value is either unknown for the row or does not apply to the row A program uses an indicator variable to insert null values into a database or to test a column value for a null value ...

Page 36: ...st Variable Names Use C naming conventions for your host variable and indicator variable names A name can contain from 1 to 31 alphanumeric characters including the underscore _ and must begin with a letter or an underscore To avoid conflicts with HP names do not begin your names with two underscores or end them with one underscore This example uses a Declare Section with host variable names EXEC ...

Page 37: ...t len char val l 1 hostvar Variable Length Character Data Type With CHARACTER SET Clause hostvar VARCHAR l CHARACTER SET charset struct short len char CHARACTER SET charset val l 1 hostvar Variable Length Character Data Type With NATIONAL CHARACTER Clause hostvar NATIONAL CHARACTER VARYING l struct short len char CHARACTER SET defcharset val l 1 hostvar hostvar A host variable name hostvar must fo...

Page 38: ...D unsigned long LARGEINT SIGNED long long FLOAT 1 to 22 bits float REAL float FLOAT 23 to 54 bits double DOUBLE PRECISION double Date Time and INTERVAL Data Types DATETIME TIMESTAMP DATE TIME char l 1 INTERVAL char l 1 l A positive integer that represents the length For DECIMAL l must range from 1 18 s A positive integer that represents the scale of the number An extra byte is generated as a place...

Page 39: ...DISPLAY also returns file system error number 1031 For numeric types NonStop SQL MP converts data between signed and unsigned types and between types with different precisions Use the SETSCALE function to communicate a number s scale to and from a database CAST Function The CAST function allows you to convert a parameter from one data type to another data type character and numeric data types only...

Page 40: ...with a C data type that corresponds to an SQL data type but it cannot be on the left hand side of a define directive Precede hostvar with a colon in an SQL statement INDICATOR is a keyword that must precede indicator_hostvar indicator_hostvar is an indicator variable of type short Precede indicator_hostvar with a colon in an SQL statement For values returned to a host variable indicator_hostvar is...

Page 41: ...es for example strlen and printf require the null terminator Follow these guidelines when you use character arrays as host variables for string literals Declaring a Character Array When you declare a character array as a host variable the C compiler reserves the last byte of the array as a place holder for a null terminator Therefore declare a character array one byte longer than the actual number...

Page 42: ...character column pad the array with blanks before inserting it into the database Otherwise the INSERT statement stores the null terminator in the database and comparison operations fail This example inserts data into the PRODUCTS table The prod_desc array is six bytes long five byes for the column value and one byte for the null terminator void function void EXEC SQL BEGIN DECLARE SECTION char pro...

Page 43: ...ma specifies the CHAR_AS_STRING option For example if a column CUSTNAME is defined as VARCHAR 26 and the SQL pragma specifies the CHAR_AS_STRING option INVOKE generates this structure struct short len char val 27 custname You can refer to the individual data items or the structure name as host variables If you explicitly declare a structure as a host variable for a VARCHAR column rather than using...

Page 44: ...MP Programming Manual for C 429847 008 2 10 Structures 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 ...

Page 45: ...ty of formats for signed decimal strings NonStop SQL MP supports only the embedded leading signed format Therefore always specify the embedded leading signed format when you intend to pass the converted decimal string to NonStop SQL MP For more information about C routines see the C C Programmer s Guide Fixed Point Data Types HP C does not have a data type that maps directly to a fixed point numbe...

Page 46: ...hostvar and specify a scale of 2 If you are retrieving a value from a database using a SELECT statement NonStop SQL MP returns a value that allows for the scale in the host variable For example if your program specifies a scale of 2 in the SELECT statement and 123 45 is stored in the database SQL MP returns 12345 to the host variable The scale is valid only for SQL statements If you use the SETSCA...

Page 47: ...ce SETSCALE host_var2 2 WHERE parts partdesc disk controller The next example retrieves the value in the PARTS PRICE column for a disk controller and stores the value in the host variable host_var3 The value has a scale of 2 EXEC SQL SELECT parts price INTO SETSCALE host_var3 2 FROM parts WHERE parts partdesc disk controller The next example retrieves the part description for the part with a price...

Page 48: ...SECTION struct billing_rec char custnum 4 char start_date 11 char billing_date 11 char time_before_pmt 5 struct billing_rec billings EXEC SQL END DECLARE SECTION strcpy billings billing_date 1996 08 20 strcpy billings time_before_pmt 90 EXEC SQL INSERT INTO billings VALUES 923 DATE 1985 10 15 billing_date TYPE AS DATE time_before_pmt TYPE AS INTERVAL DAY When you invoke a column with a date time D...

Page 49: ...in the table for December 22 1988 is birth_date DATE If the DATEFORMAT clause on the INVOKE directive specifies DEFAULT a column with the range of fields YEAR TO DAY is represented as an 11 character string 10 characters plus a byte for a null character The C compiler creates this structure struct employee_rec char name 18 char birth_date 11 INTERVAL Representation The column definition and repres...

Page 50: ...AR 10 AS DATE BROWSE ACCESS len strlen curspec memset curspec len STMT_LEN len EXEC SQL PREPARE cursor_spec from curspec Declare the dynamic cursor from the prepared statement EXEC SQL DECLARE get_proj CURSOR FOR cursor_spec Initialize the parameter in the WHERE clause printf Enter the most recent start date in the form yyyy mm dd scanf s in_start_date Open the cursor using the value of the dynami...

Page 51: ...T or UPDATE statement a program sets the indicator variable to less than zero 0 for a null value or zero 0 for a nonnull value before executing the statement This statement inserts values into the ODETAIL table The columns UNIT_PRICE and QTY_ORDERED allow null values EXEC SQL INSERT INTO odetail ordernum partnum unit_price qty_ordered VALUES odetail ordernum odetail partnum odetail unit_price odet...

Page 52: ...sor to find rows with null salaries EXEC SQL DECLARE get_null_salary CURSOR FOR SELECT empnum first_name last_name deptnum jobcode salary FROM employee WHERE salary IS NULL EXEC SQL OPEN get_null_salary EXEC SQL FETCH get_null_salary INTO employee_record empnum employee_record first_name employee_record last_name employee_record deptnum employee_record jobcode employee_record salary Test SQLCODE P...

Page 53: ...modify a table or view the INVOKE directive re creates the host variables to correspond to the new table or view when you recompile the program You must however modify a program that refers to a deleted column or must access a new column TACL DEFINEs The INVOKE directive accepts a class MAP DEFINE name for a table or view name but not for a structure tag Program performance The INVOKE directive ma...

Page 54: ..._picx PIC X 10 NOT NULL type_picx_long PIC XXXXXXXXXXXXXXXXXXXX NOT NULL type_float_15 FLOAT 15 NOT NULL type_float_30 FLOAT 30 NOT NULL type_real REAL NOT NULL type_dbl_prec DOUBLE PRECISION NOT NULL type_datetime DATETIME YEAR TO DAY NOT NULL type_date DATE NOT NULL type_time TIME NOT NULL type_timestamp TIMESTAMP NOT NULL type_interval INTERVAL YEAR TO MONTH NOT NULL type_char_null_ok CHAR 10 D...

Page 55: ... 27 96 struct typesc1_type char type_char 11 short type_char_null_i char type_char_null 11 struct short len char val 11 type_varchar short type_varchar_null_i struct short len char val 11 type_varchar_null short type_num4_s unsigned short type_num4_u long type_num9_s scale is 2 unsigned long type_num9_u scale is 2 long long type_num18_s scale is 2 short type_small_s unsigned short type_small_u lon...

Page 56: ...STRUCTURE clause for column names that are 30 or 31 characters The format of the indicator variable name depends on the PREFIX SUFFIX and NULL STRUCTURE clauses 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 TYPESC2 Definition current at 13...

Page 57: ...9 09 22 95 struct ctable_type short beg_znum_end long znum short beg_zchar_end char zchar 16 NULL STRUCTURE Clause The NULL STRUCTURE clause causes INVOKE to generate a structure for a column that contains an indicator variable The NULL STRUCTURE clause assigns the name indicator to all indicator variables in the structure This example shows an INVOKE directive with the NULL STRUCTURE clause as it...

Page 58: ...le byte character set with a host variable To associate a specific character set such as Kanji or KSC5601 with a host variable include the CHARACTER SET clause in the host variable declaration using this syntax CHARACTER SET IS are keywords that must precede the character set name You must specify the CHARACTER SET clause in uppercase letters If you omit the clause the character set defaults to UN...

Page 59: ...ytes and not characters For example this host variable declaration specifies the double byte KANJI character set for emp_name The C assignment statement sets the length emp_name len of the host variable name to 16 characters because the name emp_name val contains 8 double byte characters which are represented as c1c2c3c4c5c6c7c8 EXEC SQL BEGIN DECLARE SECTION struct short len char CHARACTER SET KA...

Page 60: ...Host Variables HP NonStop SQL MP Programming Manual for C 429847 008 2 26 VARCHAR Data Type ...

Page 61: ...dle embedded SQL statements and directives as if they were C statements Follow the same formatting and line continuation conventions that you use for C statements Here are a few specific guidelines to follow when you code embedded SQL statements and directives in a C program Do not nest SQL statements or directives Use only SQL comments in SQL statements and directives SQL comments begin with a do...

Page 62: ... pragma SQL This example uses the SQL pragma as a compiler option C IN csrc OUT s clst NOWAIT cobj SQL After the SQL pragma place other SQL statements and directives in a C source file as described in these paragraphs C Variable Declarations You can use these statements and directives with C variable declarations BEGIN DECLARE SECTION and END DECLARE SECTION directives DECLARE CURSOR statements fo...

Page 63: ...ignates the beginning of host variable declarations END DECLARE SECTION SQLRM SQLPM C Designates the end of host variable declarations INCLUDE STRUCTURES SQLRM SQLPM C Specifies the version of SQL structures generated INCLUDE SQLCA SQLRM SQLPM C Generates the SQLCA structure for run time status and error information INCLUDE SQLDA SQLRM SQLPM C Generates the SQLDA structure to receive information a...

Page 64: ... and views adds and drops table partitions renames tables and partitions of tables adds new columns to tables ALTER VIEW SQLRM Alters security attributes for a view or renames a view COMMENT SQLRM Adds a comment to an object definition CREATE SQLRM Creates a collation constraint catalog index table or view DROP SQLRM Drops a collation constraint catalog index program table or view HELP TEXT SQLRM ...

Page 65: ...or or multiple executors working in parallel CONTROL QUERY SQLRM SQLPM C Specifies whether to optimize query time for the first few rows or for all rows whether to consider a hash join algorithm for executing queries or whether to use execution time name resolution CONTROL TABLE SQLRM SQLPM C Specifies parameters that control locks opens buffers access paths join methods and join sequences on tabl...

Page 66: ...e current TMF transaction and frees resources ROLLBACK WORK SQLRM Backs out the current TMF transaction and frees resources Dynamic SQL Statements DESCRIBE SQLRM SQLPM C Returns information about output variables for a prepared statement DESCRIBE INPUT SQLRM SQLPM C Returns information about input variables for a prepared statement EXECUTE SQLRM SQLPM C Runs a prepared SQL statement EXECUTE IMMEDI...

Page 67: ...tatements or directives SQLMAP generates an SQL map in the listing WHENEVERLIST writes active WHENEVER options to the listing file after each SQL statement is processed RELEASE1 or RELEASE2 specifies the version of the SQL MP features in the program including the SQL data structures and the version of SQL MP software on which the program file can run SQLMEM SQLPM C CPG Controls the placement of SQ...

Page 68: ...SQL MP Statements and Directives HP NonStop SQL MP Programming Manual for C 429847 008 3 8 Finding Information ...

Page 69: ... 1 provides some guidelines for using these statements Table 4 1 SQL MP Statements for Data Retrieval and Modification page 1 of 2 SQL MP Statement Description Single Row SELECT statement Retrieves a single row of data from a table or protection view and places the specified column values in host variables Use when you need to retrieve only a single row SELECT statement with a cursor Retrieves a s...

Page 70: ...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 Use when you do not need to test a column value in a row before you delete the row DELETE statement with a cursor Deletes a set of rows one row at a time from a tabl...

Page 71: ...ROP CONSTRAINT DROP INDEX DROP TABLE or DROP VIEW protection view only UPDATE STATISTICS COPY LOAD PURGEDATA or RESTORE utility operation A disk or network line that goes down and then comes up again can also cause the system to terminate a program s open 3 The program tries to run another SQL statement for the table or view 4 The SQL executor tries to recover as described next However if it canno...

Page 72: ...URSOR statement did not require any input host variables and the first FETCH statement has not yet been run However if the problem occurs on a FETCH statement the SQL executor closes the cursor and returns error 8204 The program must then close and reopen the cursor before executing a subsequent FETCH statement The program might need to abnormally terminate the transaction and restart the cursor o...

Page 73: ...tomer This example sets find_this_customer to customer number 5635 using an assignment statement but in a typical application a user would enter the number EXEC SQL BEGIN DECLARE SECTION struct customer_type host variables short custnum char custname 19 char street 23 char city 15 char state 13 char postcode 11 customer int find_this_customer EXEC SQL END DECLARE SECTION void not_found_function vo...

Page 74: ...employee last_name employee deptnum INTO employee first_name employee last_name employee deptnum FROM persnl employee WHERE employee empnum find_this_employee Multirow SELECT Statement Applications frequently request a group of rows for display on a screen then request the next sequential group of rows If the operation is performed in a Pathway environment a context free server must receive the st...

Page 75: ...ELECT EMPNUM FIRST_NAME LAST_NAME DEPTNUM SALARY FROM EMPLOYEE WHERE LAST_NAME LAST LNAME ORDER BY LAST_NAME When an index on a nonkey column is efficient and available the SQL compiler probably chooses that index The Most Complex Example A more complex problem occurs when the key is composed of multiple columns In this case you should generally use a multivalue predicate for the comparison This t...

Page 76: ...values to the table To run an INSERT statement a process started by the program must have read and write access to the table or view receiving the data and read access to tables or views that you include in a SELECT statement For details see Required Access Authority on page 7 1 NonStop SQL MP returns these values to sqlcode after an INSERT statement If an INSERT statement runs successfully the SQ...

Page 77: ...indicates you attempted to insert a row with an existing key primary or unique alternate Inserting a Null Value This example inserts a row into the EMPLOYEE table and sets the SALARY column to a null value using an indicator variable Variable declarations EXEC SQL BEGIN DECLARE SECTION EXEC SQL INVOKE persnl employee AS emp_tbl struct emp_tbl emp short ind_1 EXEC SQL END DECLARE SECTION Executable...

Page 78: ...AMP EXEC SQL BEGIN WORK Insert value into tablet EXEC SQL INSERT INTO tablet columna VALUES CONVERTTIMESTAMP dtvar EXEC SQL COMMIT WORK UPDATE Statement The UPDATE statement updates the values in one or more columns in a single row or in a set of rows of a table or protection view To update a set of rows one row at a time using a cursor see Using SQL Cursors on page 4 14 To run an UPDATE statement...

Page 79: ...Procedures and Section 9 Error and Status Reporting Updating a Single Row This example updates a single row of the ORDERS table that contains information about the order number specified by update_ordernum In a typical application a user enters the values for update_date and update_ordernum EXEC SQL BEGIN DECLARE SECTION struct orders_type long ordernum long order_date long deliv_date short salesr...

Page 80: ...e department specified by hostvar_old_deptnum moved to the department specified by hostvar_new_deptnum A user enters the values for hostvar_old_deptnum and hostvar_new_deptnum EXEC SQL UPDATE persnl employee SET deptnum hostvar_new_deptnum WHERE deptnum hostvar_old_deptnum Updating Columns With Null Values This example updates the specified SALARY column to a null value using an indicator variable...

Page 81: ...se This example deletes only one row of the EMPLOYEE table because each value in empnum the primary key is unique A user enters the value for the host variable hostvar_empnum EXEC SQL DELETE FROM persnl employee WHERE empnum hostvar_empnum Deleting Multiple Rows If you do not need to check a column value before you delete a row use a single DELETE statement to delete multiple rows in a table This ...

Page 82: ...atic SQL cursor in a C program A cursor operation must run each statement in this specified order All steps are required even if you run the FETCH statement only once to retrieve a single row Example 4 1 Using a Static SQL Cursor in a C Program C source file EXEC SQL BEGIN DECLARE SECTION Declare host variable s EXEC SQL END DECLARE SECTION EXEC SQL DECLARE cursor1 CURSOR FOR SELECT column1 column...

Page 83: ...t determines the result table and sorts the table if the SELECT statement includes the ORDER BY clause For audited tables or views the OPEN statement also associates the cursor with a TMF transaction 5 Retrieve the column values from a row using the FETCH statement The FETCH statement positions the cursor at the next row of the result table and transfers the column values defined in the associated...

Page 84: ...ned with an IN EXCLUSIVE MODE clause in another program the first program must wait to access the data Cursor Position Table 4 2 describes the SQL statements that affect the cursor position in a program The cursor position is similar to the record position in a sequential file Access SQL Objects Read Tables or protection views referred to in the SELECT statement associated with the cursor that is ...

Page 85: ...is case your cursor points to the actual data and has cursor stability Virtual Sequential Block Buffering VSBB The SQL MP optimizer often uses Virtual Sequential Block Buffering VSBB as an access path strategy Conflicting UPDATE DELETE or INSERT statements can invalidate a cursor s buffering for a table Each invalidation forces the next FETCH statement to send a message to the disk process to retr...

Page 86: ...riables you use in the associated SELECT statement before the DECLARE CURSOR statement Host variables must also be within the same scope as all the SQL statements that refer to them Place the DECLARE CURSOR statement in listing order before other SQL statements including the OPEN FETCH INSERT DELETE UPDATE and CLOSE statements that refer to the cursor The DECLARE CURSOR statement must also be with...

Page 87: ... types overflow or truncation errors can occur If a host variable contains old values from the previous execution of the program a subsequent FETCH statement uses these old values as the starting point to retrieve data Therefore the FETCH does not begin at the expected location in the result table The host variables must also be declared within the scope of the OPEN statement Some additional consi...

Page 88: ... resets values in an SQLSA structure immediately before a FETCH statement runs If you use an SQLSA value elsewhere in your program save the value in a variable immediately after the FETCH statement runs To monitor statistics for a cursor declare accumulator variables for the required values and add the SQLSA values to the accumulator variables after each FETCH statement runs For audited tables and...

Page 89: ...underlying tables of shorthand views used in the statement For information about process access see Required Access Authority on page 7 1 All statements that refer to the cursor including the DECLARE CURSOR OPEN FETCH and CLOSE statements must be within the same scope This example uses the get_name_address cursor to return the name and address of all customers within a certain range from the CUSTO...

Page 90: ...pdated This process must also have read access to tables or views specified in subqueries of the search condition For information about process access see Required Access Authority on page 7 1 Do not use a stand alone UPDATE statement to update a row that has been retrieved using a FETCH statement A stand alone UPDATE statement invalidates the cursor s buffering for the table and can substantially...

Page 91: ...om a table the table still exists until it is deleted from the catalog by a DROP TABLE statement 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 more information about process access see Required Access Authority on page 7 1 A DELETE statement must run within t...

Page 92: ... scope of all other SQL statements including the DECLARE CURSOR OPEN FETCH INSERT DELETE and UPDATE statements that refer to the cursor This CLOSE statement closes the list_by_partnum cursor void list_func void EXEC SQL CLOSE list_by_partnum Only an explicit CLOSE statement or a FREE RESOURCES statement closes an open SQL cursor The CLOSE operation releases the resources used by the cursor and fre...

Page 93: ...he cursor declaration can occur to detect whether a statement name has been prepared and to maintain proper association between a procedure and a particular statement name This example declares a cursor list_by_partnum update_inv void EXEC SQL BEGIN DECLARE SECTION struct parts_type define host variables here parts_rec EXEC SQL END DECLARE SECTION EXEC SQL DECLARE list_by_partnum CURSOR FOR SELECT...

Page 94: ...Data Retrieval and Modification HP NonStop SQL MP Programming Manual for C 429847 008 4 26 Using Foreign Cursors describe input and output here exec sql close update_inv list_by_partnum ...

Page 95: ...ocess or operating system errors from the SQLCA structure SQLCAGETINFOLIST Returns to an area in the program a specified subset of the error or warning information in the SQLCA structure SQLCATOBUFFER Returns to a record area in the program the error or warning messages that NonStop SQL MP returns to the SQLCA structure To Return Version Information SQLGETCATALOGVERSION Returns the version of an S...

Page 96: ...r messages informational messages and help text used by SQLCI the SQL compiler and host language programs The default SQL message file is SYSTEM SYSTEM SQLMSG A C program opens and reads the SQL message file when it calls an SQL system procedure that returns error or status information for example SQLCADISPLAY or SQLCATOBUFFER The SQLMSG file contains text in English You can specify a different SQ...

Page 97: ...ed but the old message file is still in effect From a C program call the DEFINEADD or DEFINESETATTR system procedure Your program must add or alter the DEFINE before it calls a system procedure that opens and reads the SQL message file Otherwise your program uses the default message file For more information about system procedures see the Guardian Procedure Calls Reference Manual SQLCADISPLAY The...

Page 98: ... is the file number of the SQL message file SQLMSG is the default file If you specify 1 as the input value the system opens the message file and returns the resulting file number If you specify a value other than 1 the system uses that value as the file number of the message file To improve the performance of multiple calls to the SQLCADISPLAY or the SQLCATOBUFFER procedure specify 1 on the first ...

Page 99: ..._error_loc controls the display of the program name and line number of the SQL statement that received the error The default is Y Y Display all errors N Display only the first error B Display all errors but suppress this prefix ERROR from subsystem nn Y Display all warning messages N Display all warning messages B Display all warnings but suppress this prefix WARNING from subsystem nn Y Display ro...

Page 100: ...ended to each output line The default is a null string suffix_length is the length of the suffix string for each output line The length must be an integer value from 1 to 15 If you include suffix suffix_length is required detail_params determines whether the program uses sequential I O SIO or Enscribe I O to write to the output file The parameter detail_params points to a structure with this layou...

Page 101: ...or text exceeds output_record_length the output is wrapped at word boundaries producing subsequent lines indented 5 spaces The SQLCA can contain a maximum of 7 errors and 180 bytes of text of the actual parameters returned to the program Any information that exceeds these limits is lost SQLCADISPLAY displays a warning message that indicates when information is lost This example calls the SQLCADISP...

Page 102: ... 0 If the SQLCA is full when an error occurs the error is lost sqlca is a pointer to the SQLCA structure The C compiler declares the SQLCA structure if you specify the INCLUDE SQLCA directive first_flg specifies whether the first or the last error is set in the SQLCA The default is the first error This example calls the SQLCAFSCODE procedure include cextdecs SQLCAFSCODE short fserr EXEC SQL INCLUD...

Page 103: ...process DP2 FastSort program SORTPROG process Sequential I O SIO procedures SQLCAGETINFOLIST returns zero after a successful operation or one of the error codes shown in Table 5 3 on page 5 11 if an error occurs sqlca is a pointer to the SQLCA structure The C compiler automatically declares the SQLCA structure if you specify the INCLUDE SQLCA directive Note The SQLCAGETINFOLIST procedure returns e...

Page 104: ... fields item codes 1 through 21 for errors and warnings In addition SQLCA has a table of records item codes 22 through 29 with each record describing one error or warning NonStop SQL MP uses error_index to access this table to determine the error or warning If error_index is omitted NonStop SQL MP returns the first error record names_max is the maximum length your program allows for procedure name...

Page 105: ...ified a params_max parameter less than or equal to zero Table 5 4 SQLCAGETINFOLIST Procedure Item Codes page 1 of 2 Item Code Size Bytes Description 1 2 Version of the SQLCA structure 2 2 Maximum number of errors or warnings the SQLCA can represent 3 2 Actual number of errors or warnings 4 2 Whether there were more errors or warnings than the SQLCA had space to store 0 There were no more errors or...

Page 106: ...e warning numbers are negative 23 2 Subsystem ID First byte is 0 The second byte can be one of these letters S SQL MP component SQL compiler SQL catalog manager SQL executor SQLUTIL process SQLCI or SQLCI2 process F SQL file system D DP2 disk process G NonStop OS R FastSort program SORTPROG process L Load routines I Sequential I O SIO procedures 24 2 Suppress printing this error 0 False nonzero Tr...

Page 107: ...uffer of that size Example 5 1 Example of the SQLCAGETINFOLIST Procedure include cextdecs SQLCAGETINFOLIST define MAX_NAME_LEN 30 define ITEM_LIST_SIZE 3 struct structure to hold error information short name_len short num_errs char name MAX_NAME_LEN err_warn EXEC SQL INCLUDE SQLCA include SQLCA structure short error_code variable to hold return code Declare and initialize the item list array short...

Page 108: ...cedure is similar to the SQLCADISPLAY procedure that writes error information to a file or terminal sqlca is a pointer to the SQLCA structure The C compiler automatically declares the SQLCA structure when you specify the INCLUDE SQLCA directive output_buffer is the name of the buffer where SQLCATOBUFFER writes the error information include cextdecs SQLCATOBUFFER void SQLCATOBUFFER short sqlca i ch...

Page 109: ...teger value from 60 through 600 The default is 79 bytes The procedure pads each line with spaces and adds suffix and prefix strings if the call specifies them sql_msg_file_number is the file number of the SQL message file SQLMSG is the default file If you specify 1 as an input value the system opens the message file and returns the resulting file number If you specify a value other than 1 the syst...

Page 110: ...d line number of the SQL statement that received the error The default is Y Y Write all errors N Write only the first error B Write all errors but suppress this prefix ERROR from subsystem nn Y Write all warning messages N Write all warning messages B Write all warnings but suppress this prefix WARNING from subsystem nn Y Write row and cost statistics if the value returned to the SQLCA in the ROW ...

Page 111: ...Stop SQL MP returns errors as negative numbers and warnings as positive numbers Therefore you might need to modify your program accordingly If there is no text for an error number NonStop SQL MP displays this message No error text found If you receive this message the version of the SQL message file might be invalid To determine the version of the SQL message file use the SQLCI ENV command and che...

Page 112: ...talog_name is the fully qualified name of the catalog for which you are requesting information The name must be Left justified and padded with spaces on the right A maximum of 26 characters If you omit catalog_name SQLGETCATALOGVERSION uses the default catalog sql_version is the version of the catalog For information about versions of NonStop SQL MP see the SQL MP Version Management Guide include ...

Page 113: ...version of SQL MP file system and disk process components running on a system For a specific node assume that all SQL MP components are of the same PVU SQLGETSYSTEMVERSION 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 If you request the version number for a remote node SQLGETSYST...

Page 114: ...RE statement sqlsa is a pointer to the SQLSA structure The C compiler automatically declares the SQLSA structure when you specify the INCLUDE SQLSA directive sqlca is a pointer to the SQLCA structure The SQLCA structure contains the procedure name and line number of the SQL statement that sets the SQLSA structure The include cextdecs SQLGETSYSTEMVERSION short SQLGETSYSTEMVERSION short node_number ...

Page 115: ... SIO or Enscribe I O to write to the output file The parameter detail_params points to a structure with this layout struct detail_params_type char sio short out_fcb_1 short out_fcb_2 detail_params sio specifies whether sequential I O SIO is used outfcb1 specifies the first output file control block if SIO is enabled outfcb2 specifies the second output file control block if SIO is enabled To use ou...

Page 116: ...Records Accessed The number of records accessed in each table includes records examined by the disk process file system and SQL MP executor Records Used The number of records actually used by the statement Disc Reads The number of disk reads caused by accessing this table Message Count The number of messages sent to execute operations on this table Message Bytes The number of message bytes sent to...

Page 117: ...28 Developing a C Program in a PC Host Environment on page 6 33 Using CONTROL Directives on page 6 34 Using Compatible Compilation Tools on page 6 36 Explicit Program Compilation Table 6 1 and Table 6 2 list the C compilers their compilation mode and where you can run them Note This section contains information about some of the following G series development tools which are not available on H ser...

Page 118: ...are not supported on D series systems Instead use the native C cross compiler of the Tandem Development Suite TDS For more information see the C C Programmer s Guide for NonStop Systems NonStop SQL MP is only compatible with c89 and not c99 Compilation Mode NonStop System Where You Can Execute the Embedded SQL Program TNS D series or G series TNS R H series TNS E TNS R native D series or G series ...

Page 119: ...INEs 1 In an OSSenvironment on a TNS Rsystem Steps 2 through 5 can be invoked with the c89 utility Binder Process SQL Compiler Accelerated Object File SQL ProgramFile Accelerator or OCA CCompiler Run the compiler 2 Run the SQL compiler 5 Valid SQL ProgramFile Ready for Execution on TNS TNS R or TNS ESystems Run the Binder program if necessary 3 Optionally run the Accelerator TNS R or the OCA TNS E...

Page 120: ... Add required DEFINEs 1 In an OSSenvironment Steps 2 through 4 can be invoked with the c89 utility nld or ld Process SQL Compiler SQL ProgramFile NMCCompiler Run the compiler 2 Run the SQL compiler 4 Valid SQL ProgramFile Ready for Execution on TNS RSystems Only Run the linker if necessary 3 CObject File with SQL Source Statements File Code 700 CSource File with Embedded SQL Statements ...

Page 121: ...L Recompilation Developing a C Program in the Guardian Environment In the Guardian environment you can develop a C program to run in either the Guardian or OSS environment VST003E vsd Add required DEFINEs 1 In an OSSenvironment on a TNS Esystem Steps 2 through 4 can be invoked with the c89 utility SQL Compiler SQL ProgramFile CCOMPCompiler Run the compiler 2 Run the SQL compiler 4 Valid SQL Progra...

Page 122: ...MAP FILE persnl xempname 22 ADD DEFINE collate1 CLASS MAP FILE collate1 If you specify a DEFINE name in an SQL statement that is not in your current set of DEFINEs the SQL compiler issues a warning and leaves the statement uncompiled in the program file When you run the program the SQL executor automatically tries to recompile the SQL statement If the DEFINE is still not available at run time the ...

Page 123: ...clist cobj SQL To specify the SQL pragma in your C source file use WHENEVERLIST NOWHENEVERLIST controls the writing of active WHENEVER options to the listing file after each SQL statement is processed WHENEVERLIST causes the options to be written NOWHENEVERLIST the default causes the options not to be written CHAR_AS_ARRAY CHAR_AS_STRING specifies whether the INVOKE directive generates character t...

Page 124: ... including the SQL data structures and the version of SQL MP software on which the program file can run RELEASE1 specifies version 1 features A program that uses the RELEASE1 option is compatible with SQL MP version 1 2 or 300 or later software RELEASE2 the default specifies version 2 features A program that uses the RELEASE2 option is compatible with SQL MP version 2 or version 300 or later softw...

Page 125: ...compiler listing The default is the default output file usually your home terminal list file can also be a class SPOOL DEFINE name run option is one or more TACL RUN command options separated by commas as described in the TACL Reference Manual object specifies the object file to which the TNS C compiler writes the compilation unit If you omit object the compiler creates a file named OBJECT in your...

Page 126: ... When specified listing is usually a spooler location If you omit the OUT option the compiler writes the listing to your current default output file If the file already exists the compiler tries to delete the file and then continue run option is one or more TACL RUN command options separated by commas as described in the TACL Reference Manual object specifies the file to which the native C compile...

Page 127: ...quivalent to using the define preprocessor directive in source text undefine identifier deletes identifier as a preprocessor symbol The undefine option is equivalent to using the undef preprocessor directive in source text Binding SQL Program Files in the Guardian Environment The Binder program is a tool you can use to read link modify and build executable object files in the TNS environment You c...

Page 128: ...ject file For a multiple module program use the Binder program or linker to combine the object code from each module into a single executable object file To run the Binder program enter the BIND command at the TACL prompt The Binder program displays its banner and prompt an at sign In the next example the Binder commands combine the cobj1 and cobj2 files into an executable object file named progfi...

Page 129: ...QL data types Expands views Checks references in catalogs for SQL object names to verify their existence and to read their descriptions then evaluates the object type and characteristics for each reference Determines an optimized execution plan by analyzing the DML statements to determine the best access paths and join sort and blocking strategies Estimates the execution costs for DML statements b...

Page 130: ...piler You must run the SQL compiler on the same system where object file exists If you do not specify a system or volume name the SQL compiler uses your current default values list file identifies the destination where the SQL compiler directs the listing list file can be a disk file name process name including a spooler collector or a device name including a terminal magnetic tape unit or line pr...

Page 131: ...you partially qualify the catalog name the system expands the name by using your current default values You can also specify a class CATALOG DEFINE for catalog name The catalog object file and SQL compiler must reside on the same system If the program was previously SQL compiled and recorded in a different catalog catalog name overrides the catalog name stored in the program file The program is dr...

Page 132: ... compiler to write the DEFINE listing in an OBEY command file format so that you can use an OBEY command to later set the DEFINEs If you omit OBEYFORM the SQL compiler uses the format displayed by the TACL INFO DEFINE command If you omit DEFINES the SQL compiler does not generate a DEFINE listing NOEXPLAIN the default suppresses the EXPLAIN utility FORCE NOFORCE controls how syntax errors affect S...

Page 133: ...pile the program If any of the conditions described under the RECOMPILE option occur during execution an error is generated and the program is subject to explicit SQL recompilation for validation RECOMPILEONDEMAND RECOMPILEALL specifies whether the SQL executor should recompile an entire invalid program or only those SQL statements that require recompilation and are actually run If you specify NOR...

Page 134: ...an be executed without being registered in an SQL catalog If you specify the CATALOG option with the NOREGISTER ON option the compilation fails with SQL error 2116 If the program is already registered in a catalog the compilation fails with SQL error 2110 If the program was modified by the Binder program after it was SQL compiled the operation fails with SQL error 2103 NOREGISTER OFF the default d...

Page 135: ... store similarity information in the program file even if the similarity check is not enabled for the table or protection view CHECK INOPERABLE PLANS specifies that the SQL executor should perform the similarity check on each SQL object in an SQL statement if the similarity check is enabled for referenced tables and protection views and either of these conditions occur The statement is invalid Inv...

Page 136: ...e the SQL compiler sets the PCV to 310 If you omit the COMPILE option or specify the COMPILE PROGRAM option the default the SQL compiler sets the PCV to 1 unless the program includes other version 310 features COMPILE PROGRAM directs the SQL compiler to explicitly compile all SQL statements in the program If you include the STORE SIMILARITY INFO clause the SQL compiler also stores similarity infor...

Page 137: ...INOPERABLE PLANS option directs the SQL compiler to compile all SQL statements in the program Using Current Statistics For the SQL compiler to generate the best execution plan it must have the current statistics for referenced tables NonStop SQL MP does not automatically update these statistics A program must run the UPDATE STATISTICS statement to generate current statistics in a catalog To run th...

Page 138: ...SERV guardian name specifies the BINSERV program file the SQL compiler uses during compilation These criteria apply to guardian name If guardian name designates a system other than the system on which the SQL compiler is running the SQL compiler ignores the BINSERV parameter If guardian name does not include a volume or subvolume name the SQL compiler uses current default values The default value ...

Page 139: ...ements are not compiled during explicit SQL compilation Errors in these statements are returned at run time after dynamic compilation by a PREPARE or EXECUTE IMMEDIATE statement Warning Conditions A warning condition usually occurs when the SQL compiler has insufficient information available If a warning occurs the SQL compiler still records the program file in the catalog validates the file for e...

Page 140: ...gram file to the SQL compiler can be a C object file a file generated by the Binder program a file generated by the Accelerator the OCA or a file previously compiled by the SQL compiler Figure 6 4 shows the format of an SQL program file Figure 6 4 SQL MP Program File Format Accelerated Object Code TNS R AXCEL or TNS E OCA File Label Information Generated by the SQL Compiler C Object Code Execution...

Page 141: ...SQL Compiler T9095D42 03JUN96 COPYRIGHT TANDEM COMPUTERS INCORPORATED 1987 1996 DATE TIME 10 04 96 16 38 23 Options NOFORCE OBJECT CURRENTDEFINES RECOMPILE RECOMPILEALL REGISTERON NOEXPLAIN COMPILE PROGRAM SQL PROGRAM FILE NEWYORK DISK1 SQLPROG SQLC SQL PROGRAM CATALOG NEWYORK DATA1 INVENT SQL DEFAULT CATALOG NEWYORK DATA1 INVENT SQL Source File NEWYORK DISK1 SQLPROG SQLC SQL SLT Index 0 Run Unit ...

Page 142: ...imated cost 1 SQL SLT Index 0 Run Unit 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 qty_available Statistics Estimated cost 2 BINDER OBJECT FILE BINDER T9621D30 17JUL95 SYSTEM NEWYORK Copyright Tandem Computers Incorporated 1982 1995 Object file NEWYORK DISK1 SQLPROG SQLC TIMESTAMP 1996 10 04 16 34 56 PAGE 1 ...

Page 143: ... and writes the output to the spooler location s explain SQLCOMP IN sqlprog OUT s sqlist CATALOG disk2 sales EXPLAIN PLAN EXPLAIN DEFINES Report The EXPLAIN DEFINES report indicates the mapping of DEFINE names used in SQL statements with this information Each DEFINE name and its associated Guardian name used for SQL tables and views The default volume and default catalog used by the SQLCOMP proces...

Page 144: ...ncluded in the SQLCOMP command because it is stored in the program file The NOOBJECT option suppresses the generation of a program file so the SQL compiler does not register the program file in a catalog SQLCOMP IN sqlprog OUT s explain NOOBJECT EXPLAIN PLAN DEFINES setdefs OBEYFORM For more information about the EXPLAIN utility including detailed examples see the SQL MP Query Guide Developing a C...

Page 145: ...ine utility implicitly sets the DEFMODE attribute to ON before it creates the new DEFINE Before you run the C compiler using the c89 utility add these DEFINEs Class MAP DEFINEs specified in INVOKE directives Class MAP or class CATALOG DEFINEs specified in SQL statements If you specify a class CATALOG DEFINE for the SQLCOMP CATALOG option when you run the SQL compiler using the c89 utility add the ...

Page 146: ... Running the OSS C Compiler Run the OSS C compiler using the c89 utility to compile the source file or files that contain the embedded SQL statements The C compiler generates an object file that contains C object code and SQL source statements This c89 command invokes the C compiler to compile the pgm1 source file into the pgm1o object file c89 c pgm1 NonStop SQL MP supports Tandem floating point ...

Page 147: ...files with functions that have the same name and contain embedded SQL statements The SQL compiler uses the function name as the RTDU name Therefore when the SQL statement runs functions with the same name generate ambiguous references which can generate SQL run time errors Running the Accelerator for Cross Platforms TNS object files can be accelerated to take advantage of features on the RISC arch...

Page 148: ...1 reference pages Using the TNS c89 Utility With the D40 and later D series product versions the TNS c89 utility also uses different flags from earlier versions of the c89 utility For example to compile an SQL program file without invoking the Binder program specify the Wsqlcomp flag to run the SQL compiler by using the catalog sql sqlcat c89 Wnobind Wsql catalog sql sqlcat sqlprog c For more info...

Page 149: ...terminal To SQL compile a C program using the c89 utility use SQL compiler version 315 or later After SQL compilation an OSS program file has a PFV and a PCV of 315 or later To use the EXPLAIN utility you must also specify the Wverbose flag c89 Wsql Wverbose Wsqlcomp explain plan Wnolink sqlprog c To compile embedded SQL MP for TNS E the value specified for sqlhost must be an H series TNS E system...

Page 150: ...ockets Layer SSL or both SQL MP compilations use both SSL and SSH For more information about NSK security see the Security Management Guide HP_NSK_CONNECT_MODE This environment variable is introduced in H06 25 J06 07 RVU and can be set to any of the following values legacy secure_quiet secure_warn legacy Directs the compiler to connect using the legacy unencrypted mode secure_quiet Directs the com...

Page 151: ...esulting object files can be executed on NonStop TNS R and TNS E native systems ETK is a GUI based extension package to the Visual Studio NET product You can use ETK to edit compile build and deploy applications written in C and COBOL with embedded SQL MP You do not have to install Visual Studio NET or ETK to use the command line interface For more information see the online help in ETK or the fil...

Page 152: ...cify the SQLMAP option in the SQL directive A static CONTROL directive affects all subsequent static SQL statements that follow in listing order regardless of execution order as follows Global Scope In the global scope of a C program that is outside of any functions a static CONTROL directive affects only SQL statements that follow in listing order and are not within a function It does not affect ...

Page 153: ...SQL DECLARE CURSOR cursor1 FOR SELECT partnum partdesc price FROM sales parts WHERE partnum min_partnum AND partnum max_partnum ORDER BY partnum Short wait EXEC SQL CONTROL TABLE sales parts TIMEOUT 1 SECOND EXEC SQL DECLARE CURSOR cursor2 FOR SELECT partnum partdesc price FROM sales parts WHERE partnum min_partnum AND partnum max_partnum ORDER BY partnum Infinite wait EXEC SQL CONTROL TABLE sales...

Page 154: ...ample a version 310 C compiler listing includes this line Host Object SQL Version 310 SQL Compiler The SQL compiler SQLCOMP must have the same version as or later than the HOSV of the SQL program file To determine the version of the SQL compiler use the GET VERSION OF SYSTEM statement All SQL MP components on the NonStop operating system including the SQL compiler must have the same version SQL Pr...

Page 155: ... VERSION OF PROGRAM statement with the HOST OBJECT option You can run this statement from SQLCI or in a C program This GET VERSION OF PROGRAM statement is run from SQLCI GET HOST OBJECT VERSION OF PROGRAM sqlprog VERSION 310 SQL operation complete To embed a static GET VERSION OF PROGRAM statement in a C program you must include the INTO clause with a host variable This statement returns the HOSV ...

Page 156: ...Explicit Program Compilation HP NonStop SQL MP Programming Manual for C 429847 008 6 40 SQL Program Files ...

Page 157: ...ogs in which tables or views used by the program are registered for SQL statements that require automatic SQL recompilation For an embedded SQL statement static or dynamic to access and operate on a database object such as a table or view the process started by the program must have specific privileges associated with it The privileges for both the process access ID PAID and the group list are eva...

Page 158: ... a class CATALOG DEFINE for a catalog and a class MAP DEFINE for an object You enable and disable DEFINEs using the DEFMODE attribute If DEFMODE is ON when a program begins execution the system propagates the current set of DEFINEs from the process file segment PFS of your TACL process to the new process If DEFMODE is OFF the system propagates only the _DEFAULTS DEFINE to the new process To displa...

Page 159: ...scribed in the TACL Reference Manual argument is an argument as described in the C C Programmer s Guide Separate arguments in a list using spaces not commas For example this RUN command runs the program file named sqlprog and specifies the NAME OUT and NOWAIT run options RUN sqlprog NAME sqlrun OUT s sqlist NOWAIT This RUND command runs the program file named disk sql sqlprog under the control of ...

Page 160: ...mit of 65 535 concurrent processes per processor The actual number of concurrent processes depends on the available system resources for example virtual memory and the values specified during system generation A PIN has these divisions A low PIN ranges from 0 through 254 A high PIN ranges from 256 through 65 535 or the maximum number PIN 255 is reserved Figure 7 1 shows various processes running i...

Page 161: ...ocess In a Pathway environment you can also force a server process to run at a low PIN Interactive Commands To interactively force an SQL program to run at a low PIN use either of these methods Before you run the SQL program set the HIGHPIN object file attribute to OFF in the SQL program file using the Binder CHANGE command CHANGE HIGHPIN OFF IN sqlprog To change an object file attribute in a prog...

Page 162: ...n a Pathway environment an SQL program running as a server process can run at an available high PIN if these conditions are met The SQL program was written or converted to run at a high PIN The HIGHPIN server attribute for the SQL program in the Pathway configuration file is ON The HIGHPIN object file attribute in the SQL program file is ON A high PIN is available when the server runs To force an ...

Page 163: ...K system to the host variable named hv_sys_version If you do not specify a system name the statement returns the version of the local system To determine the PFV of an SQL program use a FUP INFO or SQLCI FILEINFO command with the DETAIL option For programs registered in version 300 or later catalogs you can also query the PROGRAMS PROGRAMFORMATVERSION column However for version 300 or later SQL MP...

Page 164: ...Program Execution HP NonStop SQL MP Programming Manual for C 429847 008 7 8 Determining Compatibility With the SQL Executor ...

Page 165: ...ompiler Validation Functions The SQL compiler validates an SQL program file after a successful explicit SQL compilation or after errors occurred during a compilation with the FORCE option specified During explicit compilation the SQL compiler performs these functions related to program validation Sets the VALID and SENSITIVE flags in the program s file label Records the timestamp of the SQL compil...

Page 166: ...ew file is invalid Binding a program file If you explicitly bind a program file using the Binder program the original file is unaffected but the resulting target file is invalid Restoring a program file If you restore a program file using the RESTORE program without specifying the SQLCOMPILE ON option the restored program becomes invalid Running the Accelerator on a program file If you run the Acc...

Page 167: ...h a SYNCDEPTH of 1 the SQL executor returns SQL error 8203 and forces the automatic recompilation of the statement If a statement is run in parallel on a table whose AUDIT attribute has changed since the last explicit SQL compilation the SQL executor returns SQL error 8207 and forces the automatic recompilation of the statement Operations That Do Not Invalidate a Program File These operations perf...

Page 168: ... PROGRAMS table but not to the program s file label because the file label is not audited Therefore a seemingly valid SQL program exists on disk but an entry for the program does not exist in the catalog You can sometimes recover from this condition by running SQLCOMP again to reenter the information in the catalog However you might first need to use the CLEANUP or GOAWAY utility to remove the inv...

Page 169: ...voked by the SQL executor of either an entire SQL program or a single static SQL statement in the program depending on whether the RECOMPILE or RECOMPILEONDEMAND option was specified during explicit SQL compilation Automatic SQL recompilation validates only the copy of the SQL program or statement in memory it does not validate the SQL program file on disk Only explicit SQL compilation validates a...

Page 170: ...s not specified the SQL executor invokes the SQL compiler to recompile a program or statement depending on the RECOMPILEALL or RECOMPILEONDEMAND option in these situations The program file is marked invalid at SQL load time The DEFINE values at SQL load time are different from the DEFINE values used to explicitly SQL compile the program The timestamp check fails for an SQL object referenced in an ...

Page 171: ... s file label to the timestamp for the same object in the statement s execution plan If the timestamps differ the SQL executor forces a recompilation with the new definition of the object After opening a table the SQL executor usually leaves a table open until the program stops running However a subsequent DDL or utility operation performed on the table or a dependent object such as an index or co...

Page 172: ...lable Uncompiled SQL Statement If the SQL executor encounters an uncompiled SQL statement it invokes the SQL compiler to compile the statement An SQL program file can contain an uncompiled SQL statement in these cases The SQL statement referenced an SQL object that did not exist or was unavailable during explicit SQL compilation The SQL statement referenced a DEFINE that did not exist during expli...

Page 173: ...hereas an inoperable plan must be recompiled to execute correctly By performing the similarity check the SQL executor recompiles only SQL statements that have inoperable execution plans It runs other SQL statements using their existing plans Executing the similarity check for an SQL statement eliminates unnecessary recompilations and is much faster than recompiling the statement The COMPILE option...

Page 174: ...QL compiler sets the program s PFV to 310 or later The SQL compiler also sets the program s PCV to 310 or later Therefore the SQL catalog in which the program is registered must have a catalog version of 310 or later For more information see the SQL MP Version Management Guide Enabling the Similarity Check for Tables and Protection Views To use the CHECK INOPERABLE PLANS option the similarity chec...

Page 175: ... for an underlying table the operation does not enable the check for a protection view defined on the table The SIMILARITYCHECK column in the TABLES table indicates whether a table or protection view has the similarity check enabled A table or protection view that has the similarity check enabled is version 310 or later All SQL MP components including the executor catalog manager and compiler must...

Page 176: ...BLE can have indexes that RUN TIME TABLE does not have but only if the execution plan does not use the additional indexes Key tags or values for indexes Creation timestamp and redefinition timestamp AUDIT attribute However if a statement performs a DELETE or UPDATE set operation on a nonaudited table that has a SYNCDEPTH of 1 the SQL executor returns an error and forces the automatic recompilation...

Page 177: ... similarity check but tables with different number of columns must observe these restrictions in addition to the other similarity check rules to pass the check The number of columns in COMPILE TIME TABLE must be less than or equal to the number of columns in RUN TIME TABLE The common columns of the tables must have identical attributes For example if COMPILE TIME TABLE has five columns RUN TIME TA...

Page 178: ...he CHECK INOPERABLE PLANS option and execution time name resolution When the SQL executor tries to use the plan with a new set of tables it retains the association of unqualified column names with tables established when the statement was explicitly compiled However if the similarity check fails and automatic recompilation is attempted the recompilation also fails because of the ambiguity If an IN...

Page 179: ...icitly 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 Consequently two tables that contain character columns associated with collations are similar only if the collations are equal ...

Page 180: ...Program Invalidation and Automatic SQL Recompilation HP NonStop SQL MP Programming Manual for C 429847 008 8 16 Preventing Automatic Recompilations ...

Page 181: ...UCTURES directive to generate version 300 or later SQL data structures If you omit this directive the C compiler generates version 2 structures by default and includes this informational message in the compilation summary INCLUDE STRUCTURES directive for SQL is missing SQL VERSION 2 is assumed This might produce incorrect SQL results in programs which use features introduced in SQL versions greate...

Page 182: ...QLSA versioning which allows a program to use an SQLSA structure that has the same version as the current SQL MP software for the system The SQLSA VERSION CURRENT option has these requirements It applies only to the SQLSA structure and not to the SQLCA or SQLDA structure The SQL MP software version must be 340 or later You must compile your program using the NMC compiler on TNS R systems or the C ...

Page 183: ...C program that uses the INCLUDE STRUCTURES directive to specify a later version of a structure than the C compiler can generate the compiler returns SQL error 11203 To determine the version of the C compiler before you compile a program run the VPROC program for the C compiler object file Then check the version in the VPROC line that contains S7094 which is the SQL compiler interface SCI product n...

Page 184: ...code Variable NonStop SQL MP returns an error or warning code to sqlcode after the execution of each embedded SQL statement or directive as follows Each SQL MP error or warning message has an assigned code For these codes and their meanings see the SQL MP Messages Manual Declaring the sqlcode Variable Declare sqlcode as a type short variable within the scope of each embedded SQL statement One meth...

Page 185: ...QL INCLUDE SQLCA Include sqlcode for simple error checking short sqlcode void do_sql_insert void Do an SQL INSERT into the parts table in_parts_rec in_partnum 4120 in_parts_rec in_price 6000000 IN_PRICE value is multiplied by 100 to reflect scale strcpy in_parts_rec in_partdesc V8 DISK OPTION EXEC SQL INSERT INTO sales parts partnum price partdesc VALUES in_parts_rec in_partnum SETSCALE in_parts_r...

Page 186: ...ndition applies to an SQL statement NonStop SQL MP processes the conditions in order of precedence For example an SQL error and an SQL warning can occur for the same statement but the error condition has a higher precedence and is processed first These WHENEVER directives check for the error warning and not found conditions EXEC SQL WHENEVER NOT FOUND CALL row_not_found EXEC SQL WHENEVER SQLERROR ...

Page 187: ...in in effect disable it at the end of the function as described following A program s order includes any files copied into the program using an include directive If a copied file contains a WHENEVER directive that directive remains in effect following the include directive A WHENEVER directive does not affect SQL statements if they appear in the program before the WHENEVER directive If you are deb...

Page 188: ...ctive for the part of the program that handles the condition Example 9 2 on page 9 9 enables and disables the WHENEVER directive Using the CALL Format To use the CALL format to execute an error handling function specify the WHENEVER directive globally and follow it with a forward declaration of the error handling functions Also ensure that each error handling function is accessible from all SQL st...

Page 189: ...no indicator variable was provided rather than the not found condition A WHENEVER NOT FOUND directive does not detect this condition 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 SELE...

Page 190: ...is specified first in the source code Example 9 3 Using the WHENEVER Directive page 1 of 2 EXEC SQL INCLUDE STRUCTURES ALL VERSION 315 define MAX_PARTDESC 19 EXEC SQL BEGIN DECLARE SECTION struct short in_partnum long in_price char in_partdesc MAX_PARTDESC in_parts_rec EXEC SQL END DECLARE SECTION EXEC SQL INCLUDE SQLCA For use with SQLCADISPLAY short sqlcode short ix Loop counter for blank paddin...

Page 191: ...occupies positions 0 through 13 start blank padding at position 14 for ix 14 ix ix in_parts_rec in_partdesc ix Do an SQL INSERT into the parts table EXEC SQL INSERT INTO parts partnum price partdesc VALUES in_parts_rec in_partnum SETSCALE in_parts_rec in_price 2 in_parts_rec in_partdesc End TMF transaction EXEC SQL COMMIT WORK void handle_errors void SQLCADISPLAY short sqlca exit EXIT_FAILURE void...

Page 192: ...edures With the SQLCA Structure Table 9 3 describes the SQL system procedures you can use to retrieve and display information from the SQLCA structure To call these procedures a program must include declarations from the cextdecs header file For more information about these SQL MP system procedures see Section 5 SQL MP System Procedures EXEC SQL INCLUDE SQLCA Table 9 2 C Identifiers Generated by t...

Page 193: ...nd the collation buffer Each DESCRIBE INPUT statement including information about input parameters output columns and the names buffer The SQLSA structure is undefined after the execution of a DSL DDL DCL or transaction control statement An SQL statement resets the SQLSA values If you use an SQLSA value elsewhere in a program save the value in a variable immediately after the statement runs To mon...

Page 194: ...se the symbolic names for these identifiers rather than the actual values because the values can change in a new RVU The native mode C compiler also generates this compiler pragma for version 330 or later SQLSA structures pragma fieldalign cshared2 SQLSA_TYPE_R330 DML_TYPE_R330 STATS_TYPE_R330 PREPARE_TYPE_R330 Example 9 4 on page 9 15 shows the layout of a version 300 through 325 SQLSA structure ...

Page 195: ...on struct DML_TYPE short num_tables struct STATS_TYPE char table_name 24 long records_accessed long records_used long disc_reads long messages long message_bytes short waits short escalations char sqlsa_reserved 4 stats 16 dml struct PREPARE_TYPE short input_num short input_names_len short output_num short output_names_len short name_map_len short sql_statement_type long output_collations_len prep...

Page 196: ... total_esp_cpu_time long long total_sortprog_cpu_time char filler 32 struct STATS_TYPE_R330 char table_name 24 long long records_accessed long long records_used long long disc_reads long long messages long long message_bytes long waits long escalations short vsbb_write short vsbb_flushed char filler 32 stats 16 dml struct PREPARE_TYPE_R330 short input_num short input_names_len short output_num sho...

Page 197: ...CPU time in microseconds used by all SORTPROG processes Applies only to a version 330 or later SQLSA structure stats Array containing num_tables valid entries one for each table accessed table_name Guardian internal file name of the table accessed records_accessed Number of records accessed in the corresponding table records_used Number of records altered or returned disc_reads Number of disk read...

Page 198: ...ames of output variables name_map_len Reserved sql_statement_type Statement being prepared name value and type _SQL_STATEMENT_SELECT 1 Cursor SELECT _SQL_STATEMENT_INSERT 2 INSERT _SQL_STATEMENT_UPDATE 3 UPDATE _SQL_STATEMENT_DELETE 4 DELETE _SQL_STATEMENT_DDL 5 DDL statement _SQL_STATEMENT_CONTROL 6 Run time CONTROL TABLE _SQL_STATEMENT_DCL 7 LOCK UNLOCK FREE RESOURCES _SQL_STATEMENT_GET 8 GET VE...

Page 199: ...so that the user does not have to know any SQL syntax If the statement requires input parameters the program can also prompt the user for these values The program can then construct the SQL statement by concatenating these values to SQL syntax elements For example a program might construct an entire SQL statement or only part of a statement such as a WHERE clause Restricted access to data You want...

Page 200: ...y compiles an SQL statement stored in a host variable and associates the prepared statement with a statement name an SQL identifier or a host variable name EXECUTE Runs a prepared SQL statement EXECUTE IMMEDIATE Compiles and runs an SQL statement stored in a host variable DECLARE CURSOR Defines an SQL cursor and associates the cursor with a SELECT statement OPEN Opens an SQL cursor Runs the associ...

Page 201: ...bout output columns or copies of any collations used by the columns The USING DESCRIPTOR clause of a FETCH statement to fill a cursor with rows from an SQL table The USING DESCRIPTOR clause of an EXECUTE statement to run a dynamic SQL statement Declaring the SQLDA Structure To declare an SQLDA structure use the INCLUDE SQLDA directive as follows sqlda name is the SQLDA structure name it must follo...

Page 202: ...ELEASE2 specifies SQL MP version 2 If release option specifies a version other than the default for the system the C compiler appends _R1 or _R2 to the SQLDA names and identifiers CPRULES is a required keyword if you specify a collation buffer collation buffer is a host variable specifying the name of the collation buffer The COLLATIONS INTO clause of the DESCRIBE statement allows you to return co...

Page 203: ...cture SQLDA_HEADER_LEN 4 The length in bytes of the SQLDA structure header fields eye_catcher and num_entries SQLDA_SQLVAR_LEN 24 The length in bytes of one SQLVAR entry SQLDA_NAMESBUF_OVHD_LEN 11 The overhead length in bytes added to the names buffer This overhead is the length field 2 bytes table name 8 bytes and period separator 1 byte Table 10 3 SQLDA Structure Fields page 1 of 2 Field Name De...

Page 204: ...aracter set ID For the precision values see Table 10 5 on page 10 10 null_info For input parameters null_info is a negative integer if the column permits null values For output columns null_info is a negative integer if the row returned is null var_ptr Extended address of the actual data value of input parameter or database column NonStop SQL MP does not return a value to var_ptr A program must in...

Page 205: ... table name 8 bytes and period separator 1 byte Use the SQLDA_NAMESBUF_OVHD_LEN identifier for this value NonStop SQL MP returns a collation name to the collation buffer as a VARCHAR item The C compiler determines the length in bytes of the collation buffer as follows collation buffer length max collation size 4 sqlvar count The 4 bytes added to max collation size is the length len field in the VA...

Page 206: ...ngth double byte character 64 _SQLDT_ASCII_V Variable length single byte character 65 _SQLDT_ASCII_V_UP Variable length single byte character upshifted 66 _SQLDT_DOUBLE_V Variable length double byte character Numeric Data Types 128 134 130 _SQLDT_16BIT_S 16 bit signed signed SMALLINT 131 _SQLDT_16BIT_U 16 bit unsigned unsigned SMALLINT 132 _SQLDT_32BIT_S 32 bit signed signed INT 133 _SQLDT_32BIT_U...

Page 207: ...H Hour to Hour 200 _SQL_DTINT_D_H Day to Hour 201 _SQL_DTINT_MI_MI Minute to Minute 202 _SQL_DTINT_H_MI Hour to Minute 203 _SQL_DTINT_D_MI Day to Minute 204 _SQL_DTINT_S_S Second to Second 205 _SQL_DTINT_MI_S Minute to Second 206 _SQL_DTINT_H_S Hour to Second 207 _SQL_DTINT_D_S Day to Second 208 _SQL_DTINT_F_F Fraction to Fraction 209 _SQL_DTINT_S_F Second to Fraction 210 _SQL_DTINT_MI_F Minute to...

Page 208: ...8 _SQL_DTINT_QUAL_Y_MO Year to Month 9 _SQL_DTINT_QUAL_Y_D Year to Day 10 _SQL_DTINT_QUAL_Y_H Year to Hour 11 _SQL_DTINT_QUAL_Y_MI Year to Minute 12 _SQL_DTINT_QUAL_Y_S Year to Second 13 _SQL_DTINT_QUAL_Y_F Year to Fraction 14 _SQL_DTINT_QUAL_MO_D Month to Day 15 _SQL_DTINT_QUAL_MO_H Month to Hour 16 _SQL_DTINT_QUAL_MO_MI Month to Minute 17 _SQL_DTINT_QUAL_MO_S Month to Second 18 _SQL_DTINT_QUAL_M...

Page 209: ...es not return an error Input Parameters and Output Variables A parameter is a name in a dynamic SQL statement that serves as a place holder for a value substituted when the statement runs Using a parameter an SQL statement can be compiled without the input values The input values are then substituted when the statement runs The syntax for a parameter is shown in the SQL MP Reference Manual Input p...

Page 210: ...al Blank pad the statement buffer dynamically compile the statement describe its variables prompt the user and read in the value for sal declare and open a cursor for the statement EXEC SQL FETCH cursor INTO enum sal output variables enum and sal If you do not know in advance which columns to select you can send the output values to data buffers the program allocated earlier and to which the progr...

Page 211: ...ent input values In this example a dynamic SQL statement uses a parameter Because the user of this program can enter any SQL statement the program does not have information about the statement during compilation The TACL DEFINE named parts represents the PARTS table 1 A user enters this SQL statement UPDATE parts SET price p 2 The program copies the statement into the host variable named intext 3 ...

Page 212: ...length_1 name_1 length_2 name_2 length_n name_n where name_1 is the first name name_2 the second name and name_n the last name The name length information is a 2 byte integer SQL data type PIC S9 4 COMP C data type int All names with a length of an odd number of characters are padded with a blank to make the length an even number When you display the names you might want to check for this blank pa...

Page 213: ... program accepts null values for input parameters the indicator parameter names are included in the names buffer For more information see Handling Null Values in Input Parameters on page 10 17 Example 10 2 Getting Parameter Values page 1 of 2 int request_invars sqldaptr input_sqlda_ptr char input_namesbuf_ptr define data_array_size 21 size for numeric parameter value maximum is 19 digits plus sign...

Page 214: ...t character is blank SQL inserts blanks to make the length fall on an even byte boundary if the name had an odd number of characters strncpy name_array input_namesbuf_ptr name_len 1 name_array name_len 0 else strncpy name_array input_namesbuf_ptr name_len name_array name_len 0 end else read and store named parameter Use a switch statement to check the data type prompt the user for input using the ...

Page 215: ...null_info contains a 1 and you are allocating memory dynamically you can now allocate two bytes of memory for a null indicator value and then set ind_ptr to point to the memory Allocate this memory at the same time you allocate memory for a possible nonnull parameter value If the user specifies a null value for the parameter assign a 1 to the location pointed to by ind_ptr NonStop SQL MP checks th...

Page 216: ... and output variable names indicator variable names are blank padded to even lengths When you are reading through the names buffer to prompt the user for parameter names you might need to be aware of the indicator fields and perform tasks like the following 1 Check the null_info field 2 If null_info is 1 read the length field for the indicator 3 Add this length field to the pointer or index to ski...

Page 217: ... and DESCRIBE statements a size to use before the memory is actually allocated you must declare the names buffer to be an arbitrarily large size this example uses 1000 Estimate a number that is greater than any possible size your names buffer could be Otherwise DESCRIBE INPUT and DESCRIBE might stop describing parameters or variables too soon 3 Declare an SQLSA structure using the INCLUDE SQLSA di...

Page 218: ...iated statement must all appear in the same procedure unless you are using a foreign cursor See Using Foreign Cursors on page 4 24 The PREPARE statement does not have to precede the other statements in the program listing order however the PREPARE statement must precede the DECLARE CURSOR statement and any DESCRIBE DESCRIBE INPUT OPEN FETCH and CLOSE statements for extended dynamic SQL statements ...

Page 219: ...specify a FOR UPDATE OF clause with a column list in the DECLARE CURSOR statement In contrast to use UPDATE WHERE CURRENT with a dynamic SQL cursor you must specify a FOR UPDATE OF clause in the SELECT statement that defines the cursor This example uses an UPDATE WHERE CURRENT operation with a dynamic SQL cursor In the example the host variable hostvar contains the SELECT statement to define the c...

Page 220: ...RGS STRING_LEN statements table char c MAX_STRGS STRING_LEN cursors table Store the statements in table t and the cursors in table c for i 1 i MAX_STRGS i EXEC SQL PREPARE s_hostvar FROM t i EXEC SQL DESCRIBE INPUT s_hostvar INTO input_sqlda Call a function to handle the input parameters EXEC SQL DESCRIBE s_hostvar INTO output_sqlda Call a function to handle the output variables EXEC SQL DECLARE C...

Page 221: ...riable and Host Variables Declare the sqlcode variable and any required host variables short sqlcode EXEC SQL BEGIN DECLARE SECTION char statement_buffer MAX_STATEMENT_LENGTH 1 EXEC SQL INVOKE employee AS employee_struct struct employee_struct employee_row input_sqlda_ptr pointer to input SQLDA output_sqlda_ptr pointer to output SQLDA input_namesbuf_ptr pointer to input names buffer output_namesbu...

Page 222: ...out the INCLUDE SQLSA directive see Section 9 Error and Status Reporting Process the Input Parameters If the input_num field in the SQLSA structure is greater than 0 zero process the input parameters Otherwise skip these steps and go to Read and Compile the SQL Statement on page 10 25 1 Get the length of the names buffer for parameter names from the input_names_len field in the SQLSA structure 2 A...

Page 223: ...cally define a variable for the indicator and put its address in ind_ptr 6 Loop through the names buffer to read the corresponding name for each parameter and prompt the user for each value Read each value into the corresponding occurrence in the input data buffer according to the data type of the value If the parameter can be null null_info is 1 and the value entered was null set ind_ptr to 1 Rea...

Page 224: ...ta type so the C program can handle and reset data_len accordingly b Allocate memory equal to data_len for the output column c Set var_ptr to point to the memory d If you are not allocating memory dynamically you would have declared a variable for each possible column value and put the address of the variable in var_ptr e If you know the number and data type of the output column values you set onl...

Page 225: ...tput_sqlda_ptr Display the values in a format according to data type For a repetitive display of column names use the output names buffer at this point and omit Step 6 of Process the Output Variables on page 10 25 Handle null values as follows If null_info is 1 check the value indicated by ind_ptr If ind_ptr is also 1 display a character representing a null value for example a blank or zero Otherw...

Page 226: ...names_len field is the length of the buffer required to contain the names of the input parameters The output_num field is the number of output variables in the statement Use this information to determine how many column values to report The output_names_len field is the length of the buffer required to contain the names of the output variables The sql_statement_type field is the type of statement ...

Page 227: ...you allocate the SQLDA you must explicitly initialize the eye_catcher and ind_ptr fields You must initialize ind_ptr even if your program is not using indicator variables to handle null values When you issue INCLUDE SQLDA to create the SQLDA template the C compiler creates a define for SQLDA_EYE_CATCHER which you then use to initialize the eye_catcher field sqlda_name eye_catcher SQLDA_EYE_CATCHER...

Page 228: ... In this call input_namesbuf_ptr is a pointer to the memory allocated for an input names buffer typedef char arrayptr 1000 if in_nameslen 0 input_namesbuf_ptr arrayptr malloc in_nameslen Example 10 4 Allocating the SQLDA Structure in main code typedef struct SQLDA_TYPE sqldaptr sqlda_type and sqlvar_type are generated by INCLUDE SQLDA sqldaptr allocate_sqlda num_entries int num_entries number of i...

Page 229: ...rs into the location pointed to by input_namesbuf_ptr Immediately after DESCRIBE INPUT runs the var_ptr field in the SQLDA points to the first entry in the names buffer You can use var_ptr to read the names from the names buffer only if you access the names buffer immediately following the DESCRIBE INPUT or DESCRIBE statement After you have set var_ptr to point to the data you can no longer use va...

Page 230: ...m_entries sqlda_ptr num_entries for i 0 i num_entries i switch sqlda_ptr sqlvar i data_type case _SQLDT_ASCII_F char type mem_reqd sqlda_ptr sqlvar i data_len break case _SQLDT_ASCII_V varchar type mem_reqd sqlda_ptr sqlvar i data_len 2 break For the numeric data types either save the scale information found in bits 0 7 or set these bits to 0 Then extract the length from bits 8 15 case _SQLDT_16BI...

Page 231: ...ode to allocate memory see Allocate Memory for the Values on page 10 31 Handle Scale If your program must handle numeric values with scale read the scale information from the output SQLDA structure The DESCRIBE statement places this information in bits 0 through 7 of the data_len field in the sqlvar array If you ignore scale set the data_len field to 0 causing data truncation Otherwise save the sc...

Page 232: ...o fall under its column heading You can use data type literals to decide how to display output column values Example 10 6 displays output Example 10 6 Displaying Output page 1 of 2 Declare open fetch and close the cursor for code see sample program int display_result sqldaptr output_sqlda_ptr char output_namesbuf_ptr int len_ptr Pointer to get length info from the names buffer int name_len Number ...

Page 233: ... rather than repetitively with each FETCH display all the names at this point The remaining code here assumes a repetitive display of column names and their associated values switch output_sqlda_ptr sqlvar i data_type case _SQLDT_ASCII_F char data type data_ptr char output_sqlda_ptr sqlvar i var_ptr data_len output_sqlda_ptr sqlvar i data_len strncpy data_array data_ptr data_len data_array data_le...

Page 234: ...termine the syntax of the statement As you process each value concatenate the corresponding text to form the statement For example suppose that the screen describes a personnel record If any column does not have a value the user can enter an N The request message you define is named list_msg This example checks the empnum field in list_msg and if required concatenates the text empnum to the dynami...

Page 235: ...data from the new location Dynamic SQL Sample Programs These pages contain two complete dynamic SQL programs in C The first program processes a SELECT statement that is partially coded into the program the user supplies the WHERE clause The second program allows the user to enter any SQL statement Basic Dynamic SQL Program The basic sample program contains a SELECT statement to find the average sa...

Page 236: ...owing is sample output for the program 47 run ezout PLEASE ENTER 1 To find average salary based on employee number 2 To find average salary based on job code 3 To find average salary based on department number 1 Please enter the comparison criteria for example 500 1000 250 500 THE AVERAGE SALARY IS 52250 48 The commented program listing appears in Example 10 7 on page 10 39 ...

Page 237: ... 512 30 31 Global variables 32 int sqlcode for error checking 33 long average for output value 34 int i loop counter 35 char temp 100 temporary storage for user input 36 37 Buffers for storing SQL statements are always blank 38 padded never null terminated 39 char cmd MAXCMD for SQL statement user enters 40 41 42 Include SQLCA for error checking SQLSA for dynamic SQL 43 processing information 44 e...

Page 238: ...we are passing in a 94 pointer to char We therefore create a pointer to 95 array of char for use by SQL 96 97 EXEC SQL END DECLARE SECTION 98 99 blank_pad cmd MAXCMD 100 101 prep_cmd cmd 102 103 exec sql PREPARE dyncmd FROM prep_cmd 104 105 strncpy osqlda eye_catcher SQLDA_EYE_CATCHER 2 106 107 osqlda num_entries 1 108 109 Initialize ind_ptr to NULL You must always initialize 110 this field even w...

Page 239: ...ns a SELECT statement to the statement buffer 152 Gets the WHERE clause from the user and concatenates it 153 to the SELECT statement 154 155 156 157 char column 9 column to be used in WHERE clause 158 int sel_index selects column for WHERE clause 159 char predicate 10 comparison predicate for WHERE 160 clause 161 size_t len for length of command to use in 162 blanking out null terminator for 163 ...

Page 240: ...ed as a template and allocated dynamically when the query is run 182 switch sel_index 183 184 case 1 strcpy column EMPNUM 185 break 186 case 2 strcpy column JOBCODE 187 break 188 case 3 strcpy column DEPTNUM 189 break 190 191 192 printf nPlease enter the comparison criteria n 193 printf for example 500 1000 250 n n 194 195 fgets temp int sizeof temp stdin 196 sscanf temp n predicate 197 198 Constr...

Page 241: ...A if there were parameters either performs a cursor FETCH for a SELECT statement or runs a non SELECT statement Before running the program command interpreter ADD DEFINE commands were entered to associate tables orders and odetail with logical names orders and odetail respectively The sample query shown selects order numbers and customer numbers from the orders table where the order includes part ...

Page 242: ...e stringh 21 include memoryh 22 include cextdecs SQLCADISPLAY 23 pragma list 24 25 include sqlh 26 27 28 Declare Section for host variable declarations 29 30 31 exec sql begin declare section 32 33 int sqlcode sqlcode required 34 exec sql include sqlca 35 exec sql include sqlsa 36 37 38 Include sqlda to get SQLDA_TYPE struct and SQLVAR_TYPE 39 struct declarations 40 41 Note for SQLDA structure tem...

Page 243: ...nk padded 81 never null terminated 82 83 define max_query_size 512 84 char host1 max_query_size 1 accepts SQL string 85 char host2 max_query_size 1 copy of the last SQL stmt 86 87 exec sql end declare section 88 89 90 The following UNION is defined for pointers to buffers of 91 different SQL data types This program does not handle 92 FLOAT DOUBLE PRECISION or DATETIME 93 94 union in_out_ptrs_u 95 ...

Page 244: ...ailure 142 143 144 145 int display_result sqldaptr sqlda ptr to output sqlda 146 char nb ptr to names buffer 147 148 begin display_result 149 150 short len_ptr int ptr to get the length 151 from the names buffer 152 short name_len num bytes in a name 153 short num_entries number of sqlvar entries 154 short i loop index 155 char data_array 39 buffer to contain data to 156 be displayed null termi 15...

Page 245: ...case _SQLDT_ASCII_F CHAR data type 207 208 data_ptr char sqlda sqlvar i var_ptr 209 data_len sqlda sqlvar i data_len 210 211 if data_len 38 212 strncpy data_array data_ptr data_len 213 data_array data_len 0 214 printf 40s s n name_array data_array 215 fflush stdout 216 217 else 218 219 display first 38 characters of data 220 printf 40s 38s n name_array data_ptr 221 fflush stdout 222 223 break 224 ...

Page 246: ...62 fflush stdout 263 break 264 265 266 case _SQLDT_32BIT_S 32 bit signed numeric 267 268 in_out_ptrs integer_ptr long sqlda sqlvar i var_ptr 269 printf 40s ld n name_array in_out_ptrs integer_ptr 270 fflush stdout 271 break 272 273 274 case _SQLDT_32BIT_U 32 bit unsigned numeric 275 276 in_out_ptrs uinteger_ptr 277 unsigned long sqlda sqlvar i var_ptr 278 printf 40s lu n name_array in_out_ptrs uin...

Page 247: ...ray_size 21 325 char data_array data_array_size 326 buffer to get numeric data 327 max 19 digits sign byte 328 null terminator 329 short data_len bytes of input data needed 330 short data_read bytes of input read 331 char name_array 31 buffer to contain null 332 terminated name of the 333 input param without the 334 leading 335 char lastchar 336 char dummy 337 338 num_entries sqlda num_entries 339...

Page 248: ... for s 389 data_len name_array 390 else 391 printf Please enter max d characters 392 data_len 393 394 if get_string in_out_ptrs char_ptr data_len 395 0 PARAM_TERMINATOR 0 396 input info too long 397 printf n Error Input data is too long n 398 fflush stdout 399 return 1 400 401 402 break 403 404 405 case _SQLDT_ASCII_V VARCHAR data type 406 407 in_out_ptrs char_ptr char sqlda sqlvar i var_ptr 2 408...

Page 249: ...449 Convert input number to appropriate numeric form 450 451 switch sqlda sqlvar i data_type 452 453 case _SQLDT_16BIT_S 16 bit signed numeric 454 in_out_ptrs smallint_ptr 455 short sqlda sqlvar i var_ptr 456 in_out_ptrs smallint_ptr atoi data_array 457 break 458 459 460 case _SQLDT_16BIT_U 16 bit unsigned numeric 461 in_out_ptrs usmallint_ptr 462 unsigned short sqlda sqlvar i var_ptr 463 in_out_p...

Page 250: ...e_name 511 array is null terminated 512 513 char get_dtname short datatype 514 515 begin get_dtname 516 517 switch datatype 518 519 case _SQLDT_ASCII_F CHAR data type 520 521 strcpy datatype_name CHARACTER 522 break 523 524 525 case _SQLDT_ASCII_V VARCHAR data type 526 527 strcpy datatype_name VARCHAR 528 break 529 530 531 case _SQLDT_16BIT_S 16 bit signed binary 532 533 strcpy datatype_name SIGNE...

Page 251: ...LSS 568 569 strcpy datatype_name LEADING SIGN SEPARATE DECIMAL 570 break 571 572 573 case _SQLDT_DEC_LSE DECIMAL datatype LSE 574 575 strcpy datatype_name LEADING SIGN EMBEDDED DECIMAL 576 break 577 578 579 case _SQLDT_DEC_TSS DECIMAL datatype TSS 580 581 strcpy datatype_name TRAILING SIGN SEPARATE DECIMAL 582 break 583 584 585 case _SQLDT_DEC_TSE DECIMAL datatype TSE 586 587 strcpy datatype_name ...

Page 252: ...0 until the semicolon 621 622 623 short get_string char data_array array to read data into 624 short array_size max bytes in array 625 short nullit if 0 terminate 626 data_array on return 627 else blank pad array 628 char terminator terminator character 629 630 begin get_string 631 632 char c 633 short ix next available slot 634 also return code 635 short i loop index 636 637 sanity check 638 if a...

Page 253: ...ut terminal 693 the SQL query A semicolon marks the end of the query 694 695 If the user types in END end E e then the session is 696 stopped If the user types in SAME same then the last 697 user query is run If the user types in an SQL 698 query the query is read in host1 array and a copy 699 of it is made in host2 array 700 701 Return 0 if query read in or SAME case 702 1 if END case 703 704 int...

Page 254: ...rs for supported data types 749 involves modifying the data_len and data_type 750 of the SQLVAR entry to reflect the data attributes 751 of the allocated buffers For example an input 752 parameter or output variable with 753 data_type _SQLDT_DEC_LSS and 754 data_len 7 assuming scale 0 755 can be modified to have 756 data_type _SQLDT_32BIT_S and 757 data_len 4 758 and a 4 byte buffer can be allocat...

Page 255: ...le information instead of setting 810 to zero as this program does 811 812 sqlda sqlvar i data_type _SQLDT_32BIT_S 813 sqlda sqlvar i data_len 4 and scale is 0 814 815 break 816 817 818 default UNSUPPORTED types or do not need adjustments 819 820 break Nothing to be done 821 822 823 switch stmt 824 825 for loop 826 827 return 0 828 829 end adjust_sqlda_scale_types 831 832 833 FUNCTION setupvarbuff...

Page 256: ...6BIT_U UNSIGNED SMALLINT 879 case _SQLDT_32BIT_S INTEGER 880 case _SQLDT_32BIT_U UNSIGNED INTEGER 881 882 883 NOTE ON SCALE INFORMATION 884 885 Bits 0 through 7 of sqlda sqlvar i data_len 886 have the scale information for the numeric 887 data types Either remember this scale 888 information and later use the values in the 889 host variables appropriately or set the 890 scale information to 0 whic...

Page 257: ...iables 944 sqldaptr sqlda pointer to be returned 945 int mem_reqd num bytes required to 946 allocate sqlda 947 short i loop index 948 949 sqlda NULL init pointer 950 951 return NULL if 0 entries requested 952 if num_entries 0 953 return sqlda 954 955 allocate sqlda 956 mem_reqd sizeof struct SQLDA_TYPE 957 num_entries 1 sizeof struct SQLVAR_TYPE 958 if sqlda sqldaptr malloc mem_reqd NULL 959 memor...

Page 258: ...return 0 997 998 num_entries sqlda num_entries 999 for i 0 i num_entries i 1000 if buf_ptr char sqlda sqlvar i var_ptr NULL 1001 free buf_ptr 1002 1003 1004 free char sqlda freeup the sqlda memory 1005 1006 return 0 1007 1008 end free_sqlda 1009 1010 1011 FUNCTION cleanup 1012 This function frees up the allocated memory for the 1013 input and output sqldas and names buffers and the 1014 data buffe...

Page 259: ...put SQL query from terminal 1056 1057 enter_input 1058 1059 freeup memory taken by sda_i sda_o 1060 and cname_i cname_o names buffers 1061 cleanup 1062 1063 if status read_query 0 1064 goto exit 1065 1066 1067 BEGIN TRANSACTION 1068 1069 exec sql begin work 1070 1071 1072 PREPARE the SQL statement 1073 1074 exec sql PREPARE S1 from host1 1075 1076 if sqlcode 0 1077 display errors warnings 1078 pri...

Page 260: ...ntf n Process stopped 1119 fflush stdout 1120 if sda_i NULL free char sda_i 1121 if sda_o NULL free char sda_o 1122 goto exit 1123 1124 1125 if out_nameslen 0 1126 if cname_o arrayptr malloc out_nameslen NULL 1127 1128 printf n 1129 printf Error Memory allocation failure for output names buffer 1130 printf n 1131 printf Process stopped 1132 fflush stdout 1133 if sda_i NULL free char sda_i 1134 if ...

Page 261: ... output variables 1179 1180 if out_numvars 0 1181 1182 exec sql DESCRIBE S1 INTO sda_o 1183 NAMES INTO cname_o 1184 1185 if sqlcode 0 1186 display error warnings 1187 printf n fflush stdout 1188 SQLCADISPLAY int sqlca 1189 if sqlcode 0 errors present 1190 1191 exec sql rollback work abort transaction 1192 goto enter_input try again 1193 1194 1195 1196 1197 Allocate output data buffers and update o...

Page 262: ... sqlca 1233 if sqlcode 0 errors present 1234 1235 exec sql rollback work abort transaction 1236 goto enter_input try again 1237 1238 1239 1240 1241 FETCH loop 1242 1243 sqlcode 0 1244 num_fetches 0 1245 1246 while sqlcode 0 1247 exec sql fetch C1 USING DESCRIPTOR sda_o 1248 1249 if sqlcode 100 eof 1250 printf n lu row s selected n num_fetches 1251 fflush stdout 1252 exec sql close C1 close cursor ...

Page 263: ...S1 using descriptor sda_i 1288 1289 else 1290 1291 exec sql execute S1 1292 1293 1294 if sqlcode 0 1295 display error warnings 1296 printf n fflush stdout 1297 SQLCADISPLAY int sqlca 1298 if sqlcode 0 errors present 1299 1300 exec sql rollback work abort transaction 1301 goto enter_input try again 1302 1303 1304 1305 printf n SQL Operation Complete n 1306 fflush stdout 1307 1308 end not a select s...

Page 264: ...Dynamic SQL Operations HP NonStop SQL MP Programming Manual for C 429847 008 10 66 Detailed Dynamic SQL Program ...

Page 265: ...haracters in a string are in the ALPHAS character class according to the specified SQL collation or collation object CPRL_ARENUMERICS_ Determines if all characters in a string are numeric according to the specified SQL collation or collation object CPRL_COMPARE1ENCODED_ Compares two strings one encoded according to the collation defined by an SQL collation or collation object CPRL_COMPARE_ Compare...

Page 266: ...ollation or collation object CPRL_GETDOWNSHIFTTABLE_ Extracts downshift information from an SQL collation or collation object CPRL_GETFIRST_ Finds the first string of a specified length according to an SQL collation or collation object CPRL_GETLAST_ Finds the last string of a specified length according to an SQL collation or collation object CPRL_GETNEXTINSEQUENCE_ Finds the next string after a sp...

Page 267: ...er of bytes in the character class name classname inputstring is a string containing the data to be scanned inputstringlength is the number of bytes to be scanned in inputstring include cextdecs CPRL_ARE_ short CPRL_ARE_ char classname i short classnamelength i char inputstring i short inputstringlength i long exceptcharaddr o long cprladdr i Code Description 0 The operation was successful 2 The S...

Page 268: ...EALPHAS_ The CPRL_AREALPHAS_ procedure determines if all characters in a string are in the ALPHAS character class according to a specified SQL collation or collation object You can also use this procedure to scan for the first character in the string that is not in the ALPHAS character class The CPRL_AREALPHAS_ procedure returns these values inputstring is an array containing the string to be scan...

Page 269: ...RL_ARENUMERICS_ The CPRL_ARENUMERICS_ procedure determines if all characters in a string are numeric according to the specified SQL collation or collation object You can also use CPRL_ARENUMERICS_ to scan for the first nonnumeric character in a string The CPRL_ARENUMERICS_ procedure returns these values inputstring is an array containing the data to be scanned inputstringlength is the number of by...

Page 270: ... assumed to be in encoded form and the second is assumed to be in original not encoded form For strings of unequal length the procedure logically pads the shorter string with blanks Use the CPRL_COMPARE1ENCODED_ procedure to compare a constant with a set of values in one pass The procedure encodes as much of the second string as necessary to perform the compare and the overhead of repeatedly encod...

Page 271: ...ares two strings according to an SQL collation or collation object Both strings are assumed to be in original not encoded form For strings of unequal length CPRL_COMPARE_ pads the shorter string with blanks CPRL_COMPARE_ is more efficient for isolated compares because only the necessary part of each string is encoded to do the compare If the same data is repeatedly compared use the CPRL_ENCODE_ an...

Page 272: ...sult of the comparison if the error code is 0 zero cprladdr is a pointer to the SQL collation or collation object CPRL_COMPAREOBJECTS_ The CPRL_COMPAREOBJECTS_ procedure compares two SQL collations or collation objects to determine whether they are equal Code Description 0 The operation was successful 2 The SQL collation or collation object is invalid 4 The version of the SQL collation or collatio...

Page 273: ...nsitive might produce a decoded string with different case letters than the original string The string ABCDE might encode to a value which when decoded is aBcDe The CPRL_DECODE_ procedure returns these values Code Description 0 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 coll...

Page 274: ...dstringmaxlength cprladdr is a pointer to the SQL collation or collation object CPRL_DOWNSHIFT_ The CPRL_DOWNSHIFT_ procedure downshifts a character string according to the downshift rules in a specified SQL collation or collation object The CPRL_DOWNSHIFT_ procedure returns these values include cextdecs CPRL_DOWNSHIFT_ short CPRL_DOWNSHIFT_ char inputstring i short inputstringlength i char shifte...

Page 275: ...ftedstring it must be greater than equal to inputstring shiftedstringlength specifies the length of the downshifted string returned in shiftedstring cprladdr is a pointer to the SQL collation or collation object CPRL_ENCODE_ The CPRL_ENCODE_ procedure encodes a character string so that a subsequent binary comparison produces proper results for the specified SQL collation Use CPRL_ENCODE_ in situat...

Page 276: ...tringlength is the number of bytes that were encoded CPRL_ENCODE_ pads the remainder of decodedstring with encoded blanks up to decodedstringmaxlength cprladdr is a pointer to the SQL collation or collation object CPRL_GETALPHATABLE_ The CPRL_GETALPHATABLE_ procedure extracts ALPHAS character class information for single byte character sets from an SQL collation or collation object Code Descriptio...

Page 277: ...cedure returns these values Code Description 0 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 1 The corresponding character code in the SQL collation or collation object is in the ALPHAS character class 0 The corresponding character code in the SQL collation or collation object is not in the AL...

Page 278: ...ETDOWNSHIFTTABLE_ procedure returns these values array is a 256 byte array specified by the user If the call is successful CPRL_GETDOWNSHIFTTABLE_ sets each byte in array to the downshifted version of the corresponding character in the SQL collation or collation object If the call is unsuccessful array is not modified 1 The corresponding character code in the SQL collation or collation object is i...

Page 279: ...PRL_GETFIRST_ procedure returns these values firststring is an array in which CPRL_GETFIRST_ returns the first string firststringmaxlength is the maximum length of firststring firststringlength specifies the number of bytes of firststring that were scanned If CPRL_GETFIRST_ is successful firststringmaxlength and firststringlength are equal cprladdr is a pointer to the SQL collation or collation ob...

Page 280: ...ese values laststring is an array in which CPRL_GETFIRST_ returns the last string laststringmaxlength specifies the maximum length of laststring laststringlength specifies the number of bytes of laststring that were scanned If CPRL_GETLAST_ is successful laststringlength and laststringmaxlength are equal cprladdr is a pointer to the SQL collation or collation object include cextdecs CPRL_GETLAST_ ...

Page 281: ...QUENCE_ returns the next string Overlapping inputstring and nextstring causes unpredictable results nextstringmaxlength specifies the maximum length of nextstring The returned value is padded with blanks as necessary to fill nextstring for this length In most cases set nextstring to the same value as inputstring include cextdecs CPRL_GETNEXTINSEQUENCE_ short CPRL_GETNEXTINSEQUENCE_ char inputstrin...

Page 282: ...rocedure extracts numeric character class information from an SQL collation or collation object The CPRL_GETNUMTABLE_ procedure returns these values array is a 256 byte array specified by the user If the call is successful CPRL_GETNUMTABLE_ sets each byte in array as follows If the call is unsuccessful array is not modified cprladdr is a pointer to the SQL collation or collation object include cex...

Page 283: ...PECIALTABLE_ procedure returns these values array is a 256 byte array specified by the user If the call is successful CPRL_GETALPHATABLE_ sets each byte in array as follows If the call is unsuccessful array is not modified cprladdr is a pointer to the SQL collation or collation object include cextdecs CPRL_GETSPECIALTABLE_ short CPRL_GETSPECIALTABLE_ char array o long cprladdr i Code Description 0...

Page 284: ...he call is unsuccessful array is not modified cprladdr is a pointer to the SQL collation or collation object CPRL_INFO_ The CPRL_INFO_ procedure returns information about an SQL collation or collation object The SQL CREATE COLLATION statement uses this procedure to determine the characteristics of SQL collations include cextdecs CPRL_GETUPSHIFTTABLE_ short CPRL_GETUPSHIFTTABLE_ char array o long c...

Page 285: ...tion objects that preserve or shorten the length on encoding lengtheningfactor is 1 characterset specifies the character set assumed by the SQL collation or collation object version is the format version of the SQL collation or collation object Code Description 0 The operation was successful 2 The SQL collation or collation object is invalid 4 The version of the SQL collation or collation object i...

Page 286: ...ECT_ uses a local 4 KB buffer allocated on the data stack If you are concerned about stack size limitations use this procedure with caution bufferlength is the size of buffer in bytes objectlength is the actual length in bytes of the collation object read into buffer include cextdecs CPRL_READOBJECT_ short CPRL_READOBJECT_ short buffer o short bufferlength i short objectlength o char filename i sh...

Page 287: ...edure returns these values inputstring is an array containing the data to be upshifted inputstringlength is the number of bytes in inputstring to be upshifted shiftedstring is an array in which CPRL_UPSHIFT_ returns the upshifted string The values for inputstring and shiftedstring can be equal but other values can cause unpredictable results include cextdecs CPRL_UPSHIFT_ short CPRL_UPSHIFT_ char ...

Page 288: ... 11 24 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 ...

Page 289: ...me Ask your database administrator or system manager for the volume where the ZTSQLMSG subvolume is installed on your system The ZTSQLMSG DOCUMENT file describes the files in the ZTSQLMSG subvolume The DOCUMENT file also explains how to create a copy of the sample database To print the DOCUMENT file enter this TGAL command at your TACL prompt TGAL IN ZTSQLMSG DOCUMENT OUT S loc The loc parameter i...

Page 290: ...e ORDERS Table SUPPLIER Table JOB Table ODETAIL Table PARTLOC Table PERSNL Subvolume SALES Subvolume INVENT Subvolume empnum first_name last_name deptnum jobcode salary suppnum suppname street city state postcode ordernum order_date deliv_date salesrep custnum deptnum deptname manager rptdept location jobcode jobdesc PARTSUPP Table partnum suppnum partcost qty_received loc_code partnum qty_on_hand...

Page 291: ... page 1 of 3 Personnel PERSNL pragma SECTION EMPLOYEE Record Definition for SYS1 VOL1 PERSNL EMPLOYEE Definition current at 09 53 58 11 10 96 struct employee_type unsigned short empnum char first_name 16 char last_name 21 unsigned short deptnum unsigned short jobcode unsigned long salary scale is 2 pragma SECTION DEPT Record Definition for SYS1 VOL1 PERSNL DEPT Definition current at 09 54 00 11 10...

Page 292: ... 05 11 10 96 struct orders_type unsigned long ordernum long order_date long deliv_date unsigned short salesrep unsigned short custnum pragma SECTION ODETAIL Record Definition for SYS1 VOL1 SALES ODETAIL Definition current at 09 54 06 11 10 96 struct odetail_type unsigned long ordernum unsigned short partnum long unit_price scale is 2 unsigned long qty_ordered pragma SECTION PARTS Record Definition...

Page 293: ... at 09 54 11 11 10 96 struct supplier_type unsigned short suppnum char suppname 19 char street 23 char city 15 char state 13 char postcode 11 pragma SECTION PARTLOC Record Definition for SYS1 VOL1 INVENT PARTLOC Definition current at 09 54 12 11 10 96 struct partloc_type char loc_code 4 unsigned short partnum long qty_on_hand pragma SECTION ERRORS Record Definition for SYS1 VOL1 INVENT ERRORS Defi...

Page 294: ...SQL MP Sample Database HP NonStop SQL MP Programming Manual for C 429847 008 A 6 ...

Page 295: ...tements directives and host variables that are used in the program Table B 1 lists SQL data structures and when each structure is generated Table B 1 SQL MP Data Structures SQL Data Structure Statement Directive or Host Variable SQLIN SQL statement or directive that generates a call to the SQL executor except the following BEGIN DECLARE SECTION or END DECLARE SECTION directive CONTROL directives I...

Page 296: ...s this pragma The SQLMEM pragma is valid only for the large memory model which is the default for the C compiler If you specify SQLMEM for the small memory model NOXMEM pragma on a TNS system the C compiler returns error 172 To specify the SQLMEM pragma in your program you must first specify the SQL pragma Otherwise the C compiler returns error 173 illegal SQLMEM option Use the SQLMEM pragma as ma...

Page 297: ...t 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 Table B 2 Virtual Memory Requirements for SQL Statements Bytes Required Description 72 Base value for a statement with no host variables 4 24 number of input host variables Required for a ...

Page 298: ...hare many of the pages in the extended data segment As a last measure use dynamic SQL statements Using dynamic SQL statements can reduce memory use however it can also degrade a program s performance because of the additional SQL run time compilations Avoiding Memory Stack Overflows To avoid memory stack overflows for most SQL statements the SQL executor needs at least 3000 words of available stac...

Page 299: ...VAR pragma also the default to direct the C compiler to allocate static aggregates in extended memory Allocate SQL internal data structures SQLIN SQLIVARS and SQLOVARS in extended memory which is the default for the large memory model The SQLMEM pragma USER or EXT controls the placement of the SQL structures SQLMEM EXT extended memory is the default Use the Binder SET EXTENDSTACK command to extend...

Page 300: ...Memory Considerations HP NonStop SQL MP Programming Manual for C 429847 008 B 6 Avoiding Memory Stack Overflows ...

Page 301: ... following these guidelines Use a local partition rather than the primary partition as the table name Use TACL DEFINEs Use current statistics Skip unavailable partitions For collations NonStop SQL MP supports run time node autonomy because collations are stored in an SQL object s file label and within expressions that operate on the SQL objects For example suppose that you create a partitioned tab...

Page 302: ...l parts that have V8 DISK OPTION in the PARTDESC column Using TACL DEFINEs By using TACL DEFINEs in a program to refer to tables and associating those DEFINEs with local partitions you increase the number of successful compilations of programs that access a distributed database All SQL compilations are affected including explicit compilations and automatic recompilations Using Current Statistics F...

Page 303: ...NTROL 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 The SKIP UNAVAILABLE PARTITION option applies to static or dynamic SQL statements that refer to partitioned tables and partitioned indexes of the tables...

Page 304: ...Maximizing Local Autonomy HP NonStop SQL MP Programming Manual for C 429847 008 C 4 Skipping Unavailable Partitions ...

Page 305: ...ime use the compiler generated length identifiers for example SQLSA_LEN for the length of an SQLSA structure to specify the memory to allocate In some cases you can also use a C function to generate the length of a structure Using the compiler generated length identifiers can reduce the impact on a program if the size of an SQL data structure changes in a future PVU Use the system generated eye ca...

Page 306: ...e combinations of SQLDA structures A version 300 or later SQLDA structure A version 1 or 2 SQLDA structure A version 300 or later SQLDA structure and a version 1 or 2 SQLDA structure Table D 1 Changes to SQL Data Structures Version Size Bytes Eye Catcher Value New Fields SQLCA Structure 1 2 Š 300 430 CA None SQLSA Structure 1 838 SA 2 838 SA None 300 325 838 SA output_collations_len Š 330 1790 SA ...

Page 307: ...15 option EXEC SQL INCLUDE STRUCTURES SQLDA VERSION 315 5 Add the necessary executable statements to process the version 310 SQLDA structure For the layout of a version 300 or later SQLDA structure and a description of each field see Section 10 Dynamic SQL Operations Generating a Version 2 SQLDA Structure If you are converting a program to use the INCLUDE STRUCTURES directive but you require a ver...

Page 308: ...ng long reserved sqlvar sqlvar count sqlda name char names buffer name length 1 Table D 2 Version 2 SQLDA Structure Fields page 1 of 2 Field Name Description eye_catcher An identifying field that a program must initialize as D1 for version 1 or DA for version 2 SQL MP statements do not return values to eye_catcher num_entries Number of input parameters or output variables the SQLDA structure can a...

Page 309: ...NTERVAL Bits 0 7 contain the leading field precision Bits 8 15 contain the fraction precision or 0 if the fraction field is not included null_info For input parameters A negative integer if the column permits null values For output columns A negative integer if the row returned is null var_ptr Extended address of the actual data value of input parameter or column NonStop SQL MP does not return var...

Page 310: ...LUDE STRUCTURES SQLDA VERSION 1 Example D 2 shows a version 1 SQLDA structure Table D 3 describes the fields in a version 1 SQLDA structure Example D 2 Version 1 SQLDA Structure define SQLDA_EYE_CATCHER DA can have _R1 appended struct SQLDA_TYPE can have _R1 appended char eye_catcher 2 short num_entries struct SQLVAR_TYPE can have _R1 appended short data_type short data_len short null_info long va...

Page 311: ...ompile each compilation unit separately 4 Use the Binder program to combine the object files into a single target object file 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 ...

Page 312: ...tax of the GET VERSION statements see the SQL MP Reference Manual RELEASE1 and RELEASE2 Options The RELEASE1 and RELEASE2 options used in the SQL pragma and the INCLUDE SQLDA directive might not be supported in future PVUs Consider modifying the program to use the INCLUDE STRUCTURES directive with the VERSION 1 or VERSION 2 options to generate version 1 or version 2 SQLDA structures Or convert the...

Page 313: ...tement error 8204 4 3 program invalidation 8 4 similarity check considerations 8 13 ALTER VIEW statement 4 3 Altering SQL file attribute 8 3 Arguments C compiler RUN command 7 3 ASSIGN command TACL 7 2 Asterisk with pointer as host variable 2 6 with similarity check 8 13 Attributes SQL file 8 3 AUDIT attribute altering and automatic recompilation 8 3 similarity check rules 8 12 AUDITCOMPRESS attri...

Page 314: ...uthority for program execution 7 1 CHECK options 8 10 SQLGETCATALOGVERSION 5 18 version considerations 8 10 CATALOG clause SQL compiler 6 6 6 15 CATALOG TACL DEFINE 6 6 6 15 cextdecs file CPRL procedures 11 2 dynamic SQL applications 10 23 10 36 header file 5 2 JULIANTIMESTAMP procedures 4 10 SQLCA structure 9 12 SQL MP procedures 1 4 system procedures 5 2 CHAR data type host variable declaration ...

Page 315: ...ions area SQL See SQLCA structure Compilation automatic recompilation 8 5 C compiler syntax 6 9 Compilation continued C source file 1 5 dynamic SQL statements 6 23 explicit SQL 6 13 6 14 COMPILE clause SQL compiler 6 20 CONTROL TABLE directive C 3 Conversational interface See SQL Conversational Interface SQLCI Conversion between SQL and C data 2 5 CONVERTTIMESTAMP function 4 10 COPY command and lo...

Page 316: ...ement 4 20 OPEN statement 4 19 SELECT statement 4 21 SQL objects 4 16 UPDATE statement 4 22 SELECT statement 4 21 stability of cursor 4 17 Virtual sequential block buffering VSBB 4 17 WHERE clause 4 21 D Data conversion between C and SQL data types 2 5 Data declarations BEGIN DECLARE SECTION directive 2 1 END DECLARE SECTION directive 2 1 statements 1 3 tables and views 2 19 Data Definition Langua...

Page 317: ...SQL MP directives Disk process DP2 SQLCADISPLAY 5 3 SQLCAFSCODE 5 8 SQLCATOBUFFER 5 14 Distributed database maximizing local autonomy C 1 Double hyphen in SQL statements 3 1 Double quotes in SQL statements 3 1 DP2 See Disk process DP2 DROP CONSTRAINT statement error 8204 4 3 program invalidation 8 5 DROP INDEX statement error 8204 4 3 program invalidation 8 5 DROP TABLE statement error 8204 4 3 pr...

Page 318: ...e SQL recompilation 8 9 UPDATE statement 4 11 EXEC SQL keywords 3 1 EXECUTE IMMEDIATE statement description 1 6 EXECUTE IMMEDIATE statement SQL compilation errors 6 23 EXECUTE statement 1 6 Executing a C program 1 5 7 1 Execution plan EXPLAIN report 6 27 optimized by SQL compiler 6 13 optimized by statistics 6 23 SQL compiler function 6 13 EXPLAIN utility EXPLAIN DEFINES report 6 27 EXPLAIN PLAN r...

Page 319: ...rocedures See System procedures Guardian H Help text similarity check rules 8 12 HIGHPIN object file attribute 7 5 HIGHPIN run option TACL RUN command 7 5 Host object SQL version HOSV C compiler 6 37 definition 6 37 Host variable colon 1 2 creating with INVOKE 2 19 data conversion 2 5 decimal data type 2 11 declaration 1 2 2 1 declare sections 2 1 definition 2 1 DELETE statement 4 13 fields in a s...

Page 320: ...through SQLCI 2 24 Item codes SQLCAGETINFOLIST 5 11 SQLCAGETINFOLIST parameter 5 10 J JULIANTIMESTAMP procedure 4 10 K Key tags similarity check rules 8 12 L Library procedures system 1 4 List file C compiler 6 9 SQL compiler 6 14 LOAD command and lost open error 4 3 Load time SQL 8 6 Local autonomy maximizing for distributed database C 1 program execution 8 6 program file validity 8 4 TACL DEFINE...

Page 321: ...agma 6 7 Not found condition WHENEVER directive 9 6 NOWHENEVERLIST option SQL pragma 6 7 NOXMEM pragma B 2 NULL keyword with INSERT statement 4 9 with UPDATE statement 4 12 NULL STRUCTURE clause with INVOKE directive 2 23 Null terminator C strings 2 7 host variables in arrays 2 7 Null value definition 2 1 dynamic SQL 10 16 10 27 input parameters 10 17 INSERT statement 2 17 4 9 INVOKE directive 2 2...

Page 322: ...ity check rules 8 12 P Parallel execution plans automatic recompilation 8 3 similarity check 8 9 PARAM command TACL for SQL program file 7 2 PARAM command TACL with SQL compiler 6 22 Parameter dynamic SQL 10 11 indicator 10 17 unnamed 10 11 using a list 10 12 using in loop 10 13 value substitution 10 11 Partition local to maximize local autonomy C 1 similarity check rules for attributes 8 12 skipp...

Page 323: ...C advantages of INVOKE directive 2 19 automatic recompilation dependencies 8 3 overview 1 1 Program file SQL binding object file 6 11 execution 7 1 SQL compilation 6 12 Program file SQL continued TACL DEFINEs 7 2 TACL RUN command 7 3 Program format version PFV CHECK options 6 18 8 10 definition 6 37 OSS program file 6 33 similarity check 8 11 SQL executor 7 7 Program object file See Program file S...

Page 324: ... statement 4 13 single row SELECT statement 4 4 single row UPDATE statement 4 11 SQLSA statistics 9 14 UPDATE statement 4 10 4 22 RTDU run time data unit 6 8 RUN command TACL SQL object file 1 5 SQL program file 7 3 Run option TACL C compiler 6 9 SQL compiler 6 15 SQL program file 7 3 Run time data unit RTDU 6 8 RUND command TACL 7 3 RUNNABLE pragma 6 9 6 12 Run time memory allocation 10 18 Run ti...

Page 325: ...FINE See _SORT_DEFAULTS DEFINE SOURCE directive SQL 2 2 Source file C compiler 6 9 SQL comments Declare Section 2 2 SQL communications area See SQLCA structure SQL compiler interface SCI 9 3 SQL compiler SQLCOMP automatic recompilation 8 5 CATALOG clause 6 6 DEFINEs 6 6 description 1 5 6 12 determining version 6 36 dynamic SQL statements 6 23 error messages 6 23 EXPLAIN report 6 26 6 27 EXPLAIN ut...

Page 326: ...2 5 declaration 9 4 dynamic SQL use 10 23 WHENEVER directive 9 6 SQLCOMP command description 6 12 EXPLAIN DEFINES option 7 2 SQLCOMP command continued SQLMAP option 9 3 syntax 6 14 SQLCOMPILE option RESTORE CHECK option 6 18 SQLDA structure declarations 10 8 eye_catcher field 10 5 names buffer 10 4 parameter 10 14 SQL statements 10 3 Version 300 template 10 4 Version 315 or later template 10 5 ver...

Page 327: ...04 4 3 program invalidation 8 5 ALTER TABLE error 8204 4 3 program invalidation 8 4 similarity check 8 13 ALTER VIEW 4 3 BEGIN WORK 10 27 coding guidelines 3 1 coding in source file 3 2 COMMIT WORK 10 28 CREATE CONSTRAINT 4 3 SQL MP statements continued CREATE INDEX NO INVALIDATE option 8 3 program invalidation 8 5 DECLARE CURSOR 4 18 DELETE 4 12 4 23 description 1 3 DROP CONSTRAINT error 8204 4 3...

Page 328: ...EFINES option SQL compiler 6 15 SQLCOMP command 6 6 STRIP command Binder 6 12 Structure as host variable 2 9 SUFFIX clause with INVOKE statement 2 23 Swap file volume for SQL compiler 6 22 SWAPVOL option PARAM command 6 22 SYNCDEPTH and automatic recompilation 8 3 System procedures Guardian CLOSE 1 4 FILE_GETINFOBYNAME_ 5 2 FILE_GETINFOLISTBYNAME_ 5 2 FILE_GETINFOLIST_ 5 2 FILE_GETINFO_ 5 2 JULIAN...

Page 329: ...tor SQL statement 3 1 Timestamp check at table open time 8 7 collation check 8 15 INSERT statement 4 10 program validation time 8 1 run time check 8 7 TMF See Transaction Management Facility TMF TNS R system 1 5 Transaction Application Language TAL host language 1 1 Transaction control statements 1 3 Transaction Management Facility TMF catalog inconsistencies 8 4 data consistency 1 1 example 9 11 ...

Page 330: ...laration 2 9 Variable length character data host variable declaration 2 9 VERIFIEDWRITES attribute similarity check rules 8 12 VERIFY utility SQL 8 1 Version management C compiler 6 36 9 3 Version management continued description 1 7 displaying information SQLGETCATALOGVERSION procedure 5 18 SQLGETOBJECTVERSION procedure 5 19 SQLGETSYSTEMVERSION procedure 5 19 INCLUDE STRUCTURES directive 9 1 SQL ...

Page 331: ...B 5 RECEIVE file 10 36 SYSTEM SYSTEM SQLMSG file 5 2 asterisk with pointer as host variable 2 6 with similarity check 8 13 Wverbose flag and EXPLAIN utility 6 33 double hyphen in SQL statements 3 1 bin compilers directory 6 30 nonnative bin compilers directory 6 30 colon with host variable 1 2 2 6 semicolon in SQL statements 1 3 3 1 _DEFAULTS DEFINE TACL 6 27 _SORT_DEFAULTS DEFINE 7 2 question mar...

Reviews:

No comments

Brands by name

Popular brands

Load more brands