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:

Code: Copy

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 · May 1, 2018

Code: Copy

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 · May 1, 2018

XbNnX_507 said:

Code: Copy

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 · May 2, 2018

XbNnX_507 said:
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 · May 2, 2018

but what if it acts more like getColumnName?

RB Smissaert · May 2, 2018

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 · May 2, 2018

me thought about getting table infos from db itself as query

Field1
INTEGER
Field2
TEXT
Field3
BLOB
Field4
REAL
Field5
NUMERIC

Code: Copy

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 · May 2, 2018

MarkusR said:

me thought about getting table infos from db itself as query

Code: Copy

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 · May 2, 2018

RB Smissaert said:
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 · May 2, 2018

RB Smissaert said:
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 · May 2, 2018

keirS said:
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 · May 2, 2018

keirS said:
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.

MarkusR said:
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 · May 2, 2018

OliverA said:
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 · May 2, 2018

OliverA said:
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 · May 2, 2018

RB Smissaert said:
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 · May 2, 2018

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 · May 2, 2018

RB Smissaert said:
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 · May 2, 2018

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 · May 2, 2018

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

Code: Copy

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 · May 2, 2018

MarkusR said:

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

Code: Copy

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

Android Question SQLite data types

Well-Known Member

Active Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Similar Threads