Sample: Create and Use a Stored Procedure Call on Persistent Object

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

This sample can be run for DB2 access only.

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

Description
This sample does the following:

  1. Connects to the database.
  2. Invokes the user-defined stored procedure call method, POAvg, with 'Employee' and 'Salary' as the input parameters.
  3. Prints the average salary returned by the stored procedure.
  4. 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 udmspcpo. Since for this sample, Data Access Builder generates code for two access methods (DB2 CLI and DB2 ESQL), a udmspcpo executable is located in each of two corresponding directories: db2cli and db2esql (these are subdirectories of samples\database\minisamp).

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

udmspcpo

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:

udmspcpo userid password

The source code for the udmspcpo 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 two directories: db2cli and db2esql. 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. Select DB2 Driver and select the database SAMPLE.
    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. Select the desired database access method (DB2 CLI or DB2 ESQL). Click Next>> to go to the next page..
    5. Click Get Tables with Filter and when the list of tables is displayed, select EMPLOYEE.
    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. Define the stored procedure before you create the store procedure call method by doing the following steps:
    1. In the Data Access Builder window, select the Employee schema icon.
    2. Select Stored Procedures from the schema's popup menu.
    3. In the Stored Procedure List window, click Add to define the stored procedure
    4. In the Stored Procedure window, do the following:
      • In the Procedure Name field, enter colstats.
      • For the first parameter:
        • Select In for Parameter Type.
        • In the SQL Name field, enter table.
        • Select CHAR for SQL Type.
        • Click Add.
      • For the second parameter:
        • Select In for Parameter Type.
        • In the SQL Name field, enter column.
        • Select CHAR for SQL Type.
        • Click Add.
      • For the third parameter:
        • Select Out for Parameter Type.
        • In the SQL Name field, enter count.
        • Select INTEGER for SQL Type.
        • Click Add.
      • For the fourth parameter:
        • Select Out for Parameter Type.
        • In the SQL Name field, enter avg.
        • Select DOUBLE for SQL Type.
        • Click Add.
      • For the fifth parameter:
        • Select Out for Parameter Type.
        • In the SQL Name field, enter std.
        • Select DOUBLE for SQL Type.
        • Click Add.
    5. Click OK to close the window. The colstats stored procedure is displayed in the Imported Store Procedures container.
    6. Click Close to close the window.
  7. Add the store procedure call method for colstats stored procedure by doing the following steps:
    1. In the Data Access Builder window, select the Employee mapping icon .
    2. Select Methods from the mapping's popup menu.
    3. In the Methods window, click Add under the Methods as Store Procedure Calls container.
    4. In the Stored Procedure call method, do the following steps to add the method:
      • On the Stored Procedure page, select colstats from the Stored Procedure list.
      • In the Method Name field, enter POAvg.
      • On the Parameters page, select table from the Parameters list box. Then select the Parameter radio button, enter table for the Name, and click Modify to change the mapping.
      • Select column from the Parameters list box. Then select Parameter radio button, enter column for the Name, and click Modify to change the mapping.
      • Select count from the Parameters list box. Then select None radio button to unmap it. You do not map this parameter because you are not using it in your program.
      • Select avg from the Parameters list box. Then select Return Value radio button, select DOUBLE for the Type, and click Modify to change the mapping.
      • Select std from the Parameters list box. Then select None radio button to unmap it. You do not map this parameter because you are not using it in your program.
      • Click OK to close the notebook.
      • The POAvg method is displayed in the Methods as Store Procedure Calls container. Click Close to close the window.
  8. In the Data Access Builder window, select Save from the File menu to save the mapping.
  9. Select the mapping icon and select Generate from its popup menu to generate the C++ classes. Exit Data Access builder
  10. 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