Right, I think this part is the problem: 4. Insert the file into a media table (file name, mime type id, file data, file size)
Evidently MySQL handles binary better than MS. MSSQL fails to insert the full set of file data - it truncates on a particular character (00 hex) if you insert from Witango. I've tried with several different files; the truncation occurs at different positions (i.e., after 47 bytes, after 158 bytes, etc.), but always on the 00 character. It's something in the web/db interface - I can read the file directly into & out of MSSQL with a stored procedure. It's fine in the filesystem, but IIRC still truncates when read out of the db with Witango (I could be wrong on that point - I'd have to go look through my notes). Ben tried it using serverside javascript encoding - it stores fine, but then dies on the unencode in Witango. Since the same process seems to work fine with other databases, I'm guessing it's something in M$ and/or the MS/Witango combination. I've talked to some MS guys here with no luck - nobody's storing files in the db & everyone's heard of problems with it. I haven't tried hitting UseNet; I ran out of time. There's an ASP upload setup in Ben's Goodies List that should upload to the db with some work, but again, I ran out of time before I got it sorted out. I also didn't ever try it with other filetypes (e.g., images). I'm hoping to be able to get back to this issue later and sort something out, as we've wanted to do this for several different projects (although this is the first I've done from the db/web side). If anyone was willing to post a fix that works with Witango and MSSQL, you'd be very popular. ;) > -----Original Message----- > From: Bill Conlon [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 17, 2003 3:17 PM > To: [EMAIL PROTECTED] > Subject: RE: Witango-Talk: PDF stored in db? > > > mysql 3.23.56 > > 1. Here's the form generation script: > > <form method="post" action="@@request$appfile?_module=<@ARG > _module>&_function=detail&_function1=updateresourcefile" > enctype="multipart/form-data"> > <table> > <tr> > <th>Resource File</th> > <td><input type=FILE SIZE=25 NAME="File"></td> > <td>This is the file that you make available to others > as a resource. > Select the file from your local or network drive. This file will be > uploaded to the database.<br /><br /><strong>Note:</strong> > The maximum > file size is 8 MB. If your connection times out when uploading large > files (greater than 1 MB), contact the <a > href="mailto:[EMAIL PROTECTED]">system > administrator</a>.</td> </tr> </table> <input type="hidden" > Name="media_id" value=<@COLUMN > "media_description.media_id">> > <input type=hidden name=path value=""> > <input type=SUBMIT NAME="Submit" VALUE="Upload File" > onclick="this.form.path.value=this.form.File.value;"> > <input type=RESET VALUE="Reset Values"> > </form> > > > 2. First step of form processing (get the media data and meta data), > lifted from a post on this list without attribution --- sorry. > > <@ASSIGN NAME="size" SCOPE="request" VALUE=<@LENGTH STR=<@ARG > File>>> <@IFEMPTY NAME=<@ARG filename>><@assign request$path > value="<@arg path>"> <@assign request$path value='<@TOKENIZE > VALUE="@@request$path" > CHARS="\:/">'> > <@assign request$path value='@@request$path[1,<@numcols > array="@@request$path">]'> > <@assign request$path value='<@TOKENIZE VALUE="@@request$path" > CHARS=".">'> > <@assign request$filename value='@@request$path[1,1]'> > <@assign request$fileextn value='.@@request$path[1,2]'> > <@ASSIGN request$filename > value='@@request$path[1,1].@@request$path[1,2]'> > <@ELSE><@assign request$path value="<@arg filename>"> > <@assign request$path value='<@TOKENIZE VALUE="@@request$path" > CHARS=".">'> > <@assign request$filename value='@@request$path[1,1]'> > <@assign request$fileextn value='.@@request$path[1,2]'> > <@ASSIGN request$filename > value='@@request$path[1,1].@@request$path[1,2]'> > </@IF> > > 3. Look up the mime type in a table (columns are id, > extension, mimetype) > > 4. Insert the file into a media table (file name, mime type id, file > data, file size) > > >Have you done this with __PDF__ files stored in __MSSQL__? > If so, how > >are you loading the files into the db? > > > >There is more to this issue than just how to set the HTTP header for > >display of pdf files. I can read the file contents and display as a > >pdf; it's the database piece that causes a problem. I > suspect Bengt is > >running into the same issues. > > > >Thanks. j > > > > > >> -----Original Message----- > >> From: Bill Conlon [mailto:[EMAIL PROTECTED] > >> Sent: Wednesday, September 17, 2003 2:41 PM > >> To: [EMAIL PROTECTED] > >> Subject: RE: Witango-Talk: PDF stored in db? > >> > >> > >> Here's what works for me: > >> > >> 1. Query my db (three tables for mime type, media meta > data, and the > >> media data itself -- but you could put this into one table) > >> > >> 2. Set up the request scope variables > >> <@ROWS> > >> <@ASSIGN NAME="type" SCOPE="request" VALUE=<@COLUMN > >> "mime.type">> <@ASSIGN NAME="date" SCOPE="request" VALUE=<@COLUMN > >> "media_description.date_modified">> > >> <@ASSIGN NAME="size" SCOPE="request" VALUE=<@COLUMN > >> "media.size">> <@ASSIGN NAME="filename" SCOPE="request" > >> VALUE=<@COLUMN "media.filename">> <@ASSIGN NAME="file" > >> SCOPE="request" VALUE=<@COLUMN "media.data">> </@ROWS> > >> > >> 3. Set up the HTTP header > >> <@PURGERESULTS><@assign name="encodeResults" scope="request" > >> value="false"><@ASSIGN NAME="httpHeader" SCOPE="request" > >> encoding="none" > >> VALUE="HTTP/1.1 200 OK<@CRLF>Server: Apache > >> 2.0.046<@CRLF>Accept-Ranges: > >> bytes<@CRLF>Content-Type: > @@request$type<@CRLF>Content-Disposition: > >> inline; filename=@@request$filename<@CRLF>Last-Modified: <@VAR > >> NAME="date" scope="request" > format="datetime"><@CRLF>Content-Length: > >> @@request$size<@CRLF><@CRLF>"><@VAR NAME="file" SCOPE="request" > >> ENCODING="none"> > >> > >> >Hiya Bengt! > >> > > >> >1. Check the binary data in your blob against your original > >> file. How > >> >are you putting the PDF in? > >> > > >> >2. Check the pdf binary data that is displayed against the > >> binary data > >> >in the original. > >> > > >> >I've still not gotten this to work properly, due to a > null character > >> >used in the PDF format. That characters always truncated > the data > >> >either at upload or at read; I haven't found a workaround > >> yet. Other > >> >folks said they had gotten it working, but they were either > >> doing image > >> >files rather than PDF, or were not on MSSQL. (I had to drop > >> the issue > >> >when I hadn't managed to resolve it after several days.) > >> > > >> >If you can give me more info about how your process > works, I'll pull > >> >the pertinent info I've collected and forward it. > >> > > >> >Good luck! > >> >jamileh > >> > > >> > > >> >> -----Original Message----- > >> >> From: Bengt Bredin [mailto:[EMAIL PROTECTED] > >> >> Sent: Wednesday, September 17, 2003 9:28 AM > >> >> To: [EMAIL PROTECTED] > >> >> Subject: SV: Witango-Talk: PDF stored in db? > >> >> > >> >> > >> >> > >> >> Hi all ! > >> >> > >> >> I have tried out this example from Troy and Scott but > can't get it > >> >> to work. It's probably some misunderstanding from me, > but I try to > >> >> get some help anyway. So I pull out my blob, a pdf file > from the > >> >> db (MSSQL) and use just a test taf to do this: > >> >> > >> >> <@ASSIGN NAME="filedata" SCOPE="Local" > >> >> VALUE="@@request$resultset[1,data]"> > >> >> > >> >> <@EXCLUDE> > >> >> <@ASSIGN NAME=ENCODERESULTS VALUE=FALSE SCOPE=LOCAL> <@ASSIGN > >> >> NAME=HttpHeader SCOPE=LOCAL > >> >> VALUE="Content-type: > application/pdf<@CRLF><@SETCOOKIES><@CRLF>"> > >> >> </@EXCLUDE><@var request$filedata ENCODING=NONE> > >> >> > >> >> Now I would like Adobe reader to start inside my > browser and show > >> >> me the pdf, but instead I get: Client Error The > response received > >> >> from the application server is invalid. On some > occasions it shows > >> >> the pdf binary data instead. > >> >> > >> >> Hope for better knowledge after San Diego trip > >> >> Cheers... > >> >> Bengt Bredin, [EMAIL PROTECTED] > >> >> Sweden > >> >> > >> >> > >> >> > >> >> > >> >> Great work Troy, > >> >> > >> >> Something to add, that I discovered the hard way, is that some > >> >> versions of the Adobe Plug-in for PDF may not > initialize properly > >> >> because the plug-in needs to know the size of the > document before > >> >> hand. > >> >> > >> >> So you might want to add "Content-Length:" to the HTTP Response > >> >> header, something like: > >> >> > >> >> <@ASSIGN NAME=ENCODERESULTS VALUE=FALSE SCOPE=LOCAL> <@ASSIGN > >> >> NAME=HttpHeader SCOPE=LOCAL VALUE="Content-type: > >> >> application/pdf<@CRLF>Content-Length: <@LENGTH STR='<@COLUMN > >> >> <@DQ>articles.art_blob<@DQ> > >> >> ENCODING=NONE>'><@CRLF><@SETCOOKIES><@CRLF>"><@COLUMN > >> >> "articles.art_blob" > >> >> ENCODING=NONE> > >> >> > >> >> Of course it might be more efficent to capture the <@LENGTH> on > >> >> the upload and store it a separate field next to the > >> PDF content. > >> >> > >> >> Hope this helps. Cheers.... > >> >> > >> >> Scott Cadillac, > >> >> Witango.org - http://witango.org > >> >> 403-281-6090 - [EMAIL PROTECTED] > >> >> -- > >> >> Information for the Witango Developer Community > >> >> --------------------- > >> >> > >> >> XML-Extranet - http://xmlx.ca > >> >> 403-281-6090 - [EMAIL PROTECTED] > >> >> -- > >> >> Well-formed Development (for hire) > >> >> --------------------- > >> >> > >> >> > >> >> > -----Original Message----- > >> >> > From: Troy Sosamon [mailto:[EMAIL PROTECTED] > >> >> > Sent: Wednesday, September 03, 2003 10:59 AM > >> >> > To: [EMAIL PROTECTED] > >> >> > Subject: RE: Witango-Talk: PDF stored in db? > >> >> > > >> >> > > >> >> > Here is the trickey part that you need. > >> >> > Use a javascrip and open a new window and call a search > >> action that > >> >> > returns a blob and use this code: > >> >> > > >> >> > <@EXCLUDE> > >> >> > <@ASSIGN NAME=ENCODERESULTS VALUE=FALSE SCOPE=LOCAL> <@ASSIGN > >> >> > NAME=HttpHeader SCOPE=LOCAL > >> >> > VALUE="Content-type: > >> >> > application/pdf<@CRLF><@SETCOOKIES><@CRLF>"> > >> >> > </@EXCLUDE><@COLUMN "articles.art_blob" ENCODING=NONE> > >> >> > > >> >> > > >> >> > <@comment>other content types-- VALUE="Content-type: > image/jpeg, > >> >> > text/html </@comment> > >> >> > > >> >> > > >> >> > Troy > >> >> > > >> >> > > >> >> > -----Original Message----- > >> >> > From: Troy Sosamon [mailto:[EMAIL PROTECTED] > >> >> > Sent: Wednesday, September 03, 2003 10:32 AM > >> >> > To: [EMAIL PROTECTED] > >> >> > Subject: RE: Witango-Talk: PDF stored in db? > >> >> > > >> >> > > >> >> > I have done it using R:tango, IIS5 and W2k. R:tango is > >> Tango2000 > >> >> > and an R:base database. > >> >> > > >> >> > I am not sure about how your DB handles the blobs, but > >> to load the > >> >> > files, I upload the the file to a temp file on the > >> server and then > >> >> > do a directdbms > >> >> > command to put the file into the database. > >> >> > Pulling the pdf out if you write it to a temp file on the > >> >> > server and link to > >> >> > that file it is very easy. > >> >> > It is a little trickier, but can be done where you pull the > >> >> > file and send it > >> >> > directly to the browser w/o needing to put it in a file on > >> >> the server. > >> >> > > >> >> > Troy Sosamon > >> >> > Denver, Co. > >> >> > > >> >> > > >> >> > -----Original Message----- > >> >> > From: Wilcox, Jamileh (HSC) [mailto:[EMAIL PROTECTED] > >> >> > Sent: Wednesday, September 03, 2003 10:08 AM > >> >> > To: [EMAIL PROTECTED] > >> >> > Subject: Witango-Talk: PDF stored in db? > >> >> > > >> >> > > >> >> > Tango2000, W2K server, IIS5, SQL2K > >> >> > > >> >> > I'm building an application to search and display lots of PDF > >> >> > files. My preference would be to store the files in the > >> database, > >> >> > rather than try > >> >> > to organize and keep track of all the separate files. Does > >> >> > anyone know > >> >> > if this method will work with PDF files? If so, has > >> anyone done a > >> >> > similar app? I'm having problems getting the files loaded & > >> >> > displayed - > >> >> > I'm not sure if I'm doing it wrong, or if it's just not > >> >> going to work. > >> >> > > >> >> > Thanks for any suggestions! > >> >> > > >> >> > jamileh > >> >> > > >> >> > > >> >> > ______________________________________________________________ > >> >> > __________ > >> >> > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > >> >> > > >> >> > ______________________________________________________________ > >> >> > __________ > >> >> > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > >> >> > > >> >> > ______________________________________________________________ > >> >> > __________ > >> >> > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > >> >> > > >> >> > >> >> ______________________________________________________________ > >> >> __________ > >> >> TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > >> >> > >> >> ______________________________________________________________ > >> >> __________ > >> >> TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > >> >> > >> >_____________________________________________________________ > >> __________ > >> >_ > >> >TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > >> > > >> > >> > >> Bill Conlon > >> > >> To the Point > >> 345 California Avenue Suite 2 > >> Palo Alto, CA 94306 > >> > >> office: 650.327.2175 > >> fax: 650.329.8335 > >> mobile: 650.906.9929 > >> e-mail: mailto:[EMAIL PROTECTED] > >> web: http://www.tothept.com > >> > >> > >> ______________________________________________________________ > >> __________ > >> TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > >> > >_____________________________________________________________ > __________ > >_ > >TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > > > > > Bill Conlon > > To the Point > 345 California Avenue Suite 2 > Palo Alto, CA 94306 > > office: 650.327.2175 > fax: 650.329.8335 > mobile: 650.906.9929 > e-mail: mailto:[EMAIL PROTECTED] > web: http://www.tothept.com > > > ______________________________________________________________ > __________ > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
