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

From: <jocave_at_my-deja.com>
Date: Sat, 02 Dec 2000 01:39:17 GMT
Message-ID: <909js5$98q$1_at_nnrp1.deja.com>


[Quoted] In article <3A2584C4.106733D0_at_NOSPAMunify.com>, [Quoted]   "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?

[Quoted] 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.

Justin Cave

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Dec 02 2000 - 02:39:17 CET

Original text of this message