ISO-ANSI Working Draft SQL Host Language Bindings (SQL/Bindings) «Part 5» March 1994  (ISO-ANSI Working Draft) SQL Part 5: SQL/Bindings- March 12, 1994 Digital Equipment Corporation Maynard, Massachusetts  Contents Page Foreword.........................................................vii Introduction.....................................................ix 1 Scope ........................................................ 1 2 Normative references ......................................... 3 3 Definitions, notations, and conventions ...................... 5 3.1 Definitions ................................................ 5 3.2 Notation ................................................... 5 3.3 Conventions ................................................ 5 3.3.1Use of terms ............................................... 5 3.3.1Other terms ................................................ 5 3.4 Object identifier for Database Language SQL ................ 6 4 Concepts ..................................................... 7 4.1 Introduction ............................................... 7 4.1.1SQL-environments ........................................... 7 4.1.2Modules and <routine>s ..................................... 7 4.1.3SQL-statements ............................................. 7 4.2 SQL-schemas ................................................ 8 4.3 Catalogs ................................................... 8 4.4 Modules .................................................... 8 4.5 Cursors .................................................... 8 4.6 SQL-statements ............................................. 9 4.6.1Classes of SQL-statements .................................. 9 4.6.2SQL-statements classified by function ......................10 4.6.3SQL-statements and transaction states ......................11 4.6.4Embeddable SQL-statements ..................................12 4.6.5Preparable and immediately executable SQL-statements .......14 4.6.6Directly executable SQL-statements .........................15 4.7 Standard programming languages .............................16 4.8 Embedded syntax ............................................17 4.9 SQL dynamic statements .....................................18 4.10 Direct invocation of SQL ...................................21 4.11 Privileges and roles .......................................21 4.12 SQL-transactions ...........................................21 4.13 SQL-connections ............................................21 4.14 SQL-sessions ...............................................22 4.15 Client-server operation ....................................23 4.16 Leveling ...................................................23 ii (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 5 Lexical elements .............................................25 5.1 <SQL terminal character> ...................................25 5.2 <token> and <separator> ....................................27 5.3 <literal> ..................................................28 5.4 Names and identifiers ......................................30 5.5 <value specification> and <target specification> ...........33 5.6 <item reference> ...........................................35 5.7 <table reference> ..........................................37 5.8 <column reference> and <row reference> .....................38 5.9 <interval value expression> ................................39 6 Query expressions ............................................41 6.1 <query specification> ......................................41 7 Data assignment rules and function determination .............43 7.1 Retrieval assignment .......................................43 8 Schema definition and manipulation ...........................45 8.1 <view definition> ..........................................45 8.2 <assertion definition> .....................................46 8.3 <trigger definition> .......................................47 9 Standard type templates and types ............................50 9.0.1SQL_Table type template ....................................50 9.0.2SQL_List type template .....................................52 10 Module .......................................................53 10.1 <module> ...................................................53 10.2 <routine> ..................................................55 10.3 <SQL procedure statement> ..................................57 10.4 Rules for externally-invoked <routine>s ....................59 11 Data manipulation ............................................61 11.1 <fetch statement> ..........................................61 12 Session management ...........................................63 12.1 <set catalog statement> ....................................63 12.2 <set schema statement> .....................................65 12.3 <set names statement> ......................................67 12.4 <set path statement> .......................................69 13 Dynamic SQL ..................................................71 13.1 Description of SQL item descriptor areas ...................71 13.2 <allocate descriptor statement> ............................80 13.3 <deallocate descriptor statement> ..........................82 13.4 <get descriptor statement> .................................83 13.5 <set descriptor statement> .................................87 Table of Contents iii  SOU-007 and X3H2-93-083 13.6 <prepare statement> ........................................91 13.7 <deallocate prepared statement> ............................100 13.8 <describe statement> .......................................102 13.9 <using clause> .............................................104 13.10<execute statement> ........................................124 13.11<execute immediate statement> ..............................127 13.12<dynamic declare cursor> ...................................129 13.13<allocate cursor statement> ................................131 13.14<dynamic open statement> ...................................133 13.15<dynamic fetch statement> ..................................135 13.16<dynamic close statement> ..................................137 13.17<dynamic delete statement: positioned> .....................138 13.18<dynamic update statement: positioned> .....................140 13.19<preparable dynamic delete statement: positioned> ..........142 13.20<preparable dynamic update statement: positioned> ..........144 14 Embedded SQL .................................................147 14.1 <embedded SQL host program> ................................147 14.2 <embedded exception declaration> ...........................157 14.3 <embedded SQL Ada program> .................................163 14.4 <embedded SQL C program> ...................................167 14.5 <embedded SQL COBOL program> ...............................172 14.6 <embedded SQL Fortran program> .............................176 14.7 <embedded SQL MUMPS program> ...............................179 14.8 <embedded SQL Pascal program> ..............................182 14.9 <embedded SQL PL/I program> ................................186 15 Direct invocation of SQL .....................................191 15.1 <direct SQL statement> .....................................191 15.2 <direct select statement: multiple rows> ...................196 16 Diagnostics management .......................................199 16.1 <get diagnostics statement> ................................199 17 Information Schema and Definition Schema .....................203 17.1 Definition Schema ..........................................203 17.1.SQL_LANGUAGES base table ...................................203 18 Status codes .................................................207 18.1 SQLSTATE ...................................................207 19 Conformance ..................................................209 19.1 Claims of conformance ......................................209 19.2 Processing methods .........................................210 Annex A Leveling the SQL Language..............................211 A.1 Full SQL Specifications ....................................211 iv (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 A.2 Intermediate SQL Specifications ............................212 A.3 Entry SQL Specifications ...................................215 Annex B Implementation-defined elements........................219 Annex C Implementation-dependent elements......................223 Annex D Deprecated features....................................225 Annex E Incompatibilities with X3.135-1992 and ISO/IEC 9075:1992..............................................227 Index Table of Contents v  SOU-007 and X3H2-93-083 TABLES Table Page 1 Data types of <key word>s used in SQL item descriptor areas ............................................................73 2 Codes used for SQL data types in Dynamic SQL ...............77 3 Codes associated with datetime data types in Dynamic SQL ...78 4 Codes used for <interval qualifier>s in Dynamic SQL ........78 5 Additional <identifier>s for use with <get diagnostics statement>..................................................199 6 Additional SQL-statement character codes for use in the diagnostics area............................................200 7 Additional SQLSTATE class and subclass values ..............207 vi (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 Foreword ___________________________________________________________________ ISO Only-SQL3 ___________________________________________________________________ ISO (the International Organization for Standardization) and IEC (the International Electrotechnical Commission) form the specialized system for worldwide standardization. National bodies that are members of ISO or IEC participate in the development of International Standards through technical committees established by the respective organization to deal with particular fields of technical activity. ISO and IEC technical committees collaborate in fields of mutual interest. Other international organizations, governmental and non-governmental, in liaison with ISO and IEC, also take part in the work. In the field of information technology, ISO and IEC have established a joint technical committee, ISO/IEC JTC 1. Draft International Standards adopted by the joint technical committee are circulated to national bodies for voting. Publication as an International Standard requires approval by at least 75% of the national bodies casting a vote. International Standard ISO/IEC 9075, Part Y, was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology. ISO/IEC 9075 consists of the following parts, under the general title Information technology - Database languages - SQL: - Part 1: Fundamentals and Basic Language - Part X: Call Level Interface - Part Y: Persistent SQL Modules - Part Z: Host Language Bindings Annexes A, B, C, D and E of this Part of this International Standard are for information only. ___________________________________________________________________ ANSI Only-SQL3 ___________________________________________________________________ <To be supplied.> ___________________________________________________________________ Foreword vii  SOU-007 and X3H2-93-083 Introduction The organization of this <ANSI> American <ISO > International Standard is as follows: 1) Clause 1, "Scope", specifies the scope of this part of this <ANSI> American <ISO > International Standard. 2) Clause 2, "Normative references", identifies additional standards that, through reference in this part of this <ANSI> American <ISO > International Standard, constitute provisions of this part of this <ANSI> American <ISO > International Standard. 3) Clause 3, "Definitions, notations, and conventions", defines the notations and conventions used in this part of this <ANSI> American <ISO > International Standard. 4) Clause 4, "Concepts", presents concepts used in the definition of Persistent SQL modules. 5) Clause 5, "Lexical elements", defines the lexical elements of the language. 6) Clause 6, "Query expressions", defines the elements of the language that produce rows and tables of data. 7) Clause 7, "Data assignment rules and function determination", specifies the rules for assignments that retrieve data from or store data into the database, and formation rules for set operations. 8) Clause 8, "Schema definition and manipulation", defines facilities for creating and managing a schema. 9) Clause 10, "Module", defines modules and procedures. 10)Clause 11, "Data manipulation", defines the data manipulation statements. Introduction ix  SOU-007 and X3H2-93-083 11)Clause 12, "Session management", defines the SQL-session management statements. 12)Clause 13, "Dynamic SQL", defines the SQL dynamic statements. 13)Clause 14, "Embedded SQL", defines the host language embeddings. 14)Clause 15, "Direct invocation of SQL", defines direct invocation of SQL language. 15)Clause 16, "Diagnostics management", defines the diagnostics management facilities. 16)Clause 17, "Information Schema and Definition Schema", defines viewed tables that contain schema information. 17)Clause 18, "Status codes", defines values that identify the status of the execution of SQL-statements and the mechanisms by which those values are returned. 18)Clause 19, "Conformance", defines the criteria for conformance to this Part of this International Standard. 19)Annex A, "Leveling the SQL Language", is an informative Annex. It lists the leveling rules defining the Entry SQL, Intermediate SQL, and Full SQL subset SQL levels of that subset of the SQL language defined by this Part. 20)Annex B, "Implementation-defined elements", is an informative Annex. It lists those features for which the body of this part of the standard states that the syntax or meaning or effect on the database is partly or wholly implementation-defined, and describes the defining information that an implementor shall provide in each case. 21)Annex C, "Implementation-dependent elements", is an informative Annex. It lists those features for which the body of this part of the standard states that the syntax or meaning or effect on the database is partly or wholly implementation-dependent. 22)Annex D, "Deprecated features", is an informative Annex. It lists features that the responsible Technical Committee intends will not appear in a future revised version of this <ANSI> American <ISO > International Standard. 23)Annex E, "Incompatibilities with X3.135-1992 and ISO /IEC 9075:1992", is an informative Annex. It It lists the incompatibilities between this version of this <ANSI> American <ISO > International Standard and <ANSI> ANSI X3.135-1992. x (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 <ISO > ISO/IEC 9075:1992. In the text of this <ANSI> American <ISO > International Standard, Clauses begin a new odd-numbered page, and in Clause 5, "Lexical elements", through Clause 19, "Conformance", Subclauses begin a new page. Any resulting blank space is not significant. Introduction xi  SOU-007 and X3H2-93-083 Information Technology - Database Languages - SQL Part Z - SQL Host Language Bindings (SQL/Bindings) 1 Scope This <ANSI> American <ISO > International Standard specifies syntax for embedding SQL-statements in a compilation unit that otherwise conforms to the standard for a particular programming language. It defines how an equivalent compilation unit may be derived that conforms to the particular programming language standard. In that equivalent compilation unit, each embedded SQL-statement has been replaced by statements that invoke a database language procedure that contains the SQL- statement. This <ANSI> American <ISO > International Standard specifies syntax for direct invocation of SQL-statements. This <ANSI> American <ISO > International Standard specifies the syntax and semantics for embedding database language procedures into a standard programming language. This <ANSI> American <ISO > International Standard does not define the method or time of binding between compilation units, including those containing embedded SQL. <More to be supplied as required> Scope 1  SOU-007 and X3H2-93-083 2 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 2 Normative references The following standards contain provisions that, through reference in this text, constitute provisions of this <ANSI> American <ISO > National Standard. At the time of publication, the editions indicated were valid. All standards are subject to revision, and parties to agreements based on this <ANSI> American <ISO > National Standard are encouraged to investigate the possibility of applying the most recent editions of the standards listed below. Members of IEC and ISO maintain registers of currently valid International Standards. ___________________________________________________________________ ANSI Only-SQL3 ___________________________________________________________________ - ANSI X3.9-1978, American National Standard Programming Language FORTRAN. - ANSI X3.23-1985, American National Standard for Information Systems-Programming Language-COBOL. - ANSI X3.53-1976, American National Standard Programming Language PL/I. - ANSI X3.135-1992, American National Standard for Information Systems - Database Language - SQL. - ANSI X3.159-1989, American National Standard for Information Systems-Programming Language-C. - ANSI X3.198-1991, American National Standard for Information Systems-Programming Language-Fortran. Note: ANSI X3.198-1991 introduces no incompatibilities with ANSI X3.9-1978 that affect the binding between Fortran and SQL; therefore, wherever "Fortran" is specified in this <ANSI> American <ISO > International Standard, ANSI X3.198-1991 is implicit. - ANSI/MDC X11.1-1990, American National Standard for Information Systems-Programming Language-MUMPS. Normative references 3  SOU-007 and X3H2-93-083 - ANSI/IEEE 770/X3.97-1983, American National Standard for Information Systems-Programming Language-Pascal. - ANSI/IEEE 770/X3.160-1989, American National Standard for Information Systems-Programming Language-Extended Pascal. - ANSI/MIL-STD-1815A-1983, American National Standard for Information Systems-Reference Manual for the Ada® Programming Language. ________________________________________________________________ ISO Only-SQL3 ________________________________________________________________ - ISO/IEC 1539:1991, Information technology-Programming languages- Fortran. - ISO 1989:1985, Programming languages-COBOL. (Endorsement of ANSI X3.23-1985). - ISO 6160:1979, Programming languages-PL/I (Endorsement of ANSI X3.53-1976). - ISO 7185:1990, Information technology-Programming languages- Pascal. - ISO 8652:1987, Programming languages-Ada. (Endorsement of ANSI/MIL-STD-1815A-1983). - ISO/IEC 9075:1992, Information Technology - Database Languages - SQL. - ISO/IEC 9899:1990, Programming languages - C. - ISO/IEC 10206:1991, Information technology-Programming languages-Extended Pascal. - ISO/IEC 11756:1992, Information technology-Programming languages-MUMPS. ________________________________________________________________ <More to be supplied as required> 4 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 3 Definitions, notations, and conventions 3.1 Definitions For the purposes of this <ANSI> American <ISO > International Standard, the following definitions apply. 3.2 Notation <To be supplied.> 3.3 Conventions All notations in Part 1 of this <ANSI> American <ISO > International Standard apply to this Part. The syntax notation used in this part of this International Standard is an extended version of BNF ("Backus Normal Form" or "Backus Naur Form"). This version of BNF is fully described in Part 1 of this <ANSI> American <ISO > International Standard. <More to be supplied as required.> 3.3.1 Use of terms 3.3.1.1 Other terms An SQL-statement S1 is said to be executed as a direct result of executing an SQL-statement if S1 is the value of an <SQL statement variable> referenced by an <execute immediate statement> contained in a <routine> that has been executed, or if S1 was the value of the <SQL statement variable> that was associated with an <SQL statement name> by a <prepare statement> and that same <SQL statement name> is referenced by an <execute statement> contained in a <routine> that has been executed. <More to be supplied as required.> Definitions, notations, and conventions 5  SOU-007 and X3H2-93-083 3.4 Object identifier for Database Language SQL 3.4 Object identifier for Database Language SQL Note: It is possible that the object identifier for SQL may have to be adjusted to account for the new structure of SQL3. 6 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 4 Concepts Many of the concepts specified in <ANSI> ANSI X3.135-1992 <ISO > ISO/IEC 9075:1992 apply to this <ANSI> American <ISO > International Standard as well. The reader must be thoroughly familiar with that <ANSI> American <ISO > International Standard before attempting to understand this document. 4.1 Introduction 4.1.1 SQL-environments An SQL-session (Subclause 4.40, "SQL-sessions", and Subclause 4.14, "SQL-sessions") also spans the execution of consecutive SQL- statements invoked by the direct invocation of SQL. 4.1.2 Modules and <routine>s Modules may be derived implicitly from <embedded SQL statement>s within <embedded SQL host program>s involving standard programming languages (see Subclause 4.8, "Embedded syntax", in this document, and Subclause 4.33, "Standard programming languages", in Part 1 of this <ANSI> American <ISO > International standard). 4.1.3 SQL-statements The classification of SQL-statements in Subclause 4.1.6, "SQL- statements", in Part 1 of this <ANSI> American <ISO > International Standard is augmented by: - SQL-dynamic statements, which support the preparation and execution of dynamically generated SQL-statements; and - the SQL embedded exception declaration. <More to be supplied as required.> Concepts 7  SOU-007 and X3H2-93-083 4.2 SQL-schemas 4.2 SQL-schemas The default schema for <preparable statement>s that are dynamically prepared in the current SQL-session through the execution of <prepare statement>s and <execute immediate statement>s is initially implementation-defined but may be changed by the use of <set schema statement>s. 4.3 Catalogs The default catalog for <preparable statement>s that are dynamically prepared in the current SQL-session through the execution of <prepare statement>s and <execute immediate statement>s is initially implementation-defined but may be changed by the use of <set catalog statement>s. 4.4 Modules ___________________________________________________________________ ANSI Only-SQL3 ___________________________________________________________________ SQL-session <module>s are implicitly-created <module>s for prepared SQL-statements (see Subclause 4.40, "SQL-sessions"). ___________________________________________________________________ 4.5 Cursors A cursor is also specified by a <dynamic declare cursor> or an <allocate cursor statement>. For every <dynamic declare cursor> in a <module>, a cursor is effectively created when an SQL-transaction (see Subclause 4.38, "SQL-transactions", in Part 2 of this <ANSI> American <ISO > International Standard) referencing the <module> is initiated. A cursor is also effectively created when an <allocate cursor statement> is executed within an SQL-transaction. In addition, an extended dynamic cursor is destroyed when a <deallocate prepared statement> is executed that deallocates the prepared statement on which the extended dynamic cursor is based. A cursor is also placed in the open state by a <dynamic open statement> and returned to the closed state by a <dynamic close statement>. 8 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 4.5 Cursors A <dynamic fetch statement> positions an open cursor on a specified row of the cursor's ordering and retrieves the values of the columns of that row. A <dynamic update statement: positioned> updates the current row of the cursor. A <dynamic delete statement: positioned> deletes the current row of the cursor. In the template procedure proc-VC, CR is also re-opened by applying the General Rules of Subclause 14.2, "<open statement>", using the same S that resulting from applying those General Rules in the course of executing the most recent <dynamic open statement> for CR. 4.6 SQL-statements 4.6.1 Classes of SQL-statements Most SQL-statements can be prepared for execution and executed in additional ways. These are: - in an embedded SQL host program, in which case they are prepared when the embedded SQL host program is preprocessed (see Subclause 4.8, "Embedded syntax"). - being prepared and executed by the use of SQL-dynamic statements (which are themselves executed in one of the foregoing two ways- see Subclause 4.9, "SQL dynamic statements"). - direct invocation, in which case they are effectively prepared immediately prior to execution (see Subclause 4.10, "Direct invocation of SQL"). There are at least three additional ways of classifying SQL- statements: - According to whether or not they may be embedded. - According to whether they may be dynamically prepared and executed. - According to whether or not they may be directly executed. This <ANSI> American <ISO > International Standard permits implementations to provide additional, implementation-defined, statements that may fall into any of these categories. This Subclause will not mention those statements again, as their classification is entirely implementation-defined. Concepts 9  SOU-007 and X3H2-93-083 4.6 SQL-statements 4.6.2 SQL-statements classified by function The following are other main classes of SQL-statements: - SQL-dynamic statements - SQL embedded exception declaration The following are additional SQL-data statements: - <dynamic declare cursor> - <allocate cursor statement> - <dynamic select statement> - <dynamic open statement> - <dynamic close statement> - <dynamic fetch statement> - <direct select statement: multiple rows> - <dynamic single row select statement> The following are additional SQL-data change statements: - <dynamic delete statement: positioned> - <preparable dynamic delete statement: positioned> - <dynamic update statement: positioned> - <preparable dynamic update statement: positioned> The following are additional SQL-session statements: - <set catalog statement> - <set schema statement> - <set names statement> ________________________________________________________________ ANSI Only-SQL3 ________________________________________________________________ - <set path statement> ________________________________________________________________ The following are the SQL-dynamic statements: - <execute immediate statement> - <allocate descriptor statement> 10 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 4.6 SQL-statements - <deallocate descriptor statement> - <get descriptor statement> - <set descriptor statement> - <prepare statement> - <deallocate prepared statement> - <describe input statement> - <describe output statement> - <execute statement> The following is the SQL embedded exception declaration: - <embedded exception declaration> 4.6.3 SQL-statements and transaction states Whether an <execute immediate statement> starts a transaction depends on what SQL-statement is the value of <SQL statement variable>. Whether an <execute statement> starts a transaction depends on what SQL-statement was the value of <SQL statement variable> when the prepared statement identified by <SQL statement name> was prepared. The following additional SQL-statements are transaction-initiating SQL-statements, i.e., if there is no current transaction, and a statement of this class is executed, a transaction is initiated: - <allocate cursor statement> - <dynamic select statement> - <dynamic open statement> - <dynamic close statement> - <dynamic fetch statement> - <direct select statement: multiple rows> - <dynamic single row select statement> - <dynamic delete statement: positioned> - <preparable dynamic delete statement: positioned> - <dynamic update statement: positioned> - <preparable dynamic update statement: positioned> Concepts 11  SOU-007 and X3H2-93-083 4.6 SQL-statements - The following SQL-dynamic statements: o <describe input statement> o <describe output statement> o <allocate descriptor statement> o <deallocate descriptor statement> o <get descriptor statement> o <set descriptor statement> o <prepare statement> o <deallocate prepared statement> The following additional SQL-statements are not transaction- initiating SQL-statements, i.e., if there is no current transaction, and a statement of this class is executed, no transaction is initiated. - SQL embedded exception declarations - The following SQL-data statements: o <dynamic declare cursor> o <dynamic select statement> 4.6.4 Embeddable SQL-statements The following SQL-statements are embeddable in an embedded SQL host program, and may be the <SQL procedure statement> in a <routine> in a <module>: - All SQL-schema statements - All SQL-transaction statements - All SQL-connection statements - All SQL-session statements - All SQL-control statements - All SQL-dynamic statements - All SQL-diagnostics statements - The following SQL-data statements: o <allocate cursor statement> o <open statement> 12 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 4.6 SQL-statements o <dynamic open statement> o <close statement> o <dynamic close statement> o <fetch statement> o <dynamic fetch statement> o <select statement: single row> o <insert statement> o <delete statement: searched> o <delete statement: positioned> o <dynamic delete statement: positioned> o <update statement: searched> o <update statement: positioned> o <dynamic update statement: positioned> The following SQL-statements are embeddable in an embedded SQL host program, and may occur in a <module>, though not in a <routine>: - <temporary table declaration> - <temporary view declaration> - <declare cursor> - <dynamic declare cursor> The following SQL-statements are embeddable in an embedded SQL host program, but may not occur in a <module>: - SQL embedded exception declarations Consequently, the following SQL-data statements are not embeddable in an embedded SQL host program, nor may they occur in a <module>, nor be the <SQL procedure statement> in a <routine> in a <module>: - <dynamic select statement> - <dynamic single row select statement> - <direct select statement: multiple rows> - <preparable dynamic delete statement: positioned> - <preparable dynamic update statement: positioned> Concepts 13  SOU-007 and X3H2-93-083 4.6 SQL-statements 4.6.5 Preparable and immediately executable SQL-statements The following SQL-statements are preparable: - All SQL-schema statements - All SQL-transaction statements - All SQL-session statements - All SQL-control statements - The following SQL-data statements: o <delete statement: searched> o <dynamic select statement> o <dynamic single row select statement> o <insert statement> o <update statement: searched> o <preparable dynamic delete statement: positioned> o <preparable dynamic update statement: positioned> o <preparable implementation-defined statement> Consequently, the following SQL-statements are not preparable: - All SQL-connection statements - All SQL-dynamic statements - All SQL-diagnostics statements - SQL embedded exception declarations - The following SQL-data statements: o <allocate cursor statement> o <open statement> o <dynamic open statement> o <close statement> o <dynamic close statement> o <fetch statement> o <dynamic fetch statement> o <select statement: single row> 14 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 4.6 SQL-statements o <delete statement: positioned> o <dynamic delete statement: positioned> o <update statement: positioned> o <dynamic update statement: positioned> o <direct select statement: multiple rows> o <temporary table declaration> o <temporary view declaration> o <declare cursor> o <dynamic declare cursor> Any preparable SQL-statement can be executed immediately, with the exception of: - <dynamic select statement> - <dynamic single row select statement> 4.6.6 Directly executable SQL-statements The following SQL-statements may be executed directly: - All SQL-schema statements - All SQL-transaction statements - All SQL-connection statements - All SQL-session statements - The following SQL-data statements: o <temporary table declaration> o <temporary view declaration> o <direct select statement: multiple rows> o <insert statement> o <delete statement: searched> o <update statement: searched> Consequently, the following SQL-statements may not be executed directly: - All SQL-dynamic statements - All SQL-diagnostics statements Concepts 15  SOU-007 and X3H2-93-083 4.6 SQL-statements - SQL embedded exception declarations - The following SQL-data statements: o <declare cursor> o <dynamic declare cursor> o <allocate cursor statement> o <open statement> o <dynamic open statement> o <close statement> o <dynamic close statement> o <fetch statement> o <dynamic fetch statement> o <select statement: single row> o <dynamic select statement> o <dynamic single row select statement> o <delete statement: positioned> o <dynamic delete statement: positioned> o <preparable dynamic delete statement: positioned> o <update statement: positioned> o <dynamic update statement: positioned> o <preparable dynamic update statement: positioned> 4.7 Standard programming languages This <ANSI> American <ISO > International Standard specifies a mechanism whereby SQL language may be embedded in programs that otherwise conform to any of the same specified programming language standards. Note: In this <ANSI> American <ISO > International Standard, for the purposes of interfacing with programming languages, the data types DATE, TIME, TIMESTAMP, and INTERVAL must be converted to or from character strings in those programming languages by means of a <cast specification>. It is anticipated 16 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 4.7 Standard programming languages that future evolution of programming language standards will support data types corresponding to these four SQL data types; this standard will then be amended to reflect the availability of those corresponding data types. The data type CHARACTER is also mapped to character strings in the programming languages. However, because the facilities available in the programming languages do not provide the same capabilities as those available in SQL, there must be agreement between the host program and SQL regarding the specific format of the character data being exchanged. Specific syntax for this agreement is provided in thie <ANSI> American <ISO > International standard. For standard programming languages, C, COBOL, Fortran, and Pascal, bit strings are mapped to character variables in the host language in a manner described in Subclause 14.1, "<embedded SQL host program>". For standard programming languages Ada and PL/I,_bit_string_variables_are_directly_supported.__________________ **Editor's Note** Standard programming language MUMPS is not mentioned herein. It should be resolved in some way. Do we want to define handling of _BITs_in_MUMPS?_____________________________________________________ | | |4.8 Embedded syntax | | | An <embedded SQL host program> (<embedded SQL Ada program>, <embedded SQL C program>, <embedded SQL COBOL program>, <embedded SQL Fortran program>, <embedded SQL MUMPS program>, <embedded SQL Pascal program>, or <embedded SQL PL/I program>) is a compilation unit that consists of programming language text and SQL text. The programming language text shall conform to the requirements of a specific standard programming language. The SQL text shall consist of one or more <embedded SQL statement>s and, optionally, one or more <embedded SQL declare section>s, as defined in this <ANSI> American <ISO > International Standard. This allows database applications to be expressed in a hybrid form in which SQL-statements are embedded directly in a compilation unit. Such a hybrid compilation unit is defined to be equivalent to a standard compilation unit in which the SQL- statements have been replaced by standard procedure or subroutine calls of SQL <routine>s in a separate <ANSI> SQL-client <ISO > SQL <module>, and in which each been removed and the declarations contained therein have been suitably transformed into standard host-language syntax. An implementation may reserve a portion of the name space in the <embedded SQL host program> for the names of procedures or subroutines that are generated to replace SQL-statements and for program variables and branch labels that may be generated as required to support the calling of these procedures or subroutines; Concepts 17  SOU-007 and X3H2-93-083 4.8 Embedded syntax whether this reservation is made is implementation-defined. They may similarly reserve name space for the <module name> and <procedure name>s of the generated <module> that may be associated with the resulting standard compilation unit. The portion of the name space to be so reserved, if any, is implementation-defined. 4.9 SQL dynamic statements In many cases, the SQL-statement to be executed can be coded into a <module> or into a compilation unit using the embedded syntax. In other cases, the SQL-statement is not known when the program is written and will be generated during program execution. An <execute immediate statement> can be used for a one-time preparation and execution of an SQL-statement. A <prepare statement> is used to prepare the generated SQL-statement for subsequent execution. A <deallocate prepared statement> is used to deallocate SQL- statements that have been prepared with a <prepare statement>. A description of the input parameters for a prepared statement can be obtained by execution of a <describe input statement>. A description of the resultant columns of a <dynamic select statement> or <dynamic single row select statement> can be obtained by execution of a <describe output statement>. A description of the output parameters of a statement that is neither a <dynamic select statement> nor a <dynamic single row select statement> can be obtained by execution of a <describe output statement>. For a statement other than a <dynamic select statement>, an <execute statement> is used to associate parameters with the prepared statement and execute it as though it had been coded when the program was written. For a <dynamic select statement>, the prepared <cursor specification> is associated with a cursor via a <dynamic declare cursor> or <allocate cursor statement>. The cursor can be opened and parameters can be associated with the cursor with a <dynamic open statement>. A <dynamic fetch statement> positions an open cursor on a specified row and retrieves the values of the columns of that row. A <dynamic close statement> closes a cursor that was opened with a <dynamic open statement>. A <dynamic delete statement: positioned> is used to delete rows through a dynamic cursor. A <dynamic update statement: positioned> is used to update rows through a dynamic cursor. A <preparable dynamic delete statement: positioned> is used to delete rows through a dynamic cursor when the precise format of the statement isn't known until runtime. A <preparable dynamic update statement: positioned> is used to update rows through a dynamic cursor when the precise format of the statement isn't known until runtime. The interface for input parameters and output parameters for a prepared statement and for the resulting values from a <dynamic fetch statement> or the execution of a prepared <dynamic single row select statement> can be either a list of parameters or embedded variables or an SQL descriptor area. An SQL descriptor area consists of zero or more item descriptor areas, together with a COUNT of the number of those item descriptor areas. Each 18 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 4.9 SQL dynamic statements item descriptor area consists of the fields specified in Table 1, "Data types of <key word>s used in SQL item descriptor areas", in Subclause 13.1, "Description of SQL item descriptor areas". The SQL descriptor area is allocated and maintained by the system with the following statements: <allocate descriptor statement>, <deallocate descriptor statement>, <set descriptor statement>, and <get descriptor statement>. An SQL descriptor area is identified by a <descriptor name>, which is a <simple value specification> whose value is an <identifier>. Two <descriptor name>s identify the same SQL descriptor area if their values, with leading and trailing <space>s removed, are equivalent according to the rules for <identifier> comparisons in Subclause 5.2, "<token> and <separator>". Dynamic statements can be identified by <statement name>s or by <extended statement name>s. Similarly, dynamic cursors can be identified by <cursor name>s and by <extended cursor name>s. The non-extended names are <identifier>s. The extended names are <target specification>s whose values are <identifier>s used to identify the statement or cursor. Two extended names are equivalent if their values, with leading and trailing <space>s removed, are equivalent according to the rules for <identifier> comparison in Subclause 5.2, "<token> and <separator>". An SQL descriptor area name may be defined as global or local. Similarly, an extended statement name or extended cursor name may be global or local. The scope of a global name is the SQL-session. The scope of a local name is the <module> in which it appears. A reference to an entity in which one specifies a global scope is valid only if the entity was defined as global and if the reference is from the same SQL-session in which it was defined. A reference to an entity in which one specifies a local scope is valid only if the entity was defined as local and if the reference is from the same <module> in which it was defined. (The scope of non-extended statement names and non-extended cursor names is always local.) Within an SQL-session, all global prepared statements (prepared statements with global statement names) belong to the SQL-session <module>. Within an SQL-session, each local prepared statement (prepared statements with local statement names) belongs to the <module> that contains the <prepare statement> or <execute immediate statement> with which it is prepared. Note: The SQL-session <module> is defined in Subclause 4.40, "SQL- sessions". Dynamic execution of SQL-statements can generally be accomplished in two different ways. Statements can be prepared for execution and then later executed one or more times; when the statement is no longer needed for execution, it can be released by the use of a <deallocate prepared statement>. Alternatively, a statement that is needed only once can be executed without the preparation step-it can be executed immediately (not all SQL-statements can be executed immediately). Concepts 19  SOU-007 and X3H2-93-083 4.9 SQL dynamic statements Many SQL-statements can be written to use "parameters" (which are manifested in static execution of SQL-statements as parameters in <SQL procedure statement>s contained in <routine>s in <module>s or as host variables in <SQL procedure statement>s contained in <embedded SQL host program>s). In SQL-statements that are executed dynamically, the parameters are called dynamic parameters (<dynamic parameter specification>s) and are represented in SQL language by a <question mark> (?). In many situations, an application that generates an SQL-statement for dynamic execution knows in detail the required characteristics (e.g., <data type>, <length>, <precision>, <scale>, etc.) of each of the dynamic parameters used in the statement; similarly, the application may also know in detail the characteristics of the values that will be returned by execution of the statement. However, in other cases, the application may not know this information to the required level of detail; it is possible in some cases for the application to ascertain the information from the Information Schema, but in other cases (e.g., when a returned value is derived from a computation instead of simply from a column in a table, or when dynamic parameters are supplied) this information is not generally available except in the context of preparing the statement for execution. To provide the necessary information to applications, SQL permits an application to request the database system to describe a prepared statement. The description of a statement identifies the number of dynamic parameters (describe input) and their data type information or it identifies the number of values to be returned (describe output) and their data type information. The description of a statement is placed into the SQL descriptor areas already mentioned. Many, but not all, SQL-statements can be prepared and executed dynamically. Note: The complete list of statements that may be dynamically prepared and executed is defined in Subclause 4.6.5, "Preparable and immediately executable SQL-statements". Certain "set statements" (<set catalog statement>, <set schema statement>, and <set names statement>) have no effect other than to set up default information (<catalog name>, <schema name>, and <character set>, respectively) to be applied to other SQL- statements that are prepared or executed immediately or that are invoked directly. Syntax errors and Access Rule violations caused by the preparation or immediate execution of <preparable statement>s are identified when the statement is prepared (by <prepare statement>) or when it is executed (by <execute statement> or <execute immediate statement>); executed (by <execute statement> or <execute immediate statement>); such violations are indicated by the raising of an exception condition. 20 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 4.9 SQL dynamic statements ____________________________________________________________________ **Editor's Note** The absence of Rules governing the relationship between prepared statements and SQL-sessions has been identified as a Possible _Problem._See_Possible_Problem_<167>_in_the_Editor's_Notes._________ | | |4.10 Direct invocation of SQL | | | | | Direct invocation of SQL is a mechanism for executing direct SQL- statements, known as <direct SQL statement>s. In direct invocation of SQL, the method of invoking <direct SQL statement>s, the method of raising conditions that result from the execution of <direct SQL statement>s, the method of accessing the diagnostics information that results from the execution of <direct SQL statement>s, and and the method of returning the results are implementation-defined. 4.11 Privileges and roles For direct SQL, the SQL-session <authorization identifier> is always the current <authorization identifier>. 4.12 SQL-transactions The operations comprising an SQL-transaction may also be performed by the direct invocation of SQL. It is implementation-defined whether or not the dynamic execution of an <SQL dynamic data statement> is permitted to occur within the same SQL-transaction as the dynamic execution of an SQL- schema statement. If it does occur, then the effect on any open cursor, prepared dynamic statement, or deferred constraint is implementation-defined. There may be additional implementation- defined restrictions, requirements, and conditions. If any such restrictions, requirements, or conditions are violated, then an implementation-defined exception condition or a completion condition warning with an implementation-defined subclass code is raised. Each direct invocation of SQL that executes an SQL-statement of an SQL-transaction is associated with that SQL-transaction. An SQL-transaction is initiated when no SQL-transaction is currently active by direct invocation of SQL that results in the execution of a transaction-initiating <direct SQL statement>. 4.13 SQL-connections An SQL-connection may also be terminated by the last execution of a <direct SQL statement> through the direct invocation of SQL. The mechanism and rules by which an SQL-implementation determines whether a direct invocation of SQL is the last execution of a <direct SQL statement> are implementation-defined. Concepts 21  SOU-007 and X3H2-93-083 4.14 SQL-sessions 4.14 SQL-sessions An SQL-session also spans the execution of a sequence of consecutive SQL-statements invoked by the direct invocation of SQL. An SQL-session has an SQL-session <module> that is different from any other <module> that exists simultaneously in the SQL- environment. The SQL-session <module> contains the global prepared SQL-statements that belong to the SQL-session. The SQL- session <module> contains a <module authorization clause> that specifies SCHEMA <schema name>, where the value of <schema name> is implementation-dependent. An SQL-session has a default catalog name that is used to effectively qualify unqualified <schema name>s that are contained in <preparable statement>s when those statements are prepared in the current SQL-session by either an <execute immediate statement> or a <prepare statement> or are contained in <direct SQL statement>s when those statements are invoked directly. The default catalog name is initially set to an implementation-defined value but can subsequently be changed by the successful execution of a <set catalog statement> or <set schema statement>. An SQL-session has a default unqualified schema name that is used to effectively qualify unqualified <schema qualified name>s that are contained in <preparable statement>s when those statements are prepared in the current SQL-session by either an <execute immediate statement> or a <prepare statement> or are contained in <direct SQL statement>s when those statements are invoked directly. The default unqualified schema name is initially set to an implementation- defined value but can subsequently be changed by the successful execution of a <set schema statement>. An SQL-session has a default SQL-path that is used to effectively qualify unqualified <routine name>s that are immediately contained in <routine invocation>s that are contained in <preparable statement>s when those statements are prepared in the current SQL- session by either an <execute immediate statement> or a <prepare statemen> or are contained in <direct SQL statement>s when those statements are invoked directly. The default SQL-path is initially set to an implementation-defined value, but can subsequently be changed by the successful execution of a <set path statement>. An SQL-session has a default character set name that is used to identify the character set implicit for <identifier>s and <character string literal>s that are contained in <preparable statement>s when those statements are prepared in the current SQL- session by either an <execute immediate statement> or a <prepare statement> or are contained in <direct SQL statement>s when those statements are invoked directly. The default character set name is initially set to an implementation-defined value but can subsequently be changed by the successful execution of a <set names statement>. 22 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 4.14 SQL-sessions The SQL-session context also comprises: - the SQL-session <module>, - the current default catalog name, - the current default unqualified schema name, - the current character set name substitution value, - the contents of all SQL dynamic descriptor areas, and - the current default SQL-path, 4.15 Client-server operation <direct SQL statement>s) containing an <SQL connection statement> or an <SQL diagnostics statement> are processed by the SQL-client. Other <direct SQL statement>s are processed by the SQL-server. 4.16 Leveling Entry SQL also includes embedded SQL interfaces to seven different programming languages, as well as direct execution of the data manipulation statements. Intermediate SQL also includes the major new facility for dynamic SQL. <More to be supplied as required> Concepts 23  SOU-007 and X3H2-93-083 24 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 5 Lexical elements All lexical common elements specified in Clause 5, "Lexical elements", in Part 1 of this <ANSI> American <ISO > International Standard apply to this Part. 5.1 <SQL terminal character> Function Define the terminal symbols of the SQL language and the elements of strings. Format <SQL terminal character> ::= !! All alternatives from Part 1 | <SQL embedded language character> <SQL embedded language character> ::= <left bracket> | <right bracket> Syntax Rules No additional Syntax Rules. Access Rules No additional Access Rules. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. Lexical elements 25  SOU-007 and X3H2-93-083 5.1 <SQL terminal character> 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. 26 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 5.2 <token> and <separator> 5.2 <token> and <separator> Function Format <non-reserved word> ::= !! All alternatives from Part 1 | DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE Syntax Rules No additional Syntax Rules. Access Rules No additional Access Rules. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Lexical elements 27  SOU-007 and X3H2-93-083 5.3 <literal> 5.3 <literal> Function Specify a non-null value. Format No additional Format. Syntax Rules 1) If a <character set specification> is not specified in a <character string literal>, then the set of characters contained in the <character string literal> shall be wholly contained in either <SQL language character> or the character repertoire indicated by: Case: a) One of the alternatives indicated in the corresponding Syntax Rule given in Part 1 of this <ANSI> American <ISO > International Standard, or b) If the <character string literal> is contained in a <preparable statement> that is prepared in the current SQL- session by an <execute immediate statement> or a <prepare statement> or in a <direct SQL statement> that is invoked directly, then the default character set name for the SQL- session. Access Rules No additional Access Rules. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 28 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 5.3 <literal> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. Lexical elements 29  SOU-007 and X3H2-93-083 5.4 Names and identifiers 5.4 Names and identifiers Function Specify names. Format <SQL statement name> ::= <statement name> | <extended statement name> <statement name> ::= <identifier> <extended statement name> ::= [ <scope option> ] <simple value specification> <dynamic cursor name> ::= <cursor name> | <extended cursor name> <extended cursor name> ::= [ <scope option> ] <simple value specification> <descriptor name> ::= [ <scope option> ] <simple value specification> <scope option> ::= GLOBAL | LOCAL Syntax Rules 1) If a <character set specification> is not specified in an <identifier>, then the set of characters contained in the <identifier> shall be wholly contained in either <SQL language character> or the character repertoire indicated by: Case: a) One of the alternatives given in the corresponding Syntax Rule in Part 1 of this <ANSI> American <ISO > International Standard, or b) If the <identifier> is contained in a <preparable statement> that is prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> or in a <direct SQL statement> that is invoked directly, then the default character set name for the SQL-session. 30 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 5.4 Names and identifiers 2) If a <schema qualified name> does not contain a <schema name> and the <schema qualified name> is contained in a <preparable statement> that is prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> or in a <direct SQL statement> that is invoked directly, then the default <unqualified schema name> for the SQL-session is implicit. 3) If a <schema name> does not contain a <catalog name> and the <unqualified schema name> is contained in a <preparable statement> that is prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> or in a <direct SQL statement> that is invoked directly, then the default catalog name for the SQL-session is implicit. 4) The <simple value specification> of <extended statement name> or <extended cursor name> shall not be a <literal>. 5) The data type of the <simple value specification> of <extended statement name> shall be character string with an implementation-defined character set and shall have an octet length of 128 octets or less. 6) The data type of the <simple value specification> of <extended cursor name> shall be character string with an implementation- defined character set and shall have an octet length of 128 octets or less. 7) The data type of the <simple value specification> of <descriptor name> shall be character string with an implementation-defined character set and shall have an octet length of 128 octets or less. 8) In a <descriptor name>, <extended statement name>, or <extended cursor name>, if a <scope option> is not specified, then a <scope option> of LOCAL is implicit. Access Rules No additional Access Rules. General Rules 1) The value of an <extended statement name> identifies a statement prepared by the execution of a <prepare statement>. If a <scope option> of GLOBAL is specified, then the scope of the <extended statement name> is the current SQL-session. If a <scope option> of LOCAL is specified or implicit, then the scope of the statement name is further restricted to the <module> in which the <extended statement name> appears. 2) A <dynamic cursor name> identifies a cursor in an <SQL dynamic statement>. Lexical elements 31  SOU-007 and X3H2-93-083 5.4 Names and identifiers 3) A <descriptor name> identifies a descriptor area. 4) A <statement name> identifies a statement prepared by the execution of a <prepare statement>. The scope of a <statement name> is the <module> in which it appears and the current SQL- session. 5) The value of an <extended cursor name> identifies a cursor created by the execution of an <allocate cursor statement>. If a <scope option> of GLOBAL is specified, then the scope of the <extended cursor name> is the current SQL-session. If a <scope option> of LOCAL is specified or implicit, then the scope of the cursor name is further restricted to the <module> in which the <extended cursor name> appears. 6) A <descriptor name> identifies an SQL descriptor area created by the execution of an <allocate descriptor statement>. If a <scope option> of GLOBAL is specified, then the scope of the <descriptor name> is the current SQL-session. If a <scope option> of LOCAL is specified or implicit, then the scope of the <descriptor name> is further restricted to the <module> in which the <descriptor name> appears. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall not contain any <extended statement name> or <extended cursor name>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any <SQL statement name>, <dynamic cursor name>, or <descriptor name>. 32 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 5.5 <value specification> and <target specification> 5.5 <value specification> and <target specification> Function Specify one or more values, parameters, or variables. Format <general value specification> ::= !! All alternatives from Part 1 | <dynamic parameter specification> <target specification> ::= !! All alternatives from Part 1 | <dynamic parameter specification> <dynamic parameter specification> ::= <question mark> Syntax Rules 1) Each <dynamic parameter specification> shall be contained in a <preparable statement> that is dynamically prepared in the current SQL-session through the execution of a <prepare statement> or an <execute immediate statement>. 2) An <item reference> that is a <simple value specification> or <simple target specification> shall be a host parameter reference or an <embedded variable specification>. Note: "Host parameter reference" is defined in Subclause 6.3, "<item reference>", in Part 1 of this <ANSI> American <ISO > International Standard. 3) The null class of <dynamic parameter specification> is the general null class. The null class of <embedded variable specification> is the general null class. Access Rules No additional Access Rules. General Rules 1) A <dynamic parameter specification> identifies a parameter used by a dynamically prepared statement. 2) A <simple value specification> specifies a <value specification> or <unsigned value specification> that is not null and does not have an associated <indicator variable>. Lexical elements 33  SOU-007 and X3H2-93-083 5.5 <value specification> and <target specification> Leveling Rules 1) The following restrictions apply for Full SQL: a) An <indicator variable> shall not be an <SQL variable name>. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Access Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A <general value specification> shall not be a <dynamic parameter specification>. 34 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 5.6 <item reference> 5.6 <item reference> Function Reference a parameter or an SQL variable. Format <item reference> ::= !! All the alternatives from Part 1 | <embedded variable specification> <embedded variable specification> ::= <embedded variable name> [ <indicator variable> ] <indicator variable> ::= [ INDICATOR ] <embedded variable name> Syntax Rules 1) Each <embedded variable name> shall be contained in an <embedded SQL statement>. Access Rules 1) If IR is a column reference, then the applicable privileges shall include SELECT for V if CR is contained in any of: a) a <query expression> simply contained in a a <direct select statement: multiple rows>; or b) a <sort specification list> contained in a <direct select statement: multiple rows>; or c) a <query specification> contained in a <dynamic single row select statement>. General Rules 1) If an <embedded variable specification> contains an <indicator variable> and the value of the indicator variable is negative, then the value specified by the <embedded variable specification> is a null value. Otherwise, the value specified by the <embedded variable specification> is the value identified by the <embedded variable name>. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. Lexical elements 35  SOU-007 and X3H2-93-083 5.6 <item reference> 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) In Intermediate SQL, the specific data type of <indicator parameter>s and <indicator variable>s shall be the same implementation-defined data type. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. 36 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 5.7 <table reference> 5.7 <table reference> Function Reference a table. Format No additional Format. Syntax Rules No additional Syntax Rules. Access Rules 1) If the <table reference> is contained in a <direct select statement: multiple rows> then the applicable privileges shall include SELECT for <ANSI> T or for at least one column of T. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) In Intermediate SQL, the specific data type of <indicator parameter>s and <indicator variable>s shall be the same implementation-defined data type. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. Lexical elements 37  SOU-007 and X3H2-93-083 5.8 <column reference> and <row reference> 5.8 <column reference> and <row reference> Function Reference a <ANSI> column or a row. <ISO > column. Format No additional Format. Syntax Rules No additional Syntax Rules. Access Rules 1) The applicable privileges shall include SELECT for C if CR is contained in any of: a) a <query expression> simply contained in a a <direct select statement: multiple rows>; or b) a <sort specification list> contained in a <direct select statement: multiple rows>; or c) a <query specification> contained in a <dynamic single row select statement>. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) In Intermediate SQL, the specific data type of <indicator parameter>s and <indicator variable>s shall be the same implementation-defined data type. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. 38 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 5.9 <interval value expression> 5.9 <interval value expression> Function Specify an interval value. Format No additional Format. Syntax Rules 1) An <interval primary> shall specify <interval qualifier> only if the <interval primary> specifies a <dynamic parameter specification>. Access Rules No additional Access Rules. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Access Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Access Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Access Rules. Lexical elements 39  SOU-007 and X3H2-93-083 40 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 6 Query expressions 6.1 <query specification> Function Specify a table derived from the result of a <table expression>. Format No additional Format. Syntax Rules 1) A column of TQS is also possibly nullable if it contains an <indicator variable>, Access Rules No additional Access Rules. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. Query expressions 41  SOU-007 and X3H2-93-083 42 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 7 Data assignment rules and function determination 7.1 Retrieval assignment Function Specify rules for value assignments that retrieve SQL-data. Syntax Rules No additional Syntax Rules. General Rules 1) If V is a null value and T is an <embedded variable specification>, then Case: a) If an indicator is specified for T, then that indicator is set to Case: i) If the null value is the general null value, then -1. ii) Otherwise, the negative of the position number of the null state. b) If no indicator is specified for T, then an exception condition is raised: data exception-null value, no indicator parameter. 2) If V is a null value and T is not an <embedded variable specification>, then T is set to V. Data assignment rules and function determination 43  SOU-007 and X3H2-93-083 44 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 8 Schema definition and manipulation 8.1 <view definition> Function Define a viewed table. Format No additional Format. Syntax Rules No additional Syntax Rules. Access Rules No additional Access Rules. General Rules 1) An update operation is an <insert statement>, <update statement: positioned>, <update statement: searched>, <dynamic update statement: positioned>, or <preparable dynamic update statement: positioned>. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. Schema definition and manipulation 45  SOU-007 and X3H2-93-083 8.2 <assertion definition> 8.2 <assertion definition> Function Specify an integrity constraint by means of an assertion and specify when the assertion is to be checked. Format No additional Format. Syntax Rules 1) The <search condition> shall also not contain an <embedded variable specification> or a <dynamic parameter specification>. Access Rules No additional Access Rules. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. 46 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 8.3 <trigger definition> 8.3 <trigger definition> Function Define triggered SQL-statements. Format No additional Format. Syntax Rules 1) The <search condition> and <triggered SQL statement>s shall not contain a <value specification> that specifies a <dynamic parameter specification>. ________________________________________________________________ ISO Only-caused by ANSI changes not yet considered by ISO ________________________________________________________________ 2) The <triggered SQL statement> shall not generally contain an <SQL dynamic statement>. ________________________________________________________________ Access Rules No additional Access Rules. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. Schema definition and manipulation 47  SOU-007 and X3H2-93-083 ___________________________________________________________________ ANSI Only-caused by ISO changes not yet considered by ANSI ___________________________________________________________________ 48 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 Schema definition and manipulation 49  SOU-007 and X3H2-93-083 9 Standard type templates and types 9.0.1 SQL_Table type template Function SQL_Table provides functions to carry out insertion, selection, updating, and deletion on SQL tables. Definition See note below Note: I don't know how to fix the Definition, which has a reference to :descr_name>. Description 1) The values of max_stmt_length, max_name_length, and max_pred_ length are implementation-dependent maximum lengths for an <SQL procedure statement>, a <descriptor name>, and a <predicate> respectively. count_type is the data type of the result of a <set function specification> that simply contains COUNT. Note: I don't know how to fix this Rule, which refers to a <descriptor name>. Without knowing how to deal with this, I have simply copied the entire Description to Part Z and left it intact in Part 1. 2) If S contains an <embedded variable name> then an exception condition is raised: syntax error or access rule violation in SQL_Table function. 3) If D identifies an SQL item descriptor area that is not currently allocated, then an exception condition is raised: invalid SQL descriptor name. 4) DATA items in dynamic descriptor areas are employed to pass data corresponding to <target specification>s as in Dynamic SQL. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 50 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. ___________________________________________________________________ Standard type templates and types 51  SOU-007 and X3H2-93-083 ___________________________________________________________________ ANSI Only-caused by ISO changes not yet considered by ANSI ___________________________________________________________________ 9.0.2 SQL_List type template Function SQL_List provides the ability to create and manipulate lists of elements of a specified type. The SQL_List type template inherits the functionality of SQL_Table (redefining several of the functions to take account of ordering in lists), and defines some new functions. Definition See note below Note: I don't know how to fix the Definition, which has a reference to :descr_name>. Description 1) The value of max_stmt_length is an implementation-dependent maximum length for an <SQL procedure statement>. The value of max_name_length is the maximum length of a <descriptor name>. Note: I don't know how to fix this Rule, which refers to a <descriptor name>. Without knowing how to deal with this, I have simply copied the entire Description to Part Z and left it intact in Part 1. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. ___________________________________________________________________ 52 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 10 Module 10.1 <module> Function Define a module. Format <module contents> ::= !! All alternatives from Part 1 ______________________________________________________________ ISO Only-caused by ANSI changes not yet considered by ISO ______________________________________________________________ | <dynamic declare cursor> ______________________________________________________________ ANSI Only-SQL3 ______________________________________________________________ | <global declaration> [ <semicolon> ] <global declaration> ::= !! All alternatives from Part 1 | <dynamic declare cursor> ______________________________________________________________ Syntax Rules 1) A <dynamic declare cursor> shall precede in the text of the <module> any <routine> that references the <cursor name> of the <dynamic declare cursor>. Access Rules No additional Access Rules. General Rules 1) After the last time that an SQL-agent performs a call of a <routine>, following the effective execution of a <rollback statement> or a <commit statement> and before the destruction of Module 53  SOU-007 and X3H2-93-083 10.1 <module> all module variables associated with this module, a <deallocate descriptor statement> that specifies DEALLOCATE DESCRIPTOR D is effectively executed, where D is the <descriptor name> of any system descriptor area that is currently allocated within an SQL-session associated with the SQL-agent. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A <module contents> shall not be a <dynamic declare cursor>. 54 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 10.2 <routine> 10.2 <routine> Function Define a routine. Format <routine> ::= ______________________________________________________________ ANSI Only-SQL3 ______________________________________________________________ [ CREATE | DECLARE ] <routine header> [ <routine name> ] <parameter list> ______________________________________________________________ ISO Only-caused by ANSI changes not yet considered by ISO ______________________________________________________________ [ CREATE ] <routine header> <routine name> <parameter list> ______________________________________________________________ [ <returns clause> ] [ <caller language clause> ] [ SPECIFIC <specific name> ] <semicolon> <routine body> <semicolon> Syntax Rules ___________________________________________________________________ ANSI Only-SQL3 ___________________________________________________________________ 1) If <routine> is not immediately contained in a <dynamic procedure>, then <routine name> shall be specified. 2) If <routine> is immediately contained in a <statement or declaration>, then DECLARE shall be specified. ________________________________________________________________ 3) The Ada base type of any parameter shall be an Ada data type declared in an Ada package named SQL_STANDARD as specified in Part 1 of this <ANSI> American <ISO > International Standard, with the following additional constants: DYNAMIC_SQL_ERROR_NO_SUBCLASS: constant SQLSTATE_TYPE := "07000"; DYNAMIC_SQL_ERROR_CURSOR_SPECIFICATION_CANNOT_ BE_EXECUTED: Module 55  SOU-007 and X3H2-93-083 10.2 <routine> constant SQLSTATE_TYPE := "07003"; DYNAMIC_SQL_ERROR_INVALID_DESCRIPTOR_COUNT: constant SQLSTATE_TYPE := "07008"; DYNAMIC_SQL_ERROR_INVALID_DESCRIPTOR_INDEX: constant SQLSTATE_TYPE := "07009"; DYNAMIC_SQL_ERROR_PREPARED_STATEMENT_NOT_A_ CURSOR_SPECIFICATION: constant SQLSTATE_TYPE := "07005"; DYNAMIC_SQL_ERROR_RESTRICTED_DATA_TYPE_ ATTRIBUTE_VIOLATION: constant SQLSTATE_TYPE := "07006"; DYNAMIC_SQL_ERROR_USING_CLAUSE_DOES_NOT_MATCH_ DYNAMIC_PARAMETER_SPEC: constant SQLSTATE_TYPE := "07001"; DYNAMIC_SQL_ERROR_USING_CLAUSE_DOES_NOT_MATCH_ TARGET_SPEC: constant SQLSTATE_TYPE := "07002"; DYNAMIC_SQL_ERROR_USING_CLAUSE_REQUIRED_FOR_ DYNAMIC_PARAMETERS: constant SQLSTATE_TYPE := "07004"; DYNAMIC_SQL_ERROR_USING_CLAUSE_REQUIRED_FOR_ RESULT_FIELDS: constant SQLSTATE_TYPE := "07007"; WARNING_INSUFFICIENT_ITEM_DESCRIPTOR_AREAS: constant SQLSTATE_TYPE := "01005"; Access Rules No additional Access Rules. General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. 56 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 10.3 <SQL procedure statement> 10.3 <SQL procedure statement> Function Define all of the SQL-statements that are <SQL procedure statement>s. Format <SQL executable statement> ::= !! All alternatives from Part 1 | <SQL dynamic statement> <SQL session statement> ::= !! All alternatives from Part 1 | <set catalog statement> | <set schema statement> | <set names statement> | <set path statement> <SQL dynamic statement> ::= <system descriptor statement> | <prepare statement> | <deallocate prepared statement> | <describe statement> | <execute statement> | <execute immediate statement> | <SQL dynamic data statement> <SQL dynamic data statement> ::= <allocate cursor statement> | <dynamic open statement> | <dynamic fetch statement> | <dynamic close statement> | <dynamic delete statement: positioned> | <dynamic update statement: positioned> <system descriptor statement> ::= <allocate descriptor statement> | <deallocate descriptor statement> | <set descriptor statement> | <get descriptor statement> Syntax Rules No additional Syntax Rules. Access Rules No additional Access Rules. Module 57  SOU-007 and X3H2-93-083 10.3 <SQL procedure statement> General Rules No additional General Rules. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Access Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Access Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) An <SQL procedure statement> shall not be an <SQL dynamic statement>. 58 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 10.4 Rules for externally-invoked <routine>s 10.4 Rules for externally-invoked <routine>s Function Specify General Rules for externally-invoked <routine>s. General Rules 1) Subsequent invocations of <direct SQL statement>s by the SQL- agent are associated with the SQL-session until the SQL-agent terminates the SQL-session or makes it dormant. 2) If no SQL-transaction is active for the SQL-agent and S is a transaction-initiating SQL-statement, then an SQL-transaction is effectively initiated and associated with this and subsequent invocations of <direct SQL statement>s by that SQL-agent until the SQL-agent terminates that SQL-transaction. 3) If execution of an <SQL dynamic data statement>, <dynamic select statement>, or <dynamic single row select statement> occurs within the same SQL-transaction as the non-dynamic or dynamic execution of an SQL-schema statement and this is not allowed by the SQL-implementation, then an exception condition is raised: invalid transaction state- schema and data statement mixing not supported. 4) If the cursor mode of the current SQL-transaction is cascade off and the <SQL procedure statement> is other than an <open statement>, a <fetch statement>, a <close statement>, a <select statement: single row>, an <SQL data change statement>, a <dynamic open statement>, a <dynamic fetch statement>, a <dynamic close statement>, a <dynamic delete statement: positioned>, or a <dynamic update statement: positioned>, then an exception condition is raised: invalid SQL statement. Module 59  SOU-007 and X3H2-93-083 60 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 11 Data manipulation 11.1 <fetch statement> Function Position a cursor on a specified row of a table and retrieve values from that row. Format No additional Format. Syntax Rules No additional Syntax Rules. Access Rules No additional Access Rules. General Rules 1) If the cursor mode of the current SQL-transaction is cascade off, and if the previous <SQL procedure statement> that specified the <cursor name> of CR that was executed within the current set-processing mode session of the current SQL- transaction was a <fetch statement> or a <dynamic fetch statement>, then an exception condition is raised: invalid SQL statement. Leveling Rules 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. Data manipulation 61  SOU-007 and X3H2-93-083 62 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 12 Session management 12.1 <set catalog statement> Function Set the default catalog name for unqualified <schema name>s in <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> and in <direct SQL statement>s that are invoked directly. Format <set catalog statement> ::= SET CATALOG <value specification> Syntax Rules 1) The <data type> of the <value specification> shall be an SQL character data type. Access Rules None. General Rules 1) Let S be the character string that is the value of the <value specification> and let V be the character string that is the value of TRIM ( BOTH ' ' FROM S ) 2) If V does not conform to the Format and Syntax Rules of a <catalog name>, then an exception condition is raised: invalid catalog name. 3) The default catalog name of the current SQL-session is set to V. Leveling Rules 1) The following restrictions apply for Full SQL: None. Session management 63  SOU-007 and X3H2-93-083 12.1 <set catalog statement> 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall not contain any <set catalog statement>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 64 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 12.2 <set schema statement> 12.2 <set schema statement> Function Set the default schema name for unqualified <schema qualified name>s in <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> and in <direct SQL statement>s that are invoked directly. Format <set schema statement> ::= SET SCHEMA <value specification> Syntax Rules 1) The data type of the <value specification> shall be an SQL character data type. Access Rules None. General Rules 1) Let S be the character string that is the value of the <value specification> and let V be the character string that is the value of TRIM ( BOTH ' ' FROM S ) 2) If V does not conform to the Format and Syntax Rules of a <schema name>, then an exception condition is raised: invalid schema name. 3) Case: a) If V conforms to the Format and Syntax Rules for a <schema name> that contains a <catalog name>, then let X be the <catalog name> part and let Y be the <unqualified schema name> part of V. The following statement is implicitly executed: SET CATALOG 'X' and the <set schema statement> is effectively replaced by: SET SCHEMA 'Y' b) Otherwise, the default unqualified schema name of the current SQL-session is set to V. Session management 65  SOU-007 and X3H2-93-083 12.2 <set schema statement> Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall not contain any <set schema statement>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 66 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 12.3 <set names statement> 12.3 <set names statement> Function Set the default character set name for <identifier>s and <character string literal>s in <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> and in <direct SQL statement>s that are invoked directly. Format <set names statement> ::= SET NAMES <value specification> Syntax Rules 1) The <data type> of the <value specification> shall be an SQL character data type. Access Rules None. General Rules 1) Let S be the character string that is the value of the <value specification> and let V be the character string that is the value of TRIM ( BOTH ' ' FROM S ) 2) If V does not conform to the Format and Syntax Rules of a <character set name>, then an exception condition is raised: invalid character set name. 3) The default character set name of the current SQL-session is set to V. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall not contain any <set names statement>. Session management 67  SOU-007 and X3H2-93-083 12.3 <set names statement> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 68 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 12.4 <set path statement> 12.4 <set path statement> Function Set the default SQL-path used to determine the subject routine of <routine invocation>s with unqualified <routine name>s <preparable statement>s that are prepared in the current SQL-session by an <execute immediate statement> or a <prepare statement> and in <direct SQL statement>s that are invoked directly. The default SQL-path remains the current default SQL-path of the SQL-session until another default SQL-path is successfully set. Format <set path statement> ::= SET CURRENT_PATH <schema namee list> Syntax Rules 1) Each <schema name> contained in <schema name list> shall conform to the Format and Syntax Rules of <schema name>. 2) The explicit or implicit <catalog name> of each <schema name> contained in <schema name list> shall be the same as the name of the default catalog of the current SQL-session. Let CN be that <catalog name> and let C be the catalog identified by CN. 3) Each <schema name> shall identify a schema in C and no schema shall be identified more than once. Access Rules None. General Rules 1) The default SQL-path of the current SQL-session is set to <schema name list>. Note: A <set path statement> that is executed between a <prepare statement> and an <execute statement> has no effect on the prepared statement. Leveling Rules 1) The following restrictions apply for Full SQL: a) Conforming Full SQL language shall not contain any <set path statement>. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. Session management 69  SOU-007 and X3H2-93-083 12.4 <set path statement> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 70 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13 Dynamic SQL ____________________________________________________________________ **Editor's Note** The fact that prepared statements can be shared among modules, but cursors cannot has been identified as a Possible Problem. See _Possible_Problem_<179>_in_the_Editor's_Notes.______________________ | | |13.1 Description of SQL item descriptor areas | | | Function Specify the identifiers, data types, and codes used in SQL item descriptor areas. Syntax Rules 1) An SQL item descriptor area comprises the items specified in Table 1, "Data types of <key word>s used in SQL item descriptor areas". 2) Let DT be a data type. The data type T of a <simple value specification> or a <simple target specification> SVT is said to match the data type specified by the item descriptor area if and only if one of the following conditions is true. Case: a) TYPE indicates NUMERIC and T is specified by NUMERIC(P,S), where P is the value of PRECISION and S is the value of SCALE. b) TYPE indicates DECIMAL and T is specified by DECIMAL(P,S), where P is the value of PRECISION and S is the value of SCALE. c) TYPE indicates INTEGER and T is specified by INTEGER. d) TYPE indicates SMALLINT and T is specified by SMALLINT. e) TYPE indicates FLOAT and T is specified by FLOAT(P), where P is the value of PRECISION. f) TYPE indicates REAL and T is specified by REAL. g) TYPE indicates DOUBLE PRECISION and T is specified by DOUBLE PRECISION. h) TYPE indicates BIT and T is specified by BIT(L), where L is the value of LENGTH. Dynamic SQL 71  SOU-007 and X3H2-93-083 13.1 Description of SQL item descriptor areas i) TYPE indicates BIT VARYING and T is specified by BIT VARYING(L), where Case: i) SVT is a <simple value specification> and L is the value of LENGTH. ii) SVT is a <simple target specification> and L is not less than the value of LENGTH. j) TYPE indicates CHARACTER and T is specified by CHARACTER(L), where L is the value of LENGTH and the <character set specification> formed by the values of CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME identifies the character set of SVT. k) TYPE indicates CHARACTER VARYING and T is specified by CHARACTER VARYING(L), where the <character set specification> formed by the values of CHARACTER_SET_CATALOG, CHARACTER_SET_ SCHEMA, and CHARACTER_SET_NAME identifies the character set of SVT and Case: i) SVT is a <simple value specification> and L is the value of LENGTH. ii) SVT is a <simple target specification> and L is not less than the value of LENGTH. l) TYPE indicates an implementation-defined data type and T satisfies the implementation-defined rules for matching that data type. 3) An item descriptor area is valid if and only if TYPE indicates a code defined in Table 2, "Codes used for SQL data types in Dynamic SQL", and one of the following is true: Case: a) TYPE indicates NUMERIC and PRECISION and SCALE are valid precision and scale values for the NUMERIC data type. b) TYPE indicates DECIMAL and PRECISION and SCALE are valid precision and scale values for the DECIMAL data type. c) TYPE indicates FLOAT and PRECISION is a valid precision value for the FLOAT data type. d) TYPE indicates INTEGER, SMALLINT, REAL, or DOUBLE PRECISION. e) TYPE indicates BIT or BIT VARYING and LENGTH is a valid length value for the BIT data type. 72 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.1 Description of SQL item descriptor areas f) TYPE indicates CHARACTER or CHARACTER VARYING, LENGTH is a valid length value for the CHARACTER data type, and CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_ SET_NAME are a valid qualified character set name for the CHARACTER data type. g) TYPE indicates a <datetime type>, DATETIME_INTERVAL_CODE is a code specified in Table 3, "Codes associated with datetime data types in Dynamic SQL", and PRECISION is a valid value for the <time precision> or <timestamp precision> of the indicated datetime datatype. h) TYPE indicates an <interval type>, DATETIME_INTERVAL_CODE is a code specified in Table 4, "Codes used for <interval qualifier>s in Dynamic SQL", and DATETIME_INTERVAL_PRECISION and PRECISION are valid values for <interval leading field precision> and <interval fractional seconds precision> for an <interval qualifier>. i) TYPE indicates an implementation-defined data type. Table_1-Data_types_of_<key_word>s_used_in_SQL_item_descriptor_areas _<key_word>_____________Data_Type__________________________________ |______________________|_<header_item_name>________________________| | | | | COUNT | exact numeric with scale 0 | | | | | KEY_TYPE | exact numeric with scale 0 | | | | | DYNAMIC_FUNCTION | character string with character set | SQL_TEXT and length not less than 128 characters | | | | | | | | | Dynamic SQL 73  SOU-007 and X3H2-93-083 13.1 Description of SQL item descriptor areas Table 1-Data types of <key word>s used ___________________________________________________________________ ___________________________________________________________________ | TYPE | exact numeric with scale 0 | | | | | LENGTH | exact numeric with scale 0 | | | | | OCTET_LENGTH | exact numeric with scale 0 | | | | | RETURNED_LENGTH | exact numeric with scale 0 | | | | | RETURNED_OCTET_ | exact numeric with scale 0 | LENGTH | PRECISION | exact numeric with scale 0 | | | | | SCALE | exact numeric with scale 0 | | | | | DATETIME_INTERVAL_ | exact numeric with scale 0 | CODE | | | | | | 74 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.1 Description of SQL item descriptor areas Table 1-Data types of <key word>s used ________________in_SQL_item_descriptor_areas_(Cont.)_______________ _<key_word>_____________Data_Type__________________________________ |______________________|_<descriptor_item_name>____________________| | | | | DATETIME_INTERVAL_ | exact numeric with scale 0 | PRECISION | NULLABLE | exact numeric with scale 0 | | | | | KEY_MEMBER | exact numeric with scale 0 | | | | | INDICATOR | exact numeric with scale 0 | | | | | DATA | matches data type specified by TYPE, | LENGTH, PRECISION, SCALE, DATETIME_ INTERVAL_CODE, DATETIME_INTERVAL_ PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_ NAME | NAME | character string with character set | | | SQL_TEXT and length not less than 128 | | | characters | | | | | UNNAMED | exact numeric with scale 0 | | | | | COLLATION_CATALOG | character string with character set | | | SQL_TEXT and length not less than 128 | characters | COLLATION_SCHEMA | character string with character set | | | SQL_TEXT and length not less than 128 | | | characters | | | | | COLLATION_NAME | character string with character set | | | SQL_TEXT and length not less than 128 | | | characters | | | | | CHARACTER_SET_ | character string with character set | | CATALOG | SQL_TEXT and length not less than 128 | | | characters | | | | | | | | | | | | | Dynamic SQL 75  SOU-007 and X3H2-93-083 13.1 Description of SQL item descriptor areas Table 1-Data types of <key word>s used ________________in_SQL_item_descriptor_areas_(Cont.)_______________ _<key_word>_____________Data_Type__________________________________ |______________________|_<descriptor_item_name>____________________| | | | | CHARACTER_SET_SCHEMA | character string with character set | SQL_TEXT and length not less than 128 characters | CHARACTER_SET_NAME | character string with character set | | | SQL_TEXT and length not less than 128 | | | characters | | | | | NULL_CLASS_CATALOG | character string with character set | | | SQL_TEXT and length not less than 128 | | | characters | | | | | NULL_CLASS_SCHEMA | character string with character set | | | SQL_TEXT and length not less than 128 | | | characters | | | | | NULL_CLASS_NAME | character string with character set | | | SQL_TEXT and length not less than 128 | |______________________|_characters________________________________| | | | |Access Rules | | | | | None. General Rules 1) Table 2, "Codes used for SQL data types in Dynamic SQL", specifies the codes associated with the SQL data types. 76 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.1 Description of SQL item descriptor areas ________Table_2-Codes_used_for_SQL_data_types_in_Dynamic_SQL_______ _Data_Type____________Code_________________________________________ | Implementation- | < 0 | defined data types | Abstract data | 17 | | types | | | | | | BIT | 14 | | | | | BIT VARYING | 15 | | | | | BOOLEAN | 16 | | | | | CHARACTER | 1 | | | | | CHARACTER VARYING | 12 | | | | | DATE, TIME, or | 9 | TIMESTAMP | DECIMAL | 3 | | | | | DOUBLE PRECISION | 8 | | | | | ENUMERATED | 13 | | | | | FLOAT | 6 | | | | | INTEGER | 4 | | | | | INTERVAL | 10 | | | | | NUMERIC | 2 | | | | | REAL | 7 | | | | |_SMALLINT___________|_5___________________________________________| | | | 2) Table 3, "Codes associated with datetime data types in Dynamic SQL", specifies the codes associated with the datetime data types. Dynamic SQL 77  SOU-007 and X3H2-93-083 13.1 Description of SQL item descriptor areas __Table_3-Codes_associated_with_datetime_data_types_in_Dynamic_SQL_ _Datetime_Data_Type___Code_________________________________________ | DATE | 1 | | | | | TIME | 2 | | | | | TIMESTAMP | 3 | | | | | TIME WITH TIME | 4 | ZONE | TIMESTAMP WITH | 5 | |_TIME_ZONE__________|_____________________________________________| | | | |3) Table 4, "Codes u|ed for <interval qualifier>s in Dynamic SQL",| specifies the codes associated with <interval qualifier>s for interval data types. ____Table_4-Codes_used_for_<interval_qualifier>s_in_Dynamic_SQL____ _Datetime_Qualifier___Code_________________________________________ | YEAR | 1 | | | | | MONTH | 2 | | | | | DAY | 3 | | | | | HOUR | 4 | | | | | MINUTE | 5 | | | | | SECOND | 6 | | | | | YEAR TO MONTH | 7 | | | | | DAY TO HOUR | 8 | | | | | DAY TO MINUTE | 9 | | | | | DAY TO SECOND | 10 | | | | | HOUR TO MINUTE | 11 | | | | | HOUR TO SECOND | 12 | | | | |_MINUTE_TO_SECOND___|_13__________________________________________| | | | 4) The value of DYNAMIC_FUNCTION is the identification of the a prepared statement. Table 26, "SQL-statement character codes for use in the diagnostics area", specifies the identifiers of SQL-statements. Leveling Rules 1) The following restrictions apply for Full SQL: None. 78 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.1 Description of SQL item descriptor areas 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Dynamic SQL 79  SOU-007 and X3H2-93-083 13.2 <allocate descriptor statement> 13.2 <allocate descriptor statement> Function Allocate an SQL descriptor area. Format <allocate descriptor statement> ::= ALLOCATE DESCRIPTOR <descriptor name> [ WITH MAX <occurrences> ] <occurrences> ::= <simple value specification> Syntax Rules 1) The data type of <occurrences> shall be exact numeric with scale 0. 2) If WITH MAX <occurrences> is not specified, then an implementation-defined default value for <occurrences> that is greater than 0 is implicit. Access Rules None. General Rules 1) Let S be the character string that is the value of the <simple value specification> that is immediately contained in <descriptor name> and let V be the character string that is the result of TRIM ( BOTH ' ' FROM S ) If V does not conform to the Format and Syntax Rules of an <identifier>, then an exception condition is raised: invalid SQL descriptor name. 2) The <allocate descriptor statement> allocates an SQL descriptor area whose name is V and whose scope is specified by the <scope option>. The descriptor area will have at least <occurrences> number of item descriptor areas. All values are initially undefined. If an SQL descriptor has already been allocated whose name is V, whose scope is specified by the <scope option>, and that has not yet been deallocated, then an exception condition is raised: invalid SQL descriptor name. 3) If <occurrences> is less than 1 or is greater than an implementation-defined maximum value, then an exception condition is raised: dynamic SQL error-invalid descriptor index. 80 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.2 <allocate descriptor statement> The maximum number of SQL descriptor areas that can be allocated at one time is implementation-defined. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) An <occurrences> and a <descriptor name> shall be a <literal>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 81  SOU-007 and X3H2-93-083 13.3 <deallocate descriptor statement> 13.3 <deallocate descriptor statement> Function Deallocate an SQL descriptor area. Format <deallocate descriptor statement> ::= DEALLOCATE DESCRIPTOR <descriptor name> Syntax Rules None. Access Rules None. General Rules 1) The <deallocate descriptor statement> deallocates an SQL descriptor area whose name is the value of the <descriptor name>'s <simple value specification> and whose scope is specified by the <scope option>. If an SQL descriptor is not currently allocated whose name is the value of the <descriptor name>'s <simple value specification> and whose scope is specified by the <scope option>, then an exception condition is raised: invalid SQL descriptor name. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) A <descriptor name> shall be a <literal>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 82 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.4 <get descriptor statement> 13.4 <get descriptor statement> Function Get information from an SQL descriptor area. Format <get descriptor statement> ::= GET DESCRIPTOR <descriptor name> <get descriptor information> <get descriptor information> ::= <get header information> [ { <comma> <get header information> }... ] | VALUE <item number> <get item information> [ { <comma> <get item information> }... ] <get header information> ::= <simple target specification 1> <equals operator> <header item name> <header item name> ::= COUNT | KEY_TYPE | DYNAMIC_FUNCTION <get item information> ::= <simple target specification 2> <equals operator> <descriptor item name> <item number> ::= <simple value specification> <simple target specification 1> ::= <simple target specification> <simple target specification 2> ::= <simple target specification> <descriptor item name> ::= TYPE | LENGTH | OCTET_LENGTH | RETURNED_LENGTH | RETURNED_OCTET_LENGTH | PRECISION | SCALE | DATETIME_INTERVAL_CODE | DATETIME_INTERVAL_PRECISION | NULLABLE | KEY_MEMBER | INDICATOR | DATA | NAME Dynamic SQL 83  SOU-007 and X3H2-93-083 13.4 <get descriptor statement> | UNNAMED | COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME | CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME Syntax Rules 1) The data type of <item number> shall be exact numeric with scale 0. 2) The data type of a <simple target specification 1> shall be the data type shown in Table 1, "Data types of <key word>s used in SQL item descriptor areas", for the corresponding <header item name>. 3) The data type of a <simple target specification 2> shall be the data type shown in Table 1, "Data types of <key word>s used in SQL item descriptor areas", for the corresponding <descriptor item name>. Access Rules None. General Rules 1) If a <descriptor name> specified in a <get descriptor statement> identifies an SQL descriptor area that is not currently allocated, then an exception condition is raised: invalid SQL descriptor name. 2) If the data type of the <simple target specification> associated with the keyword DATA does not match the data type specified by TYPE, LENGTH, the item descriptor area, then an exception condition is raised: data exception-error in assignment. Note: "Match" is defined in the Syntax Rules of Subclause 13.1, "Description of SQL item descriptor areas". 3) If the <item number> specified in a <get descriptor statement> is greater than the number of <occurrences> specified when the <descriptor name> was allocated or less than 1, then an exception condition is raised: dynamic SQL error-invalid descriptor index. 4) If the <item number> specified in a <get descriptor statement> is greater than the value of COUNT, then a completion condition is raised: no data. 84 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.4 <get descriptor statement> 5) If a <get descriptor statement> is executed to get the value of DATA without getting the value of INDICATOR and the value of INDICATOR is negative, then an exception condition is raised: data exception-null value, no indicator parameter. 6) If an exception condition is raised in a <get descriptor statement>, then the values of all targets specified by <simple target specification 1>, <simple target specification 2>, and <simple target specification 3> are implementation-dependent. 7) A <get descriptor statement> gets values in the SQL descriptor area specified by <descriptor name>. The values are as follows: a) COUNT is a count of the number of <dynamic parameter specification>s or <select list> columns described in item descriptor areas. b) The value of DYNAMIC_FUNCTION identifies a prepared statement. c) Case: i) If the SQL item descriptor area identified by <descriptor name> is a collection SL of <select list> columns of some table T and some subset of SL is the primary key of T, then KEY_TYPE is 1. ii) If the SQL item descriptor area identified by <descriptor name> is a collection SL of <select list> columns of some table T and some subset of SL is the preferred key of T, then KEY_TYPE is 2. Note: Primary keys and preferred keys of tables are defined in Subclause 4.21, "Functional dependencies and candidate keys". iii) Otherwise, KEY_TYPE is 0. d) The values of TYPE, LENGTH, OCTET_LENGTH, RETURNED_ LENGTH, RETURNED_OCTET_LENGTH, PRECISION, SCALE, DATETIME_ INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, COLLATION_ CATALOG, COLLATION_SCHEMA, COLLATION_NAME, CHARACTER_SET_ CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME are the data type, length (in characters, bits, or positions, as appropriate), length in octets, returned length in characters, returned length in octets, precision, scale, datetime data type, interval qualifier, collation, and character set of a <dynamic parameter specification> or <select list> column described by the item descriptor area specified by <item number>. e) NAME is the name associated with the <dynamic parameter specification> or <select list> column described by the item descriptor area specified by <item number>. For a <select list> column, if the column name is implementation-dependent, Dynamic SQL 85  SOU-007 and X3H2-93-083 13.4 <get descriptor statement> then NAME is the implementation-dependent name for the column and UNNAMED is set to 1; otherwise, NAME is the <derived column name> of the column and UNNAMED is set to 0. For a <dynamic parameter specification>, the values of NAME and UNNAMED are implementation-dependent. f) DATA is a value for the <target specification> described by the item descriptor area specified by <item number>. If the value of INDICATOR is negative, then the value of DATA is undefined. g) INDICATOR is a value for the <indicator parameter> associated with the <target specification>. h) Case: i) For a <select list> column, if NULLABLE is set to 1, then the column can have a null value; otherwise, the column cannot have a null value. ii) For a <dynamic parameter specification>, NULLABLE is set to 1, indicating that the <dynamic parameter specification> can have the null value. i) Case: i) For a <select list> column C, KEY_MEMBER is 1 if and only if C is a member of the primary key or preferred key of T. ii) Otherwise, KEY_MEMBER is 0. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) A <descriptor name> shall be a <literal>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 86 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.5 <set descriptor statement> 13.5 <set descriptor statement> Function Set information in an SQL descriptor area. Format <set descriptor statement> ::= SET DESCRIPTOR <descriptor name> <set descriptor information> <set descriptor information> ::= <set header information> [ { <comma> <set header information> }... ] | VALUE <item number> <set item information> [ { <comma> <set item information> }... ] <set header information> ::= <header item name> <equals operator> <simple value specification 1> <set item information> ::= <descriptor item name> <equals operator> <simple value specification 2> <simple value specification 1> ::= <simple value specification> <simple value specification 2> ::= <simple value specification> <item number> ::= <simple value specification> Syntax Rules 1) The data type of <item number> shall be exact numeric with scale 0. 2) <header item name> shall not be KEY_TYPE or DYNAMIC_FUNCTION. 3) The data type of a <simple value specification 1> shall be the data type shown in Table 1, "Data types of <key word>s used in SQL item descriptor areas", for the corresponding <header item name>. 4) The data type of a <simple value specification 2> shall be the data type shown in Table 1, "Data types of <key word>s used in SQL item descriptor areas", for the corresponding <descriptor item name>. <descriptor item name> shall not be RETURNED_LENGTH, RETURNED_OCTET_LENGTH, OCTET_LENGTH, NULLABLE, KEY_MEMBER, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, NAME, or Dynamic SQL 87  SOU-007 and X3H2-93-083 13.5 <set descriptor statement> UNNAMED. Other alternatives for <descriptor item name> shall not be specified more than once in a <set descriptor statement>. Access Rules None. General Rules 1) If a <descriptor name> specified in a <set descriptor statement> identifies an SQL descriptor area that is not currently allocated, then an exception condition is raised: invalid SQL descriptor name. 2) If the <item number> specified in a <set descriptor statement> is greater than the number of <occurrences> specified when the <descriptor name> was allocated or less than 1, then an exception condition is raised: dynamic SQL error-invalid descriptor index. 3) A <set descriptor statement> sets values in the SQL descriptor area specified by <descriptor name>. The values are as follows: a) COUNT is a count of the number of <dynamic parameter specification> values or <target specification>s described in item descriptor areas. b) TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME are a description of the data type, length (in characters, bits, or positions, as appropriate), precision, scale, datetime data type, interval qualifier, and character set of a <dynamic parameter specification> value or <target specification> described by the item descriptor area specified by <item number>. c) DATA is a value for the <dynamic parameter specification> described by the item descriptor area specified by <item number>. INDICATOR is a value for the <indicator parameter> associated with the <dynamic parameter specification>. 4) When more than one value is set in a single <set descriptor statement>, the values are effectively assigned in the following order: TYPE, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_ PRECISION, PRECISION, SCALE, CHARACTER_SET_CATALOG, CHARACTER_ SET_SCHEMA, CHARACTER_SET_NAME, LENGTH, INDICATOR, and DATA. When any value other than DATA is set, the value of DATA becomes undefined. 88 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.5 <set descriptor statement> 5) For every <set item information> specified, let DIN be the <descriptor item name> and let V be the value of the <simple value specification 2>. If DIN is DATA and the data type of V does not match the data type specified by the item descriptor area, then an exception condition is raised: data exception- error in assignment. Note: "Match" is defined in the Syntax Rules of Subclause 13.1, "Description of SQL item descriptor areas". The item descriptor area field DIN is set to V and then Case: a) If DIN is TYPE and V indicates CHARACTER or CHARACTER VARYING, then CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME are set to the values for the default character set name for the SQL-session, LENGTH is set to 1, and all other item descriptor area fields are set to implementation-dependent values. b) If DIN is TYPE and V indicates BIT or BIT VARYING, then LENGTH is set to 1 and all other item descriptor area fields are set to implementation-dependent values. c) If DIN is TYPE and V indicates DATETIME, then PRECISION is set to 0 and all other item descriptor area fields are set to implementation-dependent values. d) If DIN is TYPE and V indicates INTERVAL, then DATETIME_ INTERVAL_PRECISION is set to 2 and all other item descriptor area fields are set to implementation-dependent values. e) If DIN is TYPE and V indicates NUMERIC or DECIMAL, then SCALE is set to 0, PRECISION is set to the implementation-defined default value for the precision of NUMERIC or DECIMAL data types, respectively, and all other item descriptor area fields are set to implementation-dependent values. f) If DIN is TYPE and V indicates INTEGER or SMALLINT, then all other item descriptor area fields are set to implementation- dependent values. g) If DIN is TYPE and V indicates FLOAT, then PRECISION is set to the implementation-defined default value for the precision of FLOAT data types and all other item descriptor area fields are set to implementation-dependent values. h) If DIN is TYPE and V indicates REAL or DOUBLE PRECISION, then all other item descriptor area fields are set to implementation-dependent values. Dynamic SQL 89  SOU-007 and X3H2-93-083 13.5 <set descriptor statement> i) If DIN is DATETIME_INTERVAL_CODE and TYPE is DATETIME, then i) If V indicates DATE, TIME, or TIME WITHTIME ZONE, then PRECISION is set to 0 and all other item descriptor area fields are set to implementation-dependent values. ii) If V indicates TIMESTAMP, or TIMESTAMP WITHTIME ZONE, then PRECISION is set to 6 and all other item descriptor area fields are set to implementation-dependent values. j) If DIN is DATETIME_INTERVAL_CODE and TYPE is INTERVAL, then DATETIME_INTERVAL_PRECISION is set to 2 and i) If V indicates DAY TO SECOND, HOUR TO SECOND, MINUTE TO SECOND, or SECOND, then PRECISION is set to 6. ii) Otherwise, PRECISION is set to 0. 6) If an exception condition is raised in a <set descriptor statement>, then the values of all elements of the item descriptor area specified in the <set descriptor statement> are implementation-dependent. 7) Restrictions on changing TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, CHARACTER_ SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, NULL_ CLASS_CATALOG, NULL_CLASS_SCHEMA, and NULL_CLASS_NAME values resulting from the execution of a <describe statement> before execution of an <execute statement>, <dynamic open statement>, or <dynamic fetch statement> are implementation-defined, except as specified in the General Rules of Subclause 13.9, "<using clause>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) A <descriptor name> shall be a <literal>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 90 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.6 <prepare statement> 13.6 <prepare statement> Function Prepare a statement for execution. Format <prepare statement> ::= PREPARE <SQL statement name> FROM <SQL statement variable> <SQL statement variable> ::= <simple value specification> <preparable statement> ::= <preparable SQL data statement> | <preparable SQL schema statement> | <preparable SQL transaction statement> | <preparable SQL control statement> | <preparable SQL session statement> ______________________________________________________________ ANSI Only-SQL3 ______________________________________________________________ | <dynamic procedure> ______________________________________________________________ | <preparable implementation-defined statement> <preparable SQL data statement> ::= <delete statement: searched> | <dynamic single row select statement> | <insert statement> | <dynamic select statement> | <update statement: searched> | <preparable dynamic delete statement: positioned> | <preparable dynamic update statement: positioned> <preparable SQL schema statement> ::= <SQL schema statement> <preparable SQL transaction statement> ::= <SQL transaction statement> <preparable SQL control statement> ::= <SQL control statement> <preparable SQL session statement> ::= <SQL session statement> <dynamic select statement> ::= <cursor specification> <dynamic single row select statement> ::= <query specification> ___________________________________________________________________ ANSI Only-SQL3 Dynamic SQL 91  SOU-007 and X3H2-93-083 13.6 <prepare statement> ___________________________________________________________________ <dynamic procedure> ::= <routine> ___________________________________________________________________ <preparable implementation-defined statement> ::= !! See the Syntax Rules. Syntax Rules 1) The <simple value specification> of <SQL statement variable> shall not be a <literal>. 2) The data type of <SQL statement variable> shall be character string. 3) The Format and Syntax Rules for <preparable implementation- defined statement> are implementation-defined. 4) A <preparable SQL control statement> shall not contain an <SQL procedure statement> that is not a <preparable statement>, nor shall it contain a <dynamic single row select statement> or a <dynamic select statement>. Access Rules None. General Rules 1) Let P be the contents of the <SQL statement variable>. If P is an <SQL control statement>, then let PC be an <SQL procedure statement> contained in P. 2) If P or PS is a <preparable dynamic delete statement: positioned> or a <preparable dynamic update statement: positioned>, then P or PS, respectively, refers to either a dynamic cursor with the same <cursor name> or to an extended dynamic cursor whose <extended cursor name> value is the same as the <cursor name>. Case: a) If both an extended dynamic cursor and a dynamic cursor with the same name as the <cursor name> exist, then an exception condition is raised: ambiguous cursor name. b) If there is neither an extended dynamic cursor nor a dynamic cursor with the name of <cursor name>, then an exception condition is raised: invalid cursor name. 92 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.6 <prepare statement> 3) Let E1, E2, E3, and E4 be <value expression>s of the form "<dynamic parameter specification>" or a <dynamic parameter specification> enclosed in any number of matching parentheses. Let F be a <value expression> whose data type is determined via General Rules in Clause 6, "Scalar expressions" and the Rules in this Subclause. Let X1, X2, X3, and X4 be <value expression>s meeting the criteria for either En or F. 4) If one or more of the following are true, then an exception condition is raised: syntax error or access rule violation. a) P does not conform to the Format, Syntax Rules, and Access Rules of a <preparable statement>. ______________________________________________________________ ANSI Only-SQL3 ______________________________________________________________ b) P is a <dynamic procedure> that does not specify CREATE and one or more of the following are true: i) P immediately contains a <routine header> that is not PROCEDURE. ii) P immediately contains a <routine name>. iii) <parameter list> does not contain only <SQL parameter>s iv) <parameter list> contains a <status parameter>. v) P immediately contains a <caller language clause>. vi) P immediately contains SPECIFIC. vii) P immediately contains a <routine body> that is <external body reference>. viii)P contains a <dynamic parameter specification>. ix) P immediately contains a <routine body> that contains a <SQL procedure statement> that is not a <preparable statement>. ___________________________________________________________ c) P contains E1 as a <value expression> simply contained in a <select list>. d) P contains E1 as both operands of a single dyadic operator. e) P contains a <value expression> of the form "+ E1", "- E1", "E1 COLLATE <collation name>", or "EXTRACT (<extract field> FROM E1)". f) P contains a <set function specification> with argument E1. Dynamic SQL 93  SOU-007 and X3H2-93-083 13.6 <prepare statement> g) P contains a <null predicate> with a value E1 in the <row value constructor>. h) P contains an <overlaps predicate> where the <value expression> that is the second <row value constructor element> in either the first or second operands of the <overlaps predicate> is E1. i) P contains E1 as the first operand of COALESCE or the first <when operand> in a <case specification> or both operands of NULLIF. j) P contains a <comparison predicate> or <between predicate> where both the i-th <value expression> in the <row value constructor> that is the first operand and the i-th <value expression> in the <row value constructor> that is the second operand of the <comparison predicate> or the second or third operand of the <between predicate> are E1. k) P contains a <table value constructor> in which the i-th <value expression> in each <row value constructor> is E1, the innermost <preparable statement> that contains <table value constructor> is IPS, and either: i) IPS is not an <insert statement>, or ii) IPS is an <insert statement> and the <table value constructor> is not the <query expression> simply contained in the <insert statement>. l) P contains an <in predicate> with an <in value list> in which both the <row value constructor> and the first <value specification> of the <in value list> are E1. m) P contains a <position expression> in which both immediately contained <character value expression>s are E1. n) P contains a <form-of-use conversion> or a <character translation> whose immediately contained <character value expression> is E1. o) P contains a <fold> whose operand is E1. p) P contains a <trim function> whose <trim character> or <trim source> is E1. q) P contains a <character substring function> whose <character value expression> is E1. r) P contains a <simple comment>. s) P contains a <using argument> that is E1. t) P contains an <assignment statement> and E1 is both the <assignment target> and the <value expression>. 94 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.6 <prepare statement> u) P contains a <case statement>, E1 is the <value expression>, and no <case statement when clause> specifies a <literal>. 5) Case: a) If E1 is followed by an <interval qualifier> IQ, then the data type of E1 is assumed to be INTERVAL IQ. b) In OCTET_LENGTH(E1), CHAR_LENGTH(E1), and CHARACTER_ LENGTH(E1), the data type of E1 is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of <length> for CHARACTER VARYING. c) In POSITION(X1 IN X2), and SUBSTRING(X1 FROM X3 FOR X4), if X1 (X2) meets the criteria for E1, E2, E3, and E4, then the data type of X1 (X2) is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of <length> for CHARACTER VARYING. If X3 (X4) meets the criteria for E1, E2, E3, and E4, then the data type of X3 (X4) is assumed to be NUMERIC(P,0), where P is the implementation- defined maximum value of <precision> for NUMERIC. d) In a <value expression> of the form "X1 <concatenation operator> X2", if X1 (X2) meets the criteria for E1, E2, E3, and E4, then the data type of X1 (X2) is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of <length> for CHARACTER VARYING. e) In BIT_LENGTH(E1), the data type of E1 is assumed to be BIT VARYING(L), where L is the implementation-defined maximum value of <length> for BIT VARYING. f) In a <value expression> of the form "E1 + <datetime term>", "<datetime term> + E1" or "<datetime term> - E1", the data type of E1 is assumed to be Case: i) If the <datetime term> is a date data type, then the data type of E1 is assumed to be INTERVAL YEAR(P) TO MONTH, where P is the implementation-defined maximum <interval leading field precision>. ii) Otherwise, the data type of E1 is assumed to be INTERVAL DAY(P) TO SECOND(F), where P and F are the implementation- defined maximum <interval leading field precision> and maximum <interval fractional seconds precision>, respectively. g) In a <value expression> of the form "<interval term> * E1" or "<interval term> / E1", the data type of E1 is assumed to be NUMERIC(P,0), where P is the implementation-defined maximum value of <precision> for NUMERIC. Dynamic SQL 95  SOU-007 and X3H2-93-083 13.6 <prepare statement> h) In all other <value expression>s of the form "E1+ F", "E1- F", "E1* F", "E1/ F", "F + E1", "F - E1", "F * E1", or "F / E1", the data type of E1 is assumed to be the data type of F. i) In a <value expression> of the form "CAST (E1 AS <domain name>)", "CAST (E1 AS <data type>)", the data type of E1 is the <data type> of the specified domain or the explicitly- specified <data type>. j) If one or more operands of COALESCE are E1, then the data type of E1 is assumed to be the data type of the first operand. k) If one or more <when operand>s in a <case specification> are E1, then the data type of E1 is assumed to be the data type of the first <when operand>. l) If one operand of NULLIF is E1, then the data type of E1 is assumed to be the data type of the other operand. m) In the first and second operands of a <comparison predicate> or <between predicate>, or the first and third operands of a <between predicate>, if the i-th value of one operand is E1, then the data type of E1 is the data type of the i-th value of the other operand. n) In the first and second operands of an <overlaps predicate>, if either of the first <row value constructor element>s is E1, then the data type of E1 is the data type of the first <row value constructor element> of the other operand. If both of the first <row value constructor element>s are E1, then the data type of each E1 is assumed to be TIMESTAMP WITH TIME ZONE. o) In a <table value constructor> in which the i-th <value expression> of some <row value constructor> is E1 that contains a <row value constructor> whose i-th <value expression> is not E1, the data type of E1 is the data type of the i-th <value expression> of the first <row value constructor> whose i-th <value expression> is not E1. p) In a <table value constructor> in which the i-th <value expression> in each <row value constructor> is E1 that is the <query expression> simply contained in an <insert statement>, the data type of E1 is the data type of the corresponding column of the implicit or explicit <insert column list> contained in the <insert statement>. q) In an <in predicate> that specifies a <table subquery>, the data types of <value expression>s E1 in the <row value constructor> are assumed to be the same as the data types of the respective columns of the <table subquery>. 96 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.6 <prepare statement> r) In an <in predicate> that specifies an <in value list>, if the <row value constructor> is not E1, then let D be its data type. Otherwise, let D be the data type of the first <value specification> of the <in value list>. The data type of any E1 in the <in predicate> is assumed to be D. s) If E1 appears for <match value>, <pattern>, or <escape character> in <like predicate>, then the data type of E1 is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of <length> for CHARACTER VARYING. t) If any value in the <row value constructor> of a <quantified comparison predicate> or <match predicate> is E1, then the data type of E1 is assumed to be the same as the data type of the respective column of the <table subquery>. u) If <cycle mark value> is E1 in <recursive union>, then the data type of E1 is assumed to be character string of length 1. If <limit expression> contains a <value specification> E1, then the data type of E1 is assumed to be exact numeric with scale 0. v) If <value specification> in <set catalog statement>, <set schema statement>, <set names statement>, or <set session authorization identifier statement> is E1, then the data type of E1 is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of <length> for CHARACTER VARYING. w) If <simple value specification> in <set local time zone statement> is E1, then the data type of E1 is assumed to be CHARACTER VARYING(L), where L is the implementation-defined maximum value of <length> for CHARACTER VARYING. x) If a <value expression> in a <set clause> is E1, then the data type of E1 is assumed to be the same data type as the corresponding <object column>. y) If E1 is the <value expression> of an <assignment statement>, then the data type of E1 is assumed to be the data type of the <assignment target>. If E1 is the <assignment target> of an <assignment statement>, then the data type of E1 is assumed to be the data type of the <value expression>. z) If E1 is the <value expression> of a <case statement>, then the data type of E1 is assumed to be the data type of the <literal> of the first <case statement when clause> that specifies a <literal>. Dynamic SQL 97  SOU-007 and X3H2-93-083 13.6 <prepare statement> 6) If a <dynamic parameter specification> is contained in a <target specification>, then it is an output <dynamic parameter specification>. If a <dynamic parameter specification> is contained in a <value specification>, then it is an input <dynamic parameter specification>. 7) If the value of the <SQL statement name> identifies an existing prepared statement, then an implicit DEALLOCATE PREPARE SSN is executed, where SSN is the value of the <SQL statement name>. 8) P is prepared for execution. 9) If <extended statement name> is specified for the <SQL statement name>, then let S be the character string that is the value of the <simple target specification> and let V be the character string that is the result of TRIM ( BOTH ' ' FROM S ) If V does not conform to the Format and Syntax Rules of an <identifier>, then an exception condition is raised: invalid SQL statement identifier. 10)Case: a) If <extended statement name> is specified for the <SQL statement name>, then the value of the <extended statement name> is associated with the prepared statement. This value and explicit or implied <scope option> shall be specified for each <execute statement> or <allocate cursor statement> that is to be associated with this prepared statement. b) If <statement name> is specified for the <SQL statement name>, then: i) If P is not a <cursor specification> and <statement name> is associated with a cursor C through a <dynamic declare cursor>, then an exception condition is raised: dynamic SQL error- prepared statement is not a cursor specification. ii) Otherwise: 1) If <statement name> is not associated with a cursor and either P is not a <cursor specification> or P is a <cursor specification> that conforms to the Format and Syntax Rules of a <dynamic single row select statement>, then the same <statement name> shall be specified for each <execute statement> that is to be associated with this prepared statement. 98 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.6 <prepare statement> 2) If P is a <cursor specification> and <statement name> is associated with a cursor C through a <dynamic declare cursor>, then an association is made between C and P. The association is preserved until the prepared statement is destroyed. 11)The validity of an <extended statement name> value or a <statement name> in an SQL-transaction different from the one in which the statement was prepared is implementation-dependent. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 99  SOU-007 and X3H2-93-083 13.7 <deallocate prepared statement> 13.7 <deallocate prepared statement> Function Deallocate SQL-statements that have been prepared with a <prepare statement>. Format <deallocate prepared statement> ::= DEALLOCATE PREPARE <SQL statement name> Syntax Rules 1) If <SQL statement name> is a <statement name>, then the <module> that contains the <deallocate prepared statement> shall also contain a <prepare statement> that specifies the same <statement name>. Access Rules None. General Rules 1) If the <SQL statement name> does not identify a statement prepared in the scope of the <SQL statement name>, then an exception condition is raised: invalid SQL statement name. 2) If the value of <SQL statement name> identifies an existing prepared statement that is the <cursor specification> of an open cursor, then an exception condition is raised: invalid cursor state. 3) The prepared statement identified by the <SQL statement name> is destroyed. Any cursor that was allocated with an <allocate cursor statement> that is associated with the prepared statement identified by the <SQL statement name> is destroyed. If the value of the <SQL statement name> identifies an existing prepared statement that is a <cursor specification>, then any prepared statements that reference that cursor are destroyed. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall contain no <deallocate prepared statement>. 100 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.7 <deallocate prepared statement> 3) The following restrictions apply for Entry SQL in addition to Intermediate SQL restrictions: None. Dynamic SQL 101  SOU-007 and X3H2-93-083 13.8 <describe statement> 13.8 <describe statement> Function Obtain information about the <select list> columns or <dynamic parameter specification>s contained in a prepared statement. Format <describe statement> ::= <describe input statement> | <describe output statement> <describe input statement> ::= DESCRIBE INPUT <SQL statement name> <using descriptor> <describe output statement> ::= DESCRIBE [ OUTPUT ] <SQL statement name> <using descriptor> Syntax Rules 1) If <SQL statement name> is a <statement name>, then the containing <module> shall contain a <prepare statement> whose <statement name> is the same as the <statement name> of the <describe statement>. Access Rules None. General Rules 1) When the <describe statement> is executed, if the value of the <SQL statement name> does not identify a statement prepared in the scope of the <SQL statement name>, then an exception condition is invalid SQL statement name. 2) The descriptor area is set with information as described in Subclause 13.9, "<using clause>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall not contain any <describe input statement>. 102 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.8 <describe statement> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 103  SOU-007 and X3H2-93-083 13.9 <using clause> 13.9 <using clause> Function Describe the input/output variables for an <SQL dynamic statement>. Format <using clause> ::= <using arguments> | <using descriptor> <using arguments> ::= { USING | INTO } <using argument> [ { <comma> <using argument> }... ] <using argument> ::= <target specification> <using descriptor> ::= { USING | INTO } SQL DESCRIPTOR <descriptor name> Syntax Rules 1) The keyword INTO shall appear in <using clause> only if the <using clause> is contained in a <dynamic fetch statement> or a <result using clause>. Access Rules None. General Rules 1) The <identifier>s used to reference components of SQL descriptor areas are as shown in Table 1, "Data types of <key word>s used in SQL item descriptor areas". 2) If a <descriptor name> is specified in a <using clause>, then an SQL system descriptor area shall have been allocated and not yet deallocated whose name is the value of the <descriptor name>'s <simple value specification> and whose scope is that specified by the <scope option>. Otherwise, an exception condition is raised: invalid SQL descriptor name. 3) When a <describe output statement> is executed and the prepared statement that is being described is a <dynamic select statement> or a <dynamic single row select statement>, a representation of the column descriptors of the <select list> 104 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> columns for the prepared statement is stored in the specified SQL descriptor area as follows: a) Let N be the <occurrences> specified when the <descriptor name> was allocated. b) let T be the table defined by the prepared statement and let D be the degree of T. c) COUNT is set to D. d) DYNAMIC_FUNCTION is set to the identifier for the prepared statement as shown in Table 26, "SQL-statement character codes for use in the diagnostics area". e) If D is greater than N, then a completion condition is raised: warning-insufficient item descriptor areas. f) If D is 0 or D is greater than N, then no item descriptor areas are set. Otherwise, the first D item descriptor areas are set so that the i-th item descriptor area contains the descriptor of the i-th column of T. The descriptor of a column consists of values for TYPE, NULLABLE, NAME, UNNAMED, and other fields depending on the value of TYPE as described below. The DATA and INDICATOR fields are not relevant in this case. Those fields and fields that are not applicable for a particular value of TYPE are set to implementation-dependent values. i) TYPE is set to a code, as shown in Table 2, "Codes used for SQL data types in Dynamic SQL", indicating the data type of the column. ii) NULLABLE is set to 1 if the resulting column is possibly nullable and 0 otherwise. iii) If the column name is implementation-dependent, then NAME is set to the implementation-dependent name of the column, and UNNAMED is set to 1. Otherwise, NAME is set to the <derived column> name for the column and UNNAMED is set to 0. iv) If the column does not have the general null class, then the values of NULL_CLASS_CATALOG, NULL_CLASS_SCHEMA, and NULL_CLASS_NAME are set to the catalog name, schema name, and null class name of the null class for the column; otherwise they are set to <space>s. v) Case: 1) If TYPE indicates a <character string type>, then: LENGTH is set to the length or maximum length in characters of the character string; OCTET_LENGTH is set to the maximum possible length in octets of the character string; CHARACTER_SET_CATALOG, CHARACTER_SET_ Dynamic SQL 105  SOU-007 and X3H2-93-083 13.9 <using clause> SCHEMA and CHARACTER_SET_NAME are set to the <character set name> of the character string's character set; and COLLATION_CATALOG, COLLATION_SCHEMA and COLLATION_ NAME are set to the <collation name> of the character string's collation. If the subject <language clause> specifies C, then the lengths specified in LENGTH and OCTET_LENGTH do not include the implementation-defined null character that terminates a C character string. 2) If TYPE indicates a <bit string type>, then LENGTH is set to the length or maximum length in bits of the bit string and OCTET_LENGTH is set to the maximum possible length in octets of the bit string. 3) If TYPE indicates an <exact numeric type>, then PRECISION and SCALE are set to the precision and scale of the exact numeric. 4) If TYPE indicates an <approximate numeric type>, then PRECISION is set to the precision of the approximate numeric. 5) If TYPE indicates a <datetime type>, then LENGTH is set to the length in positions of the datetime type, DATETIME_INTERVAL_CODE is set to a code as specified in Table 3, "Codes associated with datetime data types in Dynamic SQL", to indicate the specific datetime data type, and PRECISION is set to the <time precision> or <timestamp precision>, if either is applicable. 6) If TYPE indicates an <interval type>, then DATETIME_ INTERVAL_CODE is set to a code as specified in Table 4, "Codes used for <interval qualifier>s in Dynamic SQL", to indicate the <interval qualifier> of the interval data type, DATETIME_INTERVAL_PRECISION is set to the <interval leading field precision>, and PRECISION is set to the <interval fractional seconds precision>, if applicable. 7) If TYPE indicates the <boolean type>, then PRECISION, SCALE, LENGTH, and OCTET_LENGTH are implementation- dependent. 8) If TYPE indicates an abstract data type, then the values of LENGTH, OCTET_LENGTH, SCALE, and PRECISION are implementation-dependent. 4) When a <describe output statement> is executed and the prepared statement that is being described is not a <dynamic select statement>, a <dynamic single row select statement>, or a <dynamic procedure>, a descriptor for the output <dynamic 106 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> parameter specification>s for the prepared statement is stored in the specified SQL descriptor area as follows: a) Let N be the <occurrences> specified when the <descriptor name> was allocated. b) Let D be the number of output <dynamic parameter specification>s in the prepared statement. c) COUNT is set to D. d) DYNAMIC_FUNCTION is set to the identifier for the prepared statement as shown in Table 26, "SQL-statement character codes for use in the diagnostics area". e) If D is greater than N, then a completion condition is raised: warning-insufficient item descriptor areas. f) If D is 0 or D is greater than N, then no item descriptor areas are set. Otherwise, the first D item descriptor areas are set so that the i-th item descriptor area contains a descriptor of the i-th output <dynamic parameter specification>. The descriptor of a <dynamic parameter specification> consists of values for TYPE, NULLABLE, NAME, UNNAMED, and other fields depending on the value of TYPE as described below. The DATA and INDICATOR fields are not relevant in this case. Those fields and fields that are not applicable for a particular value of TYPE are set to implementation-dependent values. i) TYPE is set to a code, as shown in Table 2, "Codes used for SQL data types in Dynamic SQL", indicating the data type of the column. ii) NULLABLE is set to 1 if the resulting column is possibly nullable and 0 otherwise. iii) If the column name is implementation-dependent, then NAME is set to the implementation-dependent name of the column, and UNNAMED is set to 1. Otherwise, NAME is set to the <derived column> name for the column and UNNAMED is set to 0. iv) If the column does not have the general null class, then the values of NULL_CLASS_CATALOG, NULL_CLASS_SCHEMA, and NULL_CLASS_NAME are set to the catalog name, schema name, and null class name of the null class for the column; otherwise they are set to <space>s. v) Case: 1) If TYPE indicates a <character string type>, then: LENGTH is set to the length or maximum length in characters of the character string; OCTET_LENGTH is set to the maximum possible length in octets of the Dynamic SQL 107  SOU-007 and X3H2-93-083 13.9 <using clause> character string; CHARACTER_SET_CATALOG, CHARACTER_SET_ SCHEMA and CHARACTER_SET_NAME are set to the <character set name> of the character string's character set; and COLLATION_CATALOG, COLLATION_SCHEMA and COLLATION_ NAME are set to the <collation name> of the character string's collation. If the subject <language clause> specifies C, then the lengths specified in LENGTH and OCTET_LENGTH do not include the implementation-defined null character that terminates a C character string. 2) If TYPE indicates a <bit string type>, then LENGTH is set to the length or maximum length in bits of the bit string and OCTET_LENGTH is set to the maximum possible length in octets of the bit string. 3) If TYPE indicates an <exact numeric type>, then PRECISION and SCALE are set to the precision and scale of the exact numeric. 4) If TYPE indicates an <approximate numeric type>, then PRECISION is set to the precision of the approximate numeric. 5) If TYPE indicates a <datetime type>, then LENGTH is set to the length in positions of the datetime type, DATETIME_INTERVAL_CODE is set to a code as specified in Table 3, "Codes associated with datetime data types in Dynamic SQL", to indicate the specific datetime data type, and PRECISION is set to the <time precision> or <timestamp precision>, if either is applicable. 6) If TYPE indicates an <interval type>, then DATETIME_ INTERVAL_CODE is set to a code as specified in Table 4, "Codes used for <interval qualifier>s in Dynamic SQL", to indicate the <interval qualifier> of the interval data type, DATETIME_INTERVAL_PRECISION is set to the <interval leading field precision>, and PRECISION is set to the <interval fractional seconds precision>, if applicable. 7) If TYPE indicates the <boolean type>, then PRECISION, SCALE, LENGTH, and OCTET_LENGTH are implementation- dependent. 8) If TYPE indicates an abstract data type, then the values of LENGTH, OCTET_LENGTH, SCALE, and PRECISION are implementation-dependent. 5) When a <describe output statement> is executed and the prepared statement that is being described is a <dynamic procedure> a 108 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> descriptor for the output parameters of the procedure are stored in the specified SQL descriptor area as follows: a) Let N be the <occurrences> specified when the <descriptor name> was allocated. b) Let D be the number of output parameters. c) COUNT is set to D. d) If D is greater than N, then a completion condition is raised: warning-insufficient item descriptor areas. e) If D is 0 or D is greater than N, then no item descriptor areas are set. Otherwise, the first D item descriptor areas are set so that the i-th item descriptor area contains a descriptor of the i-th output parameter. The descriptor of a parameter consists of values for TYPE, NULLABLE, NAME, UNNAMED, and other fields depending on the value of TYPE as described below. The DATA and INDICATOR fields are not relevant in this case. Those fields and fields that are not applicable for a particular value of TYPE are set to implementation-dependent values. i) TYPE is set to a code, as shown in Table 2, "Codes used for SQL data types in Dynamic SQL", indicating the data type of the column. ii) NULLABLE is set to 1 if the resulting column is possibly nullable and 0 otherwise. iii) NAME is set to the <parameter name> of the parameter, and UNNAMED is set to 1. iv) If the column does not have the general null class, then the values of NULL_CLASS_CATALOG, NULL_CLASS_SCHEMA, and NULL_CLASS_NAME are set to the catalog name, schema name, and null class name of the null class for the column; otherwise they are set to <space>s. v) Case: 1) If TYPE indicates a <character string type>, then: LENGTH is set to the length or maximum length in characters of the character string; OCTET_LENGTH is set to the maximum possible length in octets of the character string; CHARACTER_SET_CATALOG, CHARACTER_SET_ SCHEMA and CHARACTER_SET_NAME are set to the <character set name> of the character string's character set; and COLLATION_CATALOG, COLLATION_SCHEMA and COLLATION_ NAME are set to the <collation name> of the character string's collation. If the subject <language clause> specifies C, then the lengths specified in LENGTH and OCTET_LENGTH do not include the implementation-defined null character that terminates a C character string. Dynamic SQL 109  SOU-007 and X3H2-93-083 13.9 <using clause> 2) If TYPE indicates a <bit string type>, then LENGTH is set to the length or maximum length in bits of the bit string and OCTET_LENGTH is set to the maximum possible length in octets of the bit string. 3) If TYPE indicates an <exact numeric type>, then PRECISION and SCALE are set to the precision and scale of the exact numeric. 4) If TYPE indicates an <approximate numeric type>, then PRECISION is set to the precision of the approximate numeric. 5) If TYPE indicates a <datetime type>, then LENGTH is set to the length in positions of the datetime type, DATETIME_INTERVAL_CODE is set to a code as specified in Table 3, "Codes associated with datetime data types in Dynamic SQL", to indicate the specific datetime data type, and PRECISION is set to the <time precision> or <timestamp precision>, if either is applicable. 6) If TYPE indicates an <interval type>, then DATETIME_ INTERVAL_CODE is set to a code as specified in Table 4, "Codes used for <interval qualifier>s in Dynamic SQL", to indicate the <interval qualifier> of the interval data type, DATETIME_INTERVAL_PRECISION is set to the <interval leading field precision>, and PRECISION is set to the <interval fractional seconds precision>, if applicable. 7) If TYPE indicates the <boolean type>, then PRECISION, SCALE, LENGTH, and OCTET_LENGTH are implementation- dependent. 8) If TYPE indicates an abstract data type, then the values of LENGTH, OCTET_LENGTH, SCALE, and PRECISION are implementation-dependent. 6) When a <describe input statement> is <ANSI> executed and the prepared statement being described is not a <dynamic procedure>, <ISO > executed, a descriptor for the <dynamic parameter specification>s for the prepared statement is stored in the specified SQL descriptor area as follows: a) Let N be the <occurrences> specified when the <descriptor name> was allocated. b) Let D be the number of <dynamic parameter specification>s in the prepared statement. c) COUNT is set to D. 110 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> d) DYNAMIC_FUNCTION is set to the identifier for the prepared statement as shown in Table 26, "SQL-statement character codes for use in the diagnostics area". e) If D is greater than N, then a completion condition is raised: warning-insufficient item descriptor areas. f) If D is 0 or D is greater than N, then no item descriptor areas are set. Otherwise, the first D item descriptor areas are set so that the i-th item descriptor area contains a descriptor of the i-th <dynamic parameter specification>. The descriptor of a <dynamic parameter specification> consists of values for TYPE, NULLABLE, NAME, UNNAMED, and other fields depending on the value of TYPE as described below. The DATA and INDICATOR fields are not relevant in this case. Those fields and fields that are not applicable for a particular value of TYPE are set to implementation-dependent values. i) TYPE is set to a code, as shown in Table 2, "Codes used for SQL data types in Dynamic SQL", indicating the data type of the <dynamic parameter specification>. ii) NULLABLE is set to 1. Note: This indicates that the <dynamic parameter specification> can have the null value. iii) UNNAMED is set to 1 and NAME is set to an implementation- dependent value. iv) The values of NULL_CLASS_CATALOG, NULL_CLASS_SCHEMA, and NULL_CLASS_NAME are implementation-dependent. v) Case: 1) If TYPE indicates a <character string type>, then: LENGTH is set to the length or maximum length in characters of the character string; OCTET_LENGTH is set to the maximum possible length in octets of the character string; CHARACTER_SET_CATALOG, CHARACTER_SET_ SCHEMA and CHARACTER_SET_NAME are set to the <character set name> of the character string's character set; and COLLATION_CATALOG, COLLATION_SCHEMA and COLLATION_ NAME are set to the <collation name> of the character string's collation. If the subject <language clause> specifies C, then the lengths specified in LENGTH and OCTET_LENGTH do not include the implementation-defined null character that terminates a C character string. 2) If TYPE indicates a <bit string type>, then LENGTH is set to the length or maximum length in bits of the bit string and OCTET_LENGTH is set to the maximum possible length in octets of the bit string. Dynamic SQL 111  SOU-007 and X3H2-93-083 13.9 <using clause> 3) If TYPE indicates an <exact numeric type>, then PRECISION and SCALE are set to the precision and scale of the exact numeric. 4) If TYPE indicates an <approximate numeric type>, then PRECISION is set to the precision of the approximate numeric. 5) If TYPE indicates a <datetime type>, then LENGTH is set to the length in positions of the datetime type, DATETIME_INTERVAL_CODE is set to a code as specified in Table 3, "Codes associated with datetime data types in Dynamic SQL", to indicate the specific datetime data type and PRECISION is set to the <time precision> or <timestamp precision>, if either is applicable. 6) If TYPE indicates an <interval type>, then DATETIME_ INTERVAL_CODE is set to a code as specified in Table 4, "Codes used for <interval qualifier>s in Dynamic SQL", to indicate the <interval qualifier> of the interval data type, DATETIME_INTERVAL_PRECISION is set to the <interval leading field precision> and PRECISION is set to the <interval fractional seconds precision>, if applicable. 7) If TYPE indicates the <boolean type>, then PRECISION, SCALE, LENGTH, and OCTET_LENGTH are set to implementation-dependent values. 8) If TYPE indicates an abstract data type, then the values of LENGTH, OCTET_LENGTH, SCALE, and PRECISION are set to implementation-dependent values. _________________________________________________________ ANSI Only-SQL3 _________________________________________________________ 7) When a <describe input statement> is executed and the prepared statement being described is a <dynamic procedure>, a descriptor for the <dynamic parameter specification>s for the prepared statement is stored in the specified SQL descriptor area as follows: a) Let N be the <occurrences> specified when the <descriptor name> was allocated. b) Let D be the number of input parameters in the procedure. c) COUNT is set to D. d) DYNAMIC_FUNCTION is set to the identifier for the dynamic procedure as shown in Table 26, "SQL-statement character codes for use in the diagnostics area". 112 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> e) If D is greater than N, then a completion condition is raised: warning-insufficient item descriptor areas. f) If D is 0 or D is greater than N, then no item descriptor areas are set. Otherwise, the first D item descriptor areas are set so that the i-th item descriptor area contains a descriptor of the i-th input parameter. The descriptor of a parameter consists of values for TYPE, NULLABLE, NAME, UNNAMED, and other fields depending on the value of TYPE as described below. The DATA and INDICATOR fields are not relevant in this case. Those fields and fields that are not applicable for a particular value of TYPE are set to implementation-dependent values. i) TYPE is set to a code, as shown in Table 2, "Codes used for SQL data types in Dynamic SQL", indicating the data type of the <dynamic parameter specification>. ii) NULLABLE is set to 1. Note: This indicates that the <dynamic parameter specification> can have the null value. iii) NAME is set to the <parameter name> of the parameter and UNNAMED is set to 1. iv) The values of NULL_CLASS_CATALOG, NULL_CLASS_SCHEMA, and NULL_CLASS_NAME are implementation-dependent. v) Case: 1) If TYPE indicates a <character string type>, then: LENGTH is set to the length or maximum length in characters of the character string; OCTET_LENGTH is set to the maximum possible length in octets of the character string; CHARACTER_SET_CATALOG, CHARACTER_SET_ SCHEMA and CHARACTER_SET_NAME are set to the <character set name> of the character string's character set; and COLLATION_CATALOG, COLLATION_SCHEMA and COLLATION_ NAME are set to the <collation name> of the character string's collation. If the subject <language clause> specifies C, then the lengths specified in LENGTH and OCTET_LENGTH do not include the implementation-defined null character that terminates a C character string. 2) If TYPE indicates a <bit string type>, then LENGTH is set to the length or maximum length in bits of the bit string and OCTET_LENGTH is set to the maximum possible length in octets of the bit string. 3) If TYPE indicates an <exact numeric type>, then PRECISION and SCALE are set to the precision and scale of the exact numeric. Dynamic SQL 113  SOU-007 and X3H2-93-083 13.9 <using clause> 4) If TYPE indicates an <approximate numeric type>, then PRECISION is set to the precision of the approximate numeric. 5) If TYPE indicates a <datetime type>, then LENGTH is set to the length in positions of the datetime type, DATETIME_INTERVAL_CODE is set to a code as specified in Table 3, "Codes associated with datetime data types in Dynamic SQL", to indicate the specific datetime data type and PRECISION is set to the <time precision> or <timestamp precision>, if either is applicable. 6) If TYPE indicates an <interval type>, then DATETIME_ INTERVAL_CODE is set to a code as specified in Table 4, "Codes used for <interval qualifier>s in Dynamic SQL", to indicate the <interval qualifier> of the interval data type, DATETIME_INTERVAL_PRECISION is set to the <interval leading field precision> and PRECISION is set to the <interval fractional seconds precision>, if applicable. 7) If TYPE indicates the <boolean type>, then PRECISION, SCALE, LENGTH, and OCTET_LENGTH are set to implementation-dependent values. 8) If TYPE indicates an abstract data type, then the values of LENGTH, OCTET_LENGTH, SCALE, and PRECISION are set to implementation-dependent values. _________________________________________________________ 8) When a <using clause> is used in a <dynamic open statement> or as the <parameter using clause> in an <ANSI> <execute statement> and the prepared statement being executed is not a <dynamic procedure>, <ISO > <execute statement>, the <using clause> describes the input <dynamic parameter specification> values for the <dynamic open statement> or the <execute statement>, respectively. Let PS be the prepared <dynamic select statement> referenced by the <dynamic open statement> or the prepared statement referenced by the <execute statement>, respectively. Let D be the number of input <dynamic parameter specification>s in PS. a) If <using arguments> is specified and the number of <using argument>s is not D, then an exception condition is raised: dynamic SQL error- using clause does not match dynamic parameter specifications. 114 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> b) If <using descriptor> is specified, then: i) If the value of COUNT is greater than the number of <occurrences> specified when the <descriptor name> was allocated or is less than zero, then an exception condition is raised: dynamic SQL error-invalid descriptor count. ii) If the value of COUNT is not D, then an exception condition is raised: dynamic SQL error- using clause does not match dynamic parameter specifications. iii) If the first D item descriptor areas are not valid as specified in Subclause 13.1, "Description of SQL item descriptor areas", then an exception condition is raised: dynamic SQL error- using clause does not match dynamic parameter specifications. iv) If the value of INDICATOR is not negative, and the value of DATA is not a valid value of the data type indicated by TYPE, then an exception condition is raised: dynamic SQL error- using clause does not match dynamic parameter specifications. c) Let TDT be the effective data type of the i-th input <dynamic parameter specification>, defined to be the type represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_ INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, CHARACTER_SET_ CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME that would be set by a <describe input statement> to reflect the descriptor of the i-th input dynamic parameter of PS. Note: See the General Rules of Subclause 13.8, "<describe statement>". d) Case: i) If <using descriptor> is specified, then let SDT be the effective data type of the i-th input <dynamic parameter specification> value as represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_CODE, DATETIME_ INTERVAL_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_ SCHEMA, and CHARACTER_SET_NAME in the i-th item descriptor area. If the INDICATOR value of the i-th item descriptor area is not negative, then let SV be the value represented by the value of DATA with data type SDT. Otherwise, let SV be the general null value. ii) If <using arguments> is specified, then let SDT and SV be the data type and value, respectively, of the i-th <using argument>. e) If the <cast specification> CAST ( SV AS TDT ) Dynamic SQL 115  SOU-007 and X3H2-93-083 13.9 <using clause> violates the Syntax Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised: dynamic SQL error-restricted data type attribute violation. f) If the <cast specification> CAST (SV AS TDT ) violates the General Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised in accordance with the General Rules of Subclause 6.14, "<cast specification>". g) The <cast specification> CAST ( SV AS TDT ) is effectively performed and is the value of the i-th dynamic parameter. 9) When a <using clause> is used as the <parameter using clause> in an <execute statement> and the prepared statement that is being executed is a <dynamic procedure>, the <using clause> describes the input parameter val- ues for the <execute statement>. Let PS be the prepared <dynamic procedure>. Let D be the number of input parameters in PS. a) If <using arguments> is specified and the number of <using argument>s is not D, then an exception condition is raised: dynamic SQL error-using clause does not match dynamic parameters. b) If <using descriptor> is specified, then: i) If the value of COUNT is greater than the number of <occurrences> specified when the <descriptor name> was allocated or is less than zero, then an exception condition is raised: dynamic SQL error-invalid descriptor count. ii) If the value of COUNT is not D, then an exception condition is raised: dynamic SQL error-using clause does not match dynamic parameters. iii) If the first D item descriptor areas are not valid as specified in Subclause 13.1, "Description of SQL item descriptor areas", then an exception condition is raised: dynamic SQL error-using clause does not match dynamic parameters. iv) If the value of INDICATOR is not negative, and the value of DATA is not a valid value of the data type indicated by TYPE, then an exception condition is raised: dynamic SQL error-using clause does not match dynamic parameters. 116 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> c) Let TDT be the effective data type of the i-th input parameter, defined to be the type represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_ CODE, DATETIME_INTERVAL_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME that would be set by a <describe input statement> to reflect the descriptor of the i-th input parameter of PS. Note: See the General Rules of Subclause 13.8, "<describe statement>". d) Case: i) If <using descriptor> is specified, then let SDT be the effective data type of the i-th input parameter value as represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_ SET_NAME in the i-th item descriptor area. If the INDICATOR value of the i-th item descriptor area is not negative, then let SV be the value represented by the value of DATA with data type SDT. Otherwise, let SV be the general null value. ii) If <using arguments> is specified, then let SDT and SV be the data type and value, respectively, of the i-th <using argument>. e) If the <cast specification> CAST ( SV AS TDT ) violates the Syntax Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised: dynamic SQL error-restricted data type attribute violation. f) If the <cast specification> CAST ( SV AS TDT ) violates the General Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised in accordance with the General Rules of Subclause 6.14, "<cast specification>". g) The <cast specification> CAST ( SV AS TDT ) is effectively performed and is the value of the i-th input parameter. 10)When a <using clause> is used in a <dynamic fetch statement> or as the <result using clause> of an <execute statement> and the prepared statement that it identifies is a <dynamic single row select statement>, it describes the <target specification>s for the <dynamic fetch statement> or <execute statement>, respectively. Let PS be the prepared <dynamic select statement> Dynamic SQL 117  SOU-007 and X3H2-93-083 13.9 <using clause> referenced by the <dynamic fetch statement> or the prepared <dynamic single row select statement> referenced by the <execute statement>, respectively. Let D be the degree of the table specified by PS. a) If <using arguments> is specified and the number of <using argument>s is not D, then an exception condition is raised: dynamic SQL error- using clause does not match target specifications. b) If <using descriptor> is specified, then: i) If the value of COUNT is greater than the number of <occurrences> specified when the <descriptor name> was allocated or less than zero, then an exception condition is raised: dynamic SQL error-invalid descriptor count. ii) If COUNT is not equal to D, then an exception condition is raised: dynamic SQL error- using clause does not match target specifications. iii) If the first D item descriptor areas are not valid as specified in Subclause 13.1, "Description of SQL item descriptor areas", then an exception condition is raised: dynamic SQL error- using clause does not match target specifications. 11)When a <using clause> is used as the <result using clause> of an <execute statement> and the prepared statement that it identifies is not a <dynamic single row select statement>, it describes the <target specification>s for the <execute statement>. Let PS be the prepared statement referenced by the <execute statement>. Let D be the number of output <dynamic parameter specification>s. a) If <using arguments> is specified and the number of <using argument>s is not D, then an exception condition is raised: dynamic SQL error- using clause does not match target specifications. b) If <using descriptor> is specified, then: i) If the value of COUNT is greater than the number of <occurrences> specified when the <descriptor name> was allocated or less than zero, then an exception condition is raised: dynamic SQL error-invalid descriptor count. ii) If COUNT is not equal to D, then an exception condition is raised: dynamic SQL error- using clause does not match target specifications. 118 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> iii) If the first D item descriptor areas are not valid as specified in Subclause 13.1, "Description of SQL item descriptor areas", then an exception condition is raised: dynamic SQL error- using clause does not match target specifications. 12)When a <using clause> is used as the <result using clause> of an <execute statement> and the prepared statement that it identifies is a <dynamic procedure>, it describes the output parameters for the <execute statement>. Let PS be the prepared <dynamic procedure referenced by the <execute statement>. Let D be the degree of the table specified by PS. a) If <using arguments> is specified and the number of <using argument>s is not D, then an exception condition is raised: dynamic SQL error- using clause does not match output parameters. b) If <using descriptor> is specified, then: i) If the value of COUNT is greater than the number of <occurrences> specified when the <descriptor name> was allocated or less than zero, then an exception condition is raised: dynamic SQL error-invalid descriptor count. ii) If COUNT is not equal to D, then an exception condition is raised: dynamic SQL error- using clause does not match output parameters. iii) If the first D item descriptor areas are not valid as specified in Subclause 13.1, "Description of SQL item descriptor areas", then an exception condition is raised: dynamic SQL error- using clause does not match output parameters. 13)When a <using clause> is used in a <dynamic fetch statement> or as the <result using clause> of an <execute statement> that identifies a prepared statement that is a <dynamic single row select statement>, the result is a set of <target specification> values corresponding to the <select list> columns for the retrieved row. If <using descriptor> is specified, then the SQL descriptor area is set. The value of the i-th <target specification> is represented in the i-th item descriptor area as follows: a) Let SDT be the effective data type of the i-th <select list> column, defined to be the type represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_PRECISION, DATETIME_INTERVAL_CODE, CHARACTER_SET_CATALOG, CHARACTER_ SET_SCHEMA, and CHARACTER_SET_NAME that would be set by a <describe output statement> to reflect the description of the i-th <select list> column. Let SV be the value of the <select list> column, with data type SDT. Dynamic SQL 119  SOU-007 and X3H2-93-083 13.9 <using clause> Note: See the General Rules of Subclause 13.8, "<describe statement>". b) Let TDT be the effective data type of the i-th <target specification> as represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_ PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME, in the i-th item descriptor area. c) If the <cast specification> CAST ( SV AS TDT ) violates the Syntax Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised: dynamic SQL error- restricted data type attribute violation. d) If the <cast specification> CAST ( SV AS TDT ) violates the General Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised in accordance with the General Rules of Subclause 6.14, "<cast specification>". e) The <cast specification> CAST ( SV AS TDT ) is effectively performed, and is the value TV of the i-th <target specification>. f) If TV is a null value, then the value of INDICATOR is set to the negative number associated with that null value.. g) If TV is not a null value, then: i) The value of INDICATOR is set to 0 and the value of DATA is set to TV. ii) Case: 1) If TYPE indicates CHARACTER VARYING or BIT VARYING, then RETURNED_LENGTH is set to the length in characters or bits, respectively, of TV, and RETURNED_OCTET_LENGTH is set to the length in octets of TV. 2) If SDT is CHARACTER VARYING or BIT VARYING, then RETURNED_LENGTH is set to the length in characters or bits, respectively, of SV, and RETURNED_OCTET_LENGTH is set to the length in octets of SV. Note: All other values of the SQL descriptor area are unchanged. 120 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> 14)When a <using clause> is used as the <result using clause> of an <execute statement> that identifies a prepared statement that is not a <dynamic single row select statement>, the result is a set of <target specification> values corresponding to the output <dynamic parameter specification>s of the prepared statement. If <using descriptor> is specified, then the SQL descriptor area is set. The value of the i-th <target specification> is represented in the i-th item descriptor area as follows: a) Let SDT be the effective data type of the i-th output <dynamic parameter specification>, defined to be the type represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_PRECISION, DATETIME_INTERVAL_CODE, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_ SET_NAME that would be set by a <describe output statement> to reflect the output <dynamic parameter specification>. Let SV be the value of the output <dynamic parameter specification> with data type SDT. Note: See the General Rules of Subclause 13.8, "<describe statement>". b) Let TDT be the effective data type of the i-th <target specification> as represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_ PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME, in the i-th item descriptor area. c) If the <cast specification> CAST ( SV AS TDT ) violates the Syntax Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised: dynamic SQL error- restricted data type attribute violation. d) If the <cast specification> CAST ( SV AS TDT ) violates the General Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised in accordance with the General Rules of Subclause 6.14, "<cast specification>". e) The <cast specification> CAST ( SV AS TDT ) is effectively performed, and is the value TV of the i-th <target specification>. f) If TV is a null value, then the value of INDICATOR is set to the negative number associated with that null value.. Dynamic SQL 121  SOU-007 and X3H2-93-083 13.9 <using clause> g) If TV is not a null value, then: i) The value of INDICATOR is set to 0 and the value of DATA is set to TV. ii) Case: 1) If TYPE indicates CHARACTER VARYING or BIT VARYING, then RETURNED_LENGTH is set to the length in characters or bits, respectively, of TV, and RETURNED_OCTET_LENGTH is set to the length in octets of TV. 2) If SDT is CHARACTER VARYING or BIT VARYING, then RETURNED_LENGTH is set to the length in characters or bits, respectively, of SV, and RETURNED_OCTET_LENGTH is set to the length in octets of SV. Note: All other values of the SQL descriptor area are unchanged. 15)When a <using clause> is used as the <result using clause> of an <execute statement> and the prepared statement that is being executed is a <dynamic procedure>, the result is a set of output parameter values. If <using descriptor> is specified, then the SQL descriptor area is set. The value of the i-th output parameter is represented in the i-th item descriptor area as follows: a) Let SDT be the effective data type of the i-th output parameter, defined to be the type represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_ PRECISION, DATETIME_INTERVAL_CODE, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME that would be set by a <describe output statement> to reflect the description of the i-th output parameter. Let SV be the value of the output parameter, with data type SDT. Note: See the General Rules of Subclause 13.8, "<describe statement>". b) Let TDT be the effective data type of the output parameter as represented by the values of TYPE, LENGTH, PRECISION, SCALE, DATETIME_INTERVAL_CODE, DATETIME_INTERVAL_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_ SET_NAME, in the i-th item descriptor area. c) If the <cast specification> CAST ( SV AS TDT ) violates the Syntax Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised: dynamic SQL error-restricted data type attribute violation. d) If the <cast specification> CAST ( SV AS TDT ) 122 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.9 <using clause> violates the General Rules of Subclause 6.14, "<cast specification>", then an exception condition is raised in accordance with the Subclause 6.14, "<cast specification>". e) The <cast specification> CAST ( SV AS TDT ) is effectively performed, and is the value TV of the i-th output parameter. f) If TV is a null value, then the value of INDICATOR is set to the negative number associated with that null value. g) If TV is not a null value, then: i) The value of INDICATOR is set to 0 and the value of DATA is set to TV. ii) Case: 1) If TYPE indicates CHARACTER VARYING or BIT VARYING, then RETURNED_LENGTH is set to the length in characters or bits, respectively, of TV, and RETURNED_OCTET_LENGTH is set to the length in octets of TV. 2) If SDT is CHARACTER VARYING or BIT VARYING, then RETURNED_LENGTH is set to the length in characters or bits, respectively, of SV, and RETURNED_OCTET_LENGTH is set to the length in octets of SV. Note: All other values of the SQL descriptor area are unchanged. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) A <descriptor name> shall be a <literal>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 123  SOU-007 and X3H2-93-083 13.10 <execute statement> 13.10 <execute statement> Function Associate input parameters and output targets with a prepared statement and execute the statement. Format <execute statement> ::= EXECUTE <SQL statement name> [ <result using clause> ] [ <parameter using clause> ] <result using clause> ::= <using clause> <parameter using clause> ::= <using clause> Syntax Rules 1) If <SQL statement name> is a <statement name>, then the containing <module> shall contain a <prepare statement> whose <statement name> is the same as the <statement name> of the <execute statement>. 2) If <result using clause> is specified, then the <result using clause> shall contain either a <using arguments> or <using descriptor> that contains the keyword INTO. Access Rules None. General Rules 1) When the <execute statement> is executed, if the <SQL statement name> does not identify a statement P previously prepared in the scope of the <SQL statement name>, then an exception condition is raised: invalid SQL statement name. 2) If P is a <dynamic select statement> that does not conform to the Format and Syntax Rules of a <dynamic single row select statement>, then an exception condition is raised: dynamic SQL error-cursor_specification_cannot_be_executed.______________________ **Editor's Note** Inconsistent use of terms (e.g., <dynamic select statement> /<dynamic single row select statement> or <cursor specification> /<query specification>) has been identified as a Possible Problem. See_Possible_Problem_<155>_in_the_Editor's_Notes.___________________ | | | | | | 124 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.10 <execute statement> 3) If P contains the <table name> of a created or declared local temporary table and if the <execute statement> is not in the same <module> as the <prepare statement> that prepared the prepared statement, then an exception condition is raised: syntax rule or access rule violation in SQL dynamic statement. 4) If P contains input <dynamic parameter specification>s <ANSI> or P is a <dynamic procedure> that has input parameters, then if <ISO > and a <parameter using clause> is not specified, then an exception condition is raised: dynamic SQL error- using clause required for dynamic parameters. 5) If P is a <dynamic single row select statement> or it contains output <dynamic parameter specification>s <ANSI> or P is a <dynamic procedure> that has output parameters, then if <ISO > and and a <result using clause> is not specified, then an exception condition is raised: dynamic SQL error-using clause required for result fields. 6) If a <parameter using clause> is specified, then the General Rules specified in Subclause 13.9, "<using clause>", for a <parameter using clause> in an <execute statement> are applied. 7) If P is a <dynamic single row select statement>, then the General Rules specified in Subclause 13.9, "<using clause>", for a <result using clause> in an <execute statement> are applied. 8) P is executed. Case: a) If P is a <dynamic single row select statement>, then all General Rules in Subclause 14.5, "<select statement: single row>", apply to P, replacing "<query specification> S" with "the <query specification> contained in P". The <using argument>s contained in the <result using clause> or the item descriptor areas of the SQL descriptor referenced in the <result using clause>, if any, provide the <target specification>s corresponding to the <select list> of P. b) If the <preparable statement> is a <preparable dynamic delete statement: positioned>, then all General Rules in Subclause 13.19, "<preparable dynamic delete statement: positioned>", apply to the <preparable statement>. c) If the <preparable statement> is a <preparable dynamic update statement: positioned>, then all General Rules in Subclause 13.20, "<preparable dynamic update statement: positioned>", apply to the <preparable statement>. ______________________________________________________________ ANSI Only-SQL3 Dynamic SQL 125  SOU-007 and X3H2-93-083 13.10 <execute statement> ______________________________________________________________ d) If P is a <dynamic procedure>, then let VDECL be a list of <SQL variable declaration>s corresponding to the <parameter list> of the <routine> of the <dynamic procedure>, and let RB be the <routine body>. The execution of P is the same as the execution of the following: DECLARE VDECL BEGIN variables are set from their corresponding input parameters RB output parameters are set from their corresponding variables END ______________________________________________________________ e) Otherwise, results of the execution are the same as if the statement statement was contained in a <routine> and executed; these are described in Subclause 13.3, "<routine>". If P contains a <preparable dynamic delete statement: positioned>, then when it is executed all General Rules in Subclause 13.19, "<preparable dynamic delete statement: positioned>", apply to the <preparable statement>. If P contains a <preparable dynamic update statement: positioned>, then when it is executed all General Rules in Subclause 13.20, "<preparable dynamic update statement: positioned>", apply to the <preparable statement>. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall contain no <result using clause>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 126 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.11 <execute immediate statement> 13.11 <execute immediate statement> Function Dynamically prepare and execute a preparable statement. Format <execute immediate statement> ::= EXECUTE IMMEDIATE <SQL statement variable> Syntax Rules 1) The data type of <SQL statement variable> shall be character string. Access Rules None. General Rules 1) Let P be the contents of the <SQL statement variable>. 2) If one or more of the following are true, then an exception condition is raised: syntax error or access rule violation. a) P is a <dynamic select statement> or a <dynamic single row select statement>. b) P contains a <dynamic parameter specification>. 3) Let SV be <SQL statement variable>. <execute immediate statement> is equivalent to the following: PREPARE IMMEDIATE_STMT FROM SV ; EXECUTE IMMEDIATE_STMT ; DEALLOCATE PREPARE IMMEDIATE_STMT ; where "IMMEDIATE_STMT" is an implementation-defined <statement name> that is different that any other <statement name> in the containing <module>. Leveling Rules 1) The following restrictions apply for Full SQL: None. Dynamic SQL 127  SOU-007 and X3H2-93-083 13.11 <execute immediate statement> 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 128 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.12 <dynamic declare cursor> 13.12 <dynamic declare cursor> Function Declare a cursor to be associated with a <statement name>, which may in turn be associated with a <cursor specification>. Format <dynamic declare cursor> ::= DECLARE <cursor name> [ <cursor sensitivity> ] [ SCROLL ] CURSOR [ WITH HOLD ] FOR <statement name> Syntax Rules 1) The <cursor name> shall not be identical to the <cursor name> specified in any other <declare cursor> or <dynamic declare cursor> in the same <module>. 2) The containing <module> shall contain a <prepare statement> whose <statement name> is the same as the <statement name> of the <dynamic declare cursor>. Access Rules None. General Rules 1) All General Rules of Subclause 14.1, "<declare cursor>" apply to <dynamic declare cursor>, replacing "<open statement>" with "<dynamic open statement>" and "<cursor specification>" with "prepared statement". Leveling Rules 1) The following restrictions apply for Full SQL: a) A <dynamic declare cursor> shall not specify WITH HOLD. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall contain no <dynamic declare cursor> that specifies INSENSITIVE. b) If an <updatability clause> of FOR UPDATE with or without a <column name list> is specified, then neither SCROLL nor ORDER BY shall be specified. Dynamic SQL 129  SOU-007 and X3H2-93-083 13.12 <dynamic declare cursor> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 130 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.13 <allocate cursor statement> 13.13 <allocate cursor statement> Function Define a cursor based on a <prepare statement> for a <cursor specification>. Format <allocate cursor statement> ::= ALLOCATE <extended cursor name> [ <cursor sensitivity> ] [ SCROLL ] CURSOR [ WITH HOLD ] FOR <extended statement name> Syntax Rules None. Access Rules None. General Rules 1) When the <allocate cursor statement> is executed, if the value of the <extended statement name> does not identify a statement previously prepared in the scope of the <extended statement name>, then an exception condition is raised: invalid SQL statement name. 2) If the prepared statement associated with the <extended statement name> is not a <cursor specification>, then an exception condition is raised: dynamic SQL error-prepared statement not a cursor specification. 3) All General Rules of Subclause 14.1, "<declare cursor>" apply to <allocate cursor statement>, replacing "<open statement>" with "<dynamic open statement>" and "<cursor specification>" with "prepared statement". 4) Let S be the character string that is the value of the <simple value specification> immediately contained in <extended cursor name>. Let V be the character string that is the result of TRIM ( BOTH ' ' FROM S ) If V does not conform to the Format and Syntax Rules of an <identifier>, then an exception condition is raised: invalid cursor name. Dynamic SQL 131  SOU-007 and X3H2-93-083 13.13 <allocate cursor statement> 5) If the value of the <extended cursor name> is identical to the value of the <extended cursor name> of any other cursor allocated in the scope of the <extended cursor name>, then an exception condition is raised: invalid cursor name. 6) An association is made between the value of the <extended cursor name> and the prepared statement in the scope of the <extended cursor name>. The association is preserved until the prepared statement is destroyed, at which time the cursor identified by <extended cursor name> is also destroyed. Leveling Rules 1) The following restrictions apply for Full SQL: a) A <dynamic declare cursor> shall not specify WITH HOLD. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall not contain any <allocate cursor statement>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 132 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.14 <dynamic open statement> 13.14 <dynamic open statement> Function Associate input parameters with a <cursor specification> and open the cursor. Format <dynamic open statement> ::= OPEN <dynamic cursor name> [ <using clause> ] [ <open cascade option> ] Syntax Rules 1) If <dynamic cursor name> DCN is a <cursor name> CN, then the containing <module> shall contain a <dynamic declare cursor> whose <cursor name> is CN. 2) Let CR be the cursor identified by DCN. Access Rules 1) The Access Rules for the <query expression> simply contained in the prepared statement associated with the <dynamic cursor name> are applied. General Rules 1) If <dynamic cursor name> is a <cursor name> and the <statement name> of the associated <dynamic declare cursor> is not associated with a prepared statement, then an exception condition is raised: invalid SQL statement name. 2) If <dynamic cursor name> is an <extended cursor name> whose value does not identify a cursor allocated in the scope of the <extended cursor name>, then an exception condition is raised: invalid cursor name. 3) If the prepared statement associated with the <dynamic cursor name> contains <dynamic parameter specification>s and a <using clause> is not specified, then an exception condition is raised: dynamic SQL error-using clause required for dynamic parameters. 4) The cursor specified by <dynamic cursor name> is updatable if and only if the associated <cursor specification> specified an updatable cursor. Note: "updatable cursor" is defined in Subclause 14.1, "<declare cursor>". 5) If a <using clause> is specified, then the General Rules specified in Subclause 13.9, "<using clause>", for <dynamic open statement> are applied. Dynamic SQL 133  SOU-007 and X3H2-93-083 13.14 <dynamic open statement> 6) All General Rules of Subclause 14.2, "<open statement>", apply to the <dynamic open statement>. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 134 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.15 <dynamic fetch statement> 13.15 <dynamic fetch statement> Function Fetch a row for a cursor declared with a <dynamic declare cursor>. Format <dynamic fetch statement> ::= FETCH [ [ <fetch orientation> ] FROM ] <dynamic cursor name> <using clause> Syntax Rules 1) If <fetch orientation> is omitted, then NEXT is implicit. 2) The <using clause> shall specify INTO. 3) If <dynamic cursor name> DCN is a <cursor name> CN, then the containing <module> shall contain a <dynamic declare cursor> whose <cursor name> is CN. 4) Let CR be the cursor identified by DCN and let T be the table defined by the <cursor specification> of CR. 5) If the implicit or explicit <fetch orientation> is not NEXT, then the <dynamic declare cursor> or <allocate cursor statement> associated with CR shall specify SCROLL. 6) The number of <target specification>s in <using arguments> or the number of item descriptor areas in the SQL descriptor area referenced by <using descriptor>, as appropriate, shall be the same as the degree of T. The i-th <target specification> in <using arguments> or the i-th item descriptor area of the SQL descriptor area, as appropriate, corresponds with the i-th column of T. 7) The Syntax Rules of Subclause 9.1, "Retrieval assignment", apply to each corresponding <target specification> in <using arguments> and each column of T as TARGET and VALUE, respectively. Access Rules None. Dynamic SQL 135  SOU-007 and X3H2-93-083 13.15 <dynamic fetch statement> General Rules 1) The General Rules specified in Subclause 13.9, "<using clause>", for <dynamic fetch statement> are applied. 2) All General Rules of Subclause 14.3, "<fetch statement>", apply to the <dynamic fetch statement>, replacing "targets in the <fetch target list>" and "targets identified by the <fetch target list>" with "<target specification>s in the <using arguments> or item descriptor areas of the SQL descriptor area, as appropriate". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 136 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.16 <dynamic close statement> 13.16 <dynamic close statement> Function Close a cursor. Format <dynamic close statement> ::= CLOSE <dynamic cursor name> Syntax Rules 1) If <dynamic cursor name> DCN is a <cursor name> CN, then the containing <module> shall contain a <dynamic declare cursor> whose <cursor name> is CN. 2) Let CR be the cursor identified by DCN. Access Rules None. General Rules 1) All General Rules of Subclause 14.4, "<close statement>", apply to the <dynamic close statement>. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 137  SOU-007 and X3H2-93-083 13.17 <dynamic delete statement: positioned> 13.17 <dynamic delete statement: positioned> Function Delete a row of a table. Format <dynamic delete statement: positioned> ::= DELETE FROM <table name> WHERE CURRENT OF <dynamic cursor name> Syntax Rules 1) If <dynamic cusror name> DCN is a <cursor name> CN, then the containing <module> shall contain a <dynamic declare cursor> whose <cursor name> is CN. 2) Let CR be the cursor identified by DCN. 3) CR shall be an updatable cursor. Note: "updatable cursor" is defined in Subclause 14.1, "<declare cursor>". 4) Let T be the table identified by the <table name>. Let QS be the <query specification> that is the simply underlying table of the simply underlying table of CR. The simply underlying table of QS shall be T. Note: The "simply underlying table" of a <cursor specification> is defined in Subclause 14.1, "<declare cursor>". Access Rules 1) All Access Rules of Subclause 14.6, "<delete statement: positioned>", apply to the <dynamic delete statement: positioned>. General Rules 1) All General Rules of Subclause 14.6, "<delete statement: positioned>", apply to the <dynamic delete statement: positioned>, replacing "<delete statement: positioned>" with "<dynamic delete statement: positioned>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 138 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.17 <dynamic delete statement: positioned> 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 139  SOU-007 and X3H2-93-083 13.18 <dynamic update statement: positioned> 13.18 <dynamic update statement: positioned> Function Update a row of a table. Format <dynamic update statement: positioned> ::= UPDATE <table name> SET <set clause list> WHERE CURRENT OF <dynamic cursor name> Syntax Rules 1) If <dynamic cusror name> DCN is a <cursor name> CN, then the containing <module> shall contain a <dynamic declare cursor> whose <cursor name> is CN. 2) Let CR be the cursor identified by DCN. 3) CR shall be an updatable cursor. Note: "updatable cursor" is defined in Subclause 14.1, "<declare cursor>". 4) Let T be the table identified by the <table name>. Let QS be the <query specification> that is the simply underlying table of the simply underlying table of CR. The simply underlying table of QS shall be T. Note: The "simply underlying table" of a <cursor specification> is defined in Subclause 14.1, "<declare cursor>". ________________________________________________________________ ANSI Only-SQL3 ________________________________________________________________ 5) Each <column name> in the <object column list> shall identify a column_of_T.________________________________________________________ **Editor's Note** Paper X3H2-93-112R2.1/YOK-089.1, Proposal 1, item 24, noted that "There appear to be several Syntax Rules in Subclause 14.9, "<update statement: positioned>", that ought to have counterparts in_this_Subclause."_________________________________________________ | | |________________________________________________________________ | | | 6)|If CR is an ordered cursor, then for each <object column> | OC, the column of T identified by OC shall not be directly or indirectly referenced in the <order by clause> of the defining <cursor specification> for CR. 140 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.18 <dynamic update statement: positioned> 7) No leaf generally underlying table of T shall be an underlying table of any <query expression> generally contained in any <value expression> immediately contained in any <update source> contained in the <set clause list>. 8) If CR was specified using an explicit or implicit <updatability clause> of FOR UPDATE, then each <column name> specified as an <object column> shall identify a column in the explicit or implicit <column name list> associated with the <updatability clause>. 9) The scope of the <table name> is the entire <update statement: positioned>. Access Rules 1) All Access Rules of Subclause 14.9, "<update statement: positioned>", apply to the <dynamic update statement: positioned>. General Rules 1) All General Rules of Subclause 14.9, "<update statement: positioned>", apply to the <dynamic update statement: positioned>, replacing "<cursor name>" with "<dynamic cursor name>" and "<update statement: positioned>" with "<dynamic update statement: positioned>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall not contain any Dynamic SQL language. Dynamic SQL 141  SOU-007 and X3H2-93-083 13.19 <preparable dynamic delete statement: positioned> 13.19 <preparable dynamic delete statement: positioned> Function Delete a row of a table through a dynamic cursor. Format <preparable dynamic delete statement: positioned> ::= DELETE [ FROM <table name> ] WHERE CURRENT OF <cursor name> Syntax Rules 1) If <table name> is not specified, then the name of the underlying table of the <cursor specification> identified by <cursor name> is implicit. 2) All Syntax Rules of Subclause 14.6, "<delete statement: positioned>", apply to the <preparable dynamic delete statement: positioned>, replacing "<declare cursor>" with "<dynamic declare cursor> or <allocate cursor statement>" and "<delete statement: positioned>" with "<preparable dynamic delete statement: positioned>". Access Rules 1) All Access Rules of Subclause 14.6, "<delete statement: positioned>", apply to the <preparable dynamic delete statement: positioned>. General Rules 1) All General Rules of Subclause 14.6, "<delete statement: positioned>", apply to the <preparable dynamic delete statement: positioned>, replacing "<delete statement: positioned>" with "<preparable dynamic delete statement: positioned>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall contain no <preparable dynamic delete statement: positioned>. 142 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.19 <preparable dynamic delete statement: positioned> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. language. Dynamic SQL 143  SOU-007 and X3H2-93-083 13.20 <preparable dynamic update statement: positioned> 13.20 <preparable dynamic update statement: positioned> Function Update a row of a table through a dynamic cursor. Format <preparable dynamic update statement: positioned> ::= UPDATE [ <table name> ] SET <set clause list> WHERE CURRENT OF <cursor name> Syntax Rules 1) If <table name> is not specified, then the name of the underlying table of the <cursor specification> identified by <cursor name> is implicit. 2) All Syntax Rules of Subclause 14.9, "<update statement: positioned>", apply to the <preparable dynamic update statement: positioned>, replacing "<declare cursor>" with "<dynamic declare cursor> or <allocate cursor statement>" and "<update statement: positioned>" with "<preparable dynamic update statement: positioned>". Access Rules 1) All Access Rules of Subclause 14.9, "<update statement: positioned>", apply to the <preparable dynamic update statement: positioned>. General Rules 1) All General Rules of Subclause 14.9, "<update statement: positioned>", apply to the <preparable dynamic update statement: positioned>, replacing "<update statement: positioned>" with "<preparable dynamic update statement: positioned>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) Conforming Intermediate SQL language shall contain no <preparable dynamic update statement: positioned>. 144 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 13.20 <preparable dynamic update statement: positioned> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Dynamic SQL 145  SOU-007 and X3H2-93-083 146 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14 Embedded SQL 14.1 <embedded SQL host program> Function Specify an <embedded SQL host program>. Format <embedded SQL host program> ::= <embedded SQL Ada program> | <embedded SQL C program> | <embedded SQL COBOL program> | <embedded SQL Fortran program> | <embedded SQL MUMPS program> | <embedded SQL Pascal program> | <embedded SQL PL/I program> <embedded SQL statement> ::= <SQL prefix> <statement or declaration> [ <SQL terminator> ] <statement or declaration> ::= <declare cursor> | <dynamic declare cursor> | <temporary table declaration> | <temporary view declaration> | <embedded exception declaration> | DECLARE <SQL variable declaration> | <routine> | <SQL procedure statement> <SQL prefix> ::= EXEC SQL | <ampersand>SQL<left paren> <SQL terminator> ::= END-EXEC | <semicolon> | <right paren> <embedded SQL declare section> ::= <embedded SQL begin declare> [ <embedded character set declaration> ] [ <host variable definition>... ] <embedded SQL end declare> Embedded SQL 147  SOU-007 and X3H2-93-083 14.1 <embedded SQL host program> | <embedded SQL MUMPS declare> <embedded character set declaration> ::= SQL NAMES ARE <character set specification> <embedded SQL begin declare> ::= <SQL prefix> BEGIN DECLARE SECTION [ <SQL terminator> ] <embedded SQL end declare> ::= <SQL prefix> END DECLARE SECTION [ <SQL terminator> ] <embedded SQL MUMPS declare> ::= <SQL prefix> BEGIN DECLARE SECTION [ <embedded character set declaration> ] [ <host variable definition>... ] END DECLARE SECTION <SQL terminator> <host variable definition> ::= <Ada variable definition> | <C variable definition> | <COBOL variable definition> | <Fortran variable definition> | <MUMPS variable definition> | <Pascal variable definition> | <PL/I variable definition> <embedded variable name> ::= <colon><host identifier> <host identifier> ::= <Ada host identifier> | <C host identifier> | <COBOL host identifier> | <Fortran host identifier> | <MUMPS host identifier> | <Pascal host identifier> | <PL/I host identifier> Syntax Rules 1) An <embedded SQL host program> is a compilation unit that consists of programming language text and SQL text. The programming language text shall conform to the requirements of a specific standard programming language. The SQL text shall consist of one or more <embedded SQL statement>s and, optionally, one or more <embedded SQL declare section>s, as defined in this <ANSI> American <ISO > International Standard. 148 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.1 <embedded SQL host program> Note: "Compilation unit" is defined in Subclause 4.28, "Modules". 2) Case: a) An <embedded SQL statement> or <embedded SQL MUMPS declare> that is contained in an <embedded SQL MUMPS program> shall contain an <SQL prefix> that is "<ampersand>SQL<left paren>". There shall be no <separator> between the <ampersand> and "SQL" nor between "SQL" and the <left paren>. b) An <embedded SQL statement>, <embedded SQL begin declare>, or <embedded SQL end declare> that is not contained in an <embedded SQL MUMPS program> shall contain an <SQL prefix> that is "EXEC SQL". 3) Case: a) An <embedded SQL statement>, <embedded SQL begin declare>, or <embedded SQL end declare> contained in an <embedded SQL COBOL program> shall contain an <SQL terminator> that is END-EXEC. b) An <embedded SQL statement>, <embedded SQL begin declare>, or <embedded SQL end declare> contained in an <embedded SQL Fortran program> shall not contain an <SQL terminator>. c) An <embedded SQL statement>, <embedded SQL begin declare>, or <embedded SQL end declare> contained in an <embedded SQL Ada program>, <embedded SQL C program>, <embedded SQL Pascal program>, or <embedded SQL PL/I program> shall contain an <SQL terminator> that is a <semicolon>. d) An <embedded SQL statement> or <embedded SQL MUMPS declare> that is contained in an <embedded SQL MUMPS program> shall contain an <SQL terminator> that is a <right paren>. 4) Case: a) An <embedded SQL declare section> that is contained in an <embedded SQL MUMPS program> shall be an <embedded SQL MUMPS declare>. b) An <embedded SQL declare section> that is not contained in an <embedded SQL MUMPS program> shall not be an <embedded SQL MUMPS declare>. Note: There is no restriction on the number of <embedded SQL declare section>s that may be contained in an <embedded SQL host program>. 5) The <token>s comprising an <SQL prefix>, <embedded SQL begin declare>, or <embedded SQL end declare> shall be separated by <space> characters and shall be specified on one line. Otherwise, the rules for the continuation of lines and tokens from one line to the next and for the placement of host language Embedded SQL 149  SOU-007 and X3H2-93-083 14.1 <embedded SQL host program> comments are those of the programming language of the containing <embedded SQL host program>. 6) If an <embedded character set declaration> is not specified, then an <embedded character set declaration> that specifies an implementation-defined character set that contains at least every character that is in <SQL language character> is implicit. 7) A <temporary table declaration> or <temporary view declaration> that is contained in an <embedded SQL host program> shall precede in the text of that <embedded SQL host program> any SQL-statement or <declare cursor> that references the <table name> of the <temporary view declaration> or <temporary table declaration>. 8) A <declare cursor> that is contained in an <embedded SQL host program> shall precede in the text of that <embedded SQL host program> any SQL-statement that references the <cursor name> of the <declare cursor>. 9) A <dynamic declare cursor> that is contained in an <embedded SQL host program> shall precede in the text of that <embedded SQL host program> any SQL-statement that references the <cursor name> of the <dynamic declare cursor>. 10)An <SQL variable declaration> that is contained in an <embedded SQL host program> shall precede in the text of that <embedded SQL host program> any SQL-statement that references the <SQL variable name> of the <SQL variable declaration>. 11)An <SQL variable name> contained in an <SQL variable declaration> that is immediately contained in an <embedded SQL host program> shall be different from any other <SQL variable name> or <embedded variable name> contained in any other <SQL variable declaration> or <host variable definition>, respectively, that is immediately contained in the <embedded SQL host program>. 12)Any <host identifier> that is contained in an <embedded SQL statement> in an <embedded SQL host program> shall be defined in exactly one <host variable definition> contained in that <embedded SQL host program>. In programming languages that support <host variable definition>s in subprograms, two <host variable definition>s with different, non-overlapping scope in the host language are to be regarded as defining different host variables, even if they specify the same variable name. That <host variable definition> shall appear in the text of the <embedded SQL host program> prior to any <embedded SQL statement> that references the <host identifier>. The <host variable definition> shall be such that a host language reference to the <host identifier> is valid at every <embedded SQL statement> that contains the <host identifier>. 150 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.1 <embedded SQL host program> 13)A <host variable definition> defines the host language data type of the <host identifier>. For every such host language data type an equivalent SQL <data type> is specified in Subclause 14.3, "<embedded SQL Ada program>", Subclause 14.4, "<embedded SQL C program>", Subclause 14.5, "<embedded SQL COBOL program>", Subclause 14.6, "<embedded SQL Fortran program>", Subclause 14.7, "<embedded SQL MUMPS program>", Subclause 14.8, "<embedded SQL Pascal program>", and Subclause 14.9, "<embedded SQL PL/I program>". 14)If one or more <host variable definition>s that specify SQLSTATE or SQLCODE appear in an <embedded SQL host program>, then the <host variable definition>s shall be such that a host language reference to SQLSTATE or SQLCODE, respectively, is valid at every <embedded SQL statement>, including <embedded SQL statement>s that appear in any subprograms contained in that <embedded SQL host program>. No <embedded SQL statement> shall precede any of its applicable status code definitions in the text of the main program or subprograms that comprise the <embedded SQL host program>. 15)Given an <embedded SQL host program> H, there is an implied standard-conforming <ANSI> SQL-client <ISO > SQL <module> M and an implied standard-conforming host program P derived from H. The derivation of the implied program P and the implied <module> M of an <embedded SQL host program> H effectively precedes the processing of any host language program text manipulation commands such as inclusion or copying of text. Given an <embedded SQL host program> H with an implied <module> M and an implied program P defined as above: a) The implied <module> M of H shall be a standard-conforming SQL <module>. b) If H is an <embedded SQL Ada program>, an <embedded SQL C program>, an <embedded SQL COBOL program>, an <embedded SQL Fortran program>, an <embedded SQL MUMPS program>, an <embedded SQL Pascal program>, or an <embedded SQL PL/I program>, then the implied program P shall be a standard- conforming Ada program, a standard-conforming C program, a standard-conforming COBOL program, a standard-conforming Fortran program, a standard-conforming MUMPS program, a standard-conforming Pascal program, or standard-conforming PL/I program, respectively. 16)M is derived from H as follows: a) M contains a <module name clause> whose <module name> is either implementation-dependent or is omitted. Embedded SQL 151  SOU-007 and X3H2-93-083 14.1 <embedded SQL host program> b) M contains a <module character set specification> that is identical to the explicit or implicit <embedded character set declaration> with the keyword "SQL" removed. c) M contains a <language clause> that specifies either ADA, C, COBOL, FORTRAN, MUMPS, PASCAL, or PLI, where H is respectively an <embedded SQL Ada program>, an <embedded SQL C program>, an <embedded SQL COBOL program>, an <embedded SQL Fortran program>, an <embedded SQL MUMPS program>, an <embedded SQL Pascal program>, or an <embedded SQL PL/I program>. d) M contains a <module authorization clause> that specifies SCHEMA <schema name>, where the value of <schema name> is implementation-dependent. e) M contains a <module path specification> whose <schema name list> is implementation-dependent. f) For every <declare cursor> EC contained in H, M contains one <declare cursor> PC and one <routine> PS that contains an <open statement> that references PC. The <routine header> of PS specifies "PROCEDURE". The <procedure name> of PS is implementation-dependent. PC is a copy of EC in which each distinct <embedded variable name> has been replaced with a distinct implementation-dependent <host parameter name>. PS contains a <parameter declaration> for each <host parameter name> contained in PC. The <parameter declaration> that corresponds to a given <embedded variable name> V that is contained in EC specifies the <host parameter name> with which V was replaced, and the SQL data type that corresponds to the host language data type of V as specified in Subclause 13.5, "Data type correspondences". If H contains a <host variable definition> that specifies SQLSTATE or SQLCODE, then PS contains a <parameter declaration> that specifies SQLSTATE or SQLCODE, respectively. If H contains neither a <host variable definition> that specifies SQLSTATE nor a <host variable definition> that specifies SQLCODE, then PS contains a <parameter declaration> that specifies SQLCODE. The order of <parameter declaration>s in PS is implementation- dependent.__________________________________________________________ **Editor's Note** Paper X3H2-93-066/YOK-035, raised the issue of whether the order of declarations (including cursor declarations) in the <embedded SQL host program> needs to be preserved in the resulting <module>. See _Possible_Problem_<290>_in_the_Editor's_Notes.______________________ | | g| For every <dynamic declare cursor> EC in H, M contains one | |<dynamic declare cursor> PC that is a copy of EC. | | | | | 152 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.1 <embedded SQL host program> h) M contains one <temporary table declaration> for each <temporary table declaration> contained in H. Each <temporary table declaration> of M is a copy of the corresponding <temporary table declaration> of H. i) M contains one <temporary view declaration> for each <temporary view declaration> contained in H. Each <temporary view declaration> of M is a copy of the corresponding <temporary view declaration> of H. j) M contains one <SQL variable declaration> for each <SQL variable declaration> contained in H. Each <SQL variable declaration> of M is a copy of the corresponding <SQL variable declaration> of H. k) M contains a <routine> for each <SQL procedure statement> contained in H. The <routine> PS of M corresponding with an <SQL procedure statement> ES of H is defined as follows: Case: i) If ES is not an <open statement>, then: 1) The <routine header> of PS specifies "PROCEDURE". 2) The <procedure name> of PS is implementation-dependent. 3) The <SQL procedure statement> of PS is a copy of ES in which each distinct <embedded variable name> has been replaced with the same distinct implementation-dependent <host parameter name>. 4) PS contains a <parameter declaration> for each distinct <host parameter name> contained in the <SQL procedure statement> of PS. 5) The <parameter declaration> corresponding to a given <embedded variable name> V that is contained in ES specifies the <host parameter name> with which V was replaced and the SQL <data type> that corresponds to the host language data type of V. 6) Whether one <routine> of M can correspond to more than one <SQL procedure statement> of H is implementation- dependent. 7) If H contains a <host variable definition> that specifies SQLSTATE or SQLCODE, then PS contains a <parameter declaration> that specifies SQLSTATE or SQLCODE, respectively. If H contains neither a <host variable definition> that specifies SQLSTATE nor a <host variable definition> that specifies SQLCODE, then PS contains a <parameter declaration> that specifies SQLCODE. Embedded SQL 153  SOU-007 and X3H2-93-083 14.1 <embedded SQL host program> 8) The order of the <parameter declaration>s is implementation-dependent. ii) If ES is an <open statement>, then: 1) Let EC be the <declare cursor> in H referenced by ES. 2) PS is the <routine> in M that contains an <open statement> that references the <declare cursor> in M corresponding to EC. l) M contains one <external function declaration> for each <external function declaration> contained in H. Each <external function declaration> of M is a copy of the corresponding <external function declaration> of H. 17)P is derived from H as follows: a) Each <embedded SQL begin declare>, <embedded SQL end declare>, and <embedded character set declaration> has been deleted. If the embedded host language is MUMPS, then each <embedded SQL MUMPS declare> has been deleted. b) Each <host variable definition> in an <embedded SQL declare section> has been replaced by a valid data definition in the target host language according to the Syntax Rules specified in an <embedded SQL Ada program>, <embedded SQL C program>, <embedded SQL COBOL program>, <embedded SQL Fortran program>, <embedded SQL Pascal program>, or an <embedded SQL PL/I program> clause. c) Each <embedded SQL statement> that contains a <declare cursor>, a <dynamic declare cursor>, an <SQL variable declaration>, an <external function declaration>, a <temporary view declaration>, or a <temporary table declaration> has been deleted, and every <embedded SQL statement> that contains an <embedded exception declaration> has been replaced with statements of the host language that will have the effect specified by the General Rules of Subclause 14.2, "<embedded exception declaration>". d) Each <embedded SQL statement> that contains an <SQL procedure statement> has been replaced by host language statements that perform the following actions: i) A host language procedure or subroutine call of the <routine> of the implied <module> M of H that corresponds with the <SQL procedure statement>. If the <SQL procedure statement> is not an <open statement>, then the arguments of the call include each distinct <host identifier> contained in the <SQL procedure statement> together with the SQLCODE <host identifier>, or the SQLSTATE <host identifier>, or both. If H contains neither a <host variable definition> that specifies 154 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.1 <embedded SQL host program> SQLSTATE nor a <host variable definition> that specifies SQLCODE, then the arguments of the call include SQLCODE. If the <SQL procedure statement> is an <open statement>, then the arguments of the call include each distinct <host identifier> contained in the corresponding <declare cursor> of H together with the SQLCODE <host identifier>, or the SQLSTATE <host identifier>, or both. If H contains neither a <host variable definition> that specifies SQLSTATE nor a <host variable definition> that specifies SQLCODE, then the arguments of the call include SQLCODE. The order of the arguments in the call corresponds with the order of the corresponding <parameter declaration>s in the corresponding <routine>. Note: In an <embedded SQL Fortran program>, the "SQLCODE" variable is abbreviated to "SQLCOD" and the "SQLSTATE" variable may be abbreviated to "SQLSTA". See the Syntax Rules of Subclause 14.6, "<embedded SQL Fortran program>". ii) Exception actions, as specified in Subclause 14.2, _____"<embedded_exception_declaration>".____________________________ **Editor's Note** The preceding Rule (nor any other place in this document) does not describe how those constructs that are not standard-conforming host language constructs are to be reconstructed to be conforming. This has have been identified as a Possible Problem. See Possible Problems_<093>_and_<124>_in_the_Editor's_Notes._____________________ | | No|e: SQLSTATE is the preferred status parameter. The SQLCODE | st|tus parameter is a deprecated feature that is supported for | co|patibility with earlier versions of this | <A|SI> American | <I|O > International | Standard. See Appendix D, "Deprecated features". Access Rules None. General Rules 1) The interpretation of an <embedded SQL host program> H is defined to be equivalent to the interpretation of the implied program P of H and the implied <module> M of H. 2) If the cursor mode of the current SQL-transaction is cascade off, and the <embedded SQL statement> is other than a <close statement>, a <delete statement: positioned>, a <fetch statement>, an <open statement>, an <update statement: positioned>, a <dynamic close statement>, a <dynamic delete statement: positioned>, a <dynamic fetch statement>, a <dynamic open statement>, or a <dynamic update statement: positioned>, then an exception condition is raised: invalid SQL statement. Embedded SQL 155  SOU-007 and X3H2-93-083 14.1 <embedded SQL host program> Leveling Rules 1) The following restrictions apply for Full SQL: a) A <statement or declaration> shall not specify a <temporary view declaration>, an <SQL variable declaration>, or a <routine> that specifies an external routine. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) An <embedded SQL declare section> shall not contain an <embedded character set declaration>. b) No two <host variable definition>s shall specify the same variable name. 156 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.2 <embedded exception declaration> 14.2 <embedded exception declaration> Function Specify the action to be taken when an SQL-statement causes a specific class of condition to be raised. Format <embedded exception declaration> ::= WHENEVER <condition> <condition action> <condition> ::= | <old condition> | <SQL condition> <old condition> ::= SQLERROR <SQL condition> ::= <major category> | SQLSTATE ( <SQLSTATE class value> [ , <SQLSTATE subclass value> ] ) | CONSTRAINT <constraint name> <major category> ::= SQLEXCEPTION | SQLWARNING | NOT FOUND <SQLSTATE class value> ::= <SQLSTATE char><SQLSTATE char> !! See the Syntax Rules. <SQLSTATE subclass value> ::= <SQLSTATE char><SQLSTATE char><SQLSTATE char> !! See the Syntax Rules. <SQLSTATE char> ::= <simple Latin upper case letter> | <digit> <condition action> ::= CONTINUE | <go to> <go to> ::= { GOTO | GO TO } <goto target> <goto target> ::= <host label identifier> | <unsigned integer> | <host PL/I label variable> <host label identifier> ::= !! See the Syntax Rules. <host PL/I label variable> ::= !! See the Syntax Rules. Embedded SQL 157  SOU-007 and X3H2-93-083 14.2 <embedded exception declaration> Syntax Rules 1) If an <embedded exception declaration> that specifies <old condition> is contained in the <embedded SQL host program>, then no <embedded exception declaration>s that specifies <SQL condition> shall exist in the same <embedded SQL host program>. 2) SQLWARNING, NOT FOUND, and SQLEXCEPTION correspond to SQLSTATE class values corresponding to categories W, N, and X in Table 27, "SQLSTATE class and subclass values", respectively. 3) An <embedded exception declaration> contained in an <embedded SQL host program> applies to an <SQL procedure statement> contained in that <embedded SQL host program> if and only if the <SQL procedure statement> appears after the <embedded exception declaration> that has condition C in the text sequence of the <embedded SQL host program> and no other <embedded exception declaration> E that satisfies one of the following conditions appears between the <embedded exception declaration> and the <SQL procedure statement> in the text sequence of the <embedded SQL host program>. Let D be the <condition> contained in E. Case: a) D is the same as C. b) D is a <major catagory> and belongs to the same class to which C belongs. c) D contains an <SQLSTATE class value>, but does not contain an <SQLstate subclass value>, and E contains the same <SQLstate class value> that C contains. d) D contains the <SQLSTATE class value> that corresponds to integrity constraint violation and C contains CONSTRAINT. 4) In the values of <SQLSTATE class value> and <SQLSTATE subclass value>, there shall be no <separator> between the <SQLSTATE char>s. 5) The values of <SQLSTATE class value> and <SQLSTATE subclass value> shall correspond to class values and subclass values, respectively, specified in Table 27, "SQLSTATE class and subclass values". 6) If an <embedded exception declaration> specifies a <go to>, then the <host label identifier>, <host PL/I label variable>, or <unsigned integer> of the <go to> shall be such that a host language GO TO statement specifying that <host label identifier>, <host PL/I label variable>, or <unsigned integer> is valid at every <SQL procedure statement> to which the <embedded exception declaration> applies. 158 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.2 <embedded exception declaration> Note: 1) If an <embedded exception declaration> is contained in an <embedded SQL Ada program>, then the <goto target> of a <go to> should specify a <host label identifier> that is a label_ name in the containing <embedded SQL Ada program>. 2) If an <embedded exception declaration> is contained in an <embedded SQL C program>, then the <goto target> of a <go to> should specify a <host label identifier> that is a label in the containing <embedded SQL C program>. 3) If an <embedded exception declaration> is contained in an <embedded SQL COBOL program>, then the <goto target> of a <go to> should specify a <host label identifier> that is a section-name or an unqualified paragraph-name in the containing <embedded SQL COBOL program>. 4) If an <embedded exception declaration> is contained in an <embedded SQL Fortran program>, then the <goto target> of a <go to> should be an <unsigned integer> that is the statement label of an executable statement that appears in the same program unit as the <go to>. 5) If an <embedded exception declaration> is contained in an <embedded SQL MUMPS program>, then the <goto target> of a <go to> should be a gotoargument_ that is the statement label of an executable statement that appears in the same <embedded SQL MUMPS program>. 6) If an <embedded exception declaration> is contained in an <embedded SQL Pascal program>, then the <goto target> of a <go to> should be an <unsigned integer> that is a label. 7) If an <embedded exception declaration> is contained in an <embedded SQL PL/I program>, then the <goto target> of a <go to> should specify either a <host label identifier> or a <host PL/I label variable>. Case: a) If <host label identifier> is specified, then the <host label identifier> should be a label constant in the containing <embedded SQL PL/I program>. b) If <host PL/I label variable> is specified, then the <host PL/I label variable> should be a PL/I label variable declared in the containing <embedded SQL PL/I program>. Access Rules None. Embedded SQL 159  SOU-007 and X3H2-93-083 14.2 <embedded exception declaration> General Rules 1) Immediately after the execution of an <SQL procedure statement> STMT in an <embedded SQL host program> that returns an SQLSTATE value other than successful completion: a) Let E be the set of <embedded exception declaration>s that are contained in the <embedded SQL host program> containing STMT, that applies to STMT, and that specifies an <condition action> that is <go to>. b) Let CV and SCV be respectively the values of the class and subclass of the SQLSTATE value that indicates the result of the <SQL procedure statement>. c) If the execution of the <SQL procedure statement> caused the violation of one or more constraints or assertions, then: i) Let ECN be the set of <embedded exception declaration>s in E that specify CONSTRAINT and the <constraint name> of a constraint that was violated by execution of STMT. ii) If ECN contains more than one <embedded exception declaration>, then an implementation-dependent <embedded exception declaration> is chosen from ECN; otherwise, the single <embedded exception declaration> in ECN is chosen. iii) A GO TO statement of the host language is performed, specifying the <host label identifier>, <host PL/I label variable>, or <unsigned integer> of the <go to> specified in the <embedded exception declaration> chosen from ECN. d) Otherwise: i) Let ECS be the set of <embedded exception declaration>s in E that specify SQLSTATE, an <SQLSTATE class value>, and an <SQLSTATE subclass value>. ii) If ECS contains an <embedded exception declaration> EY that specifies an <SQLSTATE class value> identical to CV and an <SQLSTATE subclass value> identical to SCV, then a GO TO statement of the host language is performed, specifying the <host label identifier>, <host PL/I label variable>, or <unsigned integer> of the <go to> specified in the <embedded exception declaration> EY. iii) Otherwise: 1) Let EC be the set of <embedded exception declaration>s in E that specify SQLSTATE and an <SQLSTATE class value> without an <SQLSTATE subclass value>. 160 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.2 <embedded exception declaration> 2) If EC contains an <embedded exception declaration> EY that specifies an <SQLSTATE class value> identical to CV, then a GO TO statement of the host language is performed, specifying the <host label identifier>, <host PL/I label variable>, or <unsigned integer> of the <go to> specified in the <embedded exception declaration> EY. 3) Otherwise: A) Let EE be the set of <embedded exception declaration>s in E that specify SQLERROR. B) If EE contains an <embedded exception declaration> EY and CV belongs to Category W or Category X in Table 27, "SQLSTATE class and subclass values", then a GO TO statement of the host language is performed, specifying the <host label identifier>, <host PL/I label variable>, or <unsigned integer> of the <go to> specified in the <embedded exception declaration> EY. C) Otherwise: I) Let EX be the set of <embedded exception declaration>s in E that specify SQLEXCEPTION. II) If EX contains an <embedded exception declaration> EY and CV belongs to Category X in Table 27, "SQLSTATE class and subclass values", then a GO TO statement of the host language is performed, specifying the <host label identifier>, <host PL/I label variable>, or <unsigned integer> of the <go to> specified in the <embedded exception declaration> EY. III) Otherwise: 1) Let EW be the set of <embedded exception declaration>s in E that specify SQLWARNING. 2) If EW contains an <embedded exception declaration> EY and CV belongs to Category W in Table 27, "SQLSTATE class and subclass values", then a GO TO statement of the host language is performed, specifying the <host label identifier>, <host PL/I label variable>, or <unsigned integer> of the <go to> specified in the <embedded exception declaration> EY. 3) Otherwise, let ENF be the set of <embedded exception declaration>s in E that specify NOT FOUND. If ENF contains an <embedded exception declaration> EY and CV belongs to Category N in Table 27, "SQLSTATE class and subclass values", then a GO TO statement of the host Embedded SQL 161  SOU-007 and X3H2-93-083 14.2 <embedded exception declaration> language is performed, specifying the <host label identifier>, <host PL/I label variable>, or <unsigned integer> of the <go to> specified in the <embedded exception declaration> EY. Leveling Rules 1) The following restrictions apply for Full SQL: a) A <SQL condition> shall not specify SQLSTATE or CONSTRAINT. b) A <major category> shall not specify SQLEXCEPTION or SQLWARNING. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. 162 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.3 <embedded SQL Ada program> 14.3 <embedded SQL Ada program> Function Specify an <embedded SQL Ada program>. Format <embedded SQL Ada program> ::= !! See the Syntax Rules. <Ada variable definition> ::= <Ada host identifier> [ { <comma> <Ada host identifier> }... ] : <Ada type specification> [ <Ada initial value> ] <Ada initial value> ::= <Ada assignment operator> <character representation>... <Ada assignment operator> ::= <colon><equals operator> <Ada host identifier> ::= !! See the Syntax Rules. <Ada type specification> ::= <Ada qualified type specification> | <Ada unqualified type specification> <Ada qualified type specification> ::= SQL_STANDARD.CHAR [ CHARACTER SET [ IS ] <character set specification> ] <left paren> 1 <double period> <length> <right paren> | SQL_STANDARD.BIT <left paren> 1 <double period> <length> <right paren> | SQL_STANDARD.SMALLINT | SQL_STANDARD.INT | SQL_STANDARD.REAL | SQL_STANDARD.DOUBLE_PRECISION | SQL_STANDARD.BOOLEAN | SQL_STANDARD.SQLCODE_TYPE | SQL_STANDARD.SQLSTATE_TYPE | SQL_STANDARD.INDICATOR_TYPE <Ada unqualified type specification> ::= CHAR <left paren> 1 <double period> <length> <right paren> | BIT <left paren> 1 <double period> <length> <right paren> | SMALLINT | INT | REAL | DOUBLE_PRECISION | BOOLEAN | SQLCODE_TYPE | SQLSTATE_TYPE | INDICATOR_TYPE Embedded SQL 163  SOU-007 and X3H2-93-083 14.3 <embedded SQL Ada program> Syntax Rules 1) An <embedded SQL Ada program> is a compilation unit that consists of Ada text and SQL text. The Ada text shall conform to the Ada standard <ANSI> ANSI/MIL-STD-1815A. <ISO > ISO/IEC 8652. The SQL text shall consist of one or more <embedded SQL statement>s and, optionally, one or more <embedded SQL declare section>s. 2) An <embedded SQL statement> may be specified wherever an Ada statement may be specified. An <embedded SQL statement> may be prefixed by an Ada label. 3) An <Ada host identifier> is any valid Ada identifier. An <Ada host identifier> shall be contained in an <embedded SQL Ada program>. 4) An <Ada variable definition> defines one or more host variables. 5) An <Ada variable definition> shall be modified as follows before it is placed into the program derived from the <embedded SQL Ada program> (see the Syntax Rules of Subclause 14.1, "<embedded SQL host program>"): a) Any optional CHARACTER SET specification shall be removed from an <Ada qualified type specification>. b) The <length> specified in a CHAR declaration of any <Ada qualified type specification> that contains a CHARACTER SET specification shall be replaced by a length equal to the length in octets of PN, where PN is the <Ada host identifier> specified in the containing <Ada variable definition>. The modified <Ada variable definition> shall be a valid Ada object-declaration in the program derived from the <embedded SQL Ada program>. 6) An <Ada variable definition> shall be specified within the scope of Ada with and use clauses that specify the following: with SQL_STANDARD; use SQL_STANDARD; use SQL_STANDARD.CHARACTER_SET; 7) The <character representation> sequence in an <Ada initial value> specifies an initial value to be assigned to the Ada variable. It shall be a valid Ada specification of an initial value. 164 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.3 <embedded SQL Ada program> 8) CHAR describes a character string variable whose equivalent SQL data type is CHARACTER with the same length and character set specified by <character set specification>. If <character set specification> is not specified, then an implementation-defined <character set specification> is implicit. 9) BIT describes a bit string variable. The equivalent SQL data type is BIT with the same length. 10)INT and SMALLINT describe exact numeric variables. The equivalent SQL data types are INTEGER and SMALLINT, respectively. 11)REAL and DOUBLE_PRECISION describe approximate numeric variables. The equivalent SQL data types are REAL and DOUBLE PRECISION, respectively. 12)BOOLEAN describes a boolean variable. The equivalent SQL data type is BOOLEAN. 13)SQLCODE_TYPE describes an exact numeric variable whose precision is the implementation-defined precision defined for the SQLCODE parameter. SQLSTATE_TYPE describes a character string variable whose length is the length of the SQLSTATE parameter, five characters. 14)INDICATOR_TYPE describes an exact numeric variable whose specific data type is any <exact numeric type> with a scale of 0. 15)If an <embedded SQL Ada program> contains neither an <Ada variable definition> that specifies SQLSTATE and that is defined with an <Ada type specification> that specifies SQL_ STANDARD.CHAR or CHAR with <length> 5, nor an <Ada variable definition> that specifies SQLCODE and that is defined with an <Ada type specification> that specifies SQL_STANDARD.INTEGER or INTEGER, then it is assumed that the <embedded SQL Ada program> contains a variable named SQLCODE defined with a data type of INTEGER. Note: SQLSTATE is the preferred status parameter. The SQLCODE status parameter is a deprecated feature that is supported for compatibility with earlier versions of this <ANSI> American <ISO > International Standard. See Appendix D, "Deprecated features". Access Rules None. General Rules 1) See Subclause 14.1, "<embedded SQL host program>". Embedded SQL 165  SOU-007 and X3H2-93-083 14.3 <embedded SQL Ada program> Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) An <Ada variable definition> shall not specify a bit string variable. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) An <Ada qualified type specification> shall not contain a <character set specification>. 166 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.4 <embedded SQL C program> 14.4 <embedded SQL C program> Function Specify an <embedded SQL C program>. Format <embedded SQL C program> ::= !! See the Syntax Rules. <C variable definition> ::= [ <C storage class> ] [ <C class modifier> ] <C variable specification> <semicolon> <C variable specification> ::= <C numeric variable> | <C character variable> | <C derived variable> <C storage class> ::= auto | extern | static <C class modifier> ::= const | volatile <C numeric variable> ::= { long | short | float | double } <C host identifier> [ <C initial value> ] [ { <comma> <C host identifier> [ <C initial value> ] }... ] <C character variable> ::= char [ CHARACTER SET [ IS ] <character set specification> ] <C host identifier> <C array specification> [ <C initial value> ] [ { <comma> <C host identifier> <C array specification> [ <C initial value> ] }... ] <C array specification> ::= <left bracket> <length> <right bracket> <C host identifier> ::= !! See the Syntax Rules. <C derived variable> ::= <C VARCHAR variable> | <C bit variable> <C VARCHAR variable> ::= VARCHAR [ CHARACTER SET [ IS ] <character set specification> ] Embedded SQL 167  SOU-007 and X3H2-93-083 14.4 <embedded SQL C program> <C host identifier> <C array specification> [ { <comma> <C host identifier> <C array specification> }... ] <C bit variable> ::= BIT <C host identifier> <C array specification> [ <C initial value> ] [ { <comma> <C host identifier> <C array specification> [ <C initial value> ] }... ] <C initial value> ::= <equals operator> <character representation>... Syntax Rules 1) An <embedded SQL C program> is a compilation unit that consists of C text and SQL text. The C text shall conform to to the C standard <ANSI> ANSI X3.159. <ISO > ISO/IEC 9899. The SQL text shall consist of one or more <embedded SQL statement>s and, optionally, one or more <embedded SQL declare section>s. 2) An <embedded SQL statement> may be specified wherever a C statement may be specified within a function block. If the C statement could include a label prefix, then the <embedded SQL statement> may be immediately preceded by a label prefix. 3) A <C host identifier> is any valid C variable identifier. A <C host identifier> shall be contained in an <embedded SQL C program>. 4) A <C variable definition> defines one or more host variables. 5) A <C variable definition> shall be modified as follows before it is placed into the program derived from the <embedded SQL C program> (see the Syntax Rules of Subclause 14.1, "<embedded SQL host program>"): a) Any optional CHARACTER SET specification shall be removed from a <C VARCHAR variable> or a <C character variable>. b) The syntax "VARCHAR" shall be replaced by "char" in any <C VARCHAR variable>. c) The syntax "BIT" shall be replaced by "char" in any <C bit variable>. d) The <length> specified in a <C array specification> in any <C bit variable> shall be replaced by a length equal to the smallest integer not less than L/B, as defined in the Syntax Rules of this Subclause. 168 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.4 <embedded SQL C program> e) The <length> specified in a <C array specification> in any <C character variable> or in any <C VARCHAR variable> that contained a CHARACTER SET specification shall be replaced by a length equal to the length in octets of PN, where PN is the <C host identifier> specified in the containing <C variable definition>. The modified <C variable definition> shall be a valid C data declaration in the program derived from the <embedded SQL C program>. 6) The <character representation> sequence contained in a <C initial value> specifies an initial value to be assigned to the C variable. It shall be a valid C specification of an initial value. 7) Except for array specifications for character strings and bit strings, a <C variable definition> shall specify a scalar type. 8) In a <C variable definition>, the words "VARCHAR", "CHARACTER", "SET", "IS", "BIT", and "VARYING" may be specified in any combination of upper case and lower case letters (see the Syntax Rules of Subclause 5.2, "<token> and <separator>"). 9) In a <C character variable> or a <C VARCHAR variable>, if a <character set specification> is specified, then the equivalent SQL data type is CHARACTER or CHARACTER VARYING whose character repertoire is the same as the repertoire specified by the <character set specification>. If <character set specification> is not specified, then an implementation-defined <character set specification> is implicit. 10)Each <C host identifier> specified in a <C character variable> describes a fixed-length character string. The length is specified by the <length> of the <C array specification>. The value in the host variable is terminated by a null character and the position occupied by this null character is included in the length of the host variable. The equivalent SQL data type is CHARACTER whose length is one less than the <length> of the <C array specification> and whose value does not include the terminating null character. The <length> shall be greater than 1. 11)Each <C host identifier> specified in a <C VARCHAR variable> describes a variable-length character string. The maximum length is specified by the <length> of the <C array specification>. The value in the host variable is terminated by a null character and the position occupied by this null character is included in the maximum length of the host variable. The equivalent SQL data type is CHARACTER VARYING whose maximum length is 1 less than the <length> of the <C array specification> and whose value does not include the terminating null character. The <length> shall be greater than 1. Embedded SQL 169  SOU-007 and X3H2-93-083 14.4 <embedded SQL C program> 12)Each <C host identifier> specified in a <C bit variable> describes a fixed-length bit string. The value in the host variable has a BIT_LENGTH of <length>. Let B be the number of bits in a C char and let L be the <length> of the <C array specification>. The length of an equivalent C char variable is the smallest integer that is not less than the result of L/B. The equivalent SQL data type is BIT whose length is L. 13)"long" describes an exact numeric variable. The equivalent SQL data type is INTEGER or BOOLEAN. 14)"short" describes an exact numeric variable. The equivalent SQL data type is SMALLINT. 15)"float" describes an approximate numeric variable. The equivalent SQL data type is REAL. 16)"double" describes an approximate numeric variable. The equivalent SQL data type is DOUBLE PRECISION. 17)If an <embedded SQL C program> contains neither a <C variable definition> that specifies SQLSTATE and that is defined with a <C character variable> that specifies "char" with a <C array specification> that is 6, nor a <C variable definition> that specifies SQLCODE and that is defined with a <C numeric variable> that specifies "long", then it is assumed that the <embedded SQL C program> contains a variable named SQLCODE defined with a data type of "long". Note: SQLSTATE is the preferred status parameter. The SQLCODE status parameter is a deprecated feature that is supported for compatibility with earlier versions of this <ANSI> American <ISO > International Standard. See Appendix D, "Deprecated features". Access Rules None. General Rules 1) See Subclause 14.1, "<embedded SQL host program>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) A <C derived variable> shall not be a <C bit variable>. 170 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.4 <embedded SQL C program> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A <C derived variable> shall not be a <C VARCHAR variable>. b) A <C variable definition> shall not contain a <character set specification>. Embedded SQL 171  SOU-007 and X3H2-93-083 14.5 <embedded SQL COBOL program> 14.5 <embedded SQL COBOL program> Function Specify an <embedded SQL COBOL program>. Format <embedded SQL COBOL program> ::= !! See the Syntax Rules. <COBOL variable definition> ::= {01|77} <COBOL host identifier> <COBOL type specification> [ <character representation>... ] <period> <COBOL host identifier> ::= !! See the Syntax Rules. <COBOL type specification> ::= <COBOL character type> | <COBOL bit type> | <COBOL numeric type> | <COBOL integer type> <COBOL character type> ::= [ CHARACTER SET [ IS ] <character set specification> ] { PIC | PICTURE } [ IS ] { X [ <left paren> <length> <right paren> ] }... <COBOL bit type> ::= { PIC | PICTURE } [ IS ] { X [ <left paren> <length> <right paren> ] }... USAGE [ IS ] BIT <COBOL numeric type> ::= { PIC | PICTURE } [ IS ] S <COBOL nines specification> [ USAGE [ IS ] ] DISPLAY SIGN LEADING SEPARATE <COBOL nines specification> ::= <COBOL nines> [ V [ <COBOL nines> ] ] | V <COBOL nines> <COBOL integer type> ::= <COBOL computational integer> | <COBOL binary integer> <COBOL computational integer> ::= { PIC | PICTURE } [ IS ] S<COBOL nines> [ USAGE [ IS ] ] { COMP | COMPUTATIONAL } ] <COBOL binary integer> ::= { PIC | PICTURE } [ IS ] S<COBOL nines> [ USAGE [ IS ] ] BINARY 172 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.5 <embedded SQL COBOL program> <COBOL nines> ::= { 9 [ <left paren> <length> <right paren> ] }... Syntax Rules 1) An <embedded SQL COBOL program> is a compilation unit that consists of COBOL text and SQL text. The COBOL text shall conform to to the COBOL standard <ANSI> ANSI X3.23. <ISO > ISO 1989. The SQL text shall consist of one or more <embedded SQL statement>s and, optionally, one or more <embedded SQL declare section>s. 2) An <embedded SQL statement> in an <embedded SQL COBOL program> may be specified wherever a COBOL statement may be specified in the Procedure Division of the <embedded SQL COBOL program>. If the COBOL statement could be immediately preceded by a paragraph-name, then the <embedded SQL statement> may be immediately preceded by a paragraph-name. 3) A <COBOL host identifier> is any valid COBOL data-name. A <COBOL host identifier> shall be contained in an <embedded SQL COBOL program>. 4) A <COBOL variable definition> is a restricted form of COBOL data description entry that defines a host variable. 5) A <COBOL variable definition> shall be modified as follows before it is placed into the program derived from the <embedded SQL COBOL program> (see the Syntax Rules of Subclause 14.1, "<embedded SQL host program>". a) Any optional CHARACTER SET specification shall be removed from a <COBOL character type>. b) The syntax "USAGE IS BIT" shall be deleted. c) The <length> specified in any <COBOL bit type> shall be replaced by a length equal to the smallest integer not less than L/B, as defined in the Syntax Rules of this Subclause. d) The <length> specified in any <COBOL character type> that contained a CHARACTER SET specification shall be replaced by a length equal to the length in octets of PN, where PN is the <COBOL host identifier> specified in the containing <COBOL variable definition>. The modified <COBOL variable definition> shall be a valid data description entry in the Data Division of the program derived from the <embedded SQL COBOL program>. Embedded SQL 173  SOU-007 and X3H2-93-083 14.5 <embedded SQL COBOL program> 6) The optional <character representation> sequence in a <COBOL variable definition> may specify a VALUE clause. Whether other clauses may be specified is implementation-defined. The <character representation> sequence shall be such that the <COBOL variable definition> is a valid COBOL data description entry. 7) A <COBOL character type> describes a character string variable whose equivalent SQL data type is CHARACTER with the same length and character set specified by <character set specification>. If <character set specification> is not specified, then an implementation-defined <character set specification> is implicit. 8) A <COBOL bit type> describes a bit string variable. Let B be the number of bits in a COBOL character and let L be the <length> of the <COBOL bit type>. The length of an equivalent COBOL character variable is the smallest integer not less than L/B. The equivalent SQL data type is BIT whose length is the <length> of the <COBOL bit type>. If the length of the <COBOL bit type> is 1, then the equivalent SQL data type may be BOOLEAN. 9) A <COBOL numeric type> describes an exact numeric variable. The equivalent SQL data type is NUMERIC of the same precision and scale. 10)A <COBOL computational integer> describes an exact numeric variable. Note: This <COBOL type specification> is supported only for SQLCODE for compatibility with earlier versions of this <ANSI> American <ISO > International Standard. The SQLCODE status parameter is a deprecated feature that is supported for compatibility with earlier versions of this <ANSI> American <ISO > International Standard. See Appendix D, "Deprecated features". 11)A <COBOL binary integer> describes an exact numeric variable. The equivalent SQL data type is SMALLINT or INTEGER. 12)If an <embedded SQL COBOL program> contains neither a <COBOL variable definition> that specifies SQLSTATE and that is defined with a <COBOL character type> that specifies {01|77} SQLSTATE PICTURE X(5) nor a <COBOL variable definition> that specifies SQLCODE and that is defined with a <COBOL type specification> that specifies {01|77} SQLCODE PICTURE S9(PC) USAGE COMP 174 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.5 <embedded SQL COBOL program> where PC is the implementation-defined precision specified for a COBOL SQLCODE parameter in Subclause 10.3, "<routine invocation>", then it is assumed <embedded SQL COBOL program> contains a variable named SQLCODE defined with a data type of {01|77} SQLCODE PICTURE S9(PC) USAGE COMP where PC is the implementation-defined precision specified for a COBOL SQLCODE parameter in Subclause 10.3, "<routine invocation>". Note: SQLSTATE is the preferred status parameter. The SQLCODE status parameter is a deprecated feature that is supported for compatibility with earlier versions of this <ANSI> American <ISO > International Standard. See Appendix D, "Deprecated features". Access Rules None. General Rules 1) See Subclause 14.1, "<embedded SQL host program>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) A <COBOL type specification> shall not be a <COBOL bit type>. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A <COBOL character type> shall not contain a <character set specification>. Embedded SQL 175  SOU-007 and X3H2-93-083 14.6 <embedded SQL Fortran program> 14.6 <embedded SQL Fortran program> Function Specify an <embedded SQL Fortran program>. Format <embedded SQL Fortran program> ::= !! See the Syntax Rules. <Fortran variable definition> ::= <Fortran type specification> <Fortran host identifier> [ { <comma> <Fortran host identifier> }... ] <Fortran host identifier> ::= !! See the Syntax Rules. <Fortran type specification> ::= CHARACTER [ <asterisk> <length> ] [ CHARACTER SET [ IS ] <character set specification> ] | BIT [ <asterisk> <length> ] | INTEGER | REAL | DOUBLE PRECISION | LOGICAL Syntax Rules 1) An <embedded SQL Fortran program> is a compilation unit that consists of Fortran text and SQL text. The Fortran text shall conform to to the Fortran <ANSI> standards ANSI X3.9 and ANSI X3.198. <ISO > standard ISO 1539. The SQL text shall consist of one or more <embedded SQL statement>s and, optionally, one or more <embedded SQL declare section>s. 2) An <embedded SQL statement> may be specified wherever an executable Fortran statement may be specified. An <embedded SQL statement> that precedes any executable Fortran statement in the containing <embedded SQL Fortran program> shall not have a Fortran statement number. Otherwise, if the Fortran statement could have a statement number then the <embedded SQL statement> can have a statement number. 3) Blanks are significant in <embedded SQL statement>s. The rules for <separator>s in an <embedded SQL statement> are as specified in Subclause 5.2, "<token> and <separator>". 4) A <Fortran host identifier> is any valid Fortran variable name with all <space> characters removed. A <Fortran host identifier> shall be contained in an <embedded SQL Fortran program>. 176 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.6 <embedded SQL Fortran program> 5) A <Fortran variable definition> is a restricted form of Fortran type-statement that defines one or more host variables. 6) A <Fortran variable definition> shall be modified as follows before it is placed into the program derived from the <embedded SQL Fortran program> (see the Syntax Rules Subclause 14.1, "<embedded SQL host program>". a) Any optional CHARACTER SET specification shall be removed from the CHARACTER alternative in a <Fortran type specification>. b) The <length> specified in the CHARACTER alternative of any <Fortran type specification> that contained a CHARACTER SET specification shall be replaced by a length equal to the length in octets of PN, where PN is the <Fortran host identifier> specified in the containing <Fortran variable definition>. c) The syntax "BIT" shall be replaced by "CHARACTER" in any BIT alternative of a <Fortran type specification>. d) The <length> specified in any BIT alternative of a <Fortran type specification> shall be replaced by a length equal to the smallest integer not less than L/B, as defined in the Syntax Rules of this Subclause. The modified <Fortran variable definition> shall be a valid Fortran type-statement in the program derived from the <embedded SQL Fortran program>. 7) CHARACTER describes a character string variable whose equivalent SQL data type is CHARACTER with the same length and character set specified by <character set specification>. If <character set specification> is not specified, then an implementation- defined <character set specification> is implicit. 8) BIT describes a bit string variable. Let B be the number of bits in a Fortran character and let L be the <length> of the bit string variable. The length of an equivalent Fortran character variable is the smallest integer not less than L/B. The equivalent SQL data type is BIT whose length is the <length> of the bit string variable. 9) INTEGER describes an exact numeric variable. The equivalent SQL data type is INTEGER. 10)REAL describes an approximate numeric variable. The equivalent SQL data type is REAL. 11)DOUBLE PRECISION describes an approximate numeric variable. The equivalent SQL data type is DOUBLE PRECISION. 12)LOGICAL describes a boolean variable. The equivalent SQL data type is BOOLEAN. Embedded SQL 177  SOU-007 and X3H2-93-083 14.6 <embedded SQL Fortran program> 13)If an <embedded SQL Fortran program> contains neither a <Fortran variable definition> that specifies SQLSTATE or SQLSTA and that is defined with a <Fortran type specification> that specifies CHARACTER with <length> 5, nor a <Fortran variable definition> that specifies SQLCOD and that is defined with a <Fortran type specification> that specifies INTEGER, then it is assumed that the <embedded SQL Fortran program> contains a variable named SQLCOD defined with a data type of INTEGER. Note: SQLSTATE (which may be abbreviated "SQLSTA") is the preferred status parameter. The SQLCODE (SQLCOD) status parameter is a deprecated feature that is supported for compatibility with earlier versions of this <ANSI> American <ISO > International Standard. See Appendix D, "Deprecated features". Access Rules None. General Rules 1) See Subclause 14.1, "<embedded SQL host program>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) A <Fortran type specification> shall not specify BIT. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A <Fortran type specification> shall not contain a <character set specification>. 178 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.7 <embedded SQL MUMPS program> 14.7 <embedded SQL MUMPS program> Function Specify an <embedded SQL MUMPS program>. Format <embedded SQL MUMPS program> ::= !! See the Syntax Rules. <MUMPS variable definition> ::= { <MUMPS numeric variable> | <MUMPS character variable> } <semicolon> <MUMPS character variable> ::= VARCHAR <MUMPS host identifier> <MUMPS length specification> [ { <comma> <MUMPS host identifier> <MUMPS length specification> }... ] <MUMPS host identifier> ::= !! See the Syntax Rules. <MUMPS length specification> ::= <left paren> <length> <right paren> <MUMPS numeric variable> ::= <MUMPS type specification> <MUMPS host identifier> [ { <comma> <MUMPS host identifier> }... ] <MUMPS type specification> ::= INT | DEC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ] | REAL Syntax Rules 1) An <embedded SQL MUMPS program> is a compilation unit that consists of MUMPS text and SQL text. The MUMPS text shall conform to to the MUMPS standard <ANSI> ANSI/MDC X11.1. <ISO > ISO/IEC 11756. The SQL text shall consist of one or more <embedded SQL statement>s and, optionally, one or more <embedded SQL declare section>s. 2) A <MUMPS host identifier> is any valid MUMPS variable name. A <MUMPS host identifier> shall be contained in an <embedded SQL MUMPS program>. 3) An <embedded SQL statement> may be specified wherever a MUMPS command may be specified. Embedded SQL 179  SOU-007 and X3H2-93-083 14.7 <embedded SQL MUMPS program> 4) A <MUMPS variable definition> defines one or more host variables. 5) The <MUMPS character variable> describes a variable-length character string. The equivalent SQL data type is CHARACTER VARYING whose maximum length is the <length> of the <MUMPS length specification> and whose character set is implementation- defined. 6) INT describes an exact numeric variable. The equivalent SQL data type is INTEGER. 7) DEC describes an exact numeric variable. The <scale> shall not be greater than the <precision>. The equivalent SQL data type is DECIMAL with the same <precision> and <scale>. 8) REAL describes an approximate numeric variable. The equivalent SQL data type is REAL. 9) If an <embedded SQL MUMPS program> contains neither a <MUMPS variable definition> that specifies SQLSTATE and that is defined with a <MUMPS character variable > that specifies VARCHAR with a <MUMPS length specification> that specifies 5, nor a <MUMPS variable definition> that specifies SQLCODE and that is defined with a <MUMPS numeric variable> that specifies INT, then it is assumed that the <embedded SQL MUMPS program> contains a variable named SQLCODE defined with a data type of INT. Note: SQLSTATE is the preferred status parameter. The SQLCODE status parameter is a deprecated feature that is supported for compatibility with earlier versions of this <ANSI> American <ISO > International Standard. See Appendix D, "Deprecated features". Access Rules None. General Rules 1) See Subclause 14.1, "<embedded SQL host program>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 180 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.7 <embedded SQL MUMPS program> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) Conforming Entry SQL language shall contain no <embedded SQL MUMPS program>. Embedded SQL 181  SOU-007 and X3H2-93-083 14.8 <embedded SQL Pascal program> 14.8 <embedded SQL Pascal program> Function Specify an <embedded SQL Pascal program>. Format <embedded SQL Pascal program> ::= !! See the Syntax Rules. <Pascal variable definition> ::= <Pascal host identifier> [ { <comma> <Pascal host identifier> }... ] <colon> <Pascal type specification> <semicolon> <Pascal host identifier> ::= !! See the Syntax Rules. <Pascal type specification> ::= PACKED ARRAY <left bracket> 1 <double period> <length> <right bracket> OF CHAR [ CHARACTER SET [ IS ] <character set specification> ] | PACKED ARRAY <left bracket> 1 <double period> <length> <right bracket> OF BIT | INTEGER | REAL | CHAR [ CHARACTER SET [ IS ] <character set specification> ] | BIT | BOOLEAN Syntax Rules 1) An <embedded SQL Pascal program> is a compilation unit that consists of Pascal text and SQL text. The Pascal text shall conform to to one of the the Pascal standards <ANSI> ANSI/IEEE 770/X3.97 and ANSI/IEEE 770/X3.160. <ISO > ISO 7185 and ISO/IEC 10206. The SQL text shall consist of one or more <embedded SQL statement>s and, optionally, one or more <embedded SQL declare section>s. 2) An <embedded SQL statement> may be specified wherever a Pascal statement may be specified. An <embedded SQL statement> may be prefixed by a Pascal label. 3) A <Pascal host identifier> is a Pascal variable-identifier whose applied instance denotes a defining instance within an <embedded SQL begin declare> and an <embedded SQL end declare>. 182 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.8 <embedded SQL Pascal program> 4) A <Pascal variable definition> defines one or more <Pascal host identifier>s. 5) A <Pascal variable definition> shall be modified as follows before it is placed into the program derived from the <embedded SQL Pascal program> (see the Syntax Rules of Subclause 14.1, "<embedded SQL host program>"). a) Any optional CHARACTER SET specification shall be removed from the PACKED ARRAY OF CHAR or CHAR alternatives of a <Pascal type specification>. b) The <length> specified in the PACKED ARRAY OF CHAR alternative of any <Pascal type specification> that contained a CHARACTER SET specification shall be replaced by a length equal to the length in octets of PN, where PN is the <Pascal host identifier> specified in the containing <Pascal variable definition>. c) If any <Pascal type sepcification> specifies the syntax "CHAR" and contains a CHARACTER SET specification, then let L be a length equal to the length in octets of PN and PN be the <Pascal host identifier> specified in the containing <Pascal variable definition>. If L is greater than 1, then "CHAR" shall be replaced by "PACKED ARRAY [1..L] OF CHAR". d) The syntax "BIT" shall be replaced by "CHAR" in any PACKED ARRAY OF BIT or BIT alternatives of a <Pascal type specification>. e) The <length> specified in any PACKED ARRAY OF BIT alternative in a <Pascal type specification> shall be replaced by a length equal to the smallest integer not less than L/B, as defined in the Syntax Rules of this Subclause. The modified <Pascal variable definition> shall be a valid Pascal variable-declaration in the program derived from the <embedded SQL Pascal program>. 6) CHAR specified without a CHARACTER SET specification is the ordinal-type-identifier of PASCAL. The equivalent SQL data type is CHARACTER with length 1. 7) BIT describes a single-bit variable. It is mapped to a Pascal CHAR ordinal-type-identifier whose most significant bit contains either 0 or 1 and whose least significant bits contain 0. The equivalent SQL data type is BIT with length 1. 8) PACKED ARRAY [1..<length>] OF CHAR describes a character string having 2 or more components of the simple type CHAR. The equivalent SQL data type is CHARACTER with the same length and character set specified by <character set specification>. If <character set specification> is not specified, then an implementation-defined <character set specification> is implicit. Embedded SQL 183  SOU-007 and X3H2-93-083 14.8 <embedded SQL Pascal program> 9) PACKED ARRAY [1..<length>] OF BIT describes a bit string variable. Let B be the number of bits in a Pascal CHAR and let L be the <length> of the bit string variable. The length of an equivalent Pascal character variable is the smallest integer not less than L/B. The equivalent SQL data type is BIT whose length is the <length> of the bit string variable. 10)INTEGER describes an exact numeric variable. The equivalent SQL data type is INTEGER. 11)REAL describes an approximate numeric variable. The equivalent SQL data type is REAL. 12)BOOLEAN describes a boolean variable. The equivalent SQL data type is BOOLEAN. 13)If an <embedded SQL Pascal program> contains neither a <Pascal variable definition> that specifies SQLSTATE and that is defined with a <Pascal type specification> that specifies PACKED ARRAY [1..<length>] OF CHAR with <length> 5, nor a <Pascal variable definition> that specifies SQLCODE and that is defined with a <Pascal type specification> that specifies INTEGER, then it is assumed that the <embedded SQL Pascal program> contains a variable named SQLCODE defined with a data type of INTEGER. Note: SQLSTATE is the preferred status parameter. The SQLCODE status parameter is a deprecated feature that is supported for compatibility with earlier versions of this <ANSI> American <ISO > International Standard. See Appendix D, "Deprecated features". Access Rules None. General Rules 1) See Subclause 14.1, "<embedded SQL host program>". Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) A <Pascal type specification> shall not specify BIT or PACKED ARRAY [1..<length>] OF BIT. 184 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.8 <embedded SQL Pascal program> 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A <Pascal type specification> shall not contain a <character set specification>. Embedded SQL 185  SOU-007 and X3H2-93-083 14.9 <embedded SQL PL/I program> 14.9 <embedded SQL PL/I program> Function Specify an <embedded SQL PL/I program>. Format <embedded SQL PL/I program> ::= !! See the Syntax Rules. <PL/I variable definition> ::= {DCL | DECLARE} { <PL/I host identifier> | <left paren> <PL/I host identifier> [ { <comma> <PL/I host identifier> }... ] <right paren> } <PL/I type specification> [ <character representation>... ] <semicolon> <PL/I host identifier> ::= !! See the Syntax Rules. <PL/I type specification> ::= { CHAR | CHARACTER } [ VARYING ] <left paren><length><right paren> [ CHARACTER SET [ IS ] <character set specification> ] | BIT [ VARYING ] <left paren><length><right paren> | <PL/I type fixed decimal> <left paren> <precision> [ <comma> <scale> ] <right paren> | <PL/I type fixed binary> [ <left paren> <precision> <right paren> ] | <PL/I type float binary> <left paren> <precision> <right paren> <PL/I type fixed decimal> ::= { DEC | DECIMAL } FIXED | FIXED { DEC | DECIMAL } <PL/I type fixed binary> ::= { BIN | BINARY } FIXED | FIXED { BIN | BINARY } <PL/I type float binary> ::= { BIN | BINARY } FLOAT | FLOAT { BIN | BINARY } 186 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.9 <embedded SQL PL/I program> Syntax Rules 1) An <embedded SQL PL/I program> is a compilation unit that consists of PL/I text and SQL text. The PL/I text shall conform to to the PL/I standard <ANSI> ANSI X3.53. <ISO > ISO 6160. The SQL text shall consist of one or more <embedded SQL statement>s and, optionally, one or more <embedded SQL declare section>s. 2) An <embedded SQL statement> may be specified wherever a PL/I statement may be specified within a procedure block. If the PL/I statement could include a label prefix, the <embedded SQL statement> may be immediately preceded by a label prefix. 3) A <PL/I host identifier> is any valid PL/I variable identifier. A <PL/I host identifier> shall be contained in an <embedded SQL PL/I program>. 4) A <PL/I variable definition> defines one or more host variables. 5) A <PL/I variable definition> shall be modified as follows before it is placed into the program derived from the <embedded SQL PL/I program> (see the Syntax Rules of Subclause 14.1, "<embedded SQL host program>"). a) Any optional CHARACTER SET specification shall be removed from the CHARACTER or CHARACTER VARYING alternatives of a <PL/I type specification>. b) The <length> specified in the CHARACTER or CHARACTER VARYING alternatives of any <PL/I type specification> that contains a CHARACTER SET specification shall be replaced by a length equal to the length in octets of PN, where PN is the <PL/I host identifier> specified in the containing <PL/I variable definition>. The modified <PL/I variable definition> shall be a valid PL/I data declaration in the program derived from the <embedded SQL PL/I program>. 6) A <PL/I variable definition> shall specify a scalar variable, not an array or structure. 7) The optional <character representation> sequence in a <PL/I variable definition> may specify an INITIAL clause. Whether other clauses may be specified is implementation-defined. The <character representation> sequence shall be such that the <PL/I variable definition> is a valid PL/I DECLARE statement. Embedded SQL 187  SOU-007 and X3H2-93-083 14.9 <embedded SQL PL/I program> 8) CHARACTER describes a character string variable whose equivalent SQL data type has the character set specified by <character set specification>. If <character set specification> is not specified, then an implementation-defined <character set specification> is implicit. Case: a) If VARYING is not specified, then the length of the variable is fixed. The equivalent SQL data type is CHARACTER with the same length. b) If VARYING is specified, then the variable is of variable length, with maximum size the value of <length>. The equivalent SQL data type is CHARACTER VARYING with the same maximum length. 9) BIT describes a bit string variable. Case: a) If VARYING is not specified, then the length of the variable is fixed. The equivalent SQL data type is BIT with the same length. length. If the length of the variable is 1, then the equivalent SQL data type may be BOOLEAN. b) If VARYING is specified, then the variable is of variable length with maximum size of the value of <length>. The equivalent SQL data type is BIT VARYING with the same maximum length. 10)FIXED DECIMAL describes an exact numeric variable. The <scale> shall not be greater than the <precision>. The equivalent SQL data type is DECIMAL with the same <precision> and <scale>. 11)FIXED BINARY describes an exact numeric variable. The equivalent SQL data type is SMALLINT or INTEGER. 12)FLOAT BINARY describes an approximate numeric variable. The equivalent SQL data type is FLOAT with the same <precision>. 13)If an <embedded SQL PL/I program> contains neither a <PL/I variable definition> that specifies SQLSTATE and that is defined with a <PL/I type specification> that specifies CHAR or CHARACTER with <length> 5 and does not specify VARYING, nor a <PL/I variable definition> that specifies SQLCODE and that is defined with a <PL/I type specification> that specifies FIXED BIN(PP) or FIXED BINARY(PP), where PP is the implementation- defined precision specified for a PL/I SQLCODE parameter in Subclause 10.3, "<routine invocation>", then it is assumed that the <embedded SQL PL/I program> contains a variable named SQLCODE defined with a data type of FIXED BINARY(PP), where PP is the implementation-defined precision specified for a PL/I SQLCODE parameter in Subclause 10.3, "<routine invocation>". 188 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 14.9 <embedded SQL PL/I program> Note: SQLSTATE is the preferred status parameter. The SQLCODE status parameter is a deprecated feature that is supported for compatibility with earlier versions of this <ANSI> American <ISO > International Standard. See Appendix D, "Deprecated features". Access Rules None. General Rules 1) See Subclause 14.1, "<embedded SQL host program>" Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: a) A <PL/I type specification> shall not specify BIT or BIT VARYING. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A <PL/I type specification> shall not specify CHARACTER VARYING. b) A <PL/I type specification> shall not contain a <character set specification>. <character set specification>. Embedded SQL 189  SOU-007 and X3H2-93-083 190 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 15 Direct invocation of SQL 15.1 <direct SQL statement> Function Specify direct execution of SQL. Format <direct SQL statement> ::= <directly executable statement> <semicolon> <directly executable statement> ::= <direct SQL data statement> | <SQL schema statement> | <SQL transaction statement> | <SQL connection statement> | <SQL session statement> | <direct implementation-defined statement> <direct SQL data statement> ::= <delete statement: searched> | <direct select statement: multiple rows> | <insert statement> | <update statement: searched> | <temporary table declaration> | <temporary view declaration> <direct implementation-defined statement> ::= !! See the Syntax Rules Syntax Rules 1) The <direct SQL data statement> shall not contain any parameter reference, SQL variable reference, <dynamic parameter specification>, or <embedded variable specification>. 2) The <value specification> that represents the null value is implementation-defined. 3) The Format and Syntax Rules for <direct implementation-defined statement> are implementation-defined. Direct invocation of SQL 191  SOU-007 and X3H2-93-083 15.1 <direct SQL statement> Access Rules 1) The Access Rules for <direct implementation-defined statement> are implementation-defined. General Rules 1) The following <direct SQL statement>s are transaction-initiating <direct SQL statement>s: a) <direct SQL statement>s that are transaction-initiating <SQL procedure statement>s; b) <direct select statement: multiple rows>; and c) <direct implementation-defined statement>s that are transaction-initiating. 2) After the last invocation of an SQL-statement by an SQL-agent in an SQL-session: a) A <rollback statement> or a <commit statement> is effectively executed. If an unrecoverable error has occurred, or if the direct invocation of SQL terminated unexpectedly, or if any constraint is not satisfied, then a <rollback statement> is performed. Otherwise, the choice of which of these SQL- statements to perform is implementation-dependent. The determination of whether a direct invocation of SQL has terminated unexpectedly is implementation-dependent. b) Let D be the <descriptor name> of any system descriptor area that is currently allocated within the current SQL-session. A <deallocate descriptor statement> that specifies DEALLOCATE DESCRIPTOR D is effectively executed. c) All SQL-sessions associated with the SQL-agent are terminated. 3) Let S be the <direct SQL statement>. 4) The current <authorization identifier> for privilege determination for the execution of S is the SQL-session <authorization identifier>. 5) If S does not conform to the Format, Syntax Rules, and Access Rules for a <direct SQL statement>, then an exception condition is raised: syntax error or access rule violation. 6) When S is invoked by the SQL-agent: 192 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 15.1 <direct SQL statement> Case: a) If S is an <SQL connection statement>, then: i) The diagnostics area is emptied. ii) S is executed. iii) If S successfully initiated or resumed an SQL-session, then subsequent invocations of a <direct SQL statement> by the SQL-agent are associated with that SQL-session until the SQL-agent terminates the SQL-session or makes it dormant. b) Otherwise: i) If no SQL-session is current for the SQL-agent, then Case: 1) If the SQL-agent has not executed an <SQL connection statement> and there is no default SQL-session associated with the SQL-agent, then the following <connect statement> is effectively executed: CONNECT TO DEFAULT 2) If the SQL-agent has not executed an <SQL connection statement> and there is a default SQL-session associated with the SQL-agent, then the following <set connection statement> is effectively executed: SET CONNECTION DEFAULT 3) Otherwise, an exception condition is raised: connection exception-connection does not exist. Subsequent calls to a <routine> or invocations of a <direct SQL statement> by the SQL-agent are associated with the SQL-session until the SQL-agent terminates the SQL-session or makes it dormant. ii) If an SQL-transaction is active for the SQL-agent, then S is associated with that SQL-transaction. If S is a <direct implementation-defined statement>, then it is implementation-defined whether or not S may be associated with an active SQL-transaction; if not, then an exception condition is raised: invalid transaction state-active SQL- transaction. iii) If no SQL-transaction is active for the SQL-agent, then 1) Case: A) If S is a transaction-initiating <direct SQL statement>, then an SQL-transaction is initiated. Direct invocation of SQL 193  SOU-007 and X3H2-93-083 15.1 <direct SQL statement> B) If S is a <direct implementation-defined statement>, then it is implementation-defined whether or not S initiates an SQL-transaction. If an implementation defines S to be transaction-initiating, then an SQL- transaction is initiated. 2) If S initiated an SQL-transaction, then: A) Let T be the SQL-transaction initiated by S. B) T is associated with this invocation and any subsequent invocations of <direct SQL statement>s or calls to a <routine> by the SQL-agent until the SQL-agent terminates T. C) If S is not a <start transaction statement>, then Case: I) If a <set transaction statement> has been executed since the termination of the last SQL-transaction in the SQL-session (or if there has been no previous SQL-transaction in the SQL-session and a <set transaction statement> has been executed), then the access mode, constraint mode, and isolation level of T are set as specified by the <set transaction statement>. II) Otherwise, the access mode, constraint mode for all constraints, and isolation level for T are read- write, immediate, and SERIALIZABLE, respectively. D) T is associated with the SQL-session. iv) If S contains an <SQL schema statement> and the access mode of the current SQL-transaction is read-only, then an exception condition is raised: invalid transaction state- read-only SQL-transaction. v) The diagnostics area is emptied. vi) S is executed. 7) If the execution of a <direct SQL data statement> occurs within the same SQL-transaction as the execution of an SQL-schema statement and this is not allowed by the SQL-implementation, then an exception condition is raised: invalid transaction state- schema and data statement mixing not supported. 8) Case: a) If S executed successfully, then either a completion condition is raised: successful completion, or a completion condition is raised: warning, or a completion condition is raised: no data. 194 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 15.1 <direct SQL statement> b) If S did not execute successfully, then all changes made to SQL-data or schemas by the execution of S are canceled and an exception condition is raised. Note: The method of raising a condition is implementation- defined. 9) Diagnostics information resulting from the execution of S is placed into the diagnostics area as specified in Clause 18, "Diagnostics management". Note: The method of accessing the diagnostics information is implementation-defined, but does not alter the contents of the diagnostics area. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) A <direct SQL statement> shall not be an <SQL schema statement>. Direct invocation of SQL 195  SOU-007 and X3H2-93-083 15.2 <direct select statement: multiple rows> 15.2 <direct select statement: multiple rows> Function Specify a statement to retrieve multiple rows from a specified table. Format <direct select statement: multiple rows> ::= <query expression> [ <order by clause> ] Syntax Rules 1) All Syntax Rules of Subclause 7.13, "<query expression>", apply to the <direct select statement: multiple rows>. 2) The <query expression> or <order by clause> of a <direct select statement: multiple rows> shall not contain any <value specification> other than a <literal>, CURRENT_USER, SESSION_ USER, SYSTEM_USER, or CURRENT_PATH. 3) Let T be the table specified by the <query expression>. 4) If ORDER BY is specified, then each <sort specification> in the <order by clause> shall identify a column of T. Case: a) If a <sort specification> contains a <column name>, then T shall contain exactly one column with that <column name> and the <sort specification> identifies that column. b) If a <sort specification> contains an <unsigned integer>, then the <unsigned integer> shall be greater than 0 and not greater than the degree of T. The <sort specification> identifies the column of T with the ordinal position specified by the <unsigned integer>. Access Rules None. General Rules 1) All General Rules of Subclause 7.13, "<query expression>", apply to the <direct select statement: multiple rows>. 2) Let Q be the result of the <query expression>. 3) If Q is empty, then a completion condition is raised: no data. 4) If an <order by clause> is not specified, then the ordering of the rows of Q is implementation-dependent. 196 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 15.2 <direct select statement: multiple rows> 5) If an <order by clause> is specified, then the ordering of rows of the result is effectively determined by the <order by clause> as follows: a) Each <sort specification> specifies the sort direction for the corresponding sort key Ki. If ASC is specified or implied in the i-th <sort specification>, then the sort direction for Ki is ascending and the applicable <comp op> is the <less than operator>. Otherwise, the sort direction for Ki is descending and the applicable <comp op> is the <greater than operator>. b) Let X and Y be distinct rows in the result table, and let Xi and YVi be the values of Ki in these rows, respectively. The relative position of rows X and Y in the result is determined by comparing XVi and YVi according to the rules of Subclause 8.2, "<comparison predicate>", where the <comp op> is the applicable <comp op> for Ki, with the following special treatment of null values. Whether a sort key value that is null is considered greater or less than a non-null value is implementation-defined, but all sort key values that are null shall either be considered greater than all non-null values or be considered less than all non-null values. XVi is said to precede YVi if the value of the <comparison predicate> "XVi <comp op> YVi" is true for the applicable <comp op>. c) In the result table, the relative position of row X is before row Y if and only if XVn precedes YVn for some n greater than 0 and less than the number of <sort specification>s and XVi = YVi for all i < n. The relative order of two rows for which XVi = YVi for all i is implementation-dependent. 6) If Q is not empty, then Q is returned. The method of returning Q is implementation-defined. Leveling Rules 1) The following restrictions apply for Full SQL: None. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: None. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: None. Direct invocation of SQL 197  SOU-007 and X3H2-93-083 198 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 16 Diagnostics management 16.1 <get diagnostics statement> Function Get exception or completion condition information from the diagnostics area. Format <statement information item name> ::= !! All alternatives from Part 1 | DYNAMIC_FUNCTION | DYNAMIC_FUNCTION_CODE Syntax Rules Table 5-Additional <identifier>s for ________________use_with_<get_diagnostics_statement>_______________ _<identifier>_____________Data_Type________________________________ |_________________<statem|nt_information_item_name>s_______________| | | | | DYNAMIC_FUNCTION character varying (L) | | | |_DYNAMIC_FUNCTION_CODE__|_exact_numeric_with_scale_0______________| | | | Access Rules No additional Access Rules. General Rules 1) Specification of <statement information item> retrieves information about the statement execution recorded in the diagnostics area into <simple target specification>. 2) The value of DYNAMIC_FUNCTION is the identification of the statement being prepared or executed dynamically. Table 26, "SQL-statement character codes for use in the diagnostics area", in Part 1 of this <ANSI> American <ISO > International Standard specifies the identifier of the SQL-statements. Diagnostics management 199  SOU-007 and X3H2-93-083 16.1 <get diagnostics statement> 3) The value of DYNAMIC_FUNCTION_CODE is a number identifying the prepared statement executed. Table 26, "SQL-statement character codes for use in the diagnostics area", in Part 1 of this <ANSI> American <ISO > International Standard specifies the code for the SQL-statements. Positive values are reserved for SQL-statements defined by this <ANSI> American <ISO > International Standard; negative values are reserved for implementation- defined SQL-statements. Table 6-Additional SQL-statement character _______________codes_for_use_in_the_diagnostics_area_______________ _SQL-statement_______________________Identifier_________________Code | <allocate cursor statement> | ALLOCATE CURSOR | 1 | | | | | | <allocate descriptor statement> | ALLOCATE DESCRIPTOR | 2 | | | | | | <deallocate descriptor | DEALLOCATE DESCRIPTOR | 15 | statement> | <deallocate prepared statement> | DEALLOCATE PREPARE | 16 | | | | | | <describe statement> | DESCRIBE | 20 | | | | | | <direct select statement: | SELECT | 21 | multiple rows> | <dynamic close statement> | DYNAMIC CLOSE | 37 | | | | | | <dynamic delete statement: | DYNAMIC DELETE CURSOR | 38 | positioned> | <dynamic fetch statement> | DYNAMIC FETCH | 39 | | | | | | <dynamic open statement> | DYNAMIC OPEN | 40 | | | | | | <dynamic single row select | SELECT | 41 | statement> | <dynamic update statement: | DYNAMIC UPDATE CURSOR | 42 | | positioned> | | | | | | | | <execute immediate statement> | EXECUTE IMMEDIATE | 43 | | | | | | <execute statement> | EXECUTE | 44 | | | | | | <get descriptor statement> | GET DESCRIPTOR | 47 | | | | | | <preparable dynamic delete | DYNAMIC DELETE CURSOR | 54 | statement: positioned> | <preparable dynamic update | DYNAMIC UPDATE CURSOR | 55 | | statement: positioned> | | | | | | | | <prepare statement> | PREPARE | 56 | | | | | 200 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 16.1 <get diagnostics statement> Table 6-Additional SQL-statement character ___________codes_for_use_in_the_diagnostics_area_(Cont.)___________ _SQL-statement_______________________Identifier_________________Code | <set catalog statement> | SET CATALOG | 66 | | | | | | <set descriptor statement> | SET DESCRIPTOR | 70 | | | | | | <set path statement> | SET CURRENT_PATH | 69 | | | | | | <set names statement> | SET NAMES | 72 | | | | | | <set schema statement> | SET SCHEMA | 74 | | | | | | Implementation-defined | An implementation- | x[1] | statements defined character string value different from the value associated with any other SQL-statement |_Unrecognized_statements___________|_A_zero-length_string_____|_0__ | | | | | | [1]An implementation-defined negat|ve number different from t|e | | value associated with any other SQ|-statement. | | |___________________________________|__________________________|___| | | | | | | |Leveling Rules | | | | 1) The following restrictions apply for Full SQL: No additional Leveling Rules. 2) The following restrictions apply for Intermediate SQL in addition to any Full SQL restrictions: No additional Leveling Rules. 3) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: No additional Leveling Rules. Diagnostics management 201  SOU-007 and X3H2-93-083 202 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 17 Information Schema and Definition Schema 17.1 Definition Schema 17.1.1 SQL_LANGUAGES base table Function The SQL_LANGUAGES table has one row for each ISO and implementation-defined SQL language binding and programming language for which conformance is claimed. Definition CREATE TABLE SQL_LANGUAGES ( SQL_LANGUAGE_SOURCE INFORMATION_ SCHEMA.CHARACTER_DATA CONSTRAINT SQL_LANGUAGES_SOURCE_NOT_NULL NOT NULL, SQL_LANGUAGE_YEAR INFORMATION_ SCHEMA.CHARACTER_DATA, SQL_LANGUAGE_CONFORMANCE INFORMATION_ SCHEMA.CHARACTER_DATA, SQL_LANGUAGE_INTEGRITY INFORMATION_ SCHEMA.CHARACTER_DATA, SQL_LANGUAGE_IMPLEMENTATION INFORMATION_ SCHEMA.CHARACTER_DATA, SQL_LANGUAGE_BINDING_STYLE INFORMATION_ SCHEMA.CHARACTER_DATA, SQL_LANGUAGE_PROGRAMMING_LANGUAGE INFORMATION_ SCHEMA.CHARACTER_DATA, CONSTRAINT SQL_LANGUAGES_STANDARD_VALID_CHECK CHECK ( ( SQL_LANGUAGE_SOURCE = 'ISO 9075' AND SQL_LANGUAGE_YEAR IS NOT NULL AND SQL_LANGUAGE_CONFORMANCE IS NOT NULL AND SQL_LANGUAGE_IMPLEMENTATION IS NULL AND ( ( SQL_LANGUAGE_YEAR = '1987' AND SQL_LANGUAGE_CONFORMANCE IN ( '1', '2' ) AND SQL_LANGUAGE_INTEGRITY IS NULL AND ( ( SQL_LANGUAGE_BINDING_STYLE = 'DIRECT' AND SQL_LANGUAGE_PROGRAMMING_LANGUAGE IS NULL ) OR ( SQL_LANGUAGE_BINDING_STYLE IN ( 'EMBEDDED', 'MODULE' ) AND SQL_LANGUAGE_PROGRAMMING_LANGUAGE IN Information Schema and Definition Schema 203  SOU-007 and X3H2-93-083 17.1 Definition Schema ( 'COBOL', 'FORTRAN', 'PASCAL', 'PLI' ) ) ) ) OR ( SQL_LANGUAGE_YEAR = '1989' AND SQL_LANGUAGE_CONFORMANCE IN ( '1', '2' ) AND SQL_LANGUAGE_INTEGRITY IN ( 'NO', 'YES' ) AND ( ( SQL_LANGUAGE_BINDING_STYLE = 'DIRECT' AND SQL_LANGUAGE_PROGRAMMING_LANGUAGE IS NULL ) OR ( SQL_LANGUAGE_BINDING_STYLE IN ( 'EMBEDDED', 'MODULE' ) AND SQL_LANGUAGE_PROGRAMMING_LANGUAGE IN ( 'COBOL', 'FORTRAN', 'PASCAL', 'PLI' ) ) ) ) OR ( SQL_LANGUAGE_YEAR = '1992' AND SQL_LANGUAGE_CONFORMANCE IN ( 'ENTRY', 'INTERMEDIATE', 'FULL' ) AND SQL_LANGUAGE_INTEGRITY IS NULL AND ( ( SQL_LANGUAGE_BINDING = 'DIRECT' AND SQL_LANGUAGE_PROGRAMMING_LANGUGE IS NULL ) OR ( SQL_LANGUAGE_BINDING IN ( 'EMBEDDED', 'MODULE' ) AND SQL_LANGUAGE_PROGRAMMING_LANGUAGE IN ( 'ADA', 'C', 'COBOL', 'FORTRAN', 'MUMPS', 'PASCAL', 'PLI' ) ) ) ) ) ) OR ( SQL_LANGUAGE_SOURCE <> 'ISO 9075' ) ) ) Description 1) Each row represents one binding of an ISO or implementation- defined SQL language to a standard module language, direct invocation, or an embedded programming language. 2) If the value of SQL_LANGUAGE_SOURCE is 'ISO 9075', then the value of SQL_LANGUAGE_BINDING_STYLE is the style of binding of the SQL language. If the value of SQL_LANGUAGE_BINDING_STYLE is 'MODULE', then the binding style of <module> is supported. If the value of SQL_LANGUAGE_BINDING_STYLE is 'EMBEDDED', then the binding style of <embedded SQL host program> is supported. If the value of SQL_LANGUAGE_BINDING_STYLE is 'DIRECT', then the binding style of <direct SQL statement> is supported. Otherwise, the value of SQL_LANGUAGE_BINDING_STYLE is implementation- defined. 204 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 17.1 Definition Schema 3) If the value of SQL_LANGUAGE_SOURCE is 'ISO 9075', then the value of SQL_LANGUAGE_PROGRAMMING_LANGUAGE is the programming language supported by the binding style indicated by the value of SQL_LANGUAGE_BINDING_STYLE. If the value of SQL_LANGUAGE_ BINDING_STYLE is 'DIRECT', then SQL_LANGUAGE_PROGRAMMING_ LANGUAGE is the null value. If the value of SQL_LANGUAGE_ BINDING_STYLE is 'MODULE' or 'EMBEDDED', then SQL_LANGUAGE_ PROGRAMMING_LANGUAGE has the value 'ADA', 'C', 'COBOL', 'FORTRAN', 'MUMPS', 'PASCAL', or 'PLI'. Case: a) If SQL_LANGUAGE_PROGRAMMING_LANGUAGE is 'ADA', then Ada is supported with the given binding style. b) If SQL_LANGUAGE_PROGRAMMING_LANGUAGE is 'C', then C is supported with the given binding style. c) If SQL_LANGUAGE_PROGRAMMING_LANGUAGE is 'COBOL', then COBOL is supported with the given binding style. d) If SQL_LANGUAGE_PROGRAMMING_LANGUAGE is 'FORTRAN', then Fortran is supported with the given binding style. e) If SQL_LANGUAGE_PROGRAMMING_LANGUAGE is 'MUMPS', then MUMPS is supported with the given binding style. f) If SQL_LANGUAGE_PROGRAMMING_LANGUAGE is 'PASCAL', then Pascal is supported with the given binding style. g) If SQL_LANGUAGE_PROGRAMMING_LANGUAGE is 'PLI', then PL/I is supported with the given binding style. Otherwise, the value of SQL_LANGUAGE_PROGRAMMING_LANGUAGE is implementation-defined. Information Schema and Definition Schema 205  SOU-007 and X3H2-93-083 206 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 18 Status codes 18.1 SQLSTATE _______Table_7-Additional_SQLSTATE_class_and_subclass_values_______ _CategoryCondition_______________Class__Subcondition_____________Subclass | X | dynamic SQL error | 07 | (no subclass) | 000 | | | | | | | | | | | cursor specification | 003 | cannot be executed | | | | invalid descriptor | 008 | | | | | count | | | | | | | | | | | | invalid descriptor | 009 | | | | | index | | | | | | | | | | | | prepared statement | 005 | | | | | not a cursor | | specification | | | | restricted data type | 006 | | | | | attribute violation | | | | | | | | | | | | using clause | 001 | | | | | does not match | | dynamic parameter specifications | | | | using clause does | 002 | | | | | not match target | | | | | | specifications | | | | | | | | | | | | using clause | 004 | | | | | required for dynamic | | | | | | parameters | | | | | | | | | | | | using clause required | 007 | | | | | for result fields | | | | | | | | | W | warning | 01 | (no subclass) | 000 | | | | | | | | | | | insufficient item | 005 | ________________________________________descriptor_areas___________ | | | | | | Status codes 207  SOU-007 and X3H2-93-083 208 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 19 Conformance 19.1 Claims of conformance Claims of conformance to this <ANSI> American <ISO > International Standard shall state: 1) Which level of conformance is claimed (as specified in Part 1 of this <ANSI> American <ISO > International Standard). 2) Which of the following additional binding styles are supported: a) Embedded syntax (<embedded SQL host program>) b) Direct invocation and processing of SQL language (<direct SQL statement>) 3) For the embedded syntax binding style, which of the following programming languages are supported: a) Ada b) C c) COBOL d) Fortran e) MUMPS f) Pascal g) PL/I 4) The definitions for all elements and actions that this Standard specifies as implementation-defined. Conformance 209  SOU-007 and X3H2-93-083 19.2 Processing methods 19.2 Processing methods This <ANSI> American <ISO > International Standard does not define the method by which an <embedded SQL host program> is processed. Although the processing of <embedded SQL host program> is defined in terms of derivation of a program compliant with a programming language standard and a <module>, implementations of SQL are not constrained to follow that method, provided that effect is achieved. Although the processing of <direct SQL statement> is defined in terms of calls to <routine>s in a <module>, implementations of Direct Invocation are not constrained to follow that method, so long as the same effect is achieved. 210 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  Annex A (Informative) Leveling the SQL Language This Annex describes the restrictions placed on conforming Full SQL, Intermediate SQL and Entry SQL language. A.1 Full SQL Specifications 1) Subclause 5.5, "<value specification> and <target specification>": a) An <indicator variable> shall not be an <SQL variable name>. 2) Subclause 12.4, "<set path statement>": a) Conforming Full SQL language shall not contain any <set path statement>. 3) Subclause 13.12, "<dynamic declare cursor>": a) A <dynamic declare cursor> shall not specify WITH HOLD. 4) Subclause 13.13, "<allocate cursor statement>": a) A <dynamic declare cursor> shall not specify WITH HOLD. 5) Subclause 14.1, "<embedded SQL host program>": a) An <embedded SQL statement> shall contain no <temporary view declaration>. b) An <embedded SQL statement> shall contain no <external function declaration>. 6) Subclause 14.2, "<embedded exception declaration>": a) A <SQL condition> shall not specify SQLSTATE or CONSTRAINT. b) A <major category> shall not specify SQLEXCEPTION or SQLWARNING. Leveling the SQL Language 211  SOU-007 and X3H2-93-083 A.2 Intermediate SQL Specifications A.2 Intermediate SQL Specifications 1) Subclause 6.2, "<value specification> and <target specification>": a) In Intermediate SQL, the specific data type of <indicator parameter>s and <indicator variable>s shall be the same implementation-defined data type. 2) Subclause 10.3, "<SQL procedure statement>": a) An <SQL dynamic statement> shall not be an <allocate cursor statement> statement. b) An <SQL session statement> shall not be a <set schema statement>, a <set catalog statement>, or a <set names statement>. c) An <SQL dynamic statement shall not be a <deallocate prepared statement>, or a <describe input statement>. 3) Subclause 12.1, "<set catalog statement>": a) Conforming Intermediate SQL language shall not contain any <set catalog statement>. 4) Subclause 12.2, "<set schema statement>": a) Conforming Intermediate SQL language shall not contain any <set schema statement>. 5) Subclause 12.3, "<set names statement>": a) Conforming Intermediate SQL language shall not contain any <extended statement name> or <extended cursor name>. b) Conforming Intermediate SQL language shall not contain any <set names statement>. 6) Subclause 13.2, "<allocate descriptor statement>": a) An <occurrences> and a <descriptor name> shall be a <literal>. 7) Subclause 13.3, "<deallocate descriptor statement>": a) A <descriptor name> shall be a <literal>. 8) Subclause 13.4, "<get descriptor statement>": a) A <descriptor name> shall be a <literal>. 9) Subclause 13.5, "<set descriptor statement>": a) A <descriptor name> shall be a <literal>. 212 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 A.2 Intermediate SQL Specifications 10)Subclause 13.6, "<prepare statement>": a) A <preparable SQL schema statement> shall not be an <assertion definition> or a <drop assertion statement>. b) A <preparable SQL schema statement> shall not be a <collation definition>, a <drop collation statement>, a <translation definition>, or a <drop translation statement>. c) A <preparable SQL session statement> shall not be a <set schema statement>. d) A <preparable SQL session statement> shall not be a <set catalog statement>. e) A <preparable SQL session statement> shall not be a <set names statement>. f) A <preparable SQL transaction statement> shall not be a <set constraints mode statement>. g) A <preparable SQL data statement> shall not be a <dynamic single row select statement>. 11)Subclause 13.7, "<deallocate prepared statement>": a) Conforming Intermediate SQL language shall contain no <deallocate prepared statement>. 12)Subclause 13.8, "<describe statement>": a) Conforming Intermediate SQL language shall not contain any <describe input statement>. 13)Subclause 13.9, "<using clause>": a) A <descriptor name> shall be a <literal>. 14)Subclause 13.10, "<execute statement>": a) Conforming Intermediate SQL language shall contain no <result using clause>. 15)Subclause 13.12, "<dynamic declare cursor>": a) Conforming Intermediate SQL language shall contain no <dynamic declare cursor> that specifies INSENSITIVE. b) If an <updatability clause> of FOR UPDATE with or without a <column name list> is specified, then neither SCROLL nor ORDER BY shall be specified. 16)Subclause 13.13, "<allocate cursor statement>": a) Conforming Intermediate SQL language shall not contain any <allocate cursor statement>. Leveling the SQL Language 213  SOU-007 and X3H2-93-083 A.2 Intermediate SQL Specifications 17)Subclause 13.15, "<dynamic fetch statement>": a) A <dynamic fetch statement> shall not contain a <fetch orientation>. 18)Subclause 13.17, "<dynamic delete statement: positioned>": a) Let T be the table identified by the <table name>. T shall not be a table that is identified in the <from clause> of any <subquery> contained in the <cursor specification> referenced by the <dynamic cursor name>. 19)Subclause 13.18, "<dynamic update statement: positioned>": a) Let T be the table identified by the <table name>. T shall not be a table that is identified in the <from clause> of any <subquery> contained in the <cursor specification> referenced by the <dynamic cursor name>. 20)Subclause 13.19, "<preparable dynamic delete statement: positioned>": a) Conforming Intermediate SQL language shall contain no <preparable dynamic delete statement: positioned>. 21)Subclause 13.20, "<preparable dynamic update statement: positioned>": a) Conforming Intermediate SQL language shall contain no <preparable dynamic update statement: positioned>. 22)Subclause 14.1, "<embedded SQL host program>": a) An <embedded SQL statement> shall not contain a <temporary table declaration>. 23)Subclause 14.3, "<embedded SQL Ada program>": a) An <Ada variable definition> shall not specify a bit string variable. 24)Subclause 14.4, "<embedded SQL C program>": a) A <C derived variable> shall not be a <C bit variable>. 25)Subclause 14.5, "<embedded SQL COBOL program>": a) A <COBOL type specification> shall not be a <COBOL bit type>. 26)Subclause 14.6, "<embedded SQL Fortran program>": a) A <Fortran type specification> shall not specify BIT. 214 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 A.2 Intermediate SQL Specifications 27)Subclause 14.8, "<embedded SQL Pascal program>": a) A <Pascal type specification> shall not specify BIT or PACKED ARRAY [1..<length>] OF BIT. 28)Subclause 14.9, "<embedded SQL PL/I program>": a) A <PL/I type specification> shall not specify BIT or BIT VARYING. 29)Subclause 15.1, "<direct SQL statement>": a) A <direct SQL data statement> shall not be a <temporary table declaration>. b) An <SQL session statement> shall not be a <set schema statement>, a <set catalog statement>, or a <set names statement>. c) An <SQL transaction statement> shall not be a <set constraints mode statement>. d) An <SQL schema statement> shall not be an <assertion definition> or a <drop assertion statement>. e) An <SQL schema statement> shall not be a <collation definition>, a <drop collation statement>, a <translation definition> or a <drop translation statement>. f) A <direct SQL statement> shall not be an <SQL connection statement>. A.3 Entry SQL Specifications 1) All Intermediate SQL specifications are included as Entry SQL specifications. 2) Subclause 5.4, "Names and identifiers": a) Conforming Entry SQL language shall not contain any <SQL statement name> or <dynamic cursor name>. 3) Subclause 5.5, "<value specification> and <target specification>": a) A <general value specification> shall not be a <dynamic parameter specification>. 4) Subclause 10.1, "<module>": a) A <module contents> shall not be a <dynamic declare cursor>. Leveling the SQL Language 215  SOU-007 and X3H2-93-083 A.3 Entry SQL Specifications 5) Subclause 10.3, "<SQL procedure statement>": a) An <SQL procedure statement> shall not be an <SQL dynamic statement>. 6) Subclause 13.2, "<allocate descriptor statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 7) Subclause 13.3, "<deallocate descriptor statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 8) Subclause 13.4, "<get descriptor statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 9) Subclause 13.5, "<set descriptor statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 10)Subclause 13.6, "<prepare statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 11)Subclause 13.7, "<deallocate prepared statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 12)Subclause 13.8, "<describe statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 13)Subclause 13.9, "<using clause>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 14)Subclause 13.10, "<execute statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 15)Subclause 13.11, "<execute immediate statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 216 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 A.3 Entry SQL Specifications 16)Subclause 13.12, "<dynamic declare cursor>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 17)Subclause 13.13, "<allocate cursor statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 18)Subclause 13.14, "<dynamic open statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 19)Subclause 13.15, "<dynamic fetch statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 20)Subclause 13.16, "<dynamic close statement>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 21)Subclause 13.17, "<dynamic delete statement: positioned>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 22)Subclause 13.18, "<dynamic update statement: positioned>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 23)Subclause 13.19, "<preparable dynamic delete statement: positioned>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 24)Subclause 13.20, "<preparable dynamic update statement: positioned>": a) Conforming Entry SQL language shall not contain any Dynamic SQL language. 25)Subclause 14.1, "<embedded SQL host program>": a) An <embedded SQL statement> shall not contain a <dynamic declare cursor>. b) An <embedded SQL statement> shall not contain an <embedded character set declaration>. Leveling the SQL Language 217  SOU-007 and X3H2-93-083 A.3 Entry SQL Specifications 26)Subclause 14.3, "<embedded SQL Ada program>": a) An <Ada qualified type specification> shall not specify a <character set specification>. 27)Subclause 14.4, "<embedded SQL C program>": a) A <C derived variable> shall not be a <C VARCHAR variable> or a <C extended char variable>. 28)Subclause 14.5, "<embedded SQL COBOL program>": a) A <COBOL character type> shall not specify a <character set specification>. b) A <COBOL integer type> shall not be a <COBOL binary integer>. 29)Subclause 14.6, "<embedded SQL Fortran program>": a) A <Fortran type specification> shall not specify a <character set specification>. 30)Subclause 14.8, "<embedded SQL Pascal program>": a) A <Pascal type specification> shall not specify a <character set specification>. 31)Subclause 14.9, "<embedded SQL PL/I program>": a) A <PL/I type specification> shall not specify CHARACTER VARYING. b) A <PL/I type specification> shall not specify a <character set specification>. 32)Subclause 15.1, "<direct SQL statement>": a) A <direct SQL statement> shall not be an <SQL schema statement>. b) An <SQL session statement> shall not be a <set session authorization identifier statement> or a <set local time zone statement>. c) A <direct SQL data statement> shall not be a <temporary table declaration>. 218 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  Annex B (Informative) Implementation-defined elements This Annex references those features that are identified in the body of this <ANSI> American <ISO > International Standard as implementation-defined. The term implementation-defined is used to identify characteristics that may differ between implementations, but that shall be defined for each particular implementation. 1) Subclause 4.3, "Catalogs": The default catalog name substitution value for execution of <preparable statement>s that are dynamically prepared in the current SQL-session through the execution of <prepare statement>s and <execute immediate statement>s are implementation-defined. 2) Subclause 4.12, "SQL-transactions": It is implementation-defined whether or not the dynamic execution of an <SQL dynamic data statement> is permitted to occur within the same SQL-transaction as the dynamic execution of an SQL-schema statement. If it does occur, then the effect on any prepared dynamic statement is also implementation-defined. 3) Subclause 4.14, "SQL-sessions": When an SQL-session is initiated, there is an implementation-defined default catalog whose name is used to effectively qualify all unqualified <schema name>s contained in <preparable statement>s that are dynamically prepared in the current SQL-session through the execution of <prepare statement>s and <execute immediate statement>s. 4) Subclause 4.14, "SQL-sessions": When an SQL-session is initiated, there is an implementation-defined default schema whose name is used to effectively qualify all unqualified <schema qualified name>s contained in <preparable statement>s that are dynamically prepared in the current SQL-session through the execution of <prepare statement>s and <execute immediate statement>s. Implementation-defined elements 219  SOU-007 and X3H2-93-083 5) Subclause 4.14, "SQL-sessions": The value of the current default SQL-path before a successful execution of <set path statement> is implementation-defined. 6) Subclause 4.8, "Embedded syntax": Whether a portion of the name space is reserved by an implementation for the names of procedures, subroutines, program variables, branch labels, <module>s, or <routine>s is implementation-defined; if a portion of the name space is so reserved, the portion reserved is also implementation-defined. 7) Subclause 4.9, "SQL dynamic statements": Within an SQL-session, all prepared statements belong to the same implementation- defined <module> that is different from any other <module> that exists simultaneously in the environment. 8) Subclause 4.10, "Direct invocation of SQL": The method of invoking <direct SQL statement>s, the method of raising conditions as a result of <direct SQL statement>s, the method of accessing diagnostic information, and the method of returning the results are all implementation-defined. 9) Subclause 5.4, "Names and identifiers": If a <schema name> contained in a <preparable statement> that is dynamically prepared in the current SQL-session through the execution of a <prepare statement> or an <execute immediate statement> does not contain a <catalog name>, then the implementation-defined <catalog name> for the SQL-session is implicit. 10)Subclause 5.4, "Names and identifiers": If a <schema qualified name> contained in a <preparable statement> that is dynamically prepared in the current SQL-session through the execution of a <prepare statement> or an <execute immediate statement> does not contain a <schema name>, then the implementation-defined <schema name> for the SQL-session is implicit. 11)Subclause 6.2, "<value specification> and <target specification>": In Intermediate SQL, the specific data type of <indicator parameter>s and <indicator variable>s shall be the same implementation-defined data type. 12)Subclause 13.3, "<routine>": The precision of an SQLCODE parameter in an <embedded COBOL program> is an implementation- defined value between 4 and 18, inclusive. 13)Subclause 13.3, "<routine>": The precision of an SQLCODE parameter in an <embedded PL/I program> is implementation- defined. 14)Subclause 13.2, "<allocate descriptor statement>": If WITH MAX <occurrences> is not specified, then an implementation- defined default value for <occurrences> that is greater than 0 is implicit. 220 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 15)Subclause 13.2, "<allocate descriptor statement>": The maximum number of SQL descriptor areas and the maximum number of item descriptor areas for a single SQL descriptor area are implementation-defined. 16)Subclause 13.5, "<set descriptor statement>": Restrictions on changing TYPE, LENGTH, OCTET_LENGTH, SCALE, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, and CHARACTER_SET_NAME values resulting from the execution of a <describe statement> before execution of an <execute statement>, <dynamic open statement>, or <dynamic fetch statement> are implementation-defined, except as provided in the General Rules of Subclause 13.9, "<using clause>". 17)Subclause 13.6, "<prepare statement>": The Format and Syntax Rules for a <preparable implementation-defined statement> are implementation-defined. 18)Subclause 13.9, "<using clause>": The character set of the data type of <descriptor name> is implementation-defined. 19)Subclause 14.1, "<embedded SQL host program>": If an <embedded character set declaration> is not specified, then an <embedded character set declaration> containing an implementation-defined <character set specification> is implicit. 20)Subclause 14.1, "<embedded SQL host program>": Each <allocate cursor statement> is replaced with a host language procedure or subroutine call of an implementation-defined procedure that associates the <dynamic cursor name> with the prepared statement. 21)Subclause 14.3, "<embedded SQL Ada program>": SQLCODE_TYPE describes an exact numeric variable whose precision is the implementation-defined precision defined for the SQLCODE parameter. 22)Subclause 14.5, "<embedded SQL COBOL program>": The COBOL data description clauses, in addition to the PICTURE, SIGN, USAGE and VALUE clauses, that may appear in a <COBOL variable definition> are implementation-defined. 23)Subclause 14.5, "<embedded SQL COBOL program>": The precision of the <COBOL type specification> that corresponds to SQLCODE is implementation-defined. 24)Subclause 14.9, "<embedded SQL PL/I program>": The PL/I data description clauses, in addition to the <PL/I type specification> and the INITIAL clause, that may appear in a <PL/I variable definition> are implementation-defined. 25)Subclause 14.9, "<embedded SQL PL/I program>": The precision of the <PL/I type specification> that corresponds to SQLCODE is implementation-defined. Implementation-defined elements 221  SOU-007 and X3H2-93-083 26)Subclause 15.1, "<direct SQL statement>": The <value specification> that represents the null value is implementation- defined. 27)Subclause 15.1, "<direct SQL statement>": The Format, Syntax Rules, and Access Rules for <direct implementation-defined statement> are implementation-defined. 28)Subclause 15.1, "<direct SQL statement>": Whether a <direct implementation-defined statement> may be associated with an active transaction is implementation-defined. 29)Subclause 15.1, "<direct SQL statement>": Whether a <direct implementation-defined statement> initiates a transaction is implementation-defined. 30) 222 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  Annex C (Informative) Implementation-dependent elements This Annex references those places where this <ANSI> American <ISO > International Standard states explicitly that the actions of a conforming implementation are implementation-dependent. The term implementation-dependent is used to identify characteristics that may differ between implementations, but that are not necessarily specified for any particular implementation. 1) Subclause 13.4, "<get descriptor statement>": If an exception condition is raised in a <get descriptor statement>, then the values of all targets specified by <simple target specification 1>, <simple target specification 2>, and <simple target specification 3> are implementation-dependent. 2) Subclause 13.5, "<set descriptor statement>": If an exception condition is raised in a <set descriptor statement>, then the values of all elements of the descriptor specified in the <set descriptor statement> are implementation-dependent. 3) Subclause 13.6, "<prepare statement>": The validity of an <extended statement name> value or a <statement name> in an SQL-transaction different from the one in which the statement was prepared is implementation-dependent. 4) Subclause 13.9, "<using clause>": When a <describe output statement> is executed, the values of DATA and INDICATOR are implementation-dependent. If TYPE indicates a character string type or a bit string type, then the values of SCALE and PRECISION are implementation-dependent. If TYPE indicates an exact or approximate numeric type, then the values of LENGTH and OCTET_LENGTh are implementation-dependent. If TYPE indicates a boolean type, then the values of PRECISION, SCALE, LENGTH, and OCTET_LENGTH are implementation-dependent. 5) Subclause 13.9, "<using clause>": When a <describe input statement> is used, the values for NAME, DATA, and INDICATOR in the SQL dynamic descriptor area structure is implementation- dependent. If TYPE indicates a character string type or a Implementation-dependent elements 223  SOU-007 and X3H2-93-083 bit string type, then the values of SCALE and PRECISION are implementation-dependent. If TYPE indicates an exact or approximate numeric type, then the values of LENGTH and OCTET_ LENGTh are implementation-dependent. If TYPE indicates a boolean type, then the values of PRECISION, SCALE, LENGTH, and OCTET_ LENGTH are implementation-dependent. 6) Subclause 14.1, "<embedded SQL host program>": The <module name> of the implied <module> derived from an <embedded SQL host program> is implementation-dependent. 7) Subclause 14.1, "<embedded SQL host program>": The <module authorization identifier> of the implied <module> derived from an <embedded SQL host program> is implementation-dependent. 8) Subclause 14.1, "<embedded SQL host program>": In each <declare cursor> in the implied <module> derived from an <embedded SQL host program>, each <embedded variable name> has been replaced consistently with a distinct <host parameter name> that is implementation-dependent. 9) Subclause 14.1, "<embedded SQL host program>": The <procedure name> of each <routine> in the implied <module> derived from an <embedded SQL host program> is implementation-dependent. 10)Subclause 14.1, "<embedded SQL host program>": In each <routine> in the implied <module> derived from an <embedded SQL host program>, each <embedded variable name> has been replaced consistently with a distinct <host parameter name> that is implementation-dependent. 11)Subclause 14.1, "<embedded SQL host program>": For <SQL procedure statement>s other than <open statement>s, whether one <routine> in the implied <module> derived from an <embedded SQL host program> can correspond to more than one <SQL procedure statement> in the <embedded SQL host program> is implementation- dependent. 12)Subclause 14.1, "<embedded SQL host program>": In each <routine> in the implied <module> derived from an <embedded SQL host program>, the order of the instances of <parameter declaration> is implementation-dependent. 13)Subclause 15.1, "<direct SQL statement>": A <commit statement> or a <rollback statement> is executed. If an unrecoverable error has occurred, or if the direct invocation of SQL terminated unexpectedly, or if any constraint is not satisfied, then a <rollback statement> is performed. Otherwise, the choice of which of these SQL-statements to perform is implementation- dependent. The determination of whether a direct invocation of SQL has terminated unexpectedly is implementation-dependent. 224 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  Annex D (Informative) Deprecated features It is intended that the following features will be removed at a later date from a revised version of this <ANSI> American <ISO > International Standard: 1) A <COBOL computational integer>s in <embedded SQL COBOL program>s. (LON-146/X3H2-90-519) 2) The use of SQLERROR as a <condition> in an <embedded exception declaration>. Deprecated features 225  226 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 Annex E (Informative) Incompatibilities with X3.135-1992 and ISO/IEC 9075:1992 This <ANSI> American <ISO > International Standard introduces some incompatibilities with the earlier version of Database Language SQL as specified in <ANSI> ANS X3.135-1992. <ISO > ISO/IEC 9075:1992. Unless specified in this Annex, features and capabilities of Database Language SQL are compatible with the earlier version of this <ANSI> American <ISO > International Standard. No additional incompatibilties. Incompatibilities with X3.135-1992 and ISO/IEC 9075:1992 227  Index - A - <C derived variable>o167, Adao4, 17, 55, 147, 148, 170, 171, 214, 218 149, 151, 152, 154, 159, <character representation>o 163, 164, 165, 166, 205, 163, 164, 168, 169, 172, 209, 214, 218 174, 186, 187 <Ada assignment operator>o <character set specifica- 163 tion>o28, 30, 72, 148, <Ada host identifier>o148, 163, 165, 166, 167, 169, 163, 164 171, 172, 174, 175, 176, <Ada initial value>o163, 177, 178, 182, 183, 185, 164 186, 188, 189, 218, 221 <Ada qualified type <C host identifier>o148, specification>o163, 167, 168, 169, 170 164, 166, 218 <C initial value>o167, <Ada type specification>o 168, 169 163, 165 <C numeric variable>o167, <Ada unqualified type 170 specification>o163 <COBOL binary integer>o <Ada variable definition>o 172, 174, 218 148, 163, 164, 165, 166, <COBOL bit type>o172, 173, 214 174, 175, 214 <allocate cursor statement> <COBOL character type>o o8, 10, 11, 12, 14, 16, 172, 173, 174, 175, 218 18, 32, 57, 98, 100, <COBOL computational 131, 132, 135, 142, 144, integer>o172, 174, 225 200, 212, 213, 221 <COBOL host identifier>o <allocate descriptor 148, 172, 173 statement>o10, 12, 19, <COBOL integer type>o172, 32, 57, 80, 200 218 <ampersand>o147, 149 <COBOL nines>o172, 173 <asterisk>o176 <COBOL nines specification> o172 - C - <COBOL numeric type>o172, <caller language clause>o 174 55, 93 <COBOL type specification>o <C array specification>o 172, 174, 175, 214, 221 167, 168, 169, 170 <COBOL variable definition> <C bit variable>o167, 168, o148, 172, 173, 174, 221 170, 214 <colon>o148, 163, 182 <C character variable>o <comma>o83, 87, 104, 163, 167, 168, 169, 170 167, 168, 176, 179, 182, <C class modifier>o167 186 Index 1  SOU-007 and X3H2-93-083 compilation unito1, 17, <descriptor item name>o74, 18, 148, 164, 168, 173, 83, 84, 87, 88, 89 176, 179, 182, 187 <descriptor name>o19, 30, <condition>o157, 158, 225 31, 32, 50, 52, 54, 80, <condition action>o157, 81, 82, 83, 84, 85, 86, 160 87, 88, 90, 104, 105, <constraint name>o157, 160 107, 109, 110, 112, 115, <C storage class>o167 116, 118, 119, 123, 192, <cursor name>o19, 30, 53, 212, 213, 221 61, 92, 129, 133, 135, <digit>o157 137, 138, 140, 141, 142, <direct implementation- 144, 150 defined statement>o191, <cursor sensitivity>o129, 192, 193, 194, 222 131 <directly executable <cursor specification>o18, statement>o191 91, 98, 99, 100, 124, <direct select statement: 129, 131, 133, 135, 138, multiple rows>o10, 11, 140, 142, 144, 214 13, 15, 35, 37, 38, 191, cursor specification cannot 192, 196, 200 be executedo124, 207 <direct SQL data statement> <C VARCHAR variable>o167, o191, 194, 215, 218 168, 169, 171, 218 <direct SQL statement>o <C variable definition>o 21, 22, 23, 28, 30, 31, 148, 167, 168, 169, 170, 59, 63, 65, 67, 69, 191, 171 192, 193, 194, 195, 204, <C variable specification>o 209, 210, 215, 218, 220 167 <double period>o163, 182 - D - <dynamic close statement>o <deallocate descriptor 8, 10, 11, 13, 14, 16, statement>o11, 12, 19, 18, 57, 59, 137, 155, 54, 57, 82, 192, 200 200 <deallocate prepared <dynamic cursor name>o30, statement>o8, 11, 12, 31, 32, 133, 135, 137, 18, 19, 57, 100, 200, 138, 140, 141, 214, 215, 212, 213 221 <declare cursor>o13, 15, <dynamic declare cursor>o 16, 129, 142, 144, 147, 8, 10, 12, 13, 15, 16, 150, 152, 154, 155, 224 18, 53, 54, 98, 99, 129, <delete statement: 132, 133, 135, 137, 138, searched>o13, 14, 15, 140, 142, 144, 147, 150, 91, 191 152, 154, 211, 213, 215, <describe input statement>o 217 11, 12, 18, 102, 110, <dynamic delete statement: 112, 115, 117, 212, 213, positioned>o9, 10, 11, 223 13, 15, 16, 18, 57, 59, <describe output statement> 138, 155, 200 o11, 12, 18, 102, 104, <dynamic fetch statement>o 106, 108, 119, 121, 122, 9, 10, 11, 13, 14, 16, 223 18, 57, 59, 61, 90, 104, <describe statement>o57, 117, 118, 119, 135, 136, 90, 102, 200, 221 155, 200, 214, 221 2 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 <dynamic open statement>o embeddedo1, 7, 9, 10, 11, 8, 9, 10, 11, 13, 14, 12, 13, 14, 16, 17, 18, 16, 18, 57, 59, 90, 114, 20, 23, 25, 33, 35, 43, 129, 131, 133, 134, 155, 46, 50, 147, 148, 149, 200, 221 150, 151, 152, 153, 154, <dynamic parameter 155, 156, 157, 158, 159, specification>o20, 33, 160, 161, 162, 163, 164, 34, 39, 46, 47, 85, 86, 165, 167, 168, 169, 170, 88, 93, 98, 102, 107, 172, 173, 174, 175, 176, 110, 111, 112, 113, 114, 177, 178, 179, 180, 181, 115, 118, 121, 125, 127, 182, 183, 184, 186, 187, 133, 191, 215 188, 191, 204, 209, 210, <dynamic procedure>o55, 211, 214, 217, 220, 221, 91, 92, 93, 106, 109, 224, 225 110, 112, 114, 116, 119, <embedded character set 122, 125, 126 declaration>o147, 148, <dynamic select statement>o 150, 152, 154, 156, 217, 10, 11, 12, 13, 14, 15, 221 16, 18, 59, 91, 92, 104, <embedded exception 106, 114, 118, 124, 127 declaration>o11, 147, <dynamic single row select 154, 157, 158, 159, 160, statement>o10, 11, 13, 161, 162, 225 14, 15, 16, 18, 35, 38, <embedded SQL Ada program>o 59, 91, 92, 98, 104, 17, 147, 149, 151, 152, 106, 117, 118, 119, 121, 154, 159, 163, 164, 165 124, 125, 127, 200, 213 <embedded SQL begin dynamic SQL erroro81, 84, declare>o147, 148, 149, 88, 98, 114, 115, 116, 154, 182 117, 118, 119, 120, 121, <embedded SQL COBOL 122, 124, 125, 131, 133, program>o17, 147, 149, 207 151, 152, 154, 159, 172, <dynamic update statement: 173, 174, 175, 225 positioned>o9, 10, 11, <embedded SQL C program>o 13, 15, 16, 18, 45, 57, 17, 147, 149, 151, 152, 59, 140, 141, 155, 200 154, 159, 167, 168, 169, - E - 170 **Editor's Note**o17, 21, <embedded SQL declare 71, 124, 140, 152, 155 section>o17, 147, 148, effectiveo8, 9, 22, 53, 149, 154, 156, 164, 168, 54, 59, 65, 88, 115, 173, 176, 179, 182, 187 116, 117, 119, 120, 121, <embedded SQL end declare>o 122, 123, 151, 192, 193, 147, 148, 149, 154, 182 197, 219 <embedded SQL Fortran program>o17, 147, 149, 151, 152, 154, 155, 159, 176, 177, 178 <embedded SQL host program> o7, 17, 20, 147, 148, 149, 150, 151, 152, 155, 158, 160, 204, 209, 210, 224 Index 3  SOU-007 and X3H2-93-083 <embedded SQL MUMPS <Fortran variable declare>o148, 149, 154 definition>o148, 176, <embedded SQL MUMPS 177, 178 program>o17, 147, 149, - G - 151, 152, 159, 179, 180, <general value specifica- 181 tion>o33, 34, 215 <embedded SQL Pascal <get descriptor informa- program>o17, 147, 149, tion>o83 151, 152, 154, 159, 182, <get descriptor statement>o 183, 184 11, 12, 19, 57, 83, 84, <embedded SQL PL/I program> 85, 200, 223 o17, 147, 149, 151, 152, <get header information>o 154, 159, 186, 187, 188 83 <embedded SQL statement>o <get item information>o83 7, 17, 35, 147, 148, <global declaration>o53 149, 150, 151, 154, 155, <go to>o157, 158, 159, 164, 168, 173, 176, 179, 160, 161, 162 182, 187, 211, 214, 217 <goto target>o157, 159 <embedded variable name>o - H - 35, 50, 148, 150, 152, <header item name>o73, 83, 153, 224 84, 87 <embedded variable <host identifier>o148, specification>o33, 35, 150, 151, 154, 155 43, 46, 191 <host label identifier>o <equals operator>o83, 87, 157, 158, 159, 160, 161, 163, 168 162 <execute immediate <host PL/I label variable>o statement>o5, 8, 10, 157, 158, 159, 160, 161, 11, 18, 19, 20, 22, 28, 162 30, 31, 33, 57, 63, 65, <host variable definition>o 67, 69, 127, 200, 219, 147, 148, 150, 151, 152, 220 153, 154, 155, 156 <execute statement>o5, 11, - I - 18, 20, 57, 69, 90, 98, <identifier>o19, 22, 30, 114, 116, 117, 118, 119, 67, 80, 98, 104, 131, 121, 122, 124, 125, 200, 199 221 immediately containo22, <extended cursor name>o 55, 80, 93, 94, 131, 19, 30, 31, 32, 92, 131, 141, 150 132, 133, 212 <indicator variable>o33, <extended statement name>o 34, 35, 36, 37, 38, 41, 19, 30, 31, 32, 98, 99, 211, 212, 220 131, 212, 223 Information Schemao20, 203 - F - <insert statement>o13, 14, <fetch orientation>o135, 15, 45, 91, 94, 96, 191 214 insufficient item <Fortran host identifier>o descriptor areaso105, 148, 176, 177 107, 109, 111, 113, 207 <Fortran type specifica- invalid descriptor counto tion>o176, 177, 178, 115, 116, 118, 119, 207 214, 218 invalid descriptor indexo 81, 84, 88, 207 4 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 invalid SQL descriptor <occurrences>o80, 81, 84, nameo50, 80, 82, 84, 88, 105, 107, 109, 110, 88, 104 112, 115, 116, 118, 119, invalid SQL statement nameo 212, 220 100, 102, 124, 131, 133 <old condition>o157, 158 <item number>o83, 84, 85, <open cascade option>o133 86, 87, 88 <order by clause>o140, <item reference>o33, 35 196, 197 - L - - P - <left bracket>o25, 167, parametero18, 20, 33, 34, 182 35, 36, 37, 38, 39, 43, <left paren>o147, 149, 46, 47, 55, 85, 86, 88, 163, 172, 173, 179, 186 93, 98, 102, 107, 109, <length>o20, 95, 97, 163, 110, 111, 112, 113, 114, 164, 165, 167, 168, 169, 115, 116, 117, 118, 119, 170, 172, 173, 174, 176, 121, 122, 123, 124, 125, 177, 178, 179, 180, 182, 126, 127, 133, 152, 153, 183, 184, 186, 187, 188, 154, 155, 165, 170, 174, 215 175, 178, 180, 184, 188, - M - 189, 191, 207, 212, 215, <major category>o157, 162, 220, 221, 224 211 <parameter list>o55, 93, moduleo8, 12, 13, 17, 18, 126 19, 20, 22, 23, 31, 32, <parameter using clause>o 53, 54, 71, 100, 102, 114, 116, 124, 125 124, 125, 127, 129, 133, Pascalo4, 17, 147, 148, 135, 137, 138, 140, 149, 149, 151, 152, 154, 159, 151, 152, 154, 155, 204, 182, 183, 184, 185, 205, 210, 215, 220, 224 209, 215, 218 <module contents>o53, 54, <Pascal host identifier>o 215 148, 182, 183 module variableo54 <Pascal type specification> <MUMPS character variable>o o182, 183, 184, 185, 179, 180 215, 218 <MUMPS host identifier>o <Pascal variable 148, 179 definition>o148, 182, <MUMPS length specifica- 183, 184 tion>o179, 180 <period>o172 <MUMPS numeric variable>o <PL/I host identifier>o 179, 180 148, 186, 187 <MUMPS type specification>o <PL/I type fixed binary>o 179 186 <MUMPS variable definition> <PL/I type fixed decimal>o o148, 179, 180 186 - N - <PL/I type float binary>o <non-reserved word>o27 186 no subclasso207 <PL/I type specification>o - O - 186, 187, 188, 189, 215, 218, 221 <PL/I variable definition>o 148, 186, 187, 188, 221 Index 5  SOU-007 and X3H2-93-083 <precision>o20, 95, 179, <right bracket>o25, 167, 180, 186, 188 182 <preparable dynamic delete <right paren>o147, 149, statement: positioned>o 163, 172, 173, 179, 186 10, 11, 13, 14, 16, 18, <routine>o5, 7, 12, 13, 91, 92, 125, 126, 142, 17, 20, 53, 55, 59, 92, 200, 214 126, 147, 152, 153, 154, <preparable dynamic update 155, 156, 193, 194, 210, statement: positioned>o 220, 224 10, 11, 13, 14, 16, 18, <routine body>o55, 93, 126 45, 91, 92, 125, 126, <routine header>o55, 93, 144, 200, 214 152, 153 <preparable implementation- <routine name>o22, 55, 69, defined statement>o14, 93 91, 92, 221 - S - <preparable SQL control <scale>o20, 179, 180, 186, statement>o91, 92 188 <preparable SQL data <schema namee list>o69 statement>o91, 213 scopeo1, 19, 30, 31, 32, <preparable SQL schema 80, 82, 98, 100, 102, statement>o91, 213 104, 124, 131, 132, 133, <preparable SQL session 141, 150, 164 statement>o91, 213 <scope option>o30, 31, 32, <preparable SQL transaction 80, 82, 98, 104 statement>o91, 213 <semicolon>o53, 55, 147, <preparable statement>o8, 149, 167, 179, 182, 186, 20, 22, 28, 30, 31, 33, 191 63, 65, 67, 69, 91, 92, <set catalog statement>o8, 93, 94, 125, 126, 219, 10, 20, 22, 57, 63, 64, 220 97, 201, 212, 213, 215 prepared statement not a <set clause list>o140, cursor specificationo 141, 144 131, 207 <set descriptor informa- <prepare statement>o5, 8, tion>o87 11, 12, 18, 19, 20, 22, <set descriptor statement>o 28, 30, 31, 32, 33, 57, 11, 12, 19, 57, 87, 88, 63, 65, 67, 69, 91, 100, 90, 201, 223 102, 124, 125, 129, 131, <set header information>o 200, 219, 220 87 <set item information>o87, - Q - 89 <query expression>o35, 38, <set names statement>o10, 94, 96, 133, 141, 196 20, 22, 57, 67, 97, 201, <query specification>o35, 212, 213, 215 38, 41, 91, 125, 138, <set path statement>o10, 140 22, 57, 69, 201, 211, <question mark>o20, 33 220 - R - <set schema statement>o8, <result using clause>o104, 10, 20, 22, 57, 65, 66, 117, 118, 119, 121, 122, 97, 201, 212, 213, 215 124, 125, 126, 213 <simple Latin upper case Retrieval assignmento43 letter>o157 <returns clause>o55 6 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)  SOU-007 and X3H2-93-083 <simple target specifi- SQL-sessiono7, 8, 10, 12, cation 1>o83, 84, 85, 14, 15, 19, 21, 22, 23, 223 28, 30, 31, 32, 33, 54, <simple target specifi- 59, 63, 65, 67, 69, 89, cation 2>o83, 84, 85, 192, 193, 194, 219, 220 223 SQL-session <module>o8, <simple target specifica- 19, 22, 23 tion>o33, 71, 72, 83, <SQL session statement>o 84, 98, 199 57, 91, 191, 212, 215, <simple value specification 218 1>o87 <SQLSTATE char>o157, 158 <simple value specification <SQLSTATE class value>o 2>o87, 89 157, 158, 160, 161 <simple value specifica- SQL-statemento1, 5, 7, 8, tion>o19, 30, 31, 33, 9, 10, 11, 12, 13, 14, 71, 72, 80, 82, 83, 87, 15, 17, 18, 19, 20, 21, 91, 92, 97, 104, 131 22, 47, 57, 59, 78, 100, <specific name>o55 150, 157, 192, 199, 200, SQL-agento53, 54, 59, 192, 201, 224 193, 194 <SQL statement name>o5, SQL-cliento17, 23, 151 11, 30, 32, 91, 98, 100, <SQL condition>o157, 158, 102, 124, 215 162, 211 <SQL statement variable>o SQL-connectiono12, 14, 15, 5, 11, 91, 92, 127 21 <SQLSTATE subclass value>o <SQL connection statement>o 157, 158, 160 23, 191, 193, 215 <SQL terminal character>o <SQL control statement>o 25 91, 92 <SQL terminator>o147, 148, <SQL dynamic data 149 statement>o21, 57, 59, SQL-transactiono8, 12, 14, 219 15, 21, 59, 61, 99, 155, <SQL dynamic statement>o 193, 194, 219, 223 31, 47, 57, 58, 104, <SQL transaction statement> 212, 216 o91, 191, 215 <SQL embedded language <SQL variable declaration>o character>o25 126, 147, 150, 153, 154, SQL-environmento7, 22 156 <SQL executable statement>o SQL_LANGUAGESo203 57 SQL_LANGUAGE_CONFORMANCEo SQL-implementationo21, 59, 203, 204 194 SQL_LANGUAGE_IMPLEMENTATION SQL-patho22, 23, 69, 220 o203 <SQL prefix>o147, 148, 149 SQL_LANGUAGE_INTEGRITYo <SQL procedure statement>o 203, 204 12, 13, 20, 50, 52, 57, SQL_LANGUAGE_SOURCEo203, 58, 59, 61, 92, 93, 147, 204, 205 153, 154, 155, 158, 160, SQL_LANGUAGE_YEARo203, 204 192, 216, 224 SQL_TEXTo73, 75, 76 <SQL schema statement>o91, <statement information item 191, 194, 195, 215, 218 name>o199 SQL-servero23 Index 7  SOU-007 and X3H2-93-083 <statement name>o19, 30, <using descriptor>o102, 32, 98, 99, 100, 102, 104, 115, 116, 117, 118, 124, 127, 129, 133, 223 119, 121, 122, 124, 135 <statement or declaration>o - V - 55, 147, 156 <value specification>o33, successful completiono160, 47, 63, 65, 67, 94, 97, 194 98, 191, 196, 222 <system descriptor statement>o57 - T - <table name>o125, 138, 140, 141, 142, 144, 150, 214 <target specification>o19, 33, 50, 86, 88, 98, 104, 117, 118, 119, 120, 121, 125, 135, 136 <temporary table declaration>o13, 15, 147, 150, 153, 154, 191, 214, 215, 218 <temporary view declara- tion>o13, 15, 147, 150, 153, 154, 156, 191, 211 - U - <unsigned integer>o157, 158, 159, 160, 161, 162, 196 <update statement: searched>o13, 14, 15, 45, 91, 191 <using argument>o94, 104, 114, 115, 116, 117, 118, 119, 125 <using arguments>o104, 114, 115, 116, 117, 118, 119, 124, 135, 136 <using clause>o104, 114, 116, 117, 118, 119, 121, 122, 124, 133, 135 using clause does not match dynamic parameter specificationso114, 115, 207 using clause does not match target specificationso 118, 119, 207 using clause required for dynamic parameterso125, 133, 207 using clause required for result fieldso125, 207 8 (ISO-ANSI working draft) SQL Host Language Bindings (SQL/Bindings)