Android Question SQLite problem

[Anirban Sen]

I am trying to connect to SQLite database which is in the project folder. Since I was exceeding the file upload limit size, I have excluded the Object folder in the zip file.
In library reference, I have included SQL. In files, I have added "bhoomi.db".
But I am unable to reference to the database "bhoomi.db" and whether I DirInternal, DirInternalCache, etc., I am always ending up getting database/table is not found.
Kindly let me know how to connect to the database and execute the sql query.

 

[LucaMs]

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

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
  
    Dim DBFileName As String = "Bhoomi.db"
    If Not(File.Exists(File.DirInternal, DBFileName)) Then
        File.Copy(File.DirAssets, DBFileName, File.DirInternal, DBFileName)
    End If
  
    If Not(Sql1.IsInitialized) Then
        Sql1.Initialize (File.DirInternal, DBFileName, False)
    End If

    Dim cursor1 As Cursor
    cursor1 = Sql1.ExecQuery ("Select * from BauxitePurchase")
End Sub

 

[Anirban Sen]

Sorry for the delayed response.
thanks a lot.

 

[Anirban Sen]

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.

 

[gerredtor]

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 ``

 

[LucaMs]

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

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

 

[Anirban Sen]

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 ``

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.

 

[gerredtor]

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...

 

[LucaMs]

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

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

 

[gerredtor]

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

 

[Anirban Sen]

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?

 

[LucaMs]

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?

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

 

[Anirban Sen]

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!!

 

[LucaMs]

Try using this useful tool

 

[gerredtor]

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

 

[rboeck]

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?

 

[LucaMs]

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?

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.

 

[Anirban Sen]

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.