The SQL precompiler provides special keywords and syntax that
allow you to include (embed) simple and compound statements
directly into host language programs. Then you can use the SQL
precompiler to process the combined embedded statements and
host language code to produce an object file for linking and
execution.
1 – Environment
You can use SQL precompiler syntax only in Ada, C, COBOL,
FORTRAN, Pascal, and PL/I host language source files. The SQL
precompiler supports no other host languages. If you use a host
language other than the ones mentioned for embedded SQL and
you want to use the SQL interface with it, you must use the SQL
module processor.
2 – Format
(B)0[m[1;4mEXEC[m[1m [1;4mSQL[m[1m qqwqq> simple-statement qqqqwqqwqqqqqqqqqqqqqqqqqqqqqwqqq>[m
[1m mqq> compound-statement qqj mqq> ending-symbol qqqj [m
[1m [m
(B)0[m[1mending-symbol = [m
[1m [m
[1mqqqqqwq> ; qqqqqqqqwq> [m
[1m mq> END-EXEC qj [m
[1m [m
(B)0[m[1msimple-statement = [m
[1m [m
[1mqqqq> SQL statement qqqq> [m
(B)0[m[1mcompound-statement = [m
[1m [m
[1m qqwqqqqqqqqqqqqqqqqqqqqqqqwq> [1;4mBEGIN[m[1m qqwqqqqqqqqqqqqqqqqqqqqwqqqk[m
[1m mq> <beginning-label>:[m [1mqj mq> pragma-clauses[m [1mqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqk [m
[1m mwqq> variable-declaration qwj x [m
[1m mqqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> [1;4mEND[m[1m qqwqqqqqqqqqqqqqqqqqqqqwqq>[m
[1m mwq> [m [1mcompound-use-statement qwj[m [1mmq>[m [1m<ending-label>:[m [1mqj [m
[1mmqqqqqqqqqq <qqqqqqqqqqqqqqqqqj[m
(B)0[m[1mpragma-clauses =[m
[1mqqwqwq> pragma-option qqwqqqqqqqqqqqqqqqqqqwqqq>[m
[1mx[m [1mmqqqqqqq <qqqqqqqqqj[m [1mx[m
[1mmq> [1;4mPRAGMA[m[1m ( qwq> pragma-option qwq> )[m [1mqj[m
[1mmqqqqqqqq , <qqqqqqqj[m
(B)0[m[1mpragma-option =[m
[1mqqwq> [1;4mATOMIC[m[1m qqqqqqqqqqqqqqqqqqqqqqwqq>[m
[1mtq> [1;4mNOT[m [1;4mATOMIC[m[1m qqqqqqqqqqqqqqqqqqu[m
[1mtq> [1;4mON[m[1m [1;4mALIAS[m[1m <alias-name> qqqqqqqu[m
[1mtq> with-clause qqqqqqqqqqqqqqqqqu[m
[1mmq> optimize-clause qqqqqqqqqqqqqj[m
[1m [m
(B)0[m[1mwith-clause = [m
[1m [m
[1mqqq> [1;4mWITH[m[1m qq> [1;4mHOLD[m[1m qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m mq> [1;4mPRESERVE[m[1m qqwq> [1;4mON[m[1m [1;4mCOMMIT[m[1m qqqu [m
[1m tq> [1;4mON[m[1m [1;4mROLLBACK[m[1m qu [m
[1m tq> [1;4mALL[m[1m qqqqqqqqqu [m
[1m mq> [1;4mNONE[m[1m qqqqqqqqj [m
[1m [m
(B)0[m[1moptimize-clause = [m [1m [m [1m [m
[1m [m [1m [m [1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq> [m
[1m mq> [1;4mOPTIMIZE[m[1m qqwqwq> [1;4mFOR[m[1m qwq> [1;4mFAST[m[1m [1;4mFIRST[m[1m qqqqqqqqwqqqqqqqqqqwqwqj [m
[1m x x tq> [1;4mTOTAL[m[1m [1;4mTIME[m[1m qqqqqqqqu[m [1m [m [1mx x [m
[1mx[m [1mx[m [1mmq> [1;4mSEQUENTIAL[m [1;4mACCESS[m [1mqj[m [1mx[m [1mx[m
[1m x tq> [1;4mUSING[m[1m <outline-name> qqqqqqqqqqqqqqqqqqu x [m
[1mx[m [1mtq> [1;4mWITH[m[1m qwq> [1;4mDEFAULT[m [1mqqwq> [1;4mSELECTIVITY[m [1mqu[m [1mx[m
[1mx[m [1mx[m [1mtq>[m [1;4mSAMPLED[m [1mqqu[m [1mx[m [1mx[m
[1mx[m [1mx[m [1mmq>[m [1;4mAGGRESSIVE[m[1m j[m [1mx[m [1mx[m
[1m x mq> [1;4mAS[m[1m <query-name> qqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m [1m [m [1m [m
3 – Arguments
3.1 – compound-statement
A statement that can contain multiple SQL statements in an SQL
module procedure or in an embedded SQL procedure.
An embedded procedure that contains a compound statement is
called an embedded multistatement procedure. SQL supports a
restricted subset of SQL statements in a compound statement
embedded in a host langauge program. Refer to the Oracle Rdb
SQL Reference Manual for a list of valid SQL statements allowed
in a compound statement.
Compound statements can also include program-like, flow-
of-control statements (IF, LOOP, CASE, LEAVE), transaction
management statements (COMMIT and ROLLBACK), a variable
declaration statement (SET assignment), a cursor-processing
statement (FOR), and a procedure-debugging statement (TRACE).
See the Oracle Rdb SQL Reference Manual for a complete
description of a compound statement.
3.2 – ending-symbol
Ends an embedded simple or an embedded compound statement. To
end an embedded statement, follow the host language requirements
listed in the following table.
Table 5 Ending Embedded SQL Statements
Language Symbols to End EXEC SQL Statements
Ada Semicolon (;)
C Semicolon (;)
COBOL END-EXEC
FORTRAN Ending symbol not required
Pascal Semicolon (;)
PL/I Semicolon (;)
3.3 – EXEC_SQL
Prefixes each simple or compound statement. Converting
interactive statements to precompiled statements requires the
added step of starting each simple or compound statement with the
keywords EXEC SQL. SQL cannot process these statements otherwise.
Also, both keywords EXEC and SQL must be on the same line, and
you cannot insert comments between them.
3.4 – simple-statement
A statement that can contain a single SQL statement only. For a
list of SQL statements that are valid within a simple statement
and for a complete description of a simple statement, see the
Oracle Rdb SQL Reference Manual.
4 – Example
Example 1: Embedding a compound statement in a host language
program
The following example shows how to embed a multistatement
procedure in a program. The keyword PROCEDURE does not appear
in an embedded SQL application.
EXEC SQL BEGIN DECLARE SECTION ;
int x ;
EXEC SQL END DECLARE SECTION ;
EXEC SQL
BEGIN
DECLARE :y INTEGER ;
SET :y = 2 * :x ; -- :x is a host variable
UPDATE employees
SET salary = :y ;
WHERE .
.
. ;
BEGIN
DECLARE :x INTEGER ;
SET :x = 100 ; -- :x is a local variable
UPDATE employees
SET salary = :x ;
WHERE .
.
. ;
END ;
END ;
5 – SQLPRE Command Line
You can define a symbol to help you invoke the SQL precompiler:
$ SQLPRE == "$SQL$PRE"
Because the SQL precompiler requires a language qualifier, you
might want to define a particular language so that you can invoke
the command on one line:
$ SADA == "$SQL$PRE/ADA"
$ SADA SQL_DYNAMIC
By defining symbols, you can invoke the SQL precompiler with or
without a file specification for a host language program file:
o If you invoke the SQL precompiler without an input file
specification for a host language program file, the
precompiler prompts you for it. For example:
$ SQLPRE
INPUT FILE> pre-host-file-spec
o If you invoke the SQL precompiler with a host language program
file as part of the DCL command line, SQL starts processing
your file immediately after you press the Return key. For
example:
$ SADA pre-host-file-spec pre-qualifiers
Whichever method you choose to invoke the precompiler, you have
the option to specify a wide range of qualifiers that control
how the SQL precompiler processes the module file. The syntax
diagrams show the format for the qualifiers that you can include
with the host language program file specification.
5.1 – Format
(B)0[m[1mpre-host-file-qual = [m
[1m [m
[1;4mSQLPRE[m[1m qq> pre-host-file-spec qwqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m mq> <context-file-name> qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmq> pre-lang-qualifiers qqqqwqqqqqqqqqqqqqqqqqqqqqwqqqqqq> [m
[1m mqq> pre-qualifiers qqj [m
[1m [m
(B)0[m[1mpre-lang-qualifiers = [m
[1m [m
[1mqqq> / qwq> [1;4mADA[m[1m qqqqqqqqqqqqqqqqqqqqwqq> [m
[1m tq> [1;4mCC[m[1m qqwqqqqqqqqqqqqqqqqwqu [m
[1m x tq> [1;4m=VAXC[m[1m qqqqqqqu x [m
[1m x mq> [1;4m=DECC[m[1m qqqqqqqj x [m
[1m tq> [1;4mCOBOL[m[1m qqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mFORTRAN[m[1m qqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mPASCAL[m[1m qqqqqqqqqqqqqqqqqu [m
[1m mq> [1;4mPLI[m[1m qqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mpre-qualifiers = [m
[1mqqqwwq> / [1;4mARCHITECTURE[m[1m= architecture_options qqqqqqqqqqqqqqwwqq> [m
[1m xtq> / qwqqqqqqwqqwq> [1;4mANSI_FORMAT[m[1m qqqqqqqqqqqqqqqqqqqqqqux[m [1m [m
[1m xx mq [1;4mNO[m[1m qj tq> [1;4mEXTEND_SOURCE[m[1m qqqqqqqqqqqqqqqqqqqqux [m
[1m xx tq> [1;4mG_FLOAT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqux [m
[1m xx tq> [1;4mLIST[m[1m qqwqqqqqqqqqqqqqqqqqqwqqqqqqqux [m
[1mxx[m [1mx[m [1mmq> = <file-spec> qj[m [1mxx[m
[1m xx tq> [1;4mMACHINE_CODE[m[1m qqqqqqqqqqqqqqqqqqqqqux [m
[1m xx mq> [1;4mOBJECT[m[1m qwqqqqqqqqqqqqqqqqqqwqqqqqqux [m
[1m xx[m [1m mq> = <file-spec> qj xx [m
[1mxtq>[m [1m/ [1;4mFLOAT[m[1m= qwq> D_FLOAT[m [1mqqqwqqqqqqqqqqqqqqqqqqqqqqqux[m
[1mxx[m [1mtq> G_FLOAT[m [1mqqqqu[m [1mxx[m
[1mxx[m [1mmq> IEEE_FLOAT[m [1mqqj[m [1mxx[m
[1mxx[m [1mxx[m
[1m xmq> / [1;4mSQLOPTIONS[m[1m= ( qwwq> opt-no-qualifiers qwwq> ) qqqjx [m
[1m x xmq> opt-qualifiers qqqqjx x [m
[1m x mqqqqqqqqq , <qqqqqqqqqqqj x [m
[1m mqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m [1marchitecture_options =[m
[1m [m
[1mqwq> [1;4mGENERIC[m [1mqwqq>[m
[1m tq> [1;4mHOST[m[1m qqu [m
[1m tq> [1;4mEV4[m[1m qqu [m
[1m tq> [1;4mEV5[m[1m qqu [m
[1m tq> [1;4mEV56[m[1m qqu [m
[1mtq>[m [1;4mPCA56[m[1m qqu[m
[1mtq>[m [1;4mEV6[m[1m [m [1mqqu[m
[1mtq>[m [1;4mEV67[m[1m qqu[m
[1mtq>[m [1;4mEV68[m[1m qqu[m
[1mmq>[m [1;4mEV7[m[1m qqj[m
[1m [m [1m [m
[1m [m
(B)0[m[1mopt-no-qualifiers = [m
[1m [m
[1mqwqqqqqqqqwqwq> [1;4mCONNECT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> [m
[1m mq> [1;4mNO[m[1m qqj tq> [1;4mDECLARE_MESSAGE_VECTOR[m[1m qqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mEXTERNAL_GLOBALS[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mFLAG_NONSTANDARD[m[1m qqqwqqqqqqqqqqqqqqqqqqqwqqu [m
[1m x tqq> = [1;4mSQL92_ENTRY[m[1m qu x [m
[1m x tqq> = [1;4mSQL89[m[1m qqqqqqqu x [m
[1m x mqq> = [1;4mMIA[m[1m qqqqqqqqqj x [m
[1m tq> [1;4mINITIALIZE_HANDLES[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1mtq>[m [1;4mPRAGMA[m[1m = ( qq> [1;4mIDENT[m[1m = string-literal q> ) qu[m
[1mtq>[m [1;4mQUERY_EXTIMATES[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m
[1m tq> [1;4mQUIET_COMMIT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mTRANSACTION_DEFAULT[m[1m qwqqqqqqqqqqqqqqqqqqwqqqu [m
[1m x tq> = [1;4mIMPLICIT[m[1m qqqqu x [m
[1m x mq> = [1;4mDISTRIBUTED[m[1m qj x [m
[1m mq> [1;4mWARN[m[1m wqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqj [m
[1m [m [1m m> = ( qwq> warning-option qwq> ) qj [m [1m [m
[1m [m [1m mqqqqqq , <qqqqqqqqqj [m [1m [m
[1m [m [1m [m
(B)0[m[1mwarning-option = [m
[1m [m
[1mqqqwqqqwq> [1;4mWARNING[m[1m qqqqqqwqqwq> [m
[1m x tq> [1;4mNOWARNING[m[1m qqqqu x [m
[1m x tq> [1;4mDEPRECATE[m[1m qqqu x [m
[1m x mq> [1;4mNODEPRECATE[m[1m qj x [m
[1m mqqqqqqqqq , <qqqqqqqqqqqj [m
(B)0[m[1mopt-qualifiers = [m
[1m [m
[1mqqwq> [1;4mC_STRING[m[1m = c-string-options qqqqqqqqqqqqqqqqqqqqqqqqwqq> [m
[1m tq> constraint-options qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> database-options qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> optimization-options qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mQUERY_TIME_LIMIT[m[1m = <total-seconds> qqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mQUERY_MAX_ROWS[m[1m = <total-rows> qqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mQUERY_CPU_TIME_LIMIT[m[1m = <total-seconds> qqqqqqqqqqqqqu [m
[1m tq> [1;4mUSER_DEFAULT[m[1m = <username> qqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mPASSWORD_DEFAULT[m[1m = <password> qqqqqqqqqqqqqqqqqqqqqqu [m
[1m mq> [1;4mROLLBACK_ON_EXIT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mc-string-options = [m
[1m [m
[1mqwwqqwqqqqqqw> [1;4mBLANK_FILL[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqw>[m
[1m xx m> [1;4mNO[m[1m qj x [m
[1m xmqqwqqqqqqw> [1;4mFIXED_CDD_STRINGS[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x m> [1;4mNO[m[1m qj x [m
[1m m> ( wqqqqqw> [1;4mBLANK_FILL[m[1m q> , wqqqqqqw> [1;4mFIXED_CDD_STRINGS[m[1m q> ) j [m
[1m m> [1;4mNO[m[1m j m> [1;4mNO[m[1m qj [m
[1m [m
(B)0[m[1mconstraint-options = [m
[1m [m
[1mqqqqq> [1;4mCONSTRAINT_MODE[m[1m = qwq> [1;4mIMMEDIATE[m[1m qqqqqqqqqqqqqqqqqwq> [m
[1m tq> [1;4mDEFERRED[m[1m qqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mON[m[1m qqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m mq> [1;4mOFF[m[1m qqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mdatabase-options = [m
[1m [m
[1mqqwqq> [1;4mELN[m[1m qqqqqqqqqqqqqqqqqqqqqwqq> [m
[1m tqq> [1;4mNSDS[m[1m qqqqqqqqqqqqqqqqqqqqu [m
[1m tqq> rdb-options qqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA[m[1m qqqqqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA=V1[m[1m qqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA=V2[m[1m qqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA=V2N[m[1m qqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mNOVIDA[m[1m qqqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mDBIV1[m[1m qqqqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mDBIV31[m[1m qqqqqqqqqqqqqqqqqqu [m
[1m mqq> [1;4mDBIV70[m[1m qqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m [1mrdb-options = [m
[1m [m
[1mqwq> [1;4mRDBVMS[m[1m qqwqq>[m
[1m tq> [1;4mRDB030[m[1m qqu [m
[1m tq> [1;4mRDB031[m[1m qqu [m
[1m tq> [1;4mRDB040[m[1m qqu [m
[1m tq> [1;4mRDB041[m[1m qqu [m
[1mtq>[m [1;4mRDB042[m [1mqqu[m
[1mtq>[m [1;4mRDB050[m [1mqqu[m
[1mtq>[m [1;4mRDB051[m [1mqqu[m
[1mtq>[m [1;4mRDB060[m [1mqqu[m
[1mtq>[m [1;4mRDB061[m [1mqqu[m
[1mtq>[m [1;4mRDB070[m [1mqqu[m
[1mmq>[m [1;4mRDB071[m [1mqqj[m
(B)0[m [1m [m [1m [m [1m [m
[1moptimization-options= [m
[1mqqqq> [1;4mOPTIMIZATION_LEVEL[m[1m= qwqqqq> [1;4mDEFAULT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1mmqq> ([m [1mqwwq> [1;4mAGGRESSIVE[m[4m_[1;4mSELECTIVITY[m[1m qqwqqwq[m [1m)[m [1mqj[m
[1m [m [1mxtq> [1;4mFAST[m[4m_[1;4mFIRST[m[1m [m [1mqqu[m [1mx[m [1m [m
[1m [m [1mxtq> [1;4mSAMPLED[m[4m_[1;4mSELECTIVITY[m [1mqqqqu[m [1mx[m [1m [m
[1m [m [1mxmq> [1;4mTOTAL[m[4m_[1;4mTIME[m[1m [m [1mqqqqqqqqqqqqj[m [1mx[m [1m [m
[1mmqqqqqq[m [1m, <qqqqqqqqqqqqqqqqqqqqqqj[m
[1m [m
[1m [m
5.2 – Arguments
5.2.1 – ANSI_FORMAT
Syntax options:
ANSI_FORMAT
NOANSI_FORMAT
Specifies whether the SQL precompiler accepts terminal-format
COBOL or ANSI-format COBOL.
The default is the terminal format COBOL NOANSI_FORMAT qualifier.
5.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.
5.2.3 – 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 options with the C_STRING keyword to control C string
characteristics.
5.2.4 – C_STRING
Syntax options:
SQLOPTIONS= (C_STRING=[NO]BLANK_FILL)
SQLOPTIONS= (C_STRING=[NO]FIXED_CDD_STRINGS)
SQLOPTIONS= (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
CDD/Repository record definitions as fixed-length character
strings or C null-terminated strings.
5.2.5 – CONNECT
Syntax options:
SQLOPTIONS= (CONNECT)
SQLOPTIONS= (NOCONNECT)
Specifies whether or not SQL allows multiple user connections
and access to global databases across modules. All SQL modules
in an application must be compiled with connections enabled or
disabled.
The SQLOPTIONS=NOCONNECT qualifier is the default.
5.2.6 – CONSTRAINT_MODE
Syntax options:
SQLOPTIONS= (CONSTRAINT_MODE=IMMEDIATE)
SQLOPTIONS= (CONSTRAINT_MODE=DEFERRED)
SQLOPTIONS= (CONSTRAINT_MODE=ON)
SQLOPTIONS= (CONSTRAINT_MODE=OFF)
You can optionally specify either the SQLOPTIONS=(CONSTRAINT_
MODE=IMMEDIATE) or SQLOPTIONS=(CONSTRAINT_MODE=DEFERRED)
qualifier on the SQL precompiler 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 IMMEDIATE causes each affected constraint
to be evaluated immediately, as well as at the end of each
statement, until the SET ALL CONSTRAINTS DEFERRED statement
is issued, or until the transaction completes with a commit or
rollback operation.
The SET ALL CONSTRAINTS statement overrides the constraint
evaluation mode specified in the SQLOPTIONS qualifier. For more
information about the default constraint mode, see the Oracle Rdb
SQL Reference Manual.
SQL users who require ANSI-standard SQL compatibility should set
constraints as IMMEDIATE. The default (CONSTRAINT_MODE=DEFERRED)
is acceptable for most other users.
The qualifiers CONSTRAINT_MODE=ON and CONSTRAINT_MODE=OFF
duplicate the behavior of the qualifiers CONSTRAINT_
MODE=IMMEDIATE and CONSTRAINT_MODE=DEFERRED, respectively.
5.2.7 – context-file-name
An 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 information about context-
file-name.
5.2.8 – database-options
Specifies that the SQL precompiler correctly processes a program
for access to the specified database type. For more information
regarding database options, see the Oracle Rdb SQL Reference
Manual.
The precompiler database option can in turn be overridden by an
attach to a database at run time. On the DECLARE statement, SQL
sets the database options of the specified database.
By default, the SQL precompiler determines the valid database
from the database used to compile the program. If no database
is used to compile the program, the precompiler processes the
program for a database created with the most recent version of
Oracle Rdb.
5.2.9 – DECLARE_MESSAGE_VECTOR
Syntax options:
SQLOPTIONS= (DECLARE_MESSAGE_VECTOR)
SQLOPTIONS= (NODECLARE_MESSAGE_VECTOR)
Specifies that the RDB$MESSAGE_VECTOR structure be declared in
the host language as part of the SQLCA during SQLPRE processing.
You can use this switch with language compilers that support the
'$' special character.
The default is the SQLOPTIONS=(DECLARE_MESSAGE_VECTOR) qualifier.
5.2.10 – EXTEND_SOURCE
Syntax options:
EXTEND_SOURCE
NOEXTEND_SOURCE
Allows the SQL precompiler to view 132 columns of FORTRAN source
rather than the default of 72 columns.
5.2.11 – EXTERNAL_GLOBALS
Syntax options:
SQLOPTIONS= (EXTERNAL_GLOBALS)
SQLOPTIONS= (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 on OpenVMS is the SQLOPTIONS=(EXTERNAL_GLOBALS)
qualifier.
For information on using aliases and shareable images, see the
Oracle Rdb Guide to SQL Programming.
5.2.12 – FLAG_NONSTANDARD
Syntax options:
SQLOPTIONS= (FLAG_NONSTANDARD)
SQLOPTIONS= (FLAG_NONSTANDARD=SQL92_ENTRY)
SQLOPTIONS= (FLAG_NONSTANDARD=SQL89)
SQLOPTIONS= (FLAG_NONSTANDARD=MIA)
SQLOPTIONS= (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.
The default is the SQLOPTIONS=(NOFLAG_NONSTANDARD) qualifier.
5.2.13 – FLOAT
Syntax options:
FLOAT=D_FLOAT
FLOAT=G_FLOAT
FLOAT=IEEE_FLOAT
Specifies the floating point representation that the SQL
precompiler uses for floating point data types in a formal
parameter list and specifies the floating point qualifier passed
to the language compiler.
The SQL Precompiler translates embedded SQL into host language
declarations and procedure calls. In addition it generates the
procedures behind the procedure calls. The /FLOAT qualifier
for SQL$PRE determines the floating point format that SQL$PRE
assumes for host language variables and, hence, determines the
conversions that will be made internal to the generated SQL
procedures. When SQL$PRE calls the host language compiler to
process the precompiled program it passes an equivalent qualifier
to its /FLOAT qualifer that is supported by the host language.
This means that to the extent that the floating point format
of host language variables is determined by a /FLOAT qualifier,
the floating point formats of the host language variables and
the parameters of procedure calls generated by SQL$PRE are
guaranteed to be compatible. When the host language provides
a type which explicitly declares the floating point format of
the an individual variable, SQL$PRE uses that information to
determine the conversion needed regardless of the setting of the
/FLOAT qualifier.
The SQL Precompiler's default floating point format for single
or double precision floating point types is F-Floating and G-
Floating format, respectively. This is equivalent to using a
qualifier of /FLOAT=G_FLOAT with the SQL$PRE command.
If a host language variable is a record or structure (for example
a qualified parameter in the INTO clause of a singleton SELECT
statement), any fields in the record or structure that are of a
floating point type follow the same rules as described above.
NOTE
The Common Data Dictionary supports floating point types.
However when the /FLOAT qualifier specifies IEEE_FLOAT,
these types may not be used.
5.2.14 – 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.
5.2.15 – INITIALIZE_HANDLES
Syntax options:
SQLOPTIONS= (INITIALIZE_HANDLES)
SQLOPTIONS= (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 [NO]INITIALIZE_HANDLES to override the default on OpenVMS and
cause SQL to treat alias references properly as references.
The default is the SQLOPTIONS=INITIALIZE_HANDLES qualifier. This
qualifier overrides the [NO]EXTERNAL_GLOBALS qualifier.
The SQLOPTIONS=[NO]INITIALIZE_HANDLES qualifier is maintained for
compatibility with previous versions of Oracle Rdb. For V7.0 and
higher, 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.
5.2.16 – LIST
Syntax options:
LIST
NOLIST
Oracle Rdb determines whether or not the SQL precompiler
generates a list file (default file extension .lis) that contains
information about the SQL compilation and the host language
compilation. In addition, if the logical name SQL$KEEP_PREP_FILES
is defined, the SQL precompiler retains an intermediate module
list file (file extension .mli), which contains information about
the SQL compilation only. If you specify the LIST qualifier and
do not include a file specification, the SQL precompiler creates
a list file with the same file name as your source file with the
file extension .lis.
The NOLIST qualifier is the default.
5.2.17 – MACHINE_CODE
Syntax options:
MACHINE_CODE
NOMACHINE_CODE
Oracle Rdb specifies whether or not the SQL precompiler includes
machine code in the list 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.
5.2.18 – OBJECT
Syntax options:
OBJECT
NOOBJECT
Specifies whether or not the SQL precompiler 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. If you specify the OBJECT qualifier and do
not include a file specification, the precompiler creates an
object file with the same file name as the source file and the
file extension .obj. You can specify the OBJECT qualifier for any
language except Ada.
The OBJECT qualifier is the default.
5.2.19 – 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.
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$PRE/SQLOPTIONS=OPTIMIZATION_LEVEL=(TOTAL_TIME,SAMPLED_SELECTIVITY) APPCODE.SC
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 option 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.
5.2.20 – PASSWORD_DEFAULT
Syntax options:
SQLOPTIONS= (PASSWORD_DEFAULT=password)
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.
5.2.21 – pre-host-file-spec
The file specification for a host language source file that
contains embedded SQL statements. The default file extension
for the source file depends on the host language specified in the
language qualifier.
Language Default File Extension
Ada .sqlada
C .sc
COBOL .sco
FORTRAN .sfo
Pascal .spa
PL/I .spl
If the host language is Ada or COBOL, the file name (without the
file extension) cannot be longer than 27 characters.
The precompiler command line allows a list of host language
source files in this argument, but only processes the first file
specification it encounters. If you specify a list of files, the
precompiler:
o Gives a warning message that only the first file on the line
will be precompiled
o Ignores the other file specifications and passes them along to
the host language compiler in the same order as they appeared
on the precompiler command line
For instance, the following command lines are valid, but only the
MY_FILE host language file is precompiled:
$ SQLPRE/PLI/LIS/DEB MY_FILE+MY_TLB_1/LIB+MY_TLB_2/LIB
$ SQLPRE/PASCAL MY_FILE,MY_OTHER_FILE
$ SQLPRE/COB/DEB MY_FILE,MY_NODB_FILE
$ SQLPRE/CC MY_FILE+REST_OF_APPL+APPL_TLB/LIB
For the previous command lines, the precompiler passes the
following corresponding command lines to the host language
compiler:
$ PLI/LIS/DEB MY_FILE.PLI;n+MY_TLB_1/LIB+MY_TLB_2/LIB/NOG_FLOAT
$ PAS MY_FILE.PAS;n,MY_OTHER_FILE
$ COB/DEB MY_FILE.COB;n,MY_NODB_FILE
$ CC MY_FILE.C;n+REST_OF_APPL+APPL_TLB/LIB/NOG_FLOAT
The ;n notation signifies the version number of the host language
file generated by the SQL precompiler.
5.2.22 – pre-lang-qualifiers
Refers to the host language in which the program containing
embedded SQL procedures is written. You must supply a language
qualifier. The host language qualifier values are ADA, CC,
CC=VAXC, CC=DECC, COBOL, FORTRAN, PASCAL, and PLI.
The following statements apply to the CC SQL precompiler switch:
o The CC=VAXC switch instructs the precompiler to compile
the source as a VAXC source. If the VAXC compiler is not
installed, the DECC compiler is used with the /STANDARD=VAXC
host language compiler switch.
o The CC=DECC switch instructs the precompiler to compile
the source as a DECC source. If the DECC compiler is not
installed, you will get a DCL error.
o The default keyword, either VAXC or DECC, is based on your
system configuration. If the VAXC compiler is installed on
your system, VAXC is the default keyword. If the DECC compiler
is installed, DECC is the default keyword. If both compilers
are installed, the default is based on whichever C compiler
your system manager has specified.
5.2.23 – pre-qualifiers
Refers to the optional qualifiers allowed on the SQL precompiler
command line.
5.2.24 – PRAGMA
Syntax options:
SQLOPTIONS= (PRAGMA = IDENT = string-literal)
SQLOPTIONS= (NOPRAGMA)
Using the IDENT keyword with the PRAGMA qualifier allows the user
to pass a text string to the SQL Precompiler 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$PRE/CC TEST/SQLOPTION=(PRAGMA=IDENT="v1.2-32")
5.2.25 – QUERY_CPU_TIME_LIMIT
Syntax options:
SQLOPTIONS= (QUERY_CPU_TIME_LIMIT=total-seconds)
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.
5.2.26 – QUERY_ESTIMATES
Syntax options:
SQLOPTIONS= (QUERY_ESTIMATES)
SQLOPTIONS= (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 QUERY_ESTIMATES
keyword, 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 SQLOPTIONS=QUERY_ESTIMATES qualifier is the default.
5.2.27 – QUERY_MAX_ROWS
Syntax options:
SQLOPTIONS= (QUERY_MAX_ROWS=total-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.
5.2.28 – QUERY_TIME_LIMIT
Syntax options:
SQLOPTIONS= (QUERY_TIME_LIMIT=total-seconds)
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.
5.2.29 – QUIET_COMMIT___NOQUIET_COMMIT
Syntax options:
SQLOPTIONS= (QUIET_COMMIT)
SQLOPTIONS= (NOQUIET_COMMIT)
QUIET COMMIT disables error reporting for the COMMIT and ROLLBACK
statements if either statement is executed when no transaction is
active.
By default, if there is no active transaction, SQL will raise
an error when COMMIT or ROLLBACK is executed. This default,
(NOQUIET_COMMIT), is retained for backward compatibility for
applications that wish to detect the situation. If QUIET_COMMIT
is specified, a COMMIT or ROLLBACK executes successfully when
there is no active transaction.
NOTE
Within a compound statement, the COMMIT and ROLLBACK
statements are always ignored if no transaction is active.
5.2.30 – ROLLBACK_ON_EXIT
Syntax options:
SQLOPTIONS= (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.
5.2.31 – TRANSACTION_DEFAULT
Syntax options:
SQLOPTIONS= (TRANSACTION_DEFAULT = IMPLICIT)
SQLOPTIONS= (TRANSACTION_DEFAULT = DISTRIBUTED)
SQLOPTIONS= (NOTRANSACTION_DEFAULT)
Specifies when SQL starts a transaction and how SQL handles
default distributed transactions. You can specify the following
options:
o SQLOPTIONS = (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 SQLOPTIONS = (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
SQL precompiled programs and to use the USING CONTEXT clause
in embedded SQL statements. 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 USING CONTEXT clause in embedded SQL
statements, you must declare a context structure.
o SQLOPTIONS=(NOTRANSACTION_DEFAULT)
Causes SQL not to start 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 SQLOPTIONS = (TRANSACTION_DEFAULT = IMPLICIT).
5.2.32 – USER_DEFAULT
Syntax options:
SQLOPTIONS= (USER_DEFAULT=username)
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.
5.2.33 – WARN
Syntax options:
SQLOPTIONS= WARN
SQLOPTIONS= NOWARN
Specifies whether or not the SQL precompiler writes informational
and warning messages to the preprocessed host language source
file and to SYS$ERROR and SYS$OUTPUT (if different from
SYS$ERROR). The WARN qualifier accepts the following options:
o [NO]WARNING
Specifies whether or not the SQL precompiler writes
informational and warning messages to your terminal, a list
file, or both.
o [NO]DEPRECATE
Specifies whether or not the SQL precompiler writes diagnostic
messages about deprecated features.
Deprecated features are features that are currently allowed
but 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.
The SQLOPTIONS=WARN qualifier is equivalent to the
SQLOPTIONS=WARN=(WARNING, DEPRECATE) qualifier. The
SQLOPTIONS=NOWARN qualifier is equivalent to the
SQLOPTIONS=WARN=(NOWARNING, NODEPRECATE) qualifier.
You can specify the SQLOPTIONS=WARN=WARNING qualifier if you
prefer to have all warning messages except those about deprecated
features. You can specify the SQLOPTIONS=WARN=(NOWARNING,
DEPRECATE) qualifier if you prefer the deprecated feature
messages only.
5.2.34 – warning-option
Specifies whether the SQL precompiler writes warning or
diagnostic messages to your terminal, a list file, or both. Use
either or both the [NO]WARNING or [NO]DEPRECATE options with the
WARN qualifier. If you specify only a single warning option, you
do not need to use parentheses.
6 – Host Language Variable Declarations
The SQL precompiler recognizes only a subset of valid host
language variable declarations. If you refer to a variable
declaration that SQL does not recognize in an embedded SQL
statement, the precompiler generates a fatal error when it
encounters that reference.
Oracle Rdb databases and the various host languages supported
by the SQL precompiler do not necessarily support the same set
of data types. The precompiler recognizes host language variable
declarations that are equivalent to SQL data types plus a subset
of other host language variable declarations.
o For host language variable declarations of data types that are
equivalent to SQL data types, the precompiler passes values
directly between the database and the host language variable.
o Host language floating point data types will be interpreted as
having representations as determined by the /FLOAT qualifier
on the precompiler command line and individual language
rules. These rules are discussed in the host language-specific
sections that follow. In these sections, selects will be shown
from a table defined as follows:
CREATE TABLE TESTTBL (
KEYFIELD CHAR(10) PRIMARY KEY,
FLOAT1 REAL,
FLOAT2 DOUBLE PRECISION);
o For each host language, the precompiler also supports a
limited number of host language variable declarations that
do not correspond to SQL data types. SQL converts database
values to the host language data type and host language values
to the supported data type. SQL makes this conversion only for
a subset of valid host language declarations.
The following table shows the date-time data types that the
precompiler supplies.
Table 6 Precompiler Date-Time Data Mapping
Module Language
and Interactive
SQL Precompiler
DATE SQL_DATE
DATE_ANSI SQL_DATE_ANSI
DATE_VMS SQL_DATE_VMS
TIME SQL_TIME
TIMESTAMP SQL_TIMESTAMP
INTERVAL YEAR SQL_INTERVAL (YEAR)
INTERVAL YEAR TO SQL_INTERVAL (YEAR TO MONTH)
MONTH
INTERVAL MONTH SQL_INTERVAL (MONTH)
INTERVAL DAY SQL_INTERVAL (DAY)
INTERVAL DAY TO SQL_INTERVAL (DAY TO HOUR)
HOUR
INTERVAL DAY TO SQL_INTERVAL (DAY TO MINUTE)
MINUTE
INTERVAL DAY TO SQL_INTERVAL (DAY TO SECOND)
SECOND
INTERVAL HOUR SQL_INTERVAL (HOUR)
INTERVAL HOUR TO SQL_INTERVAL (HOUR TO MINUTE)
MINUTE
INTERVAL HOUR TO SQL_INTERVAL (HOUR TO SECOND)
SECOND
INTERVAL MINUTE SQL_INTERVAL (MINUTE)
INTERVAL MINUTE SQL_INTERVAL (MINUTE TO SECOND)
TO SECOND
INTERVAL SECOND SQL_INTERVAL (SECOND)
o For all other host language variable declarations, the
precompiler generates an error when it encounters a reference
to them in embedded SQL statements.
NOTE
The ANSI/ISO SQL standard specifies that variables used
in embedded SQL statements must be declared within a
pair of embedded SQL BEGIN DECLARE . . . END DECLARE
statements. The Oracle Rdb SQL precompiler does not enforce
this restriction. If you use the BEGIN DECLARE . . . END
DECLARE statements, SQL generates a warning message when
it encounters a variable declared outside of a BEGIN
DECLARE . . . END DECLARE block.
If ANSI/ISO SQL compliance is important for your
application, you should include all declarations for
variables used in embedded SQL statements within a BEGIN
DECLARE . . . END DECLARE block. See the Oracle Rdb SQL
Reference Manual on the SQL module language for more
information on the BEGIN DECLARE statement.
If you do not declare character variables using syntax that
specifies a character set or by defining the RDB$CHARACTER_SET
logical name, the SQL precompiler uses the UNSPECIFIED character
set. When you use the UNSPECIFIED character set, the precompiler
does not check to see if the character set of the variables
matches the character sets of the database. For more information
regarding the logical name, see the Oracle Rdb SQL Reference
Manual.
The RDB$CHARACTER_SET logical name is deprecated and will not be
supported in a future release.
6.1 – Character Parameters
To ensure that you specify the length of character variables
correctly, use the following guidelines:
o For the C language, any character variables 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 languages supported by the SQL precompiler, any
character variables 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 whether the SQL precompiler
interprets the length of columns in characters or octets. A
program can control how the SQL precompiler interprets the
length of columns in the following ways:
- The CHARACTER LENGTH clause of the DECLARE MODULE statement
- The DIALECT clause of the DECLARE MODULE statement
- For dynamic SQL, the SET CHARACTER LENGTH statement
6.2 – Ada Variables
SQL lets you declare host language variables directly or by
calling the Ada package, SQL_STANDARD.
You must use the SQL_STANDARD package if you want to conform to
the ANSI/ISO SQL standard. This package defines the data types
that are supported by the ANSI/ISO SQL standard. To use the
package, first copy the file SYS$COMMON:[SYSLIB]SQL$STANDARD.ADA
to your own Ada library, and then compile the package.
The package SQL_STANDARD declares the following ANSI-standard
data types:
o CHAR
o SMALLINT
The data type SMALLINT contains one subtype: INDICATOR_TYPE.
o INT
o REAL
o DOUBLE_PRECISION
o SQLCODE_TYPE
The data type SQLCODE_TYPE contains two subtypes: NOT_FOUND
and SQL_ERROR.
o SQLSTATE_TYPE
If ANSI/ISO SQL compliance is not important for your application,
you can declare host language variables directly. The following
list describes the variable declaration syntax that the SQL
precompiler supports in Ada:
o Standard package data types
- STRING
- CHARACTER
- SHORT_SHORT_INTEGER
- SHORT_INTEGER
- INTEGER
- FLOAT
By default, Ada recognizes the FLOAT data type as an F-
floating representation of floating-point data. However,
Ada also allows you to override the default and specify
that FLOAT denotes an IEEE S-Floating representation by
using the FLOAT_REPRESENTATION(IEEE_FLOAT) pragma or using
ACS CREATE LIBRARY or SET PRAGMA commands. This default
can also be overridden at installation time. SQL does
not recognize whether or not you override the F-floating
default for the FLOAT data type. If you do override the
FLOAT default, you will get Ada compile-time errors. These
compile-time errors can be overcome by using a /FLOAT=IEEE_
FLOAT qualifier with the SQL$PRE command.
To avoid problems with the ambiguity in the FLOAT data
type, use the SYSTEM package F_FLOAT and IEEE_SINGLE_FLOAT
data types.
- LONG_FLOAT
By default, Ada recognizes the LONG_FLOAT data type as a
G-floating representation of floating-point data. However,
Ada also allows you to override the default and specify
that LONG_FLOAT denotes an IEEE S-Floating representation
by using the FLOAT_REPRESENTATION(IEEE_FLOAT) pragma or
using ACS CREATE LIBRARY or SET PRAGMA commands. This
default can also be overridden at installation time.
In addition, if the FLOAT_REPRESENTATION is VAX_FLOAT
(the default), Ada allows you to specify that the LONG_
FLOAT data type be represented by a D-Floating format
by specifying the LONG_FLOAT(D_FLOAT) pragma. SQL does
not recognize whether or not you override the G-floating
default for the LONG_FLOAT data type. If you do override
the LONG_FLOAT default, you will get Ada compile-time
errors. These compile-time errors can be overcome by using
a /FLOAT qualifier with the SQL$PRE command to specify
either D_FLOATING or IEEE_FLOATING as appropriate.
To avoid problems with the ambiguity in the LONG_FLOAT data
type, use the SYSTEM package G_FLOAT, D_FLOAT, and IEEE_
DOUBLE_FLOAT data types.
NOTE
SQL$PRE will issue a warning (%SQL-W-NOFLOAT) if you
use a /FLOAT qualifier with an /ADA qualifier because
the ADA command does not have a /FLOAT qualifier.
But if you use a pragma FLOAT REPRESENTATION to
override the default floating point formats you
must use the /FLOAT qualifier to let SQL$PRE know
about this floating point format since it does not
recognize the pragma. Simply ignore the warning. In
addition to supporting IEEE formats, SQL$PRE allows
the default G_FLOAT format for 64-bit floating point
types to be overridden using a combination of the
pragma FLOAT REPRESENTATION specifying VAX_FLOAT and
the pragma LONG FLOAT specifying D_FLOAT. To use this
combination, specify an SQL$PRE qualifier of /FLOAT=D_
FLOAT.
The following example shows an Ada program with embedded
SQL that will work correctly with SQL$PRE/ADA/FLOAT=IEEE:
PRAGMA FLOAT REPRESENTATION IEEE_FLOAT;
WITH SYSTEM; USE SYSTEM;
WITH STANDARD; USE STANDARD;
WITH SQL_STANDARD; USE SQL_STANDARD;
. . .
PROCEDURE TESTIT IS
EXEC SQL BEGIN DECLARE SECTION;
KEYFIELD : STRING(1..10);
FLOATER : LONG_FLOAT; -- package STANDARD
SQLFLOATER : REAL; -- package SQL_STANDARD
GFLOATER : G_FLOAT; -- package SYSTEM
SFLOATER : IEEE_SINGLE_FLOAT; -- package SYSTEM
TFLOATER : IEEE_DOUBLE_FLOAT; -- package SYSTEM
EXEC SQL END DECLARE SECTION;
. . .
BEGIN
. . .
KEYFIELD := "1.0 ";
EXEC SQL SELECT FLOAT1, FLOAT2 INTO :SQLFLOATER, :GFLOATER
WHERE KEYFIELD = :KEYFIELD;
. . .
KEYFIELD := "2.0 ";
EXEC SQL SELECT FLOAT1, FLOAT2 INTO :SFLOATER, :TFLOATER
WHERE KEYFIELD = "KEYFIELD;
. . .
KEYFIELD := "3.0 ";
EXEC SQL SELECT FLOAT1, FLOAT2 INTO :FLOATER, TFLOATER
WHERE KEYFIELD = KEYFIELD;
o Date-time data types
The precompiler translates lines in a precompiled program that
contain any of the date-time data types.
NOTE
Oracle Rdb reserves the right to change the code
generated in translation of date-time data types at any
time, without prior notice.
- SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS
- SQL_TIME, SQL_TIMESTAMP
- SQL_INTERVAL (DAY TO SECOND)
Use this data type for variables that represent the
difference between two dates or times. (Precompiler Date-
Time Data Mapping lists all the supported INTERVAL data
types.)
o SQL definition package
The precompiler generates a package that includes definitions
for the following data types if Ada object declarations refer
to them:
- SQL_VARCHAR_n
Use this data type for variables that correspond to VARCHAR
and LONG VARCHAR columns in a database, where n is the
length specified in the definition of the columns (always
16,383 characters for LONG VARCHAR columns).
SQL declares a two-field Ada record when it encounters
SQL_VARCHAR_n, with one field, t, containing the character
string, and the second field, l, containing an integer
denoting the length of the string.
You can refer to the l field to determine the actual length
of a varying character string, and refer to the t field to
refer to the string itself. This excerpt from the online
sample program sql_all_datatypes.sqlada refers to the l
field to see if the value in an SQL_VARCHAR_n field is
null.
.
.
.
-- Variables for main program use
type ALL_DATATYPES_RECORD_TYPE IS
record
.
.
.
VARCHAR_VAR : sql_varchar_40;
end record;
.
.
.
-- The following if statements evaluate the contents of main variables
-- and then set indicators as appropriate.
.
.
.
if all_datatypes_record.varchar_var.l = 0 then
indicator_group(7) := -1; end if;
- SQLDA_ACCESS
Specifying this data type declares an SQLDA structure.
It offers an advantage over an embedded INCLUDE SQLDA
statement because you can use it in more than one
declaration to declare multiple SQLDA structures.
o CDD_TYPES package data types (must specify WITH CDD_TYPES)
- DATE_TIME_DATATYPE (Oracle Rdb recommends that you use
SQL_TIMESTAMP)
- SHORT_INTEGER_ARRAY (for indicator arrays only)
o SYSTEM package data types (must specify WITH SYSTEM)
- D_FLOAT
- G_FLOAT
- F_FLOAT
- IEEE_SINGLE_FLOAT
- IEEE_DOUBLE_FLOAT
o Arrays
Single-dimension arrays are supported to declare an indicator
array to refer to a structure in SQL statements. The elements
of the array must be declared as word integers (SHORT_
INTEGER).
Character arrays are supported as types or subtypes but cannot
refer to derived types.
SQL does not allow references to unconstrained arrays.
o Types
The precompiler recognizes types for all the preceding data
types plus records, derived types, and arrays.
- Records can refer to any recognized type.
- Derived types (NEW keyword) can refer to any recognized
type. SQL allows but ignores range constraints in derived
types.
SQL does not allow references to types that use discriminants
in any way or to access types. SQL does not allow references
to integer (RANGE keyword), floating-point (DIGITS keyword),
or fixed-point (DELTA keyword) types.
o Subtypes
Subtypes can refer to any recognized type. SQL allows but
ignores range constraints in subtypes.
o Assignments from expressions in declarations
o Context structure types
When you write applications for the Ada precompiler, you
should declare a context structure by declaring a variable
of data type SQLCONTEXT_REC instead of declaring a structure.
When you declare a variable with the data type SQLCONTEXT_REC,
the Ada precompiler generates a context structure for you. For
example, you declare the variable using the following code:
context_struc.sqlcontext_ver := 1;
context_struc.sqlcontext_tid.sqlcontext_tid_type := 1
context_struc.sqlcontext_tid.sqlcontext_tid_len := 16;
context_struc.sqlcontext_tid.sqlcontext_tid_value(1) := 0;
context_struc.sqlcontext_tid.sqlcontext_tid_value(2) := 0;
context_struc.sqlcontext_tid.sqlcontext_tid_value(3) := 0;
context_struc.sqlcontext_tid.sqlcontext_tid_value(4) := 0;
context_struc.sqlcontext_end := 0;
6.3 – C Variables
NOTE
C is a case-sensitive language. The names of objects
declared in a C program are case sensitive, but the names
of SQL tables and other names are not case sensitive.
Therefore, you must be careful about C constructs that
you specify in SQL statements. These constructs include
variable names and labels of program sections. See the
Oracle Rdb Guide to SQL Programming for more information
about declaring C variables.
The following list describes the variable declaration syntax for
character data types that the SQL precompiler supports in C:
o char x[n]
o char *x, assumes LONG VARCHAR type (that is, char x[16383])
o char CHARACTER SET character-set-name clause
The CHARACTER SET character-set-name clause is optional.
o $SQL_VARCHAR (n)
o $SQL_VARCHAR (n) CHARACTER SET character-set-name
The CHARACTER SET clause is optional.
For information about the supported character sets, see the
Oracle Rdb SQL Reference Manual.
The following list describes the variable declaration syntax that
the SQL precompiler supports in C:
o Data type keywords
o Storage class identifiers and modifiers
o struct
o union
o typedef
o Initial value assignments
o Arrays
Only single-dimension arrays are supported and only to declare
an indicator array for use with a reference to a structure
in SQL statements. Furthermore, the size of the array must be
specified explicitly. Although you can use any data type for
indicator array elements, Oracle Rdb recommends that you use
variables of the data type integer, such as int or short.
o Pointers
Only a single level of pointer variables are supported and
only those that point to elementary data types.
Because C pointer variables cannot specify length attributes,
SQL sometimes must allocate the largest possible piece of
memory to process statements that refer to char pointer
variables. SQL cannot determine the length of char pointer
variables and allocates 16,383 bytes of memory for each
variable in the following cases:
- The SQL statement contains a concatenated value expression
or a substring.
- The SQL statement refers to the char pointer variable in a
predicate, such as WHERE EMP_ID = :POINTER_VAR.
- The SQL statement converts the contents of the char pointer
variable to a numeric data type in the database.
Avoid the use of char pointer variables in these cases because
such a large memory allocation for char pointer variables
wastes memory and degrades performance, especially for remote
database access.
o Valid declaration syntax
The following are examples of valid declaration syntax:
a_var[10];
$SQL_VARCHAR(10) x,y,z;
int SQLCODE;
struct
{
char b_var[5];
short int c_var;
} a_record;
union
{
char string_date[17];
struct
{
char year_var1[2];
char year_var2[2];
char month_var[2];
char day_var[2];
char hour_var[2];
char minute_var[2];
char second_var[2];
char hundredth_var[2];
} date_group;
} date_union;
int indicator_item[2];
globaldef double c_var;
static d_var;
char *x;
o Invalid declaration syntax
Table 7 Supported C Datatypes
C
type
or
typedefSQL type Comments and Restrictions
char CHARACTER
char LONG VARCHAR Assumed to be VARCHAR (16383)
*
char CHARACTER n must be be an integer literal;
[n] #define names or expressions are not
supported.
int INTEGER Cannot be specified as unsigned.
short SMALLINT Cannot be specified as unsigned.
short INTEGER Cannot be specified as unsigned.
int
long INTEGER Cannot be specified as unsigned.
int
float REAL
double DOUBLE PRECISION
enum INTEGER
long INTEGER On OpenVMS the data type long is 32
bits
int8 TINYINT Requires #include <ints.h>
int16 SMALLINT Requires #include <ints.h>
__int16SMALLINT
int32 INTEGER Requires #include <ints.h>
__int32INTEGER
int64 BIGINT Requires #include <ints.h>
__int64BIGINT
$SQL_VARCHAR (n) The CHARACTER SET clause is optional.
SQL_DATE The SQL precompiler will transform the
SQL_DATE_ANSI pseudo types in natvie C datatypes
SQL_DATE_VMS
SQL_TIME
SQL_TIMESTAMP
SQL_INTERVAL (DAY Use this data type for variables that
TO SECOND) represent the difference between two
dates or times. Precompiler Date-Time
Data Mapping lists all the supported
INTERVAL data types.
6.4 – COBOL Variables
The following list describes the variable declaration syntax for
character data types that the SQL precompiler supports in COBOL:
o PICTURE IS can be abbreviated as PICTURE or PIC.
o CHARACTER SET character-set-name PICTURE IS.
o PICTURE clauses for numeric variables must begin with S (must
be signed) and cannot include P characters.
o PICTURE clauses cannot include editing characters.
For information about the supported character sets, see the
Oracle Rdb SQL Reference Manual.
The following list describes the variable declaration syntax that
the SQL precompiler supports in COBOL:
o PICTURE IS clause
- PICTURE IS can be abbreviated as PICTURE or PIC.
- PICTURE clauses for numeric variables must begin with S
(must be signed) and cannot include P characters.
- PICTURE clauses cannot include editing characters.
o USAGE IS clause
- USAGE IS must immediately follow a PICTURE clause.
- USAGE IS can be abbreviated as USAGE or omitted completely.
- USAGE IS must have as an argument BINARY, COMPUTATIONAL,
COMPUTATIONAL-1, COMPUTATIONAL-2, or COMPUTATIONAL-3.
COMPUTATIONAL can be abbreviated as COMP in all USAGE IS or
DISPLAY declarations. BINARY is a synonym for COMPUTATIONAL
or COMP.
o VALUE IS clause
VALUE IS can be abbreviated as VALUE and is allowed without
restriction.
o IS EXTERNAL clause
IS EXTERNAL can be abbreviated as EXTERNAL and is allowed
without restriction.
o IS GLOBAL clause
IS GLOBAL can be abbreviated as GLOBAL and is allowed without
restriction.
o SIGN clause
SIGN is allowed but must immediately follow a PICTURE clause
or a USAGE IS clause.
o Group data items
- Group data items are allowed without restriction.
- Variables associated with the SQL VARCHAR and LONG VARCHAR
data types must be declared as group data items with two
elementary items at level 49. The first elementary item
must be a small integer to contain the actual length of
the character string. The second elementary item must be a
character string long enough to contain the string itself.
* Declaration for an SQL column
* defined as VARCHAR (80):
*
01 VARYING_STRING.
49 STRING_LENGTH PIC S9(4) USAGE IS COMP.
49 STRING_TEXT PIC X(80).
o OCCURS n TIMES clause
- OCCURS clauses are permitted only for declarations of
indicator arrays. Although you can use any data type for
indicator array elements, Oracle Rdb recommends that you
declare them as integers (PIC S9(9) COMP).
- Multidimension tables (nested OCCURS clauses) and variable-
occurrence data items (OCCURS DEPENDING ON clause) are not
supported.
o REDEFINES clauses
You can refer to host language variables that have a REDEFINES
clause or that are subordinate to a REDEFINES clause.
o SQL date-time data types
- SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS
- SQL_TIME, SQL_TIMESTAMP
- SQL_INTERVAL (DAY TO SECOND)
Use this data type for variables that represent the
difference between two dates or times. (Precompiler Date-
Time Data Mapping lists all the supported INTERVAL data
types.)
The precompiler replaces these data types with host language
data declarations that are supported in the compilers
themselves.
6.5 – FORTRAN Variables
The following list describes the variable declaration syntax
for character data types that the SQL precompiler supports in
FORTRAN:
o CHARACTER
o CHARACTER character-set-name
For information about the supported character sets, see the
Oracle Rdb SQL Reference Manual.
The following list describes the variable declaration syntax that
the SQL precompiler supports in FORTRAN:
o Declarations - See the following table
o Initial values assigned in the declaration
o STRUCTURE declarations
o UNION declarations within structures
o RECORD statements
o DIMENSION statements
- DIMENSION statements are permitted only for declarations
of indicator arrays. Although you can use any data type for
indicator array elements, Oracle Rdb recommends that you
use variables of the INTEGER data type.
- Multidimension arrays and dynamic-sized arrays are not
supported.
Table 8 Supported FORTRAN Datatypes
FORTRAN type SQL type Comments and Restrictions
BYTE TINYINT
CHARACTER*n CHAR The n represents a positive integer
literal
INTEGER INTEGER
INTEGER*1 TINYINT
INTEGER*2 SMALLINT
INTEGER*4 INTEGER
INTEGER*8 BIGINT
LOGICAL INTEGER
LOGICAL*1 TINYINT
LOGICAL*2 SMALLINT
LOGICAL*4 INTEGER
LOGICAL*8 BIGINT
REAL REAL
REAL*4 REAL
REAL*8 DOUBLE
PRECISION
STRUCTURE VARCHAR The named structure can be used
/name/ to define other FORTRAN host
integer*2 variables. The len component of
len the structure must be set to the
character*n correct length of the string before
body use as a parameter to SQL. The
END n represents a positive integer
STRUCTURE literal
SQL_DATE The SQL precompiler will transform
SQL_DATE_ the pseudo types in native FORTRAN
ANSI datatypes.
SQL_DATE_VMS
SQL_TIME
SQL_
TIMESTAMP
SQL_INTERVAL Use this data type for variables
(DAY TO that represent the difference
SECOND) between two dates or times.
Precompiler Date-Time Data Mapping
lists all the supported INTERVAL
data types.
Implicit declarations are not supported. SQL generates a "host
variable was not declared" error when it encounters an implicitly
declared variable in an SQL statement.
6.6 – Pascal Variables
The following list describes the variable declaration syntax that
the SQL precompiler supports in Pascal:
o Data type keywords
Declarations can include only the following Pascal data
types:
- INTEGER8, INTEGER16, INTEGER32, and INTEGER64
- REAL
- SINGLE
- DOUBLE
- F_FLOAT
- D_FLOAT
- G_FLOAT
- S_FLOAT
- T_FLOAT
- CHAR
- PACKED ARRAY [1..n] OF CHAR;
- VARYING [u] OF CHAR
- [BYTE] -128..127;
- [WORD] -32768..32767;
- Date-time data types (Precompiler Date-Time Data Mapping
lists these data types.)
In addition, the SQL Pascal precompiler provides the following
data types:
- SQL_LONG_VARCHAR
- SQL_DATE
- SQL_SMALLINT
- SQL_INDICATOR
- SQL_BIGINT
- SQL_QUAD
- SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS
- SQL_TIME, SQL_TIMESTAMP
- SQL_INTERVAL (DAY TO SECOND)
Use this data type for variables that represent the
difference between two dates or times. (Precompiler Date-
Time Data Mapping lists all the supported INTERVAL data
types.)
o Records
The SQL precompiler supports Pascal record definitions. It
also supports nested records such as the following:
type_record_type = record
employee_id : employee_id_str;
last_name : last_name_str;
first_name : first_name_str;
middle_init : middle_init_str;
address_dat1: address_str;
address_dat2: address_str;
city : city_str;
state : state_str;
postal_code : postal_code_str;
sex : sex_str;
status_code : status_code_str;
end;
name_rec = record
last_name : last_name_str;
first_name : first_name_str;
middle_init : middle_init_str;
end;
address_rec = record
address_dat1 : address_str;
address_dat2 : address_str;
city : city_str;
state : state_str;
postal_code : postal_code_str;
end;
rec_in_rec = record
employee_id : employee_id_str;
emp_name : name_rec;
emp_addr : address_rec;
sex : sex_str;
status_code : status_code_str;
end;
rec_in_rec_in_rec = record
nested_again : rec_in_rec;
end;
A record that is used in an SQL statement cannot contain a
pointer to another record.
The SQL precompiler does not support variant records.
o Initial value assignments
The SQL precompiler supports initial values assigned in the
declaration:
dateind : SQL_INDICATOR:=0;
o Arrays
Packed arrays are supported to declare SQL character strings.
Single-dimension arrays are supported to declare an indicator
array to refer to a structure in SQL statements. The elements
of the array must be declared as word integers [WORD]-
32768..32767 or SQL_INDICATOR.
o Pointers
The SQL precompiler for Pascal supports one level of pointers.
type
a = ^integer;
var
b : a; (* the use of the variable b is supported *)
c : ^a; (* do not use any form of variable c in an SQL statement)
NOTE
The Pascal precompiler for SQL gives an incorrect %SQL-I-
UNMATEND error when it parses a declaration of an array
of records. It does not associate the END with the record
definition, and the resulting confusion in host variable
scoping causes a fatal error.
To avoid the problem, declare the record as a type and then
define your array of that type. For example:
main.spa:
program main (input,output);
type
exec sql include 'bad_def.pin'; !gives error
exec sql include 'good_def.pin'; !ok
var
a : char;
begin
end.
---------------------------------------------------------------
bad_def.pin
x_record = record
y : char;
variable_a: array [1..50] of record
a_fld1 : char;
b_fld2 : record;
t : record
v : integer;
end;
end;
end;
end;
---------------------------------------------------------------
good_def.pin
good_rec = record
a_fld1 : char;
b_fld2 : record
t : record
v: integer;
end;
end;
end;
x_record = record
y : char
variable_a : array [1..50] of good_rec;
end;
6.7 – PLI Variables
The following list describes the variable declaration syntax that
the SQL precompiler supports in PL/I:
o Declarations
Declarations can include only the following PL/I data types:
- CHARACTER
CHARACTER can be abbreviated as CHAR.
- CHARACTER VARYING
CHARACTER VARYING can be abbreviated as CHAR VAR.
- Date-time data types (Precompiler Date-Time Data Mapping
lists these data types.)
- TINYINT
TINYINT is FIXED BINARY(7).
- FIXED BINARY, FIXED DECIMAL
BINARY can be abbreviated as BIN, and DECIMAL can be
abbreviated as DEC. Scale factors are not allowed on FIXED
BINARY declarations.
- FLOAT BINARY, FLOAT DECIMAL
- SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS
- SQL_TIME, SQL_TIMESTAMP
- SQL_INTERVAL (DAY TO SECOND)
Use this data type for variables that represent the
difference between two dates or times. (Precompiler Date-
Time Data Mapping lists all the supported INTERVAL data
types.)
- DECIMAL data type is converted to FIXED
- NUMERIC data type is converted to PACKED
o Storage class attributes
Any of the storage class attributes (BASED, AUTOMATIC,
DEFINED, STATIC, variable, EXTERNAL, and INTERNAL) is allowed.
The BASED attribute declarations must include a location
reference.
o INITIAL attribute
o Structures
Structures are allowed without restriction.
o Arrays
Arrays are permitted only for declarations of indicator
arrays. Although you can use any data type for indicator
array elements, Oracle Rdb recommends that you declare them
as INTEGER variables.
Multidimension array items are not supported. Arrays of
structures are not supported. Arrays that are in a group that
is itself an array are not supported. Dynamic-sized arrays are
not supported.