Android Question SQLite data types

RB Smissaert · May 1, 2018

For various reasons I need the data types of the columns of a cursor produced by a SQLite query.
There doesn't seem an easy way to do this and the best I have come up with sofar is this:

B4X:

Sub GetCursorDataTypes(Curs As Cursor) As String()

 Dim c As Int
 Dim p As Long
 Dim r As Reflector 'needs Reflection library
 Dim iColumns As Int
 Dim lRows As Long
 Dim iResult As Int

 iColumns = Curs.ColumnCount
 lRows = Curs.RowCount
 r.Target = Curs
 Dim arrDataTypes(iColumns) As String

 For c = 0 To iColumns - 1
  For p = 0 To lRows - 1
   Curs.Position = p
   iResult = r.RunMethod2("getType", c, "java.lang.int")
   If iResult = 2 Then
    arrDataTypes(c) = "R"
    Exit 'exit row loop and move to next column
   End If
   If iResult = 4 Then
    arrDataTypes(c) = "B"
    Exit 'exit row loop and move to next column
   End If
   If iResult = 3 Then
    arrDataTypes(c) = "T"
    'no exit yet as may find a Real value?
   End If
   If iResult = 1 Then
    arrDataTypes(c) = "I"
    'no exit yet as may find a Real value?
   End If
  Next
  If arrDataTypes(c) = 0 Then
   arrDataTypes(c) = "T" 'or should we do a Null type?
  End If
 Next

 Return arrDataTypes

End Sub

Sofar this seems to work OK, but not fully tested yet.
Has anybody any better ideas or improvements?

RBS

MarkusR · May 3, 2018

this sub getType exist twice in the source code mentioned by oliver.

For sure the code I posted first gives different values for the different rows.

you mean a mix of integer & null , float & null , string & null or mix of all 0,1,2,3,4 in one column?
is it possible for you to set the database fields to not allow null for this project?

RB Smissaert · May 3, 2018

[QUOTE}
is it possible for you to set the database fields to not allow null for this project?
[/QUOTE]

I could clear up the database, so update faulty values, but I am still interested to get the default output data type of columns of a query.
Letting the user specify the output data types is not a bad option, eg:

select IntField, RealField, TextField from TestTable>>>>I,R,T

RBS

JMW · May 6, 2018

What about the typeof() function?
SELECT typeof(X), typeof(Y) FROM ...

Mahares · May 7, 2018

JMW said:
What about the typeof() function?
SELECT typeof(X), typeof(Y) FROM ...

I think the typeof(X) returns the schema column type, but the fellow in this thread is looking for the actual data output stored in the column. For instance, you may have a column type as TEXT, but it can hold an integer say 999. He is looking for INTEGER

RB Smissaert · May 7, 2018

Mahares said:
I think the typeof(X) returns the schema column type, but the fellow in this thread is looking for the actual data output stored in the column. For instance, you may have a column type as TEXT, but it can hold an integer say 999. He is looking for INTEGER

No, I am interested in the Default datatype of the result column, as in the native sqlite3_column_type function.
So, this will the same result for all the rows, no matter what the actual value is for that column row.
It will be complex to take a SQL statement and use typeof to get the result column default data type, although if you could do
that it would give the desired result.

Just wonder now if I could get this information with the explain statement and will have a look at that.

RBS

Lee Gillie CCP · May 7, 2018

I have something that pulls schema info from a PRAGMA info cursor. Basically a tiny portion of my code that runs at startup to see if auto-upgrades are needed for the app database. This is a snippit from how I use it:

B4X:

            SQL = "PRAGMA table_info(" & TableName & ");"
            crs = GlobalModule.Delivery.ExecQuery(SQL)
            Dim thisTable As Map
            thisTable.Initialize
            For row = 0 To crs.RowCount-1
                crs.Position = row
                Dim slfd As SQLiteFieldDefinition
                slfd.Initialize
                slfd.FromPRAGMACursor(crs)
                thisTable.Put(slfd.FieldName,slfd)

Here is the complete code from my SQLiteFieldDefinition class:

B4X:

Sub Class_Globals
    Public ColumnID As Int
    Public FieldName As String
    Public FieldType As String
    Public IsNotNull As Int
    Public DefaultValue As Object
    Public PrimaryKey As Int
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
    ColumnID = 0
    FieldName = ""
    FieldType = ""
    IsNotNull = 0
    DefaultValue = Null
    PrimaryKey = 0
End Sub

Public Sub FromPRAGMACursor( crs As Cursor )
    ColumnID = crs.GetInt("cid")
    FieldName = crs.GetString("name")
    FieldType = crs.GetString("type")
    IsNotNull = crs.GetInt("notnull")
    DefaultValue = crs.GetString("dflt_value")
    PrimaryKey = crs.GetInt("pk")
    'Log("cid=" & ColumnID & ", name=" & FieldName & ", type=" & FieldType & ", notnull" & IsNotNull & ", dflt_value=" & DefaultValue & ", pk=" & PrimaryKey)
End Sub

Hope this helps - Lee

OliverA · May 7, 2018

RB Smissaert said:
sqlite3_column_type

I don't think that function does what you think it does. I'm pretty sure that is the function used by Android' getType method. I'm guessing here, but I'm pretty sure. I think the function that you are looking for is something like SQlite3's sqlite3_column_decltype (https://www.sqlite.org/c3ref/column_decltype.html). The documentation for that function (..._decltype) give a clue as to why the sqlite3_column_type method works as it does

SQLite uses dynamic run-time typing. So just because a column is declared to contain a particular type does not mean that the data stored in that column is of the declared type. SQLite is strongly typed, but the typing is dynamic not static. Type is associated with individual values, not with the containers used to hold those values. From the limited testing I have done on the getType method from Android is that it can change from Null to another value. Once it changes to another non-null return value, it seems to stick for the remainder of the result set walk through.

Please note that one thing to keep in mind is that SELECT statement's result column does not have to be an actual column of a table, it can be an expression. For that, there is no declared column type and even the sqlite3_column_decltype would be of no use (see link above for an example).

Mahares said:
typeof(X) returns the schema column type

It does not. See examples on SQlite3's site (https://www.sqlite.org/datatype3.html#column_affinity_behavior_example) and notice the return values for columns containing null values.

Lee Gillie CCP said:
I have something that pulls schema info from a PRAGMA info cursor

This would be the only way to go (since there is no access to sqlite3_column_decltype in Android), but that does not cover any result columns that are not made strictly from actual columns that have been defined in a table.

Android Question SQLite data types

RB Smissaert

Well-Known Member

MarkusR

Well-Known Member

RB Smissaert

Well-Known Member

JMW

Member

Mahares

Expert

RB Smissaert

Well-Known Member

Lee Gillie CCP

Active Member

OliverA

Expert

Similar Threads

Android Question SQLite data types

Well-Known Member

Well-Known Member

Well-Known Member

Member

Expert

Well-Known Member

Active Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency