Re: Question on embedded SQL in RPG with Update

If you're new to embedded SQL, consider my motto:

"If I'm using a cursor, then I'm probably doing something wrong."

SQL is supposed to be set based, using a cursor should always be a
last resort when a set based method just won't work.

Another golden rule: don't use select *.

HTH,
Charles



On Mon, Mar 23, 2009 at 2:15 PM, Tom Deskevich
<thomas.l.deskevich@xxxxxxxxxxxxx> wrote:

You are right Scott, I can make this much easier.
But it is my first attempt at embedded SQL and I want to understand the
CURSOR method better.

When I do the changes and compile, I get this:

Record  *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ..
  10        C+ FOR UPDATE OF XXXPY70P
5761ST1 V6R1M0 080215            Create SQL ILE RPG Object         PROS
                                            DIAGNOSTIC MESSAGES
MSG ID  SEV  RECORD  TEXT
SQL0206  30      10  Position 23 Column XXXPY70P not in specified table

Looks like it wants a field specified?

I SENT YOU AN EMAIL OFF YOUR WEB PAGE, DISREGARD THAT.

Nice to see though that on your web page you have your priorities correct on
your titles.
Family first.

Tom Deskevich
Infocon Corporation
Phone 814-472-6066
Fax 814-472-5019


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Scott Klement
Sent: Monday, March 23, 2009 10:49 AM
To: RPG programming on the IBM i / System i
Subject: Re: Question on embedded SQL in RPG with Update


Hi Tom,

On your declare cursor/select statement, you need "for update of
XXXPY70P"... for example:

 C/EXEC sql
 C+ DECLARE A CURSOR FOR
 C+ SELECT *
 C+ FROM XXXPY70P
 C+ WHERE SUBSTR(DEDSC1, 68, 3) > '   '
 C+ AND SUBSTR(DEDSC1, 70, 1) <> 'N'
 C+ FOR UPDATE OF XXXPY70P
 C/end-exec

And on the update, you need to use the filename -- NOT the record format
name.  (You never use the record format name in SQL)

 C/exec sql
 C+    update xxxpy70p
 C+ set dedsc1= substr(dedsc1,1,67)
 C+ WHERE CURRENT OF A
 C/end-exec

Though, I wonder if you really need to declare a cursor and all of that.
 Do you need the fields in your RPG logic?  If not, you can just do this:

 C/exec sql
 C+    update xxxpy70p
 C+ set dedsc1= substr(dedsc1,1,67)
 C+ WHERE SUBSTR(DEDSC1, 68, 3) > '   '
 C+ AND SUBSTR(DEDSC1, 70, 1) <> 'N'
 C/end-exec

Good luck


Tom Deskevich wrote:

I am trying to do an update while in a loop with embedded SQL in RPG.

When I enable the update, SQLSTATE returns 24501 and nothing seems to be

put

in the buffer.

When disable the update, it runs fine.

I cannot find any examples of updating using embedded SQL.
I have the file format referenced in the UPDATE. I had the name of the
physical file.
Worked the same. I also tried using the name of the external data

structure,

same results.

Here is my code:

FQsysprt   O    F  120        PRINTER OFLIND(*InOF)
DPRODOCKE       E DS                  EXTNAME(XXXPY70P)
C/EXEC sql
C+ DECLARE A CURSOR FOR
C+ SELECT *
C+ FROM XXXPY70P
C+ WHERE SUBSTR(DEDSC1, 68, 3) > '   '
C+ AND SUBSTR(DEDSC1, 70, 1) <> 'N'
C/end-exec
C/EXEC sql
C+   OPEN A
C/end-exec
C     SQLSTATE      Downe     '02000'
C/EXEC sql
C+   FETCH A INTO :PRODOCKE
C/end-exec
C                   If        sqlstate='02000'
C                   leave
C                   Endif
C/exec sql
C+    update xxxpy70f                              <<< when enabled, no

recs

end up in buffer
C+ set dedsc1= substr(dedsc1,1,67) CONCAT '   '    <<< disabled, prints

all

recs that qualify
C+ WHERE CURRENT OF A
C/end-exec
C**                 except    print
C                   Enddo
C/EXEC sql
C+  CLOSE A
C/end-exec
C                   eval      *inlr=*on
OQsysprt   E            print          2
O                       deccy



Tom Deskevich
Infocon Corporation
Phone 814-472-6066
Fax 814-472-5019

--
This is the RPG programming on the IBM i / System i (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 IBM i / System i (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.