Android: Why a text field in a SQLite table demands getBlob() function?

Question

Table Structure Calcs from database your.db (SQLite using SQLite Studio):

Id: Integer
Content: Text  // Type is text, not BLOB (It's a JSON string)
...

I know that the type in SQLite it's just a suggestion, but I've verified the type outside SQLite Studio (See PS in the end)

My Kotlin code

...
your = File(this.filesDir, "your.db").absolutePath
val db = SQLiteDatabase.openDatabase(your,null,  
             SQLiteDatabase.OPEN_READWRITE)
val c = db.query("Calcs", arrayOf("Conteudo"), "Id=?", 
        arrayOf(1), null, null, null)
val stat = c.moveToFirst()
if (! stat)   exitApp(this)
val conteudo = c.getString(c.getColumnIndex("Conteudo"))
...

Error: SQLite exception unable to convert BLOB to string

The solution for my problem was really weird. I've replaced the last source line for:

val blob = c.getBlob(c.getColumnIndex("Conteudo"))
var conteudo = String(blob)
conteudo = conteudo.substring(0,conteudo.length-1)

It works because after I've used the function fromGSon() from gson package to transform a JSON string in a Kotlin class filled with my data.

First it loads a byte array with 0 extra byte added in the end of the string. So the length is equal to original field in my SQLite table (I've checked with length SQLite function) plus 1. So I've needed to take this 0 out from string before I can use in a JSON transformation.

Questions:

1) Why a column declared as Text in SQLite needs a getBlob() function in Kotlin?
2) Why it brings an extra 0 byte in the end?

PS::

Since this could be a quirk from SQLite Studio, I've decided look at SQLite command line utility (My SQLite is updated and my system is Windows):

I've filled the field conteudo using a text file your.txt (2723 characters) that stores the content, with the command:

UPDATE my_table set my_column=readfile('c:\data\android\your.txt') 
 where id=1;

So I went to the folder C:\Data\Android that holds your.db and ran SQLite:

C:\Data\Android> SQLite3 your.db

Then I've run the following commands:

.header on
.mode column
.pragma table_info('calcs');

It showed.

cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  --
0           Id          INTEGER     0                       1
1           Nome        TEXT        0                       0
2           Conteudo    TEXT        0                       0
3           Cont2       BLOB        0                       0

The last but not the least:

The command select typeof(conteudo) from calcs also has returned text.

Below I put 2 images to check my claims, First from SQLite Studio structure:

Second image is from my Kotlin code

My final piece of code in Kotlin, after all checks;

val your = fullPath(this,"your.db")
val db = SQLiteDatabase.openDatabase(your,null, 
          SQLiteDatabase.OPEN_READWRITE)
val c = db.query("Calcs", arrayOf("Conteudo",
       "Length(Conteudo) As Len"), "Nome=?",
       arrayOf(calcActive), null, null, null)
val stat = c.moveToFirst()
if (! stat) exitApp(this)
val blob = c.getBlob(c.getColumnIndex("Conteudo"))
val len = c.getInt(c.getColumnIndex("Len"))
val len2 = blob.size
val char =blob[len2-1]
val result = "Len $len, Len Blob: $len2, $char"

The result is LEN 2273 LEN BLOB 2274,0

UPDATE 2:

Now, suddenly getStrings works again for me. I swear that I don't edit the field type. In the middle, I just added a dummy field for testing. However it's clear for me that getBlob should throw an exception and not silently returns a byte array with 0 added in the end.

Answer 1

I believe that you need to investigate how you are inserting or updating the column. That is that SQLite allows you to store any type of data in any column, irrespective of the type you use use to define the column with. Perhaps have a read of Datatypes In SQLite Version 3

e.g. try running this in SQLite Studio :-

DROP TABLE IF EXISTS type_affinity_example;
CREATE TABLE IF NOT EXISTS type_affinity_example (
    id INTEGER PRIMARY KEY, -- <<<<<<<< MUST BE INTEGER VALUE ELSE INVALID DATATYPE
    col1 rumplestiltskin,
    col2 BLOB,
    col3 INTEGER,
    col4 NUMERIC,
    col5 REAL,
    col6 REALINT, -- because of rule 1 will be INTEGER type affinity NOT REAL!!!!
    col7 TEXT
);

INSERT INTO type_affinity_example (col1,col2,col3,col4,col5,col6,col7) VALUES

    (1.3456,1.3456,1.3456,1.3456,1.3456,1.3456,1.3456),
    ('A','A','A','A','A','A','A'),
    (1,1,1,1,1,1,1),
    (x'0102',x'0102',x'0102',x'0102',x'0102',x'0102',x'0102')
;

SELECT 
    rowid,
    *,
    typeof(id) AS ctypeid, 
    typeof(col1) AS ctype1, 
    typeof(col2) AS ctype2, 
    typeof(col3) AS ctype3, 
    typeof(col4) AS ctype4,  
    typeof(col5) AS ctype5, 
    typeof(col6) AS ctype6, 
    typeof(col7) AS ctype7  
