[SOLVED] Lazarus + MS Access
lazarus / 16-Databases

----------------------------------------------------------------------
mmxngg · 2012-12-04 07:32

This is my first try with Lazarus + MS Access (2010) and...it's a
fail 

I tried a simple insert with a rollback in this way :

Code: [Select]  DBConn := TODBCConnection.Create(nil);
  DBTran := TSQLTransaction.Create(nil);
  DBQuery := TSQLQuery.Create(nil);
  DBConn.Driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
  DBConn.Params.Add('DBQ='E:\TEST\TEST.accdb');
  DBConn.Params.Add('Safe Transactions=1');
  DBConn.Transaction := DBTran;
  DBConn.KeepConnection := True;
  DBConn.Open;
  DBQuery.UsePrimaryKeyAsKey := False;
  DBQuery.PacketRecords:= -1;
  DBQuery.DataBase := DBConn;
  DBQuery.SQL.Text := 'INSERT INTO TEST (ID, DESCR) VALUES(3, ''TEST
3'')';
  DBQuery.ExecSQL;
  DBTran.Rollback;
The record is inserted always...what's wrong?

----------------------------------------------------------------------
BigChimp · 2012-12-04 07:37

Just some thoughts:
1. What Lazarus+FPC version? See signature
2. Try inserting an explict DBTran.StartTransaction before your
DBQuery.ExecSQL; I think your query may be using an implicit
transaction
3. What does the DBConn.Params.Add('Safe Transactions=1'); do?

----------------------------------------------------------------------
mmxngg · 2012-12-04 07:52

1. Yeah, need update my signature. Now i use Lazarus 1.0.4 + FPC 2.6.0
2. Tried now, didn't work
3. From MSDN

Code: [Select]Safe Transactions field, choose one of the following
options.

Type 0 to commit all transactions immediately. The default value is 0.

Type 1 to save all transactions to disk only upon a commit operation.
This degrades the performance slightly.

----------------------------------------------------------------------
BigChimp · 2012-12-04 08:03

Ok, perhaps go through the bug tracker to see if there are problems
with sqldb odbc & transactions. See also
http://wiki.lazarus.freepascal.org/ODBCConn#TODBCConnection

Perhaps some of the others can shed some light on this.

As for the Safe Transactions thing - strange: option 0 seems to say to
just ignore transactions!?? (Or would a rollback still be processed
because some kind of transaction journal is kept)

----------------------------------------------------------------------
BigChimp · 2012-12-04 08:11

If you're on Quote from: BigChimp on December 04, 2012, 09:03:12 amOk,
perhaps go through the bug tracker to see if there are problems with
sqldb odbc & transactions.
This one:
http://bugs.freepascal.org/view.php?id=14944
seems interesting. It's been fixed in current FPC trunk (2.7.1)

----------------------------------------------------------------------
BigChimp · 2012-12-04 08:24

Just tried this:
Code: [Select]program accesstran;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, sysutils,
  sqldb,
  odbcconn;

{
* ADD THE SQL NEEDED TO CREATE THE DATABASE HERE *
create database test.mdb
table test,
id integer
descr varchar(50)
}

var
  Conn: TODBCConnection;
  Tran: TSQLTransaction;
  Q: TSQLQuery;
  CurDir: string;
begin
  Conn:=TODBCConnection.create(nil);
  Tran:=TSQLTransaction.create(nil);
  Q:=TSQLQuery.Create(nil);
  try
    conn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
    //Conn.HostName:='';
    //Conn.UserName:='invalidusername';
    //Conn.Password:='invalidpassword';
    //Conn.DatabaseName:='invaliddatabase';
    CurDir:=IncludeTrailingPathDelimiter(GetCurrentDir);
    conn.Params.Add('DBQ='+CurDir+'test.mdb');
    conn.Params.Add('Safe Transactions=1');
    Conn.Transaction:=Tran;
    Tran.StartTransaction;
    Q.DataBase:=Conn;
    Q.UsePrimaryKeyAsKey := False;
    Q.PacketRecords:= -1;
    Conn.Open;
    Q.SQL.Text:='INSERT INTO TEST (ID, DESCR) VALUES(3, ''This should
be rolled back'')';
    Q.ExecSQL;
    writeln('done inserting');
    Tran.RollBack;
    writeln('done rollback');
    Q.Close;
    Conn.Close;
  finally
    Q.Free;
    Tran.Free;
    Conn.Free;
  end;
  writeln('Program complete. Press a key to continue.');
  readln;
end.
on FPC 2.7.1: the rollback unfortunately has no effect either

----------------------------------------------------------------------
mmxngg · 2012-12-04 08:35

I have not tried the last example because the management for rollback
is missing in ODBCCONN unit

Need patch FPC...

Thank you

----------------------------------------------------------------------
BigChimp · 2012-12-04 08:59

Quote from: mmxngg on December 04, 2012, 09:35:20 amI have not tried
the last example because the management for rollback is missing in
ODBCCONN unit

