stored procedures

[Danny Lewkin]

Thanks for solving that Ivan!
Is there a way for me, to check where the exception came from? From  
which instruction, or line in a stored procedure??
This would come in very handy... I would have maybe found the error  
myself, but now I didn't know that the exception came from that  
instruction or line in the stored procedure....
I know it's a lot to ask for (maybe)....but it would probably save  
you some time..??






>> 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

Op 3-okt-07, om 16:22 heeft Ivan Smahin het volgende geschreven:

> 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
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina