HELPLIB.HLB  —  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.

1  –  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   
   lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   
   mqqwqqqqqqqqqqqqqqqqqqqqqqqwqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqk   
      mq> char-set-options qqqj                               x   
   lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   
   mqq> LANGUAGE language-name qqwqqqqqqqqqqqqqqqqqqqqqqqqqwqk    
                                 m> CATALOG <catalog-name> j x    
    lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj    
    mqwqqqqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqqk  
      m> SCHEMA <schema-name> qj m> AUTHORIZATION <auth-id> j  x  
    lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  
    mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk   
      mqq> PRAGMA (module-pragma-list) qj mqq> module-language-options qqj x
    lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
    mqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqwq> procedure-clause qqwqqqqqq>
      mwq> declare-statement qqwqj     mqqqqqqqqqqq<qqqqqqqqqqj                
       mqqqqqqqqqqq<qqqqqqqqqqqj

  (B)0environment =         

  qqwqq> 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 <qqqqqqqqqqqqqqqqqqqqj                
  mwqwqqqqqqqqqqqqqqq> 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<qqqqqqqqqqqqqqqqqqqqqqqj   
                                                        

  (B)0declare-statement =                         
                                              
  qwq> 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 , <qqqqqqqqqqqqqqj

  (B)0module-language-options =                                 
                                                            
  qwqwqqq> 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 <qqqqqqqqqqqqqqqqqqqqqqqj

  (B)0procedure-clause =                      
                                          
  qqwq> PROCEDURE <procedure-name> qqqqqqk
    x lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqj
    x mqwq> param-decl-list qqqqqwq> ; qqk
    x   mq> ( param-decl-list ) qj       x
    x lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqj
    x mqwq> simple-statement qqqwq> ; qwq>
    x   mq> compound-statement qj      x  
    mqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqj  

  (B)0param-decl-list =      
                         
  qwqq> param-decl qqqwq>
   mqqwqqqqq <qqqqqwqqj  
      mqqqq , <qqqqj     

  (B)0param-decl =                                                  
                                                                
  qwq> <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<qqqqqqqqj  x                
   x          mqq , <qj               x                
   tq> 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            
                                                

3  –  Arguments

3.1  –  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.2  –  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.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.4  –  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.5  –  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.6  –  char-data-types

    Refer to the Oracle Rdb SQL Reference Manual for information
    about the character data types that SQL supports.

3.7  –  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.8  –  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.9  –  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.10  –  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.11  –  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.12  –  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.13  –  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.14  –  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.15  –  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.16  –  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.17  –  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.18  –  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.19  –  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.20  –  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.21  –  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.22  –  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.23  –  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.24  –  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.25  –  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.26  –  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.27  –  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.28  –  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.29  –  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.30  –  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.31  –  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.32  –  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.33  –  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.34  –  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.35  –  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.36  –  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.37  –  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.38  –  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.39  –  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.40  –  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.41  –  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.42  –  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.43  –  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.

4  –  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 <stdio.h>

    #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;

5  –  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

6  –  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

7  –  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.

7.1  –  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 = <username> qqqqqqqqqqqqux   
    xmq> /PASSWORD_DEFAULT = <password> qqqqqqqqjx   
    mqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj   
                                                     

  (B)0no-qualifiers-1 =                                             
                                                                
  q> / 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 , <qqqqqqqj

  (B)0warning-option    =             
                                  
  qqqwqqqwq> WARNING qqqqqqwqqwq> 
     x   tq> NOWARNING qqqqu  x   
     x   tq> DEPRECATE  qqqu  x   
     x   mq> NODEPRECATE  qj  x   
     mqqqqqqqqq , <qqqqqqqqqqqj   

  (B)0 architecture_options =
                     
   qwq> 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 = <total-seconds> qqqqqux   
      xtq> /QUERY_MAX_ROWS = <total-rows> qqqqqqqqqqux   
      xtq> /QUERY_CPU_TIME_LIMIT = <total-seconds> qux   
      xmq> /ROLLBACK_ON_EXIT qqqqqqqqqqqqqqqqqqqqqqqjx   
      mqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj   

  (B)0                                      
  optimization-options=    

  qqqq> 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 , <qqqqqqqqqqqqqqqqqqqqqqj

             
             

  (B)0database-options =                   
                                       
  qqwqq> 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 , <qqqqqqqqqqqqqqqqqj        
                                                       

