# Miscellaneous > Database Programming >  Question about Blob data

## dkone

I am in the process of looking for a dev tool for SQL server DB front end.  It looks like ASPdb is a great choice for this project.  I have an eval. version running but can't seem to get the blob data to work correctly.  the DB holds files, specifically word *.doc and adobe *.pdf files.  I would like have the document launced from the browser window of ASPdb.  I have used this code in ASPdb "Attachment02,application/pdf,test,0,,".  Acrobat will start to launch from the browser, but I get an error message of "File does not begin with %PDF".  As far as the *.doc blob data, I have tried setting to application/doc (which I knew would not work).

I am rambling too long with my description, my real question is whether ASPdb can deal with outputting these blob data types properly or if it is something in SQL servers formating of the blob data that makes it imcompatible with launching as a mime type document?  Thanks

----------


## Frank

ASP-db will launch the image files and active doc files supported by the browser - gif, jpg, pdf etc... but besides the image files, it is not a good idea to launch the Active doc inside the browser unless your users are all using IE browsers. The size of the pdf especially will lock up the browsers many times because of it's size. Provide more info about your application and we might have an example for you.


Frank

----------


## dkone

This application will be used inhouse only, were I have control of the users workstation.  We only run IE 6+.  The purpose of the application is a quick front end search for our document storage database.  The attachements (blob data) is either PDF of DOC format.  Here is the code from the test query.  Like I said, when I click on the hyperlink of the ASPdb generated page for one of the attachements I get an Adobe error saying the file does not start with %PDF.  When I use my existing application that deals with this database (Access 2002) I am able to launch the applications.  The other thing that I am not clear on is what the correct syntax would be since the attachment could either be a PDF or a DOC.

<% Response.Buffer=True %>
<!-- #include Virtual=/ASPDB/ASPdb.Inc -->
<%
Set X= Server.CreateObject("ASP.DB")
X.dbUnit = "999"
X.dbMode = "Grid"
X.dbBLOB = "Attachment01,,test,0,25,25;Attachment02,applicati  on/pdf,test,0,,;Attachment03,application/pdf,test,0,,;Attachment04,,test,0,,;Attachment05,a  pplication/pdf,test,0,,;Attachment06,application/pdf,test,0,,"
X.dbGridDisplayFlds = "Attachment01, Attachment02, Attachment03, Attachment04, Attachment05, Attachment06, Attachment07Name, Attachment07, Attachment08Name, Attachment08, Attachment09Name, Attachment09"
X.dbColor = "#ffffff,#0000cc,#99CCFF,#0000cc,LightGrey,LightGr  ey"
X.dbGridInc = -1
X.dbGridIncMax = 99999
X.dbGridColSort = False
X.dbMemoTextSize = -1
X.dbStartup = "Filter"
X.dbDBType = "SQL"
X.dbDSN = "Driver={SQL Server}; UID=xxxx; PWD=xxxx; Server=xxxx; Database=xxxx"
X.dbNavigationItem = "Top,Prev,Next,Bottom,GridRow,Reload,Filter,ResetF  ilter"
X.dbSQL = "SELECT EventKey,Attachment01,Attachment02,Attachment03,At  tachment04,Attachment05,Attachment06,Attachment07N  ame,Attachment07,Attachment08Name,Attachment08,Att  achment09Name,Attachment09,Attachment10Name,Attach  ment10,Attachment11Name,Attachment11,Attachment12N  ame,Attachment12,Attachment13,Attachment14 FROM Testing_FieldWorksheets"
X.ASPdb

%>

----------


## John

Couple of thoughts here...

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.

2) I've setup document storage databases before and managed them with a SQL front end.  After a lot of meetings and discussions we decided that the best way to handle it by far was to store the files as stand-alone files and not embedded blobs.  The main reasons I can remember were:

a) Huge increase in db size.  This can mean much higher server requirements, slower performance, etc.
b) Greater potential for loss of data / corruption.  We didn't like the idea of having our files all embedded in blobs and only retrievable with a fully working system.  If anything got messed up they'd be "locked in there" unlike a system where they're stand-alone files.
c) Overhead of putting them into and retrieving them from the db and the ease of doing so.
d) Inability to handle "future formats".  A linked standalone file can be any format from ZIP to PDF to BZ2 and we don't have to worry about it.
e) Lots of other reasons I don't remember anymore!

Bottom line was we designed a document tracking system that kept all the document Titles, Keywords, Descriptions, Authors, Permissions, Updates, CheckIn/Out flags, etc. in the DB and simply had a path_filename field that got to the file.  The system was fast and worked great.  It enabled us to prototype in Access and up-size to SQL Server when working.  They may have even migrated to Oracle by now.  If it were all embedded blobs, that path would have much more more difficult.

Food for thought anyway...

John

----------


## Frank

The only time I come acroos in justifying the storage of BLOBs inside a field is a high security application. Other that that I second the suggestion that it is a bad idea to do that.

FK

----------


## dkone

John, thanks for the input, it is something to think about.  Although I think we will be leaning towards embedding the data, I will limit the attachment types to DOC only.  Frank, even though you recommend against the storing of blob data doesn't answer my question of why it is not being output properly.  just as an experiment I used this code:

X.dbBLOB = "Doc01,image/jpg,,0,,;Doc02,application/pdf,,0,,"

