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

Ok, I think it is a good idea to use SQL, because it is seems to be the
standard out there.
I have to discipline myself to realize it will take a little longer, but be
a big payoff in the long run.
I sometimes look at the employment web sites to find out what skills
employers are looking for with RPG programmers.
SQL was almost ALWAYS there.

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 rob@xxxxxxxxx
Sent: Wednesday, March 25, 2009 10:53 AM
To: RPG programming on the IBM i / System i
Subject: RE: Question on embedded SQL in RPG with Update

I never use a new OPNQRYF. I think that still goes to the old CQE instead
of the more efficient SQE - but that keeps changing with release levels.
More imbedding than stored procedures. But I use cursors a lot. :-)

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From:
"Tom Deskevich" <thomas.l.deskevich@xxxxxxxxxxxxx>
To:
"RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
Date:
03/23/2009 01:49 PM
Subject:
RE: Question on embedded SQL in RPG with Update
Sent by:
rpg400-l-bounces@xxxxxxxxxxxx

ok, sounds like I have successfully got a highly inefficient embedded SQL
program to work.
thanks for the tips.

Are you still using OPNQRY at all or are you SQL exclusively?

Do you embed or do stored procedures?

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 Charles Wilt
Sent: Monday, March 23, 2009 12:30 PM
To: RPG programming on the IBM i / System i
Subject: 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

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.

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