Storing graphic files in SQL Pro-Cons

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Storing graphic files in SQL Pro-Cons

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-09-22 : 14:19:40

I am thinking of incorporating .PDF files into applications and storing them in a "image" field as this is the easiest for the user to cut and paste in.

Pros-----Cons?

Any thing to watch for?

Performance Issues?

Application only selects one record at atime.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-22 : 14:47:40

Do not store files in the database. Store the files on the file system and put the pointer to the file in the database.

I doubt anyone here is going to recommend the approach that you are going down.

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-09-22 : 15:51:38

I totally agree with Tara. I'd love to see what "pros" there are if anyone has any.

AFAIK, it's harder to work with and makes your backups take so much time because you have all those "files" in your DB. I think those are the two biggest downsides.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-09-23 : 05:13:36

Yeah. Tara and Michael points are correct.I think no "Pros" regarding this.

":-) IT Knowledge is power :-)"

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-09-23 : 05:24:21

I agree to tara and micheal but also storing the images in the tables can also secure the files .

The Judgement of the Judge is as good as the Judge.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-23 : 07:46:57

Not really. In the database they may be secure from casual file and folder browsing, but they are fully accessible to anyone with access to the database. Unless you encrypt each file before loading it in the database, all you're doing is collecting all the files in one place and making a hacker's job easier. Keeping the files on disk lets you set security for each file, each folder, each drive, etc. And if a user only needs access to a few files, you don't need to grant them (full) database access to get them.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-09-23 : 10:18:19

All good points

The only reason I have considered it, is that the user is placing the PDF into the database and I have no experiance accessing graphic files using a pointer from the database.

Note: this file must be accessed by another user at one of the machines via his operating form.

Remember This manufacturing most of our users have trouble tying their own shoes.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 13:05:07

Jim, the files would be stored on the server, so the application would get the files from the server and display them in the application. When a user wants to submit a file, you just take the file and put it on the server then store the path and file name in the database (that is the pointer). The users do not have know anything about moving files, your application will handle it for them.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 13:51:53

Tara,

I'm thinking he's discussing pointers like:



	If TextValid('Table1.imgPicture', @ptrValue) <>  1
		BEGIN
			Select @Error_Loc = 1
			Select @Error_Message = 'Pointer value not valid.  Value equals: ' + RTrim(Convert(Char(16),@ptrValue))
			Select @Error_Type = 50002
  			GOTO ImageTest2_Error
		END

  	ReadText Table1.imgPicture @ptrValue @Offset @Size

	Select @Result_Count = @@ROWCOUNT, @error_out = @@error

	If @error_out <> 0
		BEGIN
			Select @Error_Loc = 2
			Select @Error_Type = 50001
  			GOTO ImageTest2_Error
		END
	
	If @Result_Count = 0
		BEGIN
			Select @Error_Loc = 2
			Select @Error_Message = 'Could Not find image for pointer value ' + RTrim(Convert(Char(16),@ptrValue))
			Select @Error_Type = 50002
  			GOTO ImageTest2_Error
		END
	
	If @Result_Count > 1
		BEGIN
			Select @Error_Loc = 2
			Select @Error_Message = 'Found multiple images for pointer value ' + RTrim(Convert(Char(16),@ptrValue))
			Select @Error_Type = 50002
  			GOTO ImageTest2_Error
		END

I was able to put and get PDF's in and out...but they always seem to be corrupted a little bit...gave up before I could figure out what I was doing wrong...pdf does a nice job a self correcting btw

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-09-23 : 16:28:48

quote:
Originally posted by tduggan

Jim, the files would be stored on the server, so the application would get the files from the server and display them in the application. When a user wants to submit a file, you just take the file and put it on the server then store the path and file name in the database (that is the pointer). The users do not have know anything about moving files, your application will handle it for them.

Tara

Tara they need to add the PDF, I do not have it, They have it. also if I need to place it then ceate the path and add it to the DB. it defeats the whole purpose of the application.

Also I would Like to go home from time to time.

Here is the scoop

Our Graphic deparment recives .AI And .PDF files from the customer. They use the .AI to make printing plates and The .PDF needs to be linked to the job master file so that the printing pressman can view it on his job screen to check for copy and approve the job to print.

This can happen any time day or night and our schedule changes at a moments notice.

Brett...... if it messes up the file even a little its a NO GO.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 16:54:50

Hey, I didn't say that the process messes up...I said I did...

But from everything you read, you're better off not storing them in the db...

I would like to get it to work though...

Any canned code out there to manipulate documents and track in the db?

Did you search the articles on the site yet?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 17:16:14

quote:
Originally posted by JimL

Tara they need to add the PDF, I do not have it, They have it. also if I need to place it then ceate the path and add it to the DB. it defeats the whole purpose of the application.

Also I would Like to go home from time to time.

It doesn't matter where the PDF is coming from. When you get it from the user, you copy it over to the server programmatically.
The application will know where it stored it on the server, so it will know the path and file name, so you then store that in the database. Your application will handle all of this. And this does not defeat the purpose of the application.

Tara

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-09-23 : 20:26:34

While I agree that the pointer method is generally the way to do it, in all fairness there *are* pros for the database method:
[list]

In organizations where the appdev and DBA people don't control the file servers, it can speed development time by not requiring involvement from those who do manage the file servers

With a limited budget, it may make sense to have a single very robust server. Given a choice of putting images in the DB that's on a nice RAID system or on a whitebox utility server that's got a single IDE drive, I'll take the performance hit from using the DB.

Similarly, it reduces the number of disparate systems for backup purposes

I still mostly wouldn't do it, though I do support one app that uses images-in-DB. But that was a design choice that I wasn't responsible for, and it's not enough of a pain to merit putting off new work to rearchitect that app.

Cheers
-b

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-09-24 : 08:19:55

JimL.
It maybe you are missing a step as you think this out.
The application you build will act as a file copy/delete/overwrite agent for the user. There are lots of ways to do this. Web based solutions
often use an Upload component and FSO(file system objects.) Visual languages have tree views, folder browsers, dir browsers etc. that the user utilizes to identify
files, The application constructs a new path and records it as a database entry. When the data is written there is also a file copy/delete/overwrite event that takes place.
So you create a application wide file path definition (or a bunch) that points to location in your environment. Some place locked down,
not browsable, except by your application that has a lot of space for future growth, the idea being that file copy/delete/overwrite can be done only in your application. It can makes sense to rename the files to assure uniqueness. Basically it comes down to building file names and paths as strings then asking the application to move/copy/delete/overwrite whenever it needs to. These are totally vital applications to have flying around in a manufacturing environment, the big reason is the natural audit trial when things go bad. You will also end up dabbling in version control at some point. So what first appears as kind of innocent statement,
"Some place locked down,not browsable, except by your application"
has far reaching impact.

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources