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

Discussion in 'B4J Tutorials' started by Mashiane, Jan 31, 2019.

  1. Mashiane

    Mashiane Expert Licensed User

    Ola

    UPDATE: BANanoSQLite now available for actual sqlite db CRUD.

    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..

    sqlitedb.png

    Reproduction:

    You need Ajax..

    Code:
    #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...

    Code:
    BANano.RunJavascriptMethod("OpenDB",Array As String("./assets/bibleshow.db"))
    By the way, you will need this github project.

    Ta!

    PS: Use at own risk. :D
     
    Last edited: Jul 9, 2019
    joulongleu likes this.
  2. Mashiane

    Mashiane Expert Licensed User

    Almost close..

    Code:
    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.
     
    joulongleu likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice