how can i store a pdf file in a table?

[nujdonfoe11]

my boss wants me to create a program to store scanned documents to a visual foxpro table so that we can get rid of the enormous amount of document files in our office which is taking up so much space. how can i store the scanned documents and retrieve them in the future for viewing or printing? please help me on this. thanks! By the way, i'm using visual foxpro 8.

 

[GriffMG]

Hi

There are, naturally, a number of ways to do this. The simplest is by far the worst, and that is to have a table with a GENERAL field in it and use the APPEND GENERAL command
to put the .pdf into it. Please DO NOT use this approach or I may be lynched for even saying the words (bit like saying the name Voldemort in a Harry Potter book).
This approach works, but suffers from TWO gotchas. a) the file containing the .pdf is a memo field, and it is difficult to retrieve the orginal file and b) it is restricted
to 2GB which will very quickly be eaten up, maybe as little as a few hundred thousand documents could hit the mark.


Way no. 2 is a TINY bit better, that is to use a Binary Memo field, and use REPLACE MyField with FILETOSTR("MyFile.PDF"). This works, and so long as you know the file type, you can
get the original file back easily. ** BUT ** the 2GB maximum file size still bites pretty quickly. Please DO NOT use this approach either.

No. 3 is the path (sic) to take. Don't store your files in the actual table, put the file names in there instead (finding some way to leave off the drive reference).
Then move the original file into a directory structure, instead of the database. This enables your data to grow almost without limits and, with a little imagination, span drives if needed.

Even if you only coded the files by the date or month they were received, they would remain manageable (so you might have a structure that used a new folder for each month, or week, or day or year).
Try to keep the files to no more that a thousand in each folder (or Windows is slow to manage them), the likely absolute max is probably going to be 64,000 in each folder.

I hope that helps a little.

Let us know how you get on.




Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Mike Lewis]

I endorse Griff's option 3.

Storing PDFs in a FoxPro table is not a good idea. You will get highly bloated files, difficulties in extracting the documents, and problems caused by VFP's 2 GB limit.

A much better plan is to store the files in a dedicated set of folders on a suitable drive. Then just store the filename and path within the table.

Going further, you can impose a structure on the folders, based for example on the date of the PDF. In that scheme, each file would have its date as its filename. You would have a folder for each year, and within each year a sub-folder for each month. Tht way, you would only have store the filename in the table, and your VFP code could determine the path. But that's not essential. The important point is not to store the PDFs in the table itself.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

Not that is is really needed, but I too will endorse Griff's Option 3.

The others have already given you the reasons to NOT store your PDF files within the VFP data tables, so I don't have much to add.

But perhaps yet one more endorsement will help you decide.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

I'll take another approach to the question. If you want document management, there is software more specialised on this than a database, that doesn't only apply to VFP.

If you just have the problem of sluggush behaviour ot Windows Explorer listing a single directory with tens of thousands of files in a single, you could improve that situation by a) using another file explorer, eg file commander. Then of course also simply by creating some folders and file your PDFs there. Eg in the structure Mike suggests:

Going further, you can impose a structure on the folders, based for example on the date of the PDF. In that scheme, each file would have its date as its filename.

Then VFP might of course help filing the PDFs. ADIR() can get you a list of files including file dates, so you can automatically sort them into the directory structure. You might also make that sorting depend on the file name (alphabetical sub dirs abc,def,ghi,...xyz) or create a sub dir name from an ID, eg ID 123456 -> basedir\1234\56\the.pdf or you name the pdfs with ID, thereby having unique file names.

If you really want to store the files themselves into a database, MS SQL Server has better facilities to host documents in filestreams. And guess what? That indeed also is done by putting files into a directory managed by MS SQL Server. This illustrates the file system is still the best place for files and a database should merely manage large amounts of files with their meta data. Besides a file name, that might include dates, author, topic, etc. as far as you can automtacially retrieve that info or are willing to add that info manually.

Bye, Olaf.

 

[nujdonfoe11]

Thanks for the useful tips. I followed the third option of using a memo field and setting the path for the pdf file in the memo field but i do not know how i could preview and print it programmatically. So far, what i have made is a form where i can search for a particular employee, search for a particular document file of that employee such as, a personal data sheet, but i could not preview nor print it. That is basically what i want to do. Hope you can help me on this. I'm not very familiar with handling objects created from other applications. Thanks again!

 

[Mike Lewis]

You can display and/or print the PDF by using ShellExecute(). If you haven't used that function before, this article will tell you how to do it.

You can easily integrate this into your application. For example, if you are displaying the document names in a grid, you can put your ShellExecute() code in the DblClick event of the grid, so the user will be able to view the document simply by double-clicking on its name.

Come back if you need more information.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads