Creates an external routine definition as a schema object in an
Oracle Rdb database. External routine refers to both external
functions and external procedures. A routine definition stores
information in the database about a subprogram (a function or
procedure) written in a 3GL language. The routine definition and
the routine image are independent of each other, meaning one can
exist without the other. However, to invoke an external routine,
you need both the routine definition and routine image.
SQL can invoke an external function from anywhere you can specify
a value expression. External procedures are invoked using the
CALL Compound_Statement.
1 – Environment
You can use the CREATE FUNCTION and CREATE PROCEDURE statements:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mCREATE[m[1m qwq> [1;4mFUNCTION[m[1m qqqwq> <external-routine-name> qqqqqqqqqk [m
[1m mq> [1;4mPROCEDURE[m[1m qqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqk [m
[1m mq> [1;4mSTORED[m[1m [1;4mNAME[m[1m IS <identifier> qj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mq> ( qwqqqqqqqqqqqqqqqqqqqqqqqwq> ) qqqqqqqqqqqqqqqqqqqqqqk [m
[1m mwq> parameter-list qqwqj x [m
[1m mqqqqqqqq , <qqqqqqqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqwqqqqqqqqqqqqqqqqqqqwqq> qqwqqqqqqqqqqqqqqqqqqwq> ; qq> qk [m
[1m mq> returns-clause qj mq> [1;4mLANGUAGE[m[1m [1;4mSQL[m[1m qqj x [m
[1m x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqqqq> external-body-clause qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> [m
(B)0[m[1mparameter-list = [m
[1m [m
[1mqwqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqwqwq> data-type qqqqqwqqqqqqqqqqk [m
[1m tq> [1;4mIN[m[1m qqqqu m> <parameter-name> j mq> <domain-name> qj [m [1mx [m
[1m tq> [1;4mOUT[m[1m qqqu [m [1mx[m
[1m mq> [1;4mINOUT[m[1m qj [m [1mx[m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1mmqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqk [m
[1m mq> [1;4mDEFAULT[m[1m value-expr qqqqqj[m [1mmq> mechanism-clause qj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>[m
[1m mq> [1;4mCOMMENT[m[1m [1;4mIS[m[1m qwq> 'string' qqqwqj [m
[1m mqqqqqq / <qqqqqj [m
(B)0[m[1mmechanism-clause = [m
[1m [m
[1mqqqq> [1;4mBY[m[1m qqwq> [1;4mDESCRIPTOR[m[1m qwqqq> [m
[1m tq> [1;4mLENGTH[m[1m qqqqqu [m
[1m tq> [1;4mREFERENCE[m[1m qqu [m
[1m mq> [1;4mVALUE[m[1m qqqqqqj [m
[1m [m
(B)0[m[1mexternal-body-clause = [m
[1m [m
[1mqq> [1;4mEXTERNAL[m[1m qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqk [m
[1m mq> [1;4mNAME[m[1m <external-body-name> qj [m [1m x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq[m [1m<qqqqqqqqqqqqqqqqqqqqqqj [m
[1mmwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [1;4mLANGUAGE[m[1m language-name[m [1mqqk [m
[1m mq> external-location-clause qj [m [1m x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmwq> [1;4mPARAMETER[m[1m [1;4mSTYLE[m[1m [1;4mGENERAL[m[1m qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk [m
[1m mq> [1;4mGENERAL[m[1m [1;4mPARAMETER[m[1m [1;4mSTYLE[m[1m qj [m [1m x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> [m
[1mmq> external-body-clause-2 qqj[m
(B)0[m[1mexternal-body-clause-2 =[m
[1mqqqwqqwq> [1;4mCOMMENT[m[1m [1;4mIS[m[1m qwq> '<string> ' qwqqqqwqqwqq>[m
[1m x x mqqqqqq / <qqqqqqj x x [m
[1m x tq> bind-site-clause qqqqqqqqqqqqqqqqqu x [m
[1m x tq> bind-scope-clause qqqqqqqqqqqqqqqqu x [m
[1m x tq> notify-clause qqqqqqqqqqqqqqqqqqqqu x [m
[1mx[m [1mtq>[m [1;4mCALLED[m [1;4mON[m [1;4mNULL[m [1;4mINPUT[m[1m qqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mtq>[m [1;4mRETURNS[m [1;4mNULL[m [1;4mON[m [1;4mNULL[m [1;4mINPUT[m[1m qqqqqqqu[m [1mx[m
[1mx[m [1mtq>[m [1;4mUSAGE[m[1m IS qwq>[m [1;4mLOCAL[m [1mqqqwqqqqqqqqqqu[m [1mx[m
[1mx[m [1mx [m [1mmq>[m [1;4mGLOBAL[m[1m qqj[m [1mx[m [1mx[m
[1m x mqwqqqqqqqqwqqwqqqqqqqqqqqqqqqqqqqwqqqj x [m
[1m x mq> [1;4mNOT[m[1m qj tq> [1;4mVARIANT[m[1m qqqqqqqqu x [m
[1m x mq> [1;4mDETERMINISTIC[m[1m qqj x [m
[1m mqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1mexternal-location-clause = [m
[1m [m
[1mqqqwqq> [1;4mDEFAULT[m[1m [1;4mLOCATION[m[1m qqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqk[m
[1m mqq> [1;4mLOCATION[m[1m '<image-location>' qj x[m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1m mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqwqq>[m
[1m mqq> [1;4mWITH[m[1m qqwq> [1;4mALL[m[1m qqqqqwqq> [1;4mLOGICAL_NAME[m[1m [1;4mTRANSLATION[m[1m qqj [m
[1m mq> [1;4mSYSTEM[m[1m qqj [m
(B)0[m[1mlanguage-name = [m
[1m [m
[1mqqwq> [1;4mADA[m[1m qqqqqwqq> [m
[1m tq> [1;4mC[m[1m qqqqqqqu [m
[1m tq> [1;4mCOBOL[m[1m qqqu [m
[1m tq> [1;4mFORTRAN[m[1m qu [m
[1m tq> [1;4mPASCAL[m[1m qqu [m
[1m mq> [1;4mGENERAL[m[1m qj [m
[1m [m
(B)0[m[1mbind-site-clause = [m
[1m [m
[1mqq> [1;4mBIND[m[1m [1;4mON[m[1m qqwqq> [1;4mCLIENT[m[1m qqwqq> [1;4mSITE[m[1m qqq> [m
[1m mqq> [1;4mSERVER[m[1m qqj [m
(B)0[m[1mbind-scope-clause = [m
[1m [m
[1mqqq> [1;4mBIND[m[1m [1;4mSCOPE[m[1m qqwq> [1;4mCONNECT[m[1m qqqqqqwqq> [m
[1m mq> [1;4mTRANSACTION[m[1m qqj [m
[1m [m
(B)0[m[1mnotify-clause = [m
[1m [m
[1mq> [1;4mNOTIFY[m[1m notify-entry-name qq> [1;4mON[m[1m qwqwq> [1;4mBIND[m[1m qqqqqqqqqwqwq> [m
[1m x tq> [1;4mCONNECT[m[1m qqqqqqu x [m
[1m x mq> [1;4mTRANSACTION[m[1m qqj x [m
[1m mqqqqqqqqq , <qqqqqqqqj [m
3 – Arguments
3.1 – bind-site-clause
Syntax options:
BIND ON CLIENT SITE | BIND ON SERVER SITE
Selects the execution model and environment for external routine
execution.
CLIENT site binding causes the external routine to be activated
and executed in the OpenVMS database client (application)
process. This is the default binding. This binding offers
the most efficient execution characteristics, allows sharing
resources such as I/O devices, and allows debugging of external
routines as if they were part of the client application. However,
this binding may suffer from address space limitations. Because
it shares virtual memory with the database buffers, this binding
is restricted to the client process system user environment, and
prohibits external routine execution in cases of an application
running with elevated privileges.
SERVER site binding causes the external routine to be activated
in a separate process from the database client and server. The
process is started on the same node at the database process.
This binding offers reasonable execution characteristics, a
larger address space, a true session user environment, and has
no restrictions regarding client process elevated privileges.
However, this binding does not permit sharing resources such
as I/O devices with the client (in particular, there is no
connection to the client interactive terminal), and debugging
of routines is generally not possible.
3.2 – bind-scope-clause
Syntax options:
BIND SCOPE CONNECT | BIND SCOPE TRANSACTION
Defines the scope during which an external routine is activated
and at what point the external routine is deactivated. The
default scope is CONNECT.
o CONNECT
An active routine is deactivated when you detach from the
database (or exit without detaching).
o TRANSACTION
An active routine is deactivated when a transaction is
terminated (COMMIT or ROLLBACK). In the event that a
transaction never occurs, the scope reverts to CONNECT.
3.3 – COMMENT_IS
A description about the nature of the parameter or external
routine. SQL displays the text of the comment when you execute
a SHOW FUNCTION or SHOW PROCEDURE statement. Enclose the comment
in single quotation marks (') and separate multiple lines in a
comment with a slash (/).
3.4 – DEFAULT value-expr
Specifies the default value of a parameter for a function or
procedure defined with mode IN. If you omit this parameter or if
the Call statement argument list or function invocation specifies
the DEFAULT keyword, then the value-expr specified with this
clause is used. The parameter uses NULL as the default if you do
not specify a value expression explicitly.
3.5 – DETERMINISTIC
Syntax options:
DETERMINISTIC | NOT DETERMINISTIC
The clause controls the evaluation of an external function in the
scope of a query:
o NOT DETERMINISTIC
Specifying the NOT DETERMINISTIC clause forces evaluation
of corresponding functions (in scope of a single query)
every time the function appears. If a function can return a
different result each time it is invoked, you should use the
DETERMINISTIC clause.
o DETERMINISTIC
Specifying the DETERMINISTIC clause can result in a single
evaluation of corresponding function expressions (in scope
of a single query), and the resulting value is used in all
occurrences of the corresponding function expression. When you
use the DETERMINISTIC clause, Oracle Rdb evaluates whether or
not to invoke the function each time it is used.
For example:
SELECT * FROM T1 WHERE F1() > 0 AND F1() < 20;
If you define the F1 function as DETERMINISTIC, the function
F1() may be evaluated just once depending on the optimizer. If
you define the F1 function as NOT DETERMINISTIC, the function
F1() is evaluated twice.
DETERMINISTIC is the default.
The DETERMINISTIC or NOT DETERMINISTIC clause is not allowed on
procedure definitions.
3.6 – external-body-clause
Identifies key characteristics of the routine: its name, where
the executable image of the routine is located, the language in
which the routine is coded, and so forth.
3.7 – external-body-name
The name of the external routine. If you do not specify a name,
SQL uses the name you specify in the external-routine-name
clause.
This name defines the routine entry address that is called for
each invocation of the routine body. The named routine must exist
in the external routine image selected by the location clause.
Unquoted names are converted to uppercase characters.
3.8 – external-location-clause
Syntax options:
DEFAULT LOCATION
LOCATION 'image-location'
A file specification referencing the image that contains the
routine body and optional notify entry points.
3.9 – external-routine-name
The name of the external routine. The name must be unique among
external and stored routines in the schema and can be qualified
with an alias or, in a multischema database, a schema name.
3.10 – FUNCTION
Creates an external function definition.
A function optionally accepts a list of IN parameters, always
returns a value, and is referenced by name as an element of a
value expression.
3.11 – GENERAL_PARAMETER_STYLE
This is synonymous with PARAMETER STYLE GENERAL and is
deprecated.
3.12 – language-name
The name of the host language in which the external routine
was coded. You can specify ADA, C, COBOL, FORTRAN, PASCAL, or
GENERAL. The GENERAL keyword allows you to call routines written
in any language.
3.13 – LANGUAGE_SQL
Names the language that calls the routine.
3.14 – mechanism-clause
Defines the passing mechanism. The following list describes the
passing mechanisms.
o BY DESCRIPTOR
Allows passing character data with any parameter access mode
to routines compiled by language compilers that implement the
OpenVMS calling standard.
o BY LENGTH
The LENGTH passing mechanism is the same as the DESCRIPTOR
passing mechanism.
o BY REFERENCE
Allows passing data with any parameter access mode as a
reference to the actual data.
This is the default passing mechanism for parameters. This
is also the default passing mechanism for a function value
returning character data.
o BY VALUE
Allows passing data with the IN parameter access mode to a
routine as a value and allows functions to return a value.
This is the default passing mechanism for a function value
returning noncharacter data.
3.15 – notify-clause
Specifies the name of a second external routine called (notified)
when certain external routine or database-related events occur.
This name defines the routine entry address that is called, for
each invocation of the notify routine. The named routine must
exist in the external routine image selected by the location
clause.
The events of interest to the notify routine are ON BIND, ON
CONNECT, and ON TRANSACTION. Multiple events can be specified.
The following describes the events and scope of each event:
BIND Routine activation to routine deactivation
CONNECT Database attach to database disconnect
TRANSACTION Start transaction to commit or roll back
transaction
3.16 – parameter-list
The optional parameters of the external routine. For each
parameter you can specify a parameter access mode (IN, OUT, and
INOUT), a parameter name, a data type, and a passing mechanism
(by DESCRIPTOR, LENGTH, REFERENCE, or VALUE).
The parameter access mode (IN, OUT, and INOUT) is optional and
specifies how the parameter is accessed (whether it is read,
written, or both). IN signifies read only, OUT signifies write
only, and INOUT signifies read and write. The parameter access
mode defaults to IN.
Only the IN parameter access mode may be specified with
parameters to an external function. Any of the parameter access
modes (IN, OUT, and INOUT) may be specified with parameters to an
external procedure.
The optional parameter name is prefixed with a colon (:).
The parameter name must be unique within the external routine
parameters.
The data type is required and describes the type of parameter
using either an SQL data type or a domain name.
You cannot declare a parameter as the LIST OF BYTE VARYING data
type.
3.17 – PARAMETER_STYLE_GENERAL
Passes arguments and returns values in a manner similar to the
OpenVMS convention for passing arguments and returning function
values.
3.18 – PROCEDURE
Creates an external procedure definition.
A procedure optionally accepts a list of IN, OUT, or INOUT
parameters, and is referenced by name in a CALL statement.
3.19 – RETURNS
Describes a function (returned) value. You can specify a data
type and a passing mechanism (BY DESCRIPTOR, LENGTH, REFERENCE,
or VALUE). The function value is, by definition, an OUT access
mode value.
The data type is required and describes the type of parameter
using either an SQL data type or a domain name.
You cannot declare a function value as the LIST OF BYTE VARYING
data type.
3.20 – STORED_NAME_IS
The name that Oracle Rdb uses to access the routine when defined
in a multischema database. The stored name allows you to access
multischema definitions using interfaces that do not recognize
multiple schemas in one database. You cannot specify a stored
name for a routine in a database that does not allow multiple
schemas. For more information about stored names, see Stored
Names.
3.21 – USAGE_IS
Specifies how the function or procedure can be called:
o USAGE IS GLOBAL indicates that the function or procedure can
be called outside the current module. This is the default.
o USAGE IS LOCAL specifies that the routine is restricted to
references within the module. This clause is provided for
compatibility with CREATE MODULE but is not allowed for CREATE
FUNCTION or CREATE PROCEDURE.
3.22 – VARIANT
Syntax options:
VARIANT | NOT VARIANT
These clauses are synonyms for the DETERMINISTIC and NOT
DETERMINISTIC clauses. The DETERMINISTIC clause indicates that
the same inputs to the function will generate the same output.
It is the same as the NOT VARIANT clause. The NOT DETERMINISTIC
clause indicates that the output of the function does not depend
on the inputs. It is the same as the VARIANT clause. This clause
is deprecated. Use DETERMINISTIC instead.
4 – Examples
Example 1: System provided integer absolute value routine
SQL> CREATE FUNCTION IABS (IN INTEGER BY REFERENCE)
cont> RETURNS INTEGER BY VALUE;
cont> EXTERNAL NAME MTH$JIABS
cont> LOCATION 'SYS$SHARE:DPML$SHR.EXE'
cont> LANGUAGE GENERAL
cont> PARAMETER STYLE GENERAL
cont> NOT DETERMINISTIC;
SQL> --
SQL> SELECT IABS(-33) FROM JOBS LIMIT TO 1 ROW;
33
1 row selected
Example 2: Using the NOT DETERMINISTIC clause, instead of the
DETERMINISTIC clause
The first CREATE FUNCTION statement in the following example
creates a function with the DETERMINISTIC clause. The
DETERMINISTIC clause indicates to Oracle Rdb that the function
would return the same result no matter how many times it is
called. Because the argument is a string literal (and could never
change), Oracle Rdb optimizes the entire function call so that it
is not called in subsequent select statements.
SQL> -- Create a function with a DETERMINISTIC clause.
SQL> CREATE function DO_COM (IN VARCHAR(255) BY DESCRIPTOR)
cont> RETURNS INTEGER;
cont> EXTERNAL NAME LIB$SPAWN
cont> LOCATION 'SYS$SHARE:LIBRTL.EXE'
cont> LANGUAGE GENERAL
cont> PARAMETER STYLE GENERAL
cont> DETERMINISTIC;
SQL> --
SQL> -- Use a SELECT statement to pass a string literal to the function.
SQL> --
SQL> -- Because Oracle Rdb optimizes functions with the DETERMINISTIC
SQL> -- clause, and the function is passed a string literal,
SQL> -- Oracle Rdb does not call the function from subsequent
SQL> -- statements.
SQL> --
SQL> SELECT DO_COM('WRITE SYS$OUTPUT "HELLO"'), employee_id FROM employees
cont> LIMIT TO 5 ROWS;
HELLO
DO_COM EMPLOYEE_ID
1 00164
1 00165
1 00166
1 00167
1 00168
5 rows selected
SQL> --
SQL> -- Use the NOT DETERMINISTIC clause to create the function:
SQL> --
SQL> CREATE function DO_COM (IN VARCHAR(255) BY DESCRIPTOR)
cont> RETURNS INTEGER;
cont> EXTERNAL NAME lib$SPAWN
cont> LOCATION 'SYS$SHARE:LIBRTL.EXE'
cont> LANGUAGE GENERAL
cont> PARAMETER STYLE GENERAL
cont> NOT DETERMINISTIC;
SQL> SELECT DO_COM('WRITE SYS$OUTPUT "HELLO"'), EMPLOYEE_ID FROM EMPLOYEES
cont> LIMIT TO 5 ROWS;
HELLO
HELLO
DO_COM EMPLOYEE_ID
1 00164
HELLO
1 00165
HELLO
1 00166
HELLO
1 00167
1 00168
5 rows selected
Example 3: External function and external procedure definition
The following example demonstrates:
o An external function and an external procedure
o Both CLIENT SITE and SERVER SITE binding
o BIND SCOPE
o A NOTIFY routine and events
o SQL callback using embedded SQL and SQL module language
o SQL$PRE options required when using callback
o Linker options required when using callback
In this example, a new column is added to the EMPLOYEES table in
the MF_PERSONNEL database. External routines are used to set this
column to spaces and to the SOUNDEX value corresponding to the
various employee names. Transaction control at the application
level (in this instance, in SQL) in conjunction with a notify
routine demonstrates how the actions of the external routines can
be affected by actions of the application.
The space-filling is performed by an external function, CLEAR_
SOUNDEX, (written in C) containing embedded SQL, which opens
another instance of the MF_PERSONNEL database and leaves it open
until deactivated.
The SOUNDEX name-setting is performed by an external procedure
(written in FORTRAN), assisted by a notify routine (written in
FORTRAN) which performs the database connection and transaction
control. All the database operations are performed by SQL module
language routines. The procedure also opens another instance
of the MF_PERSONNEL database, which is disconnected by the
notify routine when the routine is deactivated at the end of the
transaction. Display statements executed by the notify routine
serve to demonstrate the progress of the database operations.
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> -- Add the new column SOUNDEX_NAME to the EMPLOYEES table.
SQL> --
SQL> ALTER TABLE EMPLOYEES ADD SOUNDEX_NAME CHAR(4);
SQL> --
SQL> -- Define the CLEAR_SOUNDEX function.
SQL> --
SQL> CREATE FUNCTION CLEAR_SOUNDEX ()
cont> RETURNS INTEGER BY VALUE;
cont> EXTERNAL NAME CLEAR_SOUNDEX
cont> LOCATION 'CLEAR_SOUNDEX.EXE'
cont> LANGUAGE C PARAMETER STYLE GENERAL NOT DETERMINISTIC
cont> BIND ON SERVER SITE BIND SCOPE CONNECT;
SQL> --
SQL> -- Define the ADD_SOUNDEX_NAME procedure.
SQL> --
SQL> CREATE PROCEDURE ADD_SOUNDEX_NAME
cont> (INOUT INTEGER BY REFERENCE);
cont> EXTERNAL NAME ADD_SOUNDEX_NAME
cont> LOCATION 'ADD_SOUNDEX.EXE'
cont> LANGUAGE FORTRAN PARAMETER STYLE GENERAL
cont> BIND ON CLIENT SITE BIND SCOPE TRANSACTION
cont> NOTIFY ADD_SOUNDEX_NOTIFY ON BIND, TRANSACTION;
SQL> --
SQL> COMMIT;
SQL> DISCONNECT ALL;
SQL> EXIT;
Example 4: The CLEAR_SOUNDEX.SC program written in C
/* Set the soundex_name column to spaces, return any error as function value */
static int state = 0;
extern int clear_soundex () {
exec sql include sqlca ;
exec sql declare alias filename MF_PERSONNEL;
if (state == 0) {
exec sql attach 'filename MF_PERSONNEL';
state = 1;
}
exec sql set transaction read write;
if (SQLCA.SQLCODE < 0)
return SQLCA.SQLCODE;
exec sql update employees set soundex_name = ' ';
if (SQLCA.SQLCODE < 0)
return SQLCA.SQLCODE;
exec sql commit;
if (SQLCA.SQLCODE < 0)
return SQLCA.SQLCODE;
return 0;
}
Example 5: Compiling, creating a linker options file, and linking
the CLEAR_SOUNDEX program
$ SQL$PRE/CC/NOLIST/SQLOPT=ROLLBACK_ON_EXIT CLEAR_SOUNDEX.SC
$ CREATE CLEAR_SOUNDEX.OPT
SYMBOL_VECTOR = (CLEAR_SOUNDEX=PROCEDURE)
PSECT_ATTR=RDB$MESSAGE_VECTOR,NOSHR
PSECT_ATTR=RDB$DBHANDLE,NOSHR
PSECT_ATTR=RDB$TRANSACTION_HANDLE,NOSHR
$ LINK/SHARE=CLEAR_SOUNDEX.EXE -
CLEAR_SOUNDEX.OBJ, SQL$USER:/LIBRARY, -
CLEAR_SOUNDEX.OPT/OPT
SYMBOL_VECTOR = (CLEAR_SOUNDEX=PROCEDURE)
PSECT_ATTR=RDB$MESSAGE_VECTOR,NOSHR
PSECT_ATTR=RDB$DBHANDLE,NOSHR
PSECT_ATTR=RDB$TRANSACTION_HANDLE,NOSHR
Example 6: The ADD_SOUNDEX.FOR program written in FORTRAN
C Set the soundex values, returning any error in the IN/OUT parameter
SUBROUTINE ADD_SOUNDEX_NAME (ERROR)
CHARACTER ID*5,LAST*14,SX_NAME*4
INTEGER ERROR
ERROR = 0
ID = '00000'
10 CALL GET_NAME (ID, LAST, ERROR)
IF (ERROR .NE. 0) GO TO 80
CALL MAKE_SOUNDEX_NAME (LAST, SX_NAME)
CALL SET_SOUNDEX_NAME (ID, SX_NAME, ERROR)
IF (ERROR .EQ. 0) GO TO 10
80 IF (ERROR .EQ. 100) ERROR = 0
90 RETURN
END
C Perform database connection and transaction operations for notify events
SUBROUTINE ADD_SOUNDEX_NOTIFY (FUNC, RSV1, RSV2, RSV3)
INTEGER FUNC, RSV1, RSV2, RSV3, SQLCODE
SQLCODE = 0
GO TO (10,20,5,5,30,40,50),FUNC
5 TYPE *,'*** ADD_SOUNDEX_NOTIFY bad func ***'
GO TO 90
10 TYPE *,'*** ADD_SOUNDEX_NOTIFY activate ***'
CALL ATTACH_DB (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
20 TYPE *,'*** ADD_SOUNDEX_NOTIFY deactivate ***'
CALL DETACH_DB (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
30 TYPE *,'*** ADD_SOUNDEX_NOTIFY start tran ***'
CALL START_TRAN (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
40 TYPE *,'*** ADD_SOUNDEX_NOTIFY commit tran ***'
CALL COMMIT_TRAN (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
50 TYPE *,'*** ADD_SOUNDEX_NOTIFY rollback tran ***'
CALL ROLLBACK_TRAN (SQLCODE)
IF (SQLCODE .NE. 0) GO TO 80
GO TO 90
80 CALL SQL_SIGNAL ()
90 RETURN
END
C A 'substitute' SOUNDEX routine for demonstration purposes only
SUBROUTINE MAKE_SOUNDEX_NAME (NAME, SOUNDEX_NAME)
CHARACTER NAME*(*),SOUNDEX_NAME*4
SOUNDEX_NAME(1:1)=NAME(1:1)
IV = ICHAR(NAME(1:1))+22
SOUNDEX_NAME(2:2)=CHAR(MOD(IV,10)+48)
SOUNDEX_NAME(3:3)=CHAR(MOD(IV/10,10)+48)
SOUNDEX_NAME(4:4)=CHAR(IV/100+48)
RETURN
END
Example 7: The ADD_SOUNDEXM.SQLMOD module
-- Support for set soundex routine
MODULE ADD_SOUNDEX
LANGUAGE FORTRAN
PARAMETER COLONS
PROCEDURE ATTACH_DB (SQLCODE);
ATTACH 'FILENAME MF_PERSONNEL';
PROCEDURE DETACH_DB (SQLCODE);
DISCONNECT DEFAULT;
PROCEDURE START_TRAN (SQLCODE);
SET TRANSACTION READ WRITE;
PROCEDURE COMMIT_TRAN (SQLCODE);
COMMIT;
PROCEDURE ROLLBACK_TRAN (SQLCODE);
ROLLBACK;
PROCEDURE GET_NAME (:ID CHAR(5), :LASTNAME CHAR(14), SQLCODE);
SELECT EMPLOYEE_ID, LAST_NAME INTO :ID, :LASTNAME
FROM EMPLOYEES WHERE EMPLOYEE_ID > :ID LIMIT TO 1 ROW;
PROCEDURE SET_SOUNDEX_NAME (:ID CHAR(5), :SX_NAME CHAR(4), SQLCODE);
UPDATE EMPLOYEES SET SOUNDEX_NAME = :SX_NAME WHERE EMPLOYEE_ID = :ID;
Example 8: Compiling, creating the linker options file, and
linking the FORTRAN and SQL module language programs
$ FORTRAN/NOLIST ADD_SOUNDEX.FOR
$ SQL$MOD ADD_SOUNDEXM.SQLMOD
$ CREATE ADD_SOUNDEX.OPT
SYMBOL_VECTOR = (ADD_SOUNDEX_NAME=PROCEDURE)
SYMBOL_VECTOR = (ADD_SOUNDEX_NOTIFY=PROCEDURE)
PSECT_ATTR=RDB$MESSAGE_VECTOR,NOSHR
PSECT_ATTR=RDB$DBHANDLE,NOSHR
PSECT_ATTR=RDB$TRANSACTION_HANDLE,NOSHR
$ LINK/SHARE=ADD_SOUNDEX.EXE -
ADD_SOUNDEX.OBJ, ADD_SOUNDEXM.OBJ, SQL$USER:/LIBRARY, -
ADD_SOUNDEX.OPT/OPT
SYMBOL_VECTOR = ADD_SOUNDEX_NAME
SYMBOL_VECTOR = ADD_SOUNDEX_NOTIFY
PSECT_ATTR=RDB$MESSAGE_VECTOR,NOSHR
PSECT_ATTR=RDB$DBHANDLE,NOSHR
PSECT_ATTR=RDB$TRANSACTION_HANDLE,NOSHR
Example 9: Using the routines with interactive SQL
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> DECLARE :ERROR INTEGER;
SQL> --
SQL> SELECT EMPLOYEE_ID,SOUNDEX_NAME FROM EMPLOYEES
cont> LIMIT TO 3 ROWS;
EMPLOYEE_ID SOUNDEX_NAME
00165 NULL
00190 NULL
00187 NULL
3 rows selected
SQL> COMMIT;
SQL> --
SQL> BEGIN
cont> SET :ERROR = CLEAR_SOUNDEX ();
cont> END;
SQL> PRINT :ERROR;
ERROR
0
SQL> --
SQL> SELECT EMPLOYEE_ID,SOUNDEX_NAME FROM EMPLOYEES
cont> LIMIT TO 3 ROWS;
EMPLOYEE_ID SOUNDEX_NAME
00165
00190
00187
3 rows selected
SQL> COMMIT;
SQL> --
SQL> SET TRANSACTION READ ONLY;
SQl> BEGIN
cont> SET :ERROR = 0;
cont> CALL ADD_SOUNDEX_NAME (:ERROR);
cont> END;
*** ADD_SOUNDEX_NOTIFY activate ***
*** ADD_SOUNDEX_NOTIFY start tran ***
SQL> PRINT :ERROR;
ERROR
0
SQL> COMMIT;
*** ADD_SOUNDEX_NOTIFY commit tran ***
*** ADD_SOUNDEX_NOTIFY deactivate ***
SQL> --
SQL> SELECT EMPLOYEE_ID,SOUNDEX_NAME FROM EMPLOYEES
cont> LIMIT TO 3 ROWS;
EMPLOYEE_ID SOUNDEX_NAME
00165 S501
00190 O101
00187 L890
3 rows selected
SQL> COMMIT;
SQL> --
SQL> BEGIN
cont> SET :ERROR = CLEAR_SOUNDEX ();
cont> END;
SQL> PRINT :ERROR;
ERROR
0
SQL> --
SQL> SET TRANSACTION READ ONLY;
SQL> BEGIN
cont> SET :ERROR = 0;
cont> CALL ADD_SOUNDEX_NAME (:ERROR);
cont> END;
*** ADD_SOUNDEX_NOTIFY activate ***
*** ADD_SOUNDEX_NOTIFY start tran ***
SQL> PRINT :ERROR;
ERROR
0
SQL> ROLLBACK;
*** ADD_SOUNDEX_NOTIFY rollback tran ***
*** ADD_SOUNDEX_NOTIFY deactivate ***
SQL> --
SQL> SELECT EMPLOYEE_ID,SOUNDEX_NAME FROM EMPLOYEES
cont> LIMIT TO 3 ROWS;
EMPLOYEE_ID SOUNDEX_NAME
00165
00190
00187
3 rows selected
SQL> COMMIT;