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


How to fetch PDF files from a folder and saving it in the database


How to fetch PDF files from a folder and saving it in the database

Author
Message
ranamrana
ranamrana
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24356 Visits: 5314
You could use a SQL CLR routine for file access like: https://nclsqlclrfile.codeplex.com/

Please spell out the process in more detail.

CEWII
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5298 Visits: 875
See http://www.sommarskog.se/blobload.txt for a simple example.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63278 Visits: 13298
You could also use SSIS with the Import Column transformation. Very easy to set-up and no need of writing code :-D


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

MCSE Business Intelligence - Microsoft Data Platform MVP
cathyhill345
cathyhill345
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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.

EdVassie
EdVassie
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14214 Visits: 3901
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217423 Visits: 41994
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2453 Visits: 907
I'm with Jeff!

If you put everything in one corner, you know where everything is!
EdVassie
EdVassie
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14214 Visits: 3901
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5298 Visits: 875
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
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