Ingres Database Reference
Document #: US-13270,EN
------------------------------------------------------------------------------
Major subject: analysis Minor subjects: tech_notes
Keywords: eql, esql
Abstract:
Using ESQL and EQUEL Simultaneously in Relese 6 3GL Applications
- Equivalent to INGRES Release 6 Technical Note #30.
Expert note:
Using ESQL and EQUEL Simultaneously in Release 6 3GL Applications
=================================================================
Overview
--------
Occasionally a programmer may be faced with a situation where a given
QUEL query cannot be converted immediately to an equivalent SQL query
during the migration of an application from QUEL to SQL under Release 6
of INGRES. As an aid to migration, Release 6 INGRES will allow one to
call modules utilizing either SQL or QUEL in an application, as long as
the two query languages are not used together in any given compilable
module. A compilable module is a single file containing a programmer's
code for one or more functions and/or subroutines. This file is subse-
quently passed through a pre-processor for ESQL or EQUEL and then com-
piled with whatever language compiler is appropriate. It must be
stressed that this method should be employed only in migration situa-
tions where QUEL-based applications are being converted to SQL-based
ones. This method will not be supported indefinitely. There are, how-
ever, subtleties which must be addressed in order for a mixed query
language application to perform properly. The purpose of this document
is to discuss these subtleties.
o Embedded Query Statements (DML) and Program Modules
o SQL Database Procedures and QUEL Transactions
o EQUEL Error Handling Via IIseterr and ESQL Error Handling
o Methods Used to Connect to Databases and Transaction Semantics
o The Processing of SQL and QUEL Tables, Views, Grants, and Permits
o Linking an Application Containing Both ESQL and EQUEL
Embedded Query Statements (DML) and Program Modules
---------------------------------------------------
A compilable program module consisting of one or more callable functions
and/or subroutines may contain DML statements of one and only one query
language. Programmers often combine many callable routines into one com-
pilable file. If this method is used one must insure that no constructs
from QUEL are intermixed with SQL constructs within the entire file.
Coding some functions entirely in ESQL and others in EQUEL within the
same compilable unit is not allowed. Those functions of a given query
language will have to combined into a separate module and compiled
separately.
SQL Database Procedures and QUEL Transactions
---------------------------------------------
Invoking a SQL database procedure while in a QUEL-based transaction is
not allowed. In order to invoke a SQL database procedure one must either
already be within a SQL initiated MQT or end the current QUEL initiated
MQT prior to invoking the SQL database procedure.
EQUEL Error Handling Via IIseterr and ESQL Error Handling
---------------------------------------------------------
Declaring a user-written error handler to INGRES by explicitly using the
EQUEL IIseterr routine will impact ESQL error handling as well. If an
SQL statement returns an error the EQUEL error handling routine esta-
blished by IIseterr will be called. The error number provided to the
error handler will contain the proper SQL error number. Calling
IIseterr from an ESQL module is not supported as IIseterr is an EQUEL
feature. Once an application is completely converted from QUEL to SQL
the error handler declaration should be terminated in favor of the stan-
dard SQL method using the SQLCA data area.
Methods Used to Connect to Databases and Transaction Semantics
--------------------------------------------------------------
The method by which one connects an application to an INGRES database
has important ramifications concerning transaction semantics and lock-
ing.
By using the ESQL "EXEC SQL CONNECT dbname" statement, or running a
SQL-based ABF application, one inherits SQL transaction semantics and
locking behavior until the application is disconnected from the data-
base. This implies that the first DML statement ( Data Manipulation
Language statement ) following the EXEC SQL CONNECT statement, and the
first DML statement following the end of a transaction ( via the ESQL
"EXEC SQL COMMIT" or "EXEC SQL ROLLBACK" ) will start a new MQT (
Multi-Query Transaction ). While in a MQT, locks initiated within the
MQT are held and INGRES SET LOCKMODE commands are disallowed. One must
end the MQT before locks are released and new SET LOCKMODE commands can
be issued. Thus, in order to alter INGRES locking behavior SET LOCKMODE
commands must be issued prior to MQT initiation or immediately following
a COMMIT or ROLLBACK statement.
Connecting to the database through EQUEL's "## INGRES dbname" statement,
or running a QUEL-based ABF application, causes QUEL transaction seman-
tics and locking behavior to be used until the application is discon-
nected from the database. QUEL MQTs are initiated by the EQUEL "##
BEGIN TRANSACTION" statement. One ends a QUEL MQT by issuing a "## END
TRANSACTION" or "## ABORT" statement. Do not issue an ESQL "EXEC SQL
COMMIT" or "EXEC SQL ROLLBACK" to end an EQUEL MQT. While in a QUEL
MQT, like a SQL MQT, locks initiated within the MQT are held and INGRES
SET LOCKMODE commands are disallowed. One must end the MQT before locks
are released and new SET LOCKMODE commands can be issued. Under QUEL
transaction semantics those DML statements which are not within a MQT
are considered SSTs ( Single Statements Transactions ). Unlike SQL, one
may issue SET LOCKMODE commands after DML statements under QUEL transac-
tion semantics if the DML statement itself was a SST.
When mixing modules of ESQL and EQUEL in an application one must insure
that all DML statements within a MQT are of the same query language (
e.g. SQL, QUEL ). A SQL initiated transaction cannot contain QUEL-based
DML operations and must be ended via a SQL COMMIT or ROLLBACK statement.
The same analogous rules apply to QUEL based MQTs and SQL DML opera-
tions. All DML statements following a QUEL BEGIN TRANSACTION statement
must be QUEL DML statements as ESQL statements being disallowed. The
transaction must be ended with either a QUEL END TRANSACTION or ABORT.
The Processing of SQL and QUEL Tables, Views, Grants, and Permits
-----------------------------------------------------------------
Fundamental differences exist between SQL and QUEL when evaluating
queries.
The semantics of a view depend on the language that it was
CREATEd/DEFINEd in. The language that a view is used with has no effect
on the result that will be seen (to the extent that the queries are
equivalent). The ANSI semantics of SELECTing or RETRIEVEing from views
created by the SQL "CREATE VIEW" statement require that the resulting
data be exactly the same as if the views were "virtual" relations, and
the query run against these "virtual" relations. QUEL-created views fol-
low the query modification model instead. The behavioral differences
between SQL and QUEL views are shown in the following examples.
Given the following SQL view:
create view sv as select salary, floor, building
from emp e, dept d
where e.dept = d.dname
The tuple count of the following queries will be the same,
as "sv" behaves just as a relation would:
select salary from sv
select floor from sv
If, however, we defined the view in QUEL as:
range of e is emp
range of d is dept
define view qv (e.salary, d.floor, d.building)
where e.dept = d.dname
and ran the following queries in QUEL we will receive differing
tuple (row) counts:
retrieve (qv.salary)
retrieve (qv.floor)
The first query will return the same number of tuples (rows) as are in
emp, whereas the second will return the same as are in dept. The QUEL
view uses the smallest possible fragment of the view definition and
applies it to the query in question.
As an example, executing the following two sets of queries yields
identical results for each set:
retrieve (sv.salary)
select salary from sv
select salary from qv
retrieve (qv.salary)
Finally, the fact that Release 6 of INGRES allows for NULL values in
both database tables and the Forms Runtime System (FRS) indicates that
the programmer must handle possible NULL values in both EQUEL and ESQL
based code by using NULL indicator variables.
All tables created in Release 5 QUEL or SQL did not provide NULL value
support and took on default values instead. Release 5 tables created
from both SQL and QUEL disallowed duplicate rows for all table struc-
tures excluding "heap". This default behavior still holds true for
Release 6 QUEL tables and columns. In Release 6 SQL the default attri-
bute for the creation of a column in a table has changed to allow NULL
values. In addition all table structures will, by default, allow dupli-
cate rows.
Linking an Application Containing Both ESQL and EQUEL
-----------------------------------------------------
To link an executable program which contains both ESQL and EQUEL one
should follow the guidelines in your ESQL companion guide corresponding
to the 3GL used.
Releases affected: 6.0/00(all.all) - Releases not affected:
Errors:
Bugs/SIRS:
------------------------------------------------------------------------------
Ingres Database Reference
To William's Home Page
© William Yuan 2000
Email William