Android Question SQLite Saving value as Null

[aaronk]

Hi,

I have an odd issue and only have one customer who has this issue.

My app has been working fine on this customers device since early this year, but now he has updated to the latest version of my app and it has stopped working.

The customer has a Blackberry z30 with Cobalts side loaded running Android 4.3.

I have never used Cobalts or a blackberry so I don't know anything about it.

In my app I have made no changes in regards to how the app loads or in regards to where the customer is having the issue, so I am guessing there is something with the customers device that is causing this or something else that I am over looking.

I have cleared the app data so everything is cleared and starting from a clean install of my app. (he has even deleted the app and re-installed it again).

My app creates a SQLite database and creates a table in it.

This seems to work fine & creates the table fine.

Here is what part of my table looks like:

The code to create the table:

' ** Table: MAIN
            Data = "CREATE TABLE main (id TEXT, default_connection_id TEXT, db_version TEXT, terms_and_conditions TEXT, password TEXT"
                For k = 1 To 100
                    Data = Data & ", addon" & k & " TEXT"
                Next
                For k = 1 To 100
                    Data = Data & ", in_app_purchase" & k & " TEXT"
                Next
            Data = Data & ")"
            SQL.execNonQuery(Data) ' create the table   
            Data = ""
            Log("Table 'main' created")

            Data = "INSERT INTO main VALUES ('0','','2.0.0','0',''"
                For k = 1 To 100
                    Data = Data & ",''"
                Next
                For k = 1 To 100
                    Data = Data & ",''"
                Next
            Data = Data & ")"
            SQL.execNonQuery(Data) ' load the default data into this table

