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

How to fetch PDF files from a folder and saving it in the database Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 1:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 1:29 PM
Points: 1, Visits: 14
I have a small project to be done in which I need to fetch the pdf file from a my system and save it in database and also fetch the name of it and save it in the database.
Post #1476765
Posted Tuesday, July 23, 2013 1:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
You could use a SQL CLR routine for file access like: https://nclsqlclrfile.codeplex.com/

Please spell out the process in more detail.

CEWII
Post #1476768
Posted Wednesday, July 24, 2013 3:51 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:57 AM
Points: 802, Visits: 717
See http://www.sommarskog.se/blobload.txt for a simple example.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1477305
Posted Thursday, July 25, 2013 1:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 13,724, Visits: 10,679
You could also use SSIS with the Import Column transformation. Very easy to set-up and no need of writing code



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1477368
Posted Thursday, January 2, 2014 12:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 2:18 AM
Points: 6, Visits: 11
I want to try your method to sort large number of PDF files in the E-library. So how are things going in your project? By the way, I know how to get PDF from my system to database but I do not know how to get the name of those PDF files.

ranamrana (7/23/2013)
I have a small project to be done in which I need to fetch the pdf file from a my system and save file in database and also fetch the name of it and save it in the database.
Post #1526989
Posted Friday, January 3, 2014 7:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,879, Visits: 3,226
There are advantages and disadvantages to storing documents actually within the database.

If you look just at performance, storing files under 1MB in size generally gives faster access to the file than storing it out of the database. Above 1MB in size then storing it outside is generally faster, due to the different ways in which SQL Serve and raw NTFS deal with data management.

If you look at data integrity, you generally have far better control when the data is stored inside SQL Server, unless you can use SQL 2012 or above and File Table storage.

File Table can give you the best of all worlds for associating documents with traditional database data. The SQL Server database engine knows about the documents that are stored in the file table repository, and the files are also accessible to non-SQL Server applications.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1527526
Posted Friday, January 3, 2014 11:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 37,065, Visits: 31,628
To add to what Ed posted...

I went through this about a year ago with telephone call recordings. I, too, was well aware of storing larger files on disk instead of the database. To wit, most of the calls were geater than 1MB and I was determined to get them out of my database.

I'm not sure why they did it but they stored the file in both places and also stored the file path in the database. Being the diligent pendant that I am, I wrote some code to verify that all of the files existed and {drum role please} they did NOT all exist. That led me to the following conclusion.

No one protects data better than a DBA and his/her backups. Files can be touched by too many people and the DBA has no control over when or even if those files get backed up. Despite the performance hit and the need to do partitioning to accomodate the growing backup requirements, I'll take the performance hit and all of the work to keep those call recordings in the database because it would appear (another drum roll, please} that's the only place where I can guarantee that they won't disappear or get lost.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1527786
Posted Monday, January 6, 2014 3:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
I'm with Jeff!

If you put everything in one corner, you know where everything is!

Post #1528021
Posted Monday, January 6, 2014 5:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,879, Visits: 3,226
As Jeff and aaron have said, data integrity can be the main issue with document storage, regardless of what people may say about performance.

With Filetable storage, I think you can prevent anything other than SQL Server from deleting or adding documents. Filetable objects are also included in SQL backup and restore, which means data integrity is maintained through these operations. This effectively unifies the management of the documents and meta data.

If you design a system with the documents and meta data being stored in separately managed containers, you should not expect the two containers to remain consistent.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1528055
Posted Monday, January 6, 2014 5:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:57 AM
Points: 802, Visits: 717
Nah, the point with FileTable is exactly that it makes it possible to update the database from Explorer and similar. The difference between storing just a file path in the database, is that if you delete the file from Explorer, this is also reflected in the database, so get the consistency. And the files are included in the database backup.

Now, users needs to have access to the share where the files are located, and if they don't have access the files cannot be manipulated. But if they do not, there is not much point in using FileTable at all, but you can use regular FILESTREAM instead.

For FILESTREAM to be meaningful, you should use the Win32API to write/read files. If you read them with SQL statements, you should get the same performance as regular blobs.



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1528059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse