Sample: Create and Use a User-Defined Method with SQL Predicate

Objective
The objective of this sample is to demonstrate how to:

The sample database you will use must be set up before you work with the sample. See Preparation for Samples for information.

Description
This sample does the following:

  1. Connects to the database.
  2. Opens a cursor on the table using the openEmpInDept method, which gets the result set using the user-defined method empInDept. The SQL predicate of this user-defined method is "WHERE WORKDEPT = ?". The program passes in the value 'D11' for the parameter.
  3. Loops through the rows using the fetchNext and element methods, and prints each row.
  4. Closes the cursor.
  5. Disconnects from the database.

Generated Data Access Classes Used by this Sample
This sample uses the following generated classes and methods, as defined in the Data Access mapping, minisamp.dax:

Some of the methods listed are inherited from the Data Access class library.

Running the Sample
The executable for the sample is called udmsqlpr. Since Data Access Builder generates code for three access methods (CLI, ESQL, and ODBC), a udmsqlpr executable is located in each of three corresponding directories: db2cli, db2esql, and odbc (all of these are subdirectories of samples\database\minisamp).

To run the sample, enter the following on the command line of the appropriate directory:

udmsqlpr

If the sample fails because it could not connect to the database, enter a user ID and password that is valid for accessing the sample database, as shown:

udmsqlpr userid password

The source code for the udmsqlpr executables is located in the samples\database\minisamp\source directory. If you make changes to the sample's source code, you must import the sample into an IDE project and rebuild it.

The Data Access Builder Mapping
The Data Access samples use a complex mapping, which is provided for you in a file called minisamp.dax. One minisamp.dax mapping file is provided in each of the three directories: db2cli, db2esql, and odbc. If you want to see the mapping or generate the classes for it, import the sample into the IDE; in the Data Access Launch view, select the part and click Edit Part.

Hints on How to Create a Mapping for This Sample
Rather than using the mapping provided with the samples (minisamp.dax), you can create your own mapping that will work with this sample (but not necessarily the other samples). To create a mapping for this sample, follow these steps:

  1. In the IDE, create a new project, an executable target, and a new Data Access part. When you choose to add a Data Access part, the Data Access SmartGuide will start up.
  2. To create the schema mapping in the SmartGuide pages, do the following steps:
    1. On the overview page of the SmartGuide, click Next>> to go to the next page.
    2. If you are using DB2, select DB2 Driver and select the database SAMPLE; if you are using dBase, select ODBC Driver and select the database CPPASAMP40.
    3. Select By selecting database tables or views for the method to create the schema mapping. Click Next>> to go to the next page.
    4. If you are using DB2, select the desired database access method (DB2 CLI or DB2 ESQL). Click Next>> to go to the next page. If you are using ODBC, you will not see this page.
    5. Click Get Tables with Filter and when the list of tables is displayed, select EMPLOYEE for DB2 or EMPL for dBase.
    6. Click Finish. You will return to the IDE.
  3. Edit the new Data Access part by clicking the Edit Part button. This will launch the Data Access Builder main window.
  4. Select Properties from the mapping icon's popup menu. Ensure the Class Name field contains "Employee" and the File Stem field contains "Empl".
  5. Change the empno attribute to be a data ID by doing the following steps:
    1. Select the Employee mapping icon. Select Attributes from its popup menu.
    2. From the list of attributes, select the empno row.
    3. Enable the Data Identifier check box (near the bottom of the dialog).
    4. Click OK to close the notebook.
  6. Create the user-defined method using SQL Predicate by doing the following steps:
    1. In the Data Access Builder window, select the Employee mapping icon.
    2. Select Manager Methods from the mapping's popup menu
    3. In the Manager Methods window, click Add under Methods with SQL Predicates container.
    4. In the SQL Predicate notebook, on the SQL Query page, enter "empInDept" in the Method Name field.
    5. Enter the following for the SQL predicate:
      where workdept=?
    6. Click Validate to connect to the database for validation.

      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
    7. On the Parameter page, select the parameter and enter "Department" in the Name field.
    8. Click Modify to change the parameter name.
    9. Click OK to close the notebook.
    10. The empInDept user-defined method is now displayed in the Methods with SQL Predicates container. Click Close to close the window.
  7. In the Data Access Builder window, select Save from the File menu to save the mapping.
  8. Select the mapping icon and select Generate from its popup menu to generate the C++ classes. Exit Data Access Builder.
  9. Return to the IDE and build the part.


Data Access Builder
Data Access Builder Workflow
User-Defined Methods


Data Access Class Library and Generated Class Reference
Data Access Builder Samples
Preparation for Samples