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.
Well except that Xojo does a bunch of magic behind the scenes to that you actually have column types. So what you’re describing to me is not surprising at all.
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.
That is what I would normally do. In this case I am re-purposing an unused column rather than having to have the next release of my app update the user’s table (which would be perfectly do-able).
Mostly Xojo returns the right string in this situation, just that in one case it does not.
I’m not at my computer right now but I’m curious what happens if you call IsNumeric on an empty string. If it returns True, that would probably be your answer.
Var resfl As Boolean
resfl = IsNumeric ("")
System.DebugLog ("Result is: " + if (resfl=True, "True", "False"))
break
.
Resulting output:
14:49:18 : zzz Launched
Result is: False
14:49:40 : zzz Ended
Well that shoots that theory…
The database contents are ok, it looks like the bug is interpreting that “” is 0 because is an ‘Integer’ column.
Likely because a Variant is used.
I don’t understand how Variant could be the problem, I did the following test trying to get 0 for StringValue:
Var test As Variant = 1
Var test2 As Integer = test.Type
test = ""
Var test3 As String = test.StringValue
var test4 as integer = test.Type
but test3 is correct as ‘’ and test4 is 8 (correct type).
Could Xojo be pulling information from the Column, see that is integer then assign 0 to the Variant (because is integer) so the Variant.StringValue can only be 0 in this case?
Anyway, I hope they fix this problem. Tested with Xojo2018r3 and it shows the same problem.
Edit: I don’t use Variants much, so every time I can learn a little bit more I do some tests. Sorry if I’m missing something obvious.
As I said, I think that Xojo is taking the column type in consideration, and at a standard SQL point of view, It is not exactly wrong, some would say the opposite.
And the NativeValue of “” is coming as an Int64(0)
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.
It looks like you can store a blank space and you will get a blank space back, depending on your needs may (or not) be a good option.
Yes. Any string of non-zero length works as it should.
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
rs.MoveToNextRow
Loop
MessageBox s
Please use pure SQLite and test what is allowed and what is not.
Xojo should do the same and not try to make SQLite behave like standard SQL.
Read above, it’s pure SQLite.
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)
Personally, if the behavior has been this way since 2018, I’d prefer they document the behavior.
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.