I am storing an ammount of files, of different types, As blobs in My SQL Server 2000 database.
Some of these are XML files, inside of which are some addresses I 'd like to read.
However, I don't want to download a 2 MB jpg image from the DB, just to find out it isn' t an XML file!
My best solution after playing around und was to extract the first 5 bytes from the data, to determine if I shoshould go ahead and use the whole thing:
// Get the 'image' SQL type
Sqldatareader = selectdatacommand. executereader (commandbehavior. sequentialaccess );
// If successful
If (sqldatareader. Read ())
{
Int buffersize = 5;
Byte [] outbyte = new byte [buffersize];
Long startindex = 0;
// Read the first 5 bytes
Long retval = sqldatareader. getbytes (0, startindex, outbyte, 0, buffersize );
// Translate bytes to string
String test = fromasciibytearray (outbyte );
// Do the first 5 Characters match an xml header?
If (test. compareto ("<? XML ") = 0)
{
// Get entire file here
}
}
This is quicker, but I know it can't be the quickest way. I am trying to find out how to write a stored prodecure that will do the same on the SQL server side.
And yes, I cocould just add an extra byte column to my table to designate the file as XML, But I am interested in how to parse image data types on the SQL side.
I'll update when I find out how
From: http://dotnetjunkies.com/WebLog/andrewwhitten/archive/2004/03/20/9569.aspx