SQLite alternative

1

Hello,

i have found a good altenative for SQLite that runs under IOS 13.x. It is alasql. (http://alasql.org/). It is pure Javascript and free to use. You can use the normal sql statements.You have to put the following line in the extraheader of the project and copy the alasql.js to your webserver.

<script src="nsb/alasql.js"></script>"

Here is an example which works with the localstorage very good.

Dim erg,erg1,erg2
Dim ersetz=Array()
Dim spalte=Array()
Dim erg2=Array()
Dim Feld0=Array()
Dim Feld1=Array()
Dim Feld2=Array()

Sub Main
datenbank
End Sub

Sub datenbank
spalte[0] = "city string"
spalte[1] = "population int"
spalte[2] = "sqldatum datetime"

alasql("CREATE localStorage DATABASE IF NOT EXISTS Atlas")
alasql("ATTACH localStorage DATABASE Atlas")
alasql("USE Atlas")
alasql("CREATE TABLE IF NOT EXISTS Atlas.City ("&spalte[0]&","&spalte[1]&","&spalte[2]&")")
End Sub

Function Button1_onclick()
erg = alasql("SELECT * FROM Atlas.City") 
ergebnis 
Label1.Caption = Feld1
End Function

Function Button2_onclick()
alasql("INSERT INTO Atlas.City (city,population,sqldatum) VALUES ('Moscow',10,'1980-01-01 00:00:00'),('London',15,'1990-01-01 00:00:00')")
End Function

Sub ergebnis
erg1 = JSON.stringify(erg)
erg1 = ersetzen(erg1)
console.log(erg1)
erg2 = Split(erg1,",")
z = -1
j = spalte.length
For i = 0 To erg2.length -1 Step j
              z = z + 1
              Feld0[z] = erg2[i]
              Feld1[z] = erg2[i+1]
              Feld2[z] = erg2[i+2]
Next  
End Sub


Function ersetzen(s)
ersetz[0] = "{"
ersetz[1] = "}"
ersetz[2] = "["
ersetz[3] = "]"
ersetz[4] = """"
ersetz[5] = "city:"
ersetz[6] = "population:"
ersetz[7] = "sqldatum:"
For i = 0 To ersetz.length -1 
              s = Replace(s, ersetz[i], "")
Next
ersetzen = s
End Function

If somebody know, how I get direct the values of the result “erg” without my string operations, I would be grateful.

Thanks!

best regards

uhen

2

Thanks! That is very interesting. I use SQLite heavily in an Android project.
Ryan

3

Great find! (Right after i spent two weeks porting over to indexedDB LOL)

4

Hi,
I have found a way to get the values much easier. Here is the changed code:

Dim erg=Array()

Function Button1_onclick()
  erg = alasql("MATRIX OF SELECT * FROM Atlas.City") 
  ergebnis 
  Label1.Caption = Feld0[1] 
End Function

Sub ergebnis
  For i = 0 To erg.length -1
    Feld0[i] = erg[i,0]  ' 0 first column
    Feld1[i] = erg[i,1]   '1 second column
    Feld2[i] = erg[i,2]
  Next  
End Sub

regards

uhen

5

Can you give me detailed steps on how to install this in my webapp for use from my iPhone?

Thanks

6

I have seen an example about using alasql with AppStudio at the following links

https://nac461.uk/projects/capi-demonstration/

https://www.nac461.uk/MonPMTApp/