Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Question Unanswered: Help! store document in SQL server

    I am using Access2002 and SQL server 2k. I need to store some documents (PDF) in the database as embedded object that can be activated and edited by double-click. How can I implement this in VBA/T-SQL in the same way that "insert embedded object" does?

    UPDATETEXT or WRITETEXT will just store the document as a "long binary data". I've heard it's better only store the path in database but I need to open the database for the remote access via Internet. Can I achieve this by just storing the file path?

    Any help appreciated!

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    You heard correctly about using the file system to store the documents, and only storing the path in the database. The file system will give you much better performance.

    You will want to store the documents in a folder on your web-accessible server (web server?), in a folder with Read security permissions granted for your users (either end-user account for secure server, or web server account for public server). In the DB, the path should point to the URL of the file, such as "http://myserver.mydomain.com/files/myfile.pdf" and not the physical location ("c:/files/myfile.pdf").
    -bpd

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Or you could store the physical path to the PDF in the server, then when you need to send the file to them, you stream the data to them (which is different then pointing them to a URL).

  4. #4
    Join Date
    Aug 2003
    Posts
    6
    Thanks bpdWork and Seppuku! I now have a clue how to do it.

    Seppuku, do you mean use somthing like ADO stream to send files and then SaveToFile at remote computers?

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Originally posted by whfrank
    Thanks bpdWork and Seppuku! I now have a clue how to do it.

    Seppuku, do you mean use somthing like ADO stream to send files and then SaveToFile at remote computers?
    Exactly... If you stream the file as an attachment it will force the user to download, otherwise some browsers will attempt to associate it with an application (such as Word, Excel, PDF, etc) and open the document in the browser. You can do it either way. I've done both.

  6. #6
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Exactly. If the user opening the document in the browser is acceptable, then a file reference will be a lot less work than the streaming. If not, then streaming os the way to go.
    -bpd

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Originally posted by bpdWork
    Exactly. If the user opening the document in the browser is acceptable, then a file reference will be a lot less work than the streaming. If not, then streaming os the way to go.
    Unless you want to secure the location of the document. There may be a time when you want to download a doc into the browser window, but it's location is secure (due to username/password or some other criteria). To secure it, you may still want to stream the document to the browser so you can validate the user against any security requirements you may have. When streaming a file, you can force the download by setting the Content Disposition header as an attachement. If you ignore that, it will download into the browser (assuming there's an association), but still gives you the ability to validate the user.

    An example would be a document management system. If you just provide URLs to the documents, anyone can get them without authenticating. If you stream the document to them, you can ensure they are logged in and have the proper rights to the document, and then can send that document to them to load in their browser (or download, by forcing the Content Disposition to attachment).

  8. #8
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Actually, you can secure the documents in the file system using NTsecurity as well. Unauthorized uses will not be able to download documents they do not have access to.
    -bpd

  9. #9
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Yes but that requires you to have administrative rights on the server to setup and manage. Besides, the users you want to access the files don't always have accounts on your NT domain, requiring you to set up an account for everyone. If this is on an Intranet, NT auth may work fine, but on the Internet, chances are they won't be on your domain.

    If you really want to go that route, a LDAP solution works well. I'm doing that right now on a site using Siteminder and Secureway. It prompts for user access similar to NT auth, but can be updated through a web interface.

  10. #10
    Join Date
    Aug 2003
    Posts
    6
    Originally posted by Seppuku
    Exactly... If you stream the file as an attachment it will force the user to download, otherwise some browsers will attempt to associate it with an application (such as Word, Excel, PDF, etc) and open the document in the browser. You can do it either way. I've done both.
    If I use Access to develop the front-end application, the physical path stored in SQL server is like "C:/myfile.pdf", but this is the location on the server. How to write a procedure in Access on remote computers stream it and send it? I'm a bit confused, please help me out. Thanks!

  11. #11
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Originally posted by whfrank
    If I use Access to develop the front-end application, the physical path stored in SQL server is like "C:/myfile.pdf", but this is the location on the server. How to write a procedure in Access on remote computers stream it and send it? I'm a bit confused, please help me out. Thanks!
    Are you using Access as your database on the Internet? Or just to test with? Access should never be used as an Internet database backend.

    As for how to stream it, what server-side scripting language are you using on your website?

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    you guys are all saying that it's slow... Actually, unless you use "text in row" option on the table, - only pointers to text/image pages are stored in the table. Also, if you implement methodology that requires to store the actual documents in the file system, you just expanded your responsibility from being a dba to also being in charge of the directory structure, os access right, etc., etc... And that's not to mention disaster recovery and business continuity... Just a thought

  13. #13
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Actually, even without text-in-row turned on, the B-Tree structure used by SQL is much slower than the file system. SQL stores a pointer to the B-tree structure in the table. Depending on the file size, the b-tree breaks the file up into little pieces, with a string of pointers connecting them. Upon retrieval, the pieces are reassembled into a complete file. MS actually recommends using the file system for this type of storage if it is an option.

    I belive from his last post the whfrank is using Access as the front end, and connecting to a SQL backend over the internet. I haven't played with Access front-ends in a while (thank god!!), so I have no idea how it would deal with BLOBs, or streaming for that matter, but I am sure by now they have been built in. Maybe MS has something about how to use BLOBs through Access, which I am sure will be the limiting factor.
    -bpd

  14. #14
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    From past experience, the best performance I've seen from storing BLOBs in the DB is when they are small (such as images).

    I agree that you start getting into a gray area when you start storing on the disk w/ pointers in the DB, but that's why you have system admins and security for in the first place. If you're doing this right, then you have disaster recovery on the disks and in SQL Server. You should have a team of system admins and security personnel. All of that is their responsibility to maintain.

    Just as bpd said, everything has to be pulled from the disk whether it's stored in the DB (which is just a pointer to an area on the disk), or a drive path.

    With the BLOB stored in the DB, you have to make a request for it, the DB executes the request, hits the disk for the files containing the data, then send all the data (through the DB) to the requesting app to be streamed to the user. This takes up resources that the DB wasn't meant to do (file transfer).

    When you store the file path in the DB, you make a request for the for it, the DB executes the request and returns a string, then the file system can be directly accessed for the contents of the file and streamed to the user. Less IO, more efficient, and you remove the DB from the mix, leaving it to handle other requests quickly.

    There may be times though when this is not functionally possible. An example would be if your SQL Servers were separate from your Web Servers. In this case, all of your web servers might not have all of the documents locally (unless you're using a content manager). You may need to send the document from the SQL server to the web server in that case. But that's even more time consuming because it involves the network in a DMZ.

    What just came to mind though is that if you can pipe the documents from another server, you're set. I've never tried opening a file through named pipes in ASP though. Sounds like I have a project

    I could be wrong in all that.. but it just seems logical that you separate what the DB is good at doing from what the FS is good at doing..

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, that's what this forum is for, - to share, and disagree at times (which is what I am doing now )

    I have an app that has a custom report writer built into it (it's not Crystal, nowhere near, but better ). Certain users are allowed to create new reports and modify existing ones. Others are allowed to only execute existing reports. The app has its own security that is based on database roles. The fact that I am storing report definitions into text fields allows me not to worry about security. I know-I know, yoall gonna tell me that even if I store definitions into FS the security policy can be made to support the app security. And I would agree with you, maybe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •