System.Data.SQLite

I have a SQLite table with a blob data.  I would like to do the following, but it doesn't work:

String con = String.Format("Data Source={0};UTF8Encoding=True;Version=3;", open.FileName);
SQLiteConnection sql_con;

SQLiteCommand sql_cmd;
SQLiteDataAdapter DB;
DataSet DS = new DataSet();

sql_con = new SQLiteConnection(con);
sql_con.Open();

sql_cmd = sql_con.CreateCommand();
string CommandText = "SELECT Owner, Make, Model, Year, VIN, [Color Picture] FROM [DMV Cars]";
DB = new SQLiteDataAdapter(CommandText, sql_con);
DS.Reset();
DB.Fill(DS);

DataRowCollection dataRowCol = DS.Tables[0].Rows;
foreach (DataRow dr in dataRowCol)
{
    String owner = (string)dr["Owner"];
    String make = (string)dr["Make];
    String model = (string)dr["Model"];
    String year = (string)dr["Year"];
    int vin = (int)dr["VIN"];
    byte [ ] data = (byte [ ])dr["Color Picture"];
}
 

The cast to "byte [ ]" fails with the following error:

An unhandled exception of type 'System.InvalidCastException' occurred in sqlite-ado.exe

Additional information: Unable to cast object of type 'System.Int64' to type 'System.Byte[ ]'.

For some reason it is forcing my blob data to a Int64.  Any help would be appreciated.  Thanks.

Not seeing your table schema I can only suggest that your problem is caused by the dynamic type system of SQLite.

Even if you've declared the field as a blob - perhaps what was actually stored there is a INT64 value and this is why the cast fails. IMHO the dynamic type system of SQLite causes more harm than good and maybe this is a very good example ..

Check how did you fill that row (using an external tool?, by code?). I believe that you'll find the answer there ..

If you are not convinced - try to fill the row by code and put a byte array there. Than try to do the reading again. If you don't get a cast error this time - it is the dynamic type system of SQLite that is causing your problem.

Good Luck

Liron Levi

Creator of SQLite Compare utility for comparing SQLite databases.

  Try this one fro inserting blob. This works perfectly for me! (just a crude example)

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

      //---------------------------------------------------------------------------------

      public void InsertImage()  // got from a previous thread

        {

            using (SQLiteTransaction mytransaction = connection.BeginTransaction())

            {

                SQLiteCommand mycommand = new SQLiteCommand(connection);

                SQLiteParameter myparam = new SQLiteParameter("@myparam");

                SQLiteParameter myparam2 = new SQLiteParameter("@myparam2");

                mycommand.CommandText = "INSERT INTO memberimage VALUES(@myparam, @myparam2)";

                mycommand.Parameters.Add(myparam);         // <- myparam1 is int

                mycommand.Parameters.Add(myparam2);       // <- in myparam2 I save the blob image

                MemoryStream ms = new MemoryStream();

                myparam.Value = 1;

                pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat);

                myparam2.Value = ms.ToArray();              // <- here I save the image

                mycommand.ExecuteNonQuery();

                mytransaction.Commit();

            }

        }

        //---------------------------------------------------------------------------------

        private void GetImage()

        {

            SQLiteDataAdapter DB;

            DataSet DS = new DataSet();

            string CommandText = "SELECT memberId, memberImage from memberimage where memberId = 1";

            DB = new SQLiteDataAdapter(CommandText, connection);

            DS.Reset();

            DB.Fill(DS);

            DataRowCollection dataRowCol = DS.Tables[0].Rows;

            MemoryStream ms = null;

            foreach (DataRow dr in dataRowCol)

            {

                int id = Convert.ToInt32(dr["memberId"]);

                byte[ data = (byte[)dr["memberImage"];

                ms = new MemoryStream(data);

            }

            pictureBox2.Image = Image.FromStream(ms);

        }

 pictureBox2.Image = Image.FromStream(ms);

i think this code is not right

there are no definition of fromstream

any one who can help to debug?

thanks...

 This is working perfectly. Maybe this can help. Take a look at this. (one of the [ ] braces seems to be missing in my earier post)

       public void RetrieveMemberImage(int memberId)
        {
            // get from db using member id
            MemoryStream ms = helper.GetImage(memberId);
            if (ms != null)
            {
                picMember.Image = Image.FromStream(ms);
            } else
            {
                SetDefaultMemberImage();
            }
        }

       //helper class

         public MemoryStream GetImage(int memberId)
        {
            SQLiteProvider prov = new SQLiteProvider(Common.connection);
            MemoryStream m = null;

            string sql =
                @"SELECT image from memberimage
                         where memberId = " + memberId.ToString();
            DataSet ds = prov.GetDataSet(sql);  // replace with ur code to return dataset

            if (ds.Tables[0].Rows.Count > 0) {
                m = new MemoryStream((byte[) ds.Tables[0].Rows[0][0]);
            }
            return m;
        }

byte[  -- should read  as byte[  ]   - the post is removing the right square braces ( ] )

im in my school project right now and my project is a simple dictionary with picture so i need a blob data type

 image.fromstream is a syntax error in my code

is there any missing using statement in my code?

using.system.blah blah or something

if i search the word in the textbox, it will output the meaning and the image of the word

i know now how to call the meaning of the word, so please help me in reading the image (no update, no delete just reading)

 i want to output the image in a picturebox if the word in a textbox = true or the word is in the database...so what syntax should i do? please help

thanks in advance...