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

Use SQLite with SpiderBasic

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

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: 1018
Joined: Mon Feb 24, 2014 10:51 am

Re: Use SQLite with SpiderBasic

by Fred Sat Dec 26, 2015 5:24 pm

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

Re: Use SQLite with SpiderBasic

by Stefan Schnell Mon Dec 28, 2015 6:28 am

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: 1018
Joined: Mon Feb 24, 2014 10:51 am

Re: Use SQLite with SpiderBasic

by Fred Mon Dec 28, 2015 9:36 am

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

Re: Use SQLite with SpiderBasic

by Stefan Schnell Tue Dec 29, 2015 5:55 am

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

Re: Use SQLite with SpiderBasic

by Stefan Schnell Thu Dec 31, 2015 7:02 am

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

by Leonhardt Sun Nov 20, 2016 11:15 am

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

Re: Use SQLite with SpiderBasic

by Peter Mon Nov 21, 2016 7:32 am

<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: 267
Joined: Wed Aug 19, 2015 3:02 pm
Location: United Kingdom

Re: Use SQLite with SpiderBasic

by SparrowhawkMMU Mon Nov 21, 2016 9:03 am

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: 117
Joined: Wed May 21, 2014 1:57 pm
Location: Netherlands

Re: Use SQLite with SpiderBasic

by T4r4ntul4 Wed Nov 23, 2016 9:23 pm

I dont understand how to use this...

Where is the
Code: Select all
!v_xhr.open('GET', 'sflight.sqlite', true);

loaded from?
Return to Showcase

Who is online

Users browsing this forum: No registered users and 2 guests