|
Return to Newsletter Contents
Data Integrator: Using Stored Procedures
by: Tom Hinkle, Consultant, CRCP, Data Integrator
Instructor
Overview
Stored Procedures are not part of the Data Integrator class
curriculum and are mentioned in the reference material, but not a lot of detail
is provided. You are able to call a
database stored procedure anywhere that a SQL statement can be written.
However, depending on what that stored procedure does and whether it has
output parameters will determine exactly where it can be used.
In one of the few references to stored procedures in the
class manual, Lesson 5 under Pushed-down operations to the database, you will
find the following note:
Not all operations can be combined into single requests.
For example, when a stored procedure contains a COMMIT
statement or does not return a value, you cannot combine the stored procedure
SQL with the SQL for other operations in a query.
You can only push operations supported by the DBMS down to that DBMS.
More information about using stored procedures can be found
in the Data Integrator Reference Guide, Chapter 6 Functions and Procedures.
Importing
Stored Procedures
Before Data Integrator can call a procedure it must be
imported to the datastore. This is
done by right clicking the datastore in the Datastores tab.
Select Import By Name. You
will see the Import By Name dialog box.
Click the drop down arrow to the right of the Type label.
Select Function, enter the procedure name and verify the owner is correct,
then click the OK button. After a
short delay you can expand the datastore list and expand the Function list to
see the function name displayed.
Double click the function name and it will open a properties page and you will
see a list of input and output parameters.
Calling
Stored Procedures
Here is an example of how you could use a stored procedure
from a SQL Server 2000 database.
This procedure generates a primary key used in a Company table and also writes
that number to a control table to store the next available id for the
application to use. If you try
placing the procedure in the mapping clause it will fail. Because it returns a
value you cannot include the mapping SQL needed.
Using the following method will enable you to use the stored procedure
with satisfactory results. Use a Template table for the target table and create
a new output column with the return parameter.
To do this, create the datastore and place the source table
and query transform in the workspace.
Place a template table as your target and connect the objects.
Open the query transform and map the desired in and out columns.
To add the function, right click on the output list and select New
Function Call. Select Insert Above
or Insert Below, the Function Wizard will appear. Select the datastore name from
the list on the left and you will notice the function names in the right list.
Select the function (procedure) you want and click the Next button.
You will now see a list of the input parameters.
Enter the parameters and click Next.
The Select Output Parameters window appears.
Select the AL_SP_RETURN parameters and use the > button to move it to the
right box or Drag it into the right box.
Double click the name under the Column in query column to change the
name. Click Finish and it will be
added to the Schema Out list. The
Query Transform sample window above displays the result.
To use this value as the Company ID create another dataflow
using the Template table from the previous dataflow as your source table.
Add a Query transform and the Company table as the target.
Mapping the columns from the source to the target and deleting the
unneeded output columns finish the dataflow.
Run the job and view the result.
Data Integrator inserted the new rows to the Company table and the next
person to use the application will have the correct new ID available.
Note: Using
stored procedures will require some testing in Data Integrator and knowledge of
the procedure operation is needed.
You may find the following discussion forum very helpful when researching
difficult Business Objects topics:
www.forumtopics.com/busobj.
You will find a discussion on Data Integrator and some helpful threads.
Go to Top |
Return to Newsletter Contents
|
