Assistance With Embedded SQL

[Robert Munday <rwmunday@xxxxxxxxxxxxx>]

Also posted to the RPG List (they must all be at COMMON this week):


Have you ever had one of those days?  That which worked swell in the past no longer does.

I'm creating a PREPARE statement in my RPG program for an embedded SQL statement.  I have a quotation mark field defined as:

D @Q       S        4A   Inz('''')         Quotation Marks

I am creating a statement which contains several variable fields.  I have had to resort to breaking the statements into segments and inserting character variables.  I originally tried:

// Set Where For Records Having Days Past Due In Range For The Selected Agency            
QQW = 'Where GRGDPD Between :DayN_From And :DayN_To ' +   // Days Past Due In Range    
 ' And GRGAID = :Proc_Agency ';                           // For This Agency   

The compiler could not find Proc_Agency or the Day values, so I broke them into segments:

QQW = 'Where GRGDPD Between ' + Days_From + ' And ' + Days_To +  // Days Past Due In Range
 ' And GRGAID = ' + @Q + Proc_Agency + @Q;                       // For This Agency

The value I have for Agency, 'XXXX, is shown in debug as '   XXXX  ' with lots of spaces between the quote mark and the field value.  In another example,       

QQW = 'Where (BHBHST = ' + @Q + History_Code_Manual + @Q +  // Select Manual History Code 
 ' Or BHBHST = ' + @Q + History_Code_Special + @Q + ')' +   // Select Special History Code
 ' And BHBTDT >= ' + %Char(Last_Run_7) +              // Manual And Special Since Last Run
 ' And BHBTDT < ' + %Char(Today_7) ;                                                      

The history code fields are 3A, but show in debug as '    123  '.

What will I need to do to tighten up these values and make my prepare statement function?  I don't know if the client's system is causing this as I have been doing prepare statements for a few years and haven't had this much difficulty getting my RPG embedded SQL programs to run.  At what point did :FIELDNAME stop working for me?

Thanks for your help.



Robert Munday
Munday Software Consultants
Montgomery, AL
on assignment in Columbia, SC