Hi there,
I am trying to store a byte[] array of Image data into a BLOB column, however i am having some difficulty.
Table Structure
FX_IMG_ID (PrimaryKEY)
FX_IMG_DESC
FX_IMG_DATA (BLOB)
FX_IMG_EXT
My C# application is simply taking the user provided base64encoded string and converting it to byte[] array. Correct me if i am wrong, a byte[] array maps to BLOB in Oracle? The byte[] array could be up to 1MB/2MB in size.
Is the conversion approach correct? Please bear in mind i want to read this data out later but that's a separate topic for discussion. I need to be able to store the image correctly in the BLOB column. I would greatly appreciate if you could please provide me feedback/code sample.
Here is my test SQL code which i did and it works but its NOT taking into account of the binary[] data as i am finding difficult to implement this particular part. My current example is reliant on a physical directory where the image file resides. I would like to get away from this if possible.
DECLARE
l_dir VARCHAR2(10) := 'IMAGES';
l_file VARCHAR2(25) := 'test.jpg';
l_bfile BFILE;
l_blob BLOB;
BEGIN
INSERT INTO IMAGE_TBL (FX_IMG_ID, FX_IMG_DESC, FX_IMG_DATA, FX_IMG_EXT)
VALUES (IMAGE_TBL_SEQ.NEXTVAL, l_file, empty_blob(), '.jpg')
RETURN FX_IMG_DATA INTO l_blob;
l_bfile := BFILENAME(l_dir, l_file);
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
DBMS_LOB.fileclose(l_bfile);
COMMIT;
END;
Here is my dummy C# method I made to pass in the byte[] array(its not final solution):
public void UploadImage(OracleConnection myConnection, ImageDAO theImage)
{
myConnection.Open();
//
// logic to produce the binaryData is here.
//
//
byte[] data = Convert.FromBase64String(base64EncodedString);
//Perform INSERT
OracleCommand myCommand = new OracleCommand(
"INSERT INTO FX_IMAGE (FX_IMG_ID, FX_IMG_DESC, FX_IMG_DATA, FX_IMG_EXT)
VALUES(1,'pict1',:Img_Data, '.jpg')", myConnection);
OracleParameter myParam = myCommand.Parameters.Add(":Img_Data", OracleDbType.Blob);
myParam.OracleValue = data;
try
{
Console.WriteLine(myCommand.ExecuteNonQuery() + " rows affected.");
}
finally
{
myConnection.Close();
}
}
Questions
In Oracle is it mandatory to store the a physical image on a server directory? i.e Can i not just take the byte[] of data from C# pass it to the database proc and simply store it in the BLOB column without the need to store the image physically on a server directory? if yes then what are my options? Can someone provide me with a sample please?
Kind Regards,