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
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
Here now more prospects:
Export from a selection of different SAP tables as SQL commands and import via read in an SQLite database.
Here is a look at the tables in the development perspective of SAP Workbench:
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:
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
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:
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?