Recent

Author Topic: [SOLVED] Lazarus + MS Access  (Read 13274 times)

mmxngg

  • New Member
  • *
  • Posts: 29
[SOLVED] Lazarus + MS Access
« on: December 04, 2012, 08:32:33 am »
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?
« Last Edit: December 04, 2012, 10:21:54 pm by mmxngg »
Now i'm on Win7/32bits with Lazarus 1.0.4 FPC 2.6.0

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Lazarus + MS Access
« Reply #1 on: December 04, 2012, 08:37:43 am »
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?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

mmxngg

  • New Member
  • *
  • Posts: 29
Re: Lazarus + MS Access
« Reply #2 on: December 04, 2012, 08:52:55 am »
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.
Now i'm on Win7/32bits with Lazarus 1.0.4 FPC 2.6.0

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Lazarus + MS Access
« Reply #3 on: December 04, 2012, 09:03:12 am »
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)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Lazarus + MS Access
« Reply #4 on: December 04, 2012, 09:11:22 am »
If you're on
Ok, 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)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Lazarus + MS Access
« Reply #5 on: December 04, 2012, 09:24:26 am »
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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

mmxngg

  • New Member
  • *
  • Posts: 29
Re: Lazarus + MS Access
« Reply #6 on: December 04, 2012, 09:35:20 am »
I have not tried the last example because the management for rollback is missing in ODBCCONN unit :(

Need patch FPC...

Thank you ;)
Now i'm on Win7/32bits with Lazarus 1.0.4 FPC 2.6.0

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Lazarus + MS Access
« Reply #7 on: December 04, 2012, 09:59:28 am »
I 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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Lazarus + MS Access
« Reply #8 on: December 04, 2012, 10:08:28 am »
because 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...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

mmxngg

  • New Member
  • *
  • Posts: 29
Re: Lazarus + MS Access
« Reply #9 on: December 04, 2012, 10:33:24 am »
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  :)
« Last Edit: December 04, 2012, 10:39:15 am by mmxngg »
Now i'm on Win7/32bits with Lazarus 1.0.4 FPC 2.6.0

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Lazarus + MS Access
« Reply #10 on: December 04, 2012, 10:49:37 am »
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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Lazarus + MS Access
« Reply #11 on: December 04, 2012, 07:30:14 pm »
@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
« Last Edit: December 04, 2012, 09:52:44 pm by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

mmxngg

  • New Member
  • *
  • Posts: 29
Re: Lazarus + MS Access
« Reply #12 on: December 04, 2012, 10:21:38 pm »
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.
Now i'm on Win7/32bits with Lazarus 1.0.4 FPC 2.6.0

asdf

  • Sr. Member
  • ****
  • Posts: 310
Re: Lazarus + MS Access
« Reply #13 on: December 04, 2012, 11:19:45 pm »
Code: [Select]
  DBTran.Rollback;

This looks important, what is the use of it ?
Lazarus 1.2.4 / Win 32 / THAILAND

mmxngg

  • New Member
  • *
  • Posts: 29
Re: [SOLVED] Lazarus + MS Access
« Reply #14 on: December 05, 2012, 12:25:17 am »
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.
Now i'm on Win7/32bits with Lazarus 1.0.4 FPC 2.6.0