Retrieve SQL Source on IBM i via a batch job

Scott Lewis
Scott Lewis used Ask the Experts™
Hello, I am attempting to use the QSQGNDDL api on the IBM i. I am running V7R2 and have attached the source I have written. Whenever I execute it I receive an SQL7042. I have double checked my parameters against posted code on the net and don't see any issues in that area. I tried downloading some posted code and running that and get the same result. Are there any caveats that are unpublished? I know this issue has risen in the past and I have reviewed those articles. Again, it looks like my code is correct. Any help would be appreciated!

ScottPrint Screen of a debug session showing the SQL7042 errorrtvsqlsrc.txt
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gary PattersonVP Technology / Senior Consultant

The second level message text provides a return code that tells you the name of the problem field.  Just kook at the job log, pull up the error message, read the second level help text for more information.
Scott LewisIBM i Programmer/Administrator


Hey Gary, That is one of the first things I did to no avail. It seems the api doesn't like to return information. In other words, I execute the program which call QSQLGNDDL. The job log is silent. Even when I am in debug. I am set to log(4 0 *seclvl). Below is my detailed job log.

PREPARE of statement SYSTABLE_CURSOR completed.                    
ODP reused.                                                        
Cursor SYSTABLE opened.                                            
1 rows fetched from cursor SYSTABLE.                              
ODP not deleted.                                                  
Cursor SYSTABLE was closed.                                        
/*      */                                                        

Here is my debug session right after the call looking at the errorcode field. The next area is a hex view of field errordata.


EVAL errorcode                                                        
BYTESPROVIDED OF ERRORCODE = 128                                      
BYTESAVAILABLE OF ERRORCODE = 20                                      
ERRORMSGID OF ERRORCODE = 'SQL7042'                                    
FILLER OF ERRORCODE = '0'                                              
ERRORDATA OF ERRORCODE =                                              
     1   '       á¹                                         '
    61   '  øØ         ?ñ¹                              Ø        '
   121   '        '                                                    

EVAL errordata :x                                                        
   00000     0000001C 00000000 3F45DA13 000A0800   - .........á¹.....    
   00010     00000000 00000000 00070000 00000000   - ................    
   00020     FF1C0000 00000000 00000000 00000000   - ...............    
   00030     00000001 00200000 00000000 00007080   - ..............øØ    
   00040     00000000 00000000 0F3D6F49 DA001900   - ..........?ñ¹...    
   00050     11000000 00000000 00000000 00000000   - ................    
   00060     00000001 00200000 00000000 00000080   - ...............Ø    
   00070     40404040 40404040 40404040 40404040   -
Scott LewisIBM i Programmer/Administrator


And thank you! There must be a byte out of place somewhere....<sigh>. I have searched high and low regarding SQL7042 and online documentation is weak.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Scott LewisIBM i Programmer/Administrator


The only hint I can think of is the 1C value in the first portion of the hex rendering of the errordata. This translates to decimal 28. According to QSYS/QSQLMSG(CPF7042) field 28 is Obfuscate. The value for this field when calling the api is '0'. According to IBM this is valid. Again, how does errordata tell me which field is in error? I guess that's the big question!

Message ID . . . . . . . . . :   SQL7042      
Message file . . . . . . . . :   QSQLMSG      
  Library  . . . . . . . . . :     QSYS        
    -- Field 28 is the obfuscate option.
IBM i Programmer/Administrator
Success! Attached is my RPGLE source that generates SQL source. The issue was getting the fields to line up in the data structure being passed into the API. The final issue was the last field in the data structure. It need to be one byte only with a hex value of X'00'. This is documented nowhere. I haven't translated so much hex to decimal in years. The message data in QSQLMSG SQL7042 told me what value = what field. Then I started changing variables to see what would change in the errordata.

At any rate...YIPPEE!! I tried the posted code from sites to no avail. I'm thinking this api may change from release to release. I am on 7.2.
Gary PattersonVP Technology / Senior Consultant

Nice work.  Sounds like it was mostly just some learning curve about API error handling.

To get API errors sent as exceptions (that will appear on the job log, etc), you just specify zero bytes provided in the error ds.  If you specify nonzero bytes available, you're saying you want your program to handle the error, and that you don't want an exception signaled - so you'll get nothing on the job log unless you put it there.

For development and debugging, it is nice to see your exceptions in friendly format.  Set bytes available to zero until you get the API calls working.

If you decide to handle you own API errors, but want to see a nicely formatted message, you can still SNDPGMMSG with the MSGID and Error data.  If you want to decode manually, view in hex in debug and look up the format of the message data in the QSQLMSG message file for message ID SQL7042.  

As far as having to add X'00', APIs that take variable length strings generally expect C-style null-terminated strings.  This requirement is certainly well-documented, but I certainly understand how you may have missed it.

Glad you got it working.  IT'll be easier next time.
Scott LewisIBM i Programmer/Administrator


Thanks Gary. This was my first api from scratch. I guess we never quit learning!
Gary PattersonVP Technology / Senior Consultant

There is a redbook on APIs in RPG - you might want to check it out if you plan on doing more.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial