Storing blob in SQLite

Question

Hi, I wrote this code to read blob data from a SQLite DB:

 public static byte[] GetBlobData(string Query){
         byte[] BlobContent = new byte[0];
         
         dbcmd = dbcon.CreateCommand();
         dbcmd.CommandText = Query;
         reader = dbcmd.ExecuteReader();
         
         while (reader.Read()) {
             BlobContent = (byte[])reader["blob"];
         }
         return BlobContent;
     }

Where query is like "SELECT blob FROM 'blobfield' WHERE id="...

How about storing blob data? Any suggestion?

Thanks!

Gius

Answer 1

Answer by Giusort · Mar 14, 2018 at 02:28 PM

So, the correct code is:

 public static bool SaveBlobData(String tableName, byte[] BlobContent) {
         string query;
         
         query = "INSERT INTO " + tableName + "(blob) VALUES (@BlobContent)";
         try {
             dbcmd = dbcon.CreateCommand();
             dbcmd.CommandText = query;
             SqliteParameter setParam = new SqliteParameter("@BlobContent", BlobContent);
             dbcmd.Parameters.Add(setParam);
 
             dbcmd.ExecuteNonQuery ();
         } catch(Exception exception) {
             Debug.LogError(exception.Message);
             return false;
         }
         return true;
     }

Don't you forget to open the DB before saving data in. Hope this help!

Add comment · Show 1 · Share

talyh · Sep 16, 2019 at 08:13 PM 0

Share

While I can see this working (ie. not throwing an error), it is also not saving the byte[] value to the DB. Debugging setParam shows that it was associated properly, and I can see the array values. Looking at the DB post insertion, that column is empty. Any ideas where the issue might be?

NOTE: Because references for SQLite for C# indicate the need to also set the dbType and size, I've included those

 SqliteParameter paramPhoto = new SqliteParameter("@photoBytes", photoBytes);
 paramPhoto.Size = photoBytes.Length;
 paramPhoto.DbType = DbType.Binary;
 cmdCreateUser.Parameters.Add(paramPhoto);

Tried the code both with and without those 2 lines, same result in both cases.

Storing blob in SQLite

1 Reply

Your answer

Follow this Question

Related Questions