Android Question SQLite Insert seems not to insert

[DALB]

Hello every pro,

I use the code below to insert datas in a table which name is pvpt (for geography)

        synt="INSERT INTO pvpt(pays,villes,lat,ns,lon,ew,abrv,ajout) VALUES " & _
        "('" & _
        txtPays.Text & sep & _
        txtSaisieNelleVille.Text & sep & _
        txtLatitude.text & sep & _
        NS & sep & _
        txtLongitude.Text & sep & _
        EW & sep & _
        cap &  sep & _
        1 & _
        "')"
           
        Starter.SQL1.ExecQuery(synt)

where sep = "';'"
When I want to read the result by a SELECT word, I have any return, the line is not inserted, and the log prints this:

INSERT INTO pvpt(pays,villes,lat,ns,lon,ew,abrv,ajout) VALUES ('France','Aaa','','N','','W','A','1')

After 'Aaa' and After 'N' the variables are empty. They are only number in textx areas, like the other variables (All the variables are strings). A reload of a listView shows me that the line inserted is not saved.

The initialization of the database made with DB Browser is done in the Starter Module:

    If File.Exists(File.DirDefaultExternal, "pev.db") = False Then
        File.Copy(File.DirAssets, "pev.db", File.DirDefaultExternal, "pev.db")
    End If
    SQL1.Initialize(File.DirDefaultExternal, "pev.db", True)

So, it's a so tiny use that I'm not able to see where is the mistake.
Has someone an idea how to solve this ?

thanks much ...when you'll have time !

 

[OliverA]

synt="INSERT INTO pvpt(pays,villes,lat,ns,lon,ew,abrv,ajout) VALUES " & _
        "('" & _
        txtPays.Text & sep & _
        txtSaisieNelleVille.Text & sep & _
        txtLatitude.text & sep & _
        NS & sep & _
        txtLongitude.Text & sep & _
        EW & sep & _
        cap &  sep & _
        1 & _
        "')"

I don't see how the code above produces

INSERT INTO pvpt(pays,villes,lat,ns,lon,ew,abrv,ajout) VALUES ('France','Aaa','','N','','W','A','1')

I would have expected

INSERT INTO pvpt(pays,villes,lat,ns,lon,ew,abrv,ajout) VALUES ('France;Aaa;;N;;W;A;1')

Should this not have thrown an error? Instead of trying to produce the SQL dynamically, you could try

synt="INSERT INTO pvpt(pays,villes,lat,ns,lon,ew,abrv,ajout) VALUES (?,?,?,?,?,?,?,?)"
Starter.SQL1.ExecQuery2(synt, Array As Object(txtPays.Text, txtSaisieNelleVille.Text, txtLatitude.text, _
        NS, txtLongitude.Text, EW, cap, 1))

Note: Untested

 

[DALB]

Thanks OliverA, I'll try.

 

[DALB]

the log return this

java.lang.IllegalArgumentException: method anywheresoftware.b4a.sql.SQL.ExecQuery2 argument 2 has type java.lang.String[], got java.lang.Object[]

 

[DALB]

Is the Array as Object rigth defined ?

 

[OliverA]

Oops. Android. I always forget.

synt="INSERT INTO pvpt(pays,villes,lat,ns,lon,ew,abrv,ajout) VALUES (?,?,?,?,?,?,?,?)"
Starter.SQL1.ExecQuery2(synt, Array As String(txtPays.Text, txtSaisieNelleVille.Text, txtLatitude.text, _
        NS, txtLongitude.Text, EW, cap, 1))

Note: still untested

 

[DALB]

It doesn't work, BUT, if I transform ExecQuery2 by ExecNonQuery2, when I refresh the ListView, the line appears..
BUT, BUT, if I reload the app, the line is not present !!!

 

[OliverA]

Good grief, I'm not really useful today. ExecNonQuery2 is the correct method, since ExecQuery is for SELECT statements. Should have tested it (and maybe paid closer attention to it).

 

[DALB]

Having build my database with DB Browser SQLite and files coming from Excel, could it be a way of my problem ?

 

[OliverA]

Put a log statement where you copy the dB file from assets to dir external and see what happens every time you start your application

If File.Exists(File.DirDefaultExternal, "pev.db") = False Then
        File.Copy(File.DirAssets, "pev.db", File.DirDefaultExternal, "pev.db")
        Log(“copying dB from assets”)
    End If
    SQL1.Initialize(File.DirDefaultExternal, "pev.db", True)

If it is copying it every time, you could have a permission issue

 

[OliverA]

For the permission issue see https://www.b4x.com/android/forum/threads/sqlite-insert-doesnt-insert.111385/#post-694663

 

[DALB]

OliverA, nothing, no mesage appears.

 

[DALB]

OliverA, nothing, no mesage appears.

more information

You can see that after the SQL1.ExecNonQuery(....) the code refreshes the listview (on the right). We can see the added city beloww (Aaa).
But when I restart the app, this line does not exists.