FROM  type_affinity_example

Which produces :-

• Navicat was used (the various SQLite management tools have their own way off handling blobs)

As such it is highly likely that you are inadvertently storing the data as a BLOB.

It could just be that you've inadvertently swapped columns by using cont2 instead of conteudo and thus stored a blob at some time.

It could be because of wherever you do First it loads a byte array with 0 extra byte added in the end of the string. and you are then binding a byte array rather than a string when inserting/updating.

I'd suggest not using SELECT typeof(conteudo) FROM calcs WHERE id = 1 as this only checks the one row but rather run :-

SELECT typeof(conteudo) FROM calcs;

Or

SELECT Id,typeof(conteudo) FROM calcs WHERE typeof(conteudo) = 'blob';

Another thought is that you are adding a byte when inserting/updating but that that is not a valid character when extracting and thus that it it then converted at a BLOB type (not tried to see if that happens but it could be within the SDK that it fails to convert and assumes unconvertable character equates to a BLOB), perhaps try padding the length with a character rather than a byte.

Additional

You might be interested in the following experiment but it may explain the magical extra byte :-

Here's the DBHelper :-

class DBHelper(context: Context?) : SQLiteOpenHelper(context, DBNAME, null, DBVERSION) {

    internal var mDB: SQLiteDatabase

    init {
        this.mDB = this.writableDatabase
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(crt_mytable_sql)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {}

    fun insert(nome: String, conteudo: String, cont2: ByteArray): Long {
        val cv = ContentValues()
        cv.put(COl_MYTABLE_NOME, nome)
        cv.put(COl_MYTABLE_CONTEUDO, conteudo)
        cv.put(COL_MYTABLE_CONT2, cont2)
        return mDB.insert(TBL_MYTABLE, null, cv)
    }

    companion object {
        val DBNAME = "your.db"
        val DBVERSION = 1
        val TBL_MYTABLE = "calcs"
        val COL_MYTABLE_ID = "Id"
        val COl_MYTABLE_NOME = "Nome"
        val COl_MYTABLE_CONTEUDO = "Conteudo"
        val COL_MYTABLE_CONT2 = "Cont2"

        internal val crt_mytable_sql = "CREATE TABLE IF NOT EXISTS " + TBL_MYTABLE + "(" +
                COL_MYTABLE_ID + " INTEGER PRIMARY KEY, " +
                COl_MYTABLE_NOME + " TEXT, " +
                COl_MYTABLE_CONTEUDO + " TEXT, " +
                COL_MYTABLE_CONT2 + " BLOB " +
                ")"
    }
}

and the activity :-

class MainActivity : AppCompatActivity() {

    internal var myTestString = "abcdefghijklmnopqrstuvwxyz1234567890"

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        var mDBHlpr = DBHelper(this)
        val sb = StringBuilder().append("{")
        for (i in 0..0) {
            sb.append(myTestString)
        }
        sb.append("}")
        mDBHlpr.insert("BLAH", sb.substring(0, sb.length - 1),byteArrayOf(0))
        val columns = arrayOf(DBHelper.COl_MYTABLE_CONTEUDO,"length(" + DBHelper.COl_MYTABLE_CONTEUDO + ") AS len")
        val csr = mDBHlpr.writableDatabase.query(DBHelper.TBL_MYTABLE, columns, null, null, null, null, null)
        while (csr.moveToNext()) {
            Log.d("EXTRACTED",
                    csr.getString(
                    csr.getColumnIndex(DBHelper.COl_MYTABLE_CONTEUDO)) + " Length is " +
                    csr.getString(csr.getColumnIndex("len"))
            )
            val barray = csr.getBlob(csr.getColumnIndex(DBHelper.COl_MYTABLE_CONTEUDO))
            Log.d("BALEN","Length of the byte array is " + barray.size)
            val sb2 = StringBuilder()
            for (b in barray) {
                sb2.append((b and (0xFF).toByte()).toChar())
            }
            Log.d("VIABLOB",
                    sb2.toString() + " Length is " +
                            csr.getString(csr.getColumnIndex("len"))
            )
        }
    }
}

When run for the first time it produces :-

2019-06-21 15:25:32.633 D/EXTRACTED: {abcdefghijklmnopqrstuvwxyz1234567890 Length is 37
2019-06-21 15:25:32.633 D/BALEN: Length of the byte array is 38
2019-06-21 15:25:32.633 D/VIABLOB: {abcdefghijklmnopqrstuvwxyz1234567890�� Length is 37

i.e. extracting TEXT as a blob and converting it isn't just plain sailing (same with Java, it's not a Kotlin issue nor I suspect an SQLite issue but I suspect an SDK issue)