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

User · Answer

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

User · Answer

Here s how you can do it in C   class Program       static void Main string   args                if  File Exists  quot test db3 quot                          File Delete  quot test db3 quot                      using  var connection   new SQLiteConnection  quot Data Source test db3 Version 3 quot            using  var command   new SQLiteCommand  quot CREATE TABLE PHOTOS ID INTEGER PRIMARY KEY AUTOINCREMENT  PHOTO BLOB  quot   connection                         connection Open                command ExecuteNonQuery                 byte   photo   new byte     1  2  3  4  5                 command CommandText    quot INSERT INTO PHOTOS  PHOTO  VALUES   photo  quot               command Parameters Add  quot  photo quot   DbType Binary  20  Value   photo              command ExecuteNonQuery                 command CommandText    quot SELECT PHOTO FROM PHOTOS WHERE ID   1 quot               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    gt  0                                stream Write buffer  0   int bytesRead                   fieldOffset    bytesRead                            return stream ToArray

User · Answer

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 gt 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  gt  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

User · Answer

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  lt sqlite3 h gt    include  lt iostream gt   include  lt vector gt   int main            open sqlite3 database connection     sqlite3  db      sqlite3 open  path to database db    amp db           insert blob               sqlite3 stmt  stmtInsert   nullptr          sqlite3 prepare v2 db   INSERT INTO table name  vector blob  VALUES       -1   amp stmtInsert  nullptr            std  vector lt float gt  blobData 128      your data         sqlite3 bind blob stmtInsertFace  1  blobData data    static cast lt int gt  blobData size     sizeof float    SQLITE STATIC            if  sqlite3 step stmtInsert     SQLITE DONE              std  cout  lt  lt   Insert successful   lt  lt  std  endl          else             std  cout  lt  lt   Insert failed   lt  lt  std  endl           sqlite3 finalize stmtInsert                 retrieve blob               sqlite3 stmt  stmtRetrieve   nullptr          sqlite3 prepare v2 db   SELECT vector blob FROM table name WHERE id       -1   amp stmtRetrieve  nullptr            int id   1     your id         sqlite3 bind int stmtRetrieve  1  id            std  vector lt float gt  blobData          if  sqlite3 step stmtRetrieve     SQLITE ROW                           retrieve blob data             const float  pdata   reinterpret cast lt const float  gt  sqlite3 column blob stmtRetrieve  0                   query blob data size             blobData resize sqlite3 column bytes stmtRetrieve  0    static cast lt int gt  sizeof float                    copy to data vector             std  copy pdata  pdata   static cast lt int gt  blobData size     blobData data                        sqlite3 finalize stmtRetrieve              sqlite3 close db        return 0

User · Answer

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     amp 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

User · Answer

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  amp  amp   Convert IsDBNull reader  icon                                                   iconBytes    byte    reader  icon                                                                            transaction Commit              No need for chunking  Just cast to a byte array

[sqlite] How do I store and retrieve a blob from sqlite?

Examples related to sqlite

Examples related to blob