1

I'm developing an app in C# with .NET and MySQL database. I need to be able to insert and retrieve images in and out of the database and I have a column named 'Image' of type LONGBLOB for that purpose. The insertion goes well but when I try to retrieve the blob the following error pops up:

GetBytes() can only be called on binary or GUID columns

I'm using the following code to select from the database:

            Conn.Open();
            string sql = @"SELECT `ID`, `Image`, `Note`"
                        + " FROM `Item`"
                        + " WHERE `ID` = ?ID";
            MySqlCommand cmd = new MySqlCommand(sql, Conn);
            cmd.Parameters.Add(new MySqlParameter("?ID", iD));
            cmd.Prepare();
            rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

            while (rdr.Read())
            {   
                this.ID= rdr.GetString("ID");
                if (!rdr.IsDBNull(1))
                {
                    long len = rdr.GetBytes(1, 0, null, 0, 0);
                    byte[] ImageBytes = new byte[len];
                    rdr.GetBytes(1, 0, ImageBytes, 0, (int)len);
                    MemoryStream ms = new MemoryStream(ImageBytes);
                    this.Image = Image.FromStream(ms);
                }
                this.Note = rdr.GetString("Note");

Despite changing the column type into binary and varbinary, I still got the same error.

Does anyone know what I'm doing wrong here? TIA

Igor
  • 1,414
  • 4
  • 21
  • 43
  • Have you tried using `rdr.GetValue(1)` or `rdr.GetValues(...)` to see what is actually being returned? – Corey Mar 04 '13 at 21:03
  • GetBytes can be only used for Image type or varbinary(N). Please make sure you are using one of those – Faaiz Khan Mar 04 '13 at 21:05
  • @Corey Now I did, and it's and empty string for the null values in the database. – Igor Mar 04 '13 at 21:06
  • Side note: You don't need all the string concatenation when putting together your query - the `@` at the front makes it so your string can be multi-line - you can make it easier to copy-paste by making one big string instead of a bunch of little ones, and also you wouldn't have to worry about the leading spaces on each line. Only downside is your statement would be a tiny bit bigger, but unless you're micro-optimizing, this won't make a difference. – Joe Enos Mar 04 '13 at 21:09
  • @JoeEnos Thanks Joe, I'll do that. I didn't want the "\r\n" in the query so I concatenated :) – Igor Mar 04 '13 at 21:15
  • @Igor Empty string or null string? Either way, `rdr.IsDBNull` gives you a check for null. – Corey Mar 04 '13 at 21:15
  • @Corey empty, not null. I'm doing the `rdr.IsDBNull` check. Still confused. – Igor Mar 04 '13 at 21:26
  • Now I get: `Parametar is not valid` for `this.Image = Image.FromStream(ms);`and it's weird that `r.GetValue(1)` returns byte array only 21 bytes long. – Igor Mar 04 '13 at 22:11
  • I had the similar exception with NHibernate & MySQL. I've find out it that i can't use a Column of type **System.Version**. so, I have changed it to System.String, and all worked again – itsho Jul 07 '13 at 19:04

1 Answers1

0

Can't you just cast rdr["Image"] as byte[]?

Jason Lind
  • 263
  • 1
  • 2
  • 15
  • It says: `Unable to cast object og type string into byte[]`. – Igor Mar 04 '13 at 21:12
  • Then you don't have a byte[] you have a string. God only knows what MySQL encodes there byte strings as so you'll have to refer to their documentation to parse it – Jason Lind Feb 15 '17 at 02:56