Android Question Sqlite confusion

[GuyMeredith]

I am a newbie using B4A, so please forgive my confusion. I have created an app that uses Sqlite, and have recently added a column to one of my tables. The database is in the files manager tab, and if I open that file in Sqlite Studio and run the query that references that column, it works. However, when I try and run my app in debug mode, that same query generates an error as shown below.

That same query run in Sqlite is shown below:

The code is as follows:

I read in another post the following 'And also be aware that a file location in Debug mode and when running the final application is different.' I have tried running in both Debug and Release mode, with the same effect. It seems that I am accessing an older version of the database, but where might that be found? I assumed the file referenced in the files tab would be the one being used, which is the one that the query works against when run using Sqlite Studio, but this presumably is not the case as otherwise the query shouldn't fail?

Any advice would be gratefully received!

 

[GuyMeredith]

I am a newbie using B4A, so please forgive my confusion. I have created an app that uses Sqlite, and have recently added a column to one of my tables. The database is in the files manager tab, and if I open that file in Sqlite Studio and run the query that references that column, it works. However, when I try and run my app in debug mode, that same query generates an error as shown below.

View attachment 160066

That same query run in Sqlite is shown below:
View attachment 160065

The code is as follows:
View attachment 160067
I read in another post the following 'And also be aware that a file location in Debug mode and when running the final application is different.' I have tried running in both Debug and Release mode, with the same effect. It seems that I am accessing an older version of the database, but where might that be found? I assumed the file referenced in the files tab would be the one being used, which is the one that the query works against when run using Sqlite Studio, but this presumably is not the case as otherwise the query shouldn't fail?

Any advice would be gratefully received!

I have sorted out the issue, I had to delete the old version of the database on the Android device and copy the new one, so please don't bother replying!

 

[Brian Dean]

First of all - Welcome to B4X. Secondly, I am not sure that I can solve your problem, but here are some thoughts.

From what you say ("(I) ... have recently added a column to one of my tables.") and the error message ("... no such column") it seems likely that the database in that your app is using is downlevel. You say that your database is in the files tab. The files tab is intended to hold resources that you program needs, such as bitmap and text files. It is a sensible place to put a database if it is going to be read only, but if you intend to modify the database then you need to copy it to local storage (File.DirInternal). I wonder if that is where the problem lies. But in any case, are you sure that the database file you are using is the correct level? Can you try a simpler query that would confirm that the database is at the level that you expect?

I read in another post the following 'And also be aware that a file location in Debug mode and when running the final application is different.'

That is news to me. I have never had to consider file placement when switching between Debug and Release mode. But in any case, I am sure that this is not the cause of your problem.

Finally, if you want to show code in your post use the </> symbol at the top of the entry to open a box that formats the code - it makes it much easier to read. Also finally, if you right click on the Logs window in the IDE you will be able to cut and paste content into your post, again making it more readable.

 

[MicroDrie]

You mention the use of Sqlitestudio which I personally do not know. I assume that this uses an SQL file, while the log speaks of an in-memory database. I do not know what you want: either you use an in-memory database, on a SQLite database stored in a file. With DB Browser for SQLite you can see how to create a table in SQLite. The error message speaks of a missing column. With an in-memory SQLite database you have to adjust the creation of the table, if you work with a SQLite database in a file, then you probably did not save the adjustment, or you are using an old version.

 

[walt61]

Welcome to B4X! Here goes:

1. First of all, you'll have to copy your database from File.DirAssets (which is the Files directory in your project) to another place, e.g. File.DirInternal. Opening the file straight away from DirAssets won't work, as the contents of that directory are within the apk, which is actually a zipped container.

If File.Exists(File.DirInternal, "my.db") = False Then File.Copy(File.DirAssets, "my.db", File.DirInternal, "my.db") ' Replace "my.db" with the actual filename
SQLDataAccess.Initialize(File.DirInternal, "my.db", False)

2. Your code will have to deal with structural changes; if you make changes to the database in your Files directory and then run the app, you'll need to apply these changes to the file in DirInternal (or overwrite the database completely).

3. Don't include values in your query, but use parametrised queries (see https://www.b4x.com/android/forum/threads/b4x-code-smells-common-mistakes-and-other-tips.116651/). Use the question mark as placeholders, and pass the values to DBUtils.ExecuteMemoryTable in its StringArgs parameter, e.g.:

    Dim args(1) As String
    args(0) = ProgramID
    DBUtils.ExecuteMemoryTable(SQLDataAccess, "SELECT ... WHERE wep.program_id=?", args, 0)

 

[GuyMeredith]

Thanks very much to all who replied, I appreciate all of the advice! I will certainly adopt the suggestions about posting my code to make it more readable, and have applied the other changes suggested in respect of parameterised queries and applying the changes to the file in DirInternal.