Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > retrieving BLOB from Oracle

Reply
Thread Tools

retrieving BLOB from Oracle

 
 
matt@mailinator.com
Guest
Posts: n/a
 
      12-01-2006
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...b;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

 
Reply With Quote
 
 
 
 
matt@mailinator.com
Guest
Posts: n/a
 
      12-04-2006
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

 
Reply With Quote
 
 
 
 
RobinS
Guest
Posts: n/a
 
      12-04-2006
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.
----------------------------------
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> 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
>



 
Reply With Quote
 
matt@mailinator.com
Guest
Posts: n/a
 
      12-04-2006
RobinS wrote:
> 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

 
Reply With Quote
 
matt@mailinator.com
Guest
Posts: n/a
 
      12-04-2006
....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

 
Reply With Quote
 
nil
Guest
Posts: n/a
 
      12-05-2006

http://www.velocityreviews.com/forums/(E-Mail Removed) wrote:
> 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...b;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

 
Reply With Quote
 
matt@mailinator.com
Guest
Posts: n/a
 
      12-05-2006

(E-Mail Removed) wrote:
> ...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

 
Reply With Quote
 
matt@mailinator.com
Guest
Posts: n/a
 
      12-05-2006
nil wrote:
> 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

 
Reply With Quote
 
nil
Guest
Posts: n/a
 
      12-06-2006

(E-Mail Removed) wrote:
> nil wrote:
> > 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

 
Reply With Quote
 
matt@mailinator.com
Guest
Posts: n/a
 
      12-06-2006
nil wrote:
> 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieving Oracle BLOB Peter Afonin ASP .Net 11 07-27-2006 06:12 PM
Question about blob retrieving and jsp... gbattine Java 9 06-29-2006 05:18 PM
install_driver(Oracle) failed: Can't load 'C:/Perl/site/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle: load_file:The specified procedure could not be found at C:/Perl/lib/DynaLoader.pm line 230. Feyruz Perl Misc 4 10-14-2005 06:47 PM
Retrieving BLOB Datatype from SQL Server 2000 nkunapa Java 4 08-10-2005 04:12 PM
.NET and Oracle BLOB Robert Vabo ASP .Net 1 08-27-2003 08:11 PM



Advertisments