Hello,

I came across this very strange behaviour on Sybase 12.5, ODBC drivers
4.10.
Say you have an active statement, if you start/commit a transaction,
the active statement will stop working. Consider the following:

rec = open_rec("SELECT * FROM a_table")
While Not rec.EOF
Call BeginTran
'Nothing here
Call CommitTran
Call rec.MoveNext
End While

The call to rec.MoveNext *will* crash with "odbc call failed"

This happens only in ODBC driver setting SELECT mode = Direct (*not*
SELECT mode = cursors).

This is very strange, why would starting/commiting a transaction
affect the active statement? If this is the case, the ODBC driver
should create a connection on its own.(This problem does not occur in
oracle. )

Does this mean that I have to create a seperate connection on my own
to manage transactions?

Thanks for your help and suggections,

Regards,

Serge

On Thu, 05 Aug 2004 21:47:31 -0700, TomTom wrote:

I don't know ODBC, or VB (assuming that's the language you use).

However - why would you want to start a transaction in a loop where you
fetch rows from the server?
If the idea is to use a different connection to make changes to the
database using data from the original SELECT then you should probably try
to explicitly use a different connection, as Sybase doesn't support
multiple command streams on the same connection (*except* when using
client-side cursors)

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

Thanks for your answer Michael,

So I understand now I should use a separate connection...

But why I do not get is why the previous excerpt does not work while
this one poses no problem:

rec = open_rec("SELECT * FROM a_table")
While Not rec.EOF
Call db.Execute("UPDATE another_table SET a_column = a_value")
Call rec.MoveNext
End While

Any clue?

Regards,

Serge

Michael Peppler <mpeppler (AT) peppler (DOT) org> wrote

Anyone on this question?
This must be something everybody comes accross one day or another...

Thanks,

Serge

"TomTom" <sergiogiogio (AT) yahoo (DOT) fr> a écrit dans le message de
news:d5c8958d.0408090148.b6fa9f3 (AT) posting (DOT) google.com...