Lazarus
Programming => Databases => Topic started by: HopefulGuy on May 10, 2021, 01:21:04 am
-
I've looked, but perhaps I'm missing something but the TSQLConnection seems to be insisting I set a transaction, even for an initial read.
In the FormCreate() function, I have code like this (it's incomplete, but was setup for a quick test to ensure basic functionality):
Db1Query.Sql.Add('Select IDNo, FirstName, FamilyName from Table1 order by IDNo');
try
begin
Db1Query.Open;
while not (Db1Query.EOF) do
Db1Query.next; // just itterate through for now
end;
except
on E: Exception do
begin
MessageDlg('Database Error!', 'An error occured while attempting to access the database. Error message received was: ' + E.Message, mtError, [mbClose], '');
halt(1);
end;
DB1query is a TSQLQuery with the host, password, username and database name assigned in the component properties, outside of any function while the SQL text is defined earlier than this block.
But as soon as I try running the program, I get an error about Transactions not being set. This is a test database with a whole TWO records. Now why would I use a transaction. And why would the system insist on that? Frankly, I don't think I'd ever use a transaction because this program is not meant for multi-user simultaneous use. So how do I get around this? I can put TSQLTransaction on there, but why? Why include a component that I'll NEVER use except for assigning it to a property of some other component? The Attached image shows the dialog.
-
Now why would I use a transaction.
The documentation is an excellent place to start.
https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.transaction.html
https://www.freepascal.org/docs-html/fcl/sqldb/tsqltransaction.html
-
I did read those. They explain what a Transaction is used for. What I was trying to determine is why it is forcing transaction mode on me. Transactions are handy for more busy systems, don't get me wrong. But this is a personal project, one user/access at a time. Never more than that. So transactions are nothing more than wasted CPU power and wasted bandwidth in this case. So maybe I should have asked if anyone knows why there is no way to force non-transaction mode.
-
Transaction is required by database server, not by local application. Most SQL databases require transactions. If you are using dBase or MS Access then transactions are not necessary.
-
I did read those. They explain what a Transaction is used for. What I was trying to determine is why it is forcing transaction mode on me. Transactions are handy for more busy systems, don't get me wrong. But this is a personal project, one user/access at a time. Never more than that. So transactions are nothing more than wasted CPU power and wasted bandwidth in this case. So maybe I should have asked if anyone knows why there is no way to force non-transaction mode.
FPC's SQL-DB always requires a transaction. That is simply how it is designed. This might or might not be translated to a server side transaction depending on the DB.
Transaction is required by database server, not by local application. Most SQL databases require transactions. If you are using dBase or MS Access then transactions are not necessary.
Not quite right: a TSQLTransaction instance is required by TSQLQuery due to how the system is designed.
-
You can use ZeosDBO database components. In ZeosDBO using transactions for MySQL and MariaDB is not mandatory. I use transactions explicitly when I want to use them.
You can install ZeosDBO through Online Package Manager.
-
You can use ZeosDBO database components. In ZeosDBO using transactions for MySQL and MariaDB is not mandatory. I use transactions explicitly when I want to use them.
You can install ZeosDBO through Online Package Manager.
It is just the way how Zeos is designed -- one connection always create one transaction which it holds internally. It's essentially no different than having one connection object and one transaction object in SQLdb.
As PascalDragon wrote, the transaction might not map to a server side transaction when working with some simple db engines, which do not always require transaction.
I believe it falls to very similar approach, with either Zeos, where connection and transaction are packed in one component, or SQLdb, where you need two separate components.