EGL Reference Guide for iSeries
You use the page for SQL database connections for these reasons:
- You can enable declaration-time and debug-time access to a database that
is accessed outside of J2EE.
- Also, you can set a value for the build descriptor option sqlJNDIName,
which specifies a name to which the default datasource is bound in the JNDI
registry; for example, java:comp/env/jdbc/MyDB. That option
is included in the build descriptor that is created for you in the following
situation:
- You use the EGL Web Project Wizard, as described in Creating a
project to work with EGL; and
- When working in that wizard, you request that a build descriptor be
created.
Do as follows:
- Click Window > Preferences
- When a list of preferences is displayed, expand EGL, then click
SQL Database Connections.
- In the Connection URL field, type the URL used to connect to
the database through JDBC:
- In the Database field, type the name of the database.
- In the User ID field, type the user ID for the
connection.
- In the Password field, type the password for the user
ID.
- In the Database vendor type field, select the database product
and version that you are using for your JDBC connection.
- In the JDBC driver field, select the JDBC driver that you are
using for your JDBC connection.
- In the JDBC driver class field, type the driver class for the
driver you selected. For IBM DB2 APP DRIVER for Windows, the driver
class is
COM.ibm.db2.jdbc.app.DB2Driver;
for the Oracle JDBC thin client-side driver, the driver class is
oracle.jdbc.driver.OracleDriver; and for
the Informix JDBC NET driver, the driver class is
com.informix.jdbc.IfxDriver. For other
driver classes, refer to the documentation for the driver.
- In the class location field, type the fully qualified filename
of the *.jar or *.zip file that contains the driver
class. For IBM DB2 APP DRIVER for Windows, type the fully qualified
filename to the db2java.zip file; for example,
d:\sqllib\java\db2java.zip. For the Oracle THIN
JDBC DRIVER, type the fully qualified filename to the classes12.zip
file; for example,
d:\Ora81\jdbc\lib\classes12.zip. For other
driver classes, refer to the documentation for the driver.
- In the Connection JNDI name field, specify the database used in
J2EE. The value is the name to which the datasource is bound in the
JNDI registry; for example, java:comp/env/jdbc/MyDB. As
noted earlier, this value is assigned to the option sqlJNDIName in
the build descriptor that is constructed automatically for a given EGL Web
project.
- If you are accessing DB2 UDB and specify a value in the Secondary
authentication ID field, the value is used in the SET CURRENT SQLID
statement used by EGL at validation time. The value is
case-sensitive.
You can clear or apply preference settings:
- To restore default values, click Restore Defaults.
- To apply preference settings without exiting the preferences dialog, click
Apply.
- If you are finished setting preferences, click OK.
Related tasks
Creating a project to work with EGL
Setting EGL preferences
Related reference
sqlJNDIName
At EGL declaration time, you can use the SQL retrieve feature to create an SQL
record from the columns of an SQL table. For an overview, see SQL
support.
To set preferences for the SQL retrieve feature, do as follows:
- Click Window > Preferences, then expand EGL and
click SQL Retrieve
- Specify rules for naming each structure item that is created by the SQL
retrieve feature:
- To specify the case of the structure item name, click one of the following
radio buttons:
- Do not change case (the default) means that the case of the
structure item name is the same as the case of the related table column name
- Change to lower case means that the structure item name is a
lower-case version of the table column name
- Change to lower case and capitalize first letter after underscore
also means that the structure item name is a lower-case version of the table
column name, except that a letter in the structure item name is rendered in
uppercase if, in the table column name, the letter immediately follows an
underscore
- To specify how the underscores in the table column name are reflected in
the structure item name, click one of the following radio buttons:
- Do not change underscores (the default) means that underscores in
the table column name are included in the structure item name
- Remove underscores means that underscores in the table column
name are not included in the structure item name
- Change underscores to hyphens means that underscores in the table
column name are rendered as hyphens in the structure item name
- If you intend to retrieve data from a table that is part of an Informix
system schema, clear the check box for Exclude system schemas.
(In this case, "Informix" is the table owner.) In all other cases,
select the check box to improve the performance of the SQL retrieve
feature.
The check box is selected by default.
Related concepts
SQL support
Related tasks
Retrieving SQL table data
Setting EGL preferences
Setting preferences for SQL database connections
Related reference
Informix and EGL
EGL provides a way to create SQL record items from the definition of an SQL
table, view, or join; for an overview, see SQL support.
Do as follows when you are working in the EGL or EGL Web perspective:
- Ensure that you have set SQL preferences as appropriate. For
details, see and Setting preferences for SQL retrieve.
- Decide where to do the task--
- In an EGL source file, as you develop each SQL record; or
- In the Outline view, as may be easier when you already have SQL
records.
- If you are working in the EGL source file, you can proceed in this way--
- If you do not have the SQL record, create it:
- Type R, press Ctrl-Space, and in the content-assist list, select
one of the SQL table entries (usually SQL record with table
names).
- Type the name of the SQL record; press Tab; and type a table
name, or a comma-delimited list of tables, or the alias of a view.
You also can create an SQL record by typing the minimal content, as
appropriate if the name of the record is the same as the name of the table, as
in this example:
Record myTable type sqlRecord
end
- Right-click anywhere in the record.
- In the context menu, click SQL record > Retrieve SQL.
- If you are working in the Outline view, right click on the entry for the
SQL record and, in the context menu, click Retrieve SQL.
- Note:
- You cannot retrieve an SQL view that is defined with the DB2 condition WITH
CHECK OPTIONS.
After you create structure items, you may want to gain a productivity
benefit by creating the equivalent data item parts; see Overview on
creating data item parts from an SQL record part.
Related concepts
Creating data item parts from an SQL record part (overview)
SQL support
Related tasks
Creating data item parts from an SQL record part
Setting preferences for SQL database connections
Setting preferences for SQL retrieve
Related reference
SQL item properties
After you declare structure items in an SQL record part, you can use a special
mechanism in the EGL editor to create data item parts that are equivalent to
the structure items. The benefit is that you can more easily create a
non-SQL record (usually a basic record) for transferring data to and from the
related SQL record at run time.
Consider the following structure items:
10 myHostVar01 CHAR(3);
10 myHostVar02 BIN(9,2);
You can request that data item parts be created:
DataItem myHostVar01 CHAR(3) end
DataItem myHostVar02 BIN(9,2) end
Another effect is that the structure item declarations are rewritten:
10 myHostVar01 myHostVar01;
10 myHostVar02 myHostVar02;
As shown in this example, each data item part is given the same name as the
related structure item and acts as a typedef for the structure item.
Each data item part is also available as a typedef for other structure
items.
Before you can use a structure item as the basis of a data item part, the
structure item must have a name, must have valid primitive characteristics,
and must not point to a typedef.
Related concepts
SQL support
Related tasks
Creating data item parts from an SQL record part
Related reference
DataItem part in EGL source format
SQL record part in EGL source format
After you declare structure items in an SQL record part, you can use a special
mechanism in the EGL editor to create data item parts that are equivalent to
the structure items. For general information, see Overview on
creating data item parts from an SQL record part.
By default, the Outline view is open in the EGL perspective. If you
are working in the EGL Web perspective, open the Outline view by selecting
Show View > Outline from the Window menu. Do
as follows in the Outline view:
- For a given SQL record part, hold down Ctrl while clicking on
each of the structure items of interest. To select all the structure
items in a given record, click the topmost structure item, then hold down
Shift while clicking on the bottommost structure item.
- Right-click on the selected structure items.
- In the context menu, click Create Data Item.
The data-item parts are written at the bottom of the EGL source file, and
each structure item is changed to refer to the equivalent part.
Related concepts
Creating data item parts from an SQL record part (overview)
SQL support
Related tasks
Retrieving SQL table data
Related reference
SQL record part in EGL source format
EGL provides an implicit SQL SELECT statement for a given SQL record
part. To view the implicit SQL SELECT statement, do as follows:
- Open the EGL file that contains the SQL record part. If you do not
have the file open, right-click on the EGL file in the Project Navigator, then
select Open With > EGL Editor.
- Click inside the SQL record part, then right-click. A context menu
displays.
- Select SQL Record > View Default Select.
- To validate the SQL SELECT statement against a database, click
Validate.
- Note:
- Before using the validate function, DB2 UDB users must set the
DEFERREDPREPARE option. You can set this option interactively in the
CLP (DB2 command line processor) using the db2 update cli cfg for section
COMMON using DEFERREDPREPARE 0 command. This command will put the
keyword under the COMMON section. Execute the command db2 get cli
cfg for section common to verify that the keyword is being picked
up.
Related concepts
SQL support
Related tasks
Validating the SQL SELECT statement for an SQL record
Related reference
SQL record part in EGL source format
EGL provides an implicit SQL SELECT statement for a given SQL record
part. To validate the implicit SQL SELECT statement against a database,
do as follows:
- Open the EGL file that contains the SQL record part. If you do not
have the file open, right-click on the EGL file in the Project Navigator, then
select Open With > EGL Editor.
- Click inside the SQL record part, then right-click. A context menu
displays.
- Select SQL Record > Validate Default Select.
- Note:
- Before using the validate function, DB2 UDB users must set the
DEFERREDPREPARE option. You can set this option interactively in the
CLP (DB2 command line processor) using the db2 update cli cfg for section
COMMON using DEFERREDPREPARE 0 command. This command will put the
keyword under the COMMON section. Execute the command db2 get cli
cfg for section common to verify that the keyword is being picked
up.
Related concepts
SQL support
Related tasks
Viewing the SQL SELECT statement for an SQL record
Related reference
SQL record part in EGL source format
Within a function, you can construct an SQL PREPARE statement based on an SQL
record part and an SQL statement coded in a literal. To construct an
SQL PREPARE statement, do as follows:
- Open an EGL file with the EGL editor. The file must contain a
function and a coded SQL statement. If you do not have a file open,
right-click on the EGL file in the workbench Project Navigator, then select
Open With > EGL Editor.
- Click inside the function at the location where the SQL PREPARE statement
will reside, then right-click. A context menu displays.
- Select Add SQL Prepare Statement.
- Type a name to identify the SQL PREPARE statement. For rules, see
Naming conventions.
- If you have an SQL record variable defined, select it from the drop-down
list. The corresponding SQL record part name displays. If you do
not have an SQL record variable defined, you can type a name in the SQL record
variable name field, then select an SQL record part name using the
Browse button. You must eventually define an SQL record
variable with that name in the EGL source code.
- Select an execution statement type from the drop-down list.
- If the execution statement is of type open, enter a result-set
identifier.
- Click OK. An EGL prepare statement and the
appropriate EGL open, get, or execute statement is
constructed inside the function.
Related concepts
SQL support
Related tasks
Validating the SQL SELECT statement for an SQL record
Viewing the SQL SELECT statement for an SQL record
Related reference
Naming conventions
SQL record part in EGL source format
EGL provides an implicit SQL statement for each SQL-related EGL input/output
(I/O) statement. To construct an explicit SQL statement from an
implicit one, do as follows:
- Open the EGL file that contains the EGL I/O statement. If you do
not have the file open, right-click on the EGL file in the Project Navigator,
then select Open With > EGL Editor.
- Click on the EGL I/O statement, then right-click. A context menu
displays.
- To construct an explicit SQL statement without an INTO clause, select
SQL Statement > Add. To construct an explicit SQL
statement with an INTO clause, select SQL Statement > Add with
Into. The implicit SQL statement is appended to the EGL I/O
statement making it an explicit SQL statement.
- Note:
- The INTO clause is only valid with open, get, and get
next statements.
Related concepts
SQL support
Related tasks
Removing an SQL statement from an SQL-related EGL statement
Resetting an explicit SQL statement
Validating an implicit or explicit SQL statement
Viewing the implicit SQL for an SQL-related EGL statement
EGL provides an implicit SQL statement for each SQL-related EGL input/output
(I/O) statement. To view the implicit SQL for an EGL I/O statement, do
as follows:
- Open the EGL file that contains the EGL I/O statement. If you do
not have the file open, right-click on the EGL file in the Project Navigator,
then select Open With > EGL Editor.
- Click on the EGL I/O statement, then right-click. A context menu
displays.
- Select SQL Statement > View.
Related concepts
SQL support
Related tasks
Constructing an explicit SQL statement from an implicit one
Removing an SQL statement from an SQL-related EGL statement
Resetting an explicit SQL statement
Validating an implicit or explicit SQL statement
To validate an implicit or explicit SQL statement against a database, do as
follows:
- Open the EGL file that contains the SQL-related EGL statement or explicit
SQL statement. If you do not have the file open, right-click on the EGL
file in the Project Navigator, then select Open With > EGL
Editor.
- Click the EGL statement or SQL statement, then right-click. A
context menu displays.
- Select SQL Statement > Validate.
- Note:
- Before using the validate function, DB2 UDB users must set the
DEFERREDPREPARE option. You can set this option interactively in the
CLP (DB2 command line processor) using the db2 update cli cfg for section
COMMON using DEFERREDPREPARE 0 command. This command will put the
keyword under the COMMON section. Execute the command db2 get cli
cfg for section common to verify that the keyword is being picked
up.
Related concepts
SQL support
Related tasks
Constructing an explicit SQL statement from an implicit one
Removing an SQL statement from an SQL-related EGL statement
Resetting an explicit SQL statement
Viewing the implicit SQL for an SQL-related EGL statement
EGL provides an implicit SQL statement for each SQL-related EGL input/output
(I/O) statement. An implicit SQL statement can be appended to an EGL
I/O statement making it an explicit SQL statement (see Constructing an
explicit SQL statement from an implicit one). To remove the
appended SQL statement, do as follows:
- Open the EGL file that contains the explicit SQL statement. If you
do not have the file open, right-click on the EGL file in the Project
Navigator, then select Open With > EGL Editor.
- Click on the explicit SQL statement, then right-click. A context
menu displays.
- Select SQL Statement > Remove. The EGL I/O
statement remains.
Related concepts
SQL support
Related tasks
Constructing an explicit SQL statement from an implicit one
Resetting an explicit SQL statement
Validating an implicit or explicit SQL statement
Viewing the implicit SQL for an SQL-related EGL statement
EGL provides an implicit SQL statement for each SQL-related EGL input/output
(I/O) statement. An implicit SQL statement can be appended to an EGL
I/O statement making it an explicit SQL statement. If you change the
explicit SQL statement, do as follows to return to an SQL statement based on
the implicit SQL:
- Open the EGL file that contains the explicit SQL statement. If you
do not have the file open, right-click on the EGL file in the Project
Navigator, then select Open With > EGL Editor.
- Click on the explicit SQL statement, then right-click. A context
menu displays.
- Select SQL Statement > Reset.
Related concepts
SQL support
Related tasks
Constructing an explicit SQL statement from an implicit one
Removing an SQL statement from an SQL-related EGL statement
Validating an implicit or explicit SQL statement
Viewing the implicit SQL for an SQL-related EGL statement
Consider what happens when you specify the following EGL statement:
open myRecord;
When the EGL editor tries to create a default SQL statement, the editor
attempts to find a variable named myRecord and to identify the SQL record part
on which that variable is based. If the variable is unavailable at
development time or if the variable is undeclared, the editor attempts to use
an SQL record part named myRecord as the basis for the default SQL
statement. The editor assumes that you intend to create a variable
whose name is the name of the SQL record part.
If you wish to store an SQL-related function in a file that does not
include the variable myRecord, you can do as follows:
- In the program part, declare the global variable
- Create the function as a nested function in the program part
- Create the default SQL statement and modify it as appropriate; then,
save the file
- Move the function to the other file
After the function is moved from the program part, the record name cannot
be resolved at development time, and the editor cannot display any default SQL
statements that are based on that record.
Related concepts
SQL support
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.