SQL Programming Manual for TAL

ManualsBrandsHP ManualsServerHP NonStop G-Series

HP NonStop SQL

Programming Manual

for TAL

Abstract

This manual describes the programmatic interface to HP NonStop™ SQL for the

Transaction Application Language (TAL). 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 TAL

program.

Product Version

NonStop SQL C30

Supported Release Version Updates (RVUs)

This publication supports D40.00 and all subsequent D-series RVUs and G06.00 and

all subsequent G-series RVUs until otherwise indicated by its replacement publication.

Part Number Published

527887-001 March 2004

Summary of content (330 pages)

PAGE 1
HP NonStop SQL Programming Manual for TAL Abstract This manual describes the programmatic interface to HP NonStop™ SQL for the Transaction Application Language (TAL). 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 TAL program.
PAGE 2
Document History Part Number Product Version Published N.A. NonStop SQL C30 September 1990 N.A.
PAGE 3
HP NonStop SQL Programming Manual for TAL Index Figures Tables What’s New in This Manual ix Manual Information ix New and Changed Information About This Manual xi Audience xi Organization xii Related Manuals xii Notation Conventions ix xv 1.
PAGE 4
3. NonStop SQL Statements and Directives Contents 3.
PAGE 5
. Program Compilation and Execution Contents 5.
PAGE 6
7. Dynamic NonStop SQL Operations Contents 7.
PAGE 7
D. NonStop SQL Version Issues (continued) Contents D. NonStop SQL Version Issues (continued) System Procedures D-11 Techniques for Mixed-Version Programming D-12 Handling Mixed-Version-Objects D-12 Running on Multiple NonStop SQL Releases D-13 Using the Single-Code Thread Design D-14 E. Enforcing Data Integrity Using Constraints E-1 Managing Referential Integrity E-2 Index Figures Figure i. Figure 1-1. Figure 1-2. Figure 1-3. Figure 4-1. Figure 5-1. Figure 5-2. Figure 5-3. Figure 5-4. Figure 5-5.
PAGE 8
Figures (continued) Contents Figures (continued) Figure A-1. Figure A-2. Figure B-1. Figure B-2. Figure C-1. Figure C-2. Figure D-1. Sample NonStop SQL Database Relations A-2 Sample Database Source File A-3 Insertion Program Output B-2 Date-Time Program Run B-12 Output for the Dynamic SQL Program C-2 Output for the Detailed Dynamic SQL Program C-11 Developing a Program For Mixed-Version Nodes D-15 Tables Table 1-1. Table 1-2. Table 2-1. Table 2-2. Table 2-3. Table 2-4. Table 3-1. Table 3-2. Table 3-3.
PAGE 9
Tables (continued) Contents Tables (continued) Table D-5. Table D-6.
PAGE 10
Contents HP NonStop SQL Programming Manual for TAL—527887-001 viii
PAGE 11
What’s New in This Manual Manual Information HP NonStop SQL Programming Manual for TAL Abstract This manual describes the programmatic interface to HP NonStop™ SQL for the Transaction Application Language (TAL). 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 TAL program.
PAGE 12
What’s New in This Manual New and Changed Information HP NonStop SQL Programming Manual for TAL—527887-001 x
PAGE 13
About This Manual This manual describes the programmatic interface to NonStop SQL for the Transaction Application Language (TAL). 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. Using the programmatic interface, a C, COBOL85, Pascal, or TAL programmer can use embedded SQL statements to access a NonStop SQL database.
PAGE 14
Related Manuals About This Manual Related Manuals You will probably want to use other books from the SQL/MP library set, shown in Figure i, in conjunction with this reference manual. The complete library includes: The SQL/MP Reference Manual describes the SQL/MP language elements, expressions, predicates, functions, and SQL statements that can be run in SQLCI. The manual also describes SQLCI commands and utilities.
PAGE 15
Related Manuals About This Manual Figure i.
PAGE 16
About This Manual Notation Conventions Notation Conventions The list summarizes the conventions used for syntax and programming examples in this manual. Notation Meaning UPPERCASE LETTERS Uppercase letters represent keywords and reserved words; enter these items exactly as shown. Uppercase letters in text also represent variable names that are used in programming examples. lowercase italic letters Lowercase letters in italics represent information you supply.
PAGE 17
About This Manual Hypertext Links Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: This requirement is described under Backup DAM Volumes and Physical Disk Drives on page 3-2. General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words.
PAGE 18
About This Manual General Syntax Notation braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example: LISTOPENS PROCESS { $appl-mgr-name } { $process-name } ALLOWSU { ON | OFF } | Vertical Line. A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example: INSPECT { OFF | ON | SAVEABEND } … Ellipsis.
PAGE 19
About This Manual Notation for Messages !i and !o. In procedure calls, the !i notation follows an input parameter (one that passes data to the called procedure); the !o notation follows an output parameter (one that returns data to the calling program). For example: CALL CHECKRESIZESEGMENT ( segment-id , error ) ; !i !o !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).
PAGE 20
About This Manual Notation for Management Programming Interfaces [ ] Brackets. Brackets enclose items that are sometimes, but not always, displayed. For example: Event number = number [ Subject = first-subject-value ] A group of items enclosed in brackets is a list of all possible items that can be displayed, of which one or none might actually be displayed.
PAGE 21
About This Manual Change Bar Notation lowercase letters. Words in lowercase letters are words that are part of the notation, including Data Definition Language (DDL) keywords. For example: token-type !r. The !r notation following a token or field name indicates that the token or field is required. For example: ZCOM-TKN-OBJNAME !o. token-type ZSPI-TYP-STRING. !r The !o notation following a token or field name indicates that the token or field is optional.
PAGE 22
About This Manual Change Bar Notation HP NonStop SQL Programming Manual for TAL—527887-001 xx
PAGE 23
1 Introduction The NonStop SQL relational database management system (RDBMS) uses the structured query language (SQL) to define and manipulate data in a NonStop SQL database. Because NonStop SQL is integrated with other HP NonStop 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 an OBEY command file.
PAGE 24
Introduction Developing a Program Developing a Program A TAL program that contains embedded SQL statements can use both static and dynamic SQL operations. A static SQL operation processes SQL statements that you embed and compile in the TAL source code. A dynamic SQL operation enables a program to construct, compile, and execute an SQL statement at run time.
PAGE 25
Introduction Declaring Host Variables Table 1-2 shows the NonStop SQL directives you can embed in a TAL program. Table 1-2.
PAGE 26
Introduction Using Static SQL Statements that you can include in your program. This example shows a call to the SQLCADISPLAY procedure using all default parameters. ?SOURCE $SYSTEM.SYSTEM.EXTDECS (SQLCADISPLAY) ... CALL SQLCADISPLAY (SQLCA); ! Process errors or statistics from the SQLCA structure ... For more information, see Section 4, System Procedures. Using Static SQL Statements You can embed static SQL statements in both TAL data declarations and TAL executable statements.
PAGE 27
Introduction Using Dynamic SQL Operations Figure 1-1. Static SQL Statements in a TAL Program ! TAL variable declarations: EXEC SQL BEGIN DECLARE SECTION; ! SQL host variable declarations STRUCT .in^parts^rec; BEGIN INT in^partnum; FIXED(2) in^price; STRING in^partdesc[0:17]; END; EXEC SQL END DECLARE SECTION; ... ! Procedure Code: in^parts^rec.in^partnum := 4120; in^parts^rec.in^price := 60000.00F; ! Blank fill in^partdesc: in^parts^rec.in^partdesc ':=' [ $OCCURS(in^parts^rec.
PAGE 28
Introduction Using Dynamic SQL Operations Figure 1-2 shows a dynamic SQL operation that uses an INSERT statement similar to the static INSERT statement in Figure 1-1 on page 1-5. In Figure 1-1 on page 1-5, the static INSERT statement is embedded in the source program code; however, in Figure 1-2, the program dynamically builds the INSERT statement from information entered by a user. The program in Figure 1-2 accesses the PARTS table, which has the same file name, but resides on a different subvolume.
PAGE 29
Introduction Compiling and Executing a Program Compiling and Executing a Program Compiling and executing a TAL program that contains embedded SQL statements is similar to the steps you use for a TAL program that does not contain embedded SQL statements. You must perform only one extra step: you compile the SQL statements using the SQL compiler. These steps are shown in Figure 1-3. 1. Compile your source file (or files) using the TAL compiler. The TAL compiler generates an object file for each compilation.
PAGE 30
Introduction Compiling and Executing a Program Figure 1-3. Compiling and Executing a Program TAL/SQL Source File (s) 1 Run the TAL compiler for each source file TAL Compiler 2 Run the Binder program (if necessary) 3 Run the Accelerator (optional step for TNS/R systems only) Binder Process Accelerator TAL/SQL 4 Run the SQL compiler SQL Compiler Object File 5 Run the object file VST0103.vsd For more information, see Section 5, Program Compilation and Execution.
PAGE 31
2 Host Variables and Parameters This section describes the data items that you use to provide for communication between TAL and SQL statements in a TAL program. Host Variables A host variable is a data item you can use in both TAL and SQL statements to provide for communication between the two types of statements. For static SQL operations, a host variable can be an input or output variable (or both in some cases) in SQL statements.
PAGE 32
Host Variables and Parameters Declaring Host Variables Declaring Host Variables You declare host variables in a Declare Section in the variable declarations of your program. You can use multiple Declare Sections in a TAL program, but you cannot nest the sections. The SQL directives that delimit a Declare Section are BEGIN DECLARE SECTION and END DECLARE SECTION. Use TAL naming conventions for your host variables.
PAGE 33
Host Variables and Parameters Using Corresponding SQL and TAL Data Types Table 2-1.
PAGE 34
Host Variables and Parameters Using Corresponding SQL and TAL Data Types Data Conversion NonStop SQL performs the conversion from SQL data types to TAL data types and from TAL data types to SQL data types as follows: • • 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.
PAGE 35
Host Variables and Parameters Overriding Default Data Types Overriding Default Data Types Each TAL data type has a default SQL data type used by the SQL compiler. In most cases, you can define your host variables to match the SQL data types shown in Table 2-1 on page 2-3. Sometimes, however, you might want to use another data type. Some TAL data types allow you to override the default type by specifying an override SQL data type as an attribute of the host variable.
PAGE 36
Host Variables and Parameters Using Host Variables Table 2-2.
PAGE 37
Host Variables and Parameters Using Host Variables variable. The syntax for using a host variable in an SQL statement is shown below. For a complete description of this syntax, see the SQL/MP Reference Manual.
PAGE 38
Host Variables and Parameters Using Host Variables Using Structures as Host Variables Follow the guidelines below when you declare and use structures or fields within structures as host variables. INVOKE Directive. Use the INVOKE directive to declare structure descriptions corresponding to SQL tables and views. The structure fields (which correspond to the columns in the table or view) become host variables.
PAGE 39
Host Variables and Parameters Using Host Variables Structure Pointers. Although you cannot use a pointer by itself as a host variable, you can refer to a field in a structure by using a structure pointer. For example, you can use this host variable declaration and reference: ! Declaration: EXEC SQL BEGIN DECLARE SECTION; STRUCT s^template(*); BEGIN INT field1; ! Fields cannot be pointers in SQL INT field2; END; STRING .
PAGE 40
Host Variables and Parameters Using Host Variables ! Reference to an integer within an array ! of integers in a structure: EXEC SQL SELECT integer^value INTO :outer^struct.int^array[1] FROM table1; ! A reference to an integer within nested structures: EXEC SQL SELECT integer^value INTO :outer^struct.nested^struct^1.nested^struct^2.int^1 FROM table1; ! A reference to a VARCHAR item: EXEC SQL SELECT character^string INTO :first^dim.second^dim.
PAGE 41
Host Variables and Parameters Using Host Variables To use the host variable INDIRECT^STRUCT, specify: EXEC SQL SELECT empname INTO :indirect^struct FROM people; If you make STRUCT^POINTER point to INDIRECT^STRUCT, you can also specify: EXEC SQL SELECT empname INTO :struct^pointer FROM people; Using Arrays as Host Variables Follow these guidelines when you declare and use arrays as host variables.
PAGE 42
Host Variables and Parameters Using Host Variables The declaration for the called procedure is: PROC process^cmd(struc); STRUCT .struc; BEGIN STRING val [0:maxsize-1]; END; ... BEGIN EXEC SQL PREPARE dyn^cmd FROM :struc.val; ... END; Declaring an Equivalenced Local Structure. Declare the parameter as a STRING array. In SQL statements that are within the scope of the STRING array's declaration, refer to the parameter using the STRING array name.
PAGE 43
Host Variables and Parameters Using Host Variables Using the FIXED Data Type. This method is the simplest method to handle scale; follow these guidelines when you use it: • The value stored in the FIXED host variable must fit into the database column. FIXED variables are stored in 64 bits and can hold 19 digits. You must ensure that the host variable never contains a value greater than will fit in the column; otherwise, NonStop SQL reports an error.
PAGE 44
Host Variables and Parameters Using Host Variables The SETSCALE function causes NonStop SQL to use :host-variable in the context of SQL statements as if :host-variable were declared with a scale of scale. For example, the sequence SETSCALE (hostvar, n) causes NonStop SQL to interpret the host variable hostvar as: hostvar * 10 - n 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.
PAGE 45
Host Variables and Parameters • Using Host Variables Using SETSCALE with the UPDATE statement A program updates the PARTS.PRICE column for a disk controller to $158.34. The value is multiplied by 100 and stored in the host variable :HOSTVAR2. ! Assign 15834 to :hostvar2 EXEC SQL UPDATE =parts SET parts.price = SETSCALE (:hostvar2, 2) WHERE parts.partdesc = "disk controller" ; • Using SETSCALE with the SELECT statement for retrieving a value A program retrieves the value in the PARTS.
PAGE 46
Host Variables and Parameters Using Host Variables Table 2-3.
PAGE 47
Host Variables and Parameters Using Indicator Variables for Null Values format you want and retrieving the value using the DATEFORMAT function. You should declare the host variable size to be consistent with the format you will use. If you use the INVOKE directive to generate host variables from an SQL table definition, you can specify the DATEFORMAT clause to create host variables of the appropriate size. For more examples of the INVOKE directive, see Section 3, NonStop SQL Statements and Directives.
PAGE 48
Host Variables and Parameters Using Indicator Variables for Null Values Inserting a Null Value These examples use indicator variables to insert a null value and to select from a column that can return a null value. the TACL DEFINEs =RETIREES and =SHIPMENTS are in effect for the respective tables.
PAGE 49
Host Variables and Parameters Using Indicator Variables for Null Values Selecting a Null Value This example selects data from the SHIPMENTS table and tests for null values using the indicator variable named PROD^REC.SHIP^IND: ! Variable declarations: EXEC SQL BEGIN DECLARE SECTION; STRUCT .prod^rec; BEGIN INT prodnum; INT ship^ind; STRING date^shipped[0:9]; END; EXEC SQL END DECLARE SECTION; ... EXEC SQL DECLARE c1 CURSOR FOR SELECT prodnum, date^shipped FROM =SHIPMENTS WHERE prodnum > 7999; ...
PAGE 50
Host Variables and Parameters Creating Host Variables With the INVOKE Directive Creating Host Variables With the INVOKE Directive You can use the INVOKE directive in a Declare Section to create host variables that correspond to the columns in an SQL table. INVOKE converts the column names to TAL identifiers and writes a TAL data description for each column. If a column allows a null value, INVOKE also generates an indicator variable for the column. Caution.
PAGE 51
Host Variables and Parameters Creating Host Variables With the INVOKE Directive The INVOKE directive as it appears in the source program is: EXEC SQL BEGIN DECLARE SECTION; EXEC SQL INVOKE typestbl AS types^rec; EXEC SQL END DECLARE SECTION; The CREATE TABLE statement that defines the table is: CREATE TABLE $vol.sales.
PAGE 52
Host Variables and Parameters Creating Host Variables With the INVOKE Directive The generated structure template is: struct types^rec(*); BEGIN string a^char[0:9]; struct b^varchar; begin int len; string val [0:9]; end; int c^num4^s; int d^num4^u /SMALLINT UNSIGNED/; int(32) e^num9^s; !scale is 2 int(32) f^num9^u /INTEGER UNSIGNED/; !scale is 2 fixed(2) g^num18^s; !scale is 2 int h^small^s; int j^small^u /SMALLINT UNSIGNED/; int(32) k^int^s; int(32) l^int^u /INTEGER UNSIGNED/; fixed(0
PAGE 53
Host Variables and Parameters Parameters Your program can now read the values stored in the columns of the SQL table into the host variables. For example: EXEC SQL SELECT typestbl.a_char, typestbl.a_dbl INTO :tbl.a^char, :tbl.a^dbl; Parameters A parameter is a place holder variable in an SQL statement that enables you to specify run-time input in a dynamic SQL statement. You compile the SQL statement without the input values and then supply the values when the statement is executed.
PAGE 54
Host Variables and Parameters Using a Parameter List TYPE AS ... indicates that NonStop SQL should treat the value entered for the parameter as a date-time or INTERVAL value. A parameter that represents a date-time or INTERVAL value must have a character data type. Using a Parameter List To ensure a one-to-one correspondence between a parameter list and the host variables that you use to supply values for the parameters, use unnamed parameters.
PAGE 55
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. In this example, a dynamic SQL statement uses a parameter. Because the user of this program can enter any SQL statement, the program does not have compile-time information about the statement. The TACL DEFINE named =PARTS represents the PARTS table. 1.
PAGE 56
Host Variables and Parameters Using Default Parameter Data Types Using Default Parameter Data Types If the TYPE AS clause is omitted from a dynamic SQL statement that uses date-time or INTERVAL data types with parameters, NonStop SQL assigns a default data type to a parameter as follows: INTERVAL The parameter name is followed by a range of fields and startfield-precision is specified.
PAGE 57
3 NonStop SQL Statements and Directives This section describes: • • NonStop SQL statements and directives that you can embed in a TAL program SQL, SQLMEM, and SYMBOLPAGES TAL compiler directives The NonStop SQL statements and directives that are described in this section have specific TAL data structures, considerations, and examples. For the complete description and syntax of all NonStop SQL statements and directives, see the SQL/MP Reference Manual.
PAGE 58
NonStop SQL Statements and Directives Coding Statements and Directives Coding Statements and Directives In general, consider embedded SQL statements and directives as if they are TAL statements. Follow the same formatting and line continuation conventions for SQL statements as you use for TAL statements. Here are a few specific guidelines to follow when you embed SQL statements and directives in a TAL program: • You can code longer statements or directives on more than one line.
PAGE 59
NonStop SQL Statements and Directives Placing Statements and Directives in a Program SYMBOLPAGES Directive To use the SYMBOLPAGES directive, specify it as a compiler option in the RUN command line for the TAL compiler when you compile the program: TAL /IN tsrc, OUT $s.
PAGE 60
NonStop SQL Statements and Directives Locating Information About SQL Statements and Directives Locating Information About SQL Statements and Directives Table 3-1 summarizes the NonStop SQL statements and directives by type and the manual that documents each statement or directive. The remainder of this section describes the NonStop SQL statements and directives in alphabetic order. Only the SQL, SQLMEM, and SYMBOLPAGES directive descriptions show the complete syntax.
PAGE 61
NonStop SQL Statements and Directives Locating Information About SQL Statements and Directives Table 3-1. Summary of NonStop SQL Statements and Directives (page 2 of 3) Statement or Directive Manual * Description INSERT SQLRM+SQL/TAL Inserts rows into a table or view. OPEN SQLRM+SQL/TAL Opens a cursor. SELECT SQLRM+SQL/TAL Retrieves data from tables and views. UPDATE SQLRM+SQL/TAL Updates values in columns of a table or view.
PAGE 62
NonStop SQL Statements and Directives BEGIN DECLARE SECTION and END DECLARE SECTION Table 3-1. Summary of NonStop SQL Statements and Directives (page 3 of 3) Statement or Directive Manual * Description INCLUDE SQLSA SQLRM+TAL= Declares the SQL statistics area to receive execution statistics on DML or PREPARE statements. INVOKE SQLRM+TAL Generates a TAL structure description of a table or view.
PAGE 63
NonStop SQL Statements and Directives • • • CLOSE A program or compilation unit can contain several Declare Sections; however, you cannot nest Declare Sections (that is, put a Declare Section in another Declare Section). The TAL compiler does not recognize the SQL SOURCE directive. Use the TAL SOURCE directive instead. You can use TAL comment lines in a Declare Section. The following declarations contain a Declare Section.
PAGE 64
NonStop SQL Statements and Directives CONTROL Directives This example shows the CLOSE statement in a typical sequence of statements that use a cursor. EXEC SQL DECLARE check_emp CURSOR FOR ! Declare the cursor. SELECT deptnum FROM employee WHERE deptnum = :deptnum^del FOR UPDATE OF deptnum ; ... ! get a value for :deptnum^del. EXEC SQL OPEN check_emp ; ! Open the cursor. ! FETCH rows until the NOT FOUND condition is met. WHILE SQLCODE >= 100 DO BEGIN EXEC SQL FETCH check_emp ! FETCH a value.
PAGE 65
NonStop SQL Statements and Directives CONTROL Directives Using the CONTROL EXECUTOR Directive Follow these guidelines when you use the CONTROL EXECUTOR directive: Execution Plan. The CONTROL EXECUTOR directive affects the execution plan for subsequent DML statements in the source program in listing order (rather than execution order) until: • • Another CONTROL EXECUTOR directive resets the option. The end of the procedure occurs. Scope. The scope of a CONTROL EXECUTOR directive is as follows.
PAGE 66
NonStop SQL Statements and Directives CONTROL Directives Using the CONTROL QUERY Directive Follow these guidelines when you use the CONTROL QUERY directive: Optimization. The CONTROL QUERY directive affects the optimization for subsequent DML statements in listing order (rather than execution order) until: • • Another CONTROL QUERY directive resets the option. The end of the procedure occurs. Scope. The scope of a CONTROL QUERY directive is as follows.
PAGE 67
NonStop SQL Statements and Directives DECLARE CURSOR Scope. The scope of a CONTROL TABLE directive is as follows. • • • • • A directive affects SQL statements in the program's listing order, regardless of the execution order. A directive with the global declarations affects only statements at the global level (statements that are outside of procedures). A directive in a procedure affects only the statements in the procedure.
PAGE 68
NonStop SQL Statements and Directives DECLARE CURSOR A static SQL cursor can be accessed only from the compilation unit in which its DECLARE CURSOR statement occurs. This means that for static SQL programs, the DECLARE CURSOR, OPEN, FETCH, DELETE WHERE CURRENT, UPDATE WHERE CURRENT, and CLOSE statements must be in the same compilation unit. If the program exits a procedure with an open cursor, procedures that execute later and are in the same compilation unit can still reference the cursor.
PAGE 69
NonStop SQL Statements and Directives DELETE :parts^rec.partdesc, :parts^rec.price, :parts^rec.qty^available; END; EXEC SQL CLOSE order_by_partdesc_cursor; 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.
PAGE 70
NonStop SQL Statements and Directives DELETE Executing a Single-Row DELETE Statement To delete a single row, you move a key value to a host variable and then use the host variable in the WHERE clause of the DELETE statement. The following DELETE 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. A user enter a value for the :HOSTVAR^KEY host variable.
PAGE 71
NonStop SQL Statements and Directives DELETE This DELETE statement deletes from the PARTSUPP table all suppliers who charge more than the host variable :HOSTVAR^MAX^COST for terminals. Terminals have part numbers in the range of the host variables :HOSTVAR^MIN to :HOSTVAR^MAX. EXEC SQL DELETE FROM invent.partsupp WHERE partnum BETWEEN :hostvar^min AND :hostvar^max AND partcost > hostvar^max^cost ; . Using a Cursor to Delete Rows To read and test a value before you delete a row, you must use a cursor.
PAGE 72
NonStop SQL Statements and Directives DESCRIBE and DESCRIBE INPUT You can refer to a static SQL cursor only in the compilation unit where the cursor is declared. Thus, for static SQL programs, the DELETE WHERE CURRENT, DECLARE CURSOR, OPEN, FETCH, and CLOSE statements that refer to the cursor must be in the same compilation unit. If a program exits a procedure with an open cursor, procedures that execute later can still refer to the cursor, provided the procedures are in the same compilation unit.
PAGE 73
NonStop SQL Statements and Directives DROP The size depends on the expected size and number of columns in the SELECT statement. This example uses the arbitrary values 5 and 39; you can use different values, or you can declare a template and dynamically allocate the memory. ! Global declarations EXEC SQL INCLUDE SQLDA (sqlda1, 5, namebuf, 39); EXEC SQL BEGIN DECLARE SECTION; STRING intext [0:200]; EXEC SQL END DECLARE SECTION; ...
PAGE 74
NonStop SQL Statements and Directives EXECUTE and EXECUTE IMMEDIATE EXECUTE and EXECUTE IMMEDIATE The EXECUTE statement executes a previously prepared dynamic SQL statement. The EXECUTE IMMEDIATE statement compiles and executes an SQL statement that is represented as text in a host variable. Use an EXECUTE statement for any DDL, DML, or DCL statement except the SELECT statement. Use an EXECUTE IMMEDIATE statement for any DDL, DML, or DCL statement except the OPEN, CLOSE, and SELECT statements.
PAGE 75
NonStop SQL Statements and Directives INCLUDE SQLCA, INCLUDE SQLDA, and INCLUDE SQLSA For a dynamic SQL cursor, all statements referring to the cursor must appear in the procedure where the cursor is declared; however, if you open the cursor and use it in one call to the procedure where it is declared, you can still use the cursor in subsequent calls without opening the cursor again.
PAGE 76
NonStop SQL Statements and Directives INSERT EXEC SQL INSERT INTO table1 (column1, column2, column3, column4) VALUES (:host^var1, :host^var2, :host^var3, :host^var4) ANYWHERE ; The previous example assumes that the organization of the table is relative. The INSERT statement supplies values for the four named columns. The system supplies the value for the system key (SYSKEY). Any other columns in the table receive default values (either specified defaults or the system default).
PAGE 77
NonStop SQL Statements and Directives INSERT ! Variable declarations: EXEC SQL BEGIN DECLARE SECTION; STRUCT employee^type(*); BEGIN INT empnum /SMALLINT UNSIGNED/; STRING first^name[0:14]; STRING last^name[0:19]; INT deptnum /SMALLINT UNSIGNED/; INT jobcode /SMALLINT UNSIGNED/; INT(32) salary /INTEGER UNSIGNED/; ! scale is 2 ... END; STRUCT .emp(employee^type); INT ind^1; EXEC SQL END DECLARE SECTION; ...
PAGE 78
NonStop SQL Statements and Directives INVOKE The example uses the JULIANTIMESTAMP and CONVERTTIMESTAMP Guardian procedures and the CONVERTTIMESTAMP SQL function. To call Guardian procedures, include the declarations from the EXTDECS file. ?SOURCE $SYSTEM.SYSTEM.EXTDECS ( JULIANTIMESTAMP, CONVERTTIMESTAMP) EXEC SQL BEGIN DECLARE SECTION; FIXED dtvar; ! Variable for storing the timestamp value EXEC SQL END DECLARE SECTION; INT SQLCODE; ...
PAGE 79
NonStop SQL Statements and Directives • INVOKE Use TACL DEFINEs only for table or view names; do not use them for structure names. Table 3-2 shows the override data type mapping generated by the INVOKE directive. Table 3-2.
PAGE 80
NonStop SQL Statements and Directives INVOKE To use the structure template in your program, declare an instance of the structure. This example declares a structure named EMPLOYEE that has the layout of the structure template EMPLOYEE^TYPE: STRUCT .employee (employee^type); You must qualify the host variables defined by INVOKE (for example, EMPLOYEE.EMP^ID). You cannot reference a structure name by itself as a host identifier.
PAGE 81
NonStop SQL Statements and Directives INVOKE INTERVAL values are represented as character strings with a separator between the values of the fields (year-month or day-time). An extra byte is generated at the beginning of the INTERVAL string for a sign. The default representations for date-time and INTERVAL values are shown in these subsections. Date-Time Representation.
PAGE 82
NonStop SQL Statements and Directives ! INVOKE STRING wait^time[0:2]; ! An extra byte is generated ! for a possible sign ! END; ! Declare storage for the table: STRUCT .projects^rec(projects^type); ! Procedure code: EXEC SQL SELECT project^name, start^date, end^date, wait^time INTO :projects^rec.project^name, :projects^rec.start^date TYPE AS DATETIME YEAR TO MINUTE, :projects^rec.end^date TYPE AS DATETIME YEAR TO MINUTE, :projects^rec.
PAGE 83
NonStop SQL Statements and Directives INVOKE The statements to supply values for the columns are: billings.billing^date ':='"1988-10-20"; billings.time^before^pmt ':=' "120"; ... EXEC SQL INSERT INTO billings VALUES ("923", DATE "1985-10-15", :billing^date TYPE AS DATE, :time^before^pmt TYPE AS INTERVAL DAY(3) ); ... Using Indicator Variables for Null Values INVOKE generates indicator variables of TAL type INT for each host variable that corresponds to a column that can be null.
PAGE 84
NonStop SQL Statements and Directives INVOKE last^name CHARACTER(20) NO DEFAULT NOT NULL, retire^date DATETIME YEAR TO DAY, PRIMARY KEY empnum ) CATALOG \sys.$vol.testcat ; The compiler generates this structure template: ! Record Definition for table \SYS.$VOL.SUBVOL.
PAGE 85
NonStop SQL Statements and Directives OPEN Using 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.
PAGE 86
NonStop SQL Statements and Directives PREPARE one call to the procedure where it is declared, you can still use the cursor in subsequent calls without opening the cursor again. This example shows the OPEN statement in a typical sequence of statements that use a cursor. EXEC SQL DECLARE check_emp CURSOR FOR SELECT deptnum FROM employee WHERE deptnum = :deptnum^del FOR UPDATE OF deptnum ; ! Declare the cursor. ... ! get a value for :deptnum^del. EXEC SQL OPEN check_emp ; ! Open the cursor.
PAGE 87
NonStop SQL Statements and Directives RELEASE Follow these guidelines when you use the PREPARE statement: • • • A prepared statement is associated only with the program that executes the PREPARE statement. Only that program can execute the prepared statement.
PAGE 88
NonStop SQL Statements and Directives • • SELECT A single-row SELECT returns a single row or value. A multi-row SELECT returns multiple rows one row at a time. To execute a SELECT statement, a TAL program's process accessor ID (PAID) must have read access to all protection views, tables, and underlying tables of all shorthand views referred to in the statement. In general, specifying a select operation programmatically in a TAL program is the same as specifying an select operation using SQLCI commands.
PAGE 89
NonStop SQL Statements and Directives SELECT EXEC SQL BEGIN DECLARE SECTION; ! Variable declarations STRUCT customer^type; BEGIN INT custnum; STRING custname[0:17]; STRING street[0:21]; STRING city[0:13]; STRING state[0:11]; STRING postcode[0:9]; END; STRUCT .customer(customer^type); INT find^this^customer; EXEC SQL END DECLARE SECTION; PROC handle^not^found; FORWARD; EXEC SQL WHENEVER NOT FOUND CALL :handle^not^found; ...
PAGE 90
NonStop SQL Statements and Directives SELECT Executing a Multi-Row SELECT Statement A multi-row SELECT statement returns multiple rows one row at a time. Because host variables cannot hold more than the first row, you must declare this type of SELECT statement as a cursor. 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 these steps.
PAGE 91
NonStop SQL Statements and Directives SQL When to Initialize a Cursor. Opening a cursor causes the set of rows in the query result to be defined and ordered. If a cursor SELECT statement contains host variables in its WHERE clause, you must initialize the values of the host variables either before you open the cursor with an OPEN statement or else in the USING clause of the OPEN statement. The SQL executor places the input variables into its buffers when it opens the cursor.
PAGE 92
NonStop SQL Statements and Directives SQL PAGES num-pages specifies the number of 2048-byte pages of memory available to the TAL compiler to process SQL statements. The default is 384 pages, which is the minimum number required and a usable value for most programs. A recommended upper limit is 32,000. The actual upper limit depends on space allocated by the compiler and a value specified by the SYMBOLPAGES directive. By increasing the value, you increase the number of lines allowed in an SQL statement.
PAGE 93
NonStop SQL Statements and Directives SQLMEM SQLMAP | NOSQLMAP specifies whether the compiler listing includes an SQL map. This map enables you to determine SQL statements using output from the Measure program. SQLMAP causes an SQL map to be added at the end of the compiler listing.
PAGE 94
NonStop SQL Statements and Directives SQLMEM Table 3-3.
PAGE 95
NonStop SQL Statements and Directives SQLMEM For example, a host variable used in a DECLARE CURSOR statement is not accessed when the DECLARE CURSOR statement is processed; it is accessed when the cursor is opened. STACK places the SQL data structures in the default extended segment only initially. Before an SQL statement executes, the system allocates space at the top of the stack and issues a MOVEX procedure call to copy the SQL data structures needed for that statement to the top of the stack.
PAGE 96
NonStop SQL Statements and Directives SQLMEM length is the number of bytes that are available at address for the data structures. This length should be large enough to hold the largest SQL statement description expected in your program (that is, the sum in bytes of the SQLIN, SQLIVARS, and SQLOVARS structures). The value cannot exceed 65,536. You can use a constant or a literal for length.
PAGE 97
NonStop SQL Statements and Directives SQLMEM Estimating Sizes for SQLIN and SQLVARS The size estimates for the SQL data structures are: Structure Size, Bytes SQLCA 430 (approximate) SQLSA 838 (approximate) SQLIN 72 To estimate the size of the SQLIVARS and SQLOVARS structures, use this formula: size = (4 + 24 * n ) bytes where n is the number of host variables.
PAGE 98
NonStop SQL Statements and Directives • • SYMBOLPAGES ° Specify the STACK option to allow TAL to create a default extended segment for initial placement of the data structures. ° Convert the program to use the default extended segment and specify the EXT option (or allow EXT to be the default). For some programs, the MAPPED option might be simpler to implement than to convert the program. ° Specify the MAPPED option.
PAGE 99
NonStop SQL Statements and Directives • UPDATE 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) 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). To execute an UPDATE statement, a program's process accessor ID (PAID) must have read and write access to the table or view being updated.
PAGE 100
NonStop SQL Statements and Directives UPDATE EXEC SQL END DECLARE SECTION; ... ! Procedure code: PROC update^orders; BEGIN ... newdate ':=' "1987-05-22"; orders.ordernum := 200038; EXEC SQL UPDATE orders SET deliv^date = :newdate TYPE AS DATE WHERE ordernum = :orders.ordernum STABLE ACCESS; END; ... Updating Multiple Rows Using a Single UPDATE Statement Multi-row operations (sometimes called set operations) are performed on one or more rows by a single SQL statement.
PAGE 101
NonStop SQL Statements and Directives UPDATE When you use a cursor to select rows for subsequent update, you must use the FOR UPDATE OF clause in the cursor declaration. If you determine you want to update the data, you use a WHERE CURRENT OF clause to update the row from the table. Note. Do not use a stand-alone UPDATE operation to update a row that has been retrieved using a cursor FETCH operation. This can invalidate the cursor's buffering for the table and might degrade performance.
PAGE 102
NonStop SQL Statements and Directives UPDATE The example uses the host variables :NEW^PARTDESC, :NEW^PRICE, and :NEW^QTY and sets them to new values (entered by a user) for the columns before executing the UPDATE statement. EXEC SQL DECLARE get_by_partnum_cursor CURSOR FOR SELECT partnum, partdesc, price, qty_available FROM sales.parts WHERE (partnum >= :starting^partnum ) STABLE ACCESS FOR UPDATE OF partdesc, price, qty_available; ... ! Declare host variables new^partdesc, new^price,and new^qty. ...
PAGE 103
NonStop SQL Statements and Directives WHENEVER Updating a Column With a Null Value This 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 emp^tbl; STRUCT .emp(emp^tbl); INT ind^1; EXEC SQL END DECLARE SECTION; ... ! Executable statements: ind^1 := -1; EXEC SQL UPDATE =employee SET salary =:emp.
PAGE 104
NonStop SQL Statements and Directives HP NonStop SQL Programming Manual for TAL—527887-001 3- 48 WHENEVER
PAGE 105
4 System Procedures Use the provided system library procedures, written in TAL, to perform various SQL functions. The EXTDECS file contains source declarations for these procedures. Use the SOURCE directive to specify the declarations in the EXTDECS file for the procedures that you want to call in your program. For example, this directive specifies declarations for the SQLCADISPLAY and SQLCAFSCODE procedures: ?SOURCE $SYSTEM.SYSTEM.
PAGE 106
System Procedures Procedure Descriptions Procedure Descriptions The following SQL procedures are described in alphabetical order on subsequent pages. Each description includes the syntax, parameter definitions, and guidelines. • • • • • • • • SQLCADISPLAY SQLCAFSCODE SQLCAGETINFOLIST SQLCATOBUFFER SQLGETCATALOGVERSION SQLGETOBJECTVERSION SQLGETSYSTEMVERSION SQLSADISPLAY SQLCADISPLAY The SQLCADISPLAY procedure displays information that NonStop SQL returns to the SQLCA data structure.
PAGE 107
System Procedures • SQLCADISPLAY Disk process (DP2) CALL SQLCADISPLAY ( sqlca^ , [ output^file number , [ output^record^length , [ sql^msg^file^number , [ errors , [ warnings , [ statistics , [ caller^error^loc , [ internal^error^loc , [ prefix , [ prefix^length , [ suffix , [ suffix^length , [ detail^params sqlca^ ! i ! i ! i ] ! i,o ] ! i ] ! i ] ! i ] ! i ] ! i ] ! i ] ! i ] ! i ] ! i ] ) ; ! i ] ] input INT .EXT is a pointer to the SQLCA structure.
PAGE 108
System Procedures SQLCADISPLAY 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 INT controls the display of error messages. The values you can specify and their meanings are: N Y B Display only the first error. Display all errors. Display all errors but suppress this prefix: ERROR from subsystem [nn]: The default is Y. warnings input INT controls the display of warning messages.
PAGE 109
System Procedures SQLCADISPLAY caller^error^loc input INT controls displaying the program name and line number of the SQL statement that received the error. The values you can specify and their meanings are: Y Enable the display of the information. N Suppress the display of the information. The default is Y. internal^error^loc input INT controls displaying the location in system code where the first error in the SQLCA structure occurred.
PAGE 110
System Procedures SQLCADISPLAY suffix^len input INT specifies the length of the suffix string to append to each output line; the length can be an integer value from 1 through 15. If you include the suffix parameter, you must also include this parameter. If you omit the suffix parameter, you must also omit this parameter. detail^params input INT .EXT determines whether the program uses sequential SIO (SIO) or Enscribe I/O (which is the default) for writing to the output file.
PAGE 111
System Procedures • • SQLCAFSCODE If the error text exceeds output^record^length, the output is wrapped at word boundaries producing subsequent lines that are indented 5 spaces. The SQLCA can contain a maximum of 7 errors and 180 bytes of text of the actual parameters returned to the program. Any information that exceeds these limits is lost. SQLCADISPLAY prints a warning message that indicates when information is lost.
PAGE 112
System Procedures SQLCAGETINFOLIST first^flg input INT specifies whether the first or the last error is set in the SQLCA. You can set first^flg as follows: Nonzero value (or omitted) First error 0 Last error Guidelines Follow these guidelines when you call the SQLCAFSCODE procedure: • • 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 Guardian or file-system error code that occurs for an SQL operation.
PAGE 113
System Procedures • SQLCAGETINFOLIST Disk process (DP2) error := SQLCAGETINFOLIST , , , , , , , , error ( sqlca^ itemlist numberitems result resultmax [ errorindex [ namesmax [ paramsmax [ resultlen , [ erroritem ! ! ! ! ! ! ! ! ! ] ] ] ] ] ) ; i i i o i i i i o ! o returned value INT returns the outcome of the operation. Zero (0) indicates a successful operation. For more information on the description of other return values, see Table 4-3 on page 4-12. sqlca^ input INT .
PAGE 114
System Procedures SQLCAGETINFOLIST resultmax input INT is the maximum size, in bytes, of the result structure. errorindex input INT is the index of the SQLCA error or warning entry. The SQLCA has a fixed set of fields (item codes 1–21) that pertain to all errors and warnings. In addition, SQLCA has a table of records (item codes 22–29) with each record describing one error or warning.
PAGE 115
System Procedures SQLCAGETINFOLIST Table 4-2. SQLCAGETINFOLIST Procedure Item Codes (page 1 of 2) Item Code Size (in bytes) Description 1 2 SQLCA version. 2 2 Maximum number of errors or warnings the SQLCA can represent. 3 2 Actual number of errors or warnings. 4 2 Whether there were more errors or warnings than the SQLCA had space to store: • • 5 2 0 There were no more errors or warnings. Nonzero There were more errors or warnings.
PAGE 116
System Procedures SQLCAGETINFOLIST Table 4-2.
PAGE 117
System Procedures SQLCATOBUFFER In the example below, SQLCAGETINFOLIST returns the: • • • Name of the function containing the SQL statement that produced one or more errors or warnings Name length of the function Number of errors or warnings that occurred To avoid coding the maximum length for the function name (NAME[0:29] in this example): 1. Call SQLCAGETINFOLIST passing item code 7 (ERR^WARN.NAMELEN). 2. Call SQLCAGETINFOLIST again passing a buffer of the appropriate size.
PAGE 118
System Procedures • • • • • • SQLCATOBUFFER NonStop SQL FASTSORT program (SORTPROG process) Sequential I/O (SIO) procedures File system NonStop Kernel operating system Disk process (DP2) CALL SQLCATOBUFFER ( sqlca output^buffer output^buffer^length [ first^output^record [ output^records [ more [ output^record^length [ sql^msg^file^number [ errors [ warnings [ statistics [ caller^error^loc [ internal^error^loc [ prefix [ prefix^length [ suffix , [ suffix^length , , , , , , , , , , , , , , , sqlca ! !
PAGE 119
System Procedures SQLCATOBUFFER first^output^record input INT is the ordinal number of the first error record (line) to be written to the output buffer. The procedure discards any error records with a lower number. The default is 1. The count of lines begins with 1. To obtain more than one error record, you must increment the value in first^output^record. output^records output INT is the number of records (lines) written by SQLCATOBUFFER to output^buffer. more output .
PAGE 120
System Procedures SQLCATOBUFFER errors input INT controls the error messages that are written to the buffer. The values you can specify and their meanings are: N Write only the first error. Y Write all errors. B Write all errors but suppress the prefix: ERROR from subsystem [nn]: The default is Y. warnings input INT controls the warning messages that are written to the buffer. The values you can specify and their meanings are: N Write no warning messages. Y Write all warning messages.
PAGE 121
System Procedures SQLCATOBUFFER caller^error^loc input INT controls writing the program name and line number of the SQL statement that received the error. The values you can specify and their meanings are: Y Enable writing of the information. N Suppress writing of the information. The default is Y. internal^error^loc input INT controls writing the location in system code where the first error in the SQLCA occurred. The values you can specify and their meanings are: Y Enable writing the information.
PAGE 122
System Procedures SQLCATOBUFFER suffix^len input INT specifies the length of the suffix string to append to each output line; the length can be an integer value from 1 through 15. If you include the suffix parameter, you must also include this parameter. If you omit the suffix parameter, you must also omit this parameter. Guidelines Follow these guidelines when you call the SQLCATOBUFFER procedure: • • NonStop SQL returns errors as negative numbers and warnings as positive numbers.
PAGE 123
System Procedures SQLGETCATALOGVERSION SQLGETCATALOGVERSION The SQLGETCATALOGVERSION procedure returns the version of an SQL catalog. error := SQLGETCATALOGVERSION ( [ catalogname ] ! i , sqlversion ) ; ! o error returned value INT returns the outcome of the operation. Zero (0) indicates a successful operation. For a description of SQL errors, see the SQL/MP Messages Manual. catalogname input STRING .EXT specifies the fully qualified name of the catalog for which you are requesting information.
PAGE 124
System Procedures SQLGETOBJECTVERSION objectname input STRING .EXT specifies the fully qualified file name of the NonStop SQL object for which you are requesting information. The name must be: • • Left justified and padded with blanks A maximum of 34 characters sqlversion output INT .EXT is the version of the object. The value returned is: 1. Version 1 object that is compatible with NonStop SQL C10 2.
PAGE 125
System Procedures SQLGETSYSTEMVERSION These attributes, however, are considered Version 2 features by the catalog manager process, which handles DDL statements. Tables and views that use these features must be handled by DDL statements executed on a NonStop SQL C30 system. SQLGETSYSTEMVERSION The SQLGETSYSTEMVERSION procedure returns the version of NonStop SQL file system and disk process components that are running on a system.
PAGE 126
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. CALL SQLSADISPLAY ( sqlsa^ , [ sqlca^ , [ out^file^number , [ detail^params sqlsa^ ! i ] ! i ] ! i,o ] ) ; ! i input INT .EXT is a pointer to the SQLSA structure. The TAL compiler automatically declares the SQLSA structure when you specify the INCLUDE SQLSA directive. sqlca^ input INT .
PAGE 127
System Procedures SQLSADISPLAY sio specifies whether sequential I/O (SIO) is used; it can have these values: Y Use SIO; ignore out^file^number. N Do not use SIO; write to out^file^number. out^fcb^1 specifies the first output file control block if SIO is enabled. out^fcb^2 specifies the second output file control block if SIO is enabled. To use this field, assign it a value greater than 0.
PAGE 128
System Procedures SQLSADISPLAY Figure 4-1 shows an example of the information that SQLSADISPLAY displays at the home terminal. To generate this display, a program must: 1. Include the SQLSA and SQLCA structures. 2. Execute an SQL DML statement. 3. Call the SQLSADISPLAY procedure: CALL SQLSADISPLAY (sqlsa, sqlca); Figure 4-1. SQLSADISPLAY Display SQL statistics @ \sanfran.$system.accts.prog10.#333.
PAGE 129
5 Program Compilation and Execution This section describes the compilation and execution of a TAL program that contains embedded SQL statements.
PAGE 130
Program Compilation and Execution Compiling a TAL Program Figure 5-1. Compiling and Executing a Program TAL/SQL TALLIB Source File (s) Run-Time Library TAL Compiler Binder 4 3 2 1 SQL Compiler Accelerator TAL/SQL TAL/SQL TAL/SQL Obejct File (s) Single Object File Single Object File TAL/SQL Obejct File 5 TAL Object Code TAL Object Code TAL TNS and TNS/R Object Code SQL Source Statements SQL Source Statements SQL Source Statements Run the object file VST0501.
PAGE 131
Program Compilation and Execution Running the TAL Compiler Running the TAL Compiler To run the TAL compiler, enter an implicit TACL RUN command at your TACL prompt or from an OBEY command file. The syntax is: TAL / [ IN source-file ] [, OUT list-file ] [ , run-option] [ , run-option] ... / [ object-file ] [ ; directive [ , directive ] ... ] directive can be: [ SYMBOLPAGES num-pages ] [ SQL [ option ] [ ( option [ , option ] ...
PAGE 132
Program Compilation and Execution Using the Binder Program named ZZBI nnnn on your default subvolume (nnnn is a 4-digit number determined by the system). directive is a TAL compiler directive as described in this manual or in the TAL Reference Manual. You must include the SQL directive either in the command line or at the beginning of the primary source file. Other TAL compiler directives are optional.
PAGE 133
Program Compilation and Execution Running the Accelerator When you run the TAL compiler, use the SEARCH directive to include the TALLIB runtime library. This SEARCH directive appears in a TAL source file: ! TAL source file ?SEARCH ($system.system.TALLIB) ... You can also specify the SEARCH directive in the command line for the TAL compiler: TAL /IN partsrc, OUT $s.#partlst, NOWAIT / partobj ; SQL , SEARCH ($system.system.
PAGE 134
Program Compilation and Execution Running the SQL Compiler To run the SQL compiler, use the SQLCOMP command. The syntax is: SQLCOMP / IN object-file [ , OUT [ list-file ] ] [ , run-option] [ , run-option ] ... / [ compiler-option [ , compiler-option ] ...
PAGE 135
Program Compilation and Execution Running the SQL Compiler \ system is an optional system name. external-file is one of these Guardian names: [$ volume-name.][subvolume-name.] disk-file-name $ device-name $ device-number $ process-name $ spooler-collector-name[.# spooler-location-name] If list-file does not exist, the SQL compiler creates it. If list-file already exists, the SQL compiler appends the new output to it. If you specify OUT but omit list-file, the SQL compiler does not produce a listing.
PAGE 136
Program Compilation and Execution Running the SQL Compiler STOREDDEFINES selects set of TACL DEFINEs stored with the program the last time it was SQL compiled. This option applies only to programs that have been SQL compiled. FORCE | NOFORCE controls how syntax errors affect SQL compilation. FORCE directs the SQL compiler to produce a valid, executable object file regardless of any syntax errors.
PAGE 137
Program Compilation and Execution Running the SQL Compiler OBEYFORM specifies writing the TACL DEFINE list in OBEY format so that you can enter an OBEY command from your TACL prompt to set the DEFINEs before run time or compile time. If you omit OBEYFORM, the listing is in the format displayed by the INFO DEFINE command. If you omit DEFINES, a DEFINE listing is not generated. NOEXPLAIN disables the EXPLAIN utility. NOEXPLAIN is the default.
PAGE 138
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.
PAGE 139
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.
PAGE 140
Program Compilation and Execution Running the SQL Compiler SQL Program File Format The input program file to the SQL compiler can be a TAL object file, a file generated by the Binder program, a file generated by the Accelerator, or a file previously compiled by the SQL compiler. Figure 5-2 shows the format of a NonStop SQL program file. Figure 5-2.
PAGE 141
Program Compilation and Execution Running the SQL Compiler Using a PARAM Command With the SQL Compiler Use the TACL PARAM command to specify the BINSERV processes and the swap-file subvolume that the SQL compiler uses for explicit SQL compilation. A PARAM command 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 ]...
PAGE 142
Program Compilation and Execution Running the SQL Compiler Interpreting SQL Compiler Messages The SQL compiler issues messages for error and warning conditions. An error can prevent successful compilation of a program file, but a warning does not prevent successful compilation. For a description of SQL compiler messages, see the SQL/MP Messages Manual. Error Conditions. An error condition results from an invalid reference to an SQL object in an SQL statement.
PAGE 143
Program Compilation and Execution Running the SQL Compiler For other cases, the SQL compiler first marks the statement as having insufficient information to compile and then at run time tries to resolve the problem with automatic recompilation. The SQL compiler does not record dependencies in the USAGES catalog tables for the affected statement. At run time, if the necessary information is still not available or any TACL DEFINEs are still unresolved, execution of the statement causes an error.
PAGE 144
Program Compilation and Execution Running the SQL Compiler Figure 5-3. Sample SQL Compiler Listing (page 1 of 2) SQL Compiler - T9095C30 - (01NOV91) COPYRIGHT TANDEM COMPUTERS INCORPORATED 1987, 1988, 1989, 1990, 1991 DATE - TIME : 11/15/91 - 10:08:10 Options : NOFORCE, OBJECT, CURRENTDEFINES, RECOMPILE, RECOMPILEALL, NOEXPLAIN SQL - PROGRAM FILE = \SYS1.$VOL1.SQLTAL.PROGOBJ SQL - PROGRAM CATALOG = \SYS1.$VOL1.DBI SQL - DEFAULT CATALOG = \SYS1.$VOL1.
PAGE 145
Program Compilation and Execution Using the EXPLAIN Utility Figure 5-3. Sample SQL Compiler Listing (page 2 of 2) BINDER - OBJECT FILE BINDER - T9621C30 - (01NOV91) SYSTEM \SYS1. Copyright Tandem Computers Incorporated 1982-1989, 1991 Object file \SYS1.$VOL1.SQLTAL.
PAGE 146
Program Compilation and Execution Using the EXPLAIN Utility Generating an EXPLAIN PLAN 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. The EXPLAIN PLAN report generates a plan for a statement that contains subqueries into separate query plans, one for each subquery and one for the statement itself. This report numbers the query plans in each statement in the order they appear.
PAGE 147
Using the EXPLAIN Utility Program Compilation and Execution You can have the EXPLAIN utility generate the EXPLAIN DEFINES report in either of these formats: OBEY command file format The EXPLAIN utility generates the TACL ADD DEFINE commands for the DEFINEs. You can subsequently use the TACL OBEY command to generate the commands in the file. INFO DEFINE format The EXPLAIN utility uses the format produced by the TACL INFO DEFINE command.
PAGE 148
Using the EXPLAIN Utility Program Compilation and Execution 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 shows an EXPLAIN report that includes both the execution plans and stored DEFINEs.
PAGE 149
Program Compilation and Execution Using the EXPLAIN Utility Figure 5-6. EXPLAIN Utility Report SQL Compiler - T9095C30 - (01NOV91) COPYRIGHT TANDEM COMPUTERS INCORPORATED 1987, 1988, 1989, 1990, 1991 DATE - TIME : 11/15/91 - 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.
PAGE 150
Program Compilation and Execution Determining Program File Validity Determining Program File Validity SQL program files can be valid or invalid. A valid program file can execute with the current description of the database. Certain operations performed on the program file 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).
PAGE 151
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.
PAGE 152
Program Compilation and Execution Understanding Automatic SQL Recompilation Therefore, an invalid program file is sometimes marked as SQL valid. 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.
PAGE 153
Program Compilation and Execution Predicting Automatic SQL Recompilation If automatic SQL recompilation is enabled, these events can trigger a recompilation: • • • • • SQL load time Table open time A new TACL DEFINE at SQL load time An unavailable access path (index) The attempted execution of an uncompiled SQL statement SQL Load Time SQL load time occurs when the first SQL statement in a program is executed.
PAGE 154
Program Compilation and Execution Understanding the Run-Time Timestamp Check SQL load time TACL DEFINEs for a static SQL statement and the current active TACL DEFINEs when a PREPARE statement is executed for a dynamic SQL statement. Unavailable Access Path (Index) If the SQL executor tries to execute an SQL statement and detects that an access path in the execution plan is unavailable, the executor invokes the SQL compiler to recompile the statement.
PAGE 155
Program Compilation and Execution Understanding the Run-Time Timestamp Check If TABLEA is open when you execute the ALTER TABLE statement, the system closes the table. The next time the SQL statement is executed, the SQL executor reopens TABLEA, which forces the timestamp check and the statement to be recompiled. Figure 5-7 shows this run-time object definition check. Figure 5-7. Run-Time Checks and Automatic SQL Recompilation SQL Object Code . . . SELECT . . . FROM tablea . . .
PAGE 156
Program Compilation and Execution Understanding Run-Time Recompilation Errors Understanding Run-Time Recompilation Errors For automatic SQL recompilation of an entire program at run time, the SQL executor returns compilation errors or warnings as follows: • • If an SQL statement causes an error or warning, the statement remains uncompiled in the program file and the SQL executor suppresses the error or warning message.
PAGE 157
Program Compilation and Execution Using TACL DEFINEs This example shows the concept of maximizing local autonomy. The parts table is a partitioned table that resides on the \NEWYORK and \CHICAGO systems: \NEWYORK The first partition contains all rows in which PARTS.PARTNUM (the primary key) is less than 5000. \CHICAGO The second partition contains all rows in which PARTS.PARTNUM is 5000 or greater. An index on the PARTDESC column of table PARTS is named IXPART.
PAGE 158
Program Compilation and Execution Skipping Unavailable Partitions Skipping Unavailable Partitions Use the SKIP UNAVAILABLE PARTITION option of the CONTROL TABLE directive to cause NonStop SQL to skip a partition that is not available and to open the next available partition that satisfies the search condition of a query. (NonStop SQL also returns a warning message (8239) to the SQLCA.
PAGE 159
Program Compilation and Execution Using TACL DEFINEs an implicit RUN command, enter only the name of the program file. The syntax for the RUN command is: [ RUN[D] ] program-file [ / run-option [ , run-option ] ... / ] [ param-set ] RUN executes the program file without debugging. RUND executes the program file and initiates debugging under the control of INSPECT or DEBUG. program-file is the name of the SQL program object file.
PAGE 160
Program Compilation and Execution Estimating Program Size SQL Compilation statements. The SQL compiler registers programs in a catalog. If you are SQL compiling an object file, you might want to use a DEFINE to specify the catalog. For explicitly recompiling a program, you can specify the DEFINE set stored in the program file by using the SQLCOMP command STOREDDEFINES option.
PAGE 161
Program Compilation and Execution Estimating Program Size These structures are shared by all SQL statements and directives in a program: Bytes Structure Description 430 SQLCA Count once if you specify INCLUDE SQLCA 838 SQLSA Count once if you specify INCLUDE SQLSA Use this table to estimate the number of bytes used by each embedded SQL statement and directive in the extended data segment. Table 5-1.
PAGE 162
Program Compilation and Execution Estimating Program Size Caution. The system allocates real memory in 2 KB pages. If an SQL statement uses only part of a page, the system allocates the entire page. Therefore, the real memory used by embedded SQL statements can be larger than the figures shown in Table 5-1 on page 5-33. A program can encounter memory problems in these cases: • • • It contains a large number of embedded SQL statements. It runs on a system with limited memory (for example, 16 MB or less).
PAGE 163
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 communications area (SQLCA). The SQLCA contains run-time information including errors and warnings generated by the most recently executed dynamic or static SQL statement. SQL descriptor area (SQLDA).
PAGE 164
Error and Status Processing Using the SQLCODE Variable Declaring the SQLCODE Variable Declare SQLCODE in your program as an integer variable: INT SQLCODE; If you omit the SQLCODE declaration, 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.
PAGE 165
Error and Status Processing Using the SQLCODE Variable Figure 6-1. Checking the SQLCODE Variable ! Variable declarations EXEC SQL BEGIN DECLARE SECTION; STRUCT .in^parts^rec; BEGIN INT in^partnum; FIXED(2) in^price; STRING in^partdesc[0:17]; END; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; ! Include the SQLCA structure ! ! for detailed error information ! INT SQLCODE; ! Include the SQLCODE variable ! ! for simple error checking ! . . .
PAGE 166
Error and Status Processing Using the WHENEVER Directive Using the WHENEVER Directive The WHENEVER directive specifies an action that a program takes depending on the outcome of subsequent DML, DCL, and DDL SQL statements. WHENEVER provides tests for these conditions: • • • An error occurred. A warning occurred. No rows were found. You can specify a WHENEVER directive anywhere in your program.
PAGE 167
Error and Status Processing • • Using the WHENEVER Directive A WHENEVER directive does not affect SQL statements if they appear in the program before the WHENEVER directive. If you are debugging a program and you use a WHENEVER directive to call an error handling procedure, you might need to save the SQLCODE value in a local variable within the error handling procedure. Each subsequent SQL statement resets SQLCODE, and you might lose a value you need to debug your program.
PAGE 168
Error and Status Processing Using the WHENEVER Directive Figure 6-2. Enabling and Disabling the WHENEVER Directive EXEC SQL WHENEVER SQLERROR CALL :error^handler; PROC proc^1(i, j, k); INT i; INT j; INT k; BEGIN EXEC SQL SELECT ...; EXEC SQL SELECT ...; EXEC SQL SELECT ...; END; PROC proc^2(i, j, k); INT i; INT j; INT k; BEGIN EXEC SQL SELECT ...; EXEC SQL SELECT ...; EXEC SQL SELECT ...
PAGE 169
Error and Status Processing Using the WHENEVER Directive Using the CALL Format If you use the CALL format of WHENEVER to call an error handling procedure, follow these guidelines: • • • Specify the WHENEVER directive globally and precede the directive with a forward declaration of the error handling procedure(s). Declare the error handling procedure or subprocedure without parameters, and do not allow it to return a value.
PAGE 170
Error and Status Processing Using the WHENEVER Directive Figure 6-3. Using WHENEVER Directives ! Variable declarations: EXEC SQL BEGIN DECLARE SECTION; STRUCT .
PAGE 171
Error and Status Processing Getting Information From the SQLCA 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). The SQLCA is the primary status checking area for application programs because it contains more detailed information than the SQLCODE variable. The compiler initializes the SQLCA before each executable SQL statement.
PAGE 172
Error and Status Processing Getting Performance and Statistics Information Figure 6-4. Calling an SQL System Procedure ?SOURCE $system.system.extdecs ? ? ? (SQLCADISPLAY, SQLCAFSCODE , SQLCAGETINFOLIST, SQLCATOBUFFER) ! Global declarations: EXEC SQL INCLUDE SQLCA; ... ! Error handling routine: ... CALL SQLCADISPLAY (SQLCA); VST0604.vsd ... If your program uses the NUMOUT procedure to display an error message, you must remove the negative sign before displaying the error number.
PAGE 173
Error and Status Processing Getting Performance and Statistics Information Figure 6-5 shows the SQLSA structure generated in a TAL program by the INCLUDE SQLSA directive. NonStop SQL generates this structure and inserts it in the program following the INCLUDE SQLSA directive. Figure 6-5. SQLSA Structure Description STRUCT .
PAGE 174
Error and Status Processing Getting Performance and Statistics Information Table 6-3. SQLSA Fields (page 2 of 3) Field Name 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 Guardian internal file name of the table accessed. RECORDS^ACCESSED Number of records accessed in the corresponding table. RECORDS^USED Number of records altered or returned.
PAGE 175
Error and Status Processing Getting Information About Dynamic SQL Operations Table 6-3. SQLSA Fields (page 3 of 3) Field Name Description OUTPUT^NUM Number of output variables in the prepared statement. Output variables are SELECT columns or LASTSYSKEY on INSERT RETURNING. OUTPUT^NAMES^LENGTH Length of buffer required to contain names of output variables. NAME^MAP^USE Reserved for system use SQL^STATEMENT^TYPE Type of statement being prepared.
PAGE 176
Error and Status Processing Declaring the SQLDA and Names Buffer Declaring the SQLDA and Names Buffer Use the INCLUDE SQLDA directive to declare a structure template for the SQLDA in a TAL 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.
PAGE 177
Error and Status Processing Declaring the SQLDA and Names Buffer Figure 6-6 and Figure 6-7 on page 6-16 show structure templates generated by the INCLUDE SQLDA directive for NonStop SQL Release 1 and Release 2. Figure 6-6. Release C30 SQLDA Template and Names Buffer LITERAL SQLDA^EYE^CATCHER = "D1"; STRUCT sqlda-name (*) ; BEGIN STRING eye^catcher[0:1]; INT num^entries; STRUCT sqlvar[0: sqlvar-count - 1]; BEGIN INT data^type; INT data^len; ! fields for NUMBERS: ! scale = data^len.
PAGE 178
Error and Status Processing Declaring the SQLDA and Names Buffer Figure 6-7. Release C10 SQLDA Template and Names Buffer LITERAL SQLDA^EYE^CATCHER^R1 = "DA"; STRUCT sqlda-name (*) ; BEGIN STRING eye^catcher[0:1]; INT num^entries; STRUCT sqlvar[0: sqlvar-count - 1 ]; BEGIN INT data^type; INT data^len; ! fields for NUMBERS: ! scale = data^len.<0:7> ! length = data^len.<8:15> INT null^info; INT(32) var^ptr; INT(32) reserved; END; END; STRING . names-buffer-name[ 0 : length - 1 ]; VST0607.
PAGE 179
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 A constant declared by the system. If you requested an SQLDA structure of a release that is different from the release of the SQL compiler, SQLDA^EYE^CATCHER appears as either SQLDA^EYE^CATCHER^R1 or SQLDA^EYE^CATCHER^R2. EYE^CATCHER Identifying field that your program must initialize with the system-declared SQLDA^EYE^CATCHER literal.
PAGE 180
Error and Status Processing Initializing the EYE^CATCHER Field Table 6-4. SQLDA Fields (page 2 of 2) Field Name Description NULL^INFO * For input parameters, NULL^INFO contains a negative integer if the parameter could contain a null value. For output columns, NULL^INFO contains a negative integer if the column could contain a null value. (To verify whether the parameter or column is really null, check for a negative value at the location shown in IND^PTR).
PAGE 181
Error and Status Processing Using Literal Declarations for the SQLDA Table 6-5.
PAGE 182
Error and Status Processing Using Literal Declarations for the SQLDA Table 6-6.
PAGE 183
Error and Status Processing Example of Using the SQLDA Table 6-7.
PAGE 184
Error and Status Processing Example of Using the SQLDA Figure 6-8. SQLDA Structure Template LITERAL SQLDA^EYE^CATCHER = "D1"; STRUCT SQLDAX (*); BEGIN STRING eye^catcher[0:1]; INT num^entries; STRUCT sqlvar[0:19]; BEGIN INT data^type; INT data^len; ! fields for NUMBERS: ! scale = data^len.<0:7> ! length = data^len.<8:15> ! fields for DATETIME or INTERVAL: ! qualifier = data^len.<0:7> ! length = data^len.<8:15> INT precision; ! fields for DATETIME or INTERVAL: ! leading field precision = precision.
PAGE 185
7 Dynamic NonStop SQL Operations Dynamic SQL operations allow a 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 TAL source program at compile time. A program using static SQL statements receives input values from host variables and sends output values to host variables.
PAGE 186
Dynamic NonStop SQL Operations Determining Uses for Dynamic SQL Operations Determining Uses for Dynamic SQL Operations Programs that use dynamic SQL operations can be useful for a number of applications. For example: • • • • You can develop an interactive interface that is similar to SQLCI, but is designed for an inexperienced user. You want to switch between several copies of identical databases. For this application, you use a dynamic SQL program with run-time TACL DEFINEs.
PAGE 187
Dynamic NonStop SQL Operations Writing a Dynamic SQL Pathway Server Writing a Dynamic SQL Pathway Server If you are writing a TAL server that interfaces with Pathway and uses dynamic SQL statements, follow these steps: 1. Use the TAL SOURCE directive to include the declarations in the EXTDECS file for the Guardian OPEN, READUPDATE, and REPLY system procedures: ?SOURCE $SYSTEM.SYSTEM.
PAGE 188
Dynamic NonStop SQL Operations Specifying Input Parameters and Output Variables Restrictions for Record Layout If possible, avoid having any fields in your requester or server messages that are an odd number of bytes long. There are some subtle differences in the way SCREENÊCOBOL and TAL generate fields in records when fields contain an odd number of bytes. Therefore, take special care that the field layout in the TAL server matches the layout in the SCREEN COBOL requester.
PAGE 189
Dynamic NonStop SQL Operations Using the SQLDA and Names Buffer If you do not know in advance which columns to select, you can dynamically compile the statement, use DESCRIBE to find out which columns are being selected, and then allocate data buffers to receive the column values. The SQLDA structure contains pointers to the buffers.
PAGE 190
Dynamic NonStop SQL Operations Using the SQLDA and Names Buffer Before DESCRIBE INPUT or DESCRIBE: • • Set the EYE^CATCHER field to the literal SQLDA^EYE^CATCHER. Set NUM^ENTRIES to the number of SQLVAR entries allocated in the SQLDA (the number of input parameters or output columns expected). If you do not know this number in advance, you can get it from the INPUT^NUM or OUTPUT^NUM fields of the SQLSA after the PREPARE statement executes.
PAGE 191
Dynamic NonStop SQL Operations Using Dynamic SQL Programming Techniques The program performs these tasks: • • • • • Issues the PREPARE statement to dynamically compile the SELECT statement. Declares an input SQLDA and an output SQLDA. If needed, the program also declares corresponding input and output names buffers. Uses the DESCRIBE INPUT statement to retrieve the description for the input parameter ?SAL into an input SQLDA. The input SQLDA will need at least one SQLVAR entry for the parameter.
PAGE 192
Dynamic NonStop SQL Operations Overview of a Dynamic SQL Program Setting Up the Environment 1. Use the DATAPAGES directive to cause the BINSERV process to allocate the maximum object program data pages (64) for SQL data structures and statement buffers: ?DATAPAGES 64 2. Copy any required external declarations. Some of these declarations are shown below; however, your application might need additional declarations. ?SOURCE $system.system.
PAGE 193
Dynamic NonStop SQL Operations Overview of a Dynamic SQL Program 5. Declare the SQLCODE variable and any host variables: INT sqlcode; -- required for error processing EXEC SQL BEGIN DECLARE SECTION; ! Pointers to input and output SQLDAs. (The SQLDAs will ! be dynamically allocated later.) INT .isqlda^ptr (sqlda^templ); INT .osqlda^ptr (sqlda^templ); ! Pointers to input and output names buffers. INT .inamesbuf^ptr (namesbuf^templ); INT .
PAGE 194
Dynamic NonStop SQL Operations Overview of a Dynamic SQL Program Use the information returned to the SQLSA structure after the PREPARE statement executes in the next procedure. Handling the Input Parameters If sqlsa.PREPARE.INPUT^NUM is 0, skip these steps. 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).
PAGE 195
Dynamic NonStop SQL Operations Overview of a Dynamic SQL Program If you know the number and data type of your input parameter 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 NULL^INFO and continue as follows according to its value: 0 Do not allocate memory.
PAGE 196
Dynamic NonStop SQL Operations Overview of a Dynamic SQL Program 5. Loop through the SQLVAR array in the output SQLDA (loop n times, where n is the number of columns from sqlsa.PREPARE.OUTPUT^NUM). On each iteration: • • Check DATA^TYPE. If necessary, adjust the data type and reset DATA^LEN and PRECISION accordingly. Allocate a data buffer with size equal to DATA^LEN for the output column. GETPOOL allocates the memory. DATA^LEN is used differently for different data types.
PAGE 197
Dynamic NonStop SQL Operations Overview of a Dynamic SQL Program 2. Begin a TMF transaction (for both SELECT and non-SELECT statements): EXEC SQL BEGIN WORK; 3. Open the cursor: ! Using a cursor name: EXEC SQL OPEN c1 USING DESCRIPTOR :isqlda^ptr; ! Using a cursor host variable: EXEC SQL OPEN :cursor^hostvar USING DESCRIPTOR :isqlda^ptr; 4. Execute a loop to fetch the values and display them.
PAGE 198
Dynamic NonStop SQL Operations • Dynamically Allocating Memory If there were no input parameters: ! Using a statement name: EXEC SQL EXECUTE s1; ! Using a statement host variable: EXEC SQL EXECUTE :statement^hostvar; 7. End the TMF transaction (for both SELECT and other statements): EXEC SQL COMMIT WORK; 8. If you do not want to reexecute the statement, call PUTPOOL to deallocate the memory for the SQLDAs and names buffers and for the values.
PAGE 199
Dynamic NonStop SQL Operations Dynamically Allocating Memory Dynamic allocation of the names buffer is somewhat different, because INCLUDE SQLDA causes an actual string (not a template) to be declared for the names buffer. If you want to allocate this memory dynamically, you should omit the name-string-size parameter from the INCLUDE SQLDA statement. You can then declare a names buffer template with a large value, and use the template to allocate memory.
PAGE 200
Dynamic NonStop SQL Operations Dynamically Allocating Memory Figure 7-1. Using a PREPARE Statement to Compile a Statement EXEC SQL BEGIN DECLARE SECTION; ... STRING .statement^buffer[0:511]; ... EXEC SQL END DECLARE SECTION; ... --Prompt for a new statement. Pass statement^buffer --to a procedure that reads and interprets the input. ... EXEC SQL PREPARE s1 FROM :statement^buffer; VST0701.
PAGE 201
Dynamic NonStop SQL Operations Dynamically Allocating Memory Allocating Memory for the SQLDA Structures 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. Similarly, to allocate memory for the names buffer, you must get the length of the input or output names buffer from the SQLSA structure. For example: -- Save the SQLSA values immediately after PREPARE: inum := sqlsa.PREPARE.
PAGE 202
Dynamic NonStop SQL Operations Dynamically Allocating Memory Initializing EYE^CATCHER and IND^PTR When you allocate the SQLDA, you must explicitly initialize the EYE^CATCHER and IND^PTR fields. You must initialize IND^PTR even if your program is not using indicator variables to handle null values. Your program must initialize the EYE^CATCHER field in the SQLDA. TAL provides the SQLDA^EYE^CATCHER literal declaration, which you use as follows: sqlda^name.
PAGE 203
Dynamic NonStop SQL Operations Dynamically Allocating Memory Figure 7-2. Allocating the SQLDA Structure -- SQLDA^TYPE and SQLVAR were generated by INCLUDE SQLDA -- LITERAL pool^size^in^bytes = 8192d; -- INT .pool^head[0:18]; -- INT .pool[0:pool^size^in^bytes/2d - 1d]; -- Pool must be initialized using DEFINEPOOL before this -- procedure is called INT(32) PROC allocate^sqlda (num^entries); INT num^entries; -- number of input or output -- variables BEGIN INT .
PAGE 204
Dynamic NonStop SQL Operations Dynamically Allocating Memory Allocating Memory for the Values. After the descriptions of the input parameters and output variables are specified, the program must allocate space for the actual values. The user might enter these values for input parameters, or the system might return them for columns (output variables). The following paragraphs describe how to handle input parameters.
PAGE 205
Dynamic NonStop SQL Operations Dynamically Allocating Memory Literal Declarations Your program will need to check DATA^TYPE to make sure that the type of the value to be placed in the buffer pointed to by VAR^PTR matches the type expected by SQL. Table 7-1 shows the literal declarations that represent various data types that you can use in your program. Table 7-1.
PAGE 206
Dynamic NonStop SQL Operations Dynamically Allocating Memory Figure 7-3. Allocating Memory for Values (page 1 of 2) INT PROC setupvarbuffers (sqlda^ptr); STRUCT .EXT sqlda^ptr (sqlda^type); BEGIN INT i; INT mem^reqd; FOR i := 0 TO sqlda^ptr.num^entries-1 DO BEGIN -- Determine the amount of memory needed by this entry's -- data type. Note that for binary and decimal numeric -- items, the byte length is extracted from bits 8 - 15. -- The upper 8 bits store the scale of the item. CASE sqlda.sqlvar[i].
PAGE 207
Dynamic NonStop SQL Operations Using the Names Buffer Figure 7-3. Allocating Memory for Values (page 2 of 2) --Allocate memory for the data value and assign the byte --address of the newly allocated data buffer to VAR^PTR: sqlda.sqlvar[i].var^ptr := GETPOOL(pool^head, $DBL(mem^reqd)); IF sqlda.sqlvar[i].var^ptr = -1d THEN -- Display "Getpool memory management error" message -- and call ABEND VST0703.vsd ...
PAGE 208
Dynamic NonStop SQL Operations Using the Names Buffer Some examples of entries in the names buffer are: Complete Entry Entry Part Description |04|ABCD| |04| |ABCD| 2-byte length 4-character string with value = 4 4-character string |06|ABCDE | |06| |ABCDE | 2-byte length 4-character string with value = 6 5-character string padded with 1 trailing blank |00| | |00| || 2-byte length with value = 0 Null string A complete names buffer with the names shown in this example might look like this: |04|A
PAGE 209
Dynamic NonStop SQL Operations Using the Names Buffer Figure 7-4. Getting Parameter Values (page 1 of 2) INT PROC request^invars(sqlda^ptr, inamesbuf^ptr); STRUCT .EXT sqlda^ptr(sqlda^type); ! input SQLDA pointer STRING .
PAGE 210
Dynamic NonStop SQL Operations Using the Names Buffer Figure 7-4. Getting Parameter Values (page 2 of 2) nameix := 1; FOR i := 0 TO (input^num - 1) DO BEGIN ! Read the length for a parameter name, starting at ! the second byte in the length field (assumes no ! name ever contains more than 255 characters).
PAGE 211
Dynamic NonStop SQL Operations Allocating and Filling in Output Variables Allocating and Filling in 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.
PAGE 212
Dynamic NonStop SQL Operations Allocating and Filling in Output Variables The sequence just described displays names and values repetitively. For example: EMPNUM EMPNAME 2000 JANE ROBERTS EMPNUM EMPNAME 1566 CATHERINE WILLIAMS EMPNUM EMPNAME 1890 RICHARD SMITH You can also display the column names as headings (similar to SQLCI) by executing the following loop. Assume that the value in sqlsa.PREPARE.OUTPUT^NUM was saved in variable OUTNUM. Loop OUTNUM times: 1. Get the length of the column name. 2.
PAGE 213
Dynamic NonStop SQL Operations Allocating and Filling in Output Variables Figure 7-5. Displaying Output (page 1 of 3) -- Declare, open, fetch, and close the cursor. -- before executing this code. -- Global declarations used in printing output: STRING .
PAGE 214
Dynamic NonStop SQL Operations Allocating and Filling in Output Variables Figure 7-5. Displaying Output (page 2 of 3) FOR i := 0 to (output^num - 1) DO BEGIN @param^ := osqlda^ptr.sqlvar[i].var^ptr; datalen := osqlda^ptr.sqlvar[i].data^len; -- Position onamesbuf^ptr to the length prefix in -- the names buffer, save the length, move -- the pointer past the prefix and onto a name, and save -- the column name. Code is the same as that used for -- input parameter names (see "Getting Parameter Values").
PAGE 215
Dynamic NonStop SQL Operations Using Dynamic Cursors Figure 7-5. Displaying Output (page 3 of 3) -- Proceed to handle all the possible data types for -- output values and write the data pointed to by -- the VAR^PTR field in the output SQLDA in a format -- depending on the data type. For complete code, see -- the detailed sample program END; --end CASE --Call WRITE to print the contents of the output buffer END; -- end FOR loop, traversing sqlvar array END; VST0705.
PAGE 216
Dynamic NonStop SQL Operations • Using Dynamic Cursors The PREPARE statement does not have to precede the other statements in the program listing order; however, the PREPARE statement must be executed after DECLARE CURSOR and before DESCRIBE, DESCRIBE INPUT, OPEN, FETCH, and CLOSE.
PAGE 217
Dynamic NonStop SQL Operations Using Statement and Cursor Host Variables Using Statement and Cursor Host Variables TAL supports using statement and cursor host variables in dynamic SQL operations. You can use host variables instead of statement and cursor names, with the DECLARE CURSOR, PREPARE, OPEN, FETCH, and CLOSE statements. For each new statement or cursor name, you store the name in the host variable before executing the statements. Thus, you must code the statements only once.
PAGE 218
Dynamic NonStop SQL Operations Using Statement and Cursor Host Variables Figure 7-6. Statement and Cursor Host Variables (page 1 of 2) ?SQL ?INSPECT ?SYMBOLS ?NOCODE, NOMAP, NOLMAP ?DATAPAGES 64 .buf^end; STRING .home^term[0:11]; INT INT home^term^fnum; INT .ibuf[0:19]; STRING .sbuf := @ibuf '<<' 1; INT i; sqlcode; INT EXEC SQL BEGIN DECLARE SECTION; INT(32) answer; STRUCT .curs[0:2]; - - Table of 3 cursor names BEGIN STRING name[0:1]; END; STRUCT .
PAGE 219
Dynamic NonStop SQL Operations Using Statement and Cursor Host Variables Figure 7-6. Statement and Cursor Host Variables (page 2 of 2) PROC p MAIN; BEGIN CALL INITIALIZER; CALL MYTERM (home^term); CALL OPEN (home^term, home^term^fnum); curs[0].name ':=' "c1"; curs[1].name ':=' "c2"; curs[2].name ':=' "c3"; stmt[0].name ':=' "s1"; stmt[1].name ':=' "s2"; stmt[2].name ':=' "s3"; - - Blank fill text buffer: FOR i := 0 to 2 DO text[i].str ':=' " " & text[i].str FOR TEXT^LEN - 1; text[0].
PAGE 220
Dynamic NonStop SQL Operations Handling Null Values answer = 32 answer = 65 answer answer answer answer answer = = = = = 89000 69000 68000 96000 65000 answer = 3000 answer = 4000 answer = 4100 Example of an Application In a possible application for statement and cursor host variables, a server could use a loop to initialize the arrays of statement and cursor host variable names and the array of statements and to execute the PREPARE and DECLARE CURSOR statements.
PAGE 221
Dynamic NonStop SQL Operations Handling Null Values Allocating Memory for a Possible Null Value You can allocate memory for indicator variables at the same time you allocate memory for other values. If NULL^INFO is -1, you allocate a buffer for the indicator value and assign its address to the IND^PTR field of the appropriate SQLVAR entry. This example shows code for this allocation. The statements take place within a FOR loop to handle each input parameter or output column.
PAGE 222
Dynamic NonStop SQL Operations Handling Null Values INT .EXT param^(sql^types); --pointer to buffer that --will receive parameter value --Procedure code: --Set pointer to storage for current parameter value: @param^ := isqlda^ptr.sqlvar[i].var^ptr; -- call READX or WRITEREAD, depending on data type ... IF (isqlda^ptr.sqlvar[i].null^info = -1) AND (param^. datatype-field = "?") THEN BEGIN @ind^ := isqlda^ptr.sqlvar[i].
PAGE 223
Dynamic NonStop SQL Operations Handling Null Values byte length, name is a parameter name, ind-len is the length of an indicator parameter name, and ind-name is an indicator parameter name. Each instance of IND^PTR points to the length field for the corresponding indicator parameter name. S Q L V A R [1 ].V A R ^ P T R le n 1 nam e1 S Q L V A R [1 ].IN D ^P T R S Q L V A R [2 ].V A R ^P T R in d -le n -1 in d -n a m e -1 le n 2 nam e2 S Q L V A R [2 ].
PAGE 224
Dynamic NonStop SQL Operations HP NonStop SQL Programming Manual for TAL—527887-001 7-40 Handling Null Values
PAGE 225
A Sample NonStop SQL Database This appendix describes the sample NonStop SQL database used by some of the examples in this manual. There is one node, the \SYS1 system. The $VOL1 volume on this system contains the PERSNL, SALES, and INVENT subvolumes. Each subvolume contains a catalog and tables relating to a specific operation in the organization: PERSNL Contains the EMPLOYEE, JOB, and DEPT tables, which hold personnel data.
PAGE 226
Sample NonStop SQL Database For more information about SQLCI, see the SQL/MP Version Management Guide. Figure A-1.
PAGE 227
Sample NonStop SQL Database Figure A-2. Sample Database Source File (page 1 of 4) ! ! Personnel (PERSNL) ! ! ?SECTION EMPLOYEE ! Record Definition for table \SYS.$VOL.PERSNL.EMPLOYEE ! Definition current at 17:10:47 - 04/20/90 struct employee^type(*); BEGIN int empnum; string first^name[ 0: 14 ]; string last^name[ 0: 19 ]; int deptnum; int jobcode; salary; ! scale is 2 int(32) END; ?SECTION DEPT ! Record Definition for table \SYS.$VOL.PERSNL.
PAGE 228
Sample NonStop SQL Database Figure A-2. Sample Database Source File (page 2 of 4) ?SECTION JOB ! Record Definition for table \SYS.$VOL.PERSNL.JOB ! Definition current at 17:11:12 - 04/20/90 struct job^type(*); BEGIN int jobcode; struct jobdesc; begin int len; string val[ 0:17 ]; end; END; ! ! ! Sales (SALES) ! ! ?SECTION CUSTOMER ! Record Definition for table \SYS.$VOL.SALES.
PAGE 229
Sample NonStop SQL Database Figure A-2. Sample Database Source File (page 3 of 4) ?SECTION ODETAIL ! Record Definition for table \SYS.$VOL.SALES.ODETAIL ! Definition current at 17:11:42 - 04/20/90 struct odetail^type(*); BEGIN int(32) ordernum; int partnum; int(32) ! scale is 2 unit^price; int(32) qty^ordered; END; ?SECTION PARTS ! Record Definition for table \SYS.$VOL.SALES.
PAGE 230
Sample NonStop SQL Database Figure A-2. Sample Database Source File (page 4 of 4) ?SECTION PARTSUPP ! Record Definition for table \SYS.$VOL.INVENT.PARTSUPP ! Definition current at 17:12:11 - 04/20/90 struct partsupp^type(*); BEGIN int partnum; suppnum; int int(32) partcost; ! scale is 2 int(32) qty^received; END; ?SECTION PARTLOC ! Record Definition for table \SYS.$VOL.INVENT.
PAGE 231
B Examples of Static NonStop SQL Programs This appendix describes these sample static SQL programs: • • Insertion program (TALTEST) Date-time program (TALDT) Insertion Program The insertion program (TALTEST) uses the NonStop SQL sample database, which is described in Appendix A, Sample NonStop SQL Database. TALTEST inserts a part name, part number, and quantity into the PARTS table and then inserts a new part into the PARTLOC table.
PAGE 232
Examples of Static NonStop SQL Programs Figure B-1. Insertion Program Output RUN TALTESTO OUTPUT: START PROGRAM NEWPART ****** SUPPLIER IS ATTRACTIVE CORP BEGIN INSERT ON PARTS ************ BEGIN INSERT ON PARTLOC ********** COMMIT TRANSACTION PART ADDED. PROGRAM ENDS VSTB01.
PAGE 233
Examples of Static NonStop SQL Programs Insertion Program The TAL compiler listing for TALTEST is shown on the following pages. Page 1 [1] $VOL1.S04.TALTEST 1991-10-15 13:40:20 TAL - T9250C30 - (01NOV91) Copyright Tandem Computers Incorporated 1976, 1978, 1981-83, 1985, 1987-91 1. 000000 0 0 ?SQL NOWHENEVERLIST 2. 0000000 0 ?SYMBOLS, INSPECT, SAVEABEND, NOMAP, NOCODE, NOGMAP, NOLMAP, DATAPAGES 64 3. 000000 0 0 ?SEARCH \SYS1.$SYSTEM.SYSTEM.TALLIB Search file: \SYS1.$SYSTEM.SYSTEM.
PAGE 234
Examples of Static NonStop SQL Programs Page 2 [3] $SYSTEM.#3185 13:40:20 Insertion Program 1991-10-15 5. 000207 0 1 int suppnum /SMALLINT UNSIGNED/; 6. 000207 0 1 string suppname[0:17]; 7. 000207 0 1 string street[0:21]; 8. 000207 0 1 string city[0:13]; 9. 000207 0 1 string state[0:11]; 10. 000207 0 1 string postcode[0:9]; 11. 000207 0 1 END; Source file: [1] $VOL1.S04.TALTEST 1991-10-15 13:35:30 35. 000207 0 0 EXEC SQL INVOKE =partloc AS partloc^type; 35.
PAGE 235
Examples of Static NonStop SQL Programs Page 3 [1] $VOL1.S04.TALTEST 72. 000000 0 0 73. 000000 1 0 74. 000000 1 1 75. 000000 1 1 76. 000000 1 1 77. 000001 1 1 78. 000013 1 1 79. 000013 1 1 [ 80. 000025 1 1 81. 000025 1 1 82. 000025 1 1 83. 000030 1 1 84. 000042 1 1 85. 000047 1 1 86. 000061 1 1 87. 000066 1 1 88. 000066 1 1 89. 000066 1 1 90. 000070 1 1 91. 000070 1 1 92. 000070 1 1 93. 000073 1 1 94. 000103 1 1 95. 000114 1 1 96. 000126 1 1 97. 000126 1 1 98. 000126 1 1 99. 000126 1 1 100.
PAGE 236
Examples of Static NonStop SQL Programs J Page 4 [1] $VOL1.S04.TALTEST 108. 000000 0 0 109. 000000 1 0 110. 000000 1 1 111. 112. 113. 114. 115. 116. 117. 118. 119. 120. 121. 122. 123. 000000 000046 000046 000046 000046 000046 000046 000046 000046 000046 000046 000207 000207 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 124. 125. 126. 127. 128. 129. 130. 131.
PAGE 237
Examples of Static NonStop SQL Programs Page 158. 159. 160. 161. 162. 163. 164. 165. 166. 167. 168. 169. 170. 171. 172. 173. 174. 175. 176. 177. 178. 179. Insertion Program 6 [1] $VOL1.S04.TALTEST 1991-10-15 13:40:20 000000 0 0 PROC DO^ADD^TO^PARTLOC; 000000 1 0 BEGIN 000000 1 1 partloc^rec.loc^code ':=' in^data^rec.in^loc^code 000001 1 1 FOR $OCCURS(partloc^rec.loc^code); 000010 1 1 --Length of PARTLOC^REC.LOC^CODE is used because it's the smaller 000010 1 1 --size. 000010 1 1 000010 1 1 partloc^rec.
PAGE 238
Examples of Static NonStop SQL Programs Page 195. 196. 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. Insertion Program 8 [1] $VOL1.S04.
PAGE 239
Examples of Static NonStop SQL Programs Page 241. 242. 243. 244. 245. 246. 247. 248. 249. 250. 251. 252. 253. 254. 9 [1] $VOL1.S04.TALTEST 1991-10-15 13:40:20 000000 0 0 PROC ABORT^TRANSACTION; 000000 1 0 BEGIN 000000 1 1 EXEC SQL ROLLBACK WORK; 000040 1 1 sbuf ':=' "TRANSACTION ABORTED"->@buf^end; 000051 1 1 CALL WRITE(home^term^num,ibuf,@buf^end '-' @sbuf); 000063 1 1 END; 000000 0 0 000000 0 0 000000 0 0 PROC SUCCESSFUL^COMPLETION; 000000 1 0 BEGIN 000000 1 1 sbuf ':=' "PART ADDED.
PAGE 240
Examples of Static NonStop SQL Programs Date-Time Program Page 11 [1] $VOL1.S04.TALTEST 1991-10-15 13:40:20 BINDER AND COMPILER STATISTICS BINDER - OBJECT FILE BINDER - T9621C30 - (01NOV91) SYSTEM \SYS1. Copyright Tandem Computers Incorporated 1982-1989, 1991 Object file $VOL1.S04.
PAGE 241
Examples of Static NonStop SQL Programs • • • Date-Time Program Insert a new project name, start date, end date, and wait time. Add more wait time to a project by updating the WAIT_TIME column in the PROJECTS table. The original dates in the START_DATE and END_DATE columns in the PROJECTS table remain unchanged. Print a report showing the original dates and the new dates. The report function computes the new dates by adding wait time to both the start date and end date.
PAGE 242
Examples of Static NonStop SQL Programs Date-Time Program Figure B-2. Date-Time Program Run (page 1 of 3) 14> SQLCI >>select * from =projects; PROJECT_NAME START_DATE END_DATE 920 134 1988-02-21:20:30 1970-01-01:00:00 1989-03-21:20:30 1978-03-21:20:30 WAIT_TIME 30 30 - - - 2 row(s) selected.
PAGE 243
Examples of Static NonStop SQL Programs Date-Time Program Figure B-2.
PAGE 244
Examples of Static NonStop SQL Programs Date-Time Program Figure B-2.
PAGE 245
Examples of Static NonStop SQL Programs Date-Time Program The TAL compiler listing for TALDT is shown on the following pages. Page 1 [1] $VOL1.S04.TALDT 1991-10-15 13:41:07 TAL - T9250C30 - (01NOV91) Copyright Tandem Computers Incorporated 1976, 1978, 1981-83, 1985, 1987-91 1. 000000 0 0 ?SQL NOWHENEVERLIST 2. 000000 0 0 ?SYMBOLS, INSPECT, SAVEABEND, NOMAP, NOCODE, NOGMAP, NOLMAP, DATAPAGES 64 3. 000000 0 0 ?SEARCH \SYS1.$SYSTEM.SYSTEM.TALLIB Search file: \SYS1.$SYSTEM.XTALC30.
PAGE 246
Examples of Static NonStop SQL Programs Date-Time Program Page 2 [1] $VOL1.S04.TALDT 1991-10-15 13:41:07 41. 000265 0 0 EXEC SQL END DECLARE SECTION; 42. 000265 0 0 43. 000265 0 0 INT SQLCODE; 44. 000265 0 0 45. 000265 0 0 STRING SEL^INDEX; 46. 000265 0 0 -- for user's choice from menu 47. 000265 0 0 48. 000265 0 0 EXEC SQL INCLUDE SQLCA; 48. 000265 0 0 EXEC SQL INCLUDE SQLCA; Source file: [3] $SYSTEM.#3188 1991-10-15 13:41:27 1. 000265 0 0 struct .SQLCA; 2. 000265 0 0 BEGIN 3.
PAGE 247
Examples of Static NonStop SQL Programs Date-Time Program Page 3 [1] $VOL1.S04.TALDT 1991-10-15 13:41:07 94. 000000 1 1 95. 000000 1 1 -- Temporary variable to save the value of SQLCODE for later 96. 000000 1 1 -- checking. ROLLBACK WORK sets SQLCODE to 0. 97. 000000 1 1 INT TEMP^SQLCODE; 98. 000000 1 1 99. 000000 1 1 TEMP^SQLCODE := SQLCODE; 100. 000003 1 1 101. 000003 1 1 SBUF ':=' " RECORD NOT FOUND ***** "->@BUF^END; 102. 000014 1 1 CALL WRITE(HOME^TERM^NUM,IBUF,@BUF^END '-' @SBUF); 103.
PAGE 248
Examples of Static NonStop SQL Programs Page 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. Date-Time Program 4 [1] $VOL1.S04.TALDT 1991-10-15 13:41:07 000156 1 1 USER^PROJECTS^REC.
PAGE 249
Examples of Static NonStop SQL Programs Page 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. 254. 255. 256. 257. 258. 259. 260. 261. 262. 263. 264. Date-Time Program 5 [1] $VOL1.S04.
PAGE 250
Examples of Static NonStop SQL Programs Page 265. 266. 267. 268. 269. 270. 271. 272. 273. 274. 275. 276. 277. 278. 279. 280. 281. 282. 283. 284. 285. 286. 287. 288. 289. 290. 291. 292. 293. 294. 295. 296. 297. 298. 299. 300. 301. 302. 303. 304. 305. 306. 307. 308. 309. 310. 311. 312. 313. 314. 315. 316. 317. 318. 319. 320. 321. Date-Time Program 6 [1] $VOL1.S04.
PAGE 251
Examples of Static NonStop SQL Programs Page 322. 323. 324. 325. 326. 327. 328. 329. 330. 331. 332. 333. 334. 335. 336. 337. 338. 339. 340. 341. 342. 343. 344. 345. 346. 347. 348. 349. 350. 351. 352. 353. 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. Date-Time Program 7 [1] $VOL1.S04.TALDT 1991-10-15 13:41:07 000104 1 1 BEGIN 000104 1 2 SBUF ':=' "PROJECT NAME: "->@BUF^END; 000115 1 2 BUF^END ':=' NEW^PROJECTS^REC.
PAGE 252
Examples of Static NonStop SQL Programs Page 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. 410. 411. 412. 413. 414. 415. 416. 417. 418. 419. 420. 421. 422. Date-Time Program 8 [1] $VOL1.S04.TALDT 1991-10-15 13:41:07 000067 1 1 USER^PROJECTS^REC.END^DATE ':=' 000067 1 1 [ $OCCURS(USER^PROJECTS^REC.END^DATE) * [" "] ]; 000101 1 1 USER^PROJECTS^REC.
PAGE 253
Examples of Static NonStop SQL Programs Date-Time Program Page 9 [1] $VOL1.S04.TALDT 1991-10-15 13:41:07 BINDER AND COMPILER STATISTICS BINDER - OBJECT FILE BINDER - T9621C30 - (01NOV91) SYSTEM \PRUNE Copyright Tandem Computers Incorporated 1982-1989, 1991 Object file $VOL1.S04.
PAGE 254
Examples of Static NonStop SQL Programs HP NonStop SQL Programming Manual for TAL—527887-001 B-24 Date-Time Program
PAGE 255
C Examples of Dynamic NonStop SQL Programs This appendix describes these sample dynamic SQL programs: • Simple program (TALDYNEZ) TALDYNEZ processes a SELECT statement that is partially coded into the program; the user supplies the WHERE clause. The SQLDA structures and data buffers are allocated at compile time using the INCLUDE SQLDA directive. • Detailed program (TALDYN) TALDYN allows the user to enter any SQL statement from a terminal. The SQLDA structures and data buffers are allocated at run time.
PAGE 256
Examples of Dynamic NonStop SQL Programs Dynamic SQL Program Figure C-1. Output for the Dynamic SQL Program 10> RUN TALDEZO PLEASE ENTER: 1 2 3 ---- To find average salary based on employee # To find average salary based on job code To find average salary based on department # Please enter selection: 1 Please specify the comparison criteria: (for example: > 500, = 1000, <= 250) Enter the comparison criteria now: > 500 THE AVERAGE SALARY IS: 52250 11> VSTC01.
PAGE 257
Examples of Dynamic NonStop SQL Programs Dynamic SQL Program The TAL compiler listing for TALDYNEZ is shown on the following pages. Page 1 [1] $VOL1.S04.TALDYNEZ 1991-10-15 13:41:54 TAL - T9250C30 - (01NOV91) Copyright Tandem Computers Incorporated 1976, 1978, 1981-83, 1985, 1987-91 1. 000000 0 0 2. 000000 0 0 ?SQL NOWHENEVERLIST ?SYMBOLS, INSPECT, SAVEABEND, NOMAP, NOCODE, NOGMAP, NOLMAP, DATAPAGES 64 3. 000000 0 0 ?SEARCH \SYS1.$SYSTEM.SYSTEM.TALLIB Search file: \SYS1.$SYSTEM.SYSTEM.
PAGE 258
Examples of Dynamic NonStop SQL Programs Dynamic SQL Program Page 2 [4] $SYSTEM.#3190 1991-10-15 13:41:54 6. 001473 0 1 BEGIN 7. 001473 0 2 INT num^tables; 8. 001473 0 2 STRUCT stats[0:15]; 9. 001473 0 2 BEGIN 10. 001473 0 3 STRING table^name[0:23]; 11. 001473 0 3 INT(32) records^accessed; 12. 001473 0 3 INT(32) records^used; 13. 001473 0 3 INT(32) disc^reads; 14. 001473 0 3 INT(32) messages; 15. 001473 0 3 INT(32) message^bytes; 16. 001473 0 3 INT waits; 17. 001473 0 3 INT escalations; 18.
PAGE 259
Examples of Dynamic NonStop SQL Programs Dynamic SQL Program Page 3 [5] $SYSTEM.#3191 1991-10-15 13:41:54 19. 002336 0 2 ! fraction precision = precision.<8:15> 20. 002336 0 2 INT null^info; 21. 002336 0 2 INT(32) var^ptr; 22. 002336 0 2 INT(32) ind^ptr; 23. 002336 0 2 FIXED reserved; 24. 002336 0 2 END; 25. 002336 0 1 END; Source file: [1] $VOL1.S04.TALDYNEZ 1991-10-15 13:36:01 52. 002336 0 0 STRUCT .OSQLDA(osqlda^type); 53. 002354 0 0 54. 002354 0 0 EXEC SQL END DECLARE SECTION; 55. 002354 0 0 56.
PAGE 260
Examples of Dynamic NonStop SQL Programs Dynamic SQL Program Page 4 [1] $VOL1.S04.TALDYNEZ 1991-10-15 13:41:54 104. 000061 1 1 OSQLDA.NUM^ENTRIES := 1; 105. 000064 1 1 106. 000064 1 1 -- Initialize IND^PTR field to null. You must initialize 107. 000064 1 1 -- IND^PTR even if the program will not handle null values. 108. 000064 1 1 OSQLDA.SQLVAR[0].IND^PTR := NULL^ADDR; 109. 000070 1 1 110. 000070 1 1 EXEC SQL DESCRIBE DYNCMD INTO :OSQLDA; 111. 000140 1 1 112.
PAGE 261
Examples of Dynamic NonStop SQL Programs Dynamic SQL Program Page 5 [1] $VOL1.S04.TALDYNEZ 1991-10-15 13:41:54 161. 000000 0 0 PROC GET^CMD(CMD); 162. 000000 1 0 163. 000000 1 0 STRING .CMD; 164. 000000 1 0 165. 000000 1 0 BEGIN 166. 000000 1 1 STRING COLUMN [0:9]; -- column to be used in 167. 000000 1 1 -- WHERE clause 168. 000000 1 1 STRING SEL^INDEX; -- selects which column to 169. 000000 1 1 -- put in WHERE clause 170. 000000 1 1 STRING PREDICATE [0:9]; -- comparison predicate to 171.
PAGE 262
Examples of Dynamic NonStop SQL Programs Dynamic SQL Program Page 6 [1] $VOL1.S04.TALDYNEZ 1991-10-15 13:41:54 218. 000401 1 1 SBUF ':=' "Enter the comparison criteria now: "->@BUF^END; 219. 000412 1 1 CALL WRITEREAD(HOME^TERM^NUM,IBUF,@BUF^END '-' @SBUF,20, PRED^SIZE); 220. 000425 1 1 221. 000425 1 1 PREDICATE ':=' SBUF FOR PRED^SIZE; 222. 000432 1 1 223. 000432 1 1 -- Construct the SQL statement: 224. 000432 1 1 CMD^END ':=' COLUMN FOR COL^SIZE -> @CMD^END; 225.
PAGE 263
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 7 [1] $VOL1.S04.TALDYNEZ 1991-10-15 13:41:54 BINDER AND COMPILER STATISTICS BINDER - OBJECT FILE BINDER - T9621C30 - (01NOV91) SYSTEM \SYS1 Copyright Tandem Computers Incorporated 1982-1989, 1991 Object file $VOL1.S04.
PAGE 264
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program 3. Prepares the SQL statement and assigns it a statement name (statement and cursor host variables are not used). 4. Determines the data types of the: • • Input parameters and moves them to the host variables of the corresponding data types Output variables and moves them to the host variables of the corresponding data types 5. Sets up the SQLDA to point to the storage for the variables referenced by the query.
PAGE 265
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Figure C-2. Output for the Detailed Dynamic SQL Program 10> RUN TALDYNO This is DYNAMIC SQL test. Enter SQL statement or SAME to reuse last statement or END: >>select ordernum, custnum from =orders where ordernum in (select ordernum from =odetail where partnum = 6400); ORDERS.ORDERNUM ORDERS.CUSTNUM 200320 21 ORDERS.ORDERNUM ORDERS.CUSTNUM 300350 543 ORDERS.ORDERNUM ORDERS.CUSTNUM 800660 3210 ORDERS.ORDERNUM ORDERS.
PAGE 266
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program The TAL compiler listing for TALDYN is shown on the following pages. Page 1 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 TAL - T9250C30 - (01NOV91) Copyright Tandem Computers Incorporated 1976, 1978, 1981-83, 1985, 1987-91 1. 000000 0 0 ?SQL NOWHENEVERLIST 2. 000000 0 0 ?SYMBOLS, INSPECT, SAVEABEND, NOMAP, NOCODE, NOGMAP, NOLMAP, DATAPAGES 64 3. 000000 0 0 ?SEARCH \SYS1.$SYSTEM.SYSTEM.TALLIB Search file: \SYS1.$SYSTEM.SYSTEM.
PAGE 267
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 2 [3] $SYSTEM.#3193 1991-10-15 13:42:28 28. 000327 0 2 INT sql^statement^type; 29. 000327 0 2 END; -- prepare 30. 000327 0 1 END; -- sqlsa Source file: [1] $VOL1.S04.TALDYN 1991-10-15 13:33:55 17. 001172 0 0 18. 001172 0 0 !-----------------------------------------------------------19. 001172 0 0 ! Declare SQLDA for input parameters and output variables. 20. 001172 0 0 ! 21.
PAGE 268
Examples of Dynamic NonStop SQL Programs Page 3 [1] 42. 001172 43. 001172 44. 001172 45. 001172 46. 001172 47. 001172 48. 001172 49. 001172 50. 001172 51. 001336 52. 001336 53. 001336 54. 001336 55. 001336 56. 001336 57. 001336 58. 001336 59. 001336 60. 001336 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.
PAGE 269
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 4 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 99. 012365 0 1 FIXED(3) v^numeric = v^char; 100. 012365 0 1 REAL v^float = v^char; 101. 012365 0 1 REAL(64) v^double = v^char; 102. 012365 0 1 STRING v^decimal[0:18] = v^char; 103. 012365 0 1 STRING v^datetime[0:25] = v^char; 104. 012365 0 1 END; 105. 012365 0 0 106. 012365 0 0 --Copy in declarations for data type literals: 107. 012365 0 0 ?NOLIST, SOURCE $SYSTEM.SYSTEM.TALDECS(SQLDA) 109.
PAGE 270
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 5 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 Output formatting stuff 130. 000000 0 0 ! 131. 000000 0 0 ! Here are a few DEFINEs to make it a little easier to format 132. 000000 0 0 ! and print messages. 133. 000000 0 0 ! 134. 000000 0 0 ! put a string into the line, starting at beginning of the line 135. 000000 0 0 DEFINE PUT^STR (s) = @next^buf := @sbuf; 136. 000000 0 0 next^buf ':=' s -> @next^buf #; 137. 000000 0 0 138.
PAGE 271
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 6 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 display^result 167. 000000 0 0 PROC display^result (sqlda, cnames); 168. 000000 1 0 169. 000000 1 0 !*********************************************************! 170. 000000 1 0 ! PROC display^result ! 171. 000000 1 0 ! displays the current row of a SELECT statement ! 172. 000000 1 0 !*********************************************************! 173. 000000 1 0 174. 000000 1 0 STRUCT .
PAGE 272
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 7 [1] $VOL1.S04.TALDYN 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. 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.
PAGE 273
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 8 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 display^result 281. 282. 283. 284. 285. 286. 287. 288. 289.
PAGE 274
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 9 [1] $VOL1.S04.TALDYN 291. 292. 293. 294. 295. 296. 297. 298. 299. 300. 301. 302. 303. 304. 305. 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 306. 000000 1 1 307. 000000 1 1 308. 000000 1 1 309. 000000 1 1 310. 000000 1 1 311. 000000 1 1 312. 000000 1 1 313. 000000 1 1 314. 000000 1 1 315. 000000 1 1 316.
PAGE 275
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 10 [1] $VOL1.S04.TALDYN 348. 349. 350. 351. 000153 000153 000153 000153 352. 353. 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.
PAGE 276
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 11 [1] $VOL1.S04.TALDYN 405. 406. 407. 408. 409. 410. 000653 000655 000655 000676 000676 000712 1 1 1 1 1 1 3 3 4 4 3 3 411. 000726 1 3 412. 000731 1 3 413. 000733 1 3 414. 415. 416. 417. 418. 419. 420. 421. 422. 423. 424. 425. 426. 427. 428. 429. 430. 431.
PAGE 277
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 12 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 request^invars _SQLDT_INT_Y_Y .. _SQLDT_INT_D_F -> PUT^STR ("Please enter a date/time interval"); if namelen then begin PUT^STR^MID (" for " & param^name for namelen); end; PUT^STR^MID (": "); PRINT^LINE; param^.v^datetime ':=' " " & param^.v^datetime for $OCCURS(param^.v^datetime)-1; call READX(term,param^.v^datetime,$OCCURS (param^.v^datetime)); 462. 463. 464. 465. 466. 467. 468. 469.
PAGE 278
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 13 [1] $VOL1.S04.TALDYN 495. 496. 497. 498. 499. 500. 501. 502. 503. 504. 505. 506. 507. 508. 509. 510. 511. 512. 513. 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000003 000010 000010 000020 000020 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 514. 515. 516. 517. 518. 519. 520. 521. 522. 523. 524. 525. 526. 527. 528.
PAGE 279
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 14 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 read^query PUT^STR ("Re-executing query >> "); PRINT^LINE; call WRITEX(term,host1.str,host1.len); 552. 553. 554. 555. 556. 557. 558. 000245 000263 000276 000312 000312 000313 000313 1 1 1 1 1 1 1 2 2 2 2 2 2 2 559. 560. 561. 562. 563. 564. 565. 566. 567.
PAGE 280
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 15 [1] $VOL1.S04.TALDYN 569. 570. 571. 572. 573. 574. 575. 576. 577. 000000 000000 000000 000000 000000 000000 000000 000000 000000 0 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 578. 579. 580. 581. 000000 000000 000000 000000 1 1 1 1 0 0 0 0 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.
PAGE 281
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 16 [1] $VOL1.S04.TALDYN 626. 627. 628. 629. 000075 000075 000075 000075 1 1 1 1 3 3 3 3 630. 000075 1 3 631. 000110 1 3 632. 000123 1 3 633. 634. 635. 636. 637. 638. 639. 640. 641.
PAGE 282
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 17 [1] $VOL1.S04.TALDYN 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. 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 669. 670. 671. 672. 673. 674. 675. 676. 677.
PAGE 283
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 18 [1] $VOL1.S04.TALDYN 700. 701. 702. 703. 704. 705. 706. 707. 708. 709. 710. 711. 712. 713. 714. 000067 000067 000067 000067 000103 000104 000120 000121 000137 000170 000203 000210 000550 000550 000550 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 715. 000550 1 716. 000550 1 717. 000550 1 718. 719. 720. 721. 722. 723. 724. 725. 726. 000571 000606 000606 000624 000637 000644 000644 000644 000644 1 1 1 1 1 1 1 1 1 727. 728. 729. 730. 731.
PAGE 284
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 19 [1] $VOL1.S04.TALDYN 750. 751. 752. 753. 754. 000000 000000 000000 000000 000000 0 1 1 1 1 0 0 0 0 0 1991-10-15 13:42:28 allocate^sqlda INT(32) PROC allocate^sqlda (num^entries); !******************************************************! ! PROC allocate^sqlda: ! ! This proc allocates an sqlda structure with num^entries entries. ! ! It also initializes the sqlda and the sqlvars. ! ! A names buffer is also allocated.
PAGE 285
Examples of Dynamic NonStop SQL Programs Page 20 [1] $VOL1.S04.TALDYN 804. 805. 806. 807. 808. 809. 810. 811. 812. 813. 814. 815. 816. 817. 818. 819. 820. 821. 822. 823. 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 000000 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 824. 825. 826. 827. 828. 829. 830. 831. 832. 833. 834. 835. 836. 837. 838. 839. 840. 841. 842.
PAGE 286
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 21 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 free^sqlda 861. 000241 862. 000241 863. 000000 Page 22 [1] cleanup 865. 000000 866. 000000 867. 000000 868. 000000 1 1 1 1 END; ! of proc free^sqlda 0 0 $VOL1.S04.TALDYN 0 1 1 1 0 0 0 0 869. 000000 1 0 870. 871. 872. 873. 874. 875. 876. 877. 878. 879. 880. 881. 882. 883. 884. 885. 886. 887. 888. 889. 890. 891. 892. 893. 894. 895. 896. 897. 898. 899. 900. 901.
PAGE 287
Examples of Dynamic NonStop SQL Programs Page 23 [1] $VOL1.S04.TALDYN 903. 904. 905. 906. 907. 000000 000000 000000 000000 000000 0 1 1 1 1 0 0 0 0 0 908. 909. 910. 911. 912. 000000 000000 000000 000000 000000 1 1 1 1 1 0 0 0 1 1 913. 914. 915. 916. 917. 918. 919. 920. 921. 922. 923. 924. 925. 926. 927. 928. 929. 930. 931. 932. 933. 934. 935. 936. 937. 938. 939. 940. 941.
PAGE 288
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 24 [1] $VOL1.S04.TALDYN 943. 944. 945. 946. 947. 948. 949. 950. 951. Page 000000 000000 000000 000000 000000 000000 000011 000023 000000 25 [1] 1991-10-15 13:42:28 sql^warning^handler PROC sql^warning^handler; !******************************************************! ! Displays an SQL warning.
PAGE 289
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 26 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 dyn^tal main procedure 982. 000000 0 0 983. 000000 0 0 ! Declare WHENEVER clauses for error checking on SQL statements. ! Note: Code that allocates memory and checks the input query must ! do separate error checking, because such code does not involve ! statements that set the SQLCODE variable. 984. 000000 0 0 985. 000000 0 0 986. 000000 0 0 987. 000000 0 0 988. 000000 0 0 989. 990.
PAGE 290
Examples of Dynamic NonStop SQL Programs 1033. 1034. 1035. 1036. 1037. 1038. 000022 000040 000053 000053 000053 000053 1 1 1 1 1 1 1 1 1 1 1 1 PUT^STR ("This is DYNAMIC SQL test."); PRINT^LINE; !*****************************************************! ! Input SQL query from terminal. ! !*****************************************************! Page 27 [1] $VOL1.S04.TALDYN 1039. 1040. 1041. 1042. 000053 000053 000053 000053 1 1 1 1 1043. 1044. 1045. 1046. 000053 000053 000054 000054 1 1 1 1 1047.
PAGE 291
Examples of Dynamic NonStop SQL Programs 1093. 000345 1 1 1094. 000345 1 2 1095. 000351 1 2 Detailed Dynamic SQL Program begin @sda^o := allocate^sqlda (out^numvars); if @sda^o = NULL^ADDR then Page 28 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 dyn^tal main procedure 1096. 1097. 1098. 1099. 1100. 1101. 1102. 1103. 1104. 1105. 1106. 1107. 1108. 1109. 1110. 1111. 1112. 1113. 1114.
PAGE 292
Examples of Dynamic NonStop SQL Programs 1150. 000703 1 1 1151. 000703 1 1 1152. 000703 1 1 Detailed Dynamic SQL Program !--------------------------------------! ! Get information on output variables. ! Page 29 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 dyn^tal main procedure 1153. 000703 1 1 !--------------------------------------! 1154. 000703 1 1 1155. 000703 1 1 IF out^numvars > 0 THEN 1156. 000706 1 1 BEGIN 1157. 000706 1 2 EXEC SQL DESCRIBE s1 INTO :sda^o NAMES INTO :cname^o.val; 1158.
PAGE 293
Examples of Dynamic NonStop SQL Programs Detailed Dynamic SQL Program Page 30 [1] $VOL1.S04.TALDYN 1991-10-15 13:42:28 dyn^tal main procedure 1210. 1211. 1212. 1213. 1214. 1215. 1216. 1217. 1218. 1219. 1220. 1221. 1222. 1223. 1224. 1225. 1226. 1227. 1228. 1229. 1230. 1231. 001267 001267 001277 001315 001334 001350 001363 001435 001506 001507 001507 001507 001507 001507 001507 001513 001517 001517 001520 001520 001520 001520 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1232. 1233. 1234. 1235. 1236.
PAGE 294
Examples of Dynamic NonStop SQL Programs 1201. 1202. 1203. 1204. 1205. 001205 001205 001205 001205 001205 1 1 1 1 1 2 2 2 2 2 1206. 1207. 1208. 1209. 001210 001210 001210 001264 1 1 1 1 2 2 3 3 ! Disable SQL warning checking EXEC SQL WHENEVER SQLWARNING CONTINUE; WHILE sqlcode >= 0 do ! continue fetching rows as long as ! there are no errors or only warnings begin EXEC SQL FETCH c1 USING DESCRIPTOR :sda^o; if sqlcode = 100 then ! We've fetched all the rows Page 31 [1] $VOL1.S04.TALDYN 1267. 1268.
PAGE 295
D NonStop SQL Version Issues NonStop SQL release C30 includes features that are incompatible with NonStop SQL release C10. Usually, these features provide compatibility with ANSI and ISO SQL standards.
PAGE 296
NonStop SQL Version Issues • • • • • Version 1 and Version 2 Definitions Columns that can contain null values Clustering keys Constraints with constants using any Version 2 feature NO AUDITCOMPRESS, HEADING, and HELP TEXT attributes UNION and JOIN clauses of the SELECT statement Table D-1 shows the Version 2 entities and their descriptions. Table D-1. Version 2 Items and Descriptions Version 2 Entity Description Table A table that uses any Version 2 feature.
PAGE 297
NonStop SQL Version Issues Summary of Incompatible Changes Table D-3.
PAGE 298
NonStop SQL Version Issues Migrating a C10 Program to Run on a C30 System Table D-4. Incompatible NonStop SQL Release C30 Features 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.
PAGE 299
NonStop SQL Version Issues Migrating a C10 Program to Run on a C30 System default is DEFAULT NULL.
PAGE 300
NonStop SQL Version Issues Migrating a C10 Program to Run on a C30 System 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 TAL compiler detects the use of these reserved words and issues a syntax error. Caution.
PAGE 301
NonStop SQL Version Issues Migrating a C10 Program to Access Version 2 Objects To use a C10 SQLDA structure, specify the RELEASE1 option either in the INCLUDE SQLDA directive or in the SQL directive for the TAL compiler.
PAGE 302
NonStop SQL Version Issues Static SQL Operations If the dynamic SQL program is designed to handle null values, the program must allocate indicator variables and initialize the IND^PTR field in addition to the VAR^PTR field. To migrate a C10 program to access columns defined to allow null values or use Version 2 data types, you must TAL compile and SQL compile the program with release C30 compilers.
PAGE 303
NonStop SQL Version Issues SQL Component Compatibility 1. Alter all source program modules affected by incompatible C30 features. 2. TAL compile the programs that contain embedded SQL statements. 3. Bind object files if this step applies. Binding multiple C10 and C30 object files is discussed later in this appendix. 4. Optionally, run the Accelerator on the object file generated from Step 2 or 3 if you plan to run the object file on a TNS/R system. 5.
PAGE 304
NonStop SQL Version Issues Developing C10 Programs with C30 Software Note. For a node running the release C10 NonStop Kernel operating system with NonStop SQL release C10, the above PVUs must be explicitly installed. For a node running the release C20 NonStop Kernel operating system with NonStop SQL release C10, these PVUs are included with the C20 Guardian software, except for the BACKUP/RESTORE PVU, which does not apply.
PAGE 305
NonStop SQL Version Issues Mixed-Version Programmatic Features Mixed-Version Programmatic Features The NonStop SQL programming interface provides several features to assist you in developing programs to handle mixed versions. These features include release specification options and procedures that report the version of various SQL objects. Release Specification Options Release specification options are available in the SQL directive.
PAGE 306
NonStop SQL Version Issues Techniques for Mixed-Version Programming (T9193C10^15MAR89, T9193AAF) component SOFTDOC for the C10 versioning PVUs. For C30 programs, external declarations for these procedures are included in the EXTDECS system file for TAL programs. Techniques for Mixed-Version Programming A generic release program can run on multiple release levels of NonStop SQL and handle Version 1 and Version 2 catalogs and objects.
PAGE 307
NonStop SQL Version Issues ° ° Running on Multiple NonStop SQL Releases Create partitions for Version 2 tables or indexes Add a column that has a Version 2 data type For the last operation, you would have to issue an error if the table intended to get the new column was registered in a Version 1 catalog. For all of the above operations, the system returns an error if a program attempts to register a Version 2 object in a Version 1 catalog.
PAGE 308
NonStop SQL Version Issues Using the Single-Code Thread Design Using the Single-Code Thread Design To develop a program that can run on a node running NonStop SQL release C10 and a node running NonStop SQL release C30 software and use Version 2 features, follow these steps: 1. In static SQL statements, do not use Version 2 features and objects. Static SQL statements will be processed by both the release C10 and C30 SQL compilers. 2. Use Version 2 features and objects only in dynamic SQL statements.
PAGE 309
NonStop SQL Version Issues Using the Single-Code Thread Design Figure D-1. Developing a Program For Mixed-Version Nodes Node \DEVEL – Development Node Release C30 NonStop SQL System Release C30 TAL Compiler (RELEASE1 Option) Node \PRODR1 – Production Node 1 Release C10 NonStop SQL System Release C10 NonStop SQL Compiler Node \PRODR2 – Production Node 2 Release C30 NonStop SQL System Release C30 NonStop SQL Compiler VSTD01.
PAGE 310
NonStop SQL Version Issues Using the Single-Code Thread Design HP NonStop SQL Programming Manual for TAL—527887-001 D-16
PAGE 311
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.
PAGE 312
Enforcing Data Integrity Managing Referential Integrity table. You should ensure that these files are explicitly SQL compiled to avoid automatic recompilation every time a program runs. Note. When you add a constraint, NonStop SQL checks all rows in the table. For large tables, the CREATE CONSTRAINT operation might run for a long time. Constraints are also a replacement for program code; they operate for all programs that refer to a table to which constraints apply.
PAGE 313
Enforcing Data Integrity Managing Referential Integrity Referential Integrity: Example 1 In this example, every department must report to another valid department in the table. To verify this rule, you can use this SELECT statement to check the DEPT table: SELECT DEPTNUM, RPTDEPT FROM DEPT WHERE RPTDEPT NOT IN (SELECT DEPTNUM FROM DEPT) ; The query returns this result: DEPTNUM RPTDEPT ------- --------- 0 row(s) selected. The absence of selected rows indicates the integrity of the database is intact.
PAGE 314
Enforcing Data Integrity Managing Referential Integrity The query returns this result: CLASSNUM CLASS_COURSE -------- -------------- 0 row(s) selected. The result of zero rows selected indicates that each class points to a valid course. The result does not prove that the class points to the correct course.
PAGE 315
Index A Accelerator effect on SQL validity 5-22 running on object file 1-7, 5-5, D-9 using SQL object file for 5-12 Access path EXPLAIN utility 5-18 local autonomy 5-28 RECOMPILE option 5-9 SQL compiler function 5-5 unavailable 5-26 using EXPLAIN with 5-17 valid programs 5-22 Access privileges for SQL compiler 5-10 ADD Command, Binder 5-5 ADD DEFINE command, TACL 5-19 Aggregate functions for C10 program migration D-5 using in a program 6-7 ALLOCATESEGMENT system procedure 3-41 Allocating memory for indicato
PAGE 316
Index C Catalog, SQL access for program file execution 5-7 handling mixed versions D-12 version 1 D-2 version 2 D-2, D-8 with SQLGETCATALOGVERSION 4-19 Circumflex (^) in host variable names 2-2 CLOSE statement, SQL 3-7, 3-8, 3-34 CLOSE system procedure 1-3 COBOL85 programming language 1-1 Colon (:) with host variable 1-3 Colon(:) with host variable 2-6 COLUMNS catalog tables version 2 features D-8 Column, SQL definition for versions D-4 for C10 program migration D-4 Comments, in SQL statements 3-2 COMMIT
PAGE 317
Index D D Data advantages of NonStop SQL 1-1 conversion between SQL and TAL 2-4 enforcing integrity E-1 using INSERT statement with 3-19 using SELECT statement with 3-31 Data Control Language (DCL) SQL statements 1-2 Data declarations BEGIN DECLARE SECTION directive, SQL 3-6 END DECLARE SECTION directive, SQL 3-6 tables and views 3-22 Data Definition Language (DDL) SQL statements 1-2 Data Manipulation Language (DML) SQL statements 1-2 Data structures, SQL placing in memory 3-37 Data types conversion betwe
PAGE 318
Index E DEFINEs, TACL (continued) set by RECOMPILE option 5-9 using for SQL compilation 5-8 using to maximize local autonomy 5-28 using with INSERT statement 3-20 using with SQL compiler 5-32 using with TAL compiler 5-31 DEFINE, TAL declaration 3-2 DEFMODE option, TACL 5-32 Delete operation multiple rows 3-14 set of rows 3-15 single row 3-14 DELETE statement, SQL 3-13 DESCRIBE INPUT statement, SQL 3-16 DESCRIBE statement, SQL 3-16 DETAIL option, FILEINFO command 5-22 Directives BEGIN DECLARE SECTION 3-6 E
PAGE 319
Index F Errors and error messages (continued) for UPDATE statement 3-42 run-time SQL recompilation 5-28 SQL compiler 5-14 Example programs static program B-10 static SQL program B-1 Exclamation point (!) for TAL comments 3-2 EXEC SQL keywords 1-2, 3-1 EXECUTE IMMEDIATE statement, SQL 1-5, 3-18 EXECUTE statement, SQL 1-5, 3-18, 7-13 Executing a TAL program 1-7 Execution plan EXPLAIN report 5-18 optimized by SQL compiler 5-11 optimized by statistics 5-11 SQL compiler function 5-11 EXPLAIN option, SQL compil
PAGE 320
Index H Guardian 90 operating system 4-2, 4-7, 4-8, 4-14 H HEADING attribute 4-20 HELP TEXT attribute 4-20 Host variable array as 2-11 associated indicator variable for 2-13 creating with INVOKE 2-20 date-time data types as 2-15 declaration of 1-3, 3-6 declaring 2-2 fields in a structure as 2-8 in expressions 2-2 INDICATOR clause 2-7 initializing in cursor 3-35 INTERVAL data types as 2-15 multiple DECLARE sections for 2-2 naming conventions 2-2 null values in 2-7 pointer as 2-9 scale in 2-12 string param
PAGE 321
Index J INTERVAL data types conversion of 2-4 inserting 2-16, 3-26 literals for 6-19 selecting 3-25 with INVOKE directive 3-24 INTO clause with SELECT command 3-32 INVALIDATE option, CREATE INDEX statement 5-23 INVOKE directive, SQL 2-8, 2-17, 2-20, 3-22, A-1 Item codes for SQLCAGETINFOLIST (table) 4-10 SQLCAGETINFOLIST parameter 4-8 Local autonomy maximizing for distributed database 5-28 program execution 5-24 program file validity 5-23 skipping unavailable partitions to maximize 5-28 using current stat
PAGE 322
Index N Names buffer (continued) declaring for dynamic SQL 7-8 estimating size of 7-15 for dynamic SQL 7-23 using with a parameter 2-25 NAMES INTO clause of DESCRIBE INPUT statement 7-23 NEWPROCESS system procedure 5-30 NO AUDITCOMPRESS attribute 4-20 NO INVALIDATE option, CREATE INDEX statement 5-23 NOEXPLAIN option, SQL compiler 5-9 NOFORCE option, SQL compiler 5-8 NonStop SQL database overview 1-1 sample of A-1 using embedded SQL for 1-1 NonStop SQL directives BEGIN DECLARE SECTION 1-3, 2-2 coding of 3
PAGE 323
Index O NOT NULL clause and version incompatibility D-4 for C10 program migration D-4 NOWHENEVERLIST option, SQL directive 3-36 NULL keyword with INSERT statement 2-18, 3-47 with UPDATE statement 3-47 Null keyword with INVOKE directive 2-17 NULL STRUCTURE clause with INVOKE directive 3-27 Null values handling 7-36 handling for dynamic SQL 7-13 in host variable 2-17 in input parameters 7-36 in names buffer 7-38 in output variables 7-38 version incompatibility D-3 with a parameter 2-26 with INSERT statement
PAGE 324
Index P P PAGES option, SQL directive 3-36 Pages, allocation of 5-34 PARAM command, TACL for SQL program file 5-30 with SQL compiler 5-13 Parameters, TACL in RUN command for SQL object file 5-30 Parameter, SQL dynamic SQL default data types 2-26 in dynamic SQL operations 2-23 indicator 2-25 syntax for 2-23 unnamed 2-23 using a list 2-24 using in a loop 2-25 Partition, local using to maximize local autonomy 5-28 Pascal programming language 1-1 Pathway, dynamic SQL server 7-3 Performance, NonStop SQL 5-24,
PAGE 325
Index S Relative table insert into 3-20 RELEASE 2 option in INCLUDE SQLDA directive D-11 RELEASE statement, SQL 3-31 RELEASE1 option in INCLUDE SQLDA directive D-11 in SQL directive 3-36, D-11, D-14 RELEASE2 option in SQL directive 3-36, D-11, D-14 Renaming program file effect on SQL validity 5-23 REPLY system procedure 7-3 Requester, SCREEN COBOL 7-3 Reserved words, SQL for C10 program migration D-5 RESTORE/BACKUP version issues D-10 Retrieving data multiple rows 3-34 single row 3-33 with cursor 3-35 RET
PAGE 326
Index S SORTPROG process 4-2, 4-8, 4-14 SORT_DEFAULTS DEFINE See=_SORT_DEFAULTS DEFINE Source file, TAL compiler 5-2 SQL compilation automatic recompilation 5-24 explicit 1-7 functions of SQL compiler 5-11 insufficient information for 5-15 interpreting error messages 5-14 listing 5-15 of dynamic SQL statements 5-14 SQL compiler 1-7 unresolved TACL DEFINEs for 5-15 using a PARAM command 5-13 using DEFINEs with 5-32 using EXPLAIN utility with 5-17 warning messages for 5-14 with SQLCOMP command 5-6 SQL direc
PAGE 327
Index S SQLMSG file (continued) file number 4-15 with SQLCADISPLAY procedure 4-15 with SQLCATOBUFFER procedure 4-15 SQLOVARS data structure 3-37 SQLSA data structure description of 6-1 with PREPARE statement 3-31 STACK option, SQLMEM compiler directive 3-3 STACK option, SQLMEM directive 3-39 Statements, NonStop SQL See also NonStop SQL statements table of 1-2 Static SQL operations description 1-4 version issues D-8 Static SQL statements, using 1-4 Statistics displaying with SQLCATOBUFFER 4-13 for SQL comp
PAGE 328
Index T T Table, SQL changes and program file validity 5-23 maximizing local autonomy for partitions 5-28, 5-30 open time and automatic SQL recompilation 5-25 using SELECT statement 3-32 version 2 D-2 TACL ASSIGN command for SQL program file 5-30 DEFINEs ADD DEFINE command 5-19 CLASS CATALOG 5-7 DEFMODE option 5-19, 5-32 for automatic SQL recompilation 5-25 for SQL program execution 5-32 for SQL program file 5-30 propagating 5-32 SET DEFINE command 5-19 using to maximize local autonomy 5-29 using with SQL
PAGE 329
Index U U Uncompiled SQL statements, FORCE option 5-14 Underscore (_) in host variable names 2-2 UNTIL TAL keyword in SQL statements 1-2, 3-1 Update operation multiple rows 3-14 set of rows 3-14 single row 3-14 using indicator parameter in 2-23 using null values in 2-17 with null values 3-47 UPDATE statement, SQL 3-42 Update statistics effect on SQL validity 5-23 optimized execution plan 5-14 UPDATE STATISTICS statement, SQL 5-11, 5-14, 5-23 UPDATE WHERE CURRENT clause for a cursor 7-32 USAGES table 5-11
PAGE 330
Index Z WRITEREAD system procedure 1-3 Z ZZBInnnn object file 5-4 Special Characters ! (exclamation point) for TAL comment 3-2 " (double quote) in SQL statement 3-2 -- double hyphen in SQL comment 3-2 : (colon) with host variable 1-3 ; (semicolon) in SQL statement 3-1 =_DEFAULTS DEFINE, TACL propagating 5-32 used by EXPLAIN utility 5-18 =_SORT_DEFAULTS DEFINE 5-32 ? (question mark) as unnamed parameter 2-23 ’ (single quote) in SQL statement 3-2 HP NonStop SQL Programming Manual for TAL—527887-001 Index