Re: Question on embedded SQL in RPG with Update -- RPG400-L

David,

An embedded SQL cursor would be a direct replacement for an OPNQRYF.

If you're asking how to replace OPNQRYF without using a cursor, then
the answer is it depends.

You'd need to look at the application to determine if what is being
done can be done with a single statement.

Charles

On Tue, Mar 24, 2009 at 5:50 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx> wrote:

Charles,

How should a CLP with OPNQRYF select that calls an RPG that processes the file be replaced correctly?

I just wrote the RPG without the OPNQRYF, using a cursor in the RPG.

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Charles Wilt
Envoyé : lundi 23 mars 2009 18:30
À : RPG programming on the IBM i / System i
Objet : 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.

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