7.2  –  Arguments

7.2.1  –  ALIGN_RECORDS

    Syntax options:

       ALIGN_RECORDS
       NOALIGN_RECORDS

    Aligns the fields in an SQL module procedure record parameter.

    If your host language is C, the default is ALIGN_RECORDS;
    otherwise, the default is NOALIGN_RECORDS.

7.2.2  –  ARCHITECTURE

    Syntax options:

       ARCHITECTURE=GENERIC
       ARCHITECTURE=HOST
       ARCHITECTURE=EV4
       ARCHITECTURE=EV5
       ARCHITECTURE=EV56
       ARCHITECTURE=PCA56
       ARCHITECTURE=EV6
       ARCHITECTURE=EV67
       ARCHITECTURE=EV68
       ARCHITECTURE=EV7

    For improved performance of generated code, the ARCHITECTURE
    command line qualifier can be used on OpenVMS Alpha systems. The
    ARCHITECTURE qualifier is ignored on Itanium systems.

    The ARCHITECTURE qualifier specifies the lowest version of the
    Alpha architecture where this code will run. This allows the
    compiler to generate more efficient code, with the tradeoff that
    code may not run on older systems.

    All Alpha processors implement a core set of instructions and, in
    some cases, the following extensions:

    o  Byte/word extension (BWX) - The instructions that comprise the
       BWX extension are LDBU, LDWU, SEXTB, SEXTW, STB, and STW.

    o  Square-root and floating-point convert extension (FIX) - The
       instructions that comprise the FIX extension are FTOIS, FTOIT,
       ITOFF, ITOFS, ITOFT, SQRTF, SQRTG, SQRTS, and SQRTT.

    o  Count extension (CIX) - The instructions that comprise the CIX
       extension are CTLZ, CTPOP, and CTTZ.

    o  Multimedia extension (MVI) - The instructions that comprise
       the MVI extension are MAXSB8, MAXSW4, MAXUB8, MAXUW4, MINSB8,
       MINSW4, MINUB8, MINUW4, PERR, PKLB, PKWB, UNPKBL, and UNPKBW.

    The Alpha Architecture Reference Manual describes the extensions
    in detail.

    The keyword specified with the ARCHITECTURE qualifier determines
    which instructions the compiler can generate and which coding
    rules it must follow.

    o  GENERIC - Generate instructions that are appropriate for all
       Alpha processors. This option is the default and is equivalent
       to /ARCH=EV4.

    o  HOST - Generate instructions for the processor that the
       compiler is running on (for example, EV56 instructions on
       an EV56 processor, EV7 instructions on an EV7 processor, and
       so on).

    o  EV4 - Generate instructions for the EV4 processor (21064,
       20164A, 21066, and 21068 chips). Applications compiled with
       this option will not incur any emulation overhead on any Alpha
       processor.

    o  EV5 - Generate instructions for the EV5 processor (some 21164
       chips). (Note that the EV5 and EV56 processors both have the
       same chip number - 21164.) Applications compiled with this
       option will not incur any emulation overhead on any Alpha
       processor.

    o  EV56 - Generate instructions for EV56 processors (some 21164
       chips). This option permits the compiler to generate any
       EV4 instruction plus any instructions contained in the BWX
       extension. Applications compiled with this option may incur
       emulation overhead on EV4 and EV5 processors.

    o  PCA56 - Generate instructions for PCA56 processors (21164PC
       chips). This option permits the compiler to generate any EV4
       instruction plus any instructions contained in the BWX and MVI
       extensions. Applications compiled with this option may incur
       emulation overhead on EV4 and EV5 processors.

    o  EV6 - Generate instructions for EV6 processors (21264
       chips). This option permits the compiler to generate any
       EV4 instruction, any instruction contained in the BWX and
       MVI extensions, plus any instructions added for the EV6
       chip. These new instructions include a floating-point square
       root instruction (SQRT), integer/floating-point register
       transfer instructions, and additional instructions to identify
       extensions and processor groups. Applications compiled with
       this option may incur emulation overhead on EV4, EV5, EV56,
       and PCA56 processors.

    o  EV67 or EV68 - Generate instructions for EV67 and EV68
       processors (21264A chips). This option permits the
       compiler to generate any EV6 instruction plus the new bit
       count instructions (CTLZ, CTPOP, and CTTZ). However, the
       precompilers do not currently generate any of the new bit
       count instructions and the EV67 and EV68 have identical
       instruction scheduling models so the EV67 and EV68 are
       essentially identical to the EV6. Applications compiled with
       this option may incur emulation overhead on EV4, EV5, EV56,
       and PCA56 processors.

    o  EV7 - Generate instructions for the EV7 processor (21364
       chip). This option permits the compiler to generate any EV67
       instruction. There are no additional instructions available
       on the EV7 processor but the compiler does have different
       instruction scheduling and prefetch rules for tuning code
       for the EV7. Applications compiled with this option may incur
       emulation overhead on EV4, EV5, EV56, and PCA56 processors.

    The OpenVMS Alpha operating system includes an instruction
    emulator. This capability allows any Alpha chip to execute and
    produce correct results from Alpha instructions even if some of
    the instructions are not implemented on the chip. Applications
    using emulated instructions will run correctly but may incur
    significant emulation overhead at run time.

    Of the available extension types, the Byte/word extension (BWX)
    will often be beneficial for increased performance of Rdb-based
    applications. In addition, for those Alpha implementations
    that support quad-issue of instructions (the EV6 and later
    processors), the compiler does have different instruction
    scheduling and prefetch rules for tuning code.

    For highest levels of performance of generated code, Oracle
    recommends that the ARCHITECTURE qualifier be specified with
    the keyword that most closely matches the lowest processor type
    of the machine where the program will execute.

                LANGUAGE COMPILER SUPPORT FOR ARCHITECTURE

       If specified, the ARCHITECTURE qualifier is passed on the
       command line to the specified language compiler by the SQL
       Precompiler. The language compiler being used must support
       the ARCHITECTURE qualifier and the architecture keyword
       value when the ARCHITECTURE qualifier is specified.