Need patch FPC...
If you have a patch, please feel free to attach it to the bug I just
created:
http://bugs.freepascal.org/view.php?id=23429

Thanks.

----------------------------------------------------------------------
BigChimp · 2012-12-04 09:08

Quote from: mmxngg on December 04, 2012, 09:35:20 ambecause the
management for rollback is missing in ODBCCONN unit

I don't understand that one:
this:
Code: [Select]function TODBCConnection.Rollback(trans: TSQLHandle):
boolean;
begin
  ODBCCheckResult(
    SQLEndTran(SQL_HANDLE_DBC, FDBCHandle, SQL_ROLLBACK),
    SQL_HANDLE_DBC, FDBCHandle, 'Could not rollback!'
  );
  Result := True;
end;
is there in FPC trunk. (I'm assuming SQLEndtran is called with a
message 'Could not rollback' in case it goes wrong - but I'm not an
ODBC guru - know nothing about ODBC SQLEndtran
Do you expect more? If so please indicate so I can start looking at
the code...

----------------------------------------------------------------------
mmxngg · 2012-12-04 09:33

I have FPC 2.6.0 and in this version ODBCCONN unit dosen't have any
patch. I have applied the one posted here
http://bugs.freepascal.org/view.php?id=14944 and added
DBConn.Params.Add('AUTOCOMMIT=0') to my code but for now nothing
happen...and yes, MS Access support transactions

----------------------------------------------------------------------
BigChimp · 2012-12-04 09:49

Yep, I know Access supports transactions

Just tried FPC trunk against a Firebird server over ODBC and I'm
seeing the same problem so its' definitely the SQLDB ODBC connection
that's a problem here.

----------------------------------------------------------------------
BigChimp · 2012-12-04 18:30

@mmxngg: I think I have the solution. It works for me on my Firebird
and Access test programs with FPC trunk. Could you try adding this
line (see below):

Edit: this won't work on FPC 2.6.0, but should work on 2.6.2RC1 (and
trunk/2.7.1)

Code: [Select]  DBConn := TODBCConnection.Create(nil);
  DBTran := TSQLTransaction.Create(nil);
  DBQuery := TSQLQuery.Create(nil);
  DBConn.Driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
  DBConn.Params.Add('DBQ='E:\TEST\TEST.accdb');
  DBConn.Params.Add('Safe Transactions=1');
  // ********** insert line below
  // Tell SQLDB ODBC driver autocommit should be off (on by default):
  DBConn.Params.Add('AUTOCOMMIT=0');
  DBConn.Transaction := DBTran;
  DBConn.KeepConnection := True;
  DBConn.Open;
  DBQuery.UsePrimaryKeyAsKey := False;
  DBQuery.PacketRecords:= -1;
  DBQuery.DataBase := DBConn;
  DBQuery.SQL.Text := 'INSERT INTO TEST (ID, DESCR) VALUES(3, ''TEST
3'')';
  //******************************************* perhaps you need this
too
  DBTran.StartTransaction;
  DBQuery.ExecSQL;
  DBTran.Rollback;

Updated the wiki at
http://wiki.lazarus.freepascal.org/ODBCConn#Rollback_does_not_work
Thanks,
BigChimp

----------------------------------------------------------------------
mmxngg · 2012-12-04 21:21

Ok it works. I made some mistakes updating ODBCCONN unit (I did not
copy the new compiled version in the right directory). Then i set
AUTOCOMMIT = 0 in the transaction and not the connection (it seemed
like the right place) and did not work. Putting back the parameter in
connection with the correct  compilation everything works correctly.
Thank you.

P.S.

I think that this issue can be closed.

----------------------------------------------------------------------
asdf · 2012-12-04 22:19

Quote from: BigChimp on December 04, 2012, 07:30:14 pmCode: [Select] 
DBTran.Rollback;

This looks important, what is the use of it ?

----------------------------------------------------------------------
mmxngg · 2012-12-04 23:25

Cancel all changes to data not yet confirmed (with a commit). Useful
when working with large amounts of data and something goes wrong in
the middle.

----------------------------------------------------------------------
BigChimp · 2012-12-05 08:42

Quote from: mmxngg on December 04, 2012, 10:21:38 pmThen i set
AUTOCOMMIT = 0 in the transaction and not the connection (it seemed
like the right place) and did not work. Putting back the parameter in
connection with the correct  compilation everything works correctly.
Thank you.

P.S.

I think that this issue can be closed.
Glad it works.
1. AUTOCOMMIT sets the ODBC driver's AUTOCOMMIT mode using
SQL_ATTR_AUTOCOMMIT: http://msdn.microsoft.com/en-
us/library/windows/desktop/ms713600%28v=vs.85%29.aspx. So it applies
to connections/drivers not to transactions. This fits in with the
SQLDB concept that AFAIR allows multiple transaction objects per
connection (though not all connectors support/honour this).
2. Yes, I know about the bug report  Just wanted to make sure first
the fix is backported to 2.6.2 if possible so we don't get divergent
behaviour.