Here is a Method that will help in some situations…
If you had a statement such as “SELECT * FROM myTABLE”
and myTABLE has BOOLEAN fields that may or may not contain data other than 0,1,True,False
this will return an expanded field list, detecting and casting “boolean” fields correctly
If ONLY works for “SELECT *” at this time, but could easily be upgraded to accept a comma list of fields
Public Function selectAstrix(srcDB as SQLiteDatabase,tablename as string) as string
Dim rs As RecordSet
Dim v() As String
Dim sql As String
dim name as string
dim kind as string
// if tablename has a "." then it is schema.tablename
v=Split(tablename,".")
sql="PRAGMA "
If v.Ubound>0 Then sql=sql+v(0)+"."
sql=sql+"table_info('"+v(v.Ubound)+"')"
// pragma [<schema>.]table_info('<tablename>')
rs=srcDB.SQLSelect(SQL)
Redim v(-1)
If srcDB.Error Then
sendMSG "Error:"+ srcDB.ErrorMessage
v.append "*"
Return "* FROM "+tablename
Else
While Not rs.eof
name=rs.field("name").StringValue
kind=rs.field("type").StringValue
If kind<>"BOOLEAN" Then
v.append(name)
Else
v.append("CAST("+name+" as Text) as "+name)
end if
rs.MoveNext
Wend
End If
sql=Join(v,",")+" FROM "+tablename // NOTE it does NOT prepend SELECT (on purpose)
Return sql
End Function
One problem with this solution… that datatype of BOOLEAN is replaced with TEXT, so you no longer “really” know it was boolean to start with.
I’d almost be inclined to think that NativeValue should give you the unaltered values from the DB and that StringValue and BooleanValue should transform it “appropriately” (which is what they seem to be doing)
That too doesn’t seem to work that way but its plausible it could be a bug or maybe a feature request
[quote=327024:@Norman Palardy]I’d almost be inclined to think that NativeValue should give you the unaltered values from the DB and that StringValue and BooleanValue should transform it “appropriately” (which is what they seem to be doing)
That too doesn’t seem to work that way but its plausible it could be a bug or maybe a feature request[/quote]
I thought NativeValue might have been the answer, but it usually doesn’t return anything at all regardless of fieldtype or contents
In my opinion StringValue should return “ABC” (as should Value and NativeValue). Or then the docs should state that the results are not conforming to SQLite.
Here is what I was able to find out:
CREATE Table test (
fld1 BOOLEAN,
}
If you do
Dim rst As RecordSet = db.SQLSelect("SELECT typeof(fld1) FROM test")
… you get a type affinity of nil.
If you then do an insert with INSERT INTO test VALUES(‘ABC’) and query it with
Dim rst As RecordSet = db.SQLSelect("SELECT typeof(fld1) FROM test")
… you get a type affinity of text.
My assumption is that the Xojo driver checks the column type with pragma table_info(‘test’), which returns “boolean”. FieldSchema is probably based on that as it indeed returns 12 (= Boolean).
But on each row for each field the type affinity should be queried to set the Value property of DatabaseField. So in my opinion these should be the results returned by DatabaseField to be consistent with the SQLite docs: