Page 1 of 2

Use SQLite with SpiderBasic

Posted: Sat Dec 26, 2015 9:00 am
by Stefan Schnell
Hello community,

SQLite is a very good database library, you can find it here: http://www.sqlite.org/.

A few developers implement SQLite to JavaScript, you can find the project here: https://github.com/kripken/sql.js/.

I implement a part of an example in SpiderBasic:

Code: Select all

; Begin-----------------------------------------------------------------

  ; Directives----------------------------------------------------------
    EnableExplicit

  ; Sub LoadScript------------------------------------------------------
    Procedure LoadScript(script.s, *func)
      !$.getScript(v_script, p_func);
    EndProcedure 

  ; Function UseSQLiteDatabase------------------------------------------
    Procedure.i UseSQLiteDatabase()
      Protected sql.i
      !var v_sql = window.SQL;
      ProcedureReturn sql
    EndProcedure

  ; Function OpenDatabase-----------------------------------------------
    Procedure.i OpenDatabase(sqlite.i)
      Protected database.i
      !var v_database = new v_sqlite.Database();
      ProcedureReturn database
    EndProcedure

  ; Sub Main------------------------------------------------------------
    Procedure Main()
      Protected SQLite.i, database.i, strsql.s, res.i, value.s

      SQLite = UseSQLiteDatabase()
      If Not SQLite
        ProcedureReturn
      EndIf

      database = OpenDatabase(SQLite)
      If Not database
        ProcedureReturn
      EndIf

      strsql = "CREATE TABLE hello (a int, b char);"
      strsql = strsql + "INSERT INTO hello VALUES (0, 'hello');"
      strsql = strsql + "INSERT INTO hello VALUES (1, 'world');"

      !v_database.run(v_strsql);
      !v_res = v_database.exec('SELECT * FROM hello');

      !v_value = v_res[0].values[0]
      Debug value
      !v_value = v_res[0].values[1]
      Debug value

    EndProcedure

  ; Main----------------------------------------------------------------
    LoadScript("sql.js", @Main())

; End-------------------------------------------------------------------
These are my first steps but it shows how to use SQLite with SpiderBasic. Maybe in a future release of SpiderBasic we get a database interface like in PureBasic. :)

Enjoy it.

Cheers
Stefan

Re: Use SQLite with SpiderBasic

Posted: Sat Dec 26, 2015 5:24 pm
by Fred
Please note than the database is stored in the browser local data, so it can disappear at anytime if the user reset its browser. If you don't need it to be persistent, it's great, but for other purpose, it can be dangerous. Nice code anyway ! :)

You can also use the CDN version directly if you are connected to internet, so you don't need to download the file:

Code: Select all

LoadScript("https://cdn.rawgit.com/kripken/sql.js/master/js/sql.js", @Main())

Re: Use SQLite with SpiderBasic

Posted: Mon Dec 28, 2015 6:28 am
by Stefan Schnell
Hello community, hello Fred,

I expand the example to use existing databases.

At first I create with PureBasic a SQLite database file:

Code: Select all

; Begin-----------------------------------------------------------------

  ; Sub Main------------------------------------------------------------
    Procedure Main()

      UseSQLiteDatabase()

      Filename$ = OpenFileRequester("Wähle einen Dateinamen",
        "PureBasic.sqlite", "*.sqlite|*.sqlite", 0)

      If CreateFile(0, Filename$)
        Debug "Datenbank-Datei erstellt"
        CloseFile(0)
      Else
        Debug "Ein Fehler ist aufgetreten"
        ProcedureReturn
      EndIf
  
      If OpenDatabase(0, Filename$, "", "")
        Debug "Verbunden mit PureBasic.sqlite"
        DatabaseUpdate(0, "CREATE TABLE hello (a int, b char);")
        DatabaseUpdate(0, "INSERT INTO hello VALUES (0, 'hello');")
        DatabaseUpdate(0, "INSERT INTO hello VALUES (1, 'world');")
        CloseDatabase(0)
        Debug "Tabelle erstellt"
      Else
        Debug "Ein Fehler ist aufgetreten"
        ProcedureReturn
      EndIf

    EndProcedure

  ; Main----------------------------------------------------------------
    Main()

; End-------------------------------------------------------------------

; IDE Options = PureBasic 5.41 LTS (Windows - x86)
; CursorPosition = 9
; Folding = -
; CompileSourceDirectory
Now I load the database via XMLHttpRequest and select the information I need.

