× 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.



Hi,

I'd not use the hex value x'7D' for quotes, because it may not be
international.
Using two single quotes is the better solution.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Armbruster, Tom
Gesendet: Wednesday, April 02, 2008 00:24
An: RPG programming on the AS400 / iSeries
Betreff: RE: Assistance With Embedded SQL


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



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.