Sample: Create and Use a Stored
Procedure Call on Persistent Object
Objective
The objective of this sample is to
demonstrate how to:
- Create a simple mapping for a database
table.
- Define the stored procedure to Data Access
Builder.
- Add a stored procedure call method to the
mapping.
- Write a program that uses the generated
stored procedure call method run a stored procedure on
the database and return a value.
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:
- Connects to the database.
- Invokes the user-defined stored procedure
call method, POAvg, with 'Employee' and 'Salary' as the
input parameters.
- Prints the average salary returned by the
stored procedure.
- 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:
- Employee - POAvg
- EmployeeDatastore - connect, disconnect
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:
- 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.
- To create the schema
mapping in the SmartGuide pages, do the following steps:
- On the overview
page of the SmartGuide, click Next>>
to go to the next page.
- Select DB2 Driver
and select the database SAMPLE.
- Select By
selecting database tables or views for
the method to create the schema mapping. Click Next>>
to go to the next page.
- Select the desired
database access method (DB2 CLI or DB2 ESQL).
Click Next>> to go to the
next page..
- Click Get
Tables with Filter and when the list of
tables is displayed, select EMPLOYEE.
- Click Finish.
You will return to the IDE.
- Edit the new Data Access
part by clicking the Edit Part button.
This will launch the Data Access Builder main window.
- Select Properties
from the mapping icon's
popup menu.
Ensure the Class Name field contains
"Employee" and the File Stem
field contains "Empl".
- Change the empno
attribute to be a data ID by doing the following steps:
- Select the Employee
mapping icon. Select Attributes
from its popup menu.
- From the list of
attributes, select the empno
row.
- Enable the Data
Identifier check box (near the bottom of
the dialog).
- Click OK
to close the notebook.
- Define the stored
procedure before you create the store procedure call
method by doing the following steps:
- In the Data Access
Builder window, select the Employee
schema icon.
- Select Stored
Procedures from the schema's popup menu.
- In the Stored
Procedure List window, click Add
to define the stored procedure
- 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.
- Click OK to close
the window. The colstats stored procedure is
displayed in the Imported Store
Procedures container.
- Click Close to
close the window.
- Add the store procedure call method for
colstats stored procedure by doing the following steps:
- In the Data Access
Builder window, select the Employee
mapping icon
.
- Select Methods
from the mapping's popup menu.
- In the Methods
window, click Add under the Methods
as Store Procedure Calls container.
- 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.
- In the Data Access Builder
window, select Save from the File
menu to save the mapping.
- Select the mapping icon
and select Generate
from its popup menu to generate the C++ classes. Exit
Data Access builder
- 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