SQL Anywhere database - Does UPDATE BLOB Destroy the Cursor in ASA 9?

Author

Does UPDATE BLOB Destroy the Cursor in ASA 9?

Al Koch

2005-04-18, 1: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 with ODBC and I 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

Breck Carter [TeamSybase]

2005-04-19, 7:23 am

I do not know if ASA honors SQL_PRESERVE_CURSORS
, I can't find any
documentation on the subject.

Can you try using the WITH HOLD option on the cursor OPEN, or setting
this option on the connection?

SET TEMPORARY OPTION CLOSE_ON_ENDTRANS = 'OFF';

Breck

On 18 Apr 2005 10:31:37 -0700, "Al Koch" <AlKoch@MyRealBox.com> wrote:

>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 with ODBC and I 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
>
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Al Koch

2005-04-22, 8:23 pm

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:tqk961dtsbqjgcv
i3o3ie2up41dtoi97j1@
4ax.com...
> I do not know if ASA honors SQL_PRESERVE_CURSORS
, I can't find any
> documentation on the subject.
>
> Can you try using the WITH HOLD option on the cursor OPEN, or setting
> this option on the connection?
>
> SET TEMPORARY OPTION CLOSE_ON_ENDTRANS = 'OFF';
>
> Breck
>
Hi Breck,

Thanks for replying; I can't seem to get anyone's attention on this one!
Also, sorry for the delay in responding but I am traveling.

Just to be clear about the problem, you said "I do not know if ASA honors
SQL_PRESERVE_CURSORS
". It ceratinly appears that it does (the cursor is
preserved after an UPDATE) unless a BLOB column is involved. I only lose
the cursor when a BLOB column is part of the UPDATE.

I tried your suggestion of setting CLOSE_ON_ENDTRANS = 'OFF' on the
connection but this does not help; the error still occurs. I'm at a loss
because I too am unable to find any documentation on this. I do not have a
Sybase support contract. By any chance do you know how I might be able get
te attention of someone at Sybase without such a contract? This appears to
either be a bug or an obscure "design feature" but either way it ought to be
easier to determine which!

Thanks again for your help,
Al

Breck Carter [TeamSybase]

2005-04-25, 7:23 am

This forum is operated by volunteers; the reason you don't have any
answer yet is probably that (a) nobody reading your message has had
any problems with cursors and blobs, and (b) you haven't provide a
reproducible that's easy to run... if you do that, in a new thread
(not a reply to this message), maybe you'll have more luck.

Breck

On 22 Apr 2005 14:20:43 -0700, "Al Koch" <AlKoch@MyRealBox.com> wrote:

>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:tqk961dtsbqjgcv
i3o3ie2up41dtoi97j1@
4ax.com...
>Hi Breck,
>
>Thanks for replying; I can't seem to get anyone's attention on this one!
>Also, sorry for the delay in responding but I am traveling.
>
>Just to be clear about the problem, you said "I do not know if ASA honors
> SQL_PRESERVE_CURSORS
". It ceratinly appears that it does (the cursor is
>preserved after an UPDATE) unless a BLOB column is involved. I only lose
>the cursor when a BLOB column is part of the UPDATE.
>
>I tried your suggestion of setting CLOSE_ON_ENDTRANS = 'OFF' on the
>connection but this does not help; the error still occurs. I'm at a loss
>because I too am unable to find any documentation on this. I do not have a
>Sybase support contract. By any chance do you know how I might be able get
>te attention of someone at Sybase without such a contract? This appears to
>either be a bug or an obscure "design feature" but either way it ought to be
>easier to determine which!
>
>Thanks again for your help,
>Al
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Greg Fenton

2005-04-25, 11:23 am

Al Koch wrote:
> By any chance do you know how I might be able get
> te attention of someone at Sybase without such a contract? This appears to
> either be a bug or an obscure "design feature" but either way it ought to be
> easier to determine which!
>

You can submit a bug report for free using CaseXpress (
http://casexpress.sybase.com/ ). However, note that bugs submitted via
this tool are usually given a lower priority than those coming through
regular support channels.

If you choose to use CaseXpress, make sure that what you supply is a
*simple*, and *well defined* reproducible case. See:

http://www.ianywhere.com/support/how_to_use.html

An alternative is to open a "credit card" support case by contacting
support directly:

http://www.ianywhere.com/support/co...ct_support.html

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links