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

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 2

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)
            {
                byte[] actualRead = new byte[bytesRead];
                Buffer.BlockCopy(buffer, 0, actualRead, 0, (int)bytesRead);
                stream.Write(actualRead, 0, actualRead.Length);
                fieldOffset += bytesRead;
            }
            return stream.ToArray();
        }
    }

Answer 3

does anyone know how to do this task using Tcl/Tk interface?

Answer 4

The answer is well documented in the internal documentation for yserial module which offers a ready-to-go implementation: y_serial.py module :: warehouse Python objects with SQLite

"Serialization + persistance :: in a few lines of code, compress and annotate Python objects into SQLite; then later retrieve them chronologically by keywords without any SQL. Most useful "standard" module for a database to store schema-less data."

http://yserial.sourceforge.net

The code is very instructive, and it also compresses the BLOB before storage.