Identifying datatype of a column in an SQLite Android Cursor

Is there any way to identify the datatype of a column in a cursor in Android. The cursor object has 开发者_开发问答a number of methods to get the columnname, column value.

I want to find out the SQLite datatype of the column (TEXT, INTEGER) etc...

I'm writing a generic function to parse a cursor and perform operations. I will only get a sql string as an argument to the function.

Per the SQLite documentation (http://www.sqlite.org/datatype3.html) columns in SQLite don't have a datatype -- the values in those columns do.

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

If you're using API level 11 or above then the cursor supports getType() (see http://developer.android.com/reference/android/database/AbstractWindowedCursor.html#getType(int)).

If you're using an earlier API level, and you know that all the results in a given cursor come from the same table then you could do something like (untested):

// Assumes "cursor" is a variable that contains the cursor you're
// interested in.

String tableName = "..."; // The name of the table
SQLiteDatabase db = cursor.getDatabase();
String[] names = cursor.getColumnNames();

for (name : names) {
    Cursor typeCursor = 
        db.rawQuery("select typeof (" + name + ") from " + tableName;
    typeCursor.moveToFirst();
    Log.v("test", "Type of " + name + " is " + typeCursor.getString(0);
}

But that will (I expect) fail if the passed in cursor was (for instance) the result of a db.rawQuery() call that joined two or more tables.

The answer or NiK, is fine. But if your db is empty, his code crashes. I suggest use:

String Query = "PRAGMA table_info(my_table_name)"; 
Cursor my_cursor  = db.rawQuery(Query, null); 
my_cursor.moveToFirst();
Column_name = my_cursor.getString(my_cursor.getColumnIndex("name"));
Column_type = my_cursor.getString(my_cursor.getColumnIndex("type"));

I didn't tested yet but, try to use cursor.getType(i)

Like this:

public static List resultSetToArrayListAndroid(Cursor cursor) throws SQLException {
    int columns = cursor.getColumnCount();
    ArrayList list = new ArrayList();
    while (cursor.moveToNext()) {
        HashMap row = new HashMap(columns);
        for (int i = 1; i <= columns; ++i) {
            switch (cursor.getType(i))  {
                case Cursor.FIELD_TYPE_FLOAT:
                    row.put(cursor.getColumnName(i), cursor.getFloat(i));
                    break;
                case Cursor.FIELD_TYPE_INTEGER:
                    row.put(cursor.getColumnName(i), cursor.getInt(i));
                    break;
                case Cursor.FIELD_TYPE_STRING:
                    row.put(cursor.getColumnName(i), cursor.getString(i));
                    break;
            }
        }
        list.add(row);
    }
    return list;
}

You should use meta data from sqlite Database :

http://developer.android.com/reference/java/sql/ResultSetMetaData.html

You get this item by using getMetaData() on a ResultSet().

So, it's not the usual way of using a sqlite database in android that you shall use, but the usual JDBC way :

1. get a connection (through driver manager getConnection)
2. get a statement (through connection.createStatement)
3. get a resultset (through statement.executeQuery)
4. get the meta data (through resultset.getMetaData)

Regards, Stéphane

Here's a toList extension function for Cursor:

fun Cursor.toList(): List<List<Any>> {
    fun getValues(cursor: Cursor, numberOfFields: Int): List<Any> {
        return (0 until numberOfFields).map { index ->
            when {
                cursor.getType(index) == Cursor.FIELD_TYPE_STRING -> {
                    cursor.getString(index)
                }
                cursor.getType(index) == Cursor.FIELD_TYPE_INTEGER -> {
                    cursor.getInt(index)
                }
                else -> {
                    throw Exception("Unknown type when trying to get value at cursor.")
                }
            }
        }
    }

    val list = generateSequence { if (this.moveToNext()) this else null }
        .map {
            getValues(it, this.columnCount)
        }
        .toList()
    return list
}

Call like this:

val results: List<List<Any>> = db.rawQuery("SELECT * FROM tableName", null).toList()
val firstResult = results[0]
val firstResultsFirstValue = firstResult[0]

Make sure to set your actual table name in the query and add more types to the when function as needed.