RE: Assistance With Embedded SQL

Good catch Lim!

I didn't even see that @Q was defined as 4A.

Robert, you should never define a constant like that. Instead, define @Q as

D @Q c ''''

Actually, consider naming it something besides @Q. Best practice is to not use @,#,$ and other
symbols in your source. That way it translates ok in other languages.

Maybe you won't ever send it overseas, but at the very least, you might share code with international
users on this list.

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 Lim Hock-Chai
Sent: Tuesday, April 01, 2008 2:28 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Assistance With Embedded SQL

First problem: You cannot use host variable in Dynamic SQL.
Second problem: Probably cause by the @Q is define as 4A.

I would recommend using static sql. You can use host variable and it is
much easier to read and debug.

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