Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Embedded SQL problem!!
has no ratings.
Published: 04 Oct 2011
Revised: 23 Jan 2013 - 1585 days ago
Last viewed on: 27 May 2017 (3491 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.

Embedded SQL problem!! Published by: Chris Proctor on 04 Oct 2011 view comments(7)

Good morning. I've got one of these wonderful, "It worked great in development, but not in production!" issues..

 

First of all, here's my source where the problem appears to be:

 

else;                                                             
  // clear temp file and write associated line numbers to the file
  exec sql delete  from mlabulkopf;                               
                                                                  
  sqlstmt2='insert into mlabulkopf select sdlnid from ' +         
           %trim(datalib) + '/f4211j1 where sdkcoo = ''' +        
           o_sdkcoo + ''' and sddoco = ' + %char(o_sddoco) +      
           ' and sdktln = ' + %char(o_sdlnid);                    
(The joblog says that it can't find MLABULKOPF even though it is definitely in the job's libl)

                                                                   
  exec sql prepare workfile from :sqlstmt2;                       
  exec sql execute workfile;                                      

  // prepare sql statement for master skus                        
  sqlstmt = 'select $i$oes, $i$sdj, $i$uev +                      
            from ' + %trim(datalib) + '/f56100 +                  
            join mlabulkopf on mllineno=$ilnid where $ikcoo=''' + 
            o_sdkcoo + ''' and $idoco=' + %char(o_sddoco) +       
            ' and $idcto = ''SB'' and $ilotn=''' +                
            %trim(o_sdlitm) + ''' and $i$oes not +                
            in (''N'',''O'') +                                    
            order by $i$oes, $i$sdj for read only';               
endif;                                                            
                                                                  
// prepare, declare and open availability cursor                  
exec sql prepare availability from :sqlstmt;                      
exec sql declare c2 cursor for availability;                      
exec sql open c2;      

dow sqlcod = 0;                                         
                                                        
  // fetch next available f56100 record                
  exec sql fetch next from c2 into :a_$i$oes, :a_$i$sdj,
             :a_sumqty;                                

more code follows fetch

 

In the joblog, the first thing I see is a message that says :

MESSAGE . . . . :   CONVERSION ERROR IN ASSIGNMENT TO HOST VARIABLE A_SUMQTY
CAUSE . . . . . :   DURING AN ATTEMPT TO RETURN A VALUE TO HOST VARIABLE   
  A_SUMQTY ON A FETCH, AN EMBEDDED SELECT STATEMENT, A CALL STATEMENT, A SET
  STATEMENT, A SET DESCRIPTOR STATEMENT, OR A VALUES INTO STATEMENT, ERROR 
  TYPE 1 OCCURRED. A LIST OF THE ERROR TYPES FOLLOWS: -- ERROR TYPE 1 IS   
  OVERFLOW. --

Does this mean that the a_sumqty was greater than the 7.0 variable allows?

This is now in production and cannot be backed out, so any help would be greatly appreciated.

Thanks!                                       

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

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Embedded SQL problem!!
Posted: 5 years 7 months 24 days 10 hours 52 minutes ago
Edited: Tue, 04 Oct, 2011 at 09:11:44 (2062 days ago)

You actually have fields in the database named $i$oes, $i$sdj, $i$uev  ?  That's fun!

You may want to read this thread about a similar issue:

http://www.midrangenews.com/view?id=1390

Are you using NAMEFMT 1 or 0, i.e. *SYS or *SQL ? That might be the issue with the file not found. *SQL uses your *CURLIB while *QSYS uses *LIBL.

You may also (as an aside) consider using the SQLSTATE field instead of SQLCOD.

Check the result of SQLSTATE for < '02000' and everything is fine, otherwise it failed.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Embedded SQL problem!!
Posted: 5 years 7 months 24 days 10 hours 26 minutes ago

Hi Bob. Thanks for the input the NAMEFMT, is that something I have to define within the pgm? If so, then I went with the default. How would I define it? Sorry for the ignorance...

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Embedded SQL problem!!
Posted: 5 years 7 months 24 days 10 hours 10 minutes ago

Bob, it appears, looking at the program that it's compiled with naming convention *SYS.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Embedded SQL problem!!
Posted: 5 years 7 months 24 days 10 hours 8 minutes ago

Chris,

 You can specify it on the compiler, but I tend to embed it at the top of my source member--but it has to be ABOVE any other SQL statements. Its sort of like a KEYLIST (non executable) but is positional, hence it has to be on top.

/free                                          
    EXEC SQL SET OPTION                        
                 COMMIT = *NONE,               
                 CLOSQLCSR = *ENDACTGRP,       
                 NAMING = *SYS ,               
                 DATFMT = *ISO,                
                 DATSEP = '-',                 
                 COMPILEOPT='DBGVIEW(*SOURCE)';
/end-free                                      

 

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Embedded SQL problem!!
Posted: 5 years 7 months 24 days 10 hours 3 minutes ago

I've done that in the past too, Bob, but moved to a new job and forgot it! Lol. I'm just stepping thru it in debug now to see if I can get a little clearer picture of the problem. Thank you so much for your help. I've got a couple hours before they have to rollback everything.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Embedded SQL problem!!
Posted: 5 years 7 months 24 days 9 hours 35 minutes ago

Ok, I'm now in debug and it just executed the following statement and gave me an SQLCOD -204 (File not found).

exec sql delete  from mlabulkopf;     

Because I'm using a remote system SQLPKG I'm assuming I can still have embedded SQL pointing to the local system, right? The MLABULKOPF is definitely in my library list.

 

Yell

Posted by: abc4000
Premium member *
Comment on: Embedded SQL problem!!
Posted: 5 years 6 months 25 days 6 hours 35 minutes ago
Edited: Thu, 03 Nov, 2011 at 13:54:36 (2032 days ago)

Let us see if it works;

Please check the user-profile-login level library-list (by Job-desription) when you connect using SQLPKG.  Thank you... ABC