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






As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.