Code: Select all

; Begin-----------------------------------------------------------------

  ; Directives----------------------------------------------------------
    EnableExplicit

  ; Constants-----------------------------------------------------------
    Enumeration
      #MainWin
      #btnSelect
    EndEnumeration

  ; Variables-----------------------------------------------------------
    Global dbase.i

  ; Sub LoadScript------------------------------------------------------
    Procedure LoadScript(script.s, *func)
      !$.getScript(v_script, p_func);
    EndProcedure 

  ; Function UseSQLiteDatabase------------------------------------------
    Procedure.i UseSQLiteDatabase()

      ; Variables-------------------------------------------------------
        Protected sql.i

      !var v_sql = window.SQL;
      ProcedureReturn sql

    EndProcedure

  ; Sub btnSelectEvent--------------------------------------------------
    Procedure btnSelectEvent()

      ; Variables-------------------------------------------------------
        Protected res.i, value.s

      !v_res = v_dbase.exec('SELECT * FROM hello');
      !v_value = v_res[0].values[0];
      Debug value
      !v_value = v_res[0].values[1];
      Debug value
    
    EndProcedure

  ; Sub Main------------------------------------------------------------
    Procedure Main()
      If OpenWindow(#MainWin, 10, 10, 320, 240, "SQLite test")
        ButtonGadget(#btnSelect, 10, 10, 125, 25, "Select")
        BindGadgetEvent(#btnSelect, @btnSelectEvent())
      EndIf
    EndProcedure

  ; Sub LoadDatabase----------------------------------------------------
    Procedure LoadDatabase()

      ; Variables-------------------------------------------------------
        Protected sqlite.i, database.i, xhr.i

      ; Sub GetDatabaseEvent--------------------------------------------
        !function getDatabaseEvent() {
        !  var uInt8Array = new Uint8Array(this.response);
        !  var v_database = new v_sqlite.Database(uInt8Array);
           If Not database
             ProcedureReturn
           Else
             dbase = database
             Main()
           EndIf
        !}

      sqlite = UseSQLiteDatabase()
      If Not sqlite
        ProcedureReturn
      EndIf

      !var v_xhr = new XMLHttpRequest();
      !v_xhr.onload = getDatabaseEvent;
      !v_xhr.open('GET', 'PureBasic.sqlite', true);
      !v_xhr.responseType = 'arraybuffer';
      !v_xhr.send();
    
    EndProcedure

  ; Main----------------------------------------------------------------
    LoadScript("sql.js", @LoadDatabase())

; End-------------------------------------------------------------------

; IDE Options = SpiderBasic 1.10 (Windows - x86)
; CursorPosition = 35
; Folding = -
; WindowTheme = blue
; CompileSourceDirectory
Image

On this way you have the possibility - in combination with SQLite3COM, which I presented here - to use data from your SAP ERP system.

Enjoy it.

Cheers
Stefan

Re: Use SQLite with SpiderBasic

Posted: Mon Dec 28, 2015 9:36 am
by Fred
Now, it's getting interesting, I can see the use of local database query for client app ! Thanks for the use case, I think it will worth it to have it built-in then !

Re: Use SQLite with SpiderBasic

Posted: Tue Dec 29, 2015 5:55 am
by Stefan Schnell
Hello community, hello Fred,

thanks for the good news :D

Here now more prospects:

Export from a selection of different SAP tables as SQL commands and import via read in an SQLite database.

Image

Here is a look at the tables in the development perspective of SAP Workbench:

Image

A tiny modification of the example program:

Code: Select all

; Begin-----------------------------------------------------------------

  ; Directives----------------------------------------------------------
    EnableExplicit

  ; Constants-----------------------------------------------------------
    Enumeration
      #MainWin
      #SQLCmd
      #btnSelect
    EndEnumeration

  ; Variables-----------------------------------------------------------
    Global dbase.i

  ; Sub LoadScript------------------------------------------------------
    Procedure LoadScript(script.s, *func)
      !$.getScript(v_script, p_func);
    EndProcedure 

  ; Function UseSQLiteDatabase------------------------------------------
    Procedure.i UseSQLiteDatabase()

      ; Variables-------------------------------------------------------
        Protected sql.i

      !var v_sql = window.SQL;
      ProcedureReturn sql

    EndProcedure

  ; Sub btnSelectEvent--------------------------------------------------
    Procedure btnSelectEvent()

      ; Variables-------------------------------------------------------
        Protected res.i, value.s, sqlcmd.s, count.i, i.i

      sqlcmd = GetGadgetText(#SQLCmd)
      !v_res = v_dbase.exec(v_sqlcmd);
      !v_count = v_res[0].values.length;
      For i = 0 To count - 1
        !v_value = v_res[0].values[v_i];
        Debug value
      Next
    
    EndProcedure

  ; Sub Main------------------------------------------------------------
    Procedure Main()
      If OpenWindow(#MainWin, 10, 10, 320, 240, "SQLite test")
        EditorGadget(#SQLCmd, 10, 10, 300, 185, #PB_Editor_WordWrap)
        SetGadgetText(#SQLCmd, "SELECT * FROM SFLIGHT;")
        ButtonGadget(#btnSelect, 10, 205, 125, 25, "Select")
        BindGadgetEvent(#btnSelect, @btnSelectEvent())
      EndIf
    EndProcedure

  ; Sub LoadDatabase----------------------------------------------------
    Procedure LoadDatabase()

      ; Variables-------------------------------------------------------
        Protected sqlite.i, database.i, xhr.i

      ; Sub GetDatabaseEvent--------------------------------------------
        !function getDatabaseEvent() {
        !  var uInt8Array = new Uint8Array(this.response);
        !  var v_database = new v_sqlite.Database(uInt8Array);
           If Not database
             ProcedureReturn
           Else
             dbase = database
             Main()
           EndIf
        !}

      sqlite = UseSQLiteDatabase()
      If Not sqlite
        ProcedureReturn
      EndIf

      !var v_xhr = new XMLHttpRequest();
      !v_xhr.onload = getDatabaseEvent;
      !v_xhr.open('GET', 'sflight.sqlite', true);
      !v_xhr.responseType = 'arraybuffer';
      !v_xhr.send();
    
    EndProcedure

  ; Main----------------------------------------------------------------
    LoadScript("sql.js", @LoadDatabase())

; End-------------------------------------------------------------------
Now we can do with the database what ever we want:

Image

This scenario is interesting for data which are rarely or never change, like latitude and longitude of locations or technical specifications.
In the next step it could be of interest to mix these kind of data with highly topical data of high update frequency.

Enjoy it.

Cheers
Stefan

Re: Use SQLite with SpiderBasic

Posted: Thu Dec 31, 2015 7:02 am
by Stefan Schnell
Hello Fred, hello community,

I modified my code a little bit, now you have the possibility to save the changed database on your local file system and to reload it. On this way your changes are now persistent. ;)

Enjoy it.

Cheers
Stefan

Image

Code: Select all

; Begin-----------------------------------------------------------------

  ; Directives----------------------------------------------------------
    EnableExplicit

  ; Constants-----------------------------------------------------------
    Enumeration
      #MainWin
      #SQLCmd
      #btnSelect
      #btnSave
    EndEnumeration

  ; Variables-----------------------------------------------------------
    Global sqlite.i
    Global dbase.i

  ; Sub LoadScript------------------------------------------------------
    Procedure LoadScript(script.s, *func)
      !$.getScript(v_script, p_func);
    EndProcedure 

  ; Function UseSQLiteDatabase------------------------------------------
    Procedure.i UseSQLiteDatabase()

      ; Variables-------------------------------------------------------
        Protected sql.i

      !var v_sql = window.SQL;
      ProcedureReturn sql

    EndProcedure

  ; Sub btnSelectEvent--------------------------------------------------
    Procedure btnSelectEvent()

      ; Variables-------------------------------------------------------
        Protected res.i, value.s, sqlcmd.s, count.i, i.i

      sqlcmd = GetGadgetText(#SQLCmd)
      If UCase(Left(sqlcmd, 6)) = "SELECT"
        !v_res = v_dbase.exec(v_sqlcmd);
        !v_count = v_res[0].values.length;
        For i = 0 To count - 1
          !v_value = v_res[0].values[v_i];
          Debug value
        Next
      Else
        !v_dbase.run(v_sqlcmd);
      EndIf
    
    EndProcedure

  ; Sub btnSaveEvent----------------------------------------------------
    Procedure btnSaveDBEvent()

      ; Variables-------------------------------------------------------
        Protected b64enc.i, link.i

      !v_b64enc = btoa(String.fromCharCode.apply(null, v_dbase.export()));
      !v_link = document.createElement("a");
      !v_link.setAttribute("href", "data:application/octet-stream;base64," + v_b64enc);
      !v_link.setAttribute("download", "test.sqlite");
      !v_link.click();    

    EndProcedure

  ; Sub Main------------------------------------------------------------
    Procedure Main()

      ; Sub loadNewDBFileEvent------------------------------------------
        !function loadNewDBFileEvent() {
        !  v_dbase.close()
        !  var uInt8Array = new Uint8Array(this.result);
        !  v_dbase = new v_sqlite.Database(uInt8Array);
        !}

      ; Sub btnOpenDBEvent----------------------------------------------
        !function btnOpenDBEvent() {
        !  var reader = new FileReader();
        !  reader.onload = loadNewDBFileEvent;
        !  var dbfile = new File([''], '');
        !  reader.readAsArrayBuffer(dbfile); 
        !}

      If OpenWindow(#MainWin, 10, 40, 320, 240, "SQLite test")

        EditorGadget(#SQLCmd, 10, 10, 300, 150, #PB_Editor_WordWrap)
        ;SetGadgetText(#SQLCmd, "SELECT * FROM SFLIGHT WHERE CARRID='LH' AND PLANETYPE LIKE '727%';")
        SetGadgetText(#SQLCmd, "DROP TABLE SCARR;")
        ButtonGadget(#btnSelect, 10, 170, 125, 25, "Select")
        BindGadgetEvent(#btnSelect, @btnSelectEvent())
        ButtonGadget(#btnSave, 145, 170, 125, 25, "Save Database")
        BindGadgetEvent(#btnSave, @btnSaveDBEvent())

        !var input = document.createElement('input');
        !input.type = "file";
        !input.name = "files[]";
        !input.id = "files";
        !input.style.top = '205px';
        !input.style.left = '10px';
        !input.style.position = 'absolute';
        !input.onchange = btnOpenDBEvent;
        ;!document.getElementById("spiderbody").appendChild(input);
        !var win = document.getElementsByClassName('spiderwindow-content');
        !win[1].children[2].appendChild(input);

      EndIf
    EndProcedure

  ; Sub LoadDatabase----------------------------------------------------
    Procedure LoadDatabase()

      ; Variables-------------------------------------------------------
        Protected database.i, xhr.i

      ; Sub getDatabaseEvent--------------------------------------------
        !function getDatabaseEvent() {
        !  var uInt8Array = new Uint8Array(this.response);
        !  v_database = new v_sqlite.Database(uInt8Array);
           If Not database
             ProcedureReturn
           Else
             dbase = database
             Main()
           EndIf
        !}

      sqlite = UseSQLiteDatabase()
      If Not sqlite
        ProcedureReturn
      EndIf

      !v_xhr = new XMLHttpRequest();
      !v_xhr.onload = getDatabaseEvent;
      !v_xhr.open('GET', 'sflight.sqlite', true);
      !v_xhr.responseType = 'arraybuffer';
      !v_xhr.send();
    
    EndProcedure

  ; Main----------------------------------------------------------------
    LoadScript("sql.js", @LoadDatabase())

; End-------------------------------------------------------------------

; IDE Options = SpiderBasic 1.10 (Windows - x86)
; CursorPosition = 91
; Folding = --
; WindowTheme = blue
; CompileSourceDirectory

Re: Use SQLite with SpiderBasic

Posted: Sun Nov 20, 2016 11:15 am
by Leonhardt
mark

Re: Use SQLite with SpiderBasic

Posted: Mon Nov 21, 2016 7:32 am
by Peter
<OT>
Leonhardt wrote:mark
there's no need to mark threads this way. You can subscribe or bookmark threads:

Image

Greetings ... Peter

</OT>

Re: Use SQLite with SpiderBasic

Posted: Mon Nov 21, 2016 9:03 am
by SparrowhawkMMU
Nice little library, great work and thank you for sharing it.

I was not aware of SQLite implemented as JS. This is going to be VERY useful for me.

Re: Use SQLite with SpiderBasic

Posted: Wed Nov 23, 2016 9:23 pm
by T4r4ntul4
I dont understand how to use this...

Where is the

Code: Select all

!v_xhr.open('GET', 'sflight.sqlite', true);
loaded from?