Android Question SQLite data types

[RB Smissaert]

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:

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

 

[XbNnX_507]

Public Sub GetResultsetTypes ( xrs As ResultSet ) As List
    Dim DateTimeMethods As Map
    DateTimeMethods = CreateMap(91: "getDate", 92: "getTime", 93: "getTimestamp")
    Dim rows(xrs.ColumnCount) As Object
    Dim jrs As JavaObject = xrs
    Dim rsmd As JavaObject = jrs.RunMethod("getMetaData", Null)
    Dim DataTypes As List
    DataTypes.Initialize
    Do While xrs.NextRow
        For i = 0 To rows.Length-1
            Dim ct As Int = rsmd.RunMethod("getColumnType", Array(i + 1))
            'check whether it is a blob field
            If ct = -2 Or ct = 2004 Or ct = -3 Or ct = -4 Then
                ' BLOB OR BINARY
                DataTypes.Add( "BINARY OR BLOB")
            Else if ct = 2 Or ct = 3 Then
                'NUMERIC OR DECIMAL
                DataTypes.Add("NUMERIC OR DECIMAL")
            Else If DateTimeMethods.ContainsKey(ct) Then
                ' DATE TIME TIMSESTAMP
                DataTypes.Add("DATE OR TIME")
            Else if ct = 4 Then
                ' INTEGER
                DataTypes.Add("INTEGER")
            Else
                DataTypes.Add("TEXT")
            End If
        Next
    Loop
    Return DataTypes
End Sub

Borrowed from JRDC.

 

[RB Smissaert]

Public Sub GetResultsetTypes ( xrs As ResultSet ) As List
    Dim DateTimeMethods As Map
    DateTimeMethods = CreateMap(91: "getDate", 92: "getTime", 93: "getTimestamp")
    Dim rows(xrs.ColumnCount) As Object
    Dim jrs As JavaObject = xrs
    Dim rsmd As JavaObject = jrs.RunMethod("getMetaData", Null)
    Dim DataTypes As List
    DataTypes.Initialize
    Do While xrs.NextRow
        For i = 0 To rows.Length-1
            Dim ct As Int = rsmd.RunMethod("getColumnType", Array(i + 1))
            'check whether it is a blob field
            If ct = -2 Or ct = 2004 Or ct = -3 Or ct = -4 Then
                ' BLOB OR BINARY
                DataTypes.Add( "BINARY OR BLOB")
            Else if ct = 2 Or ct = 3 Then
                'NUMERIC OR DECIMAL
                DataTypes.Add("NUMERIC OR DECIMAL")
            Else If DateTimeMethods.ContainsKey(ct) Then
                ' DATE TIME TIMSESTAMP
                DataTypes.Add("DATE OR TIME")
            Else if ct = 4 Then
                ' INTEGER
                DataTypes.Add("INTEGER")
            Else
                DataTypes.Add("TEXT")
            End If
        Next
    Loop
    Return DataTypes
End Sub

Borrowed from JRDC.

Thanks, will have a look at that.

RBS

 

[OliverA]

Borrowed from JRDC.

This is based on JDBC, which @RB Smissaert is not using.

@RB Smissaert
I guess you're treading getType as if it could return a different result per row, such as getFloat could return a different floating number per row. I've not found anything, but what if it acts more like getColumnName? Then you would not need the inner loop. As is, for a cursor with many columns/rows, that is a lot of looping just to determine the type of the column(s).

Reference: https://developer.android.com/reference/android/database/Cursor

 

[RB Smissaert]

but what if it acts more like getColumnName?

 

[RB Smissaert]

Sorry, went off too early.

but what if it acts more like getColumnName?

Yes, would be nice if there was something like GetColumnType. Looks like a serious omission to me.

As is, for a cursor with many columns/rows, that is a lot of looping just to determine the type of the column(s).

Yes, there could be serious overhead, but can't see a way to avoid that.
I made the option a well for the user to specify the column output types, so in that case this is not needed.

RBS

 

[MarkusR]

me thought about getting table infos from db itself as query

Field1
INTEGER
Field2
TEXT
Field3
BLOB
Field4
REAL
Field5
NUMERIC

