retrieving BLOB from Oracle

[matt]

hello,

im doing my first ASP.NET app that inserts & retrieves files from
Oracle (no need for a discussion on *that*!).

i learned first-hand of the somewhat awkward technique for inserting
binary data into an Oracle BLOB column via ADO.NET. since my files are
larger than 33k, it seemed had to use this technique:

http://support.microsoft.com/default.aspx?scid=kb;en-us;322796

....in which you fill an OracleLob object and pass it into your BLOB
column (via a proc in my case).


now i have to retrieve my blob file, and response it to the user. i
imagine the process is something like: 1) get data out of db, 2)
convert to working format 3) response to client.

however, im running into hitches. not sure what is wrong. can anyone
post snippets of working code for doing this? for retrieval, heres my
snippet:


//conn & command
OracleConnection conn = new
OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
OracleCommand command = new
OracleCommand("COFE.GetInspectionReportByID", conn);
command.CommandType = CommandType.StoredProcedure;

//params
command.Parameters.Add("p_fileID", OracleType.Number).Value = reportID;
command.Parameters.Add("cur_results", OracleType.Cursor).Direction =
ParameterDirection.Output; //output

OracleLob blob = null;

//fill blob
conn.Open();
OracleDataReader dr = command.ExecuteReader();

if (dr.Read()) //has row
blob = dr.GetOracleLob(5);

byte[] byteArray = (byte[])blob.Value;

conn.Close();

//response (kitchen sink for tests)
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=" +
report.FileName);
Response.BufferOutput = true;

Response.BinaryWrite(byteArray);
Response.End();


....however, when excel loads the file, it says its an "unrecognizable
format". opening it in notepad, its empty. *but*, right-clicking the
file, its size on disk matches the pre-databased version exactly!


any idea what im missing?


thanks!
matt

 

[matt]

anybody? anybody know how to retrieve a binary file out of an Oracle
BLOB column via a proc? c'mon, somebody has to know.....

thanks,
matt

 

[RobinS]

If nobody here seems to have an answer for you, maybe you could
post this to an Oracle group?

Sorry I can't help you.
Robin S.

 

[matt]

If nobody here seems to have an answer for you, maybe you could
post this to an Oracle group?

yeah thats my next stop. i just figured since this app-code (.NET)
dependant, and not really an Oracle issue, that the .NET groups would
be most on-topic.

thanks,
matt

 

[matt]

....i tested this w/ another file format, .PDF. same thing -- the byte
array of the retrieved file matches the original's size-on-disk. when
its saved via the response to disk, it also matches the orig.

however, opening it is impossible. opening it in notepad yields an
empty file, devoid of the normal binary garbage characters.

if somebody could help me confirm the insert/retrieve techniques, id
likely be golden.


matt

 

[nil]

hello,

im doing my first ASP.NET app that inserts & retrieves files from
Oracle (no need for a discussion on *that*!).

i learned first-hand of the somewhat awkward technique for inserting
binary data into an Oracle BLOB column via ADO.NET. since my files are
larger than 33k, it seemed had to use this technique:

http://support.microsoft.com/default.aspx?scid=kb;en-us;322796

...in which you fill an OracleLob object and pass it into your BLOB
column (via a proc in my case).


now i have to retrieve my blob file, and response it to the user. i
imagine the process is something like: 1) get data out of db, 2)
convert to working format 3) response to client.

however, im running into hitches. not sure what is wrong. can anyone
post snippets of working code for doing this? for retrieval, heres my
snippet:


//conn & command
OracleConnection conn = new
OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
OracleCommand command = new
OracleCommand("COFE.GetInspectionReportByID", conn);
command.CommandType = CommandType.StoredProcedure;

//params
command.Parameters.Add("p_fileID", OracleType.Number).Value = reportID;
command.Parameters.Add("cur_results", OracleType.Cursor).Direction =
ParameterDirection.Output; //output

OracleLob blob = null;

//fill blob
conn.Open();
OracleDataReader dr = command.ExecuteReader();

if (dr.Read()) //has row
blob = dr.GetOracleLob(5);

byte[] byteArray = (byte[])blob.Value;

conn.Close();

//response (kitchen sink for tests)
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=" +
report.FileName);
Response.BufferOutput = true;

Response.BinaryWrite(byteArray);
Response.End();


...however, when excel loads the file, it says its an "unrecognizable
format". opening it in notepad, its empty. *but*, right-clicking the
file, its size on disk matches the pre-databased version exactly!


any idea what im missing?


thanks!
matt

hey can u tell me how can i save the image in the oracle in blob column
and retrieve from that?thanks in advance...
nil

 

[matt]

...however, when excel loads the file, it says its an "unrecognizable
format". opening it in notepad, its empty. *but*, right-clicking the
file, its size on disk matches the pre-databased version exactly!

figured it out. it had nothing to w/ my retrieval code.

the files were coming out of oracle w/ the correct byte size, but empty
in notepad, because i inserted them incorrectly.

for the insert, i had *sized* the byte array prior to blob-and-insert,
but i hadnt *filled* it! so the insert worked, the retrieval worked,
but the file contents were simply empty.


matt

 

[matt]

hey can u tell me how can i save the image in the oracle in blob column
and retrieve from that?thanks in advance...
nil

well, first follow the link in my OP. if youre using the ms
dataprovider (System.Data.OracleClient), and not ODP.NET (oracle's data
provider) then its how you do the blob shuffle.

getting the file *out* was much simpler for me:

//conn & command
OracleConnection conn = new
OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
OracleCommand command = new OracleCommand("Foo.GetFileByID", conn);
command.CommandType = CommandType.StoredProcedure;

//params
command.Parameters.Add("p_reportID", OracleType.Number).Value =
reportID;
command.Parameters.Add("cur_results", OracleType.Cursor).Direction =
ParameterDirection.Output; //output

//do it
DataSet ds = new DataSet();
OracleDataAdapter da = OracleClient.OracleDataAdapter(command);
da.Fill(ds);

//get file contents from returned data
byte[] fileContents = (byte[])row["FileContents"];

Response.BinaryWrite(fileContents );


matt

 

[nil]

hey can u tell me how can i save the image in the oracle in blob column
and retrieve from that?thanks in advance...
nil

well, first follow the link in my OP. if youre using the ms
dataprovider (System.Data.OracleClient), and not ODP.NET (oracle's data
provider) then its how you do the blob shuffle.

getting the file *out* was much simpler for me:

//conn & command
OracleConnection conn = new
OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
OracleCommand command = new OracleCommand("Foo.GetFileByID", conn);
command.CommandType = CommandType.StoredProcedure;

//params
command.Parameters.Add("p_reportID", OracleType.Number).Value =
reportID;
command.Parameters.Add("cur_results", OracleType.Cursor).Direction =
ParameterDirection.Output; //output

//do it
DataSet ds = new DataSet();
OracleDataAdapter da = OracleClient.OracleDataAdapter(command);
da.Fill(ds);

//get file contents from returned data
byte[] fileContents = (byte[])row["FileContents"];

Response.BinaryWrite(fileContents );


matt

should i follow the same thing when i want to retrieve image from the
database and what if i am using oracle's provider like msdaora.1.....
so is there any way to do for this provider....thanks a lot for reply

 

[matt]

should i follow the same thing when i want to retrieve image from the
database and what if i am using oracle's provider like msdaora.1.....
so is there any way to do for this provider....thanks a lot for reply

dunno, dude. research time.