7.2.3  –  C_PROTOTYPES

    Syntax options:

       C_PROTOTYPES=file-name
       NOC_PROTOTYPES

    This qualifier is deprecated and will not be supported in a
    future release. It has been replaced by the PROTOTYPES qualifier.

7.2.4  –  C_STRING

    Syntax options:

       C_STRING=[NO]BLANK_FILL
       C_STRING=[NO]FIXED_CDD_STRINGS
       C_STRING=([NO]BLANK_FILL, [NO]FIXED_CDD_STRINGS)

    Specifies how to handle C host language character strings:

    o  [NO]BLANK_FILL (default: BLANK_FILL)

       Controls whether or not C character strings are filled with
       blanks as required by the SQL89 and ANSI/ISO SQL standards or
       if the null terminator is placed after the last data byte of
       the source string.

    o  [NO]FIXED_CDD_STRINGS (default: NOFIXED_CDD_STRINGS)

       Controls whether or not SQL treats C character strings from
       Oracle CDD/Repository record definitions as fixed-length
       character strings or C null-terminated strings.

7.2.5  –  c-string-options

    Controls how SQL handles C host language character strings.

    Use either or both of the [NO]BLANK_FILL and [NO]FIXED_CDD_
    STRINGS keywords with the C_STRING qualifier to control C string
    characteristics.

7.2.6  –  CONNECT

    Syntax options:

       CONNECT
       NOCONNECT

    Specifies whether or not SQL allows multiple user sessions and
    access to global databases across modules. All SQL modules in
    an application must be compiled with connections enabled or
    disabled.

    The default setting is NOCONNECT.

7.2.7  –  CONSTRAINT_MODE

    Syntax options:

       CONSTRAINT_MODE=IMMEDIATE
       CONSTRAINT_MODE=DEFERRED
       CONSTRAINT_MODE=ON
       CONSTRAINT_MODE=OFF

    You can optionally specify either the CONSTRAINT_MODE=IMMEDIATE
    or CONSTRAINT_MODE=DEFERRED qualifier on the SQL module language
    command line to set the default constraint evaluation mode for
    commit-time constraints. (This qualifier does not affect the
    evaluation of verb-time constraints.) The default is DEFERRED;
    that is, commit-time constraints are evaluated at commit time.

    Setting constraints ON causes each of the affected constraints
    to be evaluated immediately, as well as at the end of each
    statement, until the SET ALL CONSTRAINTS OFF statement is issued
    or until the transaction completes with a commit or rollback
    operation.

    The SET ALL CONSTRAINTS statement overrides the CONSTRAINT_
    MODE=IMMEDIATE or the CONSTRAINT_MODE=DEFERRED qualifier.

    SQL users who require ANSI-standard SQL compatibility should set
    constraints IMMEDIATE. The default (CONSTRAINT_MODE=DEFERRED) is
    acceptable for most other users.

    You can use the ON keyword instead of IMMEDIATE and the OFF
    keyword instead of DEFERRED.

