Add User-Defined Methods with Customized SQL Statements

After specifying that you want to add or modify a user-defined method with a customized SQL statement (see Add User-Defined Methods or Modify User-Defined Methods on how to do this), you perform the following steps in the Customized SQL Statement notebook:

  1. Enter the name of the method in the Method Name entry field.
  2. On the SQL Statement page, enter the SQL statement in the SQL Statement entry field. For example, to create a method to update the SALARY and BONUS columns of the EMPLOYEE table, enter:
    UPDATE EMPLOYEE SET SALARY=?, BONUS=? WHERE EMPNO=? 

    Use %QUAL% to specify that a run-time qualifier for the table is used when executing the SQL query. For example, enter this SQL statement:

    UPDATE %QUAL%EMPLOYEE SET SALARY=?, BONUS=? WHERE EMPNO=? 

    The default value of %QUAL% is the qualifier in the mapping's properties notebook. At run time, use the setQualifier method to set the value of %QUAL%.

  3. Click Validate. A database connection is required for the validation. An error window opens if the SQL statement is not valid. If the SQL statement is valid, the Parameters list box on the Parameter page lists all the parameters for the user-defined 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
  4. Your SQL Statement may affect multiple rows. Since the Data Access Builder does not know if this is expected behavior, specify whether an exception should be thrown if multiple rows are affected by the SQL statement. To include the generation of this code, select the Throw exception if multiple rows affected check box. By default, this box is checked.
  5. Enter a Display Name for the method. This name can contain double-byte characters and is used instead of the Method Name by builders such as the Visual Builder. By default, this is the same as Method Name.
  6. The SQL Type page shows the SQL Types of parameters in the SQL statement. If the Validate action was successful, then all of the information is filled in for your information. However, if it was unable to determine the type of a parameter, there will be an "X" next to the parameter name in the Parameters list box. Then you must assign the parameter an SQL type by doing the following steps:
    1. Select the parameter in the Parameter list box.
    2. Select the desired type from the SQL Type drop-down list.
    3. Depending on what type you choose, fill in the appropriate values in the Length, Precision, and Scale fields.
    4. Click Modify. The "X" will be replaced by a check-mark.
  7. On the Parameter page, map the parameters to parts of the C++ method and to C++ types by doing the following steps for each parameter:
    1. Select the parameter in from the Parameters list box.
    2. Select a radio button to specify the part of the C++ method that the parameter will be Mapped To. Depending on the parameter type (from the SQL Type page), some of these radio buttons will be enabled and some will be disabled. Choose one of the following settings:
      • Parameter - Maps the SQL statement parameter to a parameter in the C++ method (it will appear in the method signature)
      • Attribute - Maps the SQL statement parameter to an attribute in the mapping. Input values for the method will be taken from the specified attribute in the current <class> object, and output values (if any) will be assigned to the specified attribute in the current <class> object.
      • Return Value - Maps the SQL statement parameter to the return value of the C++ method (it will be assigned to the variable on the left-hand side of an assignment call). This option is enabled only for output parameters.
      • None - Leaves the SQL statement parameter out of the method mapping. This option is enabled only for output parameters.
    3. Depending on your choices for the following option, fields to the right-hand side of the radio button option will be enabled for you to complete, as follows:
      • If you choose Parameter, fill in a Name for the parameter, and select a Type from the drop-down list of appropriate types (one is already selected by default).
      • If you choose Attribute, select a Name from the drop-down list of appropriate <class> attributes.
      • If you choose Return Value, select a Type from the drop-down list of appropriate types (one is already selected by default).
      • There is nothing further to specify if you choose None.
    4. Click the Modify button and move on to the next parameter.
  8. Enter a comment for the method on the Comment Page. This comment will be added to the generated code for the method.
  9. Click OK to close the window and the method is added to the method's container in the Methods window.


User-Defined Methods


Modify User-Defined Methods
Copy User-Defined Methods
Delete User-Defined Methods


Support for Non-Nullable Columns with Default Values
Sample: Create and Use a User-Defined Method With Customized SQL Statement