ASP.NET mySQL BLOB

[cuyler.jones]

Hello --

I'm having a heck of a time grabbing a blob ( a jpeg image) from a
mySQL database and displaying it on a page.

I am able to connect to the database and retrieve the data, however
when the page loads, it just spews the binary garbage rather than
displaying the image.

Here's the code:

string _connectionString = ConfigurationManager.ConnectionStrings[ "DB"
].ToString(); string imageQuery = "SELECT fullsize AS image_data
FROM table WHERE id ='A1'";

OdbcConnection connection = new OdbcConnection(
ConfigurationManager,ConnectionStrings["DB"].ToString() );

OdbcCommand cmd = new OdbcCommand( imageQuery, connection );

DataSet dsImage = new DataSet();

Response.ContentType = "image/jpeg";
Response.BinaryWrite( ( Byte[] )dsImage.Tables[ 0 ].Rows[ 0 ][
"image_data" ] );



It appears to be a problem with Base64Decoding... but I'm stumped. I'm
basically trying to replicate some PHP code:
<?

$image_data = (isset($_GET['swatchthumb'])) ? 'select swatchthumb as
image_data from birdie_product_model where uid = "'.$_GET['uid'].'"' :
'select fullsize as image_data from birdie_product_model where uid =
"'.$_GET['uid'].'"';

$image_data = mysql_fetch_object(mysql_query($image_data));

header("Content-type: image/jpeg");

echo base64_decode($image_data->image_data);
?>


Any insight (beyond the obvious (don't use Blobs!) ) would be MOST
appreciated!

Regards,

Cuyler Jones

 

[KJ]

I believe you need a little more in the headers. For example (code not
tested):

Response.Clear();
Response.ContentType = "image/jpeg";
Response.AppendHeader("Content-Length", ( Byte[] )dsImage.Tables[ 0
].Rows[ 0 ]["image_data" ].Length.ToString());
Response.AppendHeader("Content-Disposition","inline;filename=AName.jpeg);
Response.BinaryWrite(( Byte[] )dsImage.Tables[ 0 ].Rows[ 0
]["image_data" ]);
Response.End();

 

[Cuyler]

Thank you for the information. Unfortunately it has yeilded the same
results.

Regards,

Cuyler Jones

I believe you need a little more in the headers. For example (code not
tested):

Response.Clear();
Response.ContentType = "image/jpeg";
Response.AppendHeader("Content-Length", ( Byte[] )dsImage.Tables[ 0
].Rows[ 0 ]["image_data" ].Length.ToString());
Response.AppendHeader("Content-Disposition","inline;filename=AName.jpeg);
Response.BinaryWrite(( Byte[] )dsImage.Tables[ 0 ].Rows[ 0
]["image_data" ]);
Response.End();

Hello --

I'm having a heck of a time grabbing a blob ( a jpeg image) from a
mySQL database and displaying it on a page.

I am able to connect to the database and retrieve the data, however
when the page loads, it just spews the binary garbage rather than
displaying the image.

Here's the code:

string _connectionString = ConfigurationManager.ConnectionStrings[ "DB"
].ToString(); string imageQuery = "SELECT fullsize AS image_data
FROM table WHERE id ='A1'";

OdbcConnection connection = new OdbcConnection(
ConfigurationManager,ConnectionStrings["DB"].ToString() );

OdbcCommand cmd = new OdbcCommand( imageQuery, connection );

DataSet dsImage = new DataSet();

Response.ContentType = "image/jpeg";
Response.BinaryWrite( ( Byte[] )dsImage.Tables[ 0 ].Rows[ 0 ][
"image_data" ] );



It appears to be a problem with Base64Decoding... but I'm stumped. I'm
basically trying to replicate some PHP code:
<?

$image_data = (isset($_GET['swatchthumb'])) ? 'select swatchthumb as
image_data from birdie_product_model where uid = "'.$_GET['uid'].'"' :
'select fullsize as image_data from birdie_product_model where uid =
"'.$_GET['uid'].'"';

$image_data = mysql_fetch_object(mysql_query($image_data));

header("Content-type: image/jpeg");

echo base64_decode($image_data->image_data);
?>


Any insight (beyond the obvious (don't use Blobs!) ) would be MOST
appreciated!

Regards,

Cuyler Jones

 

[Mischa Kroon]

Any insight (beyond the obvious (don't use Blobs!) ) would be MOST
appreciated!

First off then, you might want to use the mysql connector.
Which is bound to perform a lot better then ODBC

This is a help segment talking about blobs:

http://dev.mysql.com/doc/refman/5.0/en/connector-net-using-blob.html

Release version:
http://dev.mysql.com/downloads/connector/net/1.0.html

 

[Cuyler]

Mischa,

Thank you for your input. I tried the mySQL connector and followed the
example, however I ended up in the same spot.

The line: FileSize = myData.GetUInt32(myData.GetOrdinal("file_size"));
from the example, throws and exception. (Cannot convert type
System.Byte[] to System.IConvertable).

One of the problems is that I do not have the size of the file stored
in the database, so I had to get it manually:

string test = myData.GetString(0);
FileSize = Convert.ToUInt32( test.Length );
....

Addtionally, the line: myData.GetBytes(myData.GetOrdinal("file"), 0,
rawData, 0, FileSize); from the example requires a cast of "FileSize"
from UInt32 to int, which is a potential issue.

I am beginning to think that what I am trying to do is impossible with
the C#2.0 / mySQL combination, or the problem is beyond my current
skill level, as I have exhausted every resource that I can think of.

Regards,

Cuyler Jones