SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert the pdf file


Insert the pdf file

Author
Message
Tuongvi Ly
Tuongvi Ly
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 1

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...


Amit Lohia
Amit Lohia
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1876 Visits: 174

Why do not use path instead of storing the whole file




Kindest Regards,

Amit Lohia
Tuongvi Ly
Tuongvi Ly
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 1

Hi,

I am not expert. Please explain more detail.


John Kane
John Kane
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 121

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
lionfan91
lionfan91
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5352 Visits: 431

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





Tuongvi Ly
Tuongvi Ly
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 1

John and Milzs

Thank you very very much for helping me to solve the problem. I am very appreciate.


Roy Ashbrook
Roy Ashbrook
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 17
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.
Sergio Campos
Sergio Campos
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 10
search help for sp_textcopy , you can insert binaries into image fields, very easy.
DCPeterson
DCPeterson
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3521 Visits: 432

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



*****************/
hmdev
hmdev
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 8

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!!


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search