7.2.8  –  CONTEXT=

    Instructs the SQL module processor to execute module language
    procedures in the context of a particular distributed
    transaction. When you use this qualifier, SQL generates an
    additional parameter for the procedures and places the parameter
    as the last parameter declared in the procedure.

    Following are the options you can specify with the CONTEXT=
    qualifier:

    o  NONE

       Specifies that the SQL module processor does not add a context
       parameter to any procedure in the module.

    o  ALL

       Specifies that the SQL module processor adds a context
       parameter to every procedure in the module.

    o  procedure-list

       Specifies that the SQL module processor adds a context
       parameter to each procedure listed. If you specify an entry
       name for a procedure in the list, the SQL module processor
       changes the name of that procedure to the name specified.

       For example, you can specify the following qualifier on the
       command line:

       /CONTEXT=(OPEN_PROC :OPEN_PROC_DIST, FETCH_PROC :FETCH_PROC_DIST,-
                 CLOS_PROC :CLOS_PROC_DIST)

       SQL passes the context parameter to the OPEN_PROC, FETCH_
       PROC, and CLOS_PROC procedures and gives them the new names
       specified. For more information, see the Oracle Rdb7 Guide to
       Distributed Transactions.

    Your application must use the context structure to pass the
    address of the distributed TID from the host language program
    to procedures in the module that are involved in the distributed
    transaction. You pass the context structure to procedures that
    contain executable SQL statements, except statements that
    you cannot execute when a transaction is already started or
    statements that you cannot use when you explicitly call the
    DECdtm system services. the Oracle Rdb SQL Reference Manual
    lists the nonexecutable statements that do not take a context
    structure.

    You can also use the CONTEXT qualifier to specify a new name for
    a procedure.

    Qualifiers used with the CONTEXT qualifier specify which
    procedures receive context parameters, and whether or not the
    names of the procedures are changed.

    Because you cannot use batch-update transactions with distributed
    transactions, you should define the SQL$DISABLE_CONTEXT logical
    name as True before you start a batch-update transaction.
    (Distributed transactions require that you are able to roll back
    transactions. Because batch-update transactions do not write to
    recovery-unit journal (.ruj) files, batch-update transactions
    cannot be rolled back.)

    If you attempt to start a distributed transaction using a batch-
    update transaction, what happens depends upon whether you call
    the DECdtm system services implicitly or explicitly and which SQL
    statement you use to start the transaction:

    o  If you start a batch-update transaction and explicitly call
       the DECdtm system services, SQL returns an error at compile
       time.

    o  If you start a batch-update transaction and implicitly call
       the DECdtm system services, SQL takes the following actions:

       -  If you use a SET TRANSACTION statement with the BATCH
          UPDATE clause, SQL starts a nondistributed transaction.

       -  If you use a DECLARE TRANSACTION statement with the BATCH
          UPDATE clause, SQL returns an error at compile time.

    The two-phase commit protocol applies only to distributed
    transactions. For more information about distributed
    transactions, see the Oracle Rdb7 Guide to Distributed
    Transactions.

7.2.9  –  context-file-name

    A SQL command procedure containing DECLARE statements that you
    want to apply when your program compiles and executes. See the
    Oracle Rdb SQL Reference Manual for more information.

7.2.10  –  database-options

    Specifies that the SQL module processor will process a program
    for access to the specified database type.

    For more information regarding database options, see the Oracle
    Rdb SQL Reference Manual

