🟥 [FREE] SimpleSqlite - an extension for working with SQLite Databases

This is a fork/rework/remix of Tom Bennedum's open-sourced aix-SQLite extension. (all Credits to Tom!)
I have stripped it back to a bare-bones/expert version, without (hopefully) affecting functionality.

The key to working with this extension/sqlite is:

  • being clear about which database file and also which database you are working with
  • remembering to OPEN the database to carry out data operations (SELECT etc)
  • remembering to CLOSE the database to carry out file operations (IMPORT/EXPORT etc)
  • the default file path for IMPORT/EXPORT is the ASD (or Assets if you use // on IMPORT)
  • knowing that all SELECT queries are returned as AI2 lists

I have written a guide to complement this release:

Working with SQLite

.
.

  • Latest Version: 2.0
  • Tested: This has been tested on App Inventor 2 using Android 10 and 12 devices, companion 2.62, and compiled apk. It may work on other derivatives....e.g. Kodular/Niotron/etc.
  • Released: 2022-05-18T23:00:00Z
  • Last Updated: 2022-05-18T23:00:00Z
  • Built: Using the Rush Extension Builder by @Shreyash
BLOCKS & USAGE:

Usage covered in the guide above

Extension

SimpleSqliteV2

ListFixerV1
A helper extension that fixes a problem with list of lists return by SELECT (read the guide)

Also see:
Integrating SQLite with Google Sheets

Enjoy :smiley: , and all feedback, comments and questions welcome.

Available for use in any MIT AppInventor projects or competitions

#####################################################################
This work by TIMAI2 is licensed under an
MIT Licence
#####################################################################

10 Likes

i use Tom Bennedum's original extension; it has suited my needs. Will give this remix a try and report.

Thanks.

1 Like

Works for me.

@TIMAI2 is it possible to ExportDatabase as /peopleCodes.db in ASD and then read it with the File.ReadFrom component?

Thanks for this great extensions as always you are awesome and genius

i was extracting one of .db files using this [ SQLiteStudio ] ( https://sqlitestudio.pl/ )

you can create them easily as well extract other apks db files :smiley:

The sqlite db is not in a text format suitable for reading with file component.

It is possible to query the entire table then save the result to a csv file for future reading.

1 Like

@Juan_Antonio

Please read my guide again, which I have updated, I found a quirk with the return of list of lists....

I was testing this @TIMAI2 extension and noticed that sometimes (like a daemon) when I deleted the aaaaaa.db file, .db-shm and .db-wal were created, here I found the explanation:

This is just a test application:

Two database paths are established: ASD/peoplesCode.db and ASD/peoplesCode2.db.
It opens, creates, inserts and selects in peoplesCode.db.

Store this file to CloudDB (in Base64 - extension).

Get as peoplesCode2.db (from Base64 - extension).
Open and Select * of peoplesCode2.db

If you want to go back to the peoplesCode.db you must Click in OpenDatabase peoplesCode.db

Borrar_Sqlite.aia (54.8 KB)

How are you deleting the database? This should only be possible using external file management, nothing to do with the extension. But best practice would be to ensure the database is closed and not the "main" database before deleting?

My comment was only because I was unaware of the existence of the .db-wal and .db-shm files.

I have tested it on an Android 9, MIT Comp. and installed.

When I delete the ASD/peoplesCode.db file without closing it, the .db-wal and .db-shm files are created.

I delete it using the File component, File.Delete

Interestingly, if I now close the database and File.Delete: ASD/peoplesCode.db again, the .db-wal and .db-shm are deleted, they are shared memory files.

1 Like