aspose file tools*
The moose likes Servlets and the fly likes Uploading a text file to a blob in MySql, then downloading that file to local file system Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Uploading a text file to a blob in MySql, then downloading that file to local file system" Watch "Uploading a text file to a blob in MySql, then downloading that file to local file system" New topic
Author

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

Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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

Joined: Sep 11, 2000
Posts: 319
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.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30548
    
152

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2500
    
    8

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.

OCUP UML fundamental and ITIL foundation
youtube channel
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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

Joined: Dec 20, 2006
Posts: 2500
    
    8

My bad. It's COMP
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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?


[Thumbnail for DownloadedFile.jpg]

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2500
    
    8

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

Joined: Sep 11, 2000
Posts: 319
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30548
    
152

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.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61237
    
  66

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?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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

Joined: Sep 11, 2000
Posts: 319
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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61237
    
  66

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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61237
    
  66

Is it a text doc or a Word file? Word files are not text docs.
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
It is a Word file; you are right, they are not the same.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61237
    
  66

As Word docs are binary, you'll be best off with a binary format.
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
The JDBC forum did say the JDBC paft of the code was correct.

I'm just puzzled as to what the problem is now...
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

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

Joined: Sep 11, 2000
Posts: 319
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
Rancher

Joined: Sep 29, 2008
Posts: 12617

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

Joined: Sep 11, 2000
Posts: 319
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30548
    
152

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

Joined: Sep 11, 2000
Posts: 319
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

Joined: Sep 11, 2000
Posts: 319
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
Rancher

Joined: Sep 29, 2008
Posts: 12617

Only the insertion into the DB is there; the upload isn't.
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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

Joined: Sep 11, 2000
Posts: 319
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30548
    
152

Mary,
To be honest, I've never written that code from hand. There are a number of open source file upload libraries though.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Uploading a text file to a blob in MySql, then downloading that file to local file system