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

[benoxy]

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

 

[OllyC]

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.

 

[benoxy]

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

 

[OllyC]

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?

 

[benoxy]

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

 

[janhes]

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.

 

[benoxy]

=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")