Store BLOB in Android's SQLite using SQLOpenHelper

Question

Is there a way to store a BLOB into Android's SQLite using SQLOpenHelper?

My BLOB of type InputStream.

Answer 1

SQLite doesn't support streaming BLOB or CLOB data. You have four options:

• Convert the InputStream to a byte[]. This will only work if you have enough memory (see below).
• Use FileOutputStream or an Android API that supports streaming
• Split the data into small blocks and store then in SQLite
• Use a database that works on Android and supports streaming. I only know the H2 database. But be aware the Android support for H2 is very new. In that case, you need to use the JDBC API, and you probably want to enable LOBs in the database, otherwise each large BLOB is stored in a separate file.

To convert an InputStream to a byte array, you could use:

public static byte[] readBytesAndClose(InputStream in) throws IOException {
    try {
        int block = 4 * 1024;
        ByteArrayOutputStream out = new ByteArrayOutputStream(block);
        byte[] buff = new byte[block];
        while (true) {
            int len = in.read(buff, 0, block);
            if (len < 0) {
                break;
            }
            out.write(buff, 0, len);
        }
        return out.toByteArray();
    } finally {
        in.close();
    }
}