Android Question How to manage multiple SQL variables

[Widget]

In all of the B4A examples I've seen with SQLite, the app uses a global variable SQL1 that is initialized when the app is started/resumed. This is fine.

But if the app has a lot of nested Subs that need SQL access to tables, they can't all reference the same global SQL1 variable because it may already have a cursor open in another Sub. For example, Sub1 may be looping through Table1 and inside the loop it calls Sub2 that needs to do a lookup to Table2 and also calls Sub3 that updates rows of Table3 etc..

My question is what is the best way to manage multiple SQL variables (so it can handle nested SQL calls)?

1. Do I use a Map that has a list of available SQL variables that are available? (The map contains re-used SQL variables that had been previously used but no longer needed)
2. Or do I have each Sub initialize its own local SQL variable when it needs to do some processing or a lookup etc.?
3. Is there a better way?

TIA

 

[Erel]

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.

 

[Widget]

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.

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

 

[Erel]

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.

 

[Widget]

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.

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?

 

[Erel]

you can have only one open Resultset?

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.

 

[Widget]

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.

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

 

[Erel]

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.

 

[Widget]

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.

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