Retrieve SQL Source on IBM i via a batch job

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
Scott LewisIBM i Programmer/AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
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/AdministratorAuthor Commented:
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.

RTVSQLSRC OBJECT(RHOUR) LIBRARY(ERSDS) SRCF(QSQLSRC) SRCLIB(QGPL)  
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.

qsqgnddl2(Template:TemplateLength:TemplateFormat:errorcode);

EVAL errorcode                                                        
BYTESPROVIDED OF ERRORCODE = 128                                      
BYTESAVAILABLE OF ERRORCODE = 20                                      
ERRORMSGID OF ERRORCODE = 'SQL7042'                                    
FILLER OF ERRORCODE = '0'                                              
ERRORDATA OF ERRORCODE =                                              
          ....5...10...15...20...25...30...35...40...45...50...55...60
     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/AdministratorAuthor Commented:
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.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Scott LewisIBM i Programmer/AdministratorAuthor Commented:
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.
Scott LewisIBM i Programmer/AdministratorAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary PattersonVP Technology / Senior Consultant Commented:
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/AdministratorAuthor Commented:
Thanks Gary. This was my first api from scratch. I guess we never quit learning!
Gary PattersonVP Technology / Senior Consultant Commented:
There is a redbook on APIs in RPG - you might want to check it out if you plan on doing more.

https://www.redbooks.ibm.com/redpapers/pdfs/redp4324.pdf
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.