help with debugging / sqltransaction
lazarus / 16-Databases

----------------------------------------------------------------------
erkka · 2011-08-29 19:50

Now this is driving me nuts.

In my main project I'm suddenly unable to set SQLTransaction1.Active
to false. For debugging I tried this:

Code: [Select]  ShowMessage('1. SQLtransaction1
'+booltostr(SQLTransaction1.Active));

  if SQLTransaction1.Active=false then
  begin
    ODBCMy.Connected:=false;
    ODBCMy.DatabaseName:='Myllytys';
    ODBCMy.Connected:=true;

    SQLTransaction1.Active:=true;

    ShowMessage('2. SQLtransaction1
'+booltostr(SQLTransaction1.Active));

    ODBCMyllytys.Connected:=false;
    SQLTransaction1.EndTransaction;
    SQLTransaction1.Active:=false;
    ODBCMyllytys.Connected:=false;
    ShowMessage('3. SQLtransaction1
'+booltostr(SQLTransaction1.Active));
  end;

and what happens?
1. displays false
2. displays true
and
3. displays true

Out of curioisity, I made another project just for testing. It uses
the same database with odbc connection. It has a dbgrid displaying
results of a sql query. And it has a button to toggle
sqltransaction.active. It works just fine, I can set
sqltransaction.active to false and back to true and again to false and
back to true, as many times as I like to.

So, any ideas what might be f**ked up in my main project? Several
times I tried to close everything, restart computer, build all and try
again - no luck.

----------------------------------------------------------------------
Lacak2 · 2011-08-30 05:42

TODBCConnection ATM does not implement proper support for
transactions, so using transactions with TODBCConnection can lead to
strange results.
See: http://bugs.freepascal.org/view.php?id=19902 and
http://bugs.freepascal.org/view.php?id=0014944

----------------------------------------------------------------------
erkka · 2011-08-30 05:58

Oh, I see. Does it mean that
 a) I could try to access my database via TODBCConnection but without
SQLTransaction
 b) I should patch some sources to make the TODBCConnection to work
with transactions

EDIT:

 c) Should I install zeos and use its components instead of
TODBCConnection ?

ps.
  sorry for a stupid question, but I've spent my night sleeping a
little and drinking a lot of coffee...

pps.
  I need to access data in a Microsoft SQL server 2005 database.
ODBCConnection did the trick and worked fine for couple of days, only
then I ran into these mysterious problems with closing the
transaction.

----------------------------------------------------------------------
Lacak2 · 2011-08-30 10:36

Quote from: erkka on August 30, 2011, 07:58:06 am a) I could try to
access my database via TODBCConnection but without SQLTransaction
I think, that you must put TSQLTransaction on form (or datamodule) and
connect it to TODBCConnection,
but because ATM ODBCConnection works only in auto-commit mode (every
statement is commited), you do not need use Commit, Rollback and all
other methods of TSQLTransaction

Quote b) I should patch some sources to make the TODBCConnection to
work with transactions
Yes if you apply patch, then you can start using transaction like
usual

Quote c) Should I install zeos and use its components instead of
TODBCConnection ?
Yes, you can use Zeos, but I have noe experience with them

Quote  I need to access data in a Microsoft SQL server 2005 database.
ODBCConnection did the trick and worked fine for couple of days, only
then I ran into these mysterious problems with closing the
transaction.
Omit ending transaction, commiting and all will work ... but if you
must wrap into transaction control more than one sql statement, then
you must apply patch for adding transaction support to ODBCConnection
and explicitly starttransaction and commit when you want

----------------------------------------------------------------------
erkka · 2011-08-30 16:11

Lacak2, thank you very much for your detailed and helpful reply!

c) I installed Zeos and managed to set up a little test project. It
looks promising, so maybe I'll study more of it.

b) Ah, you found my another question concerning patching, great! I'll
reply on that there.

a) As simple as that? Really?
  My application is simple, and uses four different sql-queries,
  each made up of one sql statement.
  So I left the SQLTransaction1 on my form, but didn't touch it in the
code,
  never setting its .Active -property to true.

  And it works!

time for a beer, I guess