7.2.11  –  DEPRECATE

    Syntax options:

       DEPRECATE
       NODEPRECATE

    The DEPRECATE and NODEPRECATE qualifiers specify whether or
    not the SQL module processor writes diagnostic messages about
    deprecated features.

    Deprecated features are currently allowed features that will
    not be allowed in future versions of SQL; that is, they will
    be obsolete. For example, one deprecated feature is the use
    of obsolete keywords such as VERB_TIME instead of VERB TIME.
    A complete list of deprecated features appears on line in the
    interactive SQL Help utility.

    You can specify the WARN=WARNING qualifier if you prefer to have
    all warning messages except those about deprecated features.
    You can specify the WARN=(NOWARNING, DEPRECATE) qualifier if you
    prefer only the deprecated feature messages. The WARN qualifier
    alone is equivalent to the WARN=(WARNING, DEPRECATE) qualifier,
    which means that SQL writes informational and warning messages,
    plus messages about deprecated features. The NOWARN qualifier
    alone is equivalent to the WARN=(NOWARNING, NODEPRECATE)
    qualifier, which means that SQL does not write any messages.

7.2.12  –  EXTERNAL_GLOBALS

    Syntax options:

       EXTERNAL_GLOBALS
       NOEXTERNAL_GLOBALS

    Specifies whether or not alias references are coerced into alias
    definitions. An alias definition is an alias declared with the
    GLOBAL keyword (the default) in the DECLARE ALIAS statement. An
    alias reference is an alias declared with the EXTERNAL keyword in
    the DECLARE ALIAS statement.

    The EXTERNAL_GLOBALS qualifier treats alias references as alias
    definitions. This qualifier provides compatibility with versions
    prior to V7.0.

    The NOEXTERNAL_GLOBALS qualifier treats alias references as alias
    references. The NOEXTERNAL_GLOBALS qualifier may be useful on
    OpenVMS if your application shares an alias between multiple
    shareable images.

    The default setting is EXTERNAL_GLOBALS.

    For information on using aliases and shareable images, see the
    Oracle Rdb Guide to SQL Programming.

7.2.13  –  FLAG_NONSTANDARD

    Syntax options:

       FLAG_NONSTANDARD
       FLAG_NONSTANDARD =SQL92_ENTRY
       FLAG_NONSTANDARD =SQL89
       FLAG_NONSTANDARD =MIA
       NOFLAG_NONSTANDARD

    Specifies whether or not SQL identifies nonstandard syntax.
    Nonstandard syntax, called an extension, refers to syntax that
    is not part of the ANSI/ISO SQL standard or the Multivendor
    Integration Architecture (MIA) standard. You can specify the
    following options:

    o  FLAG_NONSTANDARD

       Notifies you of syntax that is an extension to the ANSI/ISO
       SQL standard.

    o  FLAG_NONSTANDARD=SQL92_ENTRY

       Notifies you of syntax that is an extension to the ANSI/ISO
       SQL standard. This qualifier has the same effect on flagging
       as does the FLAG_NONSTANDARD qualifier.

    o  FLAG_NONSTANDARD=SQL89

       Notifies you of syntax that is an extension to the ANSI/ISO
       1989 standard.

    o  FLAG_NONSTANDARD=MIA

       Notifies you of syntax that is an extension to the MIA
       standard.

    o  NOFLAG_NONSTANDARD

       Prevents notification of extensions.

    Preventing notification of extensions (NOFLAG_NONSTANDARD) is the
    default.

