B4J Tutorial [BANano]: Distributing and accessing an existing SQLite Databases - Part 1

Ola

Part 2

UPDATE: BANanoSQLite now available for actual sqlite db CRUD using PHP

I've been wondering if I could be able to do this as I want to distribute my app with an already existing SQLite.DB database for READONLY access. No, this is not WebSQL but a pure SQLite database.

At first I thought perhaps if I could convert the db to json and then read it like that I would have a nice solution, well that is for another day.

For this to work, your SQLite.db should be:

1. Copied to your Files tab. This will be copied to your assets folder of your BANano App.
2. The app SHOULD RUN from a WEBSERVER. Accessing local files is blocked by CORS, perhaps if one can figure out how to access './assets/sqlite.db' it could be better. I would love that.
3. I want to do this with the built in BANano.CallAJax method though. Still learning the ropes. Would be nice. Perhaps one of the gurus here can provide some green lights.
4. The built in alaSQL for BANanoSQL has a way to attach sqlite databases, I have not been able to figure it out yet. A topic for another day.

Here is the output of my very own database selecting 1 table..

Reproduction:

You need Ajax..

#if javascript
var db;
var SQL = window.SQL;
function OpenDB(that){
    var xhr = new XMLHttpRequest();
    xhr.open('GET', that, true);
    xhr.responseType = 'arraybuffer';
    xhr.onload = function(e) {
          var uInt8Array = new Uint8Array(this.response);
          db = new SQL.Database(uInt8Array);
          var contents = db.exec("SELECT * FROM Analysis");
        console.log(contents);
    };
    xhr.send();
}
#End If

Then I call it...

BANano.RunJavascriptMethod("OpenDB",Array As String("./assets/bibleshow.db"))

By the way, you will need this github project.

Ta!

PS: Use at own risk.

 

[Mashiane]

Almost close..

Dim res As String = BANano.CallAjaxWait("./assets/bibleshow.db","GET","arraybuffer","",True,Null)
    #if javascriptsmart
        var db;
        var SQL = window.SQL;
        var uInt8Array = new Uint8Array(${res});
        db = new SQL.Database(uInt8Array);
        var contents = db.exec("SELECT * FROM Analysis;");
        console.log(contents);
    #End If

Just cant seem to get the var contents = .... line to work for now.