(I know it's most likely not the best way in doing the above, but it has been working for the past 5 or so years fine)

When the user opens my app for the first time I have created a terms and conditions and the user needs to press the accept button before they can continue.

When the user presses the accept button it then updates the SQLite value in the table to say they accept the terms and conditions.

This is where it gets stuck.

The Code I am using is:

SQL.ExecNonQuery("UPDATE Main SET terms_and_conditions = 'yes' WHERE id=0")
Log("Terms and Conditions Value = '" & SQL.ExecQuerySingleResult("SELECT terms_and_conditions FROM Main WHERE id=0") & "'")

When I run the above on my device (as well as 1000+ other customers devices, since no one else has reported this issue) the above works fine. So I know the code should work and should put the value as yes in the table for terms_and_conditions.

When this customer who is having the issue presses the accept button (which triggers the above code), it writes the value as null even knowing it should of written it as yes.

I then used my Android device to accept the terms and conditions in the app, and copied my database to the customers device and it reads the value as yes. So I know it's reading the values fine but won't write the values correctly.

But it is creating the database and creating the tables in the database fine.

Any ideas on what could be causing it to write the value as null even knowing the code should of save it correctly since there is 1000+ other devices running this same code without any issues ?

 

[Ed Brown]

I've had similar issues before too. In my situation it was due to the way the I was referencing the row in the WHERE clause.

I notice in your CREATE TABLE that id is declared as TEXT and then a row is INSERTed with an id of '0' but then the WHERE clause is referencing the id as simply 0. The '0' and 0 are not the same as one is a string literal and the other is an INT. SQLite's schema is not fixed (meaning that id can be declared as TEXT but you can just as easily save a FLOAT or INT in the field - the schema is more of a guide to what should be stored in a field).

Try changing the WHERE clause to WHERE id = '0' instead or change the INSERT to simply use 0.

 

[eps]

Hmm... Do you begin and end a transaction around this update?

Databases can be quite fussy at times and often work happily and then pull issues like this.

I would start and end a transaction around the update, to ensure the value is committed to the table.

It may well be that the version of sqlite on the blackberry is different to the other devices and isn't behaving like the other ones. Quite often some versions perform commits for you automatically whereas other versions may well want you to do it explicitly, possibly for performance reasons.

 

[aaronk]

Try changing the WHERE clause to WHERE id = '0' instead

I guess I could try that, but don't think that is the cause since its working fine on my device and was working on the customer device and now all of a sudden stoped working.

Do you begin and end a transaction around this update?

No. Just running those 2 statements. (UPDATE.. and SELECT..)

It may well be that the version of sqlite on the blackberry is different to the other devices and isn't behaving like the other ones.

Strange thing was it was working but now it's not working. I seem to be able to create the table and insert into the database, just not do an update.
Is there a way to check what version SQLite is installed on the blackberry ?

 

[eps]

I had some sql code which was working - but then started to fail... The database engine got a little tighter on what it expected and I just had to slightly adjust my code which then worked on older and newer devices.

SQLite seems to say it's auto-commit, but you never know.. I would put in a commit (well a begin and end transaction) - otherwise you haven't explicitly committed the data to the database and you can always rollback, so....

ETA : Android version and sqlite versions listed here.. https://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android

 

[eps]

Ah sorry - just seen that you indicated that it was working on their device but now isn't.

Without an actual device it could be hard to find out what the issue is... Has something changed on the device? Could the database be 'corrupted' in some way - have multiple values which satisfy the update or not have any at all?

 

[Widget]

Your code should have worked.
When you read the value from the table, are you 100% sure you are accessing the same database that was used to update the table? (You could be connecting to a different database or using a different directory.)

As a suggestion, in the future may want to change the Update statement to use parameters to ensure badly formatted field values won't interfere with the SQL statement. In this case, I don't see how that is possible because you are supplying a quoted string value for 'yes'.

The only other thing I can think of is maybe someone has created an update Trigger on the table that gets fired before (or after) the record is saved?
Or as someone else said, the database is corrupt so get them to email you the db so you can examine it.

 

[aaronk]

Could the database be 'corrupted' in some way

I copied my database to his device and got the same result.

have multiple values which satisfy the update or not have any at all?

This is the only update statement to update the database as the device can't access the rest of the app until this value is saved in the database.

When you read the value from the table, are you 100% sure you are accessing the same database that was used to update the table?

Yes. I am only using the 1 database. It works on my phone fine, so I know it should be working and connecting to the same database.

the database is corrupt

I got him to clear the app data which also deletes the database. It then created the database but still failed when saving. I then copied my database to the customers device and it still failed to update values.

I would understand that if it failed on my phone then it would fail on his phone as well, so it's a little tricky to work out why this is not working as it should be working. It's something to do with his phone somehow and can't work out what the cause is.

 

[Widget]

But when you create the database, you were able to create the table "Main" and insert record "0", correct? So you have write access to the table.

I noticed your Insert statement is inserting '0' into the column "terms_and_conditions". Try defaulting it to "NO" so it is a text value. Sqlite may think it is a numeric column and won't allow 'yes' to be inserted and will convert it to NULL instead. Worth a shot.

 

[eps]

I'm not saying corrupt as in malformed at the structure level or file storage level, but possibly that the data has got to the point where the code doesn't perform as expected.

So you are able to replicate the issue. That's a massive step up.

What is different from that DB to your current one then? If those are the only two things that are different between a working and non-working App.

 

[aaronk]

What is different from that DB to your current one then?

Nothing is different in the database. Both databases are the same.

I thought that the database was corrupt for some reason as well and why I copied my database over to his phone.

It seems that the update command is failed for some reason but insert is working.

 

[eps]

All data the same?

 

[Widget]

Nothing is different in the database. Both databases are the same.

I thought that the database was corrupt for some reason as well and why I copied my database over to his phone.

It seems that the update command is failed for some reason but insert is working.

Have you confirmed the phone's Sqlite is using the same version as yours and checked the global settings like internationalization? The problem may be something set globally on the phone (or a global Sqlite setting) that interferes with the update. Have you examined the Sqlite PRAGMA?
Have you tried using parameterized query for the update?

 

[aaronk]

I think I might of solved it, but just waiting for the customer to fully confirm.

I changed it in one part of my app, and the customer said it fixed it but then caused another Update statement to crash, but now changed the other update statement and waiting for the customer to confirm.

Changed it from:

SQL.ExecNonQuery("UPDATE Main SET terms_and_conditions = 'yes' WHERE id=0")

To:

SQL.ExecNonQuery("UPDATE Main SET terms_and_conditions = 'yes' WHERE id='0'")

Thanks to @Ed Brown for the suggestion.

Fingers crossed this is all that was wrong.

Thanks to all for your suggestions on this, your help has been great.