7.2.14  –  FLOAT

    Syntax options:

       FLOAT=D_FLOAT
       FLOAT=G_FLOAT
       FOAT=IEEE_FLOAT

    The /FLOAT qualifier determines the conversion that SQL Module
    language performs on SQL Module Language procedure parameters
    declared as single or double precision floating point SQL
    datatypes. SQL floating point datatypes are FLOAT(n), REAL, and
    DOUBLE PRECISION. See the Oracle Rdb SQL Reference Manual for
    details. Internally to Oracle Rdb, single precision floating
    point types are represented as F-Floating while double precision
    floating point types are represented and G-Floating. See SQL and
    Corresponding OpenVMS Data Types for Module Language in Section
    3.4 of the Oracle Rdb SQL Reference Manual for more details.

    By default, parameters declared as single or double precision
    floating point type are expected to be passed by the calling
    host language program in F-Floating and G-Floating format,
    respectively. This is equivalent to using a qualifier of
    /FLOAT=G_FLOAT with the SQL$MOD command.

    If the command line for SQL$MOD has /FLOAT=D_FLOAT, then the
    single and double precision floating point parameters are
    expected to be in F-Floating and D-Floating format respectively.
    SQL Module Language will convert the double precision parameters
    between D-Floating and G-Floating formats for both input and
    output.

    If the command line for SQL$MOD has /FLOAT=IEEE_FLOAT, the single
    and double precision floating point parameters are expected to
    be in IEEE S-Floating and IEEE T-Floating format, respectively.
    SQL Module Language will convert between these formats and the
    internal F-Floating and G-Floating formats for both input and
    output.

    If a parameter of an SQL Module Language procedure is of a record
    type, any fields of the record which are of floating point types
    follow the same rules as described above.

    The floating point formats of the host language program actual
    parameters must agree with the format expected by the SQL Module
    Language actual parameter. See the Oracle Rdb SQL Reference
    Manual for information concerning actual and formal parameter
    agreement.)

                                  NOTES

       Oracle Rdb always stores floating point numbers internally
       using the VAX 32-bit and 64-bit types called F-Floating (F_
       FLOAT) and G-Floating (G_FLOAT), respectively. This means
       that when IEEE formats are used in a host language program,
       Oracle Rdb converts back and forth between the VAX and IEEE
       formats. There are differences in the number of available
       bits in the fraction and exponent between these formats.
       Additionally, the IEEE formats have certain exponent values
       reserved for infinity values. These differences can cause
       floating point overflow or underflow as well as rounding
       errors during the conversion process. See Appendix A of the
       Portable Mathematics Library in the OpenVMS Operating System
       documentation for data on the maximum and minimum values for
       VAX versus IEEE floating point formats.

       When /FLOAT=IEEE_FLOAT is used, floating point data types
       may not be imported from the Common Data Dictionary.

7.2.15  –  G_FLOAT

    Syntax options:

       G_FLOAT
       NOG_FLOAT

    The /G_FLOAT and /NOG_FLOAT qualifiers are for backwards
    compatibility. They are equivalent to /FLOAT=G_FLOAT and
    /FLOAT=D_FLOAT, respectively. You should not specify both /FLOAT
    and /[NO]G_FLOAT qualifiers.

7.2.16  –  INITIALIZE_HANDLES

    Syntax options:

       INITIALIZE_HANDLES
       NOINITIALIZE_HANDLES

    Specifies whether or not alias definitions are coerced into alias
    references. The NOINITIALIZE_HANDLES qualifier causes all alias
    declarations to be treated as alias references.

    An alias definition is an alias declared with the GLOBAL keyword
    (the default) in the DECLARE ALIAS statement. An alias reference
    is an alias declared with the EXTERNAL keyword in the DECLARE
    ALIAS statement.

    The NOINITIALIZE_HANDLES qualifier may be useful for existing
    source code on OpenVMS in coercing alias definitions into alias
    references. Because there is usually no distinction between a
    definition and a reference on OpenVMS, your application might
    declare an alias definition where an alias reference is needed.
    If you reorganize your application into multiple images that
    share aliases, you must distinguish the alias definition from
    the alias reference. In this case, use the NOINITIALIZE_HANDLES
    qualifier to coerce a definition into a reference without
    changing your source code.

    If your application correctly declares alias references with the
    EXTERNAL keyword, use the NOEXTERNAL_GLOBALS qualifier, instead
    of the [NO]INITIALIZE_HANDLES to override the default on OpenVMS
    and cause SQL to treat alias references properly as references.

    The default setting is INITIALIZE_HANDLES. This qualifier
    overrides the [NO]EXTERNAL_GLOBALS qualifier.

    This qualifier is maintained for compatibility with previous
    versions of Oracle Rdb. Use the [NO]EXTERNAL_GLOBALS qualifier,
    which provides more precise control over alias definition. For
    information on using aliases and shareable images, see the Oracle
    Rdb Guide to SQL Programming.

7.2.17  –  LIST

    Syntax options:

       LIST
       NOLIST

    Determines whether or not the SQL module processor creates a list
    file containing the original module list along with any error
    messages from the processing, and, if it does, what it is named.
    The NOLIST qualifier is the default. If you specify the LIST
    qualifier and do not include a file specification, the SQL module
    processor creates a list file with the same file name as your
    module source file with the file extension .lis.

7.2.18  –  LOWERCASE_PROCEDURE_NAMES

    Syntax options:

       LOWERCASE_PROCEDURE_NAMES
       NOLOWERCASE_PROCEDURE_NAMES

    Forces the names of the module language procedures to be in
    lowercase. This qualifier not only assumes that the SQL module
    procedure names are in lowercase, it overrides the case in any
    quoted SQL module procedure.

    The default setting is NOLOWERCASE_PROCEDURE_NAMES.

