Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Problem debugging RMTDB SQL
has no ratings.
Published: 02 Jan 2014
Revised: 03 Jan 2014 - 1804 days ago
Last viewed on: 12 Dec 2018 (3140 views) 

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.

Problem debugging RMTDB SQL Published by: Chris Proctor on 02 Jan 2014 view comments(3)

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      

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS