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]

The TSQLConnection object - does it reconnect if the connection times out?

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.

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?

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.

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.

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.

The connection object can actual not detect if a connection is broken, nor can it reconnect.

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]

The connection object can actual not detect if a connection is broken, nor can it reconnect.

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]

The connection object can actual not detect if a connection is broken, nor can it reconnect.

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

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).

[MarkMLl]

Which is why the worked example to which I linked worked on the principle of asking the server for details about the connection, rather than trying to fiddle with the connection itself. Even less appropriate would be setting up a second connection in parallel, since it would be subject to different inactivity timeouts.

MarkMLl

[af0815]

Your sample is about postgres. The server drivers are very different and something can working on one type of server and not working on another. My experience for ages is on (different) MS-SQL Servers (Linux and Windows based). The communication is normal based on the freetds-drivers and there i have not found a working soloution.

BTW: SQL-DB - A Transaction with autocommit mean this is only for insert/delete/update/select statements, if you you use stored procedures - autocomit mean more or less, no autocommit - it is more a auto rollback. But this is by design (as one of the dev's anwers)

edit: BTW inserted

[MarkMLl]

Your sample is about postgres. The server drivers are very different and something can working on one type of server and not working on another. My experience for ages is on (different) MS-SQL Servers (Linux and Windows based). The communication is normal based on the freetds-drivers and there i have not found a working soloution.

The main thing that makes it specific to PostgreSQL is what I had to do to get information on the socket making up the connection.

MarkMLl

[Zvoni]

Or ping in the background the server if possible (and allowed/accepted).

Have to disagree.
A successful ping just says "Yes, the machine is alive"
It's not a statement, that the DB-service on it is running.

The only possible way to check if a Database-Server is alive is to actually trying to "access" it (in whatever way)

[MarkMLl]

The only possible way to check if a Database-Server is alive is to actually trying to "access" it (in whatever way)

What I was doing was querying the server OS's understanding of the state of the socket.

MarkMLl

[af0815]

Or ping in the background the server if possible (and allowed/accepted).

Have to disagree.
A successful ping just says "Yes, the machine is alive"
It's not a statement, that the DB-service on it is running.

I agree this is not the best soloution. But in the network here, i can say, if i can ping the device, i can try a connect to the device. And if the device is not reachable, i should forget the last connection and start from zero. For me this is the only way to work with 24/7 applications, running without a userinteraction.

If there is a better way - pls show it. Only using the connection was not running 24/7 without a userinteraction for closing some errormessages out of the deeper SQL-DB system. The actual diversystem can not handle this in a proper and safe way (for MS-SQL with freetds)

[Zvoni]

Or ping in the background the server if possible (and allowed/accepted).

Have to disagree.
A successful ping just says "Yes, the machine is alive"
It's not a statement, that the DB-service on it is running.

I agree this is not the best soloution. But in the network here, i can say, if i can ping the device, i can try a connect to the device. And if the device is not reachable, i should forget the last connection and start from zero. For me this is the only way to work with 24/7 applications, running without a userinteraction.

If there is a better way - pls show it. Only using the connection was not running 24/7 without a userinteraction for closing some errormessages out of the deeper SQL-DB system. The actual diversystem can not handle this in a proper and safe way (for MS-SQL with freetds)

Andy, i do understand you.
The problem is as follows:
1) Ping not successful --> definitely no connect, since the physical machine is not reachable (Don't start with VM's).
It doesn't matter if the machine and DB-Service is alive and (optionally) reachable from somewhere else. Your client cannot connect
2) ping successful, but DB-Service down/crashed --> definitely no connect

Meaning: in both cases the answer to "Can i connect to the Database?" is only: "Try the connection"

Because if you CAN connect, then you don't need to ping....