• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Uploading a text file to a blob in MySql, then downloading that file to local file system

 
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have searched the archives and have not found an example for uploading a text file to a blob column in MySql and then downloading that same file to a local directory.
I want to learn how to work with the Java streams and the Blob data type so hopefully someone can help me accomplish this. I have read several tutorials and taken
the code from them and modified it as best I could determine from the tutorials and books I have read the past week.

Here is what I have so far; most of this is putting together what I think should work as I have not found an example for text files.
If you can tell me if I am on the right track here, I would very much appreciate your time.

At this time, when I try to download a file, the Windows File Conversion dialog appears with "Japanese (Shift-JIS)" selected. So, obviously I have a few
things not quite right. Should I be using "setBlob" rather than "setBinaryStream" in the upload method? The examples I have found were for image files and not text files.
I have removed most, if not all, of the debug statements to shorten the post; but there are debug statements in the actual code.

I will also include a description of the table I am uploading the file to ( see below code ).




Here is the table description:

 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I know this was a long post; I tried to provide all the relevant details. I just don't know if I'm missing something with using streams or if it's a lack of understanding about blobs. At one time I did use the upload code ( along with some other lines of code, of course ) to transfer a file from one directory to another on my local machine. Now I'm trying to store the file as a blob in a MySql database and then retrieve that file to a directory on my local machine. So I think the upload code may be ok; but there is a problem with the download method.

I sure would appreciate some direction this weekend so I can understand how this works better.

Thank you so much.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Mary,
I always use setBinaryStream. There's nothing inherently problematic there.

Nothing jumps out at me in the code. My advice is to simplify it a bit more. If you write a method that retrieves the BLOB from the database and writes it out to the file system, can you open that file? This will tell you if the problem is in the JDBC code or the web code. And hopefully give an error message if the former.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The dialog appears asking me if I want to open the file or save it, so I chose to save it just now. When I go to the file system and try to open it,
that is when I get the File Conversion dialog which defaults to opening it in Japanese. If I choose to open the file instead of save the file immediately,
I also get the same dialog . Since the file is not related to anything Japanese, I assume I am using the streams incorrectly in some way.

What else might I try?

Looking at size of uploaded and downloaded files, they are the same KB so wondering why I can't open downloaded file?
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Mary Taylor wrote:...
What else might I try?

Looking at size of uploaded and downloaded files, they are the same KB so wondering why I can't open downloaded file?


Compare the original file and the downloaded file from the command promt.

On a windows machine you can do that by running:
compare <original> <downloaded>

and on unix:
diff <original> <downloaded>
or you can use GNU cmp utility, better suited for binary files.

Now you don't have to wonder anymore, but you will know for fact if they are the same.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm going to Google this, but when I try using "compare" on Windows Vista, I get this message:

'compare' is not recognized as an internal or external command,
operable program or batch file.
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My bad. It's COMP
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm attaching a picture of what the file looks like when I open it in Word. I think I am just handling the streams incorrectly.
Does anyone see an error in my code downloading or storing the file?
DownloadedFile.jpg
[Thumbnail for DownloadedFile.jpg]
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ah. This is not a clean word document. It contains markup info.
Are you streaming via a JSP page?

The issue is not in your JDBC part, but in the way you stream it.
You might get more help on how to properly stream a file on the Servlet forum.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The code I posted is a method in a Java helper class I call "Utilities.java"; it does not inherit from HttpServlet.

The arguments to the method are, however, an HttpServletResponse and a String as can be seen from the first post.

I'm not sure how to transfer a post if that is what needs to be done. I have been at this point for several days and would like to resolve this issue for sure!
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not sure if this is servlets of JDBC. Since it's been here (JDBC) a while, let's give Servlets a try. Moving.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What does the data look like in the database? Is it just the text of the file, or are the headers all mixed up in it?
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm really feeling like I should know this; but I'll ask anyway. How should I look at the
data in the database to determine the answer to your question?

