RE: Assistance With Embedded SQL

["Armbruster, Tom" <Tom.Armbruster@xxxxxxxxxxxxxxx>]

You don't need, nor should you use, double quotes in an SQL statement.
Here's how I declare the quote character:

d @Q            c                   const(x'7D')

To Lim's point, it is true that you can not use host variables in
dynamic SQL statements as you do with standard embedded SQL.  When you
prepare a statement, you are really preparing a string.  Every variable
used in the statement must be escaped and set to a character type to get
a value.

For example:

D @Num            S              4S 0 Inz(50)
D @Today          S               D   

@Today = %date();

You need to convert all non-character value to character type, and
encapsulate non-numeric data in quotes.

mySQLString = mySQLString + ' WHERE someNumber = ' + %char(%int(@Num)) +

' AND someDate = ' + @Q + %char(@Today:*ISO) + @Q;

I include the %int BIF to reduce the string size for number types that
include leading zeros.

Tom Armbruster

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Munday
Sent: Tuesday, April 01, 2008 10:57 AM
To: RPG400
Subject: Assistance With Embedded SQL

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

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