Android Question SQLite Insert seems not to insert

DALB

Active Member
Licensed User
Hello every pro,

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

B4X:
        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:

B4X:
    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

Expert
Licensed User
B4X:
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
B4X:
INSERT INTO pvpt(pays,villes,lat,ns,lon,ew,abrv,ajout) VALUES ('France','Aaa','','N','','W','A','1')
I would have expected
B4X:
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
B4X:
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
 
Upvote 0

DALB

Active Member
Licensed User
Thanks OliverA, I'll try.
 
Upvote 0

DALB

Active Member
Licensed User
the log return this

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

DALB

Active Member
Licensed User
Is the Array as Object rigth defined ?
 
Upvote 0

OliverA

Expert
Licensed User
Oops. Android. I always forget.
B4X:
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
 
Upvote 0

DALB

Active Member
Licensed User
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 !!!
 
Upvote 0

OliverA

Expert
Licensed User
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).
 
Upvote 0

DALB

Active Member
Licensed User
Having build my database with DB Browser SQLite and files coming from Excel, could it be a way of my problem ?
 
Upvote 0

OliverA

Expert
Licensed User
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
B4X:
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
 
Upvote 0

DALB

Active Member
Licensed User
OliverA, nothing, no mesage appears.
 
Upvote 0

DALB

Active Member
Licensed User
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.
 
Upvote 0
Top