bug with variables, link & stored procedures ?

[Steven Ophalvens]

Dag Bart,

thanks for your suggestion. I solved the login procedure problem, by
directly using (curtime())
instead of (select (curtime()), which is cached.

In my first attempts, I also used to declare my variables, but that didn't
seem to resolve my problem.
My biggest problem now is that the results of subqueries are cached, so
subsequent calls to my SP
try to work with wrong data.

Mvg,

Steven Ophalvens

-----Oorspronkelijk bericht-----
Van: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] Namens Mr. Bart Pietercil
Verzonden: donderdag 27 maart 2008 17:52
Aan: Valentina Developers
Onderwerp: Re: Re[4]: bug with variables, link & stored procedures ?

Dag Steven,

we are working I think on similar functionality. This is our login  
procedure (called from php)

create or replace procedure cm_spLogin(in vc_logon varchar,IN  
vc_password varchar)
begin
DECLARE users_in_table integer;
declare userid integer;
DECLARE logon VARCHAR;
DECLARE intCount INTEGER;
set logon = CONCAT(vc_logon,vc_password);
SET intCount = SELECT count(*) FROM tbl_users where unique_logon =  
logon;
SET userid = 0;
IF intCount <> 0 THEN
set userid = select recid from tbl_users where unique_logon = logon;
SET @user_id = userid;
update tbl_users SET last_login_dt = now() WHERE recid = @user_id;
else
SET users_in_table = 0;
set users_in_table = SELECT count(*) FROM TBL_USERS;
IF (users_in_table = 0) then
raise 50002,'no users in table';
ELSEIF (users_in_table > 0) then
raise 50001, 'invalid login';
end IF
END if
exception
when others THEN
call cm_CoreErrorHandler('cm_spLogin');
end;




we had our bunch of issues but this seems to be working stable.
As you can see we 'declared' the variables we our using. I wonder if  
declaring your variables would change the issues you are seeing.


vriendelijke groet

Bart Pietercil



On 27-mrt-08, at 17:32, Steven Ophalvens wrote:
> I sent a test version of the database to your email address.
>
> -----Oorspronkelijk bericht-----
> Van: valentina-bounces at lists.macserve.net
> [mailto:valentina-bounces at lists.macserve.net] Namens Ivan Smahin
> Verzonden: donderdag 27 maart 2008 17:04
> Aan: Valentina Developers
> Onderwerp: Re[4]: bug with variables, link & stored procedures ?
>
>
>
> Could you send me database for testing?
>
> -- 
> 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

_______________________________________________
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