Setting default image to column in Sqlite database

Question

In my project I am using System.Data.Sqlite. I want to create a table with image column. I am setting default image (i.e. NoImageAvailable). But it gives me error i.e. SQL logic error or missing database near "@img": syntax error. Here is my code.

byte[] img = null;

        try
        {
            img = File.ReadAllBytes(Application.StartupPath + 
                                    @"\Resources\General_Images\NOPHOTO.jpg");
        }
        catch { }

        string qry = "CREATE TABLE [CLIENT_MSTR] " +
            "([ID] INTEGER IDENTITY," + 
            "[CLIENT_ID] INTEGER PRIMARY KEY," + 
            "[ABBR] VARCHAR(3) DEFAULT '" + string.Empty + "' UNIQUE," +
            "[PHOTO] BLOB DEFAULT @img)";

        SQLiteCommand cmd = new SQLiteCommand(qry, m_dbConnection);
        cmd.Prepare();
        cmd.Parameters.Add("@img", DbType.Binary, img.Length);
        cmd.Parameters["@img"].Value = img;

        try
        {
            cmd.ExecuteNonQuery();
        }
        catch { }

What is wrong?

Community · Accepted Answer · 2017-05-23 12:20:28Z

0

I doubt that you can parametrize a blob in a DDL statement. The docs say:

BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. Example: X'53514C697465'

Whether you can include the image data in this format, I don't know; converting to the X'..' format probably is not the problem but to create and feed in such a long string may be tricky. There is an option that controls it in theory:

SQLITE_OMIT_BLOB_LITERAL

When this option is defined, it is not possible 
to specify a blob in an SQL statement using the X'ABCD' syntax.

This post also rules it out, ar least by parameters..

If you manage to get it to work in a literal, please do post it here!

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Oct 8, 2014 at 21:44

TaW

54.6k8 gold badges74 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting default image to column in Sqlite database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related