PureBasic Forums - English

Hi

It would be useful to have more than one database query open at a time.

Thanks,
Simon

swhite wrote: Sat Oct 12, 2024 5:56 pmIt would be useful to have more than one database query open at a time.

Hi Simon. Is it not possible to do so now?

DatabaseError() and some other commands don't have parameters so it's impossible to know which call caused an error.

jacdelad wrote: Sun Oct 13, 2024 7:14 amDatabaseError() and some other commands don't have parameters so it's impossible to know which call caused an error.

The DatabaseError() function simply retrieves the last database error. This is usually implemented in error handling and would be directly associated with a database operation. So, it's not totally impossible to determine the affected database.

...that's what I meant.

jacdelad wrote: Sun Oct 13, 2024 7:31 am ...that's what I meant.

I thought you meant that it is impossible.

jacdelad wrote: Sun Oct 13, 2024 7:14 am DatabaseError() and some other commands don't have parameters so it's impossible to know which call caused an error.

It is impossible, because you can't know which call caused the error (given you do multiple calls at once, threaded). Sure, it is technically possible but you will run into problems.

jacdelad wrote: Sun Oct 13, 2024 8:42 amIt is impossible, because you can't know which call caused the error (given you do multiple calls at once, threaded). Sure, it is technically possible but you will run into problems.

It would be very unwise to access common database connections from separate threads. Each thread should have its own database connection, and as such, they should work independently of the others. With such models, the DatabaseError() issue would also work independently.

Furthermore, asynchronous database connections should consider many other factors, like transaction isolation and concurrency control. Best to keep database management basic.

Nevertheless, I believe that this feature request is for nested queries, and not for concurrent connections.

Normally this is not needed.

You can solve it in a 'better' SQL command.
If you can not ..., open a second connection to the database.

I tried with threads, but it didn't work reliable. Even with only one query in a thread, to not block the main loop, it wasn't satisfying.
Never thought about nested queries, because, like infrared wrote, it usually shouldn't be needed.

It appears to me that only one query per connection is possible. I am use to opening a connection to a database but then being able to open several queries. So I assumed I could do the same in PB but that is not the case. There are times when looping through several queries is needed to update other tables. An SQL statement to do this can be quite complex and hard to test all the edge cases. It is often easier to open several queries instead. Having said that perhaps I have not understood PB database design. In such a scenario should I being opening multiple connections to the database so that each connection can have its own query? Usually I try to minimize the connections to a database so I never thought of doing it this way before.

Simon

Opening several database cursors and doing queries to data that will be used in another query is never a good idea. If other users use the same database at the same time this can lead to inconsistency.
When you are working with multiple data and you need a complex query or even multiple ones, always start a transaction on your single database cursor and do all your stuff within that transaction, in the end do a commit. Only this way the database is able to be consistent afterwards.