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