Sorry Rick, as often I don’t understand what you are saying.
Are you saying that returning 0 when we saved ‘’ to the SQLite is a bug, or the bug is not returning 0 if the column is integer and we saved ‘[garbage]’
From what I understand from SQLite docs: Datatypes In SQLite, it is a bug in Xojo to interpret that an empty string in the database should return 0 if we use .StringValue even if we define the column as Integer.
Let’s see if Xojo changes the behavior or changes the documentation.
I’m saying what I said. The subject is kind of extensive. If you did not understand, it will take some time for a kind of large explanation about SQL, Columns and their values (including the native values, the raw thing), etc to detail. Read about columns and types, then reread what I wrote. What I wrote was very extensive, I think that if reread it carefully, you may understand.
The irony is that I believe in initialising variables. So I duly initialised the integer to an empty string (‘’) as that was the default I wanted in the database. Now, had I NOT done so, the .StringValue returned for the empty (null) integer would have been what I wanted - an empty string.
NULL columns renders as “” when rendered as Column.StringValue
And renders as Nil using Column.Value
Int64 Columns not containing binary integers but strings should render as
This is not how SQLite works and what we are discussing is an interface to SQLite.
This is exactly how it works when interfacing
Xojo lacks tools to handle better native values (instead of fixed declared columns, as Xojo, and maybe all the rest of the DBs works). So I made a suggestion.
Thank you Rick, I still think you are confusing SQLite with regular SQL, but hey maybe not and I just don’t understand what you are trying to say.
After reading:
https://www.sqlite.org/datatype3.html
and
https://www.sqlite.org/stricttables.html
it looks like SQLite is way different that other SQL that only use ‘strict tables’.
But don’t worry, it doesn’t matter what I understand or what you are trying to say but what Xojo does to fix the problem.
Edit: this is what stands out with SQLite:
Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.
SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.
From that I understand that Xojo should not return 0 for StringValue even if the column is integer if the table created is not with the STRICT modifier.
Well, I’m not trying, I said. What people will do with it is what you said, what they want. 
What you may be missing is the method NativeValue of the Column
Values should be normalized to the column types or crashes could occur. BUT! If you want a more strict control on what the DB is doing, like to know if that Int64 0 origin was something else different of a string of 8 bytes 0, like a empty string, you can check the NativeValue
What I requested: more info
Beyond NativeValue, having a NativeValueType would be great.
Because, for example,
Column.Type = 19 // says the column is Int64
Column.Value = 0
Column.NativeValue = "" // In SQLite this is possible
Column.NativeValueType = 18 // It's a String
Column.Type = 19 // says the column is Int64
Column.Value = 0
Column.NativeValue = ""
Column.NativeValueType = 0 // It's a Null type
Column.Type = 19 // says the column is Int64
Column.Value = 654
Column.NativeValue = "!°rh‘r„@" // binary double representing value 654.3210
Column.NativeValueType = 7 // type Double
Oh you are not talking about the original problem but a new feature you want Xojo to have?
OMG. I will just refrain to answer that, it could sound offensive.
Oh. I made a mistake in the NULLed one. Columns have their type or NULL (In Xojo, Nil)
Alternative values can be read from NativeValue + NativeValueType
Column.Type = 19 // says the column is Int64
Column.Value = 0
Column.NativeValue = "" // In SQLite this is possible
Column.NativeValueType = 18 // It's a String
Column.Type = 19 // says the column is Int64
Column.Value = 0
Column.NativeValue = "garbage" // In SQLite this is possible
Column.NativeValueType = 18 // It's a String
Column.Type = 19 // says the column is Int64
Column.Value = 123
Column.NativeValue = "123.4" // In SQLite this is possible
Column.NativeValueType = 18 // It's a String
Column.Type = 19 // says the column is Int64
Column.Value = Nil
Column.NativeValue = ""
Column.NativeValueType = 0 // It's a Null type
Column.Type = 19 // says the column is Int64
Column.Value = 654
Column.NativeValue = "!°rh‘r„@" // binary double representing value 654.3210
Column.NativeValueType = 7 // type Double
…with the overlay of Xojo. You guys need to remember that Xojo has all this “helper” code to make the underlying SDKs behave the way that Xojo thinks they should, so that they conform to the Xojo ecosystem.
RowSet (and recordset before it) uses variants under the hood. IIRC, defining a column in SQLIte as an integer means that the value gets automagically cast to an integer when it’s pulled out and that calling stringvalue converts it into a string representation of that number.
I gotta ask though… why the heck did you define a column as integer if you wanted it to return a string? If you wanted a non-number value, use NULL. That’s how every other relational database on the planet works and you’re doing yourself a disservice doing it this way if you ever think that your product could need to scale to something bigger.
I’m just re-purposing an unused column instead of adding a new TEXT column. Easier in this instance than bumpng the schema version number and adding code to add a new column to the user’s schema.
But if in Xojo I put ‘abc’ into an integer SQLite column, I get ‘abc’ back. Same (apparently) for a string consisting of a single space. It’s only an empty string that gets converted to ‘0’. Based on what you say here, I would have expected any string to cause ‘0’ to be returned.
Something like this?
Var test As Variant = ""
Var test2 As String = Str(CType(test, Integer)) // 0
Yes, that gives us 0.
But why this gives us ‘abc’ from the database and not 0?
Var test As Variant = "abc"
Var test2 As String = Str(CType(test, Integer)) // 0
Maybe the underlying code is something like this?
Var test As Variant = "abc"
Var test2 As String
If test.IsNumeric Or test.StringValue.Length = 0 Then
test2 = Str(CType(test, Integer))
Else
test2 = test.StringValue
End If
From what I read here:
SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.
is that SQLite doesn’t care if the column is Integer or Text, if the value is Text, even if the column is Integer, the value itself is Text and needs to be treated that way. Variants are great for this (I think). So
Var test As Variant = 1 //assign a number to variant
Var test2 As String
test = "" //assign empty string
test2 = test.StringValue //we get empty string
test = 123
test = "abc"
test2 = test.StringValue //we get 'abc'
I agree 100% with this. It is not a good practice to define a column Integer and assign Text even if SQLite works this way, just for the reason that if you want the product to scale we will get problems from this design. But a bad practice (define Integer column and assign a text value), if it is allowed by SQLite and Xojo saves the empty string to the database, should pull an empty string from it.
Since SQLite 3.37.0 we can have Strict tables, so in the event of planning for the future (instead of re-purposing an unused column) we can ‘force’ SQLite to work as any other relational database on the planet. Trying to add a string (even an empty one) to an Integer column will get an exception:
cannot store TEXT value in INTEGER column Team.City
Strict is a good option to have to design new SQLite project that can potentially grow to other database engines.
In short:
Yes, meaning that for SQLite, rather than asking about the column’s defined type, the interface should be asking what the affinity for that column in that row is.
Remember, I can store an integer in the column in one row, a string in the next row, and a float in the next row. The query has to be done on a row-by-row basis.