Question about Blob data

[dkone]

I played around with the offset value and the problem seems to be with the data being output from SQL. at an offset of 1640 I get the File does not begin with '%PDF-' error. when the offset is increased to 1641, i get an error message of 'the file is damaged and cannot be repaired. both messages are coming from Acrobat. Why does MS have to make everything so damn difficult. I right click and save from the code generated by ASPDB is only 22K, but when I use Access to retrieve and save the same blod field, the size is 379K. So it is like the retrieval process is 'wrong' using ASPDB pro.

Have you been able to successfully retrieve PDF data from a blob field?


Doug

[Frank]

No, I have not tried PDF files. If the streaming has problem and losing a few bytes, the gif and jpg will not work either. I'll take a look at it. The BLOB embedding is not good unless high security is a requirement. In that case you better store the BLOB as ->

Key, BLOB in a table

and the rest of the DB as

Key, Field1, Field2 ..... in another table

If you carry the BLOB with the records, it'll kill your performnance in no time.


Frank

[John]

Why not just store the PDFs on the server and HREF to them in your database?

We've done a zillion apps like this and they work great. Advantages:

1) You don't have to deal with any corruption issues where all your binary files are "buried" inside your db and can get trashed

2) You save the CPU (and time) "converting" into the DB and "converting" them back out

3) You can store the files on one server / drive and the DB on another

4) You can change your DB or DB front end at any time and your files remain independent and accessible

5) If you ever wish to update the format of the files (newer PDFs have indexing and more searchability features) you can batch process them all in your directory easily w/o involving the DB

6) You can "index" the PDF files to make them searchable - nearly impossible if embedded in a database

I could go on, but you get the idea. Better have one HECK of a good reason to embed!

My 2 cents worth anyways...

Take care,
John

[Frank]

Yes, stroing BLOB does have a big penalty. But on the technical side, this is what I come up with ->

* Use image datatype to store pdf in SQL. Use binary does not work?
* Binarywrite to browser -> File does not begin with %PDF error.
* Byte array does start with %PDF (debug in code) before binarywrite to browser.
* Save buf byte array to file before binary write to browser. File WORKS !!!!
* So the error comes in between application/pdf and the binarywrite(byte [] buf) and not from SQL.

I'll find out more about this.


Frank

[dkone]

Frank,

Thanks for looking at this. I don't fully understand some of what you said, but it is consistent with the behavior I have seen.


John,

Your points are all valid. It all comes down to workflow and ease of use for the primary user of the DB, me. I create the PDF's from our Ricoh copier which scans to PDF and emails them to me. All file names are automatically created and are not descriptive as to the content of the document. From there I just need to drag and drop onto the front end Access application. If there was fast and easy way to create the href in the DB, I would consider changing my approach. The other part of the equation is the output of documents. A full record will consist of a test event pass/fail overview page and the work tickets (PDF files). My intent is to use crystal reports runtime to compile the report and output to a master PDF file. I am not sure how well it will work if all information is not coming from the same source.

[Frank]

This %PDF ... error message is all over the place when you search for that in google. Now, I think I got the result after testing on W2003, XP-Pro and W2000 servers.

* O/S has no effect
* ACROBAT 5.0 works !!!!
* ACROBAT 6.0 FAILS
* VS IDE mode fails (I know about thisone but keep forgetting)

The ACROBAT 6.0 fails message is ->

Could not open 'paxxxx.fdf' ....

Looks like 6.0 thinks the xxxx.pdf file is xxxxx.fdf after the binarywrite.

So, just based on this problem, storing pdf blob in DB is not a good idea at all unless you have control what version of Acrobat user is using.


Frank

[Frank]

Earlier Comment ->

1) When ASP-db creates the streaming mime output to the browser to display the image/document, it needs to know the type ahead of time. Otherwise, it can't output the correct header. The fact that you're mixing different types and don't know at runtime could be a problem.

True indeed.... I havwe added auto detect in the next release to ASP-db.Net (Tornado) and I'll go back to add that to Classic later on. The syntax is context=auto. It'll detect gif,jpg and pdf.


Frank

[Frank]

I modified both ASP-db and ASP-db.Net to support JPG, GIF and PDF.. it is really cool !! I stored different BLOBs in the SQL Image field and the Link (Inline is not affected by the contexttye) option will retrieve different types of BLOBs upon the click.

