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:
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.NextSetup query
Exec insertThis is where things get dicey
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.NextThat 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
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.NextUltimately 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.
Recent
