Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
Happy New Year, everyone. I'm hoping someone might have a solution for a debug problem I'm having. I have an SQLRPGLE program that uses an SQL cursor to a remote system (compiled with RMTDB parm). It runs when I'm not in debug; however, if I try to debug it to see the results of the FETCH, it gives me an SQLCODE -514, which says it could not find my PREPARE statement. It is in the code, but because of that error the cursor cannot open. When I put a breakpoint on the OPEN, I see the SQLCODE error. Could it have something to do with userID and authority? Just guessing... Here's a snippet of how my code is set up:
sqlstmt = 'select sdurrf, sddoco, +
substr(sdaitm,1,12), substr(sdaitm,13,2), sdlitm, sddrqj, +
sdlttr, sdnxtr, +
sum(((sduorg*.001)-(sdsocn*.001)-(sdqtyt*.001)) * +
(sduprc*.0001)), sum(((sduorg*.001)-(sdsocn*.001)-(sdqtyt*.001)) +
* (sduncs*.0001)), sum((sduorg*.001)-(sdsocn*.001)-(sdqtyt*.001)) +
from ' + %trim(datalib) + '/f4211 where sddcto = ''SB'' and +
sdaitm <> '' '' and sdan8 in +
(2000,6843,9833,78255,130138,130139, +
144119,151660,158521,149078,173566,164455) and +
sdurrf in +
('' 990'','' 993'','' 994'','' 995'',+
'' 996'','' 997'','' 998'','' 409'',+
'' 408'','' 411'','' 893'','' 894'',+
'' 895'','' 896'','' 897'','' 898'',+
'' 407'') and +
sdeuse <> ''M'' +
and ((sduorg*.001)-(sdsocn*.001)-(sdqtyt*.001)) >0 group by +
sdurrf, sddoco, substr(sdaitm,1,12), substr(sdaitm,13,2), sdlitm, +
sddrqj, sdlttr, sdnxtr order by sdurrf, sddoco, substr(sdaitm,1,12), +
substr(sdaitm,13,2), sdlitm, sddrqj, sdlttr, sdnxtr for read only';
// prepare, declare the jdebulks cursor
exec sql prepare jdebulks from :sqlstmt;
exec sql declare c1 cursor for jdebulks;
// connect to the remote host
exec sql connect to :rmthost user :rmtuser using :rmtpass;
// open the cursor
exec sql open c1;
dow sqlcod = 0;
// retrieve next available record
exec sql
fetch next from c1 into :o_sdurrf, :o_sddoco, :o_upc,
:o_label, :o_sdlitm, :o_sddrqj, :o_sdlttr, :o_sdnxtr,
:o_sumret, :o_sumcst, :o_sumqty;
// exit loop if no record found
if sqlcod <> 0;
leave;
endif;
Thanks for any assistance!
Chris
Just a guess, UPDPROD(*yes) ?
also, start using SQLSTATE, it's 2014, not 1994.
Hi Bob. Yes, I do have updprod(*yes). Lol!! I think 1994 is about when this program was written! Just curious tho, what does SQLSTATE buy you that SQLCODE doesn't?
Something I did notice.... In my joblog, when in debug, it says it couldn't find the file associated with the cursor, which is true, it's on the remote system. So, should I move the CONNECT up before the PREPARE and OPEN? If so, I wonder how it was ever working.
I looked more closely at your code:
from ' + %trim(datalib) + '/f4211