Add/Modify SQL Predicate Notebook
The Add/Modify SQL Predicate notebook allows you to add or modify a user-defined method
that is created using an SQL Predicate. The method is added to the generated <class>Manager.
Methods defined in this notebook return a collection of rows from the schema. A live
connection to the database is required so you can validate the SQL predicate.
The notebook has the following pages. Select a link to see details for the
corresponding page:
Select the appropriate tab to move around the notebook. The following buttons appear at
the bottom:
- OK - Enabled when changes have been made to the notebook. Clicking it
applies the changes and exits the notebook.
- Cancel - Cancels any changes made to the notebook and exits.
- Apply - Enabled when changes have been made to the notebook. Clicking
it applies the changes.
- Help - Accesses online help for the notebook.
SQL Predicate Page
The SQL Predicate page lets you enter a name and an SQL predicate to define the
method. It contains the following elements:
- Method Name field - Provide a method name for use in
the generated code. This field does not accept double-byte characters, because the
generated code cannot have methods with double-byte character names. If you want to
associate a double-byte character name with this method, use the Display Name
field.
- The SQL definition for the mapping is displayed for your information. The SQL predicate
that you enter will be added to this SQL statement to return a result set when the method
is called.
- SQL Predicate field - Enter the SQL predicate for the method. Enter a
question mark ('?') to indicate parameters that will be specified at run time. For
example, suppose the following SQL statement was used to create the mapping:
SELECT EMPLOYEE.SALARY, EMPLOYEE.NAME FROM %QUAL%EMPLOYEE
If you want to define a method that will return those employees who more than a value
specified at run time, and order them alphabetically by name, then enter the following SQL
Predicate:
WHERE EMPLOYEE.SALARY>? ORDER BY EMPLOYEE.NAME
- Validate button - Click this button to validate that the SQL predicate
in the SQL Predicate field is valid SQL for your mapping and the tables it uses. If the
predicate is valid, then the following page is updated with the type and parameter
information for the method.
Warning! If you are using DB2, the
following lines must be in the DB2CLI.INI file in the SQLLIB directory, or else the
validate action will return without an error message regardless of the SQL statement's
actual validity:
[common]
DEFERREDPREPARE=0
- Number of Cursors - (ESQL only.) Enter a number to indicate the maximum
number of cursors allowed open concurrently on the result set returned by this method. Use
a low number to improve generated code performance. Use a high number if you expect to use
several cursors at the same time.
- Display Name field - Specify a name for the method that is to be used
in application builders such as the Visual Builder. This can contain double-byte
characters. Note that the name of the method as it appears in the generated code is set in
the Method Name field, which cannot contain double-byte characters.
Parameter Page
The Parameter page lets you map method parameters (indicated by question marks in the SQL
predicate) to C++ types. Each parameter is displayed in the Parameters container, and is
modified by changing the fields below. Work with one parameter at a time by selecting one,
making the desired changes, and clicking the Modify button when you are
done; then, select the next parameter.
Use the following fields to modify the parameter mapping:
- Name - Enter a name for the parameter.
- SQL Type - If the SQL type of the parameter is unknown (the Validate
action on the previous page was unable to determine the database type), select
the appropriate SQL type for the parameter, according to the type of column from which it
is mapped. Otherwise, the SQL type is displayed, and cannot be changed.
- Type - Select a C++ type for the parameter from the drop down list of
eligible types for the given SQL type.
Click the Modify button when you are done with a parameter.
Comment Page
The Comment page lets you enter a comment for the user-defined method. The comment will be
added to the generated code along with the method's C++ code.

User-Defined Methods

Data Access Class Library and Generated Code
Sample: Create and Use a User-Defined Method With
SQL Predicate