How do I save and retrieve a pdf in VB6 using SQL 2005 eXPRESS

bob_mechler
bob_mechler used Ask the Experts™
on
I need a sample in VB6 or higher that will save an account number and a pdf in a blob (SQL_LONGVARBINARY) field  or whatever field is best and retrieve it, save it as a file so that it can be used as a pdf by calling it from within the program.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2014

Commented:
Per the link below: The SQL engine supports 1 CPU, 1 GB RAM, and a 4 GB database size.

Note the limitation to 4 GB.

In addition, even though you can save binary(s) data (pdf, doc, xls, bmp, jpg,...) into the SQL database, most DBA's recommend against it. It can lead to bloat, corruption and other errors. Most recommend that you store the binary files to disk and create a pointer to the original file.

------------------------------------------------------------
SQL Server 2005 Express Edition Overview: Engine Specifications
http://msdn.microsoft.com/en-us/library/ms345154%28SQL.90%29.aspx#sseover_topic4
bob_mechlerProgrammer

Author

Commented:
The PDF's need to be accessible to a web site in a DMZ zone that already can connect to the SQL Server in the Domain. The pdf's were on the computer in the DMZ in a folder and that was thought to be a security problem, which of course it is. My thought was to store the pdf's 32 k to 96 k per pdf in the same database we now populate the website with. No fuss, no muss.

If there is a less bloated way to save the pdf statements in the sql server (such as a different format), I'm all ears, but it needs to reside in the domain and only be accessible by a properly logged in user of the web site. Thanks
Bob
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2014

Commented:
I know there is a way to do this securely, but web dev is not my forte'. I've always had to address from the "local" applications that have been developed in our company.

I've asked to have the web dev/ ASP zones added to the Q.
bob_mechlerProgrammer

Author

Commented:
There are two parts to the issue. One part is as simple a program as possible to get the pdf's generated in the domain by our accounting programs into the SQL Server in the Domain. The part about getting them from that server and making a link available on the web site page can be handled by our web programmer. The Web programmer just knows about the SQL Server connection. My responsibility is for the accounting programs that use Btrieve API and PDFCreator.

Bob
Most Valuable Expert 2014

Commented:
Why do I have a feeling a kludge solution is in the making. ;-)

I spotted this link on the MSDN just below the info about SQL Express. I didn't read it in detail, but the difference between an mp3 and another binary is small, relatively. Maybe you could use that as the base code sample. And it has the advantage of being .Net ready (I hope ;-).

Managing a Music Collection Using Visual Basic Express and SQL Server Express
http://msdn.microsoft.com/en-us/library/ms345151%28SQL.90%29.aspx
Top Expert 2012
Commented:
>>No fuss, no muss. <<
Except for one pesky little detail.  Maintenance.  Specifically backups.  The more binary data you add the more you will have to back up.  Over and over again.  That is the time as well as tape/disk taken to backup will get longer and longer. Why is that different to the file system, you ask?  On the file system you can use any number of file backup systems AND you only have to backup once.

But we have all done this once and learned from our mistakes and trust me it is the best way to learn.  In fact if you look on this site you will find code that I have shared on several occasions using ADO Stream object.  it is quite simple and requires less then half a dozen lines of VB6 code.
bob_mechlerProgrammer

Author

Commented:
I'm currently testing some of the suggestions. A colleague tells me my code (not posted) retrieves a PDF from a LONGVARBINARY but only if it was placed there by another program. I'm assuming he used the ADO Stream object as acperkins suggested.

Bob
bob_mechlerProgrammer

Author

Commented:
Reviewing the code samples of acperkins I was able to use ADO in my environment to save and retrieve PDF'S.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial