bug with variables, link & stored procedures ?
Steven Ophalvens
silversoft at skynet.be
Thu Mar 27 09:11:45 CDT 2008
Some more info, with an other example, same kind of problems :
-----------
CREATE OR REPLACE PROCEDURE [sp_login_gebruiker]( IN inId LONG )
BEGIN
print 'curtime() :';
print (select curtime());
update gebruikers
set
ingelogd = TRUE,
login_datum = (select curdate()),
login_tijd = (select curtime())
where id = inId;
END
-----------
After 3 logins with several seconds in between : in the warning log file:
---
Print : curtime() :
Print : 15:06:13:773
Print : curtime() :
Print : 15:06:13:773
Print : curtime() :
Print : 15:06:13:773
---
Greetings,
Steven Ophalvens
-----Oorspronkelijk bericht-----
Van: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] Namens Steven Ophalvens
Verzonden: donderdag 27 maart 2008 14:53
Aan: 'Valentina Developers'
Onderwerp: RE: bug with variables, link & stored procedures ?
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
_______________________________________________
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
More information about the Valentina
mailing list