Sub SQL_Click
    
    Log("SQL_Click")
    
    'PRAGMA table_info(Test)

    File.Copy(File.DirAssets,"test1.db",File.DirInternalCache,"test1.db")
    
    Dim sql1 As SQL
    sql1.Initialize(File.DirInternalCache,"test1.db",False)
    
    Dim Cursor As Cursor
    Cursor = sql1.ExecQuery("PRAGMA table_info(Test)")
    For i = 0 To Cursor.RowCount - 1
        Cursor.Position = i
        Log(Cursor.GetString("name"))
        Log(Cursor.GetString("type"))
    Next
    
End Sub

 

[RB Smissaert]

me thought about getting table infos from db itself as query

Sub SQL_Click
   
    Log("SQL_Click")
   
    'PRAGMA table_info(Test)

    File.Copy(File.DirAssets,"test1.db",File.DirInternalCache,"test1.db")
   
    Dim sql1 As SQL
    sql1.Initialize(File.DirInternalCache,"test1.db",False)
   
    Dim Cursor As Cursor
    Cursor = sql1.ExecQuery("PRAGMA table_info(Test)")
    For i = 0 To Cursor.RowCount - 1
        Cursor.Position = i
        Log(Cursor.GetString("name"))
        Log(Cursor.GetString("type"))
    Next
   
End Sub

But how would that work if it is a complex SQL with joins and subqueries?

RBS

 

[MarkusR]

But how would that work if it is a complex SQL with joins and subqueries?RBS

i don't know.

Yes, would be nice if there was something like GetColumnType.

i would try a wish at "Bugs & wishlist"

my idea was copying all tables infos into a list with a user defined type (tablename,fieldname,type) once
and a sub that give you the field type from table & fieldname.

 

[keirS]

Sorry, went off too early.



Yes, would be nice if there was something like GetColumnType. Looks like a serious omission to me.

Nope. GetColumnType wouldn't work because a column can contain more than one data type as Null is a data type.

 

[MarkusR]

Nope. GetColumnType wouldn't work because a column can contain more than one data type as Null is a data type.

integer/float can't be null
string/text can only be ""
date is long = 0
but i know what you will say null is the "content" of the field

 

[OliverA]

Nope. GetColumnType wouldn't work because a column can contain more than one data type as Null is a data type.

