从MySQL数据库中读取BLOB图像 [英] reading BLOB image from MySQL database
问题描述
我有一些麻烦，从MySQL数据库中读取一个blob回来。我已经得到它成功地插入到数据库中，但似乎无法得到它读回。我知道你们当中有些人可能会想他为什么要使用一个数据库来存储BLOB的图像，而不仅仅是文件路径/文件名，但我希望有灵活性，并为很多这些图像将被存储在服务器不在本地，这优化了效率，也让我在需要时图像移动到本地。我跟着一个（短）的教程，并写了这以下的方法recieving一个blob;
I'm having some trouble reading a blob back from a MySQL database. I've gotten it to successfully insert into the database, but can't seem to get it to read back. I know some of you might be thinking "why is he using a database to store blobs for images, and not just the file paths / file names", but i want to have flexibility and as a lot of these images will be stored on a server and not locally, this optimises efficiency, as well as allowing me to move images to local if needed. I've followed a (short) tutorial and have written this following method for recieving a blob;
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'm calling it like so..
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);
不过它的示数在BytesReturned = reader.GetBytes（1，CURRENTINDEX，斑点，0，缓冲区大小）;与错误的GetBytes只能被称为二进制或GUID列。我假定这是跟在我的数据库我的字段类型，但改变列输入的二进制意味着我必须然后存储之类的，作为一个blob，但我想离开这个文件名只是作为一个正常的字符串。有我丢失的东西？或者这样做的另一种方式？
However it's erroring on the BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0,BufferSize); with the error "GetBytes can only be called on binary or guid columns". I'm assuming this is to do with my field type in my database, but changing the column to type binary means i have to then store that as a blob sort of, but i want to leave the filename just as a regular string. Is there something i'm missing? or another way of doing this?
EDIT1：我认为bytesreturned第一个参数是做与读者列，将其设置为0提供了错误无效尝试使用SequentialAccess读取事先列，生病看这个
edit1 : i think the first parameter for bytesreturned is to do with column in the reader, setting this to 0 gives the error "Invalid attempt to read a prior column using SequentialAccess", ill look into this.
EDIT2：拆卸顺序访问给我的大小13个字节的文件，（？这可能是正好位于第一行，这就是为什么顺序访问读取所有的行），所以也许我读列以错误的顺序...
edit2 : Removing sequential access gives me a file of size 13 bytes, (which could be just the first row, which is why sequential access reads all rows?) so maybe i'm reading columns in the wrong order..
编辑3：我认为，对于这种错误的原因是因为我的样子输入到数据库中。已经改变这种方法，我saveBlob现在看起来像这样：
edit 3: i believe that the reason for this error was due to the way i was inputting into the database. having changed this method, my saveBlob now looks like so:
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();
}
}
我已经通过调试运行，都blobvalue和blobfileparam（@blobfile）拥有全尺寸（约15万），但它的示数时执行查询，给下面的错误;
i've ran through the debugger, and both blobvalue and blobfileparam(@blobfile) have the full size ( around 150k ), but it's erroring upon executing the query, giving the following error;
"unable to cast object of type 'system.byte[]' to type 'system.iconvertible"
我已经看到了在code和试图改变类型的二进制图像，允许更大的文件，但给出了同样的错误。任何人都知道这个新的信息什么？
i've taken a look into the code and tried to change types binary to image, to allow larger files, but gives the same error. Anyone know anything about this new information?
编辑4：固定的一切。注意到，在我的code我使用：
edit 4: fixed everything. noticed that in my code i was using:
("@BlobFile", SqlDbType.Binary);
改变了这些类型的MySQLDbType的（DERP），它让我选择BLOB类型的。事情终于如预期运行：）
Changed these to types "MySqlDbType" (derp) and it allowed me to choose types of blob. Things are finally working as intended : )
推荐答案
您是否尝试简化第一？而不是读BLOB 100个字节的时间，尽量简化你的code只读取所有字节到一个文件中。这样，您就可以轻松地排除数据层的问题。
Have you tried simplifying first? Instead of reading the BLOB 100 bytes at a time, try simplifying your code to just read all bytes to a file. This way you can easily rule out data layer issues.
下面的文档还建议您存储文件大小为另一列：的
The following documentation also suggests you store your file size as another column: http://dev.mysql.com/doc/refman/5.5/en/connector-net-programming-blob.html
这篇关于从MySQL数据库中读取BLOB图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
