Reading a blob image from a MySQL database

Question

I am having trouble reading blob from MySQL database. I got it to successfully insert into the database, but it seems unable to read it. I know that some of you might think: “Why does he use a database to store droplets for images, and not just for file paths / file names,” but I want to have flexibility and since many of these images will be stored on the server instead of locally, it optimizes efficiency, and also allows me to move images to local ones if necessary. I completed the (short) tutorial and wrote the following blob retrieval method:

public void getBlob(string query, string fileOut) { if (this.OpenConnection() == true) { MySqlCommand cmd = new MySqlCommand(query, mConnection); //the index number to write bytes to long CurrentIndex = 0; //the number of bytes to store in the array int BufferSize = 100; //The Number of bytes returned from GetBytes() method long BytesReturned; //A byte array to hold the buffer byte[] Blob = new byte[BufferSize]; //We set the CommandBehavior to SequentialAccess //so we can use the SqlDataReader.GerBytes() method. MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); while (reader.Read()) { FileStream fs = new FileStream(DeviceManager.picPath + "\\" + reader["siteBlobFileName"].ToString(), FileMode.OpenOrCreate, FileAccess.Write); BinaryWriter writer = new BinaryWriter(fs); CurrentIndex = 0; BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0,BufferSize); while (BytesReturned == BufferSize) { writer.Write(Blob); writer.Flush(); CurrentIndex += BufferSize; BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize); } writer.Write(Blob, 0, (int)BytesReturned); writer.Flush(); writer.Close(); fs.Close(); } reader.Close(); this.CloseConnection(); } } 

and I call it that.

  mDBConnector.getBlob("SELECT siteMapPicture, siteBlobFilename FROM sites WHERE siteID = '" + DeviceManager.lastSite + "'", DeviceManager.picPath + "mappicsite" + DeviceManager.lastSite); PBSite.BackgroundImage = Image.FromFile(DeviceManager.picPath + "mappicsite" + DeviceManager.lastSite); 

However, this is an error in BytesReturned = reader.GetBytes (1, CurrentIndex, Blob, 0, BufferSize); with the error "GetBytes can only be called in binary or guide columns." I suppose this is due to my field type in my database, but changing the column for entering binary values ​​means I have to save this as a blob view, but I want to leave the file name as a regular string. Is there something I'm missing? or another way to do this?

edit1: I think that the first parameter for the bytes returned using the column in the reader sets this value to 0, it gives the error "Invalid attempt to read the previous column using SequentialAccess", do not look at this.

edit2: Removing sequential access gives me a 13-byte file (can it be only the first row, why does sequential access read all the rows?), so maybe I'm reading the columns in the wrong order.

edit 3: I believe that the reason for this error was related to the way I entered into the database. changing this method, my saveBlob now looks like this:

 public void saveBlob(string filePath, string fileName, string siteID) { if (this.OpenConnection() == true) { //A stream of bytes that represnts the binary file FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read); //The reader reads the binary data from the file stream BinaryReader reader = new BinaryReader(fs); //Bytes from the binary reader stored in BlobValue array byte[] BlobValue = reader.ReadBytes((int)fs.Length); fs.Close(); reader.Close(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = mConnection; cmd.CommandType = CommandType.Text; cmd.CommandText = "INSERT INTO x (y, z) VALUES (@BlobFile, @BlobFileName)"; MySqlParameter BlobFileNameParam = new MySqlParameter("@BlobFileName", SqlDbType.NChar); MySqlParameter BlobFileParam = new MySqlParameter("@BlobFile", SqlDbType.Binary); cmd.Parameters.Add(BlobFileNameParam); cmd.Parameters.Add(BlobFileParam); BlobFileNameParam.Value = fileName; BlobFileParam.Value = BlobValue; cmd.ExecuteNonQuery(); this.CloseConnection(); } } 

I went through the debugger, and both blobvalue and blobfileparam (@blobfile) have a full size (about 150k), but an error occurs when the request is executed:

 "unable to cast object of type 'system.byte[]' to type 'system.iconvertible" 

I looked at the code and tried changing the types of binary images to images to allow large files, but giving the same error. Does anyone know anything about this new information?

edit 4: everything fixed. noticed that in my code I used:

  ("@BlobFile", SqlDbType.Binary); 

They changed their types "MySqlDbType" (derp), and this allowed me to select blob types. Things finally work as intended :)

Answer 1

Did you try to simplify first? Instead of reading BLOB 100 bytes at a time, try simplifying your code to just read all bytes into a file. This way you can easily eliminate data level problems.

The following documentation also suggests saving the size of your file as the following column: http://dev.mysql.com/doc/refman/5.5/en/connector-net-programming-blob.html