I'll get the PNG also and see whether there are any other types to include in the support (.doc, .xls?)

Watch for this in the the next release....



Frank

[dkone]

Thanks Frank. It is nice when you find out that your not going crazy and/or doing something incorrectly. Can't wait to get my hands on the new version. I am new to having a registered version. (used the free version for 3 years before I talked the boss into pro). If I recall we get software updates included in the maintenence fee. What/when/how do I get the latest build?

Doug

[Frank]

Please contact suppor@aspdb.com... they'll take care of you. Consider using the ASP-db.Net version. It is more powerful.

Good news... classic asp-db and asp-db.net all works now with Acrobat 5 and 6, gif, jpg, png and pdf - Auto Detect!!!!


Frank

[Frank]

How to get the latest build?

Sales will setup your name, email address, product and version in the DB (csv) and in the site's front page (getupdate box). All you need is to enter your name or email and id (.id for classic) and a link will be sent to your email get the latest. Check with sales to make sure the setup. Of course, the entier system is setup with ASP-db less than 50 lines of code !!!


I just put the blob stuff in the Apr 2005 release. Try that and let me know. Manual also updated.

Frank

[dkone]

Frank,

The good news is that this thread alone will probably move me out of 'junior member' status. the bad news is that although it does work differently now, it still does not work like I expect it to.

www.tankstatus.com/forfrank.asp

Here is the new code:

<% Response.Buffer=True %>
<!-- #include Virtual=/ASPDB/ASPdb.Inc -->
<%
Set X= Server.CreateObject("ASP.DB")
X.dbUnit = "999"
X.dbMode = "Grid"
X.dbBLOB = "Attachment01,auto,Field Worksheet,0"
X.dbGridDisplayFlds = "EventKey, ARCustomerID, CustomerName, CustomerStationID, TestDate, Attachment01"
X.dbColor = "#ffffff,#0000cc,#99CCFF,#0000cc,LightGrey,LightGr ey"
X.dbGridInc = -1
X.dbGridIncMax = 20
X.dbGridColSort = False
X.dbMemoTextSize = -1
X.dbStartup = "Filter"
X.dbDBType = "SQL"
X.dbDSN = "Driver={SQL Server}; UID=; PWD=; Server=kpe-ssa; Database=compliance"
X.dbSQL = "SELECT EventKey,ARCustomerID,CustomerName,CustomerStation ID,TestDate,Attachment01,STIP3Test FROM view_testing_blob"
X.dbUserLocalText = zHead & ",<Center><h3>STIP3 Test Events</h3></Center>;"
X.ASPdb

%>

I played around with the offset value, and even when I had the text that display starting with %pdf, it still would not launch the acrobat appliation. one comment, most of the pdfs that are in the database were created in version 5.0 of acrobat. Not sure if that would make a difference. Also noteworthy is that the behaviour of the pdf data stream changes dramatically depending on your browser type, when playing with the offset value. for instance in firefox, I set the offset to 2644 and had the data start eith %pdf, but when I used the same value in IE, I got a data set that might have been 500 bytes.

Doug

[Frank]

1. why are we talking about Offset values ? Must be the way you store the BLOBs.

2. Send me one or two of your pdf files and I'll put it in my SQL db and see what happens.


Frank

[Frank]

wait a second... you have not got the latest DLL yet and you are using the new 'auto' context ???

1. Get the latest DLL from support.
2. Sen me one of your PDF file and I'll make sure it works.

Also, how do you input the blobs into the DB?


Frank

[dkone]

Frank,

I got the April 2005 version of the DLL from Mark last Friday. Like I said, it did change the output of the PDF. With the old dll version I would get an error message, now with the new version of the dll file, the PDF file is output as text to the browser. The reason I am playing with the offset is that if you look at the pdf file as text in the browser (see link from previous message), you can see the %pdf in the first 1000 bytes or so. I am assuming that the offset strips away x bytes. this seems to be confirmed as I observe the changes in the pdf txt output while playing with the offset value. The blobs are stored using a MS Access front end linked to the SQL database tables, and a dragged and dropped into a bound object frame. I sent 2 of the PDF's to frank@aspdb.com if that address is wrong, let me know and I will resend to the correct address. Thanks.

Doug