Issue with TSQLTransaction

[saikatsakura]

Hi,

I am trying to connec to an MS Access Database from Lazarus using the SqlDB components provided. I am using a dbgrid, a TODBCConnection, a TSQLQuery and a TSqlTransaction with the project. I am setting the Driver property of SqlConnection to "Microsoft Access Driver (*.mdb)" and in the Params option I am passing "DBQ=C:\test.mdb". Whenever I am trying to set the Active proeprty of the SqlTransaction object to "true", it is reverting back to "false". I have connected the diff. components together properly.

Any suggestions???

[Daddel57]

Hi,
i'm a teacher an try to change from Delphi to Lazarus. I have the same problem. I have to use a StartTransaction - Commit / Rollback block during update of some tables. Who can help?

Thanks for the help and advice!

[clauslack]

I use TSqlTransaction with Firebird SQL.
I am not sure, but MS Access not support transactions.

[Daddel57]

For me, it is not possible to change the database. In my course, we only change the data in the database. I yust can migrate from Delphi to Lazarus. Rollback is possible in Delphi together with MS Access. If rollback is not possible in case of a MS Access database together with Lazazarus, i can't use Lazarus.

[Loesje]

Do you use the ODBC driver on your Delphi projects?

[Daddel57]

No, i don't use ODBC in Delphi. In Delphi i use the BDE engine.

[LacaK]

TODBCConnection does not support transactions for now!

But it can be implemented using ODBC API SQLEndTran function.

[itsols]

LacaK, if TODBCConection doesn't support transactions, could you please let me know:

1. From where you found this info
2. Does it mean that the visual components like TSQLTransaction cannot be used with Access?
3. Does it mean that Access cannot be used with Lazarus even by writing the code (instead of using the visual components)?

Thank you for your time!

[Lacak2]

1. From where you found this info

If you look into unit odbcconn.pp, which implements TODBCConnection class, there you will find, that methods like
TODBCConnection.Commit or TODBCConnection.Rollback are not implemented ATM. So all statements work in autocommit mode.
I posted patch, which adds this functionality http://bugs.freepascal.org/view.php?id=14944

2. Does it mean that the visual components like TSQLTransaction cannot be used with Access?

I have no experience with Access, but I think, that you can use it, but transactions will be ignored

3. Does it mean that Access cannot be used with Lazarus even by writing the code (instead of using the visual components)?

IMO You can use Access with Lazarus, only transaction which wrap multiple sql statements will not work as expected by transcations control, but they will be commited after each single statement.

[itsols]

Thank you LacaK2!

I really appreciate your inputs. I've been searching for proper (complete) docs on Lazarus but only find scattered work. I shall try to contribute wherever I can. For now, I need to use Access with Lazarus. But it seems like I cannot set the Active property of the transaction. and without doing this, I can't seem to set the tSQLQuery. Is there a link you can show me that uses pure code instead of the graphical components to connect and read the DB?

While I appreciate your ideas a lot, I also want you to know that quite often I find the Lazarus wiki unaccessible during my day time. Perhaps it's a server problem...

Thanks again!

[Lacak2]

You can also look at http://wiki.lazarus.freepascal.org/ODBCConn

Example of using TODBCConnection in code
(not tested only as example of principles):
procedure TForm1.FormCreate(Sender: TObject);
var Fodbcconn: TODBCConnection;
     Ftransaction: TSQLTransaction;
     FSQLQuery: TSQLQuery;
begin
  Ftransaction:=TSQLTransaction.Create(Self);
  Fodbcconn:=TODBCConnection.Create(Self);
  with Fodbcconn do begin
     Driver:='Microsoft Access Driver (*.mdb)';
     HostName:='';
     UserName:='';
     Password:='';
     DatabaseName:='';
     Params.Append('DBQ=C:\path\to\my\database.mdb');
     Transaction:=FTransaction;
  end;
  FSQLQuery:=TSQLQuery.Create(Self);
  FSQLQuery.DataBase:=Fodbcconn;
end;

[JimBeam]

2. Does it mean that the visual components like TSQLTransaction cannot be used with Access?

I have no experience with Access, but I think, that you can use it, but transactions will be ignored

FYI, in Access itself, transactions are supported both by the Access/Jet database engine and the Access Basic/VBA (Visual Basic for Applications) programming environment. Of course, as previous posters said, it's up to the interface to Jet to implement manual transactions or just use autocommit... (I don't know whether the ODBC driver for Access supports transactions, but I would suspect it does).

[xenblaise]

There is no problem about SQLTransaction.

If you want to use microsoft access?, Read this post
Updated oct 21 2010 with add_delete_update_print
http://forum.lazarus.freepascal.org/index.php/topic,10811.0.html