× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.