B4J Question TEMP Database [SOLVED}

i have a problem with a REST Api i aim building in B4j that i am thinking on how to solve for a while and i cannot find an efficient way.

so i have many entries that i read from the DB (mysql/sqlite) and i would like to show it on the website.
the problem is this:

it is a statistic collection of the use and i collect all data and save it inside a list (blob type) that is associated to a user in the DB.
first i saved every entry as a new line in the DB but then i was thinking that this table will get very fast very huge so i decided to store that data in a LIST and always update that list only that is associated to a user and now it is only a single line in the db but i cannot read from it directly and perform queries because it is a blob type so i have to read the list and do all logic in b4j inside.

so i will give an example.

i have many users that are from a specific country now i want to group the users by country and show them sorted in a table.
in pure sql i would do it like this


very simple but how would i do it in b4x?
so i have a map and i could just check if the map has already a key and if it has i could just increase the value (that is the count) this works fine but now how to sort that list by country?
i could use another map and sort it to that map and then back but in my app it is a little bit more complicated than the example i wrote here.

so i checked and i found that you can work with TEMP database in sqlite so i was thinking creating a temporary sqlite db and use the powerful sql language as above but how can i use temporary db in b4x?

2.6. TEMP Databases​

Tables created using the "CREATE TEMP TABLE" syntax are only visible to the database connection in which the "CREATE TEMP TABLE" statement is originally evaluated. These TEMP tables, together with any associated indices, triggers, and views, are collectively stored in a separate temporary database file that is created as soon as the first "CREATE TEMP TABLE" statement is seen. This separate temporary database file also has an associated rollback journal. The temporary database file used to store TEMP tables is deleted automatically when the database connection is closed using sqlite3_close().


The TEMP database file is very similar to auxiliary database files added using the ATTACH statement, though with a few special properties. The TEMP database is always automatically deleted when the database connection is closed. The TEMP database always uses the synchronous=OFF and journal_mode=PERSIST PRAGMA settings. And, the TEMP database cannot be used with DETACH nor can another process ATTACH the TEMP database.


The temporary files associated with the TEMP database and its rollback journal are only created if the application makes use of the "CREATE TEMP TABLE" statement.

Click to expand...

does anyone know how to create a temporary db use it and then close the connection and delete the temp files?
it is used only inside a single function and i dont want to create many many sqlite db files each time that function is called.
i think this could be a perfect solution if i could use a temporary db, make my queries and close connection (delete the file)

thanx