I want to create the table store the pdf file. How do I import the files.
Suppose I have two fields NAME, and INVOICE. For field INVOICE I want to store the pdf file.
Please advise. Thank you very much...
Why do not use path instead of storing the whole file
I am not expert. Please explain more detail.
You can use several methods to insert files (pdf and other file types) to be stored in SQL Server table that includes a column defined with the IMAGE data type. You can use BCP.exe (Bulk Copy Program), BULK INSERT, TextCopy.exe as well as ADO and other programming languages, below are some KB articles that describe these methods as well as SQL code examples. Note, more details on BCP and BULK INSERT can be found in SQL Server 2000 Books Online (BOL).
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with VB.NET
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through ASP.NET
-- BCP IN file using xp_cmdshellexec master..xp_cmdshell 'bcp pubs..authors_copy in d:\authors.txt -Usa -P -c'-- Texcopy -I (in) using xp_cmdshellexec master..xp_cmdshell 'textcopy -I -Usa -P -Sjtk0 -Dpubs -Tpub_info_copy -Clogo -W"where pub_id = ''0736''" -FD:\MSSQL70\Install\algodata.gif'-- BULK INSERT using variable file name in a stored procedureCREATE PROC Sp_EnterTextFile @filename sysname as BEGIN SET nocount ON CREATE TABLE #tempf (line varchar(8000)) EXEC ('bulk INSERT #tempf FROM "' + @filename + '"') SELECT * FROM #tempf DROP TABLE #tempf END GO
These are several methods to insert binary files into an column defined with the IMAGE datatype. Note, there are people who would argue that you need only store the path to the file's location on the server disk drive, but I am not among those people who agree with this argument, primarly becasue when you insert the PDF files and use the free Adobe PDF IFilter, you can use the SQL Server 2000 Full-text Search (FTS) feature to search on the contents of the pdf file using CONTAINS and FREETEXT. You cannot use FTS, if you only store the path to the file on disk...
Thanks,JohnSQL Full Text Search Bloghttp://spaces.msn.com/members/jtkane/
Depending on your GUI (web app, VB app, etc) the methods will vary. For example, in an ASP web app you could use Persits ASPUpload (if available) or some other code that uses an <input type=file> control on your web form to upload the file. If using VB, you can use an ADO Stream object to read the contents of the file. Regardless of how you get the file contents, you can then write a stored procedure that basically just does an INSERT. Below is an example of how I am storing files (in tblFile) and the ADD stored procedure for that table.
tblFileFileID int (identity)FileNM varchar(255)SizeDM bigintContentTypeID varchar(100)UploadDT smalldatetimeContentBD imageExtensionCD varchar(3) (computed as right([FileNM],3))
CREATE PROCEDURE dbo.procFile_ADD @FileNM varchar(255), @SizeDM bigint, @ContentTypeID varchar(100), @ContentBD image = NULL, @FileID int OUTPUTASSET NOCOUNT ONBEGIN INSERT INTO tblFile (FileNM, SizeDM, ContentTypeID, UploadDT, ContentBD) VALUES (@FileNM, @SizeDM, @ContentTypeID, GETDATE(), @ContentBD) SET @FileID = @@IDENTITYEND
John and Milzs
Thank you very very much for helping me to solve the problem. I am very appreciate.
There are several reasons to store the file data in the database rather than just storing a link.
First and foremost is recoverability. What mechanisim ensures that your filesystem and the SQL database are in synch? I.e. your database is, in fact, an accurate record of the files that actually exist on your filesystem? There are several approaches to accomplishing this, but usually it comes down to periodic maintenance outages where the application is taken offline, and the filesystem and database are backed up in a "window" in which they are assumed to be in the same state. However, using that method eliminates your ability to recover to a point in time.
Another post pointed out that if you store the data in the database there exists the possibility of querying on the data which can be greatly facilitated by Full Text Indexing.
There may very well be other reasons that I can't think of right now, but that being said, storing links to a filesystem may be appropriate given the requirements of your system. You should carefilly consider your requirements and the options and understand the benefits/drawbacks of each before choosing one or the other.
I followed John Kane's suggestions and quickly got PDF files into a table and then also read back out and stored to a file. Following another reference from the SSCentral, I am now browsing for the file to upload. Do I have to retrieve the image string and save it with a .pdf extension and then open the temp file to display the pdf contents or is there a way to just open the reader and send it the data?
This site has saved me a lot of time, now to get off of it and get more work done. But... there's a trade-off!!