case results not showing in editor

[Danny Lewkin]

Hey Ivan,

maybe this helps...
Been playing with this stored procedure this morning :

create or replace procedure spLogin(in vc_logon varchar,IN  
vc_password varchar)
begin
	DECLARE users_in_table integer;
	PRINT 'LINE 3';
	declare userid integer;
	PRINT 'LINE 5';
	SET userid = 0;
	PRINT 'LINE 6';
	set userid = select recid from tbl_users where unique_logon = CONCAT 
(vc_logon,vc_password);
	PRINT 'LINE 7';
	IF userid <> 0 then
		PRINT 'LINE 9';
		@user_id = userid;
		PRINT 'LINE 11';
		update tbl_users SET last_login_dt = now() WHERE recid = @user_id;
	else
	set users_in_table = SELECT count(recid) FROM TBL_USERS;
		PRINT 'userid = 0';
		IF (users_in_table = 0) then
			raise 50002,'no users in table';
			PRINT 'Raise 50002';
		ELSEIF (users_in_table > 0) then
			raise 50001, 'invalid login';
			PRINT 'Raise 50001';
		end IF
	END if
exception
	when others THEN
	PRINT 'RUN EXCEPTION';
	call cm_CoreErrorHandler();
end

What I noticed was that if I change the line :

set userid = select recid from tbl_users where unique_logon = CONCAT 
(vc_logon,vc_password);

into

set userid = (select recid from tbl_users where unique_logon = CONCAT 
(vc_logon,vc_password));

suddenly 'vc_logon' becomes a : Error: Kernel error: 0x51500.  
Identifier "vc_logon" is not found.

So I just added the braces, maybe this helps...


Greetz,

Danny







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

> Hello Danny,
>
> Wednesday, October 3, 2007, 11:33:32 AM, you wrote:
>
>> Hi List, Ivan,
>
>> when I call this procedure :
>
>> create or replace procedure ct_select_competences_module(in
>> intModuleID long, IN intOrganisationID LONG, IN strSelectList  
>> VARCHAR)
>> Begin
>>         CASE strSelectList
>>         WHEN 'Module' THEN
>>                 SElect RecID, objptr_module, objptr_comppool->name  
>> As competence_name,
>>                 objptr_comppool->description As comp_description FROM
>> tbl_comppool_modules
>>                 WHERE objptr_module = intModuleID;
>>         WHEN 'All' THEN
>>                 SELECT RecID, name, description FROM  
>> tbl_competence_pool
>>                 WHERE RecID NOT IN (SELECT objptr_comppool FROM
>> tbl_comppool_modules WHERE objptr_module = 1) AND objptr_organisation
>> = intOrganisationID;
>>         ELSE
>>                 SELECT 1;
>>         END CASE
>> END
>
>> After calling this procedure like this :
>> CALL ct_select_competences_module(1, 1, 'All'),
>> I get 1 as Query Result, so the ELSE statement is executed.
>
>
>> So, I guess, the case-statement can NOT compare VARCHARS ?????
>> Same result with the IF-statement...
>
>> Guess this is a bug..??
>
> Yes - I have reproduced it.
>
>
> -- 
> 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