Retrieve medium blob from MySQL return only 13 bytes

Question

I searched lot and tried various methods but couldn't able to solve this problem. i need to save image into mysql file.

i used below code to save image into database.

try
{
    string location = @"C:\Users\test\Downloads\Photos\batman-greenscreen.jpg";

    FileStream fs = new FileStream(location, FileMode.Open, FileAccess.Read);
    UInt32 fileLength = (UInt32)fs.Length;
    byte[] buffer = new byte[fileLength];
    fs.Read(buffer, 0, (int)fileLength);

    string sqlPhotoQuery = "INSERT INTO tab_photo VALUES('" + photo.PhotoId + "','" + photo.ProjectID + "','" + photo.Day + "','" + photo.Barcode + "','" + photo.Photoname + "','" + photo.PhotoXml + "','" + buffer + "','" + fileLength + "')";
    int result = MySqlHelper.ExecuteNonQuery(connectionString, sqlPhotoQuery);
    if (result > 0)
        return true;
    else
        return false;
}
catch (Exception e)
{
    return false;
}   

in this file image length is 12428,

what i tried for retrieve data from database:

Photo photo = new Photo();
try
{
    string sqlQuery = "SELECT * FROM tab_photo where PhotoId='"+photoId+"'";
    MySqlDataReader rdr = MySqlHelper.ExecuteReader(connectionString, sqlQuery);
    while (rdr.Read())
    {
        photo.PhotoId = rdr.GetString("PhotoId");
        photo.ProjectID = rdr.GetString("ProjectID");
        photo.Day = rdr.GetInt32("Day");
        photo.Barcode = rdr.GetString("Barcode");
        photo.Photoname = rdr.GetString("Photoname");
        photo.PhotoXml = rdr.GetString("PhotoXml");

        MemoryStream ms;
        UInt32 FileSize;
        Bitmap outImage;

        int fileSize = rdr.GetInt32(rdr.GetOrdinal("PhotoSize"));
        byte[] rawData = new byte[fileSize];
        rdr.GetBytes(rdr.GetOrdinal("Photo"), 0, rawData, 0, (Int32)fileSize);

        photo.imageByte = rawData;

    }
}
catch (Exception e)
{}

but when i retrieve file it retrieve only for first 13 bytes. after that its value is zero and there are differences in retrieve values.

i have followed instructions mentioned in Handling BLOB Data With Connector/Net.

according to that i have changed my settings as follows:

this is how data saved in database

what are the reasons that i can't get correct values? is there any problem in data saving or data retrieving or database configurations?

Answer 1

You cannot have the correct data in the database. By concatenating the buffer into the query string, "System.Byte[]" will be inserted into the database, since the insert statement will basically look like

INSERT INTO tab_photo VALUES(...,'System.Byte[]', ...)";

Use parameterized queries as they do in the example, and you should be good.

Do not just concatenate strings from somewhere into your SQL queries. (And do not catch and swallow all exceptions in a method like this. That makes it really hard to debug in case of a failure.)