Has anyone tried running sql.js in hidden webviewer to access local database?

Data Storage
#1

I was just wondering if anyone has tried to exploit the webviewer component to give access to a localised sqlite database within an app without using any extensions?

I was thinking that it may be done using:

  1. sql.js loaded as a webpage into a webviewer
  2. using the webviewstring functionality to send in SQL queries and for getting the result of those queries

Note quite sure how you would load an SQLite file into the browser component however.

Anyway, has anyone tried this? Anyone think it is feasible?

#2

Not seen anyone trying it. Why not give it a go, see how you get on, and report back.

You may need an extension that extends the webviewer's features to get it to work ?

Suggest just try generating a virtual db first. You could then convert a local db to base64 string to transfer it to the webviewer using webviewstring, then convert back to a database object.

You might also consider using localStorage as a simple database for key:pair values....

#3

webviewer do not support this I think. Maybe customWebview can.

#4

As I suggested, use extensions.

CustomWebView
WebViewTools
WebViewExtra

#5

Some of my other requirements are:

  1. Must use SQL so that relational databases can be developed, normalised and queried.
  2. Can run on iOS and Android, which means no extensions.

I know I could just set up a remote database and work that way, but I am after a local on device solution (less moving pieces for students to deal with initially).

#6

Given I have no experience of sql.js, my approach would be:

  1. Get it running on my computer using only local files (no server) - learn how it works
  2. Test the same in a webviewer, with companion / compiled app
  3. Work up a solution to load/use/save a db file from the device

It should be platform independent - but there may be quirks to deal with for both Android and iOS
Need to remember it is SQLite and not SQL - maybe just semantics ....

#7

Does iOS have SQLite in it?

#8

Doesn't need it for this, sqlite is running in the js file.

#9

Yes, iOS does have SQLite by default, however, App Inventor for iOS can't run extensions so I need a 'vanilla' App Inventor solution so it can run on both iOS and Android devices.

#10

Tested, working in companion app using the non persistent database created/provided by sql.js :smiley:

Now to figure out how to import (export) a physical database file on the device......

#11

From what I see in

you will need to also run Node.js and jQuery (ABG edit) to be able to persist your data base in a file between runs.

#12

According to the sql.js documentation:

You can import and export a database file as a Uint8Array
(in AppInventor we would most likely convert this to/from a base64 string so that we can use the webviewstring)

#13

If you want easy setup and an easy ramp into table concepts, how about

and

#14

@andrewt

Been having a think about this, based upon what I have learnt so far:

  1. You could possibly load a local sqlite.db file from the assets, but this would only be a read only location
  2. It should be possible to make the sql.js database persistent, based upon what I have seen from the sql.js examples
  3. Extensions would be required to a) convert db files to base64 and back again to files (in order to use the webviewstring), b) simplify the functionality for loading/saving db files using the input=file tag and the Android Download Manager.
#15

Here with remote server (I imagine you want to do the same but local).

https://sql.js.org/examples/GUI/index.html

#16

Yes Juan, but @andrewt would need to use an extension (e.g. webviewextra :wink: ) for the load and save functionality.

#17

from the context, I think the OP do not want to use an extension, since he want run on iOS.

#18

Another step of...

https://www.zetakey.com/codesample-sqldb.php

websql3
websql3457×804 60.8 KB

WebSQL.aia (2.6 KB)

I don't know where it saves the data, in MIT Companion the file is created
\Android\data\edu.mit.appinventor.aicompanion3\files\AppInventor\databases\hashTable.db

2 Likes