What is wrong here? StringValue vs BooleanValue

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.

Yes, that works in VS too.

Having said that, I’d leave that up to the user to do if that’s what they really want.

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:

BooleanValue –> False
StringValue –> “ABC”
Value –> “ABC”
NativeValue –> “ABC”