Comment stocker et récupérer un blob dans sqlite ?

Question

J'ai utilisé sqlite en c++, python et maintenant (peut-être) en C#. Dans tous ces cas, je n'ai aucune idée de la façon d'insérer un blob dans une table. Comment puis-je stocker et récupérer un blob dans sqlite ?

Answer 1

Voici comment vous pouvez le faire en 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

Cela a bien fonctionné pour moi (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();
    }
}

Pas besoin de chunking. Il suffit de le convertir en un tableau d'octets.

Answer 3

Comme il n'existe pas encore d'exemple complet pour le C++, voici comment vous pouvez insérer et récupérer un tableau/vecteur de données flottantes sans contrôle d'erreur :

#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 4

J'ai fini par trouver cette méthode pour insérer un 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;
      }
   }

Pour le relire, le code est le suivant :

   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 5

Vous devez utiliser l'interface des instructions préparées de sqlite. En gros, l'idée est de préparer une déclaration avec un espace réservé pour votre blob, puis d'utiliser l'un des appels bind pour "lier" vos données...

Instructions préparées SQLite