Strange error when trying to delete a row from MySQL via ODBC

[Richard Marsden]

Here's another strange error I'm getting when using ODBC to access
MySQL. This time ODBC is being more informative, although all the
documentation I've looked at, claims that the function in question
(SQLSetPos) should not return the error!

I have a result set that has been opened for update.
I am trying to delete the current row with a call to SQLSetPos:

rc = SQLSetPos( hStmt, nRow, SQL_DELETE, SQL_LOCK_NO_CHANGE );

This reports the ODBC Error HY106, Native Error 534.
"Fetch type out of range"
(All the MySQL errors I've seen are in the 1000s or 2000s)

nRow is the current row, retrieved by a call to SQLGetStmtAttr:

rc = SQLGetStmtAttr(hStmt, SQL_ATTR_ROW_NUMBER, &nRow, sizeof(nRow), NULL);
The SQL_ATTR_CONCURRENCY attribute has been set to SQL_CONCUR_LOCK.
I can delete a row by simply executing the SQL statement, but for
various reasons it would be very good if I can delete a row from a
result set. Has anyone seen this odd error before? Any clues?
Or perhaps an alternative approach to deleting a row from a result set?

Thanks,

Richard

--
Richard Marsden
Winwaed Software Technology, http://www.winwaed.com
http://www.mapping-tools.com for MapPoint tools and add-ins

[Bill Karwin]

Richard Marsden wrote:

rc = SQLSetPos( hStmt, nRow, SQL_DELETE, SQL_LOCK_NO_CHANGE );

This reports the ODBC Error HY106, Native Error 534.
"Fetch type out of range"

http://dev.mysql.com/doc/refman/5.0/...ror-codes.html
This error code is "Invalid cursor position" according to this doc.

This may indicate that the SetPos operation cannot reach the row number
you specified, for instance because the current query has fewer rows in
its result set, or because you gave an otherwise illegal value for the
row number.

Any chance the SQLGetStmtAttr returns row numbers starting at 1, but
SetPos expects rows numbered starting at 0?

Regards,
Bill K.

[Richard Marsden]

Bill Karwin wrote:

http://dev.mysql.com/doc/refman/5.0/...ror-codes.html
This error code is "Invalid cursor position" according to this doc.

This may indicate that the SetPos operation cannot reach the row number
you specified, for instance because the current query has fewer rows in
its result set, or because you gave an otherwise illegal value for the
row number.

Any chance the SQLGetStmtAttr returns row numbers starting at 1, but
SetPos expects rows numbered starting at 0?

Thanks Bill. I've gone back to the error lists - looks like MySQL have
their errors mis-mapped. I was looking at HY106 which doesn't make
sense, but the URL you posts lists an error which makes much more sense.

I've just gone in with the debugger. I'm requesting a delete of row 2
(correct: I move there in my test code before getting the position) and
the table has six rows. So that is correct - or an out-by-one error
should not cause a failure, anyway!
I am using a SQLUSMALLINT for the row number in my call to SQLSetPos -
so no weird casting errors there.

As I'm fetching the current position and then using it for the delete, I
wondered if MySQL didn't like a delete of the current position. So I've
just tried to move back one row (to row 1) before deleting row 2.
This also fails in the same way.
--
Richard Marsden
Winwaed Software Technology, http://www.winwaed.com
http://www.mapping-tools.com for MapPoint tools and add-ins

[Richard Marsden]

Dmitriy Ivanov found the problem over on the ODBC newsgroup. I was
confusing the row numbers for rowsets and resultsets. I had a rowset of
size 1 (deliberately), but was thinking that I needed to use a result
set row number.
Richard

--
Richard Marsden
Winwaed Software Technology, http://www.winwaed.com
http://www.mapping-tools.com for MapPoint tools and add-ins