I then stored a jpeg in the first field and a pdf in the second field.  Bottom line is that it did not output the mime type properly.  Actually it did not output them at all.  Although I love the speed and ease of ASPdb, I don't think I will be able to use it for this project due to these limitations.  Thanks for the input.

----------


## Frank

it should work if your specified mime type is correct.

FK

----------


## dkone

Since this original thread, I have purchased ASPDB pro and have used it in  several projects for our intranet and internet.  I still think it is a fantastic product.  I still need help with getting PDF files to display.  The are currently saved as blob data in a SQL2000 database.    Here is the code I am using:

X.dbBLOB = "Attachment01,application/pdf,Field Worksheet,1,,"

The message I get back from the browser is:

File does not begin with '%PDF-'

If I right click on the linked blob, I can save the file to my local hard drive.  I can not open with acrobat, but when I open the file with word pad, I see a bunch of junk characters before %PDF-.  I am assuming that this is the offset.  Is that correct?  and if so, how do I calculate the offset value?  do I just count all the characters and spaces?  Or is this info that you would have?  Any help would be appreciated.  I can't seem to copy and paste the header/offset data into this message, but would email it to you on request.  Thanks in advance.

----------


## 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.  the file the 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.  ??? any comments.  I will do a test by saving a JPG file and will post comments shortly.

Doug

----------


## Frank

I just finished working on a file using a BLOB in MySQL (identical to SQL) and the code is as below. As for BLOB offset. Unless it is documented, you have to identify the it sometmes by going in and look for the 'headers'. Also, you are using Access to store them (I have not tried Access) and Access migh put a few bytes there. I think I'll try give it a try and see whetehr that is the case. SQL is pretty clean.

Frank

 Dim BGif As New tornado.z
        With BGif
            .dbUnit = "92"
            .dbSkin = 14
            .dbSQL = "SELECT * From Main"
            .dbDSN = "localhost;passport;root;sa"
            .dbMode = "Grid"
            .dbDBType = "MySQL"
            .dbProvider = "MySQL"
            .dbBookMark = "Main;0"
            .dbBLOB = "field=Photo|context=image/jpg|label=browser|offset=0"
            .dbTextHolder = "Title=Tornado Demo - Inline Gif"
            .ASPdbNET()
        End With

----------


## dkone

Well, I created a new test table and stored BMP's as my blob type data.  Although I could not get the image to appear inline, ASPDB would open the image in the browser using an offset of 78.  I don't even know where to go to find the answer to this problem.  SQL will send the PDF data correctly to Adobe when the blob is opened in an Access front end, but not when using ASPDB as the delivery vehicle.  If I ask this question in a SQL forumn, then I will get the response to check with ASPDB support, and I fear that is the same type of answer you will give me.  Do you have any customers retrieving PDF blob data from MSSQL?  can you run a test and see if it is just me or if it truly doesn't work?  Thanks for any help.

Doug

----------


## Frank

ASP-db is a Web tool, so BMP only shows in IE browser and not a good idea. Jpg/Gif no problem.

What exactly are you trying to store/retrieve? Retrieve to WHERE? Inline display (not PDF!!), HyperLink?

Clarify that.

Send me a cutdown version of your SQL file with the blob (2 records - using backup).


Frank

----------


## dkone

I was just using BMP as an alternate type of blob data to store and it just so happend I had one on my desktop.  What I am attempting is this:

Our company performs testing at gas stations.  I scan the technicians field worksheets and save them in my test results database as PDF files.  This portion is done via a MS Access front end.  These events are cyclical and the techs need to refer back to the notes from the last time they were at the site.  the notes are on the scanned field worksheets.  I want to be able to have the techs use our intranet site to retrieve the PDF files.  I want the PDF files to be links in an ASPDB generated table.

I do not know a quick way to send you a striped down version of the DB.  The exact structure of the table with the blob is as follows:

EventKey|int|4|allow nulls
Attachment01|image|16|allow nulls

As you will see in the code there are other fields that are being referenced in the view, but they are all text fields from other tables that do display properly.  We have been using this particular lookup for several months now and it has worked great.  Even with the inclusion of the blob field the table is still generated correctly, including the link to the PDF file.  http://www.tankstatus.com/forfrank.asp
this is a link to a live version of the code.  if you right click on the attachment, it can be saved locally, but even then it will not open in acrobat.  Somehow the file is being modified as it is coming out of SQL.

Here is the code I am using to retrieve the data.

<% Response.Buffer=True %>
<!-- #include Virtual=/ASPDB/ASPdb.Inc -->
<%
Set X= Server.CreateObject("ASP.DB")
X.dbUnit = "999"
X.dbMode = "Grid"
X.dbBLOB = "Attachment01,application/pdf,Field Worksheet,1,,"
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

%>

----------


## dkone

the offset in my code does not matter, if it is too low, I get the file does not begin with '%PDF-' error. when the offset is to high, i get an error message of 'the file is damaged and cannot be repaired.

----------


## Frank

Try this...

save the pdf file from the link and compare  with the original pdf and see the difference (header).

use a normal link with the pdf outside the DB BLOB field and see whether the pdf display correctly.


Frank

----------


## 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

----------


## Frank

Doug,

Looks like problem might be the way you write the BLOB to the DB. Are you using a local Winform program to do the writing? There should be no offset the file should starts with %PDF. If the BLOB is not recognized then it'll default to html. Did not get your PDF file, please try againa and send to frank@aspdb.com.


Frank

----------

