Sybase ODBC - Does UPDATE BLOB Destroy the Cursor in ASA 9?

Author

Does UPDATE BLOB Destroy the Cursor in ASA 9?

Al Koch

2005-04-16, 8:23 pm

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 ASA 9 and do the following:
1) set SQL_PRESERVE_CURSORS
in the Connection
2) set in the Statement
SQL_CONCUR_ROWVER
SQL_CURSOR_KEYSET_DR
IVEN
SQL_BIND_BY_COLUMN
SQL_ATTR_ROW_ARRAY_S
IZE
SQL_ATTR_KEYSET_SIZE
= 0 (that is, keyset size = result set size)

I then build a result set and
1) use SQLSetPos(m_hStmtHan
dle,1,SQL_POSITION,S
QL_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_hStmtHan
dle,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) but when I then
immediately follow this sequence with another
SQLSetPos(m_hStmtHan
dle,1,SQL_POSITION,S
QL_LOCK_N_CHANGE) to again position
to the 1st row, the SQLSetPos() triggers ODBC error HY109 "Invalid Cursor
Position".

If I alter my code so that I do not update the BLOB column the last
SQLSetPos() works with no problem and I can go on to access the row's 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@MyRealBoxREMO
VEALLTHESECHARS.com

Paul

2005-04-18, 11:23 am

Hi Al -

you might get a response by posting the question to the
sybase.public.sqlanywhere.general newsgroup
"Al Koch" <AlKoch@MyRealBox.com> wrote in message
news:426189f9$1@foru
ms-2-dub...
> 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 ASA 9 and do the following:
> 1) set SQL_PRESERVE_CURSORS
in the Connection
> 2) set in the Statement
> SQL_CONCUR_ROWVER
> SQL_CURSOR_KEYSET_DR
IVEN
> SQL_BIND_BY_COLUMN
> SQL_ATTR_ROW_ARRAY_S
IZE
> SQL_ATTR_KEYSET_SIZE
= 0 (that is, keyset size = result set size)
>
> I then build a result set and
> 1) use SQLSetPos(m_hStmtHan
dle,1,SQL_POSITION,S
QL_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_hStmtHan
dle,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) but when I then
> immediately follow this sequence with another
> SQLSetPos(m_hStmtHan
dle,1,SQL_POSITION,S
QL_LOCK_N_CHANGE) to again
position
> to the 1st row, the SQLSetPos() triggers ODBC error HY109 "Invalid Cursor
> Position".
>
> If I alter my code so that I do not update the BLOB column the last
> SQLSetPos() works with no problem and I can go on to access the row's
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@MyRealBoxREMO
VEALLTHESECHARS.com
>
>
>

Al Koch

2005-04-18, 1:23 pm

Thanks Paul. I'll try there also since I'm sure not getting any responses
here!

Al
"Paul" <Paul@sybase.com> wrote in message news:4263da5e@forums
-1-dub...
> Hi Al -
>
> you might get a response by posting the question to the
> sybase.public.sqlanywhere.general newsgroup
> "Al Koch" <AlKoch@MyRealBox.com> wrote in message
> news:426189f9$1@foru
ms-2-dub...
> corrupted
> position
> for
Update[color=darkred
]
then[color=darkred]
> position
Cursor[color=darkred
]
> data.
UPDATE[color=darkred
]
cannot[color=darkred
]
of[color=darkred]
> result
>
>

Paul

2005-04-19, 8:23 pm

Yeah - these groups are not the best names - the
sybase.public.connectivity.odbc has been around a long time, and was stuck
under connectivity, the association being that the old driver was CtLib
based.

I may go an talk to the folks managing the groups to see if we can change
some of this - it may or may not happen :->

PV

"Al Koch" <AlKoch@MyRealBox.com> wrote in message
news:4263eea0$1@foru
ms-1-dub...
> Thanks Paul. I'll try there also since I'm sure not getting any responses
> here!
>
> Al
> "Paul" <Paul@sybase.com> wrote in message news:4263da5e@forums
-1-dub...
data[color=darkred]
> Update
> then
> Cursor
> UPDATE
> cannot
> of
>
>

Sponsored Links