Android Question SQLIte INSERT doesn't insert

[DALB]

Hello everyone.

It's about a little script with SQLite.

In the starter module I wrote this:

    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)

In an activity module, I try to insert datas in a table (pvpt) like this:

        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 = "','" and every data is a string

But... when I want to read the result by a SELECT syntax to load a listView, the new line doesn't appear, and the log prints this:

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

where the datas after Aaa and N are empty (they represent parameters as numbers).

So, in a so little script, do you see a mistake which can help me to see how to solve this ?

Thanks...when you'll have time !

 

[Mahares]

So, in a so little script, do you see a mistake which can help me to see how to solve this ?

It is a lot simpler if you use a parameterized query in your SQL statement like this:

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

If you are not using runtime permissions ( depending on manifest), then you are ok with:
File.DirDefaultExternal. But if you are using runtime permissions, you have to use :
If File.Exists(Starter.rp.GetSafeDirDefaultExternal(""), "pev.db") = False Then

 

[DALB]

Thank you for answering
I apologize, but I doubled this question :

When I put this just before the SQL1.Exec...

If File.Exists(Starter.rp.GetSafeDirDefaultExternal(""), "pev.db") = False Then
            Log("ok permission")
        End If

no log appears

Putting the Erel's code below

Sub Activity_Create(FirstTime As Boolean)
   Dim rp As RuntimePermissions
   If rp.GetSafeDirDefaultExternal("") <> File.DirDefaultExternal Then
     Log("Please post: " & rp.GetSafeDirDefaultExternal(""))
     Log(File.DirDefaultExternal)
   Else
     Log("Nothing interesting")
   End If
End Sub

Log "Nothing intereting" appears

 

[Erel]

I'm not sure what is the question however you should never ever use File.DirDefaultExternal.
In most cases you should use File.DirInternal.

 

[DALB]

..yes, but it does work too.
If I save with Starter.SQL1.ExecN...., I can see my line in the Listview which is loaded from the SQLite Database.
But, if I shut down the app (in debug mode) and restart it, the line doesn't appear. This means that SQLite hasn't saved the datas.

So, Have I done something wrong ? The code seems so clear !

 

[OliverA]

Just for testing purposes, use File.DirInternal for your database and see what happens. I'm guessing you are copying the file from Assets if it does not exist (in this case, after modification, File.DirInternal). Once copied, use the File.DirInternal database and go from there. See if you are having the same issues. Also, are you using a real device for testing or an emulator?

 

[DALB]

With DirInternal, the problem remains. I have no saved datas in the SQLite database.
I work with real devices, Samsung S5, Yoga YT2 and Huawei Y520.

I really don't understand why the datas appears in the listview after the refresh action because they come from the database, ans why when I restart the app, this line doesn't appear as if SQLite has never saved it ?

 

[OliverA]

You'll need to post your project for any further help (in my opinion). Remove any unnecessary functionality (if necessary) up to the point that you still can reproduce your issue. Do a Tools->Clean Project and then a File->Export as Zip and post it here.

 

[DALB]

Thanks OliverA

Here is the file

 

[OliverA]

Something went wrong with that export, the OrlojPt8_11SqliCorr.b4a file is missing (or whatever the file with the .b4a ending is named).

 

[OliverA]

I think I found the issue (even without running the App). You open the db in Starter, that's a good thing. One of your problems (I just read this far) in Villes.bas is that you Re-Copy the pev.db file from Assets to DirInternal and then re-initialize it (lines 325-326). This is where your data loss occurs. Reading more through the code, it looks like you were on the right track by using Starter to open the db and using Starter.SQL1 to access your db. You just forgot to modify this one section (as you have done with the others in Villes.bas). So it was just an oversight of adapting the code initialize the db in Starter.

 

[DALB]

Hey, yes, I've seen.

One line was not deleted:

File.Copy(File.DirAssets,"pev.db",File.DirInternal, "pev.db")'copie le pays

I've tried again, with DirInternal, and it seems to work.
I wait for many entries and I'll see if it works right continuously.

I presume the problem is solved...just wait a little bit. I'll come back to confirm that.

 

[DALB]

Yes, OliverA, I wrote when you answer me for the same indication.
Thank you for your help ! And I apologize for spending your time !

 

[OliverA]

Don't forget to to delete the line below that line!

    Starter.SQL1.Initialize(File.DirInternal, "pev.db", True)

And I apologize for spending your time !

No need to apologize. That's what this forum is here for (help).