Add/Modify Customized SQL Statement Notebook
The Add/Modify Customized SQL Statement notebook allows you to add or modify a
user-defined method that is created using an SQL Statement. The method is added to the
generated <class>. A live connection to the database is required so you
can validate the SQL statement.
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 Statement Page
The SQL Statement page lets you enter a name and an SQL statement 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.
- Validate button - Click this button to validate that the SQL statement
in the SQL Statement field is valid SQL for your mapping and the tables it uses. If the
statement is valid, then the following pages are 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
- SQL Statement field - Enter the SQL statement for the method. The
statement can have both input and output parameters, and can execute an action on the
database table. For example, to create a method that updates the SALARY and BONUS columns
for a particular EMPLOYEE, enter the following SQL:
UPDATE EMPLOYEE SET SALARY=?, BONUS=? WHERE EMPNO=?
If you want to specify at run time which table qualifier to use, use %QUAL% in
front of the table name. The following example demonstrates the use of %QUAL%:
UPDATE %QUAL%EMPLOYEE SET SALARY=?, BONUS=?, WHERE EMPNO=?
The default value of %QUAL% is set in the mapping properties notebook. At
run time, use the setQualifier method to set the value of %QUAL%.
- Throw Exception check box - Enable this box if you want an exception to
be thrown by the generated code if calling this method has changed more than one row in
the table. Throwing the exception does not stop the method from changing multiple rows,
but it can give you the chance to roll back undesired changes. Clear the check box if you
do not want an exception to be thrown when multiple rows have been changed.
- 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.
SQL Type Page
The SQL Type page shows the SQL types for the parameters in the SQL statement. All of the
information fields are filled in automatically when the statement is validated, and cannot
be changed. The only exception to this occurs when the validation process is unable to
determine the SQL type of one or more of the parameters. Then, you must provide the
missing information, as described below.
Each parameter is displayed in the Parameters container. To view a parameter's SQL
mapping, select it; the fields below are updated with the information for the selected
parameter. If the SQL type of a parameter is known, all these fields will be static; there
will also be a check-mark to the left of the parameter name in the Parameters container.
If the SQL type of a parameter is unknown, there will be an "X" to the left of
the parameter name; make the appropriate setting changes, and click the Modify button.
The following fields are shown:
- Parameter Type - This field indicates whether the selected parameter is
an In parameter or a Result Column parameter. Question marks ('?') in
the SQL statement become In parameters. Commands such as SELECT or AVG create Result
Column parameters. This field is automatically filled in, and is for your information
only.
- SQL Name - This field shows the name of the selected parameter. The
name is generated automatically, and shown here for your information.
- SQL Type - This field shows the SQL type of the selected parameter. In
most cases, when you click the Validate button on the SQL Statement page,
the SQL type for this parameter is obtained from the database. In some situations,
however, Data Access Builder is unable to determine the correct type, and so this field
shows the type "Unknown". If this happens, select the correct
type from the drop-down box, and set the Length, Precision,
and Scale fields as well. Then click Modify, and the
parameter will be updated; the "X" beside the parameter in the Parameters
container will change to a check-mark.
Parameter Page
The Parameter page lets you map method parameters to corresponding parts of the C++
method, and to C++ types. Each parameter is displayed in the Parameters list box, 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 Mapped To radio buttons to modify the parameter mapping. There
are four radio buttons for selecting what part of the method the parameter is mapped to.
Only those options that are applicable to the parameter type are enabled. Select one of
the following:
- Parameter - Maps the SQL parameter to a method parameter (that is, one
of the parameters passed in to the method when it is called). Selecting this radio button
enables the Name and Type fields to the right. Enter a
name for the parameter, and select a C++ type. Only those C++ types that are appropriate,
given the parameter's SQL type, are provided.
- Attribute - Maps the parameter to an attribute of <class>.
The current persistent object's corresponding attribute value is used for method input or
is assigned an output value, depending on what the user-defined method does. Selecting
this radio button enables the Name and Type fields to
the right. In the Name field, select the desired attribute. Only those
attributes that are appropriate, given the parameter's SQL type, are provided.
- Return Value - Maps the parameter to a value returned by the method.
Selecting this radio button enables the Type field to the right. Only those C++ types that
are appropriate, given the parameter's SQL type, are provided.
- None - Removes the selected parameter from the method mapping.
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
Customized SQL Statement