AW: Embedded SQL in RPGLE pgm

RPG Built-in-Functions cannot be used in a embedded SQL statement.
But SQL has its own scalar functions:
TRIM  = %TRIM() or %TRIMR or %TRIML
RTRIM = %TRIMR()
LTRIM = %TRIML()
STRIP works like TRIM

If CtrName, AppName, DateName are fields in your file WPUSCHEDLE and
RtnField and inPickUpDate are host variables,
you also can use static SQL.

c/EXEX SQL
C+ UPDATE PUBWRK/WPUSCHEDLE
C+    SET CtrName = :retField,
C+        AppName = :retField
C+  WHERE DateName = :inPickupDate
C/End-Exec

If CtrName, AppName and DateName are variables that hold field names, you
have to use dynamic SQL.
That means you have to built the complete SQL-statement as string, convert
it to an executable SQL statement and run it.
You eihter can use the SQL statements PREPARE and EXECUTE or EXECUTE
IMMEDIATE that combines PREPARE and EXECUTE.

  MySQLString = 'UPDATE PUBWRK/WPUSCHEDLE ' +
                '   SET ' + %Trim(ctrName)  + ' = ' + %Char(retField) + ', '
                          + %Trim(appName)  + ' = ' + %Char(retField) +
                ' WHERE ' + %Trim(dateName) + ' = ' + %Char(inPickupDate) '
 /End-Free
C/EXEC SQL  Execute Immediate :MySQLString
C/END-EXEC

Be aware, if rtnField or inPickupDate are character dates, you have to
embedd them into quotes,
(i.e. = ''' + %Char(RetField) + ''', ')

Birgitta


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Lim Hock-Chai
Gesendet: Donnerstag, 4. August 2005 17:16
An: RPG programming on the AS400 / iSeries
Betreff: RE: Embedded SQL in RPGLE pgm


I don't think you can use rpg bif (%trim, %char) in sql.

The best way to verify embedded sql codes is to try it in interactive sql.
Just substitute all the rpg variables with the value you want to test with.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of RPower@xxxxxxxxxx
Sent: Thursday, August 04, 2005 10:10 AM
To: RPG programming on the AS400 / iSeries
Subject: Embedded SQL in RPGLE pgm


Ok,
I've searched the archives but can't find (or don't know what I'm looking
for) any examples of an UPDATE statement using field names that I want to
build.  I know how to do it using a plain SELECT statement and creating a
literal statement and then running it.  But what I'm trying to do this
time is something like:
     C/Exec Sql
     C+ UPDATE PUBWRK/WPUSCHEDLE
     C+ SET %Trim(ctrName) = %Char(retField),
     C+ %Trim(appName) = %Char(retField)
     C+ WHERE %Trim(dateName) = %Char(inPickupDate)
     C/End-Exec

but I don't think I'm doing this right at all am I?
Frustrated beyond all belief today...

Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
709-576-8132
rpower@xxxxxxxxxx
http://www.stjohns.ca/
___________________________________________________________________________
Success is going from failure to failure without a loss of enthusiasm. -
Sir Winston Churchill
--
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.