Specifies characteristics, such as character sets, quoting rules,
and the default date format for a nonstored module.
1 – Environment
You can use the DECLARE MODULE statement:
o Embedded in host language programs to be precompiled
o In a context file
This command is not executable.
2 – Format
(B)0[m[1;4mDECLARE[m[1m [1;4mMODULE[m[1m <module-name> qqwqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m mq> [1;4mDIALECT[m[1m environment qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqwqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m mq> char-set-options qj mqqq> [1;4mCATALOG[m[1m <catalog-name> qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqwqqqqqqqqqqqqqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwk [m
[1m mq> [1;4mSCHEMA[m[1m <schema-name> jmq> [1;4mAUTHORIZATION[m[1m <auth-id> qjx [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq>[m
[1m mq> [1;4mPRAGMA[m[1m (module-pragma-list) qj mq> module-language-options qj [m
[1m [m
(B)0[m[1menvironment = [m
[1mqqwqq> [1;4mSQL99[m[1m qqqqqwq> [m
[1mtqq> [1;4mSQL92[m[1m qqqqqu [m
[1m tqq> [1;4mSQL89[m[1m qqqqqu [m
[1m tqq> [1;4mSQLV40[m[1m qqqqu [m
[1m mqq> [1;4mMIA[m[1m qqqqqqqj [m
[1m [m
(B)0[m[1mchar-set-options = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqq> qqqqqqqqqqqqqwqqqqqk [m
[1m mqq> [1;4mNAMES[m[1m [1;4mARE[m[1m names-char-set qqj x [m
[1mlqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqj [m
[1mmwqwqqqqqqqqqqqqqqq> qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> [m
[1m x tqq> [1;4mLITERAL[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m support-char-set qqqqu x [m
[1m x tqq> [1;4mNATIONAL[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m support-char-set qqqu x [m
[1m x tqq> [1;4mDEFAULT[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m support-char-set qqqqu x [m
[1m x tqq> [1;4mIDENTIFIER[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m names-char-set qqqu x [m
[1mx[m [1mmqq> [1;4mDISPLAY[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m names-char-set qqqqqqj[m [1mx [m
[1mmqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mmodule-pragma-list = [m
[1m [m
[1m qqqqqqqqqqqqqqqqq> [1;4mIDENT[m[1m string-literal qqqqqqqqqqq>[m
(B)0[m[1mmodule-language-options = [m
[1m [m
[1mqwqwqqq> [1;4mALIAS[m[1m <alias-name> qqqqqqqqqqqqqqqqqqqqqqqqwqwqq>[m
[1m x tqqq> [1;4mCHARACTER[m[1m [1;4mLENGTH[m[1m qwq> [1;4mCHARACTERS[m[1m qwqqqqqqqqu x [m
[1m x x mq> [1;4mOCTETS[m[1m qqqqqj x x [m
[1m x tqqq> [1;4mDEFAULT[m[1m [1;4mDATE[m[1m [1;4mFORMAT[m[1m qqwqq> [1;4mSQL99[m[1m qwqqqqqqqqu x [m
[1m x x tqq> [1;4mSQL92[m[1m qu x x [m
[1mx[m [1mx[m [1mmqq> [1;4mVMS[m[1m qqqj[m [1mx[m [1mx[m
[1m x tqqq> [1;4mKEYWORD[m[1m [1;4mRULES[m[1m environment qqqqqqqqqqqqqqqqqu x [m
[1m x tqqq> [1;4mPARAMETER[m[1m [1;4mCOLONS[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m x tqqq> [1;4mQUOTING[m[1m [1;4mRULES[m[1m environment qqqqqqqqqqqqqqqqqu x [m
[1m x tqqq> [1;4mRIGHTS[m[1m qqwqq> [1;4mINVOKER[m[1m qqqwqqqqqqqqqqqqqqqqqu x [m
[1m x x mqq> [1;4mRESTRICT[m[1m qqj x x [m
[1m x tqqq> [1;4mVIEW[m[1m [1;4mUPDATE[m[1m [1;4mRULES[m[1m environment qqqqqqqqqqqqqu x [m
[1m x tqqq> [1;4mQUIET[m[1m [1;4mCOMMIT[m[1m qwq> [1;4mON[m[1m qqqwqqqqqqqqqqqqqqqqqqu x [m
[1mx[m [1mx[m [1mmq> [1;4mOFF[m [1mqqj[m [1mx[m [1mx[m
[1mx[m [1mmqqq> [1;4mCOMPOUND[m[1m [1;4mTRANSACTIONS[m[1m [m [1mqwq> [1;4mINTERNAL[m [1mqwqqqqj[m [1mx[m
[1mx [m [1mmq> [1;4mEXTERNAL[m [1mqj[m [1mx[m
[1mmqqqqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqj[m
3 – Arguments
3.1 – ALIAS alias name
Specifies the module alias. 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.
3.2 – AUTHORIZATION
Specifies the authorization identifier for the module. If you
do not specify a schema clause, the authorization identifier
specifies the default schema.
To comply with the ANSI/ISO 1989 standard, specify the
AUTHORIZATION clause without the schema name. Specify both the
AUTHORIZATION clause and the schema name to comply with the
ANSI/ISO SQL 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 SCHEMA clause and the DECLARE
ALIAS statement 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 use
the RIGHTS RESTRICT clause. The RIGHTS RESTRICT 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 – CATALOG catalog name
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.4 – CHARACTER_LENGTH
Syntax options:
CHARACTER LENGTH CHARACTERS | CHARACTER LENGTH OCTETS
Specifies whether the length of character string parameters,
columns, and domains are interpreted as characters or octets. The
default is octets.
3.5 – DEFAULT_CHARACTER_SET
Specifies the character set for parameters that are not qualified
by a character set. The default is DEC_MCS. This clause overrides
the character set specified in the NAMES ARE clause. See
Supported Character Sets for a list of the allowable character
sets.
3.6 – DEFAULT_DATE_FORMAT
Syntax options:
DEFAULT DATE FORMAT { SQL99 | SQL92 | VMS }
Controls the default interpretation for the data type of
the CURRENT_TIMESTAMP built in function and column or CAST
expressions with the DATE data type. The DATE and CURRENT_
TIMESTAMP data types can be either VMS or ANSI/ISO Standard
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.
If you specify SQL99 or SQL92, both data types are interpreted as
SQL standard 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 SQLOPTIONS =
ANSI_DATE qualifier because the qualifier will be deprecated in a
future release.
3.7 – DIALECT
Controls the following settings:
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 can 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
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 specified
before it or be overridden by clauses specified after it.
The following statements are specific to the SQL99 dialect:
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 will 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 SET_DIALECT statement for more information about
dialects.
3.8 – DISPLAY CHARACTER SET names-char-set
Specifies the character set encoding and characteristics expected
of text strings returned back to SQL from Oracle Rdb.
3.9 – IDENTIFIER_CHARACTER_SET
Specifies the character set used for database object names
such as table names and column names. This clause overrides the
character set specified in the NAMES ARE clause. See the Oracle
Rdb SQL Reference Manual for a list of allowable character sets
and option values.
The specified character set must contain ASCII characters.
3.10 – KEYWORD_RULES
Controls whether or not identifiers can be keywords. If you
specify SQL99, SQL92, 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 SQLOPTIONS = ANSI_
IDENTIFIER qualifier because the qualifier will be deprecated in
a future release.
3.11 – 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 Supported Character Sets for a list of the allowable
character sets.
3.12 – MODULE module name
An optional name for the nonstored module. If you do not supply a
module name, the default name is SQL_MODULE.
Use any valid OpenVMS name. (See the User_Supplied_Names HELP
topic 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.13 – NAMES_ARE
Specifies the character set used for the default, identifier,
and literal character sets for the module. 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.
You must ensure that the character set specified in this clause
matches the character set of all the databases attached to by
any particular connection and must contain ASCII characters. See
the Oracle Rdb SQL Reference Manual for a list of the allowable
character sets.
3.14 – NATIONAL_CHARACTER_SET
Specifies the character set for literals qualified by the
national character set. See Supported Character Sets for a list
of the allowable character sets.
3.15 – PARAMETER_COLONS
If you use the PARAMETER COLONS clause, all parameter names
must begin with a colon (:). This is valid in context files for
module language only. 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, required colons
will be the default because it allows processing of ANSI/ISO SQL
standard modules.
Use the PARAMETER COLONS clause, rather than the SQLOPTIONS
deprecated in a future release.
3.16 – QUOTING_RULES
Controls whether double quotation marks are interpreted as string
literals or delimited identifiers. If you specify SQLV40, SQL
interprets double quotation marks as literals. All other dialects
interpret double quotation marks as delimited identifiers. The
default is SQLV40.
Use the QUOTING RULES clause, rather than the SQLOPTIONS = ANSI_
QUOTING qualifier because the qualifier will be deprecated in a
future release.
3.17 – RIGHTS
Syntax options:
RIGHTS INVOKER | RIGHTS RESTRICT
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 prevents any user other than one with the
correct authorization identifier from invoking that module.
All applications that use multischema restrict 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 SQLOPTIONS = ANSI_
AUTHORIZATION qualifier because the qualifier will be deprecated
in a future release.
3.18 – SCHEMA schema name
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 names and other schema names
with an authorization identifier. If you do not specify a
default schema name for a module, you must specify a default
authorization identifier.
Using the SCHEMA clause, separate 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 auth-id, 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.
3.19 – VIEW_UPDATE_RULES
Specifies whether or not the SQL module processor applies the
ANSI/ISO SQL standard for updatable views to all views created
during compilation.
If you specify SQL99, SQL92, SQL89, or MIA, the SQL module
processor applies that ANSI/ISO SQL standard for updatable views
to all views created during compilation. Views that do not comply
with the specified ANSI/ISO SQL standard for updatable views
cannot be updated.
The specified 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 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.
4 – Example
Example 1: Declaring a module specifying character strings of
different character sets
Assuming that the character sets for the database match the
character sets specified in the program, the following example
shows a simple SQL precompiled C program that retrieves one row
from the COLOURS table.
/* This SQL precompiled program does some simple tests of character length
* and character sets.
*/
#include stdio
#include descrip
main()
{
/* Specify CHARACTER LENGTH CHARACTERS in the DECLARE MODULE statement.
* In addition, specify the NAMES, NATIONAL, and DEFAULT character sets.
*/
EXEC SQL DECLARE MODULE CCC_COLOURS
NAMES ARE DEC_KANJI
NATIONAL CHARACTER SET KANJI
SCHEMA RDB$SCHEMA
AUTHORIZATION SQL_SAMPLE
CHARACTER LENGTH CHARACTERS
DEFAULT CHARACTER SET DEC_KANJI
ALIAS RDB$DBHANDLE;
/* If you do not specify character sets in the DECLARE ALIAS statement, SQL
* uses the character sets of the compile-time database.
*/
EXEC SQL DECLARE ALIAS FILENAME MIA_CHAR_SET;
int SQLCODE;
/* Because the default character set is DEC_KANJI, you do not need to qualify
* the variable dec_kanji_p with the character set, but you must declare
* char in lowercase.
*/
char dec_kanji_p[31];
/* When you declare a parameter with lowercase char, SQL considers the
* character set unspecified and allocates single-octet characters.
*/
char english_p[31];
/* When you specify the character set, SQL allocates single- or multi-octet
* characters, depending upon the character set.
*/
char CHARACTER SET DEC_MCS french_p[31];
char CHARACTER SET KANJI japanese_p[31];
.
.
.
/* Select one row from the COLOURS table. */
EXEC SQL SELECT ENGLISH, FRENCH, JAPANESE, ROMAJI,
KATAKANA, HINDI, GREEK, ARABIC, RUSSIAN
INTO :english_p, :french_p, :japanese_p, :dec_kanji_p,
:katakana_p, :devanagari_p, :isolatingreek_p,
:isolatinarabic_p, :isolatincyrillic_p
FROM COLOURS LIMIT TO 1 ROW;
if (SQLCODE != 0)
SQL$SIGNAL();
printf ("\nENGLISH: %s", english_p);
printf ("\nFRENCH: %s", french_p);
printf ("\nJAPANESE: %s", japanese_p);
printf ("\nROMAJI: %s", dec_kanji_p);
printf ("\nKATAKANA: %s", katakana_p);
printf ("\nHINDI: %s", devanagari_p);
printf ("\nGREEK: %s", isolatingreek_p);
printf ("\nARABIC: %s", isolatinarabic_p);
printf ("\nRUSSIAN: %s", isolatincyrillic_p);
EXEC SQL ROLLBACK;
}