Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use the Adapter DB with Stored Proc with parameters input and output

Status
Not open for further replies.

benoxy

Programmer
Jun 19, 2007
14
FR
Hello,

We want to use adapter DATABASE most natively as possible because we gains performances with this process.
( example stored procedure @Proto_wtx_cht10dbout_integrer( @param1 char(4) input, @param2 varchar input, @param1 int output )
I do not want to use DBLOOKUP or DBQUERY.
I can retrieve the retun value with this format ( -MDQ E: \ APPLI \ ad_ \ w2 \ mdq \ ad_acces_db.mdq -DBNAME ad_rpsit_aa -PROC Proto_wtx_cht10dbout_integrer -TRACE ).
But I can't retrieve the output parameter value of this stored procedure.
Could you create a type tree dedicated to recover the value output by catch mecanism ?

Thank
 
the easiest way to do it is to use DBLOOKUP or DBQUERY - why can't you use them?

the alternative is to have a parent map build the adapter string (so that you can populate the input variables) and override the input of the child map, so the child map gets the results of the DB procedure call.

if the procedure is called using an output card you won't be able to get back the results.
 
I don't want to work with DBLOOKUP / DBQUERY for reasons of performance gains. When you use the Database adapter ( DATABASE ), the process is faster.
I want to handle errors in the stored procedure.
Thanks for the information, OllyC. I will try with this syntax: =PUT("DB","-MDQ E:\APPLI\ak_\w1\mdq\ak_acces_db.mdq -DBNAME ad_rpsit_aa -PROC proto_wtx_cht10dbout_integrer -TRACE",PACKAGE( ligne:ce_proto_wtx ))

Benoxy
 
AFAIK DBLOOKUP and DBQUERY are the same speed as using input / output cards (or the GET/PUT functions) - why do you think they are slower?

 
I measured the execution time with adaptor DATABASE under these conditions :
* Turn on the output card DATABASE option
* Include in the output card this script line: - MDQ E:\APPLI\ak_\ w1 \ mdq \ ak_acces_db.mdq - DBNAME ad_rpsit_aa - PROC proto_wtx_cht10dbout_integrer -TRACE.

I obtain better times with this use

Example:
For an insertion of 10000 recordings, using the DBLOOKUP, the treatment lasts 2 minute. With the DATABASE I obtain 7s

 
What were the parameters to DBLOOKUP? Were you committing after each row? I would have thought with the same parameters the process time should be very similar.
 
=DBLOOKUP("call ?=proto_wtx_chantier10_integrer('" + co_wtx_message:ce_f_proto_wtx + "', '"
+ lb_wtx_message:ce_f_proto_wtx + "',?)",
"-MDQ E:\APPLI\ad_\w2\mdq\ad_acces_db.mdq -DBNAME ad_rpsit_aa")
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top