Embedded SQL & commitment control

All,

I am trying to do this in sql:

Ffile up e k disk

C  *hival  setgt    file
C          readp    file
C          eval     key = key + 1
C          write(e) file
C          if       %error
C          unlock
C          endif

to get the next key value in a file.

I am having trouble with commitment control.

This is what I have:

C/exec sql
C+ set transaction isolation level read committed
C/end-exec

C/exec sql
C+ declare @cursor cursor for select 
C+ max(key) from file
C/end-exec

C/exec sql
C+ open @cursor
C/end-exec

C/exec sql
C+ fetch next from @cursor into :@key
C/end-exec

C/exec sql
C+ insert into file (key) values (@key)
C/end-exec
C             if       sqlcod <> *zero
C/exec sql
C+ rollback
C/end-exec
C             else
C/exec sql
C+ commit
C/end-exec
C             endif

The file gets locked *SHRRD, which means the select
statement can still occur after another job's select 
and before that job's insert.

Could someone please show where I've gone astray?

Thanks,
Phil


__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - Buy the things you want at great prices.
http://auctions.yahoo.com/
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---