HP NonStop SQL Programming Manual for Pascal Abstract This manual describes the programmatic interface to HP NonStop™ SQL for the Pascal programming language. The NonStop SQL relational database management system (RDBMS) uses the structured query language (SQL) to describe and manipulate data in NonStop SQL databases. This manual is intended for application programmers who are coding embedded SQL statements in a Pascal program.
Document History Part Number Product Version Published N.A NonStop SQL C30 November 1989 N.
HP NonStop SQL Programming Manual for Pascal Index Figures Tables What’s New in This Manual ix Manual Information ix New and Changed Information About This Manual xi Audience xi Organization xi Related Manuals xii Notation Conventions ix xiii 1.
Contents 3. NonStop SQL Statements and Directives (continued) 3.
3. NonStop SQL Statements and Directives (continued) Contents 3.
5. Program Compilation and Execution Contents 5.
6. Error and Status Processing (continued) Contents 6. Error and Status Processing (continued) Guidelines for Declaring the SQLDA 6-18 Data Type Literals 6-18 Example of Declaring the SQLDA 6-21 7.
D. NonStop SQL Version Issues (continued) Contents D. NonStop SQL Version Issues (continued) Developing C10 Applications with C30 Software D-10 Binding C10 and C30 Object Files D-10 Programmatic Features for Version Control D-11 Release Specification Options D-11 Version Information Procedures D-11 Programming Techniques for Mixed Versions D-12 Generic Release Programs D-12 Using the Single-Code Thread Design D-14 E.
Figures (continued) Contents Figures (continued) Figure 7-6. Figure 7-7. Figure 7-8. Figure 7-9. Figure A-1. Figure A-2. Figure B-1. Figure D-1. Checking Data Type 7-25 Allocating Memory for Values 7-26 Getting Parameter Values 7-29 Displaying Output 7-32 Sample NonStop SQL Database Relations A-1 Sample Database Source File A-2 Date-Time Program Run B-7 Developing and Installing a Generic Application D-15 Tables Table 1-1. Table 1-2. Table 2-1. Table 2-2. Table 3-1. Table 4-1. Table 4-2. Table 4-3.
Contents HP NonStop SQL Programming Manual for Pascal—528614-001 viii
What’s New in This Manual Manual Information HP NonStop SQL Programming Manual for Pascal Abstract This manual describes the programmatic interface to HP NonStop™ SQL for the Pascal programming language. The NonStop SQL relational database management system (RDBMS) uses the structured query language (SQL) to describe and manipulate data in NonStop SQL databases. This manual is intended for application programmers who are coding embedded SQL statements in a Pascal program.
What’s New in This Manual New and Changed Information HP NonStop SQL Programming Manual for Pascal—528614-001 x
About This Manual This manual describes: • • • The programmatic interface to NonStop SQL for Pascal How to embed SQL statements and directives in a Pascal program How to call the SQL system library procedures The NonStop SQL relational database management system (RDBMS) uses the structured query language (SQL) to define and manipulate data in NonStop SQL databases. NonStop SQL provides both a conversational and a programmatic interface.
Related Manuals About This Manual Appendix A Describes the sample NonStop SQL database that is used for the programming examples. Appendix B Provides two programming examples that use static SQL operations. Appendix C Provides two programming examples that use dynamic SQL operations. Appendix D Describes version issues between NonStop SQL release 1 and release 2. Appendix E Discusses using constraints and referential integrity checking to ensure data is consistent in a NonStop SQL database.
Notation Conventions About This Manual Figure i.
About This Manual General Syntax Notation General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: MAXATTACH lowercase italic letters. Lowercase italic letters indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name computer type.
About This Manual General Syntax Notation | Vertical Line. A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example: INSPECT { OFF | ON | SAVEABEND } … Ellipsis. An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times.
About This Manual Notation for Messages !i,o. In procedure calls, the !i,o notation follows an input/output parameter (one that both passes data to the called procedure and returns data to the calling program). For example: error := COMPRESSEDIT ( filenum ) ; !i:i. !i,o In procedure calls, the !i:i notation follows an input string parameter that has a corresponding parameter specifying the length of the string in bytes.
About This Manual Notation for Management Programming Interfaces horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: proc-name trapped [ in SQL | in SQL file system ] { } Braces. A group of items enclosed in braces is a list of all possible items that can be displayed, of which one is actually displayed.
About This Manual !o. Notation for Management Programming Interfaces The !o notation following a token or field name indicates that the token or field is optional. For example: ZSPI-TKN-MANAGER token-type ZSPI-TYP-FNAME32.
1 Introduction NonStop SQL is the Tandem relational database management system that uses SQL to define and manipulate data in a NonStop SQL database. Because NonStop SQL is integrated with other Tandem software products, you can execute SQL statements interactively or programmatically. Using the SQL conversational interface (SQLCI), you can enter SQL statements and directives interactively from a terminal or from an OBEY command file.
Introduction Embedding SQL Statements Embedding SQL Statements You embed SQL statements by preceding each SQL statement with the keywords EXEC SQL and by terminating the statement with either a semicolon (;) or Pascal terminator if a terminator is appropriate for the context. The example below shows some embedded SQL statements in a Pascal source file: PROGRAM sample (input,output); ... ; { Pascal variable declarations } EXEC SQL BEGIN DECLARE SECTION; ...
Introduction Declaring Host Variables For more information, see Section 3, NonStop SQL Statements and Directives. Declaring Host Variables A host variable provides communications between Pascal statements and SQL statements in a program. A host variable is a Pascal variable with a data type that corresponds to an SQL data type. You use host variables in SQL statements to receive data from a database and to insert data into a database.
Introduction Using Dynamic SQL Figure 1-1 shows an example of static SQL statements embedded in a Pascal program. In this example, the program inserts a row into the PARTS table, which has the columns PARTNUM, PRICE, and PARTDESC. This example also declares data items as host variables so that they can contain input values for the columns of the row. Figure 1-1.
Introduction Compiling and Executing a Program The program in Figure 1-2 accesses the PARTS table, which exists on a different subvolume. When this program runs, it prompts an operator for information to build the INSERT statement. The operator enters the following information in the INTEXT variable to specify the location of the PARTS table and other values needed to construct the INSERT statement: INSERT INTO $VOL5.SALES.PARTS (PARTNUM, PRICE, PARTDESC) VALUES (4120, 60000.
Introduction Compiling and Executing a Program Figure 1-3. Compiling and Executing a Program Pascal/SQL Source File 1 RUN 2 RUN Pascal Compiler BINDER Program Run-Time Library Object File RUN 3 Accelerator Pascal Object RUN 4 5 SQL Compiler SQL Program Object File RUN VST0103.vsd To compile a Pascal program source file that contains embedded SQL statements, you: 1. Run the Pascal compiler to compile the Pascal statements in each source file of your program. 2.
Introduction Compiling and Executing a Program For more information, see Section 5, Program Compilation and Execution.
Introduction Compiling and Executing a Program HP NonStop SQL Programming Manual for Pascal—528614-001 1-8
2 Host Variables and Parameters This section describes host variables and parameters, two mechanisms that provide communication between Pascal and SQL statements in a Pascal program. Host Variables A host variable is a data item you can use in both Pascal and SQL statements to provide communication between the two types of statements. For static SQL programs, a host variable can be an input or output variable (or both in some cases).
Host Variables and Parameters Declaring Host Variables nest the sections. The SQL directives that delimit a Declare Section are BEGIN DECLARE SECTION and END DECLARE SECTION. Use Pascal naming conventions for your host identifiers. Host variable names and indicator variable names can contain a maximum of 31 alphanumeric characters from the following categories: letters (A-Z, a-z), digits (0-9), and the underscore (_). The first character must be a letter, and the name cannot begin with an underscore.
Host Variables and Parameters Declaring Host Variables Table 2-1.
Host Variables and Parameters • • Declaring Host Variables When a host variable serves as an input variable (supplies a value to the database), the system first converts the value that the variable contains to a compatible SQL data type and then uses the value in the SQL operation. When a host variable serves as an output variable (receives a value from a database), the system converts the value to the data type of the host variable.
Host Variables and Parameters Declaring Host Variables J_SMALL_U SMALLINT UNSIGNED NOT NULL, K_INT_S INTEGER SIGNED NOT NULL, L_INT_U INTEGER UNSIGNED NOT NULL, M_LARGE_S LARGEINT SIGNED NOT NULL, N_DEC_S DECIMAL (18,2) SIGNED NOT NULL, O_DEC_U DECIMAL (9,2) UNSIGNED NOT NULL, P_COB_P9 PIC 9(9) COMP NOT NULL, Q_COB_PX PIC X(10) NOT NULL, Z_LONG PIC XXXXXXXXXXXXXXXXXXXX NOT NULL, A_DBL FLOAT (15) NOT NULL, B_DBL FLOAT (30) NOT NULL, C_DBL REAL NOT NULL, D_DBL DOUBLE PRECISION NOT NULL, A_DT_TIME DATETIME Y
Host Variables and Parameters e_dt_time end; Using Host Variables : fstring(7); Using Host Variables After you declare a host variable, you can use the variable in an SQL statement just as you would in a Pascal statement, except that you must precede the host variable name with a colon (:). The colon causes the Pascal compiler to treat the name as a host variable. If the host variable contains a date-time or INTERVAL value, include the TYPE AS clause when you use the variable.
Host Variables and Parameters Using Host Variables For inserting null values, the programmer sets the value of the indicator variable to any value less than zero. TYPE AS indicates that values in the host variable will have the specified date-time data type. A host variable that is to contain date or time values must be defined with a character data type.
Host Variables and Parameters Using Host Variables If the value in :host-variable is to be entered into a database using an INSERT or UPDATE statement, the program must assign a value to :host-variable that allows for the scale. For example, if the program is representing a price of $123.45, then the program should assign 12345 to :host-variable and use SETSCALE to specify a scale of 2.
Host Variables and Parameters • Using Host Variables Using SETSCALE with the SELECT statement for retrieving a value A program retrieves the value in the PARTS.PRICE column for a disk controller and stores the value in host variable :HOSTVAR3. The value has a scale of 2. EXEC SQL SELECT PARTS.PRICE INTO SETSCALE ( :HOSTVAR3, 2 ) FROM =PARTS WHERE PARTS.
Host Variables and Parameters Using Host Variables TYPE AS DATE TYPE AS TIME TYPE AS TIMESTAMP TYPE AS INTERVAL YEAR You can also use date-time and INTERVAL literals. For example, to insert a date-time or INTERVAL value using a literal into TABLE: EXEC SQL INSERT INTO TABLE (START_DATE ) VALUES DATE "1990-03-21"; Or, to insert the value using a host variable: { Variable declarations: } EXEC SQL BEGIN DECLARE SECTION; DATEVAR : FSTRING(9) ; EXEC SQL END DECLARE SECTION; ...
Host Variables and Parameters Using Host Variables If columns can contain null values, a program should use indicator variables when referring to those columns. A program might: • • • Insert null values into the database with an INSERT or UPDATE statement Retrieve null values from the database with a SELECT statement Specify null values in a WHERE clause in an UPDATE, DELETE, or SELECT statement You can also use indicator variables with the INSERT, UPDATE, DELETE, and SELECT statements.
Host Variables and Parameters Parameters :EMP_TBL.JOBCODE, :EMP_TBL.SALARY INDICATOR :IND_1); EXEC SQL INSERT INTO =EMPLOYEE VALUES (:EMP_TBL.EMPNUM, :EMP_TBL.FIRST-NAME, :EMP_TBL.LAST_NAME, :EMP_TBL.DEPTNUM, :EMP_TBL.JOBCODE, :EMP_TBL.SALARY INDICATOR :IND_1); You can also set the column to the null value using the NULL keyword.
Host Variables and Parameters Using Parameters in Loops Using Parameters in Loops Parameters are often used when a dynamic SQL statement is executed repeatedly with different input values. Example The user of the program described in the following example can enter any SQL statement—the program does not have compile-time information about the statement. A DEFINE named =PARTS is in effect for the PARTS table. The scenario includes these steps: 1.
Host Variables and Parameters Using Indicator Parameters parameter. The name of the indicator parameter follows the parameter name in the SQL statement. For example: INSERT INTO =employee VALUES (1000, ?p INDICATOR ?i ); If the user enters a null value for ?P, the program should set ?I to a value less than zero. If the user enters a nonnull value, the program should set ?I to 0. Both ?P and ?I appear in the names buffer to allow prompting the user for a null value.
3 NonStop SQL Statements and Directives This section describes SQL statements and directives that have specific considerations, data structures, or examples for the Pascal programmatic interface. All SQL statements, including their syntax, are described in the companion manual, SQL/MP Reference Manual. Embedding SQL Statements and Directives An SQL statement or directive embedded in a Pascal program must begin with the keywords EXEC SQL and end with a semicolon (;) or other Pascal statement terminator.
NonStop SQL Statements and Directives • Placing Statements and Directives in a Program A SQL statement or directive can span several lines in the source text. For example: EXEC SQL SELECT CUSTOMER.CUSTNAME INTO : CUSTOMER.CUSTNAME FROM SALES.CUSTOMER WHERE CUSTNUM = :FIND_THIS_CUSTOMER; • Use either SQL or Pascal comments in statements and directives. An SQL comment statement begins with a double hyphen (--) and ends with the end of the line.
NonStop SQL Statements and Directives Locating Information About SQL Statements and Directives Use these statements and directives anywhere a Pascal data declaration can appear: • • • • • BEGIN DECLARE SECTION and END DECLARE SECTION directives DECLARE CURSOR statement (static cursor only, global declarations only) INCLUDE SQLCA, INCLUDE SQLSA, and INCLUDE SQLDA directives INVOKE directive CONTROL TABLE, CONTROL QUERY, and CONTROL EXECUTOR directives Executable Statements Use these statements and direc
NonStop SQL Statements and Directives Locating Information About SQL Statements and Directives Table 3-1.
NonStop SQL Statements and Directives Locating Information About SQL Statements and Directives Table 3-1. Summary of NonStop SQL Statements and Directives (page 3 of 4) RELEASE SQL+Pascal Deallocates memory for a dynamic SQL statement referred to through a host variable. Transaction Control Statements Statement or Directive Manual* Description BEGIN WORK SQL Starts a TMF transaction. COMMIT WORK SQL Commits all database changes made during the current TMF transaction and frees resources.
NonStop SQL Statements and Directives BEGIN DECLARE SECTION and END DECLARE SECTION Table 3-1. Summary of NonStop SQL Statements and Directives (page 4 of 4) Error Checking Directives Statement or Directive WHENEVER Manual* Description SQL+Pascal ** Generates code that checks SQL statement execution for errors, warnings, and the no-rowfound condition.
NonStop SQL Statements and Directives INVOKE and SOURCE in the Declare Section ORDERS, ORDERNUM, DATE, or PARTCOST cannot be used as host variables because they are not declared within the Declare Section. { Variable declarations: } VAR SUPPLIER : INT16; TYPE ORDTYPE = RECORD ORDERNUM : INT16; DATE : FSTRING (6); END; ORD = ARRAY [ 1..
NonStop SQL Statements and Directives CONTROL Directives A cursor remains open until it is closed. If a static SQL program exits a procedure with an open cursor, procedures that execute later and that are within the same compilation unit can still refer to the cursor. If a dynamic SQL cursor is opened and used in one call to the procedure where it is declared, you can still use the cursor in subsequent calls without opening the cursor again. For more information on cursors, see SELECT on page 3-29.
NonStop SQL Statements and Directives Dynamic SQL Statements Dynamic SQL Statements Dynamic CONTROL directives are processed using a PREPARE statement and executed using an EXECUTE statement or processed and executed using an EXECUTE IMMEDIATE statement. A dynamic CONTROL directive affects only dynamic SQL statements that are prepared after the CONTROL directive in execution order. Note.
NonStop SQL Statements and Directives Dynamic SQL Cursors Dynamic SQL Cursors For dynamic SQL cursors, all statements referencing the cursor must be in the procedure where the cursor is defined. However, if you open the cursor and use it in one call to the procedure where it is defined, you can still use the cursor in subsequent calls without opening the cursor again. All Cursors At run time, all FETCH statements that specify the cursor must execute before the CLOSE statement.
NonStop SQL Statements and Directives DELETE WHERE (PARTDESC = :PARTS.PARTDESC) STABLE ACCESS FOR UPDATE OF PARTDESC, PRICE, QTY_AVAILABLE; For more information on cursors, see SELECT on page 3-29. DELETE The DELETE statement deletes one or more rows from a table or protection view. The number of rows deleted is determined by the WHERE clause.
NonStop SQL Statements and Directives Multirow Delete Operation Using a Single Statement statement deletes only one row of the EMPLOYEE table because each value in EMPNUM is unique. EMPNUM is the primary key of the EMPLOYEE table. { User enters a value for :hostvar_key to } { specify the employee to delete. } ...
NonStop SQL Statements and Directives Multirow Delete Operation Using a Cursor WHERE partnum BETWEEN 6400 and 6700 AND partcost > 1600.00; Multirow Delete Operation Using a Cursor To read and test a value before you delete a row, you must use a cursor. A cursor allows you to delete a set of rows one row at a time. You specify the set of rows with the SELECT clause in the DECLARE CURSOR statement.
NonStop SQL Statements and Directives DELETE WHERE CURRENT and Dynamic SQL Cursors DELETE WHERE CURRENT and Dynamic SQL Cursors For a dynamic SQL cursor, all statements referencing the cursor must be in the procedure where the cursor is defined; however, if you open the cursor and use it in one call to the procedure where it is defined, you can still use the cursor in subsequent calls without opening the cursor again. For more information on cursors, see SELECT on page 3-29.
NonStop SQL Statements and Directives Examples Examples This INSERT statement, you can substitute different values for the three parameters each time you execute the statement. INSERT INTO $VOL5.SALES.PARTS (PARTNUM, PRICE, PARTDESC) VALUES ( ?P1, ?P2, ? ) The INSERT statement shown is contained in a host variable. You can also declare host variables that will contain the parameter values. The following example executes the INSERT statement.
NonStop SQL Statements and Directives EXECUTE IMMEDIATE The following code uses the EXECUTE statement in the case where the parameter descriptions are unknown: { Declare the SQLDA and name it SDAI; declare the } { names buffer and name it NAMESINPUT. } EXEC SQL INCLUDE SQLDA (SDAI, 5, NAMESINPUT, 39); { 5 and 39 are arbitrary values for sqlda-size and } { name-string-size. Your program can use different values, } { or it can allocate memory dynamically.
NonStop SQL Statements and Directives Example The Pascal compiler dynamically generates the length for variables of type STRING. Example The Pascal code for an EXECUTE IMMEDIATE operation is: { Variable declarations: } EXEC SQL BEGIN DECLARE SECTION; VAR STATEMENT : STRING(300); EXEC SQL END DECLARE SECTION; ...
NonStop SQL Statements and Directives INCLUDE Directives INCLUDE Directives The INCLUDE SQLCA, INCLUDE SQLDA, and INCLUDE SQLSA compile-time directives declare the following data structures: • • • SQLCA—Stores error and warning information about executable SQL statements SQLDA—Stores information about input parameters and output variables for dynamic SQL statements SQLSA—Stores execution and performance statistics These directives must be between sections because declarations added to the program begin
NonStop SQL Statements and Directives INSERT and Access available. If you specify APPEND instead of ANYWHERE, the row is added to the end of the table. The SQLCODE values that NonStop SQL returns after an INSERT statement are: Value Meaning 0 The INSERT statement was successful. 100 No rows qualified for an INSERT through a SELECT specification. <0 An error occurred; SQLCODE contains the error number. > 0 (not 100) A warning occurred; SQLCODE contains the first warning number.
NonStop SQL Statements and Directives { SALARY : INT32; {* scale is 2 *} } { END; Inserting Date-Time and INTERVAL Values } ... VAR EMP_TBL : EMP_TBL_TYPE; IND_1 : INT16; ... EXEC SQL END DECLARE SECTION; { Procedure code: } IND_1 := -1; EXEC SQL INSERT INTO =EMPLOYEE VALUES (:EMP_TBL.EMPNUM, :EMP_TBL.FIRST-NAME, :EMP_TBL.LAST_NAME, :EMP_TBL.DEPTNUM, :EMP_TBL.JOBCODE, SETSCALE(:EMP_TBL.
NonStop SQL Statements and Directives Inserting a Timestamp Statements to supply values for the columns are: BILLING_DATE := '1988-10-20'; TIME_BEFORE_PMT := '90'; { TIME_BEFORE_PMT is 3 bytes long to provide space for a } { possible negative sign. You can assign any value of } { 3 bytes or less - for example, -90, 10, +5, 150 } ...
NonStop SQL Statements and Directives INVOKE EXEC SQL INSERT INTO T (A) VALUES (CONVERTTIMESTAMP (:DATE_TIME) ); EXEC SQL COMMIT WORK; END. INVOKE The INVOKE directive declares a view or a table as a type definition that corresponds one to one with the columns in an SQL table or view. INVOKE writes a Pascal data description for each column in the table or view. For views only, INVOKE includes the system-defined primary keys in the definition.
NonStop SQL Statements and Directives • INVOKE Guidelines If no record name is supplied, the compiler adds the _TYPE suffix to the unqualified table or view name and returns it as a type definition. Your program must then declare a variable of this type.
NonStop SQL Statements and Directives CITY STATE POSTCODE CREDIT END; Handling Columns of Date-Time and INTERVAL Types : : : : FSTRING(14); FSTRING(12); FSTRING(10); FSTRING(2); Your program must now declare storage for the record. The variable CUSTOMER has the TYPE definition generated by INVOKE: VAR CUSTOMER : CUST_TYPE; The following example shows a sample CREATE TABLE statement for an employee table.
NonStop SQL Statements and Directives Handling Columns of Date-Time and INTERVAL Types Intervals are represented as character strings with a separator between the values of the INTERVAL fields (year-month or day-time); an extra byte is generated at the beginning of the INTERVAL string for a sign. This byte can contain blanks, a plus, or a minus. You can insert or retrieve date-time values in any of three formats, independently of the SQL column definition.
NonStop SQL Statements and Directives INVOKE and Indicator Variables The invoked type in the Pascal program is: TYPE EMPLOYEE_TYPE = RECORD NAME : FSTRING(20); AGE : FSTRING(6); END ; The host variable reference is: :AGE TYPE AS INTERVAL YEAR(2) TO MONTH INVOKE and Indicator Variables INVOKE generates indicator variables for each host variable that corresponds to a column that can be null.
NonStop SQL Statements and Directives INVOKE With the NULL STRUCTURE Clause LAST_NAME CHARACTER(20) NO DEFAULT NOT NULL, RETIRE_DATE DATETIME YEAR TO DAY, PRIMARY KEY EMPNUM ) CATALOG \SYS.$VOL.TESTCAT; INVOKE statements on this table produce output as follows: the PREFIX clause causes INVOKE to generate indicator variables with the prefix IND_VAR; in the NULL STRUCTURE version, indicator variables are named INDICATOR.
NonStop SQL Statements and Directives INVOKE With the PREFIX and SUFFIX Clauses end; END; INVOKE With the PREFIX and SUFFIX Clauses This example specifies both a prefix and a suffix for indicator variables. The indicator variables generated are X_ZNUM_2 and X_ZCHAR_2. The CREATE TABLE statement that defines table TAB is: CREATE TABLE TAB ( ZNUM NUMERIC (6), ZCHAR CHARACTER(16), PRIMARY KEY ZNUM ) CATALOG \SYS.$VOL.
NonStop SQL Statements and Directives All Cursors one call to the procedure where it is defined, you can still use the cursor in subsequent calls without opening the cursor again. All Cursors At run time, the OPEN statement must execute before all FETCH statements, and the CLOSE statement must execute after all FETCH statements through the cursor. Any host variable used in a DECLARE CURSOR must be in scope at every instance of an OPEN or FETCH operation for that cursor.
NonStop SQL Statements and Directives Single-Row SELECT Single-Row SELECT A single-row SELECT is a request to return a single row to the program. The INTO clause returns the single row to one or more host variables. The row is typically identified by a unique key value or by a unique value of a column within the row. You can write a single SELECT statement to return the desired row, whether the identifying value is a key value or a non-key value.
NonStop SQL Statements and Directives POSTCODE Single-Row SELECT : FSTRING(10); END; VAR CUSTOMER : CUSTOMER_TYPE; FIND_THIS_CUSTOMER : INT16 := 0; EXEC SQL END DECLARE SECTION; PROCEDURE HANDLE_NOT_FOUND; FORWARD; EXEC SQL WHENEVER NOT FOUND CALL :HANDLE_NOT_FOUND; PROCEDURE HANDLE_NOT_FOUND; BEGIN ... END; {HANDLE_NOT_FOUND} PROCEDURE FIND_RECORD; BEGIN WRITELN('Please enter the customer number: '); READLN(FIND_THIS_CUSTOMER); EXEC SQL SELECT CUSTOMER.CUSTNAME, CUSTOMER.STREET, CUSTOMER.
NonStop SQL Statements and Directives Multirow SELECT FROM MYTABLE WHERE COLUMN1 = :HOSTVAR_KEY; In this example, the WHERE clause specifies that the selected row contains a primary key, COLUMN1, whose value is equal to the value of a specified host variable. Only one row is retrieved from the table because a unique primary key value is used for the selection. Selecting Date-Time and INTERVAL Values. In the following example, a table named PROJECTS is invoked from a database.
NonStop SQL Statements and Directives Multirow SELECT A cursor is the mechanism for dealing with a set of rows returned in sequence to a program. To use a cursor, you use the DECLARE CURSOR, OPEN, FETCH, and CLOSE statements as shown in the following steps. These steps are required even when only the next single row is needed and only one FETCH is done. 1. Name and define a cursor in a DECLARE CURSOR statement. The DECLARE CURSOR statement includes a SELECT statement to describe the rows to be returned.
NonStop SQL Statements and Directives SELECT Syntax Consideration for IN EXCLUSIVE MODE and IN SHARE MODE For dynamic SQL cursors, all statements referencing the cursor must be in the same procedure where the cursor was declared. A cursor remains open until it is closed. If a static SQL program exits a procedure with an open cursor, procedures that execute later and that are within the same compilation unit can still refer to the cursor.
NonStop SQL Statements and Directives SQL Instead, the statement should be worded like this: EXEC SQL SELECT A INTO :NAME FROM T WHERE A > :NAME2 EXCLUSIVE MODE; SQL The SQL directive causes the Pascal compiler to process SQL statements and directives. The SQL directive must appear as the first line of the primary source file or in the command line when you run the Pascal compiler. ?SQL [ option ] [ ( option [ , option ] ...
NonStop SQL Statements and Directives UPDATE SQLMAP | NOSQLMAP specifies adding information to the listing to enable you to determine corresponding SQL statements from MEASURE output. A MEASURE report for an SQL statement identifies the statement by the name of its run-time data unit (RTDU) and by its index in the section location table (SLT). An RTDU is an internal SQL data structure that resides in the code file and contains the SQL object generated for each procedure or subprocedure.
NonStop SQL Statements and Directives • UPDATE and Locks A set of rows A set of rows, one row at a time using a cursor (a mechanism for dealing with a set of rows returned in sequence to a program) In general, specifying an update transaction in a program is the same as specifying an update transaction in SQLCI. UPDATE and Locks A lock on an updated row is held until the TMF transaction is committed or rolled back (audited table) or until the program releases the lock (nonaudited table).
NonStop SQL Statements and Directives Single-Row Update Operation Single-Row Update Operation A single-row update operation is a request to update a single row in a table. The following example updates a single row of the ORDERS table that contains information about order number 200038. The example declares the host variables :ORDERS and :NEWDATE, which serve as input variables in the UPDATE statement.
NonStop SQL Statements and Directives Multirow Update Operation Using a Single Statement The following example changes an employee’s number. The employee is the manager of a department. Because EMPNUM is a primary key of the EMPLOYEE table, the code must delete the employee’s row and insert a row with the new number. The code must also update the DEPT table to change the MANAGER column to the employee's new number.
NonStop SQL Statements and Directives Multirow Update Operation Using a Cursor The following statement updates several rows of the CUSTOMER table to revise each customer’s credit rating to A1. EXEC SQL UPDATE SALES.CUSTOMER SET CREDIT = "A1" WHERE CUSTNUM IN (21, 3333, 324); In the next example, the salary of each employee working for a department that is located in San Francisco is increased by 10 percent.
NonStop SQL Statements and Directives Multirow Update Operation Using a Cursor OR DEPTNUM = :DEPTNUM_UPD FOR UPDATE OF DEPTNUM; EXEC SQL FETCH CHECK_EMP INTO :DEPTNUM; { Program logic to test the data } ... EXEC SQL UPDATE EMPLOYEE SET DEPTNUM = :NEWDEPT WHERE CURRENT OF CHECK_EMP; The following example also uses a cursor to position in the PARTS table on the part number specified by host variable STARTING_PARTNUM. The program can then fetch rows and determine whether to update data in the columns.
NonStop SQL Statements and Directives Updating Columns With Null Values Updating Columns With Null Values The following example updates a column by setting the column to a null value using an indicator variable. This example uses the TACL DEFINE name =EMPLOYEE for the EMPLOYEE table. { Variable Declarations: } EXEC SQL BEGIN DECLARE SECTION ; EXEC SQL INVOKE =EMPLOYEE AS EMPTBL_TYPE ; VAR EMPTBL : EMPTBL_TYPE; IND_1 : INT16; EXEC SQL END DECLARE SECTION ; ...
4 NonStop SQL System Procedures Use the provided TAL system library procedures to perform various SQL functions. These procedures are declared in the PEXTDECS file. Table 4-1 describes the NonStop SQL system procedures by function. The FILEINQUIRE procedure is described in the Guardian Procedure Calls Reference Manual. Table 4-1.
NonStop SQL System Procedures Procedure Descriptions Table 4-1. NonStop SQL System Procedures (page 2 of 2) Determining the Catalog for a Table, Index, or View Procedure Description FILEINQUIRE (NonStop Kernel operating system procedure) Returns information about a file. The input codes 13 and 14 apply specifically to SQL objects. Procedure Descriptions These NonStop SQL system procedures are described in this subsection. Each description includes the syntax, parameter definitions, and guidelines.
NonStop SQL System Procedures SQLCADISPLAY NonStop SQL communicates error and statistics information to a program through the SQLCA data structure. However, because the SQLCA contains information in a form that is not suitable to display, you must call the SQLCADISPLAY procedure to convert this information to a suitable format.
NonStop SQL System Procedures SQLCADISPLAY sql-msg-file-number input/output INT16 contains the file number of the SQLMSG file. If you pass -1 on input, the system opens the SQLMSG file and returns the resulting file number. If you pass a value other than -1, the system uses that value as the file number of the SQLMSG file.
NonStop SQL System Procedures SQLCADISPLAY statistics input CHAR controls the writing of statistics. The values you can specify and their meanings are: Y Display row and cost statistics if the value returned to the SQLCA in the ROW or COST field is greater than or equal to 0 R Display rows only C Display cost only N Display no statistics The default is Y. caller-error-loc input CHAR controls displaying the program name and line number of the SQL statement that received the error.
NonStop SQL System Procedures detail_params SQLCADISPLAY input (record) determines whether the program uses sequential I/O or Enscribe I/O for writing to the output file.
NonStop SQL System Procedures • SQLCAFSCODE Pascal programs that call SQLCADISPLAY must use the XADDR function to facilitate type matching between the SQLCA parameter and the formal type EXTADDR. The following example is for a typical call. Example Using all defaults, the SOURCE, INCLUDE SQLCA, and call statements for SQLCADISPLAY are: IMPORT BEGIN ?SOURCE $ volume. subvolume.PEXTDECS (SQLCADISPLAY) END; ... EXEC SQL INCLUDE SQLCA; ...
NonStop SQL System Procedures SQLCAGETINFOLIST first-flag input INT16 specifies whether the first or the last error is set in the SQLCA. You can set first-flag as follows: nonzero value (or omitted) First error 0 Last error Guidelines • • If the SQLCA is full when a file-system error occurs, the error is lost and cannot be returned by SQLCAFSCODE. Use SQLCAFSCODE to get the NonStop Kernel operating system and file-system error codes that occur for SQL operations.
NonStop SQL System Procedures • SQLCAGETINFOLIST Disk process (DP2) FUNCTION SQLCAGETINFOLIST TAL EXTENSIBLE ( sqlca: EXTADDR; itemlist: EXTADDR; number-items: INTEGER; result: EXTADDR; result-max: INTEGER; OPTIONAL error-index: INTEGER; OPTIONAL names-max: INTEGER; OPTIONAL params-max: INTEGER; OPTIONAL result-len: EXTADDR; OPTIONAL error-item: EXTADDR ) :INTEGER; sqlca input specifies the record name of the SQLCA from which information is to be retrieved.
NonStop SQL System Procedures names-max SQLCAGETINFOLIST input is the maximum length your program will allow for procedure IDs or file names (item codes 9, 13, and 19). Names that exceed this length are truncated (no error results from the truncation). params-max input is the maximum length your program will allow for parameter information (item codes 16 and 29). Parameter information that exceeds this length will be truncated (no error results from the truncation).
NonStop SQL System Procedures SQLCAGETINFOLIST Table 4-2. SQLCAGETINFOLIST Procedure Item Codes (page 2 of 3) Item Code Size (in bytes) Description 10 2 Syntax error location; if there was no syntax error, SQL returns –1. 11 2 Maximum length, in bytes, of the system procedure that sets the first error or warning. 12 2 Actual length, in bytes, of the system procedure that sets the first error or warning.
NonStop SQL System Procedures SQLCAGETINFOLIST Table 4-2. SQLCAGETINFOLIST Procedure Item Codes (page 3 of 3) Item Code Size (in bytes) Description 27 2 Sequence in which the error or warning was set. 28 2 Size of the buffer that contains parameters. Each string is delimited by a zero. 29 (in item code 28) Buffer that contains parameters, delimited by a 0. Each parameter begins on an even word boundary and is preceded by 2 bytes. Table 4-3 lists the SQLCAGETINFOLIST error codes. Table 4-3.
NonStop SQL System Procedures SQLCAGETINFOLIST TYPE ERR_WARN_TYPE = RECORD NAME_LEN : INT16; NUM_ERRS : INT16; NAME : FSTRING(MAX_NAME_LEN); ERR_CODE : INT16; END; { { { { 2 bytes } 2 bytes } 30 bytes } 2 bytes } VAR ERR_WARN_RESULT : ERR_WARN_TYPE; { Include the SQLCA declaration: } EXEC SQL INCLUDE SQLCA; { Declare a variable to hold the return code for the call: } VAR CALL_ERROR : INT16; { Declare a variable to hold the index of the entry for } { which you want information: } VAR ERROR_ITEM: INT16
NonStop SQL System Procedures SQLCATOBUFFER 1. Call SQLCAGETINFOLIST, passing item code 6 (maximum length of procedure name) 2. Call SQLCAGETINFOLIST again, passing a buffer of the appropriate size. SQLCATOBUFFER The SQLCATOBUFFER procedure writes to a buffer the error or warning messages that NonStop SQL returns to the application program. The buffer is a record area declared in the variable declarations in the Pascal program.
NonStop SQL System Procedures SQLCATOBUFFER first-record-number input INT16 is the ordinal number of the error record (line) to be moved to the output buffer. The procedure discards any error records with a lower number. If the call statement omits this parameter, the value 1 is the default. The count of lines moved begins with 1. To obtain more than one error record, you must increment the value in firstrecord- number. See Examples on page 4-18.
NonStop SQL System Procedures SQLCATOBUFFER To improve the performance of a program that makes multiple calls to the SQLCADISPLAY or SQLCATOBUFFER procedures, specify a variable containing -1 on the first call and then use the returned file number for subsequent calls. By using the file number, the system opens the file only once and uses the file number for subsequent calls; otherwise, the system opens the file for each call. errors input CHAR controls the writing of error messages.
NonStop SQL System Procedures SQLCATOBUFFER caller-error-loc input CHAR controls the writing of the program name and line number of the SQL statement that received the error. The values the program can specify and their meanings are: Y Enable writing the information N Suppress writing the information The default is Y. internal-error-loc input CHAR controls the writing of the location in system code where the first error in the SQLCA occurred.
NonStop SQL System Procedures • • SQLCATOBUFFER SQLCATOBUFFER works by starting with the first-record-number indicated to move output lines to the record area until all error messages are moved or until the text fills the record area. SQLCATOBUFFER returns to output-records a count of the lines moved to the buffer. If an overflow occurs, the procedure sets the more flag to Y.
NonStop SQL System Procedures SQLGETCATALOGVERSION SQLGETCATALOGVERSION The SQLGETCATALOGVERSION procedure returns a value that indicates the version of an SQL catalog. FUNCTION SQLGETCATALOGVERSION TAL EXTENSIBLE (OPTIONAL catalogname : EXTADDR; EXTREF VAR sqlversion : INT16) : INT16; catalogname input EXTADDR specifies the fully qualified file name of the catalog for which you are requesting information.
NonStop SQL System Procedures SQLGETOBJECTVERSION objectname input EXTADDR specifies the fully qualified file name of the SQL object for which you are requesting information. The name must be: • • Left justified and padded with blanks No longer than 34 characters sqlversion output INT16 is the version of the SQL object.
NonStop SQL System Procedures • • • SQLGETSYSTEMVERSION NO AUDITCOMPRESS attribute HEADING attribute HELP TEXT attribute These three attributes, however, are considered Version 2 features by the catalog manager process, which handles DDL statements. The tables and views that use these features must be handled by DDL statements executed on a NonStop SQL C30 system.
NonStop SQL System Procedures SQLSADISPLAY SQLSADISPLAY The SQLSADISPLAY procedure displays the execution statistics of SQL statements. However, SQLSADISPLAY does not display an SQLSA structure produced by a PREPARE statement. PROCEDURE SQLSADISPLAY TAL EXTENSIBLE ( sqlsa: EXTADDR; OPTIONAL sqlca: EXTADDR; OPTIONAL out_file_num: INT16; OPTIONAL detail_params: EXTADDR ); sqlsa input EXTADDR is a pointer to the SQLSA to be displayed. The SQLSA is included in the program with the INCLUDE SQLSA directive.
NonStop SQL System Procedures detail_params SQLSADISPLAY input (record) determines whether the program uses sequential I/O or Enscribe I/O for writing to the output file.
NonStop SQL System Procedures SQLSADISPLAY Figure 4-1. SQLSADISPLAY Display Format SQL statistics @ \ system.$ vol. subvol. file.# line Table Name Records Records Disc Accessed Used Reads process cpu,pin Message Message Bytes Count Lock WE VST0401.vsd Table 4-5 describes the elements shown in Figure 4-1. Table 4-5. SQLSADISPLAY Procedure Display Elements Element Description \ system.$ vol. subvol.
NonStop SQL System Procedures SQLSADISPLAY The information that SQLSADISPLAY displays at the home terminal is: SQL statistics @ \sanfran.$mamoth.a.program.#333.2 process 12,255 Records Records Disc Message Message Lock Table Name Accessed Used Reads Count Bytes WE \a.$b.c.d 123 22 3 10 3245 \w.$x.y.z 9987231 1 99999 1 100 e \sanfran.$mamoth.accts.
NonStop SQL System Procedures HP NonStop SQL Programming Manual for Pascal—528614-001 4- 26 SQLSADISPLAY
5 Program Compilation and Execution This section describes the compilation and execution of a Pascal program that contains embedded SQL statements: • • • • • • • Compiling a Pascal source program including running the Pascal and SQL compilers and using the Binder program Using the EXPLAIN utility Determining SQL program file validity Understanding automatic SQL recompilation Maximizing local autonomy Executing an SQL program file Estimating memory use Figure 5-1 on page 5-2 shows the steps you follow to
Program Compilation and Execution Compiling a Pascal Program Figure 5-1. Compiling and Executing a Program Pascal/ SQL Run-Time Library Source File (s) Pascal Compiler 4 3 2 1 SQL Compiler Accelerator Binder Pascal/SQL Pascal/SQL Obejct File (s) Single Object File Pascal/SQL Single Object File Pascal/SQL Obejct File 5 Pascal Object Code Pascal Object Code SQL Source Statements SQL Source Statements Pascal TNS and TNS/R Object Code Run the object file SQL Source Statements VST0501.
Program Compilation and Execution Running the Pascal Compiler 5. Run the object file from a terminal using the TACL RUN (or RUND) command or from a process using a system procedure such as NEWPROCESS. Running the Pascal Compiler The Pascal compiler generates Pascal object code in the program file. To run the Pascal compiler, enter an implicit TACL RUN command at your TACL prompt or from an OBEY command file.
Program Compilation and Execution Using Binder If you do not specify the SWAP option, the compiler uses the volume on which it resides (normally $SYSTEM). In practice, this volume is frequently full and it is sometimes necessary to specify an alternative volume to ensure enough temporary disk space for the compilation. run-option is a TACL RUN command option, as described in the TACL Reference Manual.
Program Compilation and Execution Running the Accelerator Binder and issue the Binder command described here. For more information on using Binder with Pascal, see the Pascal Reference Manual. The command to invoke Binder is: BIND [ / [ IN command-file ] [, OUT list-file ] / ] [, run-options ... ] / ] command-file specifies a file that contains Binder commands. list-file specifies the file to receive output listings. run-options are the RUN command options documented in the TACL Reference Manual.
Program Compilation and Execution Running the SQL Compiler before you run the SQL compiler. For more information about the Accelerator, see the Binder Manual and DLL Programmer’s Guide for TNS/R Systems. Running the SQL Compiler The SQL compiler generates SQL object code in the Pascal object file. SQL compilation also verifies the SQL objects used in SQL statements and generates an optimized execution plan for each SQL statement.
Program Compilation and Execution Running the SQL Compiler You must run the SQL compiler on the same system on which object-file exists. If you do not specify a system or volume name, the SQL compiler uses your current default values. list-file is the destination to which 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, line printer). The syntax is: [\ system.
Program Compilation and Execution Running the SQL Compiler CURRENTDEFINES | STOREDDEFINES specifies the set of DEFINEs used to interpret DEFINE names in the SQL statements in the program file. CURRENTDEFINES selects the current set of DEFINEs for compiling the program. CURRENTDEFINES is the default. STOREDDEFINES selects the set of DEFINEs stored with the program the last time it was SQL compiled. This option applies only to programs that previously have been SQL compiled.
Program Compilation and Execution Running the SQL Compiler file-name is the destination to which the DEFINE listing is written in addition to the compiler listing. The file is an external-file as described for OUT list-file. OBEYFORM specifies writing the DEFINE list in an OBEY format so that you can enter an OBEY command from your TACL prompt to set the DEFINEs at run time or compile time. If you omit OBEYFORM, the listing is in the format displayed by the INFO DEFINE command.
Program Compilation and Execution Running the SQL Compiler NORECOMPILE directs the SQL compiler not to automatically recompile the program. (Therefore, if any of the preceding run-time conditions occur, the program cannot continue execution and would need explicit SQL compilation for revalidation.) RECOMPILEONDEMAND | RECOMPILEALL specifies whether the compiler should recompile an entire invalid program or only those SQL statements that require recompilation and are actually executed.
Program Compilation and Execution Running the SQL Compiler Statistics Information For the SQL compiler to determine the best execution plan, it needs the current statistics for any referenced tables. To provide these statistics, a database administrator can use the UPDATE STATISTICS statement, which writes statistical information about the tables in the catalog in which the table is registered. For more information, see the UPDATE STATISTICS statement in the SQL/MP Reference Manual.
Program Compilation and Execution • Running the SQL Compiler Reports error and warning conditions—The compiler reports error and warning conditions, as described in Interpreting SQL Compiler Messages on page 5-14. Program File Format The input program file to the SQL compiler can be a Pascal object file, a file generated by the Binder program, or a file previously updated by the SQL compiler. The SQL compiler adds header information to the file and SQL object code.
Program Compilation and Execution Running the SQL Compiler Figure 5-2. NonStop SQL Program File Format SQL Program File SQL Valid SQL Sensitive Compilation Timestamp File Label Information Generated by the SQL Compiler Pascal Object Code Object Code Generated by the Pascal Compiler SQL Source Statement 1 SQL Source Statement 2 . . . SQL Source Statements Copied by the Pascal Compiler SQL Source Statement n SQL Object Code Execution Plans Generated by the SQL Compiler VST0502.
Program Compilation and Execution Running the SQL Compiler does not apply to automatic SQL recompilation or dynamic SQL compilation. The syntax for the PARAM command for explicit SQL compilation is: PARAM [ parameter-name parameter-value ] [ , parameter-name parameter-value ]... parameter-name parameter-value are parameter name and value pairs.
Program Compilation and Execution Running the SQL Compiler Error Conditions. An error condition results from an invalid reference to an SQL object in an SQL statement. Examples of invalid references are incorrect column names or incompatible data types. If an error occurs, the SQL compiler produces a listing, but it does not record the program file in the catalog and does not validate it for execution. Dynamic SQL statements are not compiled during explicit SQL compilation.
Program Compilation and Execution • Running the SQL Compiler Insufficient information The SQL compiler did not have enough information to determine the validity of a statement. For example, an SQL statement refers to a table that is unavailable because the table is on an unavailable remote node. (Another example is an SQL statement that refers to a table that does not exist.
Program Compilation and Execution Using the EXPLAIN Utility Figure 5-3. Sample SQL Compiler Listing SQL Compiler - T9095C30 - (15JUN91) COPYRIGHT TANDEM COMPUTERS INCORPORATED 1987, 1988, 1989, 1990, 1991 DATE - TIME : 10/09/89 - 09:51:18 Options : NOFORCE, OBJECT, CURRENTDEFINES, RECOMPILE, RECOMPILEALL, NOEXPLAIN SQL - PROGRAM FILE = \SYS.$VOL1.PROGS.PTESTO SQL - PROGRAM CATALOG = \SYS.$VOL1.INVENT SQL - DEFAULT CATALOG = \SYS.$VOL1.INVENT SQL - Source File = $VOL1.PROGS.
Program Compilation and Execution Using the EXPLAIN Utility modifying a query would improve the performance of your program. The EXPLAIN utility has these report options: • • EXPLAIN PLAN report EXPLAIN DEFINES report Optimized execution plan:EXPLAIN PLAN report; Generating an EXPLAIN Report The EXPLAIN PLAN report applies only to SQL DML statements. A plan shows the strategy for executing a DML statement and includes optimized access paths, joins, and sorts.
Program Compilation and Execution Generating an EXPLAIN DEFINES Report Figure 5-6 on page 5-21 shows an example of an EXPLAIN PLAN report (and stored DEFINEs). Generating an EXPLAIN DEFINES Report The EXPLAIN DEFINES report shows the mapping of DEFINE names used in SQL DML, DCL, and DDL statements.
Program Compilation and Execution Generating an EXPLAIN DEFINES Report Figure 5-5. INFO DEFINE Format of the EXPLAIN DEFINES Report DEFINE NAME CLASS VOLUME CATALOG =_DEFAULTS DEFAULTS guardian-90-name guardian-90-name DEFINE NAME CLASS FILE define - name MAP guardian-90-name DEFINE NAME CLASS FILE define - name MAP guardian-90-name VST0505.vsd Figure 5-6 on page 5-21 shows an EXPLAIN report that includes both the execution plans and stored DEFINEs.
Program Compilation and Execution Determining Program File Validity Figure 5-6. EXPLAIN Utility Report SQL Compiler - T9095C30 - (15AUG89) COPYRIGHT TANDEM COMPUTERS INCORPORATED 1987, 1988, 1989 DATE - TIME : 09/24/89 - 09:18:15 Options : NOFORCE, NOOBJECT, STOREDDEFINES, RECOMPILE, RECOMPILEALL, EXPLAIN PLAN DEFINES, OBEYFORM SQL - PROGRAM FILE = \SYS1.$VOL1.PROGS.OXPLAIN SQL - PROGRAM CATALOG = \SYS1.$VOL1.INVENT SQL - DEFAULT CATALOG = \SYS1.$VOL1.
Program Compilation and Execution SQL Sensitive Program Files or on database objects used by the program cause a program file to become invalid. An invalid program file requires SQL recompilation because of changes to either itself, or the database, or both. The SQL compiler validates a program file after a successful explicit SQL compilation or after errors have occurred during the compilation with the FORCE option in effect.
Program Compilation and Execution Changes to Database Objects Changes to Database Objects When changes are made to database objects (such as tables, views, and indexes), a program file that uses these objects becomes invalid. Altering the database object requires a new execution plan for the program file.
Program Compilation and Execution Understanding Automatic SQL Recompilation NonStop SQL allows this discrepancy to provide local autonomy in database management operations. An invalid program that is erroneously marked valid is detected at run time by a timestamp check and automatically recompiled. Understanding Automatic SQL Recompilation Automatic SQL recompilation is the run-time compilation, invoked by the SQL executor, of an entire program file or a single SQL statement in a program file.
Program Compilation and Execution • • • • Predicting Automatic SQL Recompilation Table open time A new DEFINE at SQL load time An unavailable access path (index) The attempted execution of an uncompiled SQL statement SQL Load Time The following conditions can cause automatic recompilation at SQL load time: • The SQL VALID flag is set to N Automatic recompilation of an invalid program file occurs each time the program runs.
Program Compilation and Execution Understanding the Run-Time Timestamp Check recompile the statement. The SQL compiler then determines the best alternative access path (if such a path exists) to execute the statement. Only the affected statement is automatically recompiled when the access path becomes unavailable. Attempted Execution of an Uncompiled SQL Statement If the SQL executor encounters an uncompiled SQL statement, it invokes the SQL compiler to compile the statement.
Program Compilation and Execution Run-Time Recompilation Errors Figure 5-7. Run-Time Checks and Automatic SQL Recompilation SQL Object Code . . . CPU SELECT . . . FROM TABLEA . . . Timestamp PROGRAMA SQL Executor Object SQL Object Code (copy) ... SELECT . . . FROM TABLEA . . . Catalog Automatic SQL Recompilation Rewrites Area Timestamp PROGRAMA Catalog Timestamp TABLEA VST0507.
Program Compilation and Execution • Maximizing Local Autonomy If the SQL executor subsequently tries to execute the uncompiled statement, the SQL executor tries again to automatically recompile the statement. If the statement still causes a compilation error or warning, the SQL executor returns a run-time error message to the program. At run time, a program receives SQL compiler errors only when the SQL statement that produces the error executes.
Program Compilation and Execution Using DEFINEs The program running on \NEWYORK uses a DEFINE to associate the PARTS table with the first partition located at \NEWYORK: SET DEFINE CLASS MAP ADD DEFINE =PARTS, FILE \NEWYORK.$VOL1.SALES.PARTS If \CHICAGO is unavailable at compile time, the SQL compiler can still compile the program because enough information is available in the catalogs on \NEWYORK where the first partition is registered.
Program Compilation and Execution Using the RUN Command from your TACL process; for information about executing a program file using system procedures, see the System Procedure Calls Reference Manual. Before running the program file, you can specify DEFINE, PARAM, and ASSIGN commands for run-time parameters and control. For information about these commands, see the TACL Reference Manual. NonStop SQL returns any run-time errors and warnings to the SQLCODE variable and the SQLCA if it is declared.
Program Compilation and Execution Guidelines for Using DEFINEs run-option is a run option of the RUN command, as described in the TACL Reference Manual. param-set is a list of one or more parameters to pass to the program, with multiple parameters separated by a space, as described in the TACL Reference Manual. Guidelines for Using DEFINEs A DEFINE is a named set of attributes and associated values stored in the process file segment (PFS) of a process.
Program Compilation and Execution Estimating Memory Use Before issuing the RUN command, you can specify TACL DEFINE commands that apply to SQL statements in the program. You can create, modify, delete, and display DEFINEs with TACL commands or NonStop Kernel operating system procedure calls. You can also specify the =_SORT_DEFAULTS DEFINE to control sort operations; however, if you modify a DEFINE after SQL load time, the change has no effect on static SQL statements.
Program Compilation and Execution • • Estimating Memory Use Count a host variable once per occurrence.
Program Compilation and Execution Estimating Memory Use HP NonStop SQL Programming Manual for Pascal—528614-001 5-34
6 Error and Status Processing NonStop SQL returns error and status information to an application program following the execution of each embedded SQL statement. NonStop SQL returns some information to the integer SQLCODE variable and more extensive information to these data structures: • • SQL descriptor area (SQLDA). The SQLDA contains information about input parameters and output variables in dynamic SQL statements. SQL statistics area (SQLSA).
Error and Status Processing Checking the SQLCODE Variable Declaring SQLCODE Declare SQLCODE in your program as an integer variable: VAR SQLCODE: INT16; If you omit SQLCODE, the compiler generates an "undeclared identifier" error. The SQLCODE variable must be declared within scope of the embedded SQL statement that you will execute. To ensure SQLCODE is always in this scope, declare SQLCODE as a global variable at the start of each module that contains embedded SQL statements.
Error and Status Processing Using the WHENEVER Directive WARNING_SUM := WARNING_SUM + 1; SQLCADISPLAY ( XADDR (SQLCA),,,,'N','Y'); END ; {HANDLE_WARNINGS} PROCEDURE PERFORM_WORK ; BEGIN { Check for errors and warnings before beginning: } IF SQLCODE < 0 THEN HANDLE_ERRORS ELSE IF (SQLCODE > 0) AND (SQLCODE <> 100) THEN HANDLE_WARNINGS ; { Start a TMF transaction: } EXEC SQL BEGIN WORK ; { Do an SQL INSERT into the PARTS table:} IN_PARTS_REC.IN_PARTNUM := 4120; IN_PARTS_REC.
Error and Status Processing Using the WHENEVER Directive You can specify a WHENEVER directive anywhere in your program. The Pascal compiler inserts statements that perform run-time checking on the SQLCODE variable for all statements in listing order after the WHENEVER directive appears. The WHENEVER directive remains active for all statements that follow it in listing order until the program disables it.
Error and Status Processing Using the WHENEVER Directive EXEC SQL WHENEVER SQLERROR ; { Disables checking } Using the CALL Format If you use the CALL format of WHENEVER, follow these guidelines: • • • Specify the WHENEVER directive globally and precede the directive with a FORWARD declaration of the error handling procedure(s). Declare the procedure or subprocedure without parameters, and do not allow it to return a value.
Error and Status Processing Using the WHENEVER Directive Avoiding Infinite Loops You might need to disable the WHENEVER directive within an error handling procedure to avoid an infinite loop if the error handling code generates errors or warnings.
Error and Status Processing Using the WHENEVER Directive a “no rows found” condition, and the WHENEVER NOT FOUND directive fails to detect the condition. Example of Using WHENEVER Directives The program in the following example inserts two column values into the PARTS table and checks for errors using WHENEVER directives. The WHENEVER SQLWARNING and WHENEVER SQLERROR directives refer to parts of the program that handle the respective condition.
Error and Status Processing Getting Information From the SQLCA INSERT INTO SALES.PARTS (PARTNUM, PRICE, PARTDESC) VALUES (:IN_PARTS_REC.IN_PARTNUM, SETSCALE (:IN_PARTS_REC.IN_PRICE, 2), :IN_PARTS_REC.IN_PARTDESC) ; { End the TMF transaction: } EXEC SQL COMMIT WORK; END ; { PERFORM_WORK } Getting Information From the SQLCA NonStop SQL returns run-time information, including errors and warnings, for the most recently executed SQL statement to the SQL communication area (SQLCA).
Error and Status Processing Getting Execution Statistics Table 6-2.
Error and Status Processing • Declaring the SQLSA Each SELECT operation through a cursor reports statistics on the OPEN and CLOSE statements for the cursor, and on each FETCH operation. Dynamic SQL Statements • • Each PREPARE statement reports information about input parameters, output columns, and the input and output names buffer length.
Error and Status Processing Using the SQLSA Figure 6-1. SQLSA Record Description TYPE STATS_TYPE = RECORD table_name : FSTRING(24); records_accessed : LONGINT; records_used : LONGINT; disk_reads : LONGINT; messages : LONGINT; message_bytes : LONGINT; waits : INTEGER; escalations : INTEGER; sqlsa_reserved : FSTRING(24); END ; DML_TYPE = RECORD num_tables : INTEGER; stats : ARRAY [1..
Error and Status Processing Using the SQLSA Table 6-3. SQLSA Fields (page 2 of 3) Field Description num_tables Number of tables accessed by a DML statement. Maximum is 16. stats Array containing num_tables valid entries, one for each table accessed. table_name NonStop Kernel operating system 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.
Error and Status Processing Getting Information About Dynamic SQL Operations Table 6-3. SQLSA Fields (page 3 of 3) Field Description output_names_len Length of buffer required to contain names of output variables. name_map_len Reserved for system use. sql_statement_type Type of statement being prepared.
Error and Status Processing Declaring the SQLDA and Names Buffer Declaring the SQLDA and Names Buffer Use the INCLUDE SQLDA directive to declare a record description for the SQLDA in a Pascal program using dynamic SQL statements. The syntax is: INCLUDE SQLDA ( sqlda-name [ , sqlvar-count] [ , names-buffer-name, name-string-size ] [ , { RELEASE1 | RELEASE2 } ] ) sqlda-name is the SQLDA structure name.
Error and Status Processing Declaring the SQLDA and Names Buffer Figure 6-2 on page 6-15 and Figure 6-3 on page 6-16 show the record description generated by the INCLUDE SQLDA directive for NonStop SQL Release 1 and Release 2. Figure 6-2.
Error and Status Processing Declaring the SQLDA and Names Buffer Figure 6-3. NonStop SQL Release 1 SQLDA Record Description and Names Buffer CONST SQLDA_EYE_CATCHER [_R1 ] = 'DA'; TYPE SQLVAR_TYPE [_R1 ] = RECORD data_type : INTEGER; ( CASE INTEGER OF 1: (data_len : INTEGER); { fields for numbers } 2: (scale : BYTE; nlen : BYTE); ); null_info : INTEGER; var_ptr : EXTADDR; reserved : LONGINT; END ; SQLDA_TYPE [ _R1 ] = RECORD eye_catcher : FSTRING(2); num_entries : INTEGER; sqlvar : ARRAY [1..
Error and Status Processing Declaring the SQLDA and Names Buffer Table 6-4. SQLDA Fields (page 1 of 2) Field Name Description SQLDA_EYE_CATCHER Constant declared by the system. If you requested an SQLDA release option that is different from the release of the SQL compiler, SQLDA_EYE_CATCHER appears as either SQLDA_EYE_CATCHER_1_R1 or SQLDA_EYE_CATCHER_R2. sqlda_type Type definition for the SQLDA structure.
Error and Status Processing Guidelines for Declaring the SQLDA Table 6-4. SQLDA Fields (page 2 of 2) Field Name Description fraction_precision* Fraction precision for a date-time or INTERVAL column. null_info* For input parameters, contains a negative integer if the column could contain a null value. (To verify whether a column is null, check the ind_ptr field for a negative value.) For output columns, contains a negative integer if the row returned is null.
Error and Status Processing Data Type Literals Table 6-5 and Table 6-6 on page 6-20 list the literals. Table 6-5.
Error and Status Processing Data Type Literals Table 6-6.
Error and Status Processing Example of Declaring the SQLDA Table 6-7.
Error and Status Processing Example of Declaring the SQLDA 2: (scale : BYTE; nlen : BYTE); { fields for DATETIME or 3: (dt_qualifier : BYTE; dt_len : BYTE) ); ( CASE INTEGER OF 1: (precision : { fields for DATETIME or 2: (lead_field_precision : fraction_precision : null_info : INTEGER; var_ptr : EXTADDR; ind_ptr : EXTADDR; reserved : INT64; END ; INTERVAL } INTEGER); INTERVAL } BYTE; BYTE) ); SQLDA_TYPE = RECORD eye_catcher : FSTRING(2); num_entries : INTEGER; sqlvar : ARRAY [1..
7 Using Dynamic SQL Dynamic SQL is an advanced programming technique that allows an application program to construct, compile, and execute an SQL statement that is unknown until the program is executing. This section describes concepts that are important for understanding how dynamic SQL operations work. A static SQL statement appears in the Pascal source program at compile time. A program using static SQL statements receives input values from host variables and sends output values to host variables.
Using Dynamic SQL • • • Developing a Dynamic SQL Application You want to switch between several copies of identical databases. For this application, you use a dynamic SQL program with run-time DEFINEs. You want to restrict access to the data in a table. For example, the program might code an UPDATE statement for certain columns in a table, but allow the user to enter the selection criteria (WHERE clause) at run time. Your program must communicate with other software that communicates with the user.
Using Dynamic SQL Writing a Dynamic SQL Pathway Server You can use the predefined constants FILE_NUMBR, CONDITION_CODE, BYTE_COUNT, INTERNAL_FILENAME_TYPE, and OK_CONDITION to pass information to the GUARDIAN procedures. Except for constructing the SQL statement, the following tasks are not unique to servers using SQL. You perform these tasks in addition to the tasks you would perform for any dynamic SQL program: 2. Define storage for the messages to be received from the SCREEN COBOL requester. 3.
Using Dynamic SQL Specifying Input Parameters and Output Variables THEN STATEMENT := STATEMENT &' EMPNUM '; ... The statement now contains the string SELECT EMPNUM. You continue to construct the entire statement based on the values the user entered. 6. After the database request has been processed, construct the reply message. Instead of formatting and displaying the output values, you assign the values to the reply message.
Using Dynamic SQL Using the SQLDA and Names Buffer The following sequence shows a typical context for input parameters and output variables in dynamic SQL operations. If you know in advance which columns will be selected, you can use this sequence. HOSTVAR := 'SELECT EMPNUM, DEPT FROM =EMPLOYEE WHERE SALARY > ?SAL'; { ?SAL is the input parameter } ...
Using Dynamic SQL Using the SQLDA and Names Buffer ° An indication of whether the item could contain a null value in the NULL_INFO field ° The extended address of the input parameter or output variable in the VAR_PTR field ° The extended address of the associated indicator variable in the IND_PTR field When your program issues a DESCRIBE INPUT or DESCRIBE statement, the system supplies values for all the fields of the SQLDA except EYE_CATCHER, NUM_ENTRIES, VAR_PTR, and IND_PTR.
Using Dynamic SQL Dynamic SQL Programming Techniques display column names for output to the user, you should declare one or more names buffers. You can generate an SQLDA template and allocate a names buffer by entering the INCLUDE SQLDA directive. The generated SQLDA template has the format shown with INCLUDE SQLDA in Section 6, Error and Status Processing. Example.
Using Dynamic SQL • Overview of a Dynamic SQL Program Run-time memory allocation This subsection shows several methods to use dynamic SQL statements; it does not show the most efficient or only method to develop a particular application. Appendix C, Examples of Dynamic NonStop SQL Programs provides program examples that use dynamic SQL operations. Overview of a Dynamic SQL Program The following paragraphs describe a dynamic SQL program that handles any SQL statement and allocates memory at run time.
Using Dynamic SQL Overview of a Dynamic SQL Program OUTPUT_SQLDA_PTR... { pointer to output SQLDA } INPUT_NAMESBUF_PTR... { pointer to input names buffer } OUTPUT_NAMESBUF_PTR... { pointer to output names buffer } STATEMENT_BUFFER... { buffer for the statement typed } { in by the user } STATEMENT_HOSTVAR... { name of the statement that was } { entered --for use with PREPARE } { DESCRIBE, OPEN, FETCH, CLOSE } CURSOR_HOSTVAR...
Using Dynamic SQL Overview of a Dynamic SQL Program 1. Get the number of input parameters and the length of the names buffer (for parameter names) from the SQLSA structure (SQLSA.PREPARE.INPUT_NUM and SQLSA.PREPARE.INPUT_NAMES_LEN). 2. Allocate memory for the input SQLDA (and names buffer, if needed). This example uses the procedure ALLOCATE_SQLDA. 3. Initialize the SQLDA header fields (SQLDA_EYE_CATCHER is defined by the Pascal compiler): INPUT_SQLDA_PTR^.
Using Dynamic SQL Overview of a Dynamic SQL Program If NULL_INFO is 0, it is good practice to set IND_PTR to an invalid address. A recommended invalid address is %HFFFC0000%D, as shown in the detailed dynamic SQL program in Appendix C, Examples of Dynamic NonStop SQL Programs. 6. Loop through the names buffer to read the corresponding name for each parameter and prompt the user for each value.
Using Dynamic SQL Overview of a Dynamic SQL Program If you know the number and data type of your output column values, you can simply set DATA_TYPE, DATA_LEN, and VAR_PTR. Some programs might check DATA_TYPE and DATA_LEN again when the actual values are obtained. • • If you are handling null values, check SQLVAR.NULL_INFO. If NULL_INFO is 0, do nothing. If NULL_INFO is -1, allocate 2 bytes of memory for the indicator variable. If NULL_INFO is -1, set SQLVAR.
Using Dynamic SQL Overview of a Dynamic SQL Program Using a cursor host variable: EXEC SQL OPEN :CURSOR_HOSTVAR USING DESCRIPTOR :INPUT_SQLDA_PTR^; 5. Execute a loop to fetch the values and display them. Using a cursor name: EXEC SQL FETCH C1 USING DESCRIPTOR :OUTPUT_SQLDA_PTR^; Using a cursor host variable: EXEC SQL FETCH :CURSOR_HOSTVAR USING DESCRIPTOR :OUTPUT_SQLDA_PTR^; Display the values in a format according to data type.
Using Dynamic SQL Dynamically Allocating Memory Using a statement host variable: EXEC SQL EXECUTE :STATEMENT_HOSTVAR 8. End the TMF transaction (for both SELECT and other statements): EXEC SQL COMMIT WORK; 9. Deallocate the memory for the SQLDAs and names buffers and for the values (the example uses procedures DEALLOCATE_SQLDA and DEALLOCATE_SPACE to deallocate memory and declares HEAP_POINTER in the main code). The following paragraphs describe some of these steps in detail.
Using Dynamic SQL Dynamically Allocating Memory Using A Record Variant.
Using Dynamic SQL Dynamically Allocating Memory Example 1 To position a pointer past the header and onto the first SQLVAR record: 1. Make an INT32 version of the contents of SQLVAR_PTR to allow pointer arithmetic: T := RETYPE (SQLVAR_PTR, INT32); 2.
Using Dynamic SQL Dynamically Allocating Memory Figure 7-1.
Using Dynamic SQL Dynamically Allocating Memory Once at the beginning of the array, you can use the following statement repeatedly to position to subsequent SQLVAR records: SQLVAR_PTR.INT32_FIELD := SQLVAR_PTR.INT32_FIELD + SIZEOF(SQLVAR_TYPE); Accessing the SQLDA and Names Buffer. You can declare host variables for SQLDA and names buffer pointers in the main variable declarations as shown in Figure 7-2. MAXCHARS is a constant declared elsewhere in the program: Figure 7-2.
Using Dynamic SQL Dynamically Allocating Memory Figure 7-3. Using PREPARE to Compile a Statement CONST MAXCMD = 256; ... EXEC SQL BEGIN DECLARE SECTION; VAR STATEMENT_BUFFER : STRING(MAXCMD); EXEC SQL END DECLARE SECTION; ... WRITELN('Enter a new SQL statement:'); READLN(STATEMENT_BUFFER); ... STATEMENT_HOSTVAR := 'S1'; ... EXEC SQL PREPARE :STATEMENT_HOSTVAR FROM :STATEMENT_BUFFER; VST0703.
Using Dynamic SQL Dynamically Allocating Memory Allocating Memory for the SQLDAs and Names Buffers In preparation for allocating memory to store the SQLDA structure, you must get the number of input parameters or output variables from the SQLSA structure. To allocate memory for the SQLDAs and names buffers for the input and output variables, you use the MALLOC function. MALLOC (n) allocates a block of memory, n bytes in length, from the heap and returns the address of that block.
Using Dynamic SQL Dynamically Allocating Memory Figure 7-4. Preparing to Allocate the SQLDA NUM_INPUT_VARS := SQLSA.PREPARE.INPUT_NUM; {get the number of input parameters from the SQLSA} IF (NUM_INPUT_VARS > 0) THEN BEGIN INPUT_NAMESBUF_SIZE := SQLSA.PREPARE.INPUT_NAMES_LEN; { get the size of the input names buffer from the SQLSA} ALLOCATE_SQLDA(INPUT_SQLDA_PTR, NUM_INPUT_VARS, INPUT_NAMESBUF_PTR, INPUT_NAMESBUF_SIZE); END; VST0704.
Using Dynamic SQL Dynamically Allocating Memory Figure 7-5. Allocating the SQLDA and Names Buffer PROCEDURE ALLOCATE_SQLDA (VAR SQLDA_HEADER_PTR : SQLDA_HEADER_TYPE; NUMVARS : INTEGER; VAR NAMESBUF_PTR : CHAR_BUF_TYPE; NAMES_BUFFER_SIZE : LONGINT); VAR GENERIC_PTR : PTR_TO_ANY; SQLDA_SIZE : LONGINT; SIZE_TO_ALLOCATE : LONGINT; ...
Using Dynamic SQL Dynamically Allocating Memory columns (output variables). The following description shows how to handle input parameters. The program uses the DESCRIBE INPUT statement to fill in the SQLDA and names buffer with the descriptions of the input parameters in the SQL statement. If you specify NAMES INTO, the names of the parameters are also returned in the names buffer.
Using Dynamic SQL Dynamically Allocating Memory Literals have been defined to represent these data types. Some of the more common literals follow; for a complete list, see Section 6, Error and Status Processing.
Using Dynamic SQL Dynamically Allocating Memory Figure 7-6. Checking Data Type ... CASE DATA_TYPE OF SQLDT_ASCII_V : display a character string SQLDT_16BIT_U : display a 16-bit integer value SQLDT_32BIT_U : display a 32-bit integer value . . . check other data types OTHERWISE WRITELN('Data Type', DATA_TYPE, 'is not supported'); END; VST0706.vsd Figure 7-7 on page 7-26 shows sample code for allocating memory for input parameter values.
Using Dynamic SQL Dynamically Allocating Memory Figure 7-7. Allocating Memory for Values (page 1 of 2) ... { Declare pointers for an SQLVAR structure and for the names} { buffer.
Using Dynamic SQL Using the Names Buffer Figure 7-7. Allocating Memory for Values (page 2 of 2) { Initialize the loop counter; get NUM_INPUT_VARS from the { SQLSA: NUM_INPUT_VARS := SQLSA.PREPARE.
Using Dynamic SQL Using the Names Buffer might want to check for this blank padding. Expressions appear as a null string with a length of 0. For the program to determine the names in the names buffer, you can write a routine to return the VARCHAR structure for a name when given the index of the parameter information desired.
Using Dynamic SQL Using the Names Buffer Figure 7-8. Getting Parameter Values PROCEDURE PROMPT_USER(VAR NAMESBUF_PTR : PTR_TO_ANY); VAR COUNT : INTEGER; { loop counter } LEN : INTEGER: { length of a parameter name } BEGIN { Read the length of the first parameter name: } LEN := NAMESBUF_PTR.INT32_FIELD^; { Set the pointer past the length field and onto the { name field : NAMESBUF_PTR.INT32_FIELD := NAMESBUF_PTR.
Using Dynamic SQL Handling Output Variables ... END ; VAR P : PTR_TEST; ... P.EXTPTR := SQLDA.SQLVAR[i].VAR_PTR; IF P.LONG < 0 THEN { name does not fit } Handling Output Variables To allocate space for output variables, you perform essentially the same set of operations described for allocating space for input parameters except that the pointers point to the output SQLDA and names buffer.
Using Dynamic SQL Displaying Output 4. Read the DATA_TYPE field from the SQLVAR array to get the data type of the column value to be written. 5. Retrieve the value from the location pointed to by the VAR_PTR field in the SQLVAR array. Format the value and write it to output. The steps to use depend on the data type of the value.
Using Dynamic SQL Displaying Output Figure 7-9.
Using Dynamic SQL Using Dynamic Cursors Figure 7-9. Displaying Output (page 2 of 2) { Loop for each column: { For each column name in the names buffer, move the names { buffer pointer to point to the name, and write the name { to the output file, character by character. Then { display the value for that column. } } } } } FOR I := 1 TO NUM_COLUMNS DO BEGIN { Save the length field for the column, from the names { buffer: } LEN := NAMES_PTR.
Using Dynamic SQL Using Dynamic Cursors following paragraphs provide some points to consider when you use cursors. The order for executing statements to use a cursor with dynamic SQL operations is: PREPARE statement-name FROM : host-variable { Dynamically compile the } { SELECT statement defining } { the cursor } ...
Using Dynamic SQL Using Statement and Cursor Host Variables Using Cursors with an UPDATE WHERE CURRENT Clause To use UPDATE WHERE CURRENT with a static cursor, you 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 associated with the cursor. The following example shows an UPDATE WHERE CURRENT operation with a dynamic SQL cursor.
Using Dynamic SQL Handling Null Values { according to flag } END; EXEC SQL OPEN : cursor-host-variable; { Loop until SQLCODE = 100: } BEGIN EXEC SQL FETCH : cursor-host-variable INTO : columnhostvariables; { Display column values } END; EXEC SQL CLOSE : cursor-host-variable; Handling Null Values The input and output SQLDA structures have two fields, NULL_INFO and IND_PTR, that are used for handling null values.
Using Dynamic SQL Handling Null Values indicates a nonnull value, and gets the parameter value from the location VAR_PTR points to. Handling Null Values in Output Variables DESCRIBE sets NULL_INFO to -1 if the output variable can contain a null value—that is, if the prepared statement included a null indicator. If the value returned is null, SQL checks NULL_INFO and moves a -1 into the location pointed to by IND_PTR.
Using Dynamic SQL Handling Null Values 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 such as the following: 1. Check NULL_INFO. 2. If NULL_INFO is -1, read the length field for the indicator. 3. Add this length field plus 2 to the pointer or index to skip to the next name in the names buffer.
A Sample NonStop SQL Database This appendix describes the sample NonStop SQL database used by some of the examples in this manual. The SQL statements to create the database are shown in the SQL/MP Reference Manual. Figure A-1 shows the names of columns and tables and the relationships among the tables in the sample database. Figure A-1.
Sample NonStop SQL Database Sample Database Source File Sample Database Source File A source file for the database was created using INVOKE commands executed from SQLCI. For example, the INVOKE command for the DEPT table is: >>INVOKE PERSNL.DEPT FORMAT PASCAL TO SRCFILE (DEPT) ; Figure A-2 shows the source file containing the record descriptions of database tables. Figure A-2. Sample Database Source File (page 1 of 4) {* {* Personnel (PERSNL) {* {* ? SECTION EMPLOYEE {* Record Definition for \SYS1.$VOL1.
Sample NonStop SQL Database Sample Database Source File Figure A-2. Sample Database Source File (page 2 of 4) {* {* {* Sales (SALES) {* {* ?SECTION CUSTOMER {* Record Definition for \SYS1.$VOL1.SALES.CUSTOMER {* Definition current at 09:54:38 - 12/11/91 type customer_type = record custnum : cardinal; custname : fstring(18); street : fstring(22); city : fstring(14); state : fstring(12); postcode : fstring(10); credit : fstring(2); END; ?SECTION ORDERS p {* Record Definition for \SYS1.$VOL1.SALES.
Sample NonStop SQL Database Sample Database Source File Figure A-2. Sample Database Source File (page 3 of 4) ?SECTION PARTS {* Record Definition for \SYS1.$VOL1.SALES.PARTS {* Definition current at 09:54:43 - 12/11/91 type parts_type = record partnum : cardinal; partdesc : fstring(18); price : int32; {* scale is 2 *} qty_available : int32; END; {* {* {* Inventory (INVENT) {* {* ?SECTION SUPPLIER {* Record Definition for \SYS1.$VOL1.INVENT.
Sample NonStop SQL Database Sample Database Source File Figure A-2. Sample Database Source File (page 4 of 4) ?SECTION PARTLOC {* Record Definition for \SYS1.$VOL1.INVENT.PARTLOC {* Definition current at 09:54:48 - 12/11/91 type partloc_type = record loc_code : fstring(3); partnum : cardinal; qty_on_hand : int32; END; ?SECTION ERRORS {* Record Definition for \SYS1.$VOL1.INVENT.
Sample NonStop SQL Database Sample Database Source File HP NonStop SQL Programming Manual for Pascal—528614-001 A- 6
B Examples of Static NonStop SQL Programs This appendix provides these static NonStop SQL programming examples: • • Insertion program Date-time program For the NonStop SQL sample database used with these examples, see Appendix A. Insertion Program The insertion program on the following pages uses static SQL statements. The program inserts a part name, part number, and quantity into the PARTS table and then inserts a new part into the PARTLOC table.
Examples of Static NonStop SQL Programs Insertion Program The source file name is NEWPART and the object file name is POBJ. Tandem Pascal (T9256C30 01DEC90) COPYRIGHT TANDEM COMPUTERS INCORPORATED 1986,1987,1988,1989 1 2 3 4 5 6 7 ... 8 ... 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 1 2 3 4 5 6 7 8 9 29 1 2 3 4 5 6 7 8 9 10 11 30 1 2 3 4 5 6 7 8 0 0 0 0 0 0 0 ?SYMBOLS ?SQL NOWHENEVERLIST 0 ?SOURCE $system.system.
Examples of Static NonStop SQL Programs 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 1 2 3 4 5 6 7 56 57 58 59 60 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Insertion Program {* VAR DECLARATIONS: *} {* VAR declarations for host variables: *} VAR SUPPLIER_OF_PARTS : INTEGER := 8; PARTS_REC : PARTS_TYPE; SUPPLIER_REC : SUPPLIER_TYPE; PARTLOC_REC : PARTLOC_TYPE; EXEC SQL END DECLA
Examples of Static NonStop SQL Programs 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 0 0 1 2 2 2 2 2 2 0 0 0 1 2 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 0 1 2 2 2 2 2 2 2 2 2 2 2 2 0 0 Insertion Program PROCEDURE ABORT_TRANSACTION; FORWARD; PROCEDURE CHECK_SUPPLIER; FORWARD; PROCEDURE CLOSE_C
Examples of Static NonStop SQL Programs 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 Insertion Program 0 PROCEDURE DO_ADD_TO_PARTS (VAR IN_DATA_REC: IN_DATA_TYPE); 1 BEGIN 2 PARTS_REC.PARTNUM := IN_DATA_REC.IN_PARTNUM; 2 PARTS_REC.PARTDESC := IN_DATA_REC.IN_PARTDESC; 2 PARTS_REC.
Examples of Static NonStop SQL Programs 210 211 212 213 214 215 216 217 218 0 1 1 1 1 1 1 1 1 Date-Time Program BEGIN {* Main program *} START_OPERATIONS; CHECK_SUPPLIER; DO_ADD_TO_PARTS (IN_DATA_REC); DO_ADD_TO_PARTLOC (IN_DATA_REC); CLOSE_CURSOR; COMMIT_TRANSACTION; SUCCESSFUL_COMPLETION; END. {* Main program *} Date-Time Program The date-time program uses a table named PROJECTS, which is not a part of the sample database. The program therefore includes comments describing the columns of the table.
Examples of Static NonStop SQL Programs Date-Time Program Figure B-1. Date-Time Program Run (page 1 of 3) 14> SQLCI >SELECT * FROM =PROJECTS; PROJECT_NAME -----------------------920 134 START_DATE --------------------1988-02-21:20:30 1970-01-01:00:00 END_DATE -----------------------1989-03-21:20:30 1978-03-21:20:30 WAIT_TIME ----------------30 30 --- 2 row(s) selected.
Examples of Static NonStop SQL Programs Date-Time Program Figure B-1. Date-Time Program Run (page 2 of 3) WAIT TIME ADDED.
Examples of Static NonStop SQL Programs Date-Time Program Figure B-1. Date-Time Program Run (page 3 of 3) PLEASE ENTER: 1 -- to insert new project data 2 -- to add wait time to a project 3 -- to report original and new project dates 4 -- to exit the program 4 I DID IT ALL! TERMINATING NOW ....
Examples of Static NonStop SQL Programs 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 1 2 3 4 5 6 7 8 9 10 52 53 54 55 56 57 58 59 60 61 62 63 64 1 2 3 4 5 6 7 65 66 67 68 69 70 71 72 73 74 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 { { { { { Date-Time Program Invoke the PROJECTS table into 3 record areas for: } --storing the current dates } --storing the original dates } --storing the values entered by the
Examples of Static NonStop SQL Programs 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 0 0 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 0 0 1 2 2 2 2 2 2 131 132 2 2 133 134 135 136 2 2 2 2 Date-Time Program EXEC SQL DECLARE NEW_DATES_CURSOR CURSOR FOR SELECT PROJECT_NAME, START_DATE + WAIT_TIME, END_DATE
Examples of Static NonStop SQL Programs 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 4 4 4 4 2 2 2 0 0 0 1 1 1 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 4 4 4 4 2 2 0 0 0 0 0 0 0 Date-Time Program EXEC SQL INSERT INTO =PROJECTS (PROJECT_NAME, START_DATE, END_DATE, WAIT_TIME) VAL
Examples of Static NonStop SQL Programs 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 Date-Time Program 0 PROCEDURE PRINT_REPORT; 1 BEGIN 2 2 EXEC SQL BEGIN WORK; 2 2 EXEC SQL 2 OPEN OLD_DATES_CURSOR; 2 2 EXEC SQL 2 OPEN NEW_DATES_CURSOR; 2 2 IF SQLCODE = 0 THEN 3 BEGIN 4 WRITELN('*********************************************'); 4 WRITELN; 4
Examples of Static NonStop SQL Programs 251 252 253 254 255 256 257 258 259 260 261 262 263 0 1 2 2 2 2 2 2 2 2 2 3 4 264 4 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 4 4 4 2 0 0 0 1 2 2 2 2 2 2 2 2 2 2 2 3 4 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 Date-Time Program PROCEDURE FETCH_AND_DISPLAY_OLD; BEGIN EXEC SQL FETCH OLD_DATES_CURSOR INTO :OLD_PROJECTS_REC.PROJECT_NAME, :OLD_PROJECTS_REC.
Examples of Static NonStop SQL Programs 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 0 1 1 1 1 1 1 1 1 2 3 3 3 3 321 322 323 324 325 326 327 328 329 330 331 332 333 334 3 3 3 3 3 4 4 4 4 4 4 3 3 1 Date-Time Program BEGIN { main program } WRITELN; WRITELN(' START PROJECTS UPDATE PROGRAM '); WRITELN; SEL_INDEX := 0; 3 WHILE SEL_INDEX <> 4 DO BEGIN WRITELN; WRITELN ('PLEASE ENTER:'); WRITELN ('1 -- to insert new project data'); WRITELN ('2 -- to add wait time to a project'); WRITELN ('3
Examples of Static NonStop SQL Programs HP NonStop SQL Programming Manual for Pascal—528614-001 B-16 Date-Time Program
C Examples of Dynamic NonStop SQL Programs This appendix contains these two complete dynamic SQL programs: • • Simple program. The first program processes a SELECT statement that is partially coded into the program; the user supplies the WHERE clause. The SQLDAs and data buffers are allocated at compile time using the INCLUDE SQLDA directive. Detailed program. The second program allows the user to enter any SQL statement. The SQLDAs and data buffers are allocated at run time.
Examples of Dynamic NonStop SQL Programs Simple Dynamic SQL Program Sample output for the program is as follows: 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> HP NonStop SQL Programming Manual for Pascal—528614-001 C- 2
Examples of Dynamic NonStop SQL Programs Simple Dynamic SQL Program Code for the simple dynamic SQL program follows. Tandem Pascal (T9256C30 01DEC90) COPYRIGHT TANDEM COMPUTERS INCORPORATED 1986,1987,1988,1989 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 1 2 3 4 5 6 7 34 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 0 { This program finds the average salary for employees } 0 { according to criteria established by the user.
Examples of Dynamic NonStop SQL Programs 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 35 36 37 38 39 40 41 42 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 43 0 1 1 1 1 1 1 1 0 0 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 1 1 1 1 0 0 0 0 0 PREPARE_TYPE = RECORD input_num input_names_len output_num output_names_len name_map_len sql_statement_type END; Simple Dynamic SQL Program : : : : : : INTEGER; INTEGER; INTE
Examples of Dynamic NonStop SQL Programs 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 Simple Dynamic SQL Program 0 { Declare WHENEVER clause for error checking: } 0 PROCEDURE SQL_ERR; FORWARD; 0 EXEC SQL WHENEVER SQLERROR CALL :SQL_ERR; 0 0 { ----------------------------------------------------------- } 0 0 EXEC SQL BEGIN DECLARE SECTION; 0 PROCEDURE P
Examples of Dynamic NonStop SQL Programs 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 Simple Dynamic SQL Program 0 { ----------------------------------------------------------- } 0 0 PROCEDURE GET_CMD(VAR CMD: CMDSTR); 1 { Assigns a SELECT statement to the statement buffer.
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Detailed Dynamic SQL Program This program allows the user to enter any statement. The program prepares and executes the statement in a TMF transaction. The code is independent of any database because the program does not reference database definitions; only the entered statements reference a particular database.
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program (select ordernum from =odetail where partnum = 6400) ORDERS.ORDERNUM 200320 ORDERS.CUSTNUM 21 ORDERS.ORDERNUM 300350 ORDERS.CUSTNUM 543 ORDERS.ORDERNUM 800660 ORDERS.CUSTNUM 3210 ORDERS.ORDERNUM 400410 ORDERS.
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program The remainder of this appendix shows the program code. Tandem Pascal (T9256C30 01DEC90) COPYRIGHT TANDEM COMPUTERS INCORPORATED 1986,1987,1988,1989 1 2 3 4 5 6 7 0 0 0 0 0 0 0 ?list,nomap,xmem ?SQL ?symbols 8 0 9 0 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ...
Examples of Dynamic NonStop SQL Programs 54 55 56 57 1 2 3 4 5 6 7 58 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 59 0 0 0 0 0 0 1 1 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 0 0 1 1 1 0 0 1 1 1 1 1 1 1 0 0 1 1 1 1 1 1 0 0 0 Detailed Dynamic SQL Program {**********************************************************} {* Declare SQLCA and SQLSA.
Examples of Dynamic NonStop SQL Programs 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 Detailed Dynamic SQL Program 0 {**********************************************************} 0 {* Declare SQLDA. The SQLDA is explicitly declared to provide a *} 0 {* template for dynamic memory allocation for input and output *} 0 {* variables.
Examples of Dynamic NonStop SQL Programs 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 0 7 8 9 10 : : : : (sqldap (bufp (hp (xa Detailed Dynamic SQL Program : : : : P_SQLDA_HDR); P_CHAR_BUF); P_HEAP_HDR); EXTADDR); end; {**********************************************************} {* Declare an error control block to report errors.
Examples of Dynamic NonStop SQL Programs 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 0 0 0 0 0 0 0 0 0 0 Detailed Dynamic SQL Program {**********************************************************} {* Copy in external procedures for using large memory model. *} {* PASEXT file contains both MALLOC and FREE.
Examples of Dynamic NonStop SQL Programs 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 Detailed Dynamic SQL Program 0 {**********************************************************} 0 {* ALLOC_SPACE : Allocate the required number of bytes, nb, on *} 0 {* the current heap if there is sufficient space.
Examples of Dynamic NonStop SQL Programs 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 Detailed Dynamic SQL Program 0 {**********************************************************} 0 {* DEALLOC_SQLDA : Deallocate storage for a SQLDA on the heap.
Examples of Dynamic NonStop SQL Programs 295 296 297 298 299 300 301 302 303 304 305 2 3 4 4 4 4 5 6 6 6 4 306 307 308 309 310 311 312 313 314 315 5 4 4 4 4 4 4 5 6 6 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 6 7 8 9 9 9 6 6 5 5 7 7 6 6 7 8 8 7 7 4 4 2 2 0 0 0 0 0 0 0 346 0 347 348 349 350 351 352 353 0 0 1 2 2 2 0 Detailed Dynamic SQL Program for i := 1 to nc do begin { process each column ----1 } len := namp.
Examples of Dynamic NonStop SQL Programs 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 Detailed Dynamic SQL Program 0 {**********************************************************} 0 {* HANDLE_WARNING : If an SQL warning has been issued, display the *} 0 {* warning and set global warning condition.
Examples of Dynamic NonStop SQL Programs 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 Detailed Dynamic SQL Program 0 {**********************************************************} 0 {* SAME_CMD : Compare the input string (or a substring prefix) *} 0 {* with a known command string to decide whether they *} 0 {* are the same
Examples of Dynamic NonStop SQL Programs 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 Detailed Dynamic SQL Program 0 {**********************************************************} 0 {* PARSE_CMD : Parse the command string and return the following *} 0 {* tokens depending upon the input: *} 0 {* CMD_PREV_TYP : input is a PREVCMD string. *} 0 {* CMD_SQL_TYP : input is a SQL string.
Examples of Dynamic NonStop SQL Programs 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 0 0 0 0 0 0 0 1 1 1 1 2 2 2 3 3 4 4 4 4 5 5 4 2 3 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Detailed Dynamic SQL Program {**********************************************************} {* GET_CMD : Read the command input
Examples of Dynamic NonStop SQL Programs 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 3 3 3 3 3 3 4 4 4 5 6 6 5 5 5 5 5 5 5 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 8 9 9 9 9 9 9 9 9 Detailed Dynamic SQL Program begin {--Define actions to be taken when an error or warning occur
Examples of Dynamic NonStop SQL Programs 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 9 10 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 12 13 13 13 13 13 13 13 13 13 13 13 14 14 14 15 15 15 16 16 16 13 13 13 13 13 13 11 11 9 9 7 7 8 8 9 9 9 9 9 9 9 9 Detailed Dynamic SQL Program if (G
Examples of Dynamic NonStop SQL Programs 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 9 10 11 11 11 11 11 11 11 11 11 11 12 13 13 13 13 13 13 13 13 13 13 14 15 15 15 15 15 15 15 15 15 13 13 11 11 9 9 7 7 8 9 9 9 9 9 9 9 10 11 11 11 11 11 12 13 13 13 13 14 15 15 15 Detailed Dynamic SQL Program if
Examples of Dynamic NonStop SQL Programs 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 Detailed Dynamic SQL Program 15 {**********************************************************} 15 if (np > 0) then { some parameters specified } 16 EXEC SQL OPEN C1 USING DESCRIPTOR :isqlda_p^ 16 else EXEC
D NonStop SQL Version Issues The C30 release of NonStop SQL includes some features that are incompatible with the C10 release. Usually, these features provide for compatibility with SQL ANSI and ISO standards. If you are developing programs that will run in a mixed NonStop SQL C10 and C30 environment, you need to ensure that your programs accommodate the incompatible changes. Caution. If you are converting a C10 program, check the program for features that have incompatible changes.
NonStop SQL Version Issues • Version 1 and Version 2 Definitions Version 2 functions Exponentiation Date-time functions UPSHIFT function • Version 2 features Version 2 data types Version 2 functions Ability of columns to contain null values Clustering keys Constraints with constants using any Version 2 feature NO AUDITCOMPRESS attribute HEADING attribute HELP TEXT attribute UNION clause of the SELECT statement JOIN clause of the SELECT statement • • • • • Version 2 table—A table that uses any Version
NonStop SQL Version Issues Summary of C30 Changes Incompatible With C10 Table D-1.
NonStop SQL Version Issues Migrating an Application to Run on Release 2 (C30) Table D-2. Summary of Incompatible C30 Feature Changes C10 Program Operation Required Change DDL statements that create column definitions and omit the NOT NULL clause or the SYSTEM DEFAULT clause. Explicitly include the NOT NULL and SYSTEM DEFAULT clauses in the CREATE TABLE or ALTER TABLE ADD COLUMN statements to ensure the same column definition produced by C10.
NonStop SQL Version Issues Migrating an Application to Run on Release 2 (C30) default is DEFAULT NULL.
NonStop SQL Version Issues Migrating an Application to Run on Release 2 (C30) Check your programs for these words in SQL statements and change any that are present as correlation names, cursor names, SQL statement names, and column names. The Pascal compiler detects the use of these reserved words and issues a syntax error. Caution.
NonStop SQL Version Issues Migrating an Application to Access Version 2 Objects To use a C10 SQLDA, you can specify the SQL directive release option for the Pascal compiler or specify the RELEASE1 option in the INCLUDE SQLDA directive. Migrating an Application to Access Version 2 Objects If a program developed on C10 needs to access Version 2 objects or catalogs, consider the issues in the following discussion.
NonStop SQL Version Issues Recommendations for Installing Migrated Programs migrate a C10 program to access columns defined to allow null values or use Version 2 data types, you must Pascal compile and SQL compile the program with C30 compilers. Static SQL The SQLDA structure is generated automatically by the language compiler for static SQL. The SQLDA has additional fields in the C30 release to accommodate new data types.
NonStop SQL Version Issues SQL Component Compatibility SQL statements will follow C30 functioning when a C10 program runs on a C30 system. The recommended procedure for installing programs includes SQL compiling all SQL program object files with the C30 release of the SQL compiler. SQL compilation performs error checking for potential version problems. The steps to install migrated programs are as follows: 1. Alter all source program modules affected by incompatible C30 features. 2.
NonStop SQL Version Issues Developing C10 Applications with C30 Software Table D-4. SQL C10 Versioning IPM Summary (page 2 of 2) Component Name IPM Numbers Component Identification TSQLCI2 T9198C10^06MAR89 T9198AAA FASTSORT T9620C10^14FEB89 T9620AAB Note: For a node running NonStop Kernel operating system C10 with NonStop SQL C10, these versioning IPMs must be explicitly installed.
NonStop SQL Version Issues Programmatic Features for Version Control 3. SQL compile the resulting object file with the C30 SQL compiler. Note. Do not: • • Pass C10 SQLDA structures from C10/C30 modules to C30 modules Pass C30 SQLDA structures from C10/C30 modules to C10 modules Programmatic Features for Version Control The NonStop SQL programming interface provides several features to assist you in developing programs to handle mixed versions.
NonStop SQL Version Issues Programming Techniques for Mixed Versions SQLGETSYSTEMVERSION Returns a value that indicates the version of the SQL file system and disk process components on a given system. All other NonStop SQL components are considered to be the same version. For more information on the descriptions of these procedures, see Section 4, NonStop SQL System Procedures. For C10 programs to use procedures for handling mixed versions, program code must include the procedure declarations.
NonStop SQL Version Issues Generic Release Programs executing a catalog query, the program can use the SQLGETCATALOGVERSION procedure to check the SQL catalog version. The program executes the appropriate query depending on the catalog version. For example, if a program wants to determine whether a user table contains date-time columns and the attributes of those columns, the program must first determine whether the catalog in which the table is registered is a Version 2 catalog.
NonStop SQL Version Issues Using the Single-Code Thread Design Advantages of this technique are: • ° ° Dynamic SQL is not necessary. Program logic is simpler than in the single-code thread design. Maintain a single-code thread by using IF/THEN/ELSE statements in the source program; the statements test for the appropriate SQL version and choose between two code branches at run time.
NonStop SQL Version Issues Using the Single-Code Thread Design are represented as string text assigned to host variables in PREPARE statements and, therefore, are not processed at explicit SQL compilation time. • • If the program uses dynamic SQL, include multiple SQLDA structures. The program must declare both RELEASE1 format and RELEASE2 format SQLDAs and contain code to initialize both types of SQLDAs.
NonStop SQL Version Issues Using the Single-Code Thread Design HP NonStop SQL Programming Manual for Pascal—528614-001 D-16
E Enforcing Data Integrity Data integrity requires that certain data conditions must be true within a database. Examples of these conditions are: • • • • Data format, such as numeric only Value ranges, such as between 500 and 1000 A relationship between items within a row A relationship between items in different rows of a table or between rows in different tables (referential integrity) The data format is controlled by the data type definition in the CREATE TABLE statement.
Enforcing Data Integrity Managing Referential Integrity Constraints are also a replacement for program code; they operate for all programs that refer to a table to which constraints apply. For example, constraints can be used to establish value ranges for columns, true or false conditions, and so forth. Consider the following example: CREATE CONSTRAINT MGRNUM_CONST ON DEPT CHECK MANAGER BETWEEN 0001 AND 5000 ; This constraint on the DEPT table restricts the value of the column MANAGER to the range shown.
Enforcing Data Integrity Examples In this example, every department must report to another valid department in the table. To verify this rule, you can use the following SELECT statement to check the DEPT table: SELECT DEPTNUM, RPTDEPT FROM DEPT WHERE RPTDEPT NOT IN (SELECT DEPTNUM FROM DEPT) ; The query returns the following result: DEPTNUM RPTDEPT ------- --------- 0 row(s) selected. The absence of selected rows indicates the integrity of the database is intact.
Enforcing Data Integrity HP NonStop SQL Programming Manual for Pascal—528614-001 E- 4 Examples
Index A C Accelerator 5-2, 5-5 Access path local autonomy 5-28 RECOMPILE option 5-9 results when unavailable 5-25 SQL compiler function 5-11 valid programs 5-21 Access privileges for SQL compiler 5-10 Advantages of embedded SQL 1-1 Aggregate functions and WHENEVER 6-7 version issues D-5 Altering SQL file attribute, effect of 5-23 ANYWHERE clause with INSERT statement 3-18 APPEND clause with INSERT statement 3-19 Authority for program execution 5-31 Automatic SQL recompilation 5-24 C10 applications, migra
Index D Cursor CLOSE statement 3-7 closing 7-13 declaration 3-10 DECLARE CURSOR statement 3-9 DELETE statement 3-11 dynamic 3-8, 7-33 FETCH statement 3-17 initialize WHERE variable 3-33 OPEN statement 3-28 opening 7-12 PREPARE statement 3-29 RELEASE statement 3-29 SELECT statement 3-29 static 3-7, 3-9 UPDATE WHERE CURRENT statement 3-37 when to close 3-34 when to initialize 3-34 Cursor scope 3-33 D Data consistency 1-1 Data Control Language (DCL) statements 1-2 Data declaration, Pascal 3-1 Data Definitio
Index E DESCRIBE INPUT statement 2-13, 3-14, 7-7 DESCRIBE statement 3-14, 7-7 Directives, Pascal compiler 5-4 SQL 7-1 XMEM 7-8 Disk process (DP2) 4-2, 4-9, 4-14 Distributed database, maximizing local autonomy 5-28 DML statements See Data manipulation language (DML) DUPLICATE command, FUP 5-22 Duplicating SQL files, effect of 5-10 Dynamic memory allocation 7-14 Dynamic SQL operations and static SQL 7-1 applications for 7-1 compilation 5-14 declaring the names buffer 7-5 declaring the SQLDA 7-5 description
Index G File label program file validation 5-23 File number of SQLMSG file 4-4, 4-15 SQLCADISPLAY 4-3 FILEINQUIRE procedure 4-1 File-system errors SQLCADISPLAY 2-4, 4-2 SQLCAFSCODE 4-8 SQLCAGETINFOLIST 4-8 SQLCATOBUFFER 4-14 FIXED Pascal data type 3-22 FOR UPDATE OF clause UPDATE statement 3-40 FORCE option error messages 5-15 SQL compiler 5-15 FREE RESOURCES statement and cursors 3-34 FUP DUPLICATE command 5-22 G Generic release application, description D-12 GOTO format of WHENEVER 6-5 H HEADING attrib
Index L L Large-memory model 5-32 List file Pascal compiler 5-3 SQL compiler 5-6 Listings, SQL compiler 5-16 Literals for data types 6-18 for date-time ranges 6-20 for date-time values 6-20 Load time, SQL 5-24 Local autonomy maximizing 5-28 program execution 5-31 program file validity 5-21 using current statistics for 5-29 using DEFINEs for 5-29 Logical DEFINE See DEFINE M MALLOC procedure 7-14, 7-20 Maximizing local autonomy See Local Autonomy MEASURE program 3-36 Memory management dynamic allocation 7-
Index O NonStop SQL statements (continued) comments 3-2 COMMIT WORK 7-14 CREATE TABLE 2-4, 7-2 DECLARE CURSOR 3-3, 3-9, 3-40 DELETE 3-11 DESCRIBE 3-14 DESCRIBE INPUT 2-13, 3-14, 7-23 embedding 3-1 EXECUTE 1-4, 3-14, 7-6 EXECUTE IMMEDIATE 1-4 FETCH 3-17, 3-37, 3-40, 7-5 INSERT 2-8, 2-11, 3-18, 7-6 OPEN 3-28, 3-34, 3-37 placing 3-2 PREPARE 2-13, 3-29, 4-23, 5-15, 7-9 RELEASE 3-29 SELECT 2-8, 3-9, 3-29, 7-4 terminating 3-1 UPDATE 2-8, 3-36 UPDATE STATISTICS 5-15, 5-23, 5-29 NonStop SQL Statements (table) 1-2
Index Q Parameters example of 2-13 indicator 2-13 syntax for 2-12 Pascal compiler 3-2 syntax 5-3 WHENEVER pseudocode 6-4 Pascal run-time library 5-2 Pathway dynamic SQL server 7-2 error checking with requester 3-34 Performance 5-22 PEXTDECS file 1-3, 4-1, 6-18, 7-2, 7-24 Physical file attributes See File attributes PRECISION field, in SQLDA 7-5 PREFIX clause with INVOKE 3-26 PREPARE statement 2-13, 3-29, 4-23, 5-15, 7-9 Program dependencies send automatic SQL recompilation 5-24 Program execution for SQL p
Index S Sequential I/O (SIO) procedures 4-2, 4-3, 4-8, 4-14, 4-22 Set operations delete 3-12 update 3-39 SETSCALE function 2-4, 2-7, 3-23 Single row delete 3-11 update 3-38 Single-code thread design D-12 SKIP UNAVAILABLE PARTITION option CONTROL TABLE directive 5-29 Sort operations, using DEFINEs 5-32 SORTPROG process 4-2, 4-8, 4-14 Source file, Pascal compiler 5-2 SQL communication area See SQLCA SQL compilation automatic recompilation 5-24 dynamic SQL statements 5-14 error messages 5-15 EXPLAIN utility
Index T SQLSA data structure (continued) record description 6-10 SQLSADISPLAY procedure 4-22 SQLVAR structure, fields in 7-5 STOREDDEFINES option, SQL compiler 5-8 Subqueries, result D-6 SUFFIX clause with INVOKE 3-26 Swap file for SQL compiler 5-13 SWAPVOL parameter, PARAM command 5-14 SYSKEY with INSERT statement 3-18 System procedures FILEINQUIRE 4-1 MALLOC 7-14 T Table changes and program file validity 5-22 open time, automatic SQL recompilation 5-24 version 2 D-8 TACL RUN command 3-2, 5-29, 5-30 Tim
Index W W Warning message detection using WHENEVER 6-6 Warning messages, SQL compiler 5-14 WHENEVER directive 6-3, 7-9 disabling in sample program B-6 WHENEVERLIST option, SQL directive 3-36 WHERE clause with SELECT statement 3-30 WHERE CURRENT OF UPDATE statement 3-40 WHERE CURRENT OF clause, in DELETE statement 3-13 X XMEM directive 7-8 Special Characters $RECEIVE, opening 7-3 =_DEFAULTS DEFINE propagating 5-32 used by EXPLAIN utility 5-19 =_SORT_DEFAULTS DEFINE 5-32 HP NonStop SQL Programming Manual
