Does UPDATE BLOB Destroy the Cursor in SQL Server 2000?

Hello,

Does updating a BLOB column cause a result set/cursor to be
"lost/destroyed"? I don't meant that the data in the database is corrupted
but rather that the result set itself is lost.

I am using SS 2000 and do the following:
1) set SQL_PRESERVE_CURSORS in the Connection
2) set in the Statement
SQL_CONCUR_ROWVER
SQL_CURSOR_KEYSET_DRIVEN
SQL_BIND_BY_COLUMN
SQL_ATTR_ROW_ARRAY_SIZE
SQL_ATTR_KEYSET_SIZE = 0 (that is, keyset size = result set size)

I then build a result set and
1) use SQLSetPos(m_hStmtHandle,1,SQL_POSITION,SQL_LOCK_N_CHANGE) to position
to the 1st row of the block so that I can access some of the row's data for
display
2) use SQLSetPos(m_hStmtHandle,1,SQL_UPDATE,SQL_LOCK_NO_CHANGE) to Update
row 1, followed by
3) use SQLPutData() to put the BLOB column out in blocks
4) use Commit to commit the data

The data is updated successfully (including the BLOB data). It appears that
all is well at this point because if I immediately follow this sequence with
another SQLSetPos(m_hStmtHandle,1,SQL_POSITION,SQL_LOCK_N_CHANGE) to again
position to the 1st row, this SQLSetPos() does not trigger an error.

However, if I then use SQLGetData() to try and fetch data from the BLOB
column, SQLGetData() triggers ODBC error 07009 "Invalid Descriptor Index".

If I alter my code so that I do not update the BLOB column I can go on to
access the row's non BLOB data.

Normally, with the Connection and Statement settings I am using, an UPDATE
(and Commit) does not affect the result set and cursor. However, I cannot
find any documentation stating what happens when a BLOB column is part of
the UPDATE. My code suggests that updating a BLOB column causes the result
set to be lost but I'd like to confirm that this is by design.

Can anyone comment on this?

Thanks
Al
AlKoch@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



.

Relevant Pages

  • UPDATE BLOB Destroys the Cursor in SQL Server 2000!!!
    ... Does updating a BLOB column cause a result set/cursor to be ... use Commit to commit the data ... if I then use SQLGetData() to try and fetch data from the BLOB ... My code suggests that updating a BLOB column causes the result ...
    (microsoft.public.sqlserver.odbc)
  • Does UPDATE BLOB Destroy the Cursor in SQL Server 2000?
    ... Does updating a BLOB column cause a result set/cursor to be ... use Commit to commit the data ... if I then use SQLGetData() to try and fetch data from the BLOB ... My code suggests that updating a BLOB column causes the result ...
    (microsoft.public.sqlserver.odbc)
Loading