7.2.19  –  MACHINE_CODE

    Syntax options:

       MACHINE_CODE
       NOMACHINE_CODE

    Oracle Rdb determines whether or not the SQL module processor
    includes machine code in the list (.lis) file; however, to
    generate the list file with the machine code in it, you must
    also specify the LIST qualifier.

    The NOMACHINE_CODE qualifier is the default.

7.2.20  –  module-file-spec

    The file specification for an SQL module source file. The default
    file extension for the source file is .sqlmod.

7.2.21  –  module-qualifiers

    A set of qualifiers that you can optionally apply to the SQL
    module processor command line.

7.2.22  –  no-qualifiers

    You can add the NO prefix to negate any qualifier in this group.

7.2.23  –  OBJECT

    Syntax options:

       OBJECT
       NOOBJECT

    Specifies whether or not the SQL module processor creates an
    object file when compiling the source file if the compilation
    does not generate fatal errors; and, if an object file is
    produced, what the file is named. The OBJECT qualifier is the
    default. If you specify the OBJECT qualifier and do not include
    a file specification, the SQL module processor creates an object
    file with the same file name as the source file and with the file
    extension .obj.

7.2.24  –  OPTIMIZATION_LEVEL

    Specifies the optimizer strategy to be used to process all
    queries within your SQL module language program. Select the:

    o  AGGRESSIVE_SELECTIVITY option if you expect a small number of
       rows to be selected.

    o  DEFAULT option to accept the Oracle Rdb defaults: FAST_FIRST
       and DEFAULT SELECTIVITY. strategy.

    o  FAST_FIRST option if you want your program to return data to
       the user as quickly as possible, even at the expense of total
       throughput.

    o  SAMPLED_SELECTIVITY option to use literals in the query to
       perform preliminary estimation on indices.

    o  TOTAL_TIME option if you want your program to run at the
       fastest possible rate, returning all the data as quickly as
       possible. If your application runs in batch, accesses all the
       records in a query, and performs updates or writes reports,
       you should specify TOTAL_TIME.

    You can select either the TOTAL_TIME or the FAST_FIRST option in
    conjunction with either the AGGRESSIVE_SELECTIVITY or SAMPLED_
    SELECTIVITY option. Use a comma to separate the keywords and
    enclosed the list in parentheses.

    The following example shows how to use the OPTIMIZATION_LEVEL
    qualifier:

    $ SQL$MOD/OPTIMIZATION_LEVEL=(TOTAL_TIME,SAMPLED_SELECTIVITY) APPCODE.SQLMOD

    Any query that explicitly includes an OPTIMIZE WITH, or OPTIMIZE_
    FOR clause is not affected by the settings established using the
    OPTIMIZATION_LEVEL qualifier.

    You affect the optimizer strategy of static SQL queries with the
    optimization level qualifier; however, the default optimizer
    strategy set by the OPTIMIZATION_LEVEL qualifier can be
    overridden by the default optimizer strategy set in a top-level
    SELECT statement.

    In contrast, the SET OPTIMIZATION LEVEL statement specifies the
    query optimization level for dynamic SQL query compilation only;
    the statement does not affect the SQL compile-time environment
    nor does it affect the run-time environment of static queries.

7.2.25  –  PACKAGE_COMPILATION

    Syntax options:

       PACKAGE_COMPILATION
       NOPACKAGE_COMPILATION

    Determines if a package specification is produced and loaded into
    the ACS library.

    Oracle Rdb produces a package specification when you process
    a module with the LANGUAGE ADA clause specified in the module
    header unless you specify the NOPACKAGE_COMPILATION qualifier.
    The NOPACKAGE_COMPILATION qualifier prevents the package
    specification from being loaded in the ACS library, but still
    creates and compiles the .ada file.

    The PACKAGE_COMPILATION qualifier is the default.

7.2.26  –  PARAMETER_CHECK

    Syntax options:

       PARAMETER_CHECK
       NOPARAMETER_CHECK

    Specifies whether or not the SQL module processor compares
    the number of formal parameters declared for a procedure with
    the number of parameters specified in the SQL statement of the
    procedure:

    o  PARAMETER_CHECK (default)

       Checks that parameter counts match and generates an error at
       run time (not compile time) when they do not.

    o  NOPARAMETER_CHECK

       Suspends checking parameters to improve module compilation
       time. Consider using the NOPARAMETER_CHECK qualifier after you
       have debugged your SQL module.

    SQL checks parameter counts by default. To improve module
    compilation time, you must explicitly use the NOPARAMETER_CHECK
    qualifier.

