Storing PDF in SQL Server 2000 BLOB field

[Guest]

Hi,

I'm using the code in article http://support.microsoft.com/?kbid=309158 as
the basis for a C# (VS 2003) application which adds records to a SQL Server
2000 table containing a BLOB field. The BLOB field holds the binary content
of a PDF document. There is an existing application which writes to the same
database but I don't have the code for it. The PDF is retrieved from the DB
by an ASP web page. This works fine.

When the ASP application retrieves a PDF written to the DB by my C#
application it displays as if I'd opened it in notepad. Any clues on what I'm
doing wrong?

TIA,

Howard

 

[Paul Clement]

¤ Hi,
¤
¤ I'm using the code in article http://support.microsoft.com/?kbid=309158 as
¤ the basis for a C# (VS 2003) application which adds records to a SQL Server
¤ 2000 table containing a BLOB field. The BLOB field holds the binary content
¤ of a PDF document. There is an existing application which writes to the same
¤ database but I don't have the code for it. The PDF is retrieved from the DB
¤ by an ASP web page. This works fine.
¤
¤ When the ASP application retrieves a PDF written to the DB by my C#
¤ application it displays as if I'd opened it in notepad. Any clues on what I'm
¤ doing wrong?

Is this an ASP or ASP.NET application? Do you have some sample code to post so we can see what you
are doing?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

Hi Paul,

my application is a C# Windows service. It uses a filemonitor to determine
that a PDF has been placed in a folder. It converts the PDF information into
a SQL Server table row with the PDF itself written to an image column. The
example shown in the URL in my original message is pretty much what I'm doing
except it's a PDF I'm working with not a picture.

I've discovered that the first 200-odd bytes of the file appear to be
missing when retrieved from the DB. The DB reports the right size (in bytes)
for the file though

TIA,

H

 

[james]

Why would you want to store a PDF in SQL Server as a blob? It's better to
store the PDF as a file in a folder and place the location of the file as a
pointer in SQL.

 

[Paul Clement]

¤ Hi Paul,
¤
¤ my application is a C# Windows service. It uses a filemonitor to determine
¤ that a PDF has been placed in a folder. It converts the PDF information into
¤ a SQL Server table row with the PDF itself written to an image column. The
¤ example shown in the URL in my original message is pretty much what I'm doing
¤ except it's a PDF I'm working with not a picture.
¤
¤ I've discovered that the first 200-odd bytes of the file appear to be
¤ missing when retrieved from the DB. The DB reports the right size (in bytes)
¤ for the file though

Can the code in the KB example write the file back out to disk properly? If not, it would seem there
is a problem with the code used to insert the file into the database.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Mythran]

On Thu, 24 Mar 2005 05:55:03 -0800, "SysDevCS"

¤ Hi Paul,
¤
¤ my application is a C# Windows service. It uses a filemonitor to
determine
¤ that a PDF has been placed in a folder. It converts the PDF information
into
¤ a SQL Server table row with the PDF itself written to an image column.
The
¤ example shown in the URL in my original message is pretty much what I'm
doing
¤ except it's a PDF I'm working with not a picture.
¤
¤ I've discovered that the first 200-odd bytes of the file appear to be
¤ missing when retrieved from the DB. The DB reports the right size (in
bytes)
¤ for the file though

Can the code in the KB example write the file back out to disk properly?
If not, it would seem there
is a problem with the code used to insert the file into the database.


Paul
~~~~
Microsoft MVP (Visual Basic)

Otherwise, are you setting the content-type for the pdf to the proper value?

Mythran

 

[Elton Wang]

You can also use Stored Procedure to save BLOB data to DB

http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/cpguide/html/cpconwritingblobvaluestodatabase.asp

And use DataReader with SequentialAccess to obtain BLOB
data from DB

http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/cpguide/html/cpconobtainingblobvaluesfromdatabase.asp

HTH

Elton Wang
(e-mail address removed)

-----Original Message-----
Hi,

I'm using the code in article

http://support.microsoft.com/?kbid=309158 as

 

[Mythran]

Why would you want to store a PDF in SQL Server as a blob? It's better to
store the PDF as a file in a folder and place the location of the file as
a pointer in SQL.

Why do you say that? I'm curious as to why you would believe that.

Mythran

 

[WJ]

Why would you want to store a PDF in SQL Server as a blob?

Security ! If the PDF contents are sensitive, RDBMS is the best bank for it.
On top of that: A DBA can take advantage of the Maintenance/System backup
which is already in place for the target DB.

It's better to store the PDF as a file in a folder and place the location
of the file as a pointer in SQL.

You will introduce Update Anomalies because a same piece of data is stored
in multiple places, which is a no-no in DB design. On top of that, it is
less secure because Asp.Net application can only protect its own pages, not
foreign matters such as files and images sitting around.

John Webb

 

[JiangZemin]

Yea, there are pros and cons on both sides of this, but it doesnt make sense
to make a blanket statement like "its better to do it this way" without
knowing the infrastructure and requirements. That kind of stuff helps
create those annoying people at work who say things like "why are you doing
it that way? its better to do it this way... i read about it on a
newsgroup!".

the .NET data access architecture guide has short section on handling blobs
that may help in considering the issue:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp

 

[Cowboy \(Gregory A. Beamer\)]

BLOBs are fun to play with ... NOT.

One of the biggest challenges with BLOBs is gettting the entire content out.
the data gets chunked as it is pulled. For this reason, it is best to use a
Reader (which streams) when serving BLOBs. It looks like Elton beat me to
the URL, so check out his post.

Overall, I am not extremely fond of storing BLOBs in a database. There are
certainly cases where this is the wisest decision, but more often than not
the decision is made either because a) the app is already pulling data, so
it seems easier or b) there is a perception that it is easier to secure the
file as a BLOB. The security issue is partiallly true, as it is easier to
set up security in the database in many instances.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************

 

[Guest]

I know that this is necessary when retrieving data from a BLOB field - are
you saying that I should set content type when writing the data too?

 

[Guest]

I know that setting content type is necessary when retrieving data from a
BLOB field - are you saying that I have to do this when writing BLOB data to
the database too? If so, what do I need to do?

 

[Guest]

I know that setting content type is necessary when retrieving data from a
BLOB field - are you saying that I should also set content type when I
*write* to a BLOB field? If so, what do I need to do?

H

 

[Guest]

Good idea. I'll check this out. My application doesn't retrieve the data it
writes but I'll write a little test app to see what I get back.

H

 

[Guest]

I understand your curiosity - and others have provided their own opinions
here - my answer is that the DB is legacy and I *have* to write to a BLOB
field.

H

 

[Guest]

Hi, to avoid opening document in IE use Response.Writefile method followedby
Response.end