Re: ODBC application getting error 01002 on SQLFetch of 11th row

jocave_at_my-deja.com wrote:
>
> In article <3A2584C4.106733D0_at_NOSPAMunify.com>,
> "Mark E. Hansen" <meh_at_NOSPAMunify.com> wrote:
> > I hope someone can help...
> >
> > I have an ODBC application running on Windows/NT 4, sp/4 and have a
> > server running on RedHat Linux version 7. The Oracle release in both
> > cases is 8iR2.
> >
> > When I do a select from a table and include a "for update" in the
 statement,
> > then I get an error from SQLFetch on the 11th row:
> >
> > ORA-01002: fetch out of sequence
> >
> > I tried a commit after the 10th row, with:
> >
> > SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT)
> >
> > but this didn't help.
> >
> > Can anyone tell me what I need to do to prevent this error and get
> > through the entire result set?
>
> By default, ODBC will auto-commit your statements. I'll wager that you
> haven't called SQLSetConnectAttr to turn autocommit off.
>
> The sequence of events that's going on here, and the reason you're
> seeing the error on the 11th row, is as follows-
> - You execute the select ... for update statement
> - The 'for update' clause in your select statement will lock all the
> rows you selected
> - OCI will automatically pre-fetch the first 10 rows (this is the
> default value for Prefetch Count in the ODBC setup dialog)
> - Since the select succeeds, the statement is committed automatically,
> releasing the locks you just took out.
> - Fetching beyond the 10th row will cause the error because the 'for
> update' locks have been lost.

You've hit the nail on the head! Thanks. I verified this by changing the prefetch count to 12 (in the ODBC properties) and now it fails when fetching the 13th row.

My process is very simple: It's just supposed to delete old rows. So I've removed the "for udpate" clause because I use a separate "delete from <table> where rowid = ?" statement rather than in in-place delete from the cursor.

in another thread ("Configure Oracle ODBC to not wait for locks") I'm asking how to prevent my process from hanging if I attempt to delete a row that is currently locked by another process.

I would appreciate any insight on that thread as well.

Thanks for your help!

>
> Justin Cave
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Dec 04 2000 - 17:17:59 CET