stored procedures

[Ivan Smahin]

Hello Danny,

Tuesday, October 2, 2007, 1:04:59 PM, you wrote:

> Hi List,

> I have these stored procedures in my DB:

> create or replace procedure spLogin(in vc_logon varchar,IN  
> vc_password varchar)
> begin
>         DECLARE users_in_table integer;
>         declare userid INTeger;
>         set userid = select recid from tbl_users where unique_logon = CONCAT
> (vc_logon,vc_password);
>         IF userid IS NOT null then
>                 @user_id = userid;
>                 update tbl_users SET last_login_dt = now() WHERE recid = @user_id;
>         else
>         set users_in_table = SELECT count(recid) 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();
> end


> create or replace procedure cm_CoreErrorHandler()
> begin
>         declare @errorStr varchar(2044);
>         @errorStr = concat('time: ',now(),' user with id ', at user_id,'
> generated error with id ',@@LastErrorNumber,': ',@@LastErrorMessage);
>         print @errorStr;
> end


> When I try to login like this
> CALL spLogin('dummy', dummy');
> SELECT @errorStr;

> I don't get the expected error 50001 'invalid login', instead I get  
> this :

> 'it's too few values'  and errornumber was 13...something, I had to  
> close VStudio...

Fixed. Actually you've got exception from
set userid = select recid from tbl_users where unique_logon = CONCAT(vc_logon,vc_password);

Because there is no result and whole operation (assignment) treated as
error.

Next, take a look on cm_CoreErrorHandler. Following concat() rules (as
most  of  the  functions) if some argument is NULL then result must be
set to NULL too.

Not-initialized variable is set to NULL internally.
@user_id  is  NULL in "IF userid IS NOT null then" else block.

So  you  have  to  initialize  the  variable before using (seems to be
common rule).

...
else
    SET @user_id = 0;
    ...


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