NEED HELP debugging stored procedure
Hi:
I'm a novice to writing stored procedures but am developing
a Powerbuilder 4.0 app & need to learn. I've written a simple
SP that I call from a PB4 function. The problem I am having is
that my SP seems to return bad values for valid data - ie:
it's job is to check the existance of a clientid in a client
table - if exists we return a 1, not exists we return a zero.
When called with a clientid that is valid, it returns a zero,
incorrectly. The SP is below:
//start
CREATE PROCEDURE sp_iscidok ( q_cid char(6)) returning int;BEGIN
define rval int;
define x_cid char(6);
define sqlerr int;
ON EXCEPTION SET sqlerr
if sqlerr = 100 then
return 0;
else
return 1;
end if
select unique cid into x_cid from client where cid = q_cid;
END EXCEPTION
END
END PROCEDURE
//end
this gets called from a function defined as follows:
int f_check_client( string cid )
//start
int rval
DECLARE isok PROCEDURE FOR dbt.sp_iscidok( :cid );
if sqlca.sqlcode <> 0 then
MessageBox("declare error")
return 0
end if
EXECUTE isok;
if sqlca.sqlcode <> 0 then
MessageBox("execute error")
return 0
end if
FETCH isok into :rval;
if sqlca.sqlcode <> 0 then
MessageBox("fetch error")
return 0
end if
return rval;
//end
no matter what I pass in the SP returns 0.
Also sometimes the execute statement fails with a SQLCODE of -1
Any ideas?
How to debug?
How to write better to achieve my purpose?
Thanks in advance
Mike
--
Michael Stenzler <rpcg@panix.com>
RPCG, Inc. 70 Hudson St. Hoboken, NJ 07030
All opinions expressed herein belong to me - not RPCG.