How to store and retrieve blob from sqlite

Question

I used sqlite in C ++, python and now (possibly) in C #. In all of these cases, I have no idea how to insert blob into a table. How to store and retrieve blob in sqlite?

Answer 1

Here you can do it in C #:

class Program { static void Main(string[] args) { if (File.Exists("test.db3")) { File.Delete("test.db3"); } using (var connection = new SQLiteConnection("Data Source=test.db3;Version=3")) using (var command = new SQLiteCommand("CREATE TABLE PHOTOS(ID INTEGER PRIMARY KEY AUTOINCREMENT, PHOTO BLOB)", connection)) { connection.Open(); command.ExecuteNonQuery(); byte[] photo = new byte[] { 1, 2, 3, 4, 5 }; command.CommandText = "INSERT INTO PHOTOS (PHOTO) VALUES (@photo)"; command.Parameters.Add("@photo", DbType.Binary, 20).Value = photo; command.ExecuteNonQuery(); command.CommandText = "SELECT PHOTO FROM PHOTOS WHERE ID = 1"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { byte[] buffer = GetBytes(reader); } } } } static byte[] GetBytes(SQLiteDataReader reader) { const int CHUNK_SIZE = 2 * 1024; byte[] buffer = new byte[CHUNK_SIZE]; long bytesRead; long fieldOffset = 0; using (MemoryStream stream = new MemoryStream()) { while ((bytesRead = reader.GetBytes(0, fieldOffset, buffer, 0, buffer.Length)) > 0) { stream.Write(buffer, 0, (int)bytesRead); fieldOffset += bytesRead; } return stream.ToArray(); } } } 

Answer 2

You need to use the sqlite interface of prepared statements. Basically, the idea is that you prepare a statement using a placeholder for your blob, and then use one of the bind calls to "bind" your data ...

Prepared SQLite Reports

Answer 3

I ended up with this method for adding blob:

  protected Boolean updateByteArrayInTable(String table, String value, byte[] byteArray, String expr) { try { SQLiteCommand mycommand = new SQLiteCommand(connection); mycommand.CommandText = "update " + table + " set " + value + "=@image" + " where " + expr; SQLiteParameter parameter = new SQLiteParameter("@image", System.Data.DbType.Binary); parameter.Value = byteArray; mycommand.Parameters.Add(parameter); int rowsUpdated = mycommand.ExecuteNonQuery(); return (rowsUpdated>0); } catch (Exception) { return false; } } 

To read this code:

  protected DataTable executeQuery(String command) { DataTable dt = new DataTable(); try { SQLiteCommand mycommand = new SQLiteCommand(connection); mycommand.CommandText = command; SQLiteDataReader reader = mycommand.ExecuteReader(); dt.Load(reader); reader.Close(); return dt; } catch (Exception) { return null; } } protected DataTable getAllWhere(String table, String sort, String expr) { String cmd = "select * from " + table; if (sort != null) cmd += " order by " + sort; if (expr != null) cmd += " where " + expr; DataTable dt = executeQuery(cmd); return dt; } public DataRow getImage(long rowId) { String where = KEY_ROWID_IMAGE + " = " + Convert.ToString(rowId); DataTable dt = getAllWhere(DATABASE_TABLE_IMAGES, null, where); DataRow dr = null; if (dt.Rows.Count > 0) // should be just 1 row dr = dt.Rows[0]; return dr; } public byte[] getImage(DataRow dr) { try { object image = dr[KEY_IMAGE]; if (!Convert.IsDBNull(image)) return (byte[])image; else return null; } catch(Exception) { return null; } } DataRow dri = getImage(rowId); byte[] image = getImage(dri); 

Answer 4

This worked for me (C #):

 byte[] iconBytes = null; using (var dbConnection = new SQLiteConnection(DataSource)) { dbConnection.Open(); using (var transaction = dbConnection.BeginTransaction()) { using (var command = new SQLiteCommand(dbConnection)) { command.CommandText = "SELECT icon FROM my_table"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { if (reader["icon"] != null && !Convert.IsDBNull(reader["icon"])) { iconBytes = (byte[]) reader["icon"]; } } } } transaction.Commit(); } } 

No separation required. Just add to the byte array.

Answer 5

In C ++ (without error checking):

 std::string blob = ...; // assume blob is in the string std::string query = "INSERT INTO foo (blob_column) VALUES (?);"; sqlite3_stmt *stmt; sqlite3_prepare_v2(db, query, query.size(), &stmt, nullptr); sqlite3_bind_blob(stmt, 1, blob.data(), blob.size(), SQLITE_TRANSIENT); 

It could be SQLITE_STATIC if the request is executed before the blob is destroyed .