Yes, but then his code would return a Null data type for a column that has values for all but the last row. That would make no sense. I still think that getType is column specific and not row specific. I guess a test needs to be done to see if getType changes per row. Look at section 3.4 of this page (https://www.sqlite.org/datatype3.html#column_affinity_behavior_example) at the "typeof" SQlite build in function example. That function would make no sense if types can change per row instead of being typed to a column. I'll try to find some source behind the getType of the Android API to see what it does.

i would try a wish at "Bugs & wishlist"

This has nothing to do with B4A in general, it's an underlying Android API issue.

 

[MarkusR]

This has nothing to do with B4A in general, it's an underlying Android API issue.

me believes it just belongs the sql library.
a column must have the same type because it show just rows of a field with different values, the type you are set at table design (or in query)
at sqllite if NULL is mentioned they spoke about storage classes.

Each column in an SQLite 3 database is assigned one of the following type affinities:

• TEXT
• NUMERIC
• INTEGER
• REAL
• BLOB

https://www.sqlite.org/datatype3.html

 

[RB Smissaert]

Yes, but then his code would return a Null data type for a column that has values for all but the last row. That would make no sense. I still think that getType is column specific and not row specific. I guess a test needs to be done to see if getType changes per row. Look at section 3.4 of this page (https://www.sqlite.org/datatype3.html#column_affinity_behavior_example) at the "typeof" SQlite build in function example. That function would make no sense if types can change per row instead of being typed to a column. I'll try to find some source behind the getType of the Android API to see what it does.

This has nothing to do with B4A in general, it's an underlying Android API issue.

SQLite has sqlite3_column_type and that will return the default data type of the result column.
So if doing:
create table test(IntField integer, RealField real, TextField text, BlobField blob)
select IntField, RealField, TextField, BlobField from test
and doing sqlite3_column_type for those 4 result columns will produce:
1, 2, 3 and 4

This is what I would like to do. No need here to bother (and waste time) with different rows.
Android or certainly B4A doesn't seem to have such a function.
GetType does change per row unfortunately, depending on the inserted value.

RBS

 

[OliverA]

Android or certainly B4A doesn't seem to have such a function.

B4A just wraps the functionality of the Android API and looking at the source, Android takes the row into effect when determining the type of a column value. See https://github.com/aosp-mirror/plat.../java/android/database/CursorWindow.java#L370. Android really does not expose the actual SQLite3 API, so neither the Reflection nor JavaObject libraries can be used to gain more functionality. So it's not a B4A limitation, it's an Android limitation. I know you stated the aversion to including an extra library, but you may want to look at JdbcSQL (https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/). It says remote database connection, but it can be used to open/create a local SQLite3 database (just find some B4J examples) using the SQLite3 database driver jar. This way you would have access to Java's JDBC and therefore the code in post #2 would/should be available. The SQLite3 database driver jar may also expose some functionality (pure guess here) that can be accessed via JavaObject and/or Reflection libraries.

 

[RB Smissaert]

This way you would have access to Java's JDBC and therefore the code in post #2 would/should be available.

But that code walks the whole recordset, so not sure now something like sqlite3_column_type is available.

RBS

 

[OliverA]

But that code walks the whole recordset

It does not have to. It works on the "MetaData" of the ResultSet. You can just call it on the returned ResultSet without stepping through all the records. As the poster said, he adapted it from JRDC2 and in that particular case, it's just easier to call getColumnType in the loop. Note, you may need to make sure something was returned from the query before this works properly (just guessing here). In other words, I don't know what this does if no records are retrieved (but no error was thrown).

Note: @XbNnX_507 was emulating your code of stepping through all records, so his looping makes sense.

 

[RB Smissaert]

Note, you may need to make sure something was returned from the query before this works properly

No need to check for data types if there are no rows produced.

@XbNnX_507 was emulating your code of stepping through all records, so his looping makes sense.

Don't understand that if all rows will return same.

RBS

 

[MarkusR]

here they speak only about columns.
if they really make different types in one column then is that so ..

public interface Cursor extends Closeable {
    /*
     * Values returned by {@link #getType(int)}.
     * These should be consistent with the corresponding types defined in CursorWindow.h
     */
    /** Value returned by {@link #getType(int)} if the specified column is null */
    static final int FIELD_TYPE_NULL = 0;

    /** Value returned by {@link #getType(int)} if the specified  column type is integer */
    static final int FIELD_TYPE_INTEGER = 1;

    /** Value returned by {@link #getType(int)} if the specified column type is float */
    static final int FIELD_TYPE_FLOAT = 2;

    /** Value returned by {@link #getType(int)} if the specified column type is string */
    static final int FIELD_TYPE_STRING = 3;

    /** Value returned by {@link #getType(int)} if the specified column type is blob */
static final int FIELD_TYPE_BLOB = 4;

 

[RB Smissaert]

here they speak only about columns.
if they really make different types in one column then is that so ..

public interface Cursor extends Closeable {
    /*
     * Values returned by {@link #getType(int)}.
     * These should be consistent with the corresponding types defined in CursorWindow.h
     */
    /** Value returned by {@link #getType(int)} if the specified column is null */
    static final int FIELD_TYPE_NULL = 0;

    /** Value returned by {@link #getType(int)} if the specified  column type is integer */
    static final int FIELD_TYPE_INTEGER = 1;

    /** Value returned by {@link #getType(int)} if the specified column type is float */
    static final int FIELD_TYPE_FLOAT = 2;

    /** Value returned by {@link #getType(int)} if the specified column type is string */
    static final int FIELD_TYPE_STRING = 3;

    /** Value returned by {@link #getType(int)} if the specified column type is blob */
static final int FIELD_TYPE_BLOB = 4;

Not sure what you are saying here.
For sure the code I posted first gives different values for the different rows.

RBS