bug with variables, link & stored procedures ?

[Ivan Smahin]

Hello Steven,

Thursday, March 27, 2008, 3:53:12 PM, you wrote:

> Hello Ivan,

> there remain some problems with the unpredictable values I get in stored
> procedures
> I changed my SP to the following : 

> ----------
> reate or replace procedure [sp_answer_question](
>         IN correct boolean, 
>         IN testId long, 
>         IN VraagID long, 
>         in gebruikerID long)

> BEGIN

>         insert into test_resultaten (juist) values (correct);

>         print 'correct :';
>         print correct;
>         print 'testId';
>         print testId; 
>         print 'VraagID'; 
>         print VraagID;  
>         print 'gebruikerID';
>         print gebruikerID;

>         print 'SELECT Last_RecID( lpserver )';
>         print (SELECT Last_RecID('lpserver'));

>         link (SELECT Last_RecID('lpserver')) of test_resultaten 
>                 with (select id from testVragen where test_id=testId and
> vraag_id =VraagID) of testVragen 
>                 using link_test_resultaat_vraag;

>         print 'SELECT Last_RecID( lpserver )';
>         print (SELECT Last_RecID('lpserver'));

>         link (SELECT Last_RecID('lpserver')) of test_resultaten 
>                 with (select id from studenten_testen where test_id=testId
> and gebruiker_id =gebruikerID) of studenten_testen 
>                 using link_stud_test_resultaten;

> END
> ----------
> After 3 executions I get this in my warning log file :

> --------------
> Print : correct :
> Print : 1
> Print : testId
> Print : 6
> Print : VraagID
> Print : 710792656
> Print : gebruikerID
> Print : 8
> Print : SELECT Last_RecID( lpserver )
> Print : 1
> Print : SELECT Last_RecID( lpserver )
> Print : 1
> Print : correct :
> Print : 1
> Print : testId
> Print : 6
> Print : VraagID
> Print : 1389960124
> Print : gebruikerID
> Print : 8
> Print : SELECT Last_RecID( lpserver )
> Print : 1
> Print : correct :
> Print : 0
> Print : testId
> Print : 6
> Print : VraagID
> Print : 1407737997
> Print : gebruikerID
> Print : 8
> Print : SELECT Last_RecID( lpserver )
> Print : 1
> Print : SELECT Last_RecID( lpserver )
> Print : 1
> --------------------

> In my VServer_Embedded.log I get this :
> ...
> 14:38:38.186000 (2232): (648) I_SqlDatabase 'lpserver.vdb' SqlQuery 'call
> SP_ANSWER_QUESTION(:1,:2,:3,:4)'.
> 14:38:38.488000 (2776): (648) Database 'lpserver' has been successfully
> closed.
> ...
> 14:38:43.954000 (5136): (644) I_SqlDatabase 'lpserver.vdb' SqlQuery 'call
> SP_ANSWER_QUESTION(:1,:2,:3,:4)'.
> 14:38:44.143000 (5136): (644) ERROR 0x70504: Record "10" does not exist in
> table "test_resultaten". 
> 14:38:44.175000 (2776): (644) Database 'lpserver' has been successfully
> closed.
> ...
> 14:38:51.085000 (2776): (680) I_SqlDatabase 'lpserver.vdb' SqlQuery 'call
> SP_ANSWER_QUESTION(:1,:2,:3,:4)'.
> 14:38:51.354000 (2776): (680) ERROR 0x70504: Record "8" does not exist in
> table "test_resultaten". 
> 14:38:51.386000 (4852): (680) Database 'lpserver' has been successfully
> closed.


> I hope this helps you in solving this.


The only thing I see is attempt to use binding values in place of SP
params. Definitely it should work, but obviously there is some glitch.

Actually, binded-values usage brings no much sense in this case in
terms of performance.

SP-body is prepared and 'cached' once (including params). So there is no overhead with
preprocessing params on each call. Moreover in this case it
brings some extra operations for preparing and packing/unpacking params.

I suggest to avoid SP-call + binding combination.



-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com