Android Question How to manage multiple SQL variables

You don't need multiple SQL objects as you can have multiple open cursors.

The best place for the SQL object is Process_Globals of the Starter service and initialize it in Service_Create.

Erel said:

You don't need multiple SQL objects as you can have multiple open cursors.

The best place for the SQL object is Process_Globals of the Starter service and initialize it in Service_Create.

Click to expand...

That's great news.

So B4A has its own connection pool and all I have to do to release resources is to execute cursor.close and internally it will reuse that connection later if needed. Well that's one less headache I have to worry about. Can I assume B4i does the same thing with its ResultSet?

TIA

It is not a connection pool. The native SQLite driver in Android supports it. I've just tested it in B4i and it doesn't work.

Erel said:

It is not a connection pool. The native SQLite driver in Android supports it. I've just tested it in B4i and it doesn't work.

Click to expand...

When you say it doesn't work in B4i, are you saying with SQL (Sqlite) you can have only one open Resultset? If so, then what's the solution for having nested queries?

Widget said:

you can have only one open Resultset?

Click to expand...

Yes (in B4i). The solution is to read all the results to a List (you can use DBUtils.ExecuteMemoryTable) and then work with the list.

Erel said:

Yes (in B4i). The solution is to read all the results to a List (you can use DBUtils.ExecuteMemoryTable) and then work with the list.

Click to expand...

Thanks Erel. I think using DBUtils to get the results returned in a List is the answer for creating universal database apps (apps that work in B4A and iOS). This will work for me 90% of the time. However if I have to traverse a large table it would be more efficient to use a Cursor/Resultset on the table directly because I don't need all of the rows loaded into memory at one time.

But for other users who don't want to use DBUtils or when I need to access SQLite Resultset directly for large tables, which of the 3 options in my first post is the better option for nested database calls? (I am writing an app that works in B4A and B4i and prefer to minimize the amount of custom code to handle the different platforms).

TIA

Either create a more complex query that does everything in a single query or cache the results. You shouldn't use multiple SQL objects pointing to the same database. You will corrupt the database.

Erel said:

Either create a more complex query that does everything in a single query or cache the results. You shouldn't use multiple SQL objects pointing to the same database. You will corrupt the database.

Click to expand...

If that's true, it should be documented.
Has anyone been able to corrupt a Sqlite database by having two SQL objects pointing to the same database?

Sqlite has file locking to prevent 2 processes from writing to the same table at the same time (assuming the app can handle the case when it cannot establish a lock and will retry later). I would have thought it was possible to have a service updating the database table (sparingly) in the background when the user had another app open that referenced the same database table. The foreground app would likely be reading the table that the background app is updating the table. For example, the service could be downloading stock data and the foreground app would be charting it.

Can users chime in and verify using two SQL objects pointing to the same database causes corruption? Can they post a working app?
TIA