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 - 1242 days ago
Last viewed on: 28 May 2017 (2178 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

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Problem debugging RMTDB SQL
Posted: 3 years 4 months 26 days 18 hours 31 minutes ago

Just a guess,  UPDPROD(*yes) ?

also, start using SQLSTATE, it's 2014, not 1994. 

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Problem debugging RMTDB SQL
Posted: 3 years 4 months 26 days 18 hours 28 minutes ago
Edited: Thu, 02 Jan, 2014 at 13:44:51 (1243 days ago)

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. 

 

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Problem debugging RMTDB SQL
Posted: 3 years 4 months 25 days 21 hours 21 minutes ago
Edited: Fri, 03 Jan, 2014 at 14:30:01 (1242 days ago)

I looked more closely at your code:

from ' + %trim(datalib) + '/f4211

  1. You're using the *SYS naming convention. That's normally fine, but I've found that using the *SQL naming convention works better with Remote Connections.
  2. SQLState is the preferred variable to check for errors as it is platform independent. SQLCOD (you don't use SQLCODE) is really RPG-only and IBM i specific.
  3. Often it is beneficial to use EXEC SQL RELEASE ALL; at the start and/or end of your App to reset any rogue connections that may still be open.