I did use the same upload code a while back to simply transfer the file from one directory to
another in the file system, and that worked fine. Then I tried to progress to loading it in the
database which at least gives no obvious errors. Finally I want to download the blob from
the database, and I'm told the JDBC code is fine.

I'll be out for a couple of hours right now, but I will try whatever you suggest when I return.

Thanks.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Should a text file be stored as a "LONGBLOB" or "LONGTEXT" file?

As you can see from the initial post, this Word document is
being stored as a 'LONGBLOB"; does anyone know if that is correct
and will work?

Unfortunately, I've been at this impasse more than a week; if you
have any suggestions, I'd love to hear them!

Thanks so much.
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Mary Taylor wrote:I'm really feeling like I should know this; but I'll ask anyway. How should I look at the
data in the database to determine the answer to your question?

Any database tools such a Squirrel SQL, DBVisualizer, or a handful of others should let you peak at any JDBC-compliant database.
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is it a text doc or a Word file? Word files are not text docs.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It is a Word file; you are right, they are not the same.
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As Word docs are binary, you'll be best off with a binary format.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The JDBC forum did say the JDBC paft of the code was correct.

I'm just puzzled as to what the problem is now...
 
Author
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm wary of not setting the content type or encoding. I've also seen cross-browser issues with setting the content-length; I might first try not setting that at all.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The content type in the response is set ( see initial post ) as follows:



Is this not correct?

What additional setting should be made?

Thank you as always.
 
David Newton
Author
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Oh; missed that--normally I see people putting all related functionality close together in a class.

But no, that's not correct: as has been discussed Word files are *not* plain text. I also don't know of mimetypes that have three levels, but could be wrong about that.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I do believe in putting related functionality together; I'm just not at all familiar with this
so please tell me where I could improve the code. I will investigate what the content type
should be; that could be the entire problem! Thanks.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Mary Taylor wrote:The content type in the response is set ( see initial post ) as follows:



Is this not correct?

What additional setting should be made?

Thank you as always.


No. The context type should be "application/msword"
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have changed the downloading code as follows and still not having success; do you see any other mistakes?
I feel like I am just overlooking something obvious because I've been looking at this for ten days now.

This code is a bit more organized ; all code related to the response is in one place. (Had to change this when I read
that you must use the setContentType method on the ServletResponse before obtaining any object (Stream or Writer) used for output).

The length of the file being downloaded as put out by the debug statement matches the size of the file uploaded so I'm
thinking the file is correct in the database. and the downloading code is the problem.

Even though I have now set the encoding for the response, Windows is still popping up the File Conversion dialog and defaulting to

Japanese (Shift-JIS)

. Is the encoding set properly? What else is incorrect?



Thanks to all who are taking a look at this; I hope it is a good resource for others once we solve this.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just looked at contents of blob through MySql Query Browser. Actually the Query Browser doesn't open the blob;
I had to save it to a file and then open it.

The file being downloaded is what is in the database so the error is in the upload code and not the download code.
Please see the initial post for the upload code. Of course, it looks fine to me; but there must be something wrong
with it.
 
David Newton
Author
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Only the insertion into the DB is there; the upload isn't.
 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
We have to be getting close to the solution!!!

I have to go pick up my car now; do I need to set the encoding here also perhaps or where it is inserted into the database?

 
Mary Taylor
Ranch Hand
Posts: 327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Maybe it's not an encoding issue after all; perhaps it's that I'm not stripping the headers off before inserting in the database.
I think I'm dumping the whole POST request from the user into the database. When I look in the database, the headers are all there
and then the actual content of the Word document is there in very readable format.

Should I just give up and use some framework to do this or does anyone have sample code that will strip of the headers
and let me just grab the content of the file I am uploading?

Thanks for your time.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Mary,
To be honest, I've never written that code from hand. There are a number of open source file upload libraries though.
reply
    Bookmark Topic Watch Topic
  • New Topic