RE: Assistance With Embedded SQL

:variableName

Has never worked for prepared statements.

You can use parameter markers ( the ? symbol ) in prepared statements.

So you could use:

QQW = 'Where GRGDPD Between ? And ? ' + // Days Past Due In Range
' And GRGAID = ? '; // For This Agency

Prepare s1 from :qqw;

Execute s1 using :DayN_From, :DayN_To, :Proc_Agency


However, why are you using dynamic SQL anyway? From what you've shown, you don't need it. Static SQL
is preferred since it performs better.

Without using parameter markers, your 2nd version is the only why to handle host variables with
dynamic SQL.

As far as what you're seeing in debug for the value of Proc_Agency, sounds like you've got a RPG
related bug as the use of static/dynamic SQL wouldn't effect that.

Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Robert Munday
Sent: Tuesday, April 01, 2008 1:57 PM
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


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.