Forum > Database
Life cycle of TSQLTransaction, etc.
Mario:
Hi,
I am wondering what the best practices are with transactions, connections and queries in terms of life cycle. The code I have seen is very simple so that isn't an issue there.
I am wondering
* The TSQLConnection object - does it reconnect if the connection times out?
* The TSQLTransaction object I need to do queries. Do I destroy it each time I am done with a set of queries or can I basically query all the way to the horizon?
Finally, and related, if I have a function that does something complex with a database, do I pass it the connection, the connection and transaction, or something else?
I am really looking forward to learn about how it is done in modern Pascal.
af0815:
The connection object can actual not detect if a connection is broken, nor can it reconnect.
But Zeros 8 should be able to detect a broken connection on some systems. If it broken the queries and connection is closed, so you can try anew attempt. I have not tested this, because mssql is not a supported system for the detection (as far as I have read this)
MarkMLl:
--- Quote from: Mario on July 12, 2024, 03:35:00 pm ---The TSQLConnection object - does it reconnect if the connection times out?
--- End quote ---
No, the server-end state will be lost so that transaction is- by definition- screwed. See https://github.com/MarkMLl/testdb which I did as the result of a discussion about somebody's problems when working from home and his ISP reallocated his IP address.
--- Quote ---The TSQLTransaction object I need to do queries. Do I destroy it each time I am done with a set of queries or can I basically query all the way to the horizon?
--- End quote ---
I think you should find that it's there basically to act as a "handle" (term used extremely loosely) to establish a particular level of transaction isolation and handle commit/rollback.
--- Quote ---Finally, and related, if I have a function that does something complex with a database, do I pass it the connection, the connection and transaction, or something else?
I am really looking forward to learn about how it is done in modern Pascal.
--- End quote ---
Depends on the level of abstraction you're using, and whether you're using graphical components.
What I found from the above experiments did leave a bit of a sour taste. What they demonstrated in particular is that trying to use a grid to (potentially) edit multiple rows was unwise, since if the connection was interrupted it became difficult to reconcile what the grid currently looked like with what was in the database... and there's limits to the extent to which that can be fixed using an ever-increasing number of middleware layers.
--- Quote from: af0815 on July 12, 2024, 03:57:18 pm ---The connection object can actual not detect if a connection is broken, nor can it reconnect.
--- End quote ---
I was able to- basically- poll the connection state for PostgreSQL, but had to patch some of the low-level stuff. Details in project cited above.
MarkMLl
Zvoni:
--- Quote from: af0815 on July 12, 2024, 03:57:18 pm ---The connection object can actual not detect if a connection is broken, nor can it reconnect.
--- End quote ---
Yes and no.
No, the Conn-Object can't detect a broken connection by itself automagically.
Yes, (THEORETICALLY!! Never tested it!) it could "detect" a broken connection by just firing of a "dummy"-SQL-Statement before you do anything else.
Could even be implemented as a "cycling" poll within its own thread
@Transactions: In pure Database-speak, you don't "need" a Transaction-Object, since, in a nutshell, the Transaction-Object just expands SQL-Statements with "BEGIN TRANSACTION", "COMMIT" (et al).
Pretty much all DBMS use implicit Transactions, if there is no "BEGIN TRANSACTION".
OTOH, even without a Transaction-Object you could still have them via direct SQL.
Everything said (also see Andy's and Mark's answers): It's definitely good practice to use a Transaction-Object
And no: You don't "destroy" the Transaction-Object everytime you used it.
You create it once, usually at the same time you create the connection, and you only destroy it, if you definitely don't need it anymore, usually when a Connection is explicitely closed.
@Your Function: TSQLQuery and TSQLTransaction are DBMS-Agnostic, so in layman's terms: They don't know, to which kind of DBMS you are connected. That's the job of the Connection-Object.
So if you have an abstraction-layer, the Connection-Object is actually the one you want to "pass", since TSQLQuery and TSQLTransaction actually just call the "corresponding" methods of the Connection-Object, which actually executes the lowlevel API-Calls to the Database
af0815:
--- Quote from: Zvoni on July 15, 2024, 08:42:00 am ---
--- Quote from: af0815 on July 12, 2024, 03:57:18 pm ---The connection object can actual not detect if a connection is broken, nor can it reconnect.
--- End quote ---
Yes and no.
No, the Conn-Object can't detect a broken connection by itself automagically.
Yes, (THEORETICALLY!! Never tested it!) it could "detect" a broken connection by just firing of a "dummy"-SQL-Statement before you do anything else.
Could even be implemented as a "cycling" poll within its own thread
--- End quote ---
I have tested such a polling - it is blocking and brings sometimes a popup with the information the connection is not working and this popup can not be handled by a exception alone and so not useable for machine systems. ANd the blocking in the background will try the system to shutdown your "not" responding app. Not a good situation.
Yes you can check some connectivity by sending "NOP" Sql Statements from time to time. Or ping in the background the server if possible (and allowed/accepted).
Navigation
[0] Message Index
[#] Next page