How do I store and retrieve a blob from sqlite?

Question

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

Answer 1

Here's how 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

This worked fine 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 need for chunking. Just cast to a byte array.

Answer 3

I ended up with this method for inserting a 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;
      }
   }

For reading it back the code is:

   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

Since there is no complete example for C++ yet, this is how you can insert and retrieve an array/vector of float data without error checking:

#include <sqlite3.h>

#include <iostream>
#include <vector>

int main()
{
    // open sqlite3 database connection
    sqlite3* db;
    sqlite3_open("path/to/database.db", &db);

    // insert blob
    {
        sqlite3_stmt* stmtInsert = nullptr;
        sqlite3_prepare_v2(db, "INSERT INTO table_name (vector_blob) VALUES (?)", -1, &stmtInsert, nullptr);

        std::vector<float> blobData(128); // your data
        sqlite3_bind_blob(stmtInsertFace, 1, blobData.data(), static_cast<int>(blobData.size() * sizeof(float)), SQLITE_STATIC);

        if (sqlite3_step(stmtInsert) == SQLITE_DONE)
            std::cout << "Insert successful" << std::endl;
        else
            std::cout << "Insert failed" << std::endl;

        sqlite3_finalize(stmtInsert);
    }

    // retrieve blob
    {
        sqlite3_stmt* stmtRetrieve = nullptr;
        sqlite3_prepare_v2(db, "SELECT vector_blob FROM table_name WHERE id = ?", -1, &stmtRetrieve, nullptr);

        int id = 1; // your id
        sqlite3_bind_int(stmtRetrieve, 1, id);

        std::vector<float> blobData;
        if (sqlite3_step(stmtRetrieve) == SQLITE_ROW)
        {
            // retrieve blob data
            const float* pdata = reinterpret_cast<const float*>(sqlite3_column_blob(stmtRetrieve, 0));
            // query blob data size
            blobData.resize(sqlite3_column_bytes(stmtRetrieve, 0) / static_cast<int>(sizeof(float)));
            // copy to data vector
            std::copy(pdata, pdata + static_cast<int>(blobData.size()), blobData.data());
        }

        sqlite3_finalize(stmtRetrieve);
    }

    sqlite3_close(db);

    return 0;
}

Answer 5

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

SQLite Prepared Statements

Answer 6

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);

That can be SQLITE_STATIC if the query will be executed before blob gets destructed.