Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Insert the pdf file Expand / Collapse
Author
Message
Posted Wednesday, May 18, 2005 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 23, 2005 6:01 PM
Points: 3, 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...

 

 

Post #183482
Posted Wednesday, May 18, 2005 3:26 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, Visits: 174

Why do not use path instead of storing the whole file

 




Kindest Regards,

Amit Lohia
Post #183536
Posted Wednesday, May 18, 2005 3:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 23, 2005 6:01 PM
Points: 3, Visits: 1

Hi,

I am not expert.  Please explain more detail.

Post #183545
Posted Thursday, May 19, 2005 12:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 19, 2013 1:22 AM
Points: 118, 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
Post #183607
Posted Thursday, May 19, 2005 7:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 22, 2014 6:36 AM
Points: 3,756, Visits: 337

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




Post #183672
Posted Thursday, May 19, 2005 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 23, 2005 6:01 PM
Points: 3, Visits: 1

John and Milzs

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

 

Post #183717
Posted Thursday, May 19, 2005 9:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 21, 2014 12:49 PM
Points: 31, 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.
Post #183744
Posted Friday, May 20, 2005 1:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 16, 2008 8:43 AM
Points: 25, Visits: 10
search help for sp_textcopy , you can insert binaries into image fields, very easy.

Post #184268
Posted Friday, May 20, 2005 1:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:16 AM
Points: 1,035, Visits: 410

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



*****************/
Post #184272
Posted Wednesday, June 14, 2006 8:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 1, 2012 6:48 AM
Points: 13, 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!!

Post #287606
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse