1 SQLMOD72 The SQL module language and SQL module processor allow procedures that contain SQL statements to be called from any host language, including those not supported by the SQL precompiler. The SQL module language provides a calling mechanism for host language programs to execute SQL statements contained in a separate file called an SQL module file. The file contains module language elements that specify a single SQL module. The module includes one or more procedures. A procedure can contain a: o Simple statement, which consists of a single SQL statement and optional parameter declarations o Compound statement, which can include local variable declarations, multiple SQL statements, flow control statements, and transaction management statements A procedure that contains a single SQL statement is called a simple-statement procedure. A procedure that contains a compound statement, which can contain multiple SQL statements, is called a multistatement procedure. The host language program uses call statements to specify a particular SQL module procedure and supplies a sequence of actual parameters that corresponds in number and in data type to the parameter declarations in the procedure. A call to a procedure in an SQL module causes the simple or compound statement in the procedure to be executed. Oracle Rdb recommends using SQL module language, rather than precompiled SQL, because module language offers the following advantages: o Module language allows procedures that contain SQL statements to be called from any host language. In contrast, the SQL precompiler only supports a subset of host languages: Ada, C, COBOL, FORTRAN, Pascal, and PL/I. o Programs that use the SQL module language can isolate all SQL statements in SQL modules to improve modularity and avoid using two languages in the same source file. o Programs can work around restrictions of the SQL precompiler by calling SQL modules: - Programs that support pointer variables can take full advantage of dynamic SQL and use the SQLDA and SQLDA2 with the SQL module language. - SQL module language does not restrict use of host language features not supported by the precompiler (such as pointer variables in C, block structure, macros, user-defined types, and references to array elements). o Programs written in languages for which there is an ANSI standard can avoid embedding code that does not conform to the standard by isolating noncompliant SQL statements in SQL modules. For a detailed discussion of programming considerations for the SQL module language, see the Oracle Rdb Guide to SQL Programming. 2 Environment SQL module language elements must be part of an SQL module file. 2 Format (B)0MODULE qqqqwqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqwqk   m> <module-name> qj m> DIALECT environment qqqj x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk   mq> char-set-options qqqj x   lqqqqqqqqqqqqqqqqqqqqqqqq LANGUAGE language-name qqwqqqqqqqqqqqqqqqqqqqqqqqqqwqk   m> CATALOG <catalog-name> j x   lqqqqqqqqqqqqqqqqqqqqqqqq SCHEMA <schema-name> qj m> AUTHORIZATION <auth-id> j x   lqqqqqqqqqqqqqqqqqqqqqqqq PRAGMA (module-pragma-list) qj mqq> module-language-options qqj x  lqqqqqqqqqqqqqqqqqqqqqqqq procedure-clause qqwqqqqqq>   mwq> declare-statement qqwqj mqqqqqqqqqqq SQL99 qqqqqwq>  tqq> SQL92 qqqqqu   tqq> SQL89 qqqqqu   tqq> SQLV40 qqqqu   mqq> MIA qqqqqqqj    (B)0char-set-options =    qqwqqqqqqqqqqqqqqqq> qqqqqqqqqqqqqwqqqqqk   mqq> NAMES ARE names-char-set qqj x  lqqqqqqqqqqqqqqqqq qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>   x tqq> LITERAL CHARACTER SET support-char-set qqqqu x   x tqq> NATIONAL CHARACTER SET support-char-set qqqu x   x tqq> DEFAULT CHARACTER SET support-char-set qqqqu x   x tqq> IDENTIFIER CHARACTER SET names-char-set qqqu x  x mqq> DISPLAY CHARACTER SET names-char-set qqqqqqj x  mqqqqqqqqqqqqqqqqqqqqqqqqqqq declare-alias-statement qqqqqqqwqqqq>   tq> declare-cursor-statement qqqqqqu   tq> declare-statement qqqqqqqqqqqqqu   tq> declare-table-statement qqqqqqqu   mq> declare-transaction-statement qj    (B)0module-pragma-list =     qqqqqqqqqqqqwqqwq> DEC_ADA qqqqqqqqqqqqqqqwqqwqqqq> x  tq> GNAT_ADA qqqqqqqqqqqqqqu x x mq> IDENT string-literal qqqj x mqqqqqqqqqqqqqqqq ,  ALIAS <alias-name> qqqqqqqqqqqqqqqqqqqqqqqqwqwqq>  x tqqq> CHARACTER LENGTH qwq> CHARACTERS qwqqqqqqqqu x   x x mq> OCTETS qqqqqj x x   x tqqq> DEFAULT DATE FORMAT qqwqq> SQL99 qwqqqqqqqqu x   x x tqq> SQL92 qu x x  x x mqq> VMS qqqj x x  x tqqq> KEYWORD RULES environment qqqqqqqqqqqqqqqqqu x   x tqqq> PARAMETER COLONS qqqqqqqqqqqqqqqqqqqqqqqqqqu x   x tqqq> QUOTING RULES environment qqqqqqqqqqqqqqqqqu x   x tqqq> RIGHTS qqwqq> INVOKER qqqwqqqqqqqqqqqqqqqqqu x   x x mqq> RESTRICT qqj x x   x tqqq> VIEW UPDATE RULES environment qqqqqqqqqqqqqu x   x tqqq> QUIET COMMIT qwq> ON qqqwqqqqqqqqqqqqqqqqqqu x  x x mq> OFF qqj x x x mqqq> COMPOUND TRANSACTIONS  qwq> INTERNAL qwqqqqj x x  mq> EXTERNAL qj x mqqqqqqqqqqqqqqqqqqqqqqqqqqq PROCEDURE <procedure-name> qqqqqqk  x lqqqqqqqqqqqqqqq param-decl-list qqqqqwq> ; qqk  x mq> ( param-decl-list ) qj x  x lqqqqqqqqqqqqqqq simple-statement qqqwq> ; qwq>  x mq> compound-statement qj x   mqqqqqqqqqqqqqqqqq param-decl qqqwq>  mqqwqqqqq <parameter-name> qqk   x lqqqqqqqqqqqqqqqqqqqj   x mwq> data-type qqqqqwwqqqqqqqqqqqqqqq>qqqqqqqqqqqqqwqqwq>   x tq> <domain-name> qumq> BY DESCRIPTOR wqqqqq>qqqqwj x   x mq> record-type qqqj mq> CHECK qj x   tq> SQLCA qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> SQLCODE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> SQLSTATE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   mqwqqqqqqqqqqqqqqqqqqqqqwqqwq> SQLDA qqqqqqqqqqqqqqqqqqqqu   mq> <parameter-name> qj mq> SQLDA2 qqqqqqqqqqqqqqqqqqqj    (B)0record-type =    q> RECORD qqqk  lqqqqqqqqqqqqj  mww> <item-name> w> data-type qqwwqqw> END RECORD q>   xx m> record-type jx x   xmqqqqqqqqqwqqqqqqqwqqq FROM <path-name> qqqk x   xlqqqqqqqqqqqqqqqqqqqqqqj x   xmwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqu   x tq> FIXED qqqqqqqqqqqqqqqqqqqqqu x   x tq> NULL TERMINATED BYTES qqqqqu x   x mq> NULL TERMINATED CHARACTERS j x   mq> INDICATOR ARRAY OF qqqqqk x   lqqqqqqqqqqqqqqqqqqqqqqqqqj x   mqq> <array-length> qqqqqqk x   lqqqqqqqqqqqqqqqqqqqqqqqqqj x   mqq> exact-numeric-type qqqqqqqqqj    (B)0exact-numeric-type =    qqqqqqwqq> SMALLINT qqqwqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqwq>  tqq> BIGINT qqqqqu mq> ( <n> ) qj   x   tqq> TINYINT qqqqj   x   tqq> INTEGER qqwqqqqqqqq qqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqu  x mq> ( <n> ) qj mq> IS qqwq> 4 qwq> BYTES qj x  x   mq> 8 qj x  tqq> DECIMAL qqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqj  mqq> NUMERIC qqj mq> ( qq> <n> qwqqqqqqqqqqwq> ) qj   mq> , <n> qj  (B)0language-name =     qwq> ADA qqqqqwqq>  tq> BASIC qqqu   tq> C qqqqqqqu   tq> COBOL qqqu   tq> FORTRAN qu   tq> PASCAL qqu   tq> PLI qqqqqu   mq> GENERAL qj  (B)0data-type =    qqwq> char-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   tq> TINYINT qqqqqqqqqqqqqqwqqqqqwqqqqqqqqqqwqqqqqqqqqqqu   tq> SMALLINT qqqqqqqqqqqqqu mq> (<n>) qj x   tq> BIGINT qqqqqqqqqqqqqqqu x   tq> LIST OF BYTE VARYING qj x   tq> INTEGER qwqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqu   x mq> (<n>) qj mq> IS qqwq> 4 qwq> BYTES qj x   x mq> 8 qj x   tq> DECIMAL qwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqu   tq> NUMERIC qjmq> ( qq> <n> wqqqqqqqqqqwq> ) j x   x mq> , <n> qj x   tq> FLOAT qwqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> (<n>) qj x  tq> NUMBER qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqu x mq> ( qwqq <p> qwqwqqqqqqqqqwq> ) qj x x mq> * qqqj mq> <d> qqj x  tq> REAL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> DOUBLE PRECISION qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   mq> date-time-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  (B)0char-data-types =    qwq> CHAR qqqqqqqqqqqqqwwqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>  tq> CHARACTER qqqqqqqqumq> ( <n> ) qjmq> CHARACTER SET char-set-name qj x  tq> CHAR VARYING qqqqqu   x  tq> CHARACTER VARYING j x  tq> VARCHAR qqw> ( <n> ) qqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqu  tq> VARCHAR2 qj  mq> CHARACTER SET char-set-name qj  x  tq> LONG VARCHAR qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> NCHAR qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> NATIONAL CHAR qqqqqqu mq> ( <n> ) qj    x   tq> NATIONAL CHARACTER qj    x   tq> NCHAR VARYING qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqu  tq> NATIONAL CHAR VARYING qqqqqqu mq> ( <n> ) qj    x   tq> NATIONAL CHARACTER VARYING qj    x  tq> RAW q> ( <n> ) qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu mq> LONG qwqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mq> RAW qj (B)0date-time-data-types =    qqwq> DATE qwqqqqqqqqqqwqqqqqqqqqqqqqqqqqwqq>   x tq> ANSI qu x   x mq> VMS qqqj x   tq> TIME qqq> frac qqqqqqqqqqqqqqqqqqqqu   tq> TIMESTAMP qq> frac qqqqqqqqqqqqqqqqu   mq> INTERVAL qqq> interval-qualifier qqj    (B)0frac =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mqq> ( <numeric-literal> ) qj    (B)0interval-qualifier =    qqwq> YEAR qqq> prec qqwqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqwq>   x mq> TO MONTH qj x   tq> MONTH qq> prec qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> DAY qqqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO qwq> HOUR qqqqqqqqqqqqqqqu   x tq> MINUTE qqqqqqqqqqqqqu   x mq> SECOND q> frac qqqqqu   tq> HOUR qqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO qwq> MINUTE qqqqqqqqqqqqqu   x mq> SECOND q> frac qqqqqu   tq> MINUTE q> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO SECOND qqqqqq> frac qqqqqu   mq> SECOND q> seconds-prec qqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0prec =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mqq> ( <numeric-literal> ) qj    (B)0seconds-prec =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   mq> ( <numeric-literal-1> qqqk x   lqqqqqqqqqqqqqqqqqqqqqqqqqj x   mwqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qqj   m> , <numeric-literal-2> qqj    2 Arguments 3 ALIAS Specifies the default alias for the module. If you do not specify a module alias, the default alias is the authorization identifier for the module. When the FIPS flagger is enabled, the ALIAS clause (by itself or used with the AUTHORIZATION clause) is flagged as nonstandard syntax. If the application needs to refer to only one database across multiple modules, it is good practice to use the same alias for the default database in all modules that will be linked to make up an executable image. If that image will include modules processed with the SQL precompiler, you should specify RDB$DBHANDLE in the AUTHORIZATION clause of all SQL modules in the image because the alias RDB$DBHANDLE always designates the default database in precompiled SQL programs. 3 AUTHORIZATION Specifies the authorization identifier for the module. If you do not specify a schema authorization, the authorization identifier is the user name of the user compiling the module. If you want to comply with the ANSI/ISO SQL89 standard, specify the AUTHORIZATION clause without the schema-name. Specify both the AUTHORIZATION clause and the schema name to comply with the ANSI/ISO SQL99 Standard. When you attach to a multischema database, the authorization identifier for each schema is the user name of the user compiling the module. This authorization identifier defines the default alias and schema. You can use the ALIAS and SCHEMA clauses to override the defaults. If you attach to a single-schema database or specify that MULTISCHEMA IS OFF in your ATTACH or DECLARE ALIAS statements and you specify both an AUTHORIZATION clause and an ALIAS clause, the authorization identifier is ignored by SQL unless you specify the RIGHTS clause in the module file. The RIGHTS clause causes SQL to use the authorization identifier specified in the module AUTHORIZATION clause for privilege checking. If procedures in the SQL module always qualify table names with an authorization identifier, the AUTHORIZATION clause has no effect on SQL statements in the procedures. When the FIPS flagger is enabled, the omission of an AUTHORIZATION clause is flagged as nonstandard ANSI syntax. 3 BY_DESCRIPTOR Specifies that the formal parameter will be passed to the calling program module by descriptor. The BY DESCRIPTOR clause is useful when: o You specify the GENERAL keyword in the LANGUAGE clause of an SQL module, but the default for the language is to pass parameters by descriptor. The default for GENERAL is to pass parameters by reference, but you can override that default passing mechanism by specifying BY DESCRIPTOR. o You want to take advantage of the CHECK option for parameter declarations. That option is available only for parameters declared with the BY DESCRIPTOR clause. o You need to override the default parameter passing mechanism for languages that pass parameters by reference. The BY DESCRIPTOR clause supports only OpenVMS static descriptors, which are fixed-length fields. For any language, the passing mechanism for SQL module formal parameters must be the same as the actual parameters in the host language module. Ada, BASIC, C, FORTRAN, Pascal, and PL/I do not support passing records by descriptor. You may construct a descriptor from elements in all these languages and pass the constructed descriptor to the SQL module language by reference. o When you construct a descriptor for a host language record when the module language is Ada, BASIC, C, FORTRAN, Pascal, PL/I, or GENERAL, use a fixed-length descriptor (CLASS_S) with a character string data type, and pass the length of the entire record. o If the language is Ada, BASIC, FORTRAN, or Pascal, pass indicator arrays using an array descriptor (CLASS_A) and the data type of all of the array elements. o If the language is COBOL, pass arrays using fixed-length (CLASS_S) descriptors and character string data types, regardless of the data types of the array elements. o If the language is C, the SQL module processor interprets CHAR fields one way when the data type is defined in the module, and another way when the definition is read from the dictionary. When the data type is defined in the module, the SQL module processor interprets character strings within records as null-terminated strings. In other words, if you declare a field specified as CHAR(9), the C module language interprets this as a field that is actually 10 characters long, with the tenth character being the null terminator. However, if you include a record in a C module from the data dictionary, you can specify any of three options for CHAR field interpretation. 3 CATALOG Specifies the default catalog for the module. Catalogs are groups of schemas within a multischema database. If you omit the catalog name when specifying an object in a multischema database, SQL uses the default catalog name RDB$CATALOG. Databases created without the multischema attribute do not have catalogs. You can use the SET CATALOG statement to change the current default catalog name in dynamic or interactive SQL. 3 CHARACTER_LENGTH Specifies whether the length of character string parameters, columns, and domains are interpreted as characters or octets. If the dialect is set to SQL89, SQL92, SQL99 or MIA, the default is CHARACTERS. Otherwise, the default is OCTETS. 3 char-data-types Refer to the Oracle Rdb SQL Reference Manual for information about the character data types that SQL supports. 3 CHECK Specifies that SQL compares at run time the data type, length, and scale of the descriptor for an actual parameter to what was declared for the procedure parameter in the SQL module. If the two do not match, SQL returns an error. The CHECK clause works only with parameters passed by descriptor from the calling host language module. Because there is no connection between an SQL module and a calling host language program module when they are compiled, there is no way for SQL to check for agreement between formal parameter declarations and actual parameters in calls to the module. The CHECK clause provides a way to do such checking when the program runs. If a formal parameter declaration does not specify the CHECK clause, SQL assumes that procedure and calling parameters agree. If they do not, programs can give unpredictable results. However, you may choose not to use the CHECK clause because: o The CHECK clause is not part of ANSI-standard SQL. o There is a minor performance penalty for SQL to check parameters at run time. o Using CHECK can make host programs more complicated. The CHECK clause follows these rules in comparing formal parameters with call parameters: o If a formal parameter is TIMESTAMP data type, the CHECK clause accepts any corresponding actual parameter that is 8 bytes long. o If the language is C and the formal parameter is CHAR data type, the CHECK clause expects the descriptor to be 1 byte longer than the number of characters in the formal parameter. This occurs because character strings in C include a terminator character (they are in ASCIZ format) that is not included in the length of the formal parameter declaration. When you retrieve data definitions from the dictionary, however, you can change the default interpretation of character data by specifying FIXED or NULL TERMINATED CHARACTERS in the record-type clause of the FROM path-name clause. o The CHECK clause supports dynamic string descriptors (CLASS_D) in BASIC for procedure parameters declared with the CHARACTER data type. However, the CHECK clause does not compare the length of the descriptor with the length of the procedure parameter because the buffer to receive the data is allocated at run time. o If the formal parameter is VARCHAR data type, the descriptor that the CHECK clause accepts depends on the language. - If the language is PL/I or Pascal (languages that support varying character data type), the descriptor must be a varying string (CLASS_VS) descriptor, the data type must be varying text, and the length must be the same as the length of the formal parameter declaration. - If the language is not PL/I or Pascal, the CHECK clause accepts a varying string descriptor as in the preceding paragraph, or a fixed-length (CLASS_S) or unspecified (DTYPE_Z) descriptor with data type of text and a length 2 bytes longer than the length of the formal parameter declaration. For more detail on the different types of OpenVMS argument descriptors, see the OpenVMS programming documentation. 3 compound-statement Most commonly, includes multiple executable SQL statements, associated variable declarations, and control statements within a BEGIN . . . END block; however, each of these arguments is optional. For instance, you can create an empty BEGIN . . . END block (BEGIN END;). SQL executes the compound statement when the procedure in which it is embedded is called by a host language module. See the Oracle Rdb SQL Reference Manual for more complete information about a compound statement. 3 COMPOUND_TRANSACTIONS Syntax options: COMPOUND TRANSACTIONS INTERNAL COMPOUND TRANSACTIONS EXTERNAL Allows you to specify whether SQL should start a transaction before executing a compound statement or stored procedure. The COMPOUND TRANSACTIONS EXTERNAL clause instructs SQL to start a transaction before executing a procedure. The COMPOUND TRANSACTIONS INTERNAL clause instructs SQL to allow a procedure to start a transaction as required by the procedure execution. By default, SQL starts a transaction before executing a compound statement if there is no current transaction 3 data-type You can specify the character set of parameters that are defined as character data types. SQL assumes the character set of parameters based on the following rules: o If a parameter is not qualified by a character set or defined as a national character data type, SQL considers the parameter to be of the default character set as specified in the DEFAULT CHARACTER SET clause. o If a parameter is defined as a national character data type (NCHAR, NCHAR VARYING), SQL considers the parameter to be of the national character set as specified in the NATIONAL CHARACTER SET clause. o If a parameter is defined as a data type qualified by a character set, SQL considers the parameter to be of that character set. See the Oracle Rdb SQL Reference Manual for information about data types and qualifying a data type with a character set. The Argument INTEGER topic describes the INTEGER data type with regard to the SQL module language. The SQL data type specified for the formal parameter in a module must be equivalent to the data type of the host language variable declaration for the actual parameter. If the formal parameter and actual parameter are not declared with equivalent data types, SQL can give unpredictable results. The data type for a database key is CHAR(n), where n equals the number of bytes of the database key. See the Oracle Rdb SQL Reference Manual for more information on database keys. 3 declare-statement Any of the following statements: o DECLARE ALIAS o DECLARE CURSOR o DECLARE STATEMENT o DECLARE TABLE o DECLARE TRANSACTION You must place all DECLARE statements in an SQL module together after the LANGUAGE clause of the module. All such DECLARE statements are optional. All the DECLARE statements except DECLARE TRANSACTION can be repeated. For each DECLARE CURSOR statement, however, there must be only one procedure in the SQL module that contains an OPEN statement that corresponds to the DECLARE CURSOR statement. Do not use any punctuation to separate DECLARE statements or to separate the declare-statement section from the procedure section. 3 DEFAULT_CHARACTER_SET Specifies the character set for parameters that are not qualified by a character set and are not defined as a national character data type. If you do not specify a character set in this clause or in the NAMES ARE clause, the default is DEC_MCS. This clause overrides the character set specified in the NAMES ARE clause. See the Oracle Rdb SQL Reference Manual for a list of the allowable character sets. 3 DEFAULT_DATE_FORMAT Controls the default interpretation for columns with the DATE or CURRENT_TIMESTAMP data type. The DATE and CURRENT_TIMESTAMP data types can be either VMS or SQL format. If you specify VMS, both data types are interpreted as VMS format. The VMS format DATE and CURRENT_TIMESTAMP contain YEAR to SECOND fields, like a TIMESTAMP. If you specify an SQL standard such as SQL99, both data types are interpreted as SQL format. The SQL format DATE contains only the YEAR to DAY fields. The default is VMS. Use the DEFAULT DATE FORMAT clause, rather than the ANSI_DATE qualifier, because the qualifier will be deprecated in a future release. 3 DIALECT Controls the following settings for the current connection: o Whether the length of character string parameters, columns, and domains are interpreted as characters or octets o Whether double quotation marks are interpreted as string literals or delimited identifiers o Whether or not identifiers may be keywords o Which views are read-only o Whether columns with the DATE or CURRENT_TIMESTAMP data type are interpreted as VMS or SQL99 format o Whether or not parameter names begin with a colon o Whether or not the session character sets change depending on the dialect specified The DIALECT clause lets you specify the settings with one clause, instead of specifying each setting individually. Because the module processor processes the module clauses sequentially, the DIALECT clause can override the settings of clauses (for example, QUOTING RULES) specified before it or be overridden by clauses specified after it. The following statements are specific to the SQL92 and SQL99 dialects: o The default constraint evaluation time setting changes from DEFERRABLE to NOT DEFERRABLE. o Conversions between character data types when storing data or retrieving data raise exceptions or warnings in certain situations. o You can specify DECIMAL or NUMERIC for formal parameters in SQL modules, and declare host language parameters with packed decimal or signed numeric storage format. SQL generates an error message if you attempt to exceed the precision specified. o The USER keyword specifies the current active user name for a request. o A warning is generated when a null value is eliminated from a SET function. o The WITH CHECK OPTION clause on views returns a discrete error code from an integrity constraint failure. o An exception is generated with non-null terminated C strings. See the Oracle Rdb SQL Reference Manual for more information on the settings for each option of the DIALECT clause. 3 DISPLAY_CHARACTER_SET Specifies the character set used for automatic translation between applications and SQL. If you do not specify a character set the default is DEC_MCS. See the Oracle Rdb SQL Reference Manual for a list of allowable character sets. 3 domain-name You can specify an SQL data type directly or name a domain. If you name a domain, the parameter inherits the data type of the domain. 3 FIXED The FIXED, NULL TERMINATED BYTES, and NULL TERMINATED CHARACTERS clauses tell the module processor how to interpret C language text fields. Example 3 shows how the size of the text field you declare varies according to which of the three interpretation options you select. If you specify FIXED, the module processor interprets CHAR fields from the dictionary as fixed-length character strings. 3 FROM_path_name Specifies the data dictionary path name of a data dictionary record definition. You can use this clause to retrieve data definitions from the dictionary. The data dictionary record definition that you specify cannot contain any OCCURS clauses or arrays. You must specify a data dictionary record definition that contains only valid SQL or Oracle Rdb data types. The FROM path-name clause cannot be used in a second-level record specification (a record-type that you specify within record- type). 3 IDENTIFIER_CHARACTER_SET Specifies the character set used for object names such as cursor names and table names. If you do not specify a character set in this clause or in the NAMES ARE clause, the default is DEC_MCS. This clause overrides the character set specified in the NAMES ARE clause. The specified character set must contain ASCII. NOTE If the dialect or character sets are not specified in the module header, SQL uses the RDB$CHARACTER_SET logical name to determine the character sets to be used by the database. See the Oracle Rdb SQL Reference Manual for more detail regarding the RDB$CHARACTER_SET logical name. The RDB$CHARACTER_SET logical name is deprecated and will not be supported in a future release. 3 INDICATOR_ARRAY_OF Specifies a one-dimensional array of elements with one of the data types shown in the exact-numeric-type diagram. An indicator array provides indicator parameters for fields in the host structure. The indicator array must have at least as many elements in it as the record definition has. You cannot use an indicator array as a record or contain it within a record. In other words, the INDICATOR ARRAY OF clause cannot be used in a second-level record specification (a record- type that you specify within record-type). You cannot explicitly refer to individual elements in an indicator array. For this reason, you cannot use indicator arrays in UPDATE statements or WHERE clauses. 3 item-name Specifies the name of an item in a record. Do not give the same name for two record items at the same level in the same record declaration. When SQL statements within a procedure refer to an item name within a subrecord in the same procedure as a parameter declaration, they must fully qualify the item name with the record name and all intervening subrecord names. Separate record names from item names with periods. 3 KEYWORD_RULES Controls whether or not identifiers can be keywords. If you specify SQL92, SQL99, SQL89, or MIA, you cannot use keywords as identifiers, unless you enclose them in double quotation marks. If you specify SQLV40, you can use keywords as identifiers. The default is SQLV40. Use the KEYWORD RULES clause, rather than the ANSI_IDENTIFIER qualifier, because the qualifier will be deprecated in a future release. 3 LANGUAGE A keyword that specifies the name of the host language in which the program is written. This program calls the procedures in the module. Specify GENERAL for languages that do not have a corresponding keyword in the LANGUAGE clause. The language identifier determines: o The kinds of data types that the SQL module processor considers valid in the module's formal parameter declarations. If a language does not support a data type equivalent to some SQL data type, the SQL module processor generates a warning message when it encounters the data type in a formal parameter. (A formal parameter is the name in an SQL module procedure declaration that represents the corresponding actual parameter in a host language call to the SQL module procedure.) For example, SQL supports the BIGINT data type, but PL/I does not. The module processor generates a warning message when it encounters a BIGINT formal parameter in an SQL module that specifies the PL/I language in the LANGUAGE section. o The default mechanism for passing parameters to and from a host language source file. Parameters are always passed by the default passing mechanism for the language specified in the language clause. The following table shows those defaults. Table 3 Default Passing Mechanism for Host Languages to SQL Modules Language Passing Mechanism Ada By reference BASIC CHAR by descriptor; all others by reference C By reference COBOL By reference FORTRAN CHAR, SQLCA, SQLDA by descriptor; all others by reference Pascal By reference PL/I By reference GENERAL By reference o The default data type that SQL expects for certain actual parameters. In COBOL, for example, if a DOUBLE PRECISION formal parameter is declared in an SQL module procedure, the procedure expects the parameter to be passed from the calling module as D_FLOAT rather than G_FLOAT because COBOL does not support G_FLOAT. Similarly, in C, if a CHAR(n) formal parameter is declared in an SQL module procedure, the procedure expects the parameter to be passed from the calling module as an ASCIZ string with a length of (n+1). 3 LITERAL_CHARACTER_SET Specifies the character set for literals that are not qualified by a character set or national character set. If you do not specify a character set in this clause or in the NAMES ARE clause, the default is DEC_MCS. This clause overrides the character set for unqualified literals specified in the NAMES ARE clause. See the Oracle Rdb SQL Reference Manual for a list of the allowable character sets. 3 MODULE An optional name for the module. If you do not supply a module name, the default name is SQL_MODULE. Use any valid operating system name. (See the Oracle Rdb SQL Reference Manual for more information on user-supplied names.) However, the name must be unique among the modules that are linked together to form an executable image. 3 NAMES_ARE Specifies the character set used for the default, identifier, and literal character sets for the module. This clause also specifies the character string parameters that are not qualified by a character set or national character set. If you do not specify a character set, the default is DEC_MCS. The character set specified in this clause must contain ASCII. 3 NATIONAL_CHARACTER_SET Specifies the character set for literals qualified by the national character set and for parameters defined as a national character data type (NCHAR, NCHAR VARYING). If you do not specify a character set in this clause, the default is DEC_MCS. See the Oracle Rdb SQL Reference Manual for a list of the allowable character sets. 3 NULL_TERMINATED_BYTES Specifies that text fields from the dictionary are null- terminated. The module processor interprets the length field in the dictionary as the number of bytes in the string. If n is the length in the dictionary, then the number of data bytes is n-1 and the length of the string is n bytes. In other words, the module processor assumes that the last character of the string is for the null terminator. Thus, a field that the dictionary lists as 10 characters can hold only a 9- character SQL field from the C module language. (Other module languages could fit a 10-character SQL field into it.) If you do not specify a character interpretation option, NULL TERMINATED BYTES is the default. 3 NULL_TERMINATED_CHARACTERS Specifies that CHAR fields from the dictionary are null- terminated, but the module processor interprets the length field as a character count. If n is the length in the dictionary, then the number of data bytes is n, and the length of the string is n+1 bytes. 3 parameter-name The name for a formal parameter. Use any valid SQL name. See the Oracle Rdb SQL Reference Manual for more information on user- supplied names. Formal parameter names do not have to be the same as the host language variables for the actual parameters to which they correspond. However, making the names the same is a useful convention for keeping track of which parameter corresponds to which host language variable. SQLCA, SQLCODE, SQLDA, SQLDA2, and SQLSTATE are special-purpose parameters and do not require user-supplied names (although you can optionally specify a parameter name with SQLDA or SQLDA2). There are three ways to specify a valid SQL data type for the formal parameter: o data-type o domain-name o record-type 3 PARAMETER_COLONS If you use the PARAMETER COLONS clause, all parameter names must begin with a colon (:). This rule applies to both declarations and references of module language procedure parameters. If you do not use this clause, no parameter name can begin with a colon. The current default behavior is no colons are used. However, this default is deprecated syntax. In the future, colons will be the default because it allows processing of ANSI-standard modules. Use the PARAMETER COLONS clause, rather than the ANSI_PARAMETERS qualifier, because the qualifier will be deprecated in a future release. 3 PROCEDURE Specifies the name of a procedure. Use any valid OpenVMS name.) (See the Oracle Rdb SQL Reference Manual for more information on user-supplied names.) The procedure name is used in host language calls to specify a particular procedure. In addition to a procedure name, a procedure in an SQL module must contain one or more parameter declarations and an SQL statement. 3 QUIET_COMMIT Syntax options: QUIET COMMIT ON | QUIET COMMIT OFF The QUIET COMMIT ON clause disables error reporting for the COMMIT and ROLLBACK statements if either statement is executed when no transaction is active. The QUIET COMMIT OFF clause enables error reporting for the COMMIT and ROLLBACK statements if either statement is executed when no transaction is active: MODULE TXN_CONTROL LANGUAGE BASIC PARAMETER COLONS QUIET COMMIT ON PROCEDURE S_TXN (SQLCODE); SET TRANSACTION READ WRITE; PROCEDURE C_TXN (SQLCODE); COMMIT; The QUIET COMMIT OFF clause is the default. 3 QUOTING_RULES Controls whether double quotation marks are interpreted as string literals or delimited identifiers. If you specify SQL99, SQL92, SQL89, or MIA, SQL interprets double quotation marks as delimited identifiers. If you specify SQLV40, SQL interprets double quotation marks as literals. The default is SQLV40. Use the QUOTING RULES clause, rather than the ANSI_QUOTING qualifier, because the qualifier will be deprecated in a future release. 3 RECORD...END_RECORD Specifies the beginning and end of the record that you are supplying in a module language parameter declaration. A record definition cannot contain an SQLDA, an SQLDA2, an SQLCODE, an SQLCA, or an SQLSTATE. 3 record-type You can pass records and indicator arrays to SQL module language procedures using the record-type clause. You can also pass records and indicator arrays to SQL module language procedures and retrieve data dictionary record declarations using the record-type clause. If a record reference has an indicator, it must be an indicator array. Specify the INDICATOR ARRAY OF clause instead of an item name or path name. The following example shows the use of record structures and indicator arrays in an SQL module language program. Because parameters in the module are preceded by colons, you must include the PARAMETER COLONS clause in the module header. MODULE employee_module LANGUAGE pascal AUTHORIZATION pers PARAMETER COLONS DECLARE pers ALIAS FOR FILENAME mf_personnel DECLARE WORK_STATUS_CURSOR CURSOR FOR SELECT * FROM PERS.WORK_STATUS PROCEDURE OPEN_WORK_STATUS SQLCODE; OPEN WORK_STATUS_CURSOR; PROCEDURE CLOSE_WORK_STATUS SQLCODE; CLOSE WORK_STATUS_CURSOR; PROCEDURE FETCH_EMPS_TO_DEPS_CURSOR SQLCODE, :work_status_rec record status_code PERS.work_status.STATUS_CODE_DOM status_name PERS.work_status.STATUS_NAME_DOM status_type PERS.work_status.STATUS_DESC_DOM end record :ind_array record indicator array of 3 SMALLINT end record ; FETCH WORK_STATUS_CURSOR INTO :work_status_rec INDICATOR :ind_array; 3 RIGHTS Specifies whether or not a module must be executed by a user whose authorization identifier matches the module authorization identifier. If you specify RESTRICT, SQL bases privilege checking on the default authorization identifier. The default authorization identifier is the authorization identifier of the user who compiles a module unless you specify a different authorization identifier using an AUTHORIZATION clause in the module. The RESTRICT option causes SQL to compare the user name of the person who executes a module with the default authorization identifier and prevent any user other than one with the correct authorization identifier from invoking that module. All applications that use multischema will be the invoker by default. If you specify INVOKER, SQL bases the privilege on the authorization identifier of the user running the module. The default is INVOKER. Use the RIGHTS clause, rather than the ANSI_AUTHORIZATION qualifier, because the qualifier will be deprecated in a future release. 3 SCHEMA Specifies the default schema name for the module. The default schema is the schema to which SQL statements refer if those statements do not qualify table and other schema names with an authorization identifier. If you do not specify a default schema name for a module, the default schema name is the same as the authorization identifier. Using the SCHEMA clause, separate SQL modules can each declare different schemas as default schemas. This can be convenient for an application that needs to refer to more than one schema. By putting SQL statements that refer to a schema in the appropriate module's procedures, you can minimize tedious qualification of schema element names in those statements. When you specify SCHEMA schema-name AUTHORIZATION authorization- name, you specify the schema name and the schema authorization identifier for the module. The schema authorization identifier is considered the owner and creator of the schema and everything in it. When the FIPS flagger is enabled for entry-level SQL92 or lower, the SCHEMA clause (by itself or used with the AUTHORIZATION clause) is flagged as nonstandard ANSI syntax. If procedures in the SQL module always qualify table names with an authorization identifier, the SCHEMA clause has no effect on SQL statements in the procedures. 3 SQLCA A formal parameter for the SQLCA (see the Oracle Rdb SQL Reference Manual for more information on the SQLCA). The calling program module must declare a record that corresponds to the structure of the SQLCA and specify that record declaration as the calling parameter for the SQLCA formal parameter. Specifying SQLCA as a formal parameter is an alternative to specifying SQLCODE. Using SQLCA instead of SQLCODE lets the calling program module take advantage of the information SQL puts in the third element of the SQLERRD array in the SQLCA. Future versions of SQL may use the SQLCA for additional information. 3 SQLCODE A formal parameter that SQL uses to indicate the execution status of the SQL statement in the procedure. The SQLCODE formal parameter does not require a data type declaration; SQL automatically declares SQLCODE with an INTEGER data type. However, the calling program module must still declare an integer variable for the actual parameter that corresponds to SQLCODE. The SQLCODE parameter must be passed by reference. Oracle Rdb recommends that you use the SQLSTATE status parameter rather than SQLCODE. SQLSTATE complies with ANSI/ISO SQL standard and SQLCODE may be deprecated in a future release of Oracle Rdb. See the Oracle Rdb SQL Reference Manual for more information about SQLCODE. 3 SQLDA_SQLDA2 A formal parameter for the SQLDA or SQLDA2 (see the Oracle Rdb SQL Reference Manual for more information on the SQLDA and SQLDA2). The calling program module must declare a record that corresponds to the structure of the SQLDA or SQLDA2 and specify that record declaration as the calling parameter for the SQLDA or SQLDA2 formal parameter. You can optionally precede SQLDA or SQLDA2 in the parameter declaration with another name the SQL statement in the module procedure can use to refer to the SQLDA or SQLDA2. 3 SQLSTATE A formal parameter that SQL uses to indicate the execution status of the SQL statement in the procedure. The SQLSTATE formal parameter does not require a data type declaration; SQL automatically declares SQLSTATE with a CHAR(5) data type. However, the calling program module must still declare a character variable for the actual parameter that corresponds to SQLSTATE. The SQLSTATE parameter must be passed by reference. Oracle Rdb recommends that you use the SQLSTATE status parameter rather than SQLCODE. SQLSTATE complies with the ANSI/ISO SQL standard and SQLCODE may be deprecated in a future release of Oracle Rdb. 3 VIEW_UPDATE_RULES Specifies whether or not the SQL module processor applies the ANSI/ISO standard for updatable views to all views created during compilation. If you specify SQL92, SQL99, SQL89, or MIA, the SQL module processor applies the ANSI/ISO standard for updatable views to all views created during compilation. Views that do not comply with the ANSI/ISO standard for updatable views cannot be updated. The default is SQLV40. The ANSI/ISO standard for updatable views requires the following conditions to be met in the SELECT statement: o The DISTINCT keyword is not specified. o Only column names can appear in the select list. Each column name can appear only once. Functions and expressions such as max(column_name) or column_name +1 cannot appear in the select list. o The FROM clause refers to only one table. This table must be either a base table or a derived table that can be updated. o The WHERE clause does not contain a subquery. o The GROUP BY clause is not specified. o The HAVING clause is not specified. If you specify SQLV40, SQL does not apply the ANSI/ISO standard for updatable views. Instead, SQL considers views that meet the following conditions to be updatable: o The DISTINCT keyword is not specified. o The FROM clause refers to only one table. This table must be either a base table or a view that can be updated. o The GROUP BY clause is not specified. o The HAVING clause is not specified. 2 Examples Example 1: Calling an SQL module procedure from a Pascal program The following example is a Pascal program that calls a procedure in an SQL module file: PROGRAM list_employees(OUTPUT); { Program to list employees' names whose last name matches a LIKE predicate. Note the following: 1) The input parameter (like_string) to the SELECT expression in the DECLARE CURSOR is supplied on the OPEN_CURSOR call. 2) The output parameters are returned on each FETCH_INTO call. 3) The cursor is closed after the desired rows are processed, so that it will be positioned properly in subsequent operations. } TYPE LAST_NAME = PACKED ARRAY[1..14] OF CHAR; FIRST_NAME = PACKED ARRAY[1..10] OF CHAR; VAR { Variable data } sqlcode : INTEGER := 0; emp_last : LAST_NAME; emp_first: FIRST_NAME; like_string : LAST_NAME := 'T_ _ _ _ _ _ _ _ _ _ _ _ _'; { Declarations of entry points in the SQL module } PROCEDURE SET_TRANS (VAR sqlcode : INTEGER); EXTERNAL; PROCEDURE OPEN_CURSOR (VAR sqlcode: INTEGER; name : LAST_NAME); EXTERNAL; PROCEDURE FETCH_INTO (VAR sqlcode : INTEGER; VAR last : LAST_NAME; VAR first : FIRST_NAME); EXTERNAL; PROCEDURE CLOSE_CURSOR (VAR sqlcode : INTEGER); EXTERNAL; PROCEDURE ROLLBACK_TRANS (VAR sqlcode : INTEGER); EXTERNAL; BEGIN SET_TRANS (sqlcode); { Start a read-only transaction.} OPEN_CURSOR (sqlcode, like_string);{ Open the cursor, supplying } { the string to match against. } WRITELN('Matching Employees:'); { Print header. } REPEAT { Iterate matching names. } BEGIN FETCH_INTO (sqlcode, emp_last, emp_first);{ Fetch the next name. } IF sqlcode = 0 THEN WRITELN(emp_first, emp_last); { Print employee information. } END UNTIL sqlcode <> 0; IF sqlcode <> 100 { Print any error information. } THEN WRITELN ('SQL error code = ', sqlcode); CLOSE_CURSOR (sqlcode); { Finish the cursor operation. } ROLLBACK_TRANS (sqlcode); { Finish the transaction. } END. Here is the SQL module file that this program calls: MODULE employees LANGUAGE PASCAL AUTHORIZATION SQL_USER ALIAS RDB$DBHANDLE DECLARE ALIAS FOR FILENAME PERSONNEL DECLARE names CURSOR FOR SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE match_string PROCEDURE SET_TRANS SQLCODE; SET TRANSACTION READ ONLY; PROCEDURE OPEN_CURSOR SQLCODE match_string CHAR(14); OPEN names; PROCEDURE FETCH_INTO SQLCODE l_name CHAR(14) f_name CHAR(10); FETCH names INTO l_name, f_name; PROCEDURE CLOSE_CURSOR SQLCODE; CLOSE names; PROCEDURE ROLLBACK_TRANS SQLCODE; ROLLBACK; Example 2: Calling an SQL module procedure from a C program The following example is a C program that calls a procedure that is in an SQL module file: /* C program to list employees' names where the last name matches a LIKE predicate. Note the following: 1) The input parameter (like_string) to the SELECT expression in the DECLARE CURSOR is supplied on the OPEN_CURSOR call. 2) The output parameters are returned on each FETCH_INTO call. 3) The cursor is closed after the desired rows are processed, so that it will be positioned properly in subsequent operations. */ #include #pragma dictionary "name" typedef struct name NAME_TYPE; extern void FETCH_INTO (int *sqlcode, NAME_TYPE *name_record); typedef char LAST_NAME[15]; typedef int *SQLCODE; /* Declarations of entry points in the SQL module */ extern void SET_TRANS (int *sqlcode); extern void OPEN_CURSOR (int *sqlcode, LAST_NAME name); extern void CLOSE_CURSOR (int *sqlcode); extern void ROLLBACK_TRANS (int *sqlcode); void main () { int sqlcode = 0; NAME_TYPE name_record; LAST_NAME like_string = "T%"; SET_TRANS (&sqlcode); /* Start a read-only transaction. */ if (sqlcode != 0) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); OPEN_CURSOR (&sqlcode, like_string); /* Open the cursor, supplying */ /* the string to match against. */ if (sqlcode != 0) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); printf ("Matching Employees:\n"); /* Print header. */ do /* Iterate matching names. */ { FETCH_INTO (&sqlcode, &name_record);/* Fetch the next name. */ if (sqlcode == 0) printf ("%s%s\n", name_record.f_name, name_record.l_name); } /* Print employee information. */ while (sqlcode == 0); if (sqlcode != 100) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); CLOSE_CURSOR (&sqlcode); /* Complete the cursor operation. */ if (sqlcode != 0) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); ROLLBACK_TRANS (&sqlcode); /* Finish the transaction. */ if (sqlcode != 0) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); } Here is the SQL module file that this program calls: MODULE employees LANGUAGE C AUTHORIZATION SQL_USER ALIAS RDB$DBHANDLE DECLARE ALIAS FOR PATHNAME 'MF_PERSONNEL' DECLARE names CURSOR FOR SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE match_string PROCEDURE SET_TRANS SQLCODE; SET TRANSACTION READ ONLY; PROCEDURE OPEN_CURSOR SQLCODE match_string CHAR(14); OPEN names; PROCEDURE FETCH_INTO SQLCODE, name_record RECORD FROM 'name' END RECORD; FETCH names INTO name_record; PROCEDURE CLOSE_CURSOR SQLCODE; CLOSE names; PROCEDURE ROLLBACK_TRANS SQLCODE; ROLLBACK; 2 Character_Parameters To ensure that you specify the length of character string parameters correctly, use the following guidelines: o For C host language programs that call SQL modules declared with LANGUAGE C, any character parameters that correspond to character data type columns must be defined as the length of the longest valid column value in octets, plus 1 octet to allow for the null terminator. o For other host language programs (or C host language programs that call SQL modules declared with LANGUAGE GENERAL), any character parameters that correspond to character data type columns must be defined as the length of the longest valid column value in octets. o When calculating the length of the longest valid column value, you must take into consideration the number of octets for each character in the character set of the column and whether the SQL module language interprets the length of columns in characters or octets. A program can control how the SQL module language interprets the length of columns in the following ways: - The CHARACTER LENGTH clause of the module header or DECLARE MODULE statement - The DIALECT clause of the module header or DECLARE MODULE statement - For dynamic SQL, the SET CHARACTER LENGTH statement 2 Host_Language_Data_Types The SQL data type specified for the formal parameter in a module must be equivalent to the data type of the host language variable declaration for the actual parameter. If the formal parameter and actual parameter are not declared with equivalent data types, SQL can give unpredictable results. However, host languages typically do not support the same set of data types that SQL supports. To work with a column in a database defined with a data type not supported in a host language, the module must declare formal parameters of a data type that the host language supports. SQL automatically converts between the data type of the database column and the formal parameter when it processes the SQL statement in a procedure. The following table shows the OpenVMS data types that SQL requires for actual parameters when you declare formal parameters for each SQL data type. Table 4 SQL and Corresponding OpenVMS Data Types for Module Language Formal Parameter Data Type Requires Actual Parameter of OpenVMS Data Type CHAR (n) Character string (DSC$K_DTYPE_T) CHAR (n), Character string (DSC$K_DTYPE_T) qualified by character set NCHAR (n) Character string (DSC$K_DTYPE_T) VARCHAR (n) Varying character string (DSC$K_DTYPE_VT) VARCHAR (n), Varying character string (DSC$K_DTYPE_VT) qualified by character set NCHAR VARYING Varying character string (DSC$K_DTYPE_VT) (n) LONG VARCHAR Varying character string (DSC$K_DTYPE_VT) TINYINT [(n)] Signed byte integer (DSC$K_DTYPE_B) SMALLINT [(n)] Signed word integer (DSC$K_DTYPE_W) INTEGER [(n)] Signed longword integer (DSC$K_DTYPE_L) BIGINT [(n)] Signed quadword integer (DSC$K_DTYPE_Q) QUADWORD [(n)] Signed quadword integer (DSC$K_DTYPE_Q) DECIMAL Packed decimal string (DSC$K_DTYPE_P) [(n)[,(n)]] NUMERIC Numeric string, left separate sign (DSC$K_DTYPE_ [(n)[,(n)]] NL) FLOAT [(n)] Single- or double-precision, floating-point number, depending on n. For single-precision: DSC$K_DTYPE_F or DSC$K_DTYPE_FS and for double- precision: DSC$K_DTYPE_G, DSC$K_DTYPE_D, or DSC$K_DTYPE_FT. REAL Single-precision, floating-point number (DSC$K_ DTYPE_F or DSC$K_DTYPE_FS). DOUBLE Double-precision, floating-point number (DSC$K_ PRECISION DTYPE_G, DSC$K_DTYPE_D, or DSC$K_DTYPE_FT). (DATE) No equivalent OpenVMS data type; two-longword array DATE ANSI No equivalent OpenVMS data type; two-longword array DATE VMS Absolute date and time (DSC$K_DTYPE_ADT) TIME No equivalent OpenVMS data type; two-longword array TIMESTAMP No equivalent OpenVMS data type; two-longword array INTERVAL (Year- No equivalent OpenVMS data type; two-longword month) array INTERVAL (Day- No equivalent OpenVMS data type; two-longword time) array LIST OF BYTE Not supported VARYING 2 SQLMOD_Command_Line You can define a symbol to make invoking the SQL module processor easier. For example: $ SQLMOD == "$SQL$MOD" You then can invoke the SQL module processor with or without a module file specification: o If you invoke the SQL module processor without a module file specification, the module processor prompts you for it. For example: $ SQLMOD INPUT FILE> module-file-specification o If you invoke the SQL module processor with a module file specification as part of the DCL command line, SQL starts processing your module file immediately after you press the Return key. For example: $ SQLMOD module-file-specification Either way, there are several qualifiers you can specify with the file specification that control how SQL processes the module file. The syntax diagram shows the format for those qualifiers. 3 Format (B)0module-file-spec-qual =    SQLMOD qqqq> module-file-spec qwqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   tq> <context-file-name> qqu   tq> module-qualifiers-1 qqu   mq> module-qualifiers-2 qqj    (B)0module-qualifiers-1 =    qqwwqwqq> no-qualifiers-1 qqwqqqqqqqqqqqqqqqqqwwq>   xx mqq> no-qualifiers-2 qqj xx  xtq> /ARCHITECTURE = architecture_options qqux  xtq> /C_STRING = c-string-options qqqqqqqqqqux   xtq> /CONSTRAINT_MODE = qwq> IMMEDIATE qqqqqux   xx tq> DEFERRED qqqqqqux   xx tq> OFF qqqqqqqqqqqux   xx mq> ON qqqqqqqqqqqqux   xtq> /CONTEXT = qwq> NONE qqqqqqqqqqqqqqqqqqux   xx tq> ALL qqqqqqqqqqqqqqqqqqqux   xx   mq> procedure-list qqqqqqqqux  xtq> /FLOAT = qwq> D_FLOAT qqqqqqqqqqqqqqqqqux xx   tq> G_FLOAT qqqqqqqqqqqqqqqqqux xx mq> IEEE_FLOAT qqqqqqqqqqqqqux  xtq> /USER_DEFAULT = qqqqqqqqqqqqux   xmq> /PASSWORD_DEFAULT = qqqqqqqqjx   mqqqqqqqqqqqqqqqqqqqqq / wqqqqwqwq> ALIGN_RECORDS qqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>  m NO j tq> C_PROTOTYPES qqwqqqqqqqqqqqqqqqqqqqqqwqqqqu x mq> = <file-name> qqj x  tq> CONNECT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> EXTERNAL_GLOBALS qqqqqqqqqqqqqqqqqqqqqqqqqu   tq> FLAG_NONSTANDARD qqwqqqqqqqqqqqqqqqqqqqwu   x tq> = MIA qqqqqqqqqux   x tq> = SQL89 qqqqqqqux   x mq> = SQL92_ENTRY qjx   tq> G_FLOAT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> INITIALIZE_HANDLES qqqqqqqqqqqqqqqqqqqqqqqu   tq> LIST qqqqwqqqqqqq>qqqqqqqqqwqqqqqqqqqqqqqqu   x mq> = <file-spec> j x  tq> LOWERCASE_PROCEDURE_NAMES qqqqqqqqqqqqqqqqu  mq> MACHINE_CODE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0no-qualifiers-2 =    q> / wq>qqwqwq> OBJECT qqwqqqqqqq>qqqqqqqqqwqqqqqqqqqqqqqqqqwqqq>  m NO j x mq> = <file-spec> j   x   tq> PACKAGE_COMPILATION qqqqqqqqqqqqqqqqqqqqqqqqu   tq> PARAMETER_CHECK qqqqqqqqqqqqqqqqqqqqqqqqqqqu  tq> PRAGMA = ( q> IDENT = string-literal q> ) qqu tq> PROTOTYPES qqwqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqu x mq> = <prototypesfile> qqj x  tq> QUERY_ESTIMATES qqqqqqqqqqqqqqqqqqqqqqqqqqqu  tq> QUIET_COMMIT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu  tq> TRANSACTION_DEFAULT qwqqqqqqqqqqqqqqqqqqwqqqu   x tq> = IMPLICIT qqqqu   x   x mq> = DISTRIBUTED qj   x   mq> WARN qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqj     m> = ( w> warning-option w> ) qqj    mqqqqqq , WARNING qqqqqqwqqwq>   x tq> NOWARNING qqqqu x   x tq> DEPRECATE qqqu x   x mq> NODEPRECATE qj x   mqqqqqqqqq , GENERIC qwqq>  tq> HOST qqu   tq> EV4 qqu   tq> EV5 qqu   tq> EV56 qqu  tq> PCA56 qqu tq> EV6  qqu tq> EV67 qqu tq> EV68 qqu mq> EV7 qqj       (B)0c-string-options =    qwwqqwqqqqqqw> BLANK_FILL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqw>  xx m> NO qj x   xmqqwqqqqqqw> FIXED_CDD_STRINGS qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x m> NO qj x   m> ( wqqqqqw> BLANK_FILL q> , wqqqqqqw> FIXED_CDD_STRINGS q> ) j   m> NO j m> NO qj    (B)0module-qualifiers-2 =    qqqqwwq> /database-options qqqqqqqqqqqqqqqqqqqqqqqwwq>   xtq> /optimization_options qqqqqqqqqqqqqqqqqqqux   xtq> /QUERY_TIME_LIMIT = qqqqqux   xtq> /QUERY_MAX_ROWS = qqqqqqqqqqux   xtq> /QUERY_CPU_TIME_LIMIT = qux   xmq> /ROLLBACK_ON_EXIT qqqqqqqqqqqqqqqqqqqqqqqjx   mqqqqqqqqqqqqqqqqqqqqq OPTIMIZATION_LEVEL= qwqqqq> DEFAULT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>  mqq> ( qwwq> AGGRESSIVE_SELECTIVITY qqwqqwq ) qj   xtq> FAST_FIRST  qqu x     xtq> SAMPLED_SELECTIVITY qqqqu x     xmq> TOTAL_TIME  qqqqqqqqqqqqj x   mqqqqqq , ELN qqqqqqqqqqqqqqqqqqqqqwqq>   tqq> NSDS qqqqqqqqqqqqqqqqqqqqu   tqq> rdb-options qqqqqqqqqqqqqu   tqq> VIDA qqqqqqqqqqqqqqqqqqqqu   tqq> VIDA=V1 qqqqqqqqqqqqqqqqqu   tqq> VIDA=V2 qqqqqqqqqqqqqqqqqu   tqq> VIDA=V2N qqqqqqqqqqqqqqqqu   tqq> NOVIDA qqqqqqqqqqqqqqqqqqu   tqq> DBIV1 qqqqqqqqqqqqqqqqqqqu   tqq> DBIV31 qqqqqqqqqqqqqqqqqqu   mqq> DBIV70 qqqqqqqqqqqqqqqqqqj    (B)0 rdb-options =    qwq> RDBVMS qqwqq>  tq> RDB030 qqu   tq> RDB031 qqu   tq> RDB040 qqu   tq> RDB041 qqu  tq> RDB042 qqu tq> RDB050 qqu tq> RDB051 qqu tq> RDB060 qqu tq> RDB061 qqu tq> RDB070 qqu mq> RDB071 qqj (B)0procedure-list =    qq> ( qwq> <procedure> wqqqqqqqqqqqqqqqqqqqwwq> ) q>   x mqq> : <entry-name> jx   mqqqqqqqqqqqqqqq , ATTACH 'FILENAME intro_personnel'; SQL> DECLARE MY_CURSOR cont> TABLE CURSOR FOR cont> SELECT * FROM EMPLOYEES; SQL> OPEN MY_CURSOR; SQL> SHOW SQLCA; SQLCA: SQLCAID: SQLCA SQLCABC: 128 SQLCODE: 0 SQLERRD: [0]: 0 [1]: 0 [2]: 100 [3]: 16 [4]: 0 [5]: 0 SQLWARN0: SQLWARN1: SQLWARN2: SQLWARN3: SQLWARN4: SQLWARN5: SQLWARN6: SQLWARN7: 4 QUERY_MAX_ROWS Limits the number of records returned during query processing by counting the number of rows returned by the query and returning an error message if the query exceeds the total number of rows specified. The default is an unlimited number of record fetches. Dynamic SQL options are inherited from the compilation qualifier. 4 QUERY_TIME_LIMIT Limits the number of records returned during query processing by counting the number of seconds used to process the query and returning an error message if the query exceeds the total number of seconds specified. The default is unlimited time for the query to compile. Dynamic SQL options are inherited from the compilation qualifier. 4 ROLLBACK_ON_EXIT Rolls back outstanding transactions when a program exits from SQL. On OpenVMS outstanding transactions are committed when a program exits from SQL by default. Therefore, if you want to roll back changes, specify this qualifier on the command line. 4 TRANSACTION_DEFAULT Syntax options: TRANSACTION_DEFAULT=IMPLICIT TRANSACTION_DEFAULT=DISTRIBUTED NOTRANSACTION_DEFAULT Specifies when SQL starts a transaction and how SQL handles default distributed transactions. You can specify the following options: o TRANSACTION_DEFAULT=IMPLICIT Causes SQL to start a transaction when you issue either a SET TRANSACTION statement or the first executable SQL statement in a session. o TRANSACTION_DEFAULT=DISTRIBUTED Causes SQL to use the distributed transaction identifier (TID) for the default distributed transaction established by the DECdtm system service SYS$START_TRANS. Using this option eliminates the need to declare context structures in host language programs and to pass context structures to SQL module procedures. Because it closes all cursors, it also eliminates the need to call the SQL_CLOSE_CURSORS routine. You must explicitly call the DECdtm system services when you use this option. This option provides support for the Structured Transaction Definition Language (STDL) of the Multivendor Integration Architecture (MIA) standard. If you specify the TRANSACTION_DEFAULT=DISTRIBUTED option with the CONTEXT qualifier, you must declare a context structure and pass the context structure to the statements named in the CONTEXT qualifier or, if you specify CONTEXT=ALL, to most executable statements involved in the distributed transaction. See the Oracle Rdb SQL Reference Manual for information about which executable statements do not require a context structure. o NOTRANSACTION_DEFAULT Prevents SQL from starting a transaction unless you execute a SET TRANSACTION statement. If you use this qualifier and issue an executable statement without first issuing a SET TRANSACTION statement, SQL returns an error. The default is TRANSACTION_DEFAULT=IMPLICIT. 4 USER_DEFAULT Specifies the user name at compile time. If you use the USER DEFAULT clause of the DECLARE ALIAS statement, you use this qualifier to pass the compile-time user name to the program. 4 WARNING Syntax options: WARNING NOWARNING You can use combinations of the warning options to specify which warning messages the SQL module processor writes. If you specify only a single warning option, you do not need the parentheses. The WARNING and NOWARNING qualifiers specify whether or not the SQL module processor writes informational and warning messages. 4 warning-option Specifies whether the SQL module processor writes informational and warning messages to your terminal, a list file, or both. The WARN qualifier is the default. You can specify two warning options with the WARN qualifier to customize message output. You cannot specify warning options if you specify the NOWARN qualifier. 3 Example Example 1: Compiling and linking a program with an SQL module $ SQLMOD :== $SQL$MOD $ SQLMOD LIST_EMP_PASMOD.SQLMOD $ PASCAL LIST_EMP.PAS $ ! This LINK command requires that the logical name $ ! LNK$LIBRARY is defined as SYS$LIBRARY:SQL$USER.OLB $ LINK LIST_EMP.OBJ, LIST_EMP_PASMOD.OBJ $ RUN LIST_EMP.EXE Matching Employees: Alvin Toliver Louis Tarbassian