Sample: Create and Use a User-Defined Method with Customized
SQL Statement
Objective
The objective of this sample is to demonstrate how to:
- Create a simple mapping for a database table.
- Add a user-defined method with a customized SQL statement
to the mapping.
- Write a program that uses the generated user-defined
method perform a function on the table rows and return a
value.
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:
- Connects to the database.
- Invokes the user-defined method numEmpInDept method with
'D11' as the input parameter, which counts the number of
employees in department D11
- Prints the integer (that is, the number of employees in
the department) returned by the user-defined method.
- 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:
- EmployeeManager - numEmpInDept
- 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 udmcsql.
Since Data Access Builder generates code for three access methods
(CLI, ESQL, and ODBC), a udmcsql 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:
udmcsql
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:
udmcsql userid password
The source code for the udmcsql 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:
- 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.
- 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.
- Select By selecting database tables or
views for the method to create the
schema mapping. Click Next>> to
go to the next page.
- 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.
- Click Get Tables with Filter and
when the list of tables is displayed, select Employee
for DB2 or EMPL for dBase.
- 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.
- Create the user-defined method using SQL Predicate 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 with Customized SQL
Statements container.
- On the SQL Statement page, enter numEmpInDept in
the Method Name field.
- Enter the following the SQL statement field:
select count(workdept) from employee where workdept=?
- Click Validate to connect to the
database to validate the SQL statement.
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
- On the parameters page, select the In parameter
from the Parameters list box.
Then select the Attribute radio
button. Enter department for the
name and select String for the
data type.
- Click Modify to change the
parameter.
- Select the result column parameter from the Parameters
list box. Then select Return Value
radio button and select Integer
for the data type.
- Click Modify to change the
parameter.
- Click OK to close the notebook.
- The numEmpInDept is displayed in the Methods
with Customized SQL Statements
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