Android Question I write a test program to show that same SQLite files, some file can’t be read well. I need help.

[AndyChang]

Dear Sir
I face a very strange problem. With the same program, some SQLite data file can be read well, but some are fail. Almost every four or five files will have one file fail to be read. It make me crazy.

I write a test program to show how it happened.
I prepare four files to be read. (a.db / b.db/c.db/d.db) I found it fail when read c.db

Then I create a new file e.db with all the same contains with c.db

124 SQLname = "c.db" <------- I change file name from c.db to e.db then everything goes nice
125 OpenSQLitefile(SQLname)
126 Dim Cursor1 As Cursor If I use file c.db Then error happened.
127 Dim recordID As Int The data in c.db are totally the same as e.db
128 Dim TableText As String
129 TableText = "c1" <------- I change table name from c1 to e1

There will be OK. I try to change the filename from a.db to c.db in program. Then I will fail reading c.db The result points to a fact that c.db is a bad file.

But I can check c.db by using a software package DB Browser. It can work well in that package.

I prepare two ZIP files to show the problem.

One read a/b/c/d and fail.

One read a/b/e/d and success

Please help me to see if there is any mistake I made in my test program.

Andy

 

[Erel]

1. Please post error messages and logs as text instead of screenshots.

2. Tip:

Dim FirstTime As Boolean : FirstTime = True

Can be written like this:

Dim FirstTime As Boolean = True

3. Why are you initializing the SQL object twice?

4. You can use DBUtils.CopyDBFromAssets to copy the database.

5. The FirstTime variable doesn't do anything useful. It will always be True at that point.

6. You should use parameterized queries:
Change:

Cursor1 = SQL1.ExecQuery("SELECT * FROM " & TableText & " WHERE ID = " & recordID)

To:

Cursor1 = SQL1.ExecQuery2("SELECT * FROM " & TableText & " WHERE ID = ?", Array As String (recordID))

I ran your program (test_c.zip) after fixing the initialization issue and it worked properly.

 

[AndyChang]

Dear Erel
Thanks a lot for your kindness to help even in the weekend.
I will follow your advice and test the program. Thank you.
Andy

 

[AndyChang]

Dear Erel
I can read c.db correctly now. Thanks you very much.
Andy

 

[Computersmith64]

6. You should use parameterized queries:
Change:

Cursor1 = SQL1.ExecQuery("SELECT * FROM " & TableText & " WHERE ID = " & recordID)

To:

Cursor1 = SQL1.ExecQuery2("SELECT * FROM " & TableText & " WHERE ID = ?", Array As String (recordID))

Or even better:

Cursor1 = SQL1.ExecQuery2($"SELECT * FROM ${TableText} WHERE ID=?"$, Array As String(recordID))

'OR if recordID is an Int (so guaranteed not to have any special chars in it):

Cursor1 = SQL1.ExecQuery($"SELECT * FROM ${TableText} WHERE ID=${recordID}"$)

- Colin.

 

[Erel]

OR if recordID is an Int (so guaranteed not to have any special chars in it):

It is still better to use ExecQuery2.
1. It is a good habit.
2. It is safe from SQL injection. It is true that if recordID is an Int then it is not relevant but good practices protect us from future changes.

For example:

$"SELECT * FROM ${TableText} WHERE ID = ${ListOfObjects.Get(i))}"$

Or later you decide to make ID a string instead of an int.

 

[Computersmith64]

It is still better to use ExecQuery2.
1. It is a good habit.
2. It is safe from SQL injection. It is true that if recordID is an Int then it is not relevant but good practices protect us from future changes.

For example:

$"SELECT * FROM ${TableText} WHERE ID = ${ListOfObjects.Get(i))}"$

Or later you decide to make ID a string instead of an int.

Yeah - I ran into problems recently because I got carried away using smart string literals & had one query where one of the values being inserted had an apostrophe, so it's definitely something you need to be careful with. I do still use smart string literals in queries dealing with numeric fields/values, but have changed all those with text values back to being parameterized.

I do like the convenience of the smart string literals though!

- Colin.