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



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



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






As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.