RE: Embedded SQL with cursor with delete

Check the rules:

A cursor is deletable if all of the following are true: 
v The outer fullselect identifies only one base table or deletable view.

v The outer fullselect does not include a GROUP BY clause or HAVING
clause. 
v The outer fullselect does not include aggregate functions in the
select list. 
v The outer fullselect does not include a UNION, UNION ALL, EXCEPT, or
INTERSECT operator. 
v The outer fullselect does not include the DISTINCT clause. 
v The select-statement does not contain an ORDER BY clause, or the
SENSITIVE keyword or FOR UPDATE clause is also specified. 
v The select-statement does not include a FOR READ ONLY clause. 
v The select-statement does not include a FETCH FIRST n ROWS ONLY
clause. 
v The result of the outer fullselect does not make use of a temporary
table. 
v The select-statement does not include the SCROLL keyword, or the
SENSITIVE keyword or FOR UPDATE clause is also specified. 
v The select list does not includes a DATALINK column unless a FOR
UPDATE clause is specified.

Notice the sixth one, I think you need to specify SENSITIVE and/or for
update on your declare statement.

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, November 21, 2006 12:46 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Embedded SQL with cursor with delete

Select * From PCWSDET 
         Where DWSNBR = ?
         Order By DWSNBR, DVNDNR, DSLOTX

-- 
Jeff Crosby
Dilgard Frozen Foods, Inc.
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the 
opinion of my
company.  Unless I say so.
 

-----Original Message-----
From: rpg400-l-bounces+jlcrosby=dilgardfoods.com@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces+jlcrosby=dilgardfoods.com@xxxxxxxxxxx
m] On Behalf Of Wilt, Charles
Sent: Tuesday, November 21, 2006 12:41 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Embedded SQL with cursor with delete

What's your select statement look like?

Charles Wilt
--
iSeries Systems Administrator / Developer Mitsubishi Electric 
Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, November 21, 2006 11:58 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: Embedded SQL with cursor with delete

I'm using embedded SQL to process selected records from a file.

Optionally I want to delete some of the records and I'm 

trying to do 

it like
this:

Exec SQL  Delete From PCWSDET Where Current of MyCursor;

It bombs with SQL state 42828 which says "This SQLSTATE 

reports an 

attempt to DELETE WHERE CURRENT OF CURSOR or UPDATE WHERE 

CURRENT OF 

CURSOR on a cursor that is fetching rows using a blocking 

protocol."

The documentation I read says _not_ to specify "For 

Update Of" when 

deleting only.

How do I tell it not to use a blocking cursor?

Thanks.

--
Jeff Crosby
Dilgard Frozen Foods, Inc.
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the 

opinion of 

my company.  Unless I say so.


--
This is the RPG programming on the AS400 / iSeries 

(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 AS400 / iSeries (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 AS400 / iSeries (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.