I found a problem with SQlite on NETMF and Gadgeteer Package 2014 R2.
When I try to read a sqlite table register that contains a null value, it triggers the following message:
“Row must contain exactly as many members as the number of columns in this result set”
To replicate the issue, the following code can be used:
GHI.SQLite.Database db = new Database();
db.ExecuteNonQuery("CREATE TABLE [Samples] ( [IdField] integer PRIMARY KEY NOT NULL, [Datafield1] text, [Datafield2] text )");
db.ExecuteNonQuery("INSERT INTO Samples (IdField, Datafield1, Datafield2) VALUES (1, 'DATA 1', 'DATA 2')");
db.ExecuteNonQuery("INSERT INTO Samples (IdField, Datafield1) VALUES (2, 'DATA 1')");
GHI.SQLite.ResultSet qry1Res = db.ExecuteQuery("SELECT * FROM Samples WHERE IdField = 1"); //this is ok, because no null values on result set
GHI.SQLite.ResultSet qry2Res = db.ExecuteQuery("SELECT * FROM Samples WHERE IdField = 2"); //this throws the error, because DataField2 contains a null value on result set
Is this a bug from GHI Sqlite implementation or did i miss something? Is there a workaround so I can keep with product development?
@ Ninja - I did not handle a null return value properly in creating the result object. Regretfully there is no work around until the next release. For now, the only really way to keep is working is to look into using default values temporarily.
I also found another situation with the sqlite implementation:
There is a problem with the integer type casting. If you write a big integer (more than 4 bytes long) into an integer field on database then it will be wrongly casted to a NET int type. This is not the right behavior for a field of integer type on sqlite, because a sqlite integer can be up to 8 bytes. The only workaround i can see is to save this int values grater than 4 bytes as a string value, but this leads to problems with indexing, slower queries and bigger database size.
@ Ninja - I noticed the same behaviour. Unfortunately I had to go to strings as well. I uses “ticks” for a primary key/unique ID for the records.
In the end it’s not too bad since I send the records up to the cloud using JSON so the value gets converted to a string anyway. However, I would have prefered proper operation of SQLite and store them as 8 byte numbers.
Is there somewhere we can register it as a bug that needs to be addressed in future releases?
@ andre.m - My middle child was in Kindergarten last year and halfway thru the year they stopped sending books home and gave us a website with user id for him to do his reading.
Whether it is a good practice or not will be a big discussion, and database engines still implement NULL values.
For my scenario, the database can be shared from other platforms where the SQLite null values are admitted, so my device is unable to read directly databases with any field queried that contains NULL values. If I want to be able to read them then I have to force the fields to have a default value on an already created database. The portability is lost once you do that.
It is always better to make the implementation behave as standard as possible.