Use SQLite with SpiderBasic

Created a nice software using SpiderBasic ? Post you link here !
Stefan Schnell
Posts: 46
Joined: Tue Dec 01, 2015 8:17 am
Contact:

Use SQLite with SpiderBasic

Post 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
Fred
Site Admin
Posts: 1506
Joined: Mon Feb 24, 2014 10:51 am

Re: Use SQLite with SpiderBasic

Post 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())
Stefan Schnell
Posts: 46
Joined: Tue Dec 01, 2015 8:17 am
Contact:

Re: Use SQLite with SpiderBasic

Post 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
Fred
Site Admin
Posts: 1506
Joined: Mon Feb 24, 2014 10:51 am

Re: Use SQLite with SpiderBasic

Post 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 !
Stefan Schnell
Posts: 46
Joined: Tue Dec 01, 2015 8:17 am
Contact:

Re: Use SQLite with SpiderBasic

Post 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
Stefan Schnell
Posts: 46
Joined: Tue Dec 01, 2015 8:17 am
Contact:

Re: Use SQLite with SpiderBasic

Post 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
Leonhardt
Posts: 20
Joined: Wed Feb 26, 2014 9:41 am

Re: Use SQLite with SpiderBasic

Post by Leonhardt »

mark
User avatar
Peter
Posts: 1086
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080
Contact:

Re: Use SQLite with SpiderBasic

Post 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>
User avatar
SparrowhawkMMU
Posts: 281
Joined: Wed Aug 19, 2015 3:02 pm
Location: United Kingdom

Re: Use SQLite with SpiderBasic

Post 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.
User avatar
T4r4ntul4
Posts: 132
Joined: Wed May 21, 2014 1:57 pm
Location: Netherlands
Contact:

Re: Use SQLite with SpiderBasic

Post by T4r4ntul4 »

I dont understand how to use this...

Where is the

Code: Select all

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