Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Andy Kramek
  Where is Andy Kramek?
 Hot Springs Village
 Arkansas - United States
 Andy Kramek
 To: solomon sackey
  Where is solomon sackey?
 Texas - United States
 solomon sackey
Subject: RE: wk-sheet, pictures in memo field
Thread ID: 155360 Message ID: 155373 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: General VFP Topics
Date: Monday, December 31, 2007 11:46:38 AM         

> Dear Expert,
> How do you handle worksheets, pictures and scanned documents in a vfp sql server application?
> In short where is the appropriate place to store & retrieve pictures, scanned documents and worksheets?
> I am keeping track of yearly bugdets of departments in a spreadsheet. I will like images of it incorporated in a datafile (.i.e sql server) such that if I select a particular department all the references to all the yearly spreadsheets will appear ina grid.
> On selecting the reference in the grid it will pull the appropriate image, spreadsheet etc.

I strongly suggest that you do NOT store the images themselves directly in the database but keep them as separate files on disk and store only the path, filename, description and perhaps some searchable key words in the database.

There are a number of reasons for this.

First there is no real benefit to storing the images directly in the database - you cannot search or access parts of the image independently, or separate it into logical components. It is a single mass of data that must be handled as a unit and it reallly makes no practical difference whether that unit is astand-alone file on disk, or a column in an extended file (images/blobs in SQL are just like Memo fields in VFP - they are stored in separate files and linked to the tables by pointers).

Second, it vastly increases the size of database. This makes backups and restores take longer and there is always the issue of integrity when restoring large amounts of binary data (though this is supposedly less of an issue in SQL 2005 than it used to be).

Third it also bloats the transaction logs - any time you insert data the entire image goes into the transaction log and you will run into major issues with transaction logs filling up and having to be extended - which dramatically slows the performance of the database - and all dependent applications!

Fourth, you vastly increase the network load if you include the images in queries - every image gets shipped whether or not it is wanted. If you don't include the image column in the initial query then there is no point in having the image in the database since you will have to make a second call the server to retrieve the image anyway.

That is the main downside to storing the files on disk; i.e. that you have to make an extra trip to the server to retrieve the image when you want to display it, but it sounds as if you are planning to do that anyway, and only display a file after the user has chosen it from a listing in a grid. Trust me, you will save yourself a lot of grief if you keep your image files OUT of the database.

Andy Kramek
Microsoft MVP (Visual FoxPro)
Tightline Computers Inc, Akron Ohio, USA


wk-sheet, pictures in memo field Posted by solomon sackey @ 12/31/2007 8:09:51 AM
RE: wk-sheet, pictures in memo field Posted by Andy Kramek @ 12/31/2007 11:46:38 AM
RE: wk-sheet, pictures in memo field Posted by tushar @ 12/31/2007 3:19:48 PM
RE: wk-sheet, pictures in memo field Posted by Ken Murphy @ 1/1/2008 3:58:42 PM