7.2.27  –  PASSWORD_DEFAULT

    Specifies the user's password at compile time.

    If you use the USING DEFAULT clause of the DECLARE ALIAS
    statement, you use this qualifier to pass the compile-time user's
    password to the program.

7.2.28  –  PRAGMA

    Syntax options:

       PRAGMA = IDENT = string-literal
       NOPRAGMA

    Using the IDENT keyword with the PRAGMA qualifier allows the user
    to pass a text string to the SQL Module Language compiler to be
    written to the Object Module Header. This is a way to note the
    generation of the compiler module.

    If the PRAGMA (IDENT ...) clause is used as part of the DECLARE
    MODULE statement, then that value will override any value used on
    the command line.

    The ANALYZE/OBJECT and LIBRARY command can be used to display
    this ident string, and the value will be displayed in LINKER map
    files.

    OpenVMS limits the IDENT string to a 15 octet string. If the
    string is longer than this (even with trailing spaces) then an
    error will be reported by the SQL precompiler.

    The following example demonstrates the use of the qualifier to
    establish the generation of the compiler module.

    $ SQL$MOD TEST/PRAGMA=IDENT="v1.2-32"

7.2.29  –  PROTOTYPES

    Syntax options:

       PROTOTYPES[=prototypesfile]
       NOPROTOTYPES

    The PROTOTYPES qualifier uses the LANGUAGE clause from the module
    to generate routine declarations for the following languages: C
    (C++), Pascal, and BLISS. The qualifier is ignored for all other
    language values.

    The prototypes file specification defaults to the same device,
    directory, and file name as the module language source. The file
    types default to .h for C, .PAS for Pascal, and .REQ for BLISS.

    For the BLISS language, the PROTOTYPES qualifier generates
    EXTERNAL ROUTINE declarations for each SQL module language
    procedure.

    For the Pascal language, the generated external procedure
    declarations are suitable for inclusion in either a Pascal
    program or module. Structured types (RECORD ... END RECORD),
    SQLDA, and SQLCA used by the SQL module language procedures are
    declared as UNSAFE arrays of bytes to simplify passing structures
    via these external definitions. However, care must be taken as
    this form of declaration disables the strong typing checks in
    Pascal.

    The output for the C language includes pre-processor directives
    to conditionally include C++ "extern C" syntax and also allow
    multiple #include references.

    The default setting is NOPROTOTYPES.

7.2.30  –  QUERY_CPU_TIME_LIMIT

    Limits the amount of CPU time used to optimize a query for
    execution. If the query is not optimized and prepared for
    execution before the CPU time limit is reached, an error message
    is returned.

    The default is unlimited time for the query to compile. Dynamic
    SQL options are inherited from the compilation qualifier.

7.2.31  –  QUERY_ESTIMATES

    Syntax options:

       QUERY_ESTIMATES
       NOQUERY_ESTIMATES

    Specifies whether or not SQL returns the estimated number of
    rows and estimated number of disk I/O operations in the SQLCA
    structure. If you specify the default, which is the QUERY_
    ESTIMATES qualifier, SQL returns the estimated number of rows
    in the field SQLCA.SQLERRD[2] and the estimated number of disk
    I/O operations in the field SQLCA.SQLERRD[3]. The value of
    SQLCA.SQLERRD[2] and SQLCA.SQLERRD[3] is normally 0 after you
    execute an OPEN statement for a table.

    The following example shows interactive SQL output from a
    statement that accesses the INTRO_PERSONNEL database. The
    database was loaded using the sample program SQL$INTRO_LOAD_
    EMPL_C.SQLMOD with the QUERY_ESTIMATES qualifier on the module
    language command line. The SQLCA.SQLERRD[2] field shows that SQL
    estimates 100 rows. The SQLCA.SQLERRD[3] field shows that SQL
    estimates 16 disk I/O operations.

    $ SQL$
    SQL> 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:

7.2.32  –  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.

7.2.33  –  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.

7.2.34  –  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.

7.2.35  –  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.

7.2.36  –  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.

7.2.37  –  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.

7.2.38  –  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.

7.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
Close Help