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 - 1703 days ago
Last viewed on: 22 Sep 2017 (3700 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)

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 11 months 20 days 10 hours 14 minutes ago
Edited: Tue, 04 Oct, 2011 at 09:11:44 (2180 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 11 months 20 days 9 hours 47 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 11 months 20 days 9 hours 31 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 11 months 20 days 9 hours 29 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 11 months 20 days 9 hours 25 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 11 months 20 days 8 hours 56 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 10 months 20 days 5 hours 56 minutes ago
Edited: Thu, 03 Nov, 2011 at 13:54:36 (2150 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