macOS Catalina, IDE 2023r1.1. I just created Issue 72754 for this.
I define a column as integer in an SQLiteDatabase with no default specified. I then insert an empty string into it. On querying that column, asking for .StringValue, instead of being returned an empty string, I am returned the string “0”.
This violates the SQLite guarantee that what you put into a column, you get back.
A normal SQL column should hold 2 kinds of values: It’s defined type, or NULL
SQLite is a bit flexible to this rule, but possible upper layers aren’t (like Xojo, maybe ODBC, whatever). You should stick to the general rule.
Maybe you wish to go the other way around and define the column as text, and insert anything there? Including textual numbers that can be converted back to Ints (I guess Xojo handles it in column.IntegerValue)
From a RowSet, one may ask for a variety of values back, such as StringValue, IntegerValue, BooleanValue, and more. In the case of BooleanValue, for example, Xojo must already be asking for more information since SQLite doesn’t have a Boolean type. If instead of an empty string, I enter ‘abc’, then ‘abc’ is what I get back. If Xojo always returned 0 for StringValue when the column is integer, that would be one thing. But this is not happening.
If I don’t initialise that column at all, Xojo returns me a column of type NULL, as expected.
I suppose the other thing it could do is spot the “” and store Null instead, assuming the column allowed Null. I think I would prefer that option rather than making it look like there’s an actual value (0). If you were to average the column, for example, you would change the result by having a zero in there.
Wow. THAT is a BUG. Inconsistency. Column type int64 should return a Column.StringValue “0” for “garbage”, and Column.NativeValue could be “garbage” (as it is)
Also, a “” (Empty string) value should be stored and retrieved as is (lenght zero string) in Column.NativeValue, today it is becoming an Int64(0), (8 bytes 0), but it should end as 0 in Integer and floating point columns returning 0 for Column.IntegerValue, Column.DoubleValue, and “0” for Column.StringValue (as the column is numeric (should not hold strings), not text. But consulting Column.NativeValueType we could see type 18, String, instead of 19, Int64 of the column, for example.
Because SQLite, Xojo should have a way to differentiate a Column.Type (what was designed) and a new Column.NativeValueType (what was returned), all other DBs both could match.
Var db As New SQLiteDatabase
If not db.Connect Then Raise New DatabaseException("Not connected?", 666)
db.ExecuteSQL("CREATE TABLE test (int_val INTEGER); INSERT INTO test (int_val) VALUES (1),(''),('garbage');")
Var rs As RowSet = db.SelectSQL("SELECT * FROM test;")
Var c As DatabaseColumn
Var s As String = ""
Var row As Integer = 0
Do Until rs.AfterLastRow
row = row+1
c = rs.ColumnAt(0)
s = s + "Row "+row.ToText + " value=["+c.StringValue+"] type = "+c.Type.ToString+EndOfLine
The problem is the Xojo interface not being able to interpret SQLite and its idiosyncrasies. I also offered a solution. A new Column.NativeValueType So we could interpret inconsistent values (things users inserted into columns not matching the design)
As soon as we fix serious things, less legacy errors are accumulated. The proposition has very little impact to the current behavior, just adds more tools for users interpreting the values. Breaking changes should be documented and have points of attention in each release where they are introduced.