Putting PDFs into Oracle

Anyone experienced with blobs?

[RossWal]

I need to write a VB process that graps PDFs that are being FTP'd to a folder, and store the PDF's off in Oracle. Next I need to be able to take them from Oracle and pass them through for browser disply via a .net process.

I'm just starting researching this. Has anyone done anything like this before, either writing or retrieving blobs in Oracle?

Thanks!

Ross

[sun818]

Store the PDF file in a folder on the server. Record the file name only in a table using Oracle only as a pointer to the file. You may run into export and conversion issues later if you store the PDF inside the database.

[RossWal]

That was my first plan, then I started thinking that blobs have been around for a good while, and it seems to me this is exactly what they are intended for. On the otherhand, I've yet to find anyone who champions their usage.

Thanks,
Ross

[Xoc]

The magic lines you want are (from some of my code, where I've got a dataset named dsBugDetails with a Table named TblAttachments, that I'm doing a lookup into by strId to find a blob field named binAttachment):

Public Class Attachment 
Implements IHttpHandler 
Public ReadOnly Property IsReusable() As Boolean Implements System.Web.IHttpHandler.IsReusable 
Get 
Return False 
End Get 
End Property 
Public Sub ProcessRequest(ByVal context As System.Web.HttpContext) _ 
Implements System.Web.IHttpHandler.ProcessRequest 
'some code goes here to retrieve strId from the  
'file name or query string or some such 
'and fill the dataset with the record. 
Dim abyte() As Byte _ 
= dsBugDetails.TblAttachments.FindByintAttachmentId(CInt(strId)).binAttachment 
context.Response.OutputStream.Write(abyte, 0, abyte.Length) 
context.Response.End() 
End Sub 

Then you need to add to the web.config file to the directory where this is located (where the DefaultNamespace is the namespace that the Attachment class comes from and AssemblyName is the assembly it is found in):

<?xml version="1.0" encoding="utf-8"?> 
<configuration> 
 <system.web> 
<httpHandlers> 
<add verb="GET,HEAD" path="*.pdf" type="DefaultNamespace.Attachment,AssemblyName" /> 
</httpHandlers> 
 </system.web> 
</configuration> 

You will also need to modify the IIS mappings to have *.pdf to go through .NET.

[aspdaddy]

pros - data integrity and security
cons - db bloat, probably not an issue in oracle

I have only use blobs in access to store biz rules and configuations where I dont want the client to be able to read it, but dont want to hard-code it either.