bug with variables, link & stored procedures ?

[Steven Ophalvens]

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.

Greetings,

Steven Ophalvens

-----Oorspronkelijk bericht-----
Van: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] Namens Ivan Smahin
Verzonden: donderdag 27 maart 2008 14:11
Aan: Valentina Developers
Onderwerp: Re: bug with variables, link & stored procedures ?

Hello Steven,

Thursday, March 27, 2008, 2:59:12 PM, you wrote:

> I got more problems using this workaround. At first this workaround seems
to
> work, but
> upon subsequent calls of this stored procedure, using the declared
variables
> with a
> 'select' causes the original value to be used, not the value that it is
> supposed to be :

> Just a part of my stored procedure :
> ---
>         print 'idtestresult = ';
>         print idtestresult;
>         print ' select idtestresult =';
>         print (select idtestresult);
> ---
> When I have run this stored procedure 3 times, I get the following in my
> warnings log file:
> ---
> Print : idtestresult = 
> Print : 1
> Print :  select idtestresult =
> Print : 1
> Print : idtestresult = 
> Print : 2
> Print :  select idtestresult =
> Print : 1
> Print : idtestresult = 
> Print : 3
> Print :  select idtestresult =
> Print : 1
> Print : idtestresult = 
> Print : 4
> Print :  select idtestresult =
> Print : 1
> ---

I will try to reproduce it.

> So the variables themselves contain the correct value, but when called
with
> select, they don't
> give the correct result. BUT I have to use it with a select to be able to
> use the link statement,
> because the link statement fails without the select keyword.

> This is a real showtopper for me.

> Any ideas?

Originally it was:


         set idtestresult = SELECT Last_RecID('lpserver') as id;
         set idtestvraag = select id from testVragen where test_id=testId
and vraag_id =VraagID;

         link record (idtestresult) of test_resultaten
                 with record idtestvraag of testVragen
                 using link_test_resultaat_vraag;


You can try this instead:

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


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

_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina

__________ NOD32 2277 (20070518) Informatie __________

Dit bericht is gecontroleerd door het NOD32 Antivirus Systeem.
http://www.nod32.nl