Forum > Database
Best practice for multiple / parallel queries (select, insert, ...)
Hi im Pascal:
Hi all,
I've been using databases quite a lot in Lazarus/FPC (TSQLConnection, TSQLTransaction, TSQLQuery, ...) with SQLite3 and generally have been able to figure things out.
However, I often run into issues and I wonder if there is a design flaw or wrong approach, and what is the best recommended practice to do this.
Simple cases like this work flawlessly:
--- Code: ---Setup query (I'm using a custom "query builder" class for this to prepare the statements)
Exec select
while not query.EOF
do nice things with database row
query.Next
--- End code ---
--- Code: ---Setup query
Exec insert
--- End code ---
This is where things get dicey
--- Code: ---Setup query
Exec select
while not query.EOF
do nice things with database row
I want to iterate further based on data, think like a nested loop for i in x, for j in x[i] ...
Setup query2
Exec select
while not query2.EOF
Nested query results processing
query2.Next
query.Next
--- End code ---
That alone always seemed to not work, probably due to underlying stuff getting mixed up, like using the same query and/or transaction and/or connection. I even tried to hack my way out of it my taking ownership of the query and allocating a new one for the nested query, etc.
However, now when also wanting to do parallel insert, it simply won't work anymore, because apparently a select query still uses an open transaction, which I've come to realize is apparently because there are some crazy features in TSQLQuery where one can still update / change data even after only having performed a SELECT. So this does not work
--- Code: ---Setup query
Exec select
while not query.EOF
do nice things with database row
Decide I need to enter a new row into database based on selected data
Setup query2
Exec insert
query.Next
--- End code ---
Ultimately I can come up with a few ideas, but I wanted to ask the expert opinion on whether there are any recommendations or if I'm completely on the wrong track:
* I could first select, then parse everything into an internal model / structure (e.g., TVector of records) and then process / iterate over that instead (I know this could work)
* I could create complete duplicate sets of (Connection, Transaction, Query) and operate them in parallel, e.g., at least one separate one for reading and writing (not tried yet)
* ... some other elegant options I don't know about, perhaps using a single query, or using multiple (Transaction, Query) pairs within a Connection, although I doubt that will work, due to already having the "No transaction within an transaction" error.
Edit:
Tried option 2, still does not work due to multiple transaction error, so having only a single active transaction seems a global restriction per database even and not just per connection.
> My current working direction is now option 1 which seems to be working ok.
Zvoni:
You do know that you can execute Inserts, Updates and Deletes just with the connection object?
Hi im Pascal:
--- Quote from: Zvoni on April 06, 2024, 04:56:25 pm ---You do know that you can execute Inserts, Updates and Deletes just with the connection object?
--- End quote ---
Yes, I do. But can I do it in parallel, is the problem...
Edit: Ah, you mean only TSQLConnection without Query and Transaction, I was not aware if that's the case. However, I use paramter substitution / prepared statements heavily, which according to the docs would not be supported here, and anyway it uses a default transaction in the background, which I'm guessing still would conflict in the same way with any other transaction started due to an insert statement.
Zvoni:
Well, then there is your answer: use a second transaction (you’re already using a second query).
One caveat: the „inner“ transaction MUST be committed or rolled back before continuing with the outer one
Hi im Pascal:
--- Quote from: Zvoni on April 06, 2024, 08:13:39 pm ---Well, then there is your answer: use a second transaction (you’re already using a second query).
One caveat: the „inner“ transaction MUST be committed or rolled back before continuing with the outer one
--- End quote ---
Are you sure? I'll have to try again tomorrow... I will give more concrete error messages. I thought I distinctly remembered that merely attempting to execute another query with an "inner" transaction already raised an exception along the lines of "Cannot start another transaction within a transaction".
Navigation
[0] Message Index
[#] Next page