Android Question SQLite problem

You must copy (by code) your db file from File.DirAssets to File.DirInternal, then initialize it from DirInternal

Sorry for the delayed response.
thanks a lot.

I am running the following code but when I check the database, database shows old values. LME_Price table has only one row which shows the latest data.

Sub JobDone (job As HttpJob)
Dim Query As String
Log("JobName = " & job.JobName & ", Success = " & job.Success)
If job.Success = True Then
Select job.JobName
Case "Job1"
LMEDate = sf.Mid(job.GetString,1,4)&sf.Mid(job.GetString,6,2)&sf.Mid(job.GetString,9,2)
LME_Rate = sf.Mid(job.GetString,12,6)
metxtLMEPrice.Text = LME_Rate
Query = "Update LME_Price set LastDate = ?, value = ?"
Sql1.ExecNonQuery2(Query, Array As String(LMEDate,LME_Rate))
Log("Result: " &Sql1.ExecQuery("Select value from LME_Price"))
Case "Job2"
INR_ExchRate = sf.Mid(job.GetString,12,6)
metxtLMEExch.Text = INR_ExchRate
End Select
Else
Log("Error: " & job.ErrorMessage)
ToastMessageShow("Error: " & job.ErrorMessage, True)
End If
job.Release
End Sub

The log is showing "Result: android.database.sqlite.SQLiteCursor@a51c6b4" which I am unable to understand.

Please suggest what I am doing wrong.

Please use the "Code" Tag...
and you can use
Query = "Update `LME_Price` set `LastDate` = '"& LMEDate &"', `value` = '" & LME_Rate & "'"

and use in your query: ' ' and ``

Anirban Sen said:

Log("Result: " &Sql1.ExecQuery("Select value from LME_Price"))

Click to expand...

This is the wrong line; you can not get a value that way, you need a cursor for that query.

gerredtor said:

Please use the "Code" Tag...
and you can use
Query = "Update `LME_Price` set `LastDate` = '"& LMEDate &"', `value` = '" & LME_Rate & "'"

and use in your query: ' ' and ``

Click to expand...

Could you kindly elaborate more? You mean execute:
Code:
Query = "Update `LME_Price` set `LastDate` = '"& LMEDate &"', `value` = '" & LME_Rate & "'"
Sql1.ExecNonQuery(Query)

or
Query = "Update `LME_Price` set `LastDate` = '"& LMEDate &"', `value` = '" & LME_Rate & "'"
Sql1.ExecNonQuery2(Query, Array As String(LMEDate,LME_Rate))

The first option do not update the database i.e. after running the module, when I view the database using SQLiteBrowser, I still see the old values. The second option gives error.

you can use this:

Query = "UPDATE `LME_Price` SET `LastDate` = '"& LMEDate &"', `value` = '" & LME_Rate & "'"
Sql1.ExecNonQuery(Query)

when you have problems try this:

log("UPDATE `LME_Price` SET `LastDate` = '"& LMEDate &"', `value` = '" & LME_Rate & "'")

and look for the right query:

set = SET
Update = UPDATE

usw...

Anirban Sen said:

Query = "Update LME_Price set LastDate = ?, value = ?"
Sql1.ExecNonQuery2(Query, Array As String(LMEDate,LME_Rate))

Click to expand...

This is right, I think.
Also, you're doing well to use version 2 of SQL commands (with list of arguments).

i use this Query = "UPDATE `LME_Price` SET `LastDate` = '"& LMEDate &"', `value` = '" & LME_Rate & "'"

you can use this when you fill the values in the query

i dont use ExecNonQuery2

Ok. But either way, why is the database not getting updated? Am I missing anything? Do I need to do anything to commit changes to the database? Or maybe from file.dirinternal to file.dirassets?

Anirban Sen said:

Ok. But either way, why is the database not getting updated? Am I missing anything? Do I need to do anything to commit changes to the database? Or maybe from file.dirinternal to file.dirassets?

Click to expand...

If you don't get error messages updating data, you're updating a DB but you're checking another one.

You are right. I ran a query checking for updated results and I am actually getting the updated result.
However, the database (Bhoomi.db) is in the same project folder. I am checking this database using DB Browser for SQLite. However, this database is showing old values. There is another Bhoomi.db in Files folder of the project but it also is showing the old values.
This is really perplexing!!

Try using this useful tool

can i have a screenshot from your db ? e.g. you forgot the WHERE command

Last week i had an problem with file.exist: i wanted to check for existence and used some uppercase letters in the filename - i got false for the existence, but the file was in Dirassets; then i changed all letters to lowercase and only then file.exist worked as expected - maybe your problem is the same?

rboeck said:

Last week i had an problem with file.exist: i wanted to check for existence and used some uppercase letters in the filename - i got false for the existence, but the file was in Dirassets; then i changed all letters to lowercase and only then file.exist worked as expected - maybe your problem is the same?

Click to expand...

It should not be, because if you can not access a file you can not write on it and you will get errors; he does not get errors, updating data.

Apologies to everyone. I am new to writing applications for android and also new to B4A - I think I made a silly mistake.

As was suggested by LucaMs, I am using the following code to copy the DB:
Code:
If Not(File.Exists(File.DirInternal, DBFileName)) Then
File.Copy(File.DirAssets, DBFileName, File.DirInternal, DBFileName)
End If

What I was doing was checking the database file on my desktop for the updates. Now, I think I understand that the database file was getting updated in the Android device but the changed database, do not reflect on the desktop. Is there any way that desktop database version also gets updated?

And once again, my apologies to everyone for the silly mistake.