Insert the pdf file
-
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
Kindest Regards,Amit Lohia
-
Hi,
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#
http://support.microsoft.com/default.aspx?scid=kb;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with VB.NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through ASP.NET
http://support.microsoft.com/?id=326502
-- BCP IN file using xp_cmdshell
exec master..xp_cmdshell 'bcp pubs..authors_copy in d:\authors.txt -Usa -P -c'
-- Texcopy -I (in) using xp_cmdshell
exec 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 procedure
CREATE 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,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John T. Kane -
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.
tblFile
FileID int (identity)
FileNM varchar(255)
SizeDM bigint
ContentTypeID varchar(100)
UploadDT smalldatetime
ContentBD image
ExtensionCD 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 OUTPUT
AS
SET NOCOUNT ON
BEGIN
INSERT INTO tblFile (FileNM, SizeDM, ContentTypeID, UploadDT, ContentBD)
VALUES (@FileNM, @SizeDM, @ContentTypeID, GETDATE(), @ContentBD)
SET @FileID = @@IDENTITY
END
-
John and Milzs
Thank you very very much for helping me to solve the problem. I am very appreciate.
-
I would echo this question. Is there a reason you must store the file in sql as opposed to just storing the files in a specific location and storing path locations? If not, I would go that route.
-
search help for sp_textcopy , you can insert binaries into image fields, very easy.
-
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.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
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!!
-
What dev environment/user interface are you using? For example, if you are using classic ASP, you can just do something like this:
rs.Open... ' get a recordset with the data
With Response
.ContentType = rs("ContentTypeID")
.AddHeader "content-Disposition", "attachment;filename=" & rs("FileNM")
.BinaryWrite rs("ContentBD")
End With
rs.Close
with ASP.NET, something like:
With cmd
.CommandText = "procFile_GET"
.Parameters.Add("@FileID", SqlDbType.UniqueIdentifier).Value = fileID
dr = .ExecuteReader
If dr.Read Then
outData = dr("ContentBD")
With Response
.Buffer = True
.ContentType = dr("ContentTypeID")
.AddHeader("content-Disposition", "attachment;filename=" & dr("FileNM"))
.BinaryWrite(outData)
End With
End If
dr.Close()
End With
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply