Embedded SQL for jBC
The name "SQL" is an abbreviation for "Structured Query Language".
The SQL language enables the defining, manipulating and controlling of data in a
relational database. A relational database is a database that appears to the user as a
collection of tables. A table is defined to be an unordered collection of rows. Finally
the SQL terminology tends to refer to records as rows and fields within a record as a
columns within a row.
Embedded SQL is a version of SQL designed for direct incorporation into hosts programs
or specifically in the case of jBASE, into jBC programs.
An embedded SQL jBC program contains normal jBC code statements plus an embedded SQL
declare section, zero or more embedded cursor definitions, zero or more embedded exception
declarations and one or more embedded SQL statements.
Embedded SQL declarations, definitions and statements are prefixed by the reserved
words EXEC SQL. This part of the embedded SQL standard also enables the jBC preprocessor
to recognize and distinguish SQL statements from the normal jBC code statements. The
embedded SQL statements are terminated by a semicolon.
Embedded SQL statements can include references to jBC variables. The jBC variables must
be prefixed with a colon to distinguish them from SQL column names. The jBC variables
cannot be qualified or subscripted and must refer to scalars, i.e. character strings or
numbers, not arrays or expressions.
All jBC variables that will be referenced in embedded SQL statements must be defined
within an embedded SQL declare section, the jBC variable definitions are limited to simple
forms. i.e. no expressions or arrays.
An embedded SQL cursor must not appear in an embedded SQL statement before it has been
defined by an embedded SQL cursor definition.
Any jBC variables that will be referenced in embedded SQL statements must have a data
type that is compatible with the SQL data type of the column with which they are to be
compared or assigned. However this requirement does not prevent jBC variables from using
the same name as embedded SQL column references.
Embedded SQL statement exceptions can be handled either by utilizing the SYSTEM(0) function or predetermined by the SQL WHENEVER statement.
The following jBC code provides an example of using embedded SQL for Oracle.
* Declare jBC variables to use within embedded SQL statements (A)
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
* Predetermine action on SQLERROR ( B )
EXEC SQL WHENEVER SQLERROR DO SQL_ERROR() ;
* Connect to database supplying user and password ( C )
User = "demo" ; Passwd = "demo99"
EXEC SQL CONNECT :User IDENTIFIED BY :Passwd;
* Create Parts table ( D )
EXEC SQL CREATE TABLE Parts
PartNo INTEGER NOT NULL PRIMARY KEY,
* Loop until no more PartNos
* Prompt for PartNo
CRT "Part Number :":
WHILE PartNo NE " DO
* Prompt for PartName
CRT "Part Name :":
* Add PartNo and PartName into Parts table ( E )
EXEC SQL INSERT INTO Parts VALUES (:PartNo, :PartName );
* Commit updates to database ( F )
EXEC SQL COMMIT ;
( A ) Declare jBC variables to use within embedded SQL statements
This section declares jBC variables so that they can be used within embedded SQL
statements. All references to jBC within the embedded SQL statement must be prefixed by a
colon. This feature of the embedded SQL standard is used by the jBC preprocessor to
identify jBC variables when parsing the embedded SQL statement. The jBC variables must be
the same data type as the source or target embedded SQL columns.
( B ) Predetermine action on SQLERROR
This section configures the action to take on detecting an error with the previous
executed embedded SQL statement. Every SQL statement should in principle be followed by a
test of the returned SQLCODE value. This can be achieved by utilizing the SYSTEM(0) function, which returns the result of the
last SQL statement, or alternatively using the embedded SQL WHENEVER statement to
predetermine the action for all subsequent embedded SQL statements. The SYSTEM(0) function will return three different
||embedded sql statement failed.
||Embedded SQL statement successful.
||NOT FOUND. No rows where found.
The format of the embedded SQL WHENEVER statement is as follows:
EXEC SQL WHENEVER Condition Action ;
||DO Function - Oracle implementation.
CALL Function - Ingress and Informix implementation.
||User defined function.
SQLERROR() - Display embedded SQL error then return to program.
SQLABORT() - Display embedded SQL error then exit program.
||Label in executing program.
( C ) Connect to database supplying user and password
This section connects the specified user and or passwd combination to the SQL database.
This command can be embedded SQL implementation dependent. The user must be correctly
configured for the target database.
( D ) Create Parts table.
This section creates an SQL table called Parts. The table has two constituent data types,
these are defined as an integer value PartNo and a character string PartName. The PartNo
is defined as a non null unique value and is defined as the primary key. This definition
provides a close match to the usual format of a record and id. The only data type that is
truly common to all hosts and their languages is fixed length character strings, the
integer value used here is for demonstration purposes and is not recommended.
( E ) Add PartNo and PartName into table Parts.
This embedded SQL statement inserts the values entered for PartNo and PartName into the
SQL table Parts. PartNo is inserted as the first column whereas PartName is inserted as
the second column of each row. Effectively PartNo is the record id and PartName is the
first field in the record PartNo. The jBC pre-processor parses the embedded SQL statements
and provides code to convert any specified jBC variables to the format required by the
embedded SQL implementation. Any returned parameters are then converted back into jBC variables.
( F ) Commit updates to database.
This embedded SQL statement makes all updates by embedded SQL statements since the last
SQL commit statement visible to other users or programs on the database. If a program
executes an embedded SQL statement and no transaction is currently active then one is
automatically started. Each subsequent SQL statement update by the same program without an
intervening commit or rollback, is considered part of the same transaction. A transaction
terminates by either an embedded SQL COMMIT, normal termination, or an embedded SQL
ROLLBACK statement, abnormal termination. An abnormal termination does not change the
database with respect to any of the embedded SQL updates executed since the last commit or
rollback. Database updates made by a given transaction do not become visible to any other
distinct transaction until and unless the given transaction completes with a normal
termination. i.e. an embedded SQL COMMIT statement.
EMBEDDED SQL COMPILER OPTION
In order to compile jBC programs containing embedded SQL statements the jBASE compiler
option "q" must be invoked with the jbc compiler command. The "q"
option also expects an SQL implementation specifier.
e.g. To compile the jBC example program PartEntry.b for an Oracle SQL implementation
jbc -Jo -Jqo PartEntry.b
In this example the SQL specifier is "o" for Oracle. Other specifiers are
added as and when embedded SQL implementations are required. e.g. The "i" option
informs the jBASE compiler to invoke mechanisms for the Ingres embedded SQL
implementation. Although the embedded SQL standard is the same, each SQL provider requires different
manipulative techniques in order to compile and connect to the database.
The jbc compiler pre-processes the jBC program parsing the normal jBC and embedded SQL
statements to produce an intermediate C program. The SQL implementation dependent
pre-processor is then invoked to convert the embedded SQL statements to the implementation
defined internal functions. The resulting program is then is then compiled and linked. The
jbc compilation should be executed in a user account which has been enabled for the
required embedded SQL implementation. Attempting to compile in an account not enabled for
the required SQL implementation may cause compilation failure as certain environment
variables for the implementation may not have been modified for the correct directory paths, etc.
When attempting to compile a program with embedded SQL and you get an error
along the lines of...
Command failed: nsqlprep PartEntry.sqc...this is an indication that either you have not loaded the Embedded SQL
Kit and do not have the 'nsqlprep' command, or the 'nsqlprep' command does exist
but it is not visible to the PATH environment variable.
SQL Pre Processor error -1