Win a copy of Zero to AI - A non-technical, hype-free guide to prospering in the AI era this week in the Artificial Intelligence and Machine Learning forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

Storing an MS Excel xlsx file in a database; when retrieved it will not display

 
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I am storing MS Excel xlsx, MS Excel xls, MS Word docx and MS Powerpoint pptx files in a MySQL database and then retrieving them. When I retrieve these files they will not display. I am also doing this for pdf, doc, jpeg, jpg, png, and gif; which all work fine.

The error for the xlsx file is: "Excel cannot open the file 'resourcefile.xlsx' because the file format or file extension is not valid.".

This is the code I use to store the MS Excel xlsx file (client side javascript, ajax):



Which gives (first part of string):

UEsDBBQABgAIAAAAIQBi7p1oXgEAAJAEAAATAAgCW0NvbnRlbnRfVHlwZXNdLnhtbCCiBAIooAA

I then retrieve it with (server side java code):



Which returns (first part of string):

data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,UEsDBBQABgAIAAAAIQBi7p1oXgEAAJAEAAATAAgCW0NvbnRlbnRfVHlwZXNdLnhtbCCiBAIooAACAAAA

Can anyone please explain what I am doing incorrectly?
 
Master Rancher
Posts: 4698
49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why are you turning your sheet into a base64 encoded String rather than simply sending the document to the server?
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Dave,

Because this is how I read it should be done to store it as a longblob in MySQL. It works for pdf, doc, jpeg, jpg, png, and gif. It took me ages to get working. Now I am trying to add MS Excel xlsx, MS Excel xls, MS Word docx and MS Powerpoint pptx files.

Kind regards,

Glyn
 
Saloon Keeper
Posts: 6625
161
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Storing files in a DB is almost never the right approach. That's what file systems are for. Only the associated metadata should go into the DB.
 
Dave Tolls
Master Rancher
Posts: 4698
49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim's valid statement aside, if you were sticking docs into a longblob then why not stream them as bytes? You only base64 encode things to stick them in character columns (clobs).

Blobs are for binary data.
 
Saloon Keeper
Posts: 22630
153
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another vote for Tim Moore.

BLOB stands for Binary Large OBject. CLOB stands for Character Large OBJect. Sometimes known as a long string/text field, depending on the database vendor.

Base64 was designed for Multimedia Internet Mail Exchange (MIME) back in the days when the Internet often meant passing data through different computers that ran different OS's on different hardware and with different byte/word sizes. By turning binary data into a character representation, you avoided things like getting bytes swapped around or having parity values jammed on to of the 8th bit of a byte (ASCII as originally designed was a 7 bit code with an optional parity bit).

If memory serves, an XLSX file is a ZIPP (binary) file containing a set of XML (text) files. If you email it, then the email system will encode it in Base64 format as an attachment with MIME headers, but the recipient's email client program will then decode it again. You don't have to do encoding yourself. If you send the XLSX as a download from a web application, however, the data will be transmitted as binary data. So again, no encoding on your part.

In short, if you MUST store an XLSX, Word document or PDF in a database, make it a BLOB and forget about encoding it. But it's better to store such items as discrete files and just use the database to locate and describe them. In fact, a lot of database vendors internally handle BLOB/CLOB fields just that way. But when you do it yourself you have better control over the process and it's less messy if the database gets corrupted and you have to rebuild it.
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tim,

Thank you for your advice. When I store the image I currently use:



How do I store the "image" as a blob without encoding it please? I have tried ps.setBlob(5, image) and ps.setBlob(5, new SerialBlob(image)); both of which give me an error.

Kind regards,

Glyn
 
Tim Holloway
Saloon Keeper
Posts: 22630
153
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you need something like new SerialBlob(image.getBytes()) because I don't think that image is actually a byte array. Without seeing it created, I think its some sort of Object.

Definitely don't encode it. As we said, it's pointless.
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tim,

I tried:



The stored file began with: data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,UEsDBBQABgAIAAAAIQ

When I retrieved it with:



I got back: data/application/vnd/openxmlformats/officedocument/spreadsheetml/sheet/base64/UEsDBBQABgAIAAAAIQDfz5

I will try your suggestion next.

Do I need to change my retrieval code as well?

Kind regards,

Glyn


 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tim,

I have tried your sugestion:

                       


and got the same result (i.e., the returned string is:  data/application/vnd/openxmlformats/officedocument/spreadsheetml/sheet/base64/UEsDBBQABgAIAAAAIQDfz5).

I then changed the read to:

                                     

And again got the same output.
 
Tim Holloway
Saloon Keeper
Posts: 22630
153
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Come to think of it, why is a spreadsheet an "image"?  
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tim,

OK, I have corrected my stupid coding mistakes replacing "==" with ".eqauls()" and putting in displays to ensure the correct path is taken. So I now store with:



And retrieve with:




This now stores and returns: data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,UEsDBBQABgAIAAAAIQDfz5sukgEAA

However, I still get the same error when trying to open the file.

Kind regards,

Glyn
 
Marshal
Posts: 25927
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm a bit confused by this code:



It looks like you're saying, if it's a spreadsheet then extract the bytes from the blob and turn them into a String. Turning them into a String would be the wrong thing to do.
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

This is probably where my issue lies. What code should I use to get the spreadsheet from the database please?

Kind regards,

Glyn
 
Paul Clapham
Marshal
Posts: 25927
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I thought what you just posted was in fact the code to get the spreadsheet from the database. Looks okay to me as far as where you put it into a byte array.

And from your earlier post it looks like maybe you took data from an array of bytes and transferred that into the database, although for some reason you want to treat spreadsheets differently from other files types. I don't see any point in that, they're all just arrays of bytes under the covers.

Just don't convert any of those byte arrays to a String, because none of them are text.
 
Tim Moores
Saloon Keeper
Posts: 6625
161
Android Mac OS X Firefox Browser VI Editor Tomcat Server Safari
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:Just don't convert any of those byte arrays to a String, because none of them are text.


Quoted for emphasis.
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul and Tim,

I have traced this to the download function I am using which I got from: http://danml.com/download.html

I have compared the string just before putting it into the DB with just before passing to the download function and they are identical. I then used my program to store the resulting downloaded file (that will not open) to the database and compared this file with the previous string and they were significantly different. I therefore believe that the download function is the cause.

The download function documentation states that it does handle strings and certainly works with doc, pdf, png, jpeg, jpg and gif. So I am surprised this is not working.

You say not to convert to string. So if I pass back "byte[]" then how is this handled by Typed Array of data (also supported by the function) in the javascript?

I will try to get some time this weekend to rewrite the code to use "byte[]".

Kind regards and thanks for all your help,

Glyn
 
Paul Clapham
Marshal
Posts: 25927
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As far as I'm concerned, I would treat XLSX files exactly the same way I treat JPG and PDF files. I see no reason to treat them any differently. I don't see the need for any additional programming -- if you have special code for XLSX I would remove it. On the Java side you must be getting a byte array for JPG and PDF files and you don't have a question about how to deal with that.

Just because your download function handles strings, it doesn't follow that String is a suitable container for the contents of an XLXS file or any other binary file.
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

I started out by treating doc, docx, xls, xlsx, pptx the same way as pdf, png, jpeg, jpg and gif, and the only one that worked was doc. And therefore my hours of frustration and the long list of posts :-). I am very, very greatful for everyones help.

Kind regards,

Glyn
 
Paul Clapham
Marshal
Posts: 25927
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Then you must be doing something different on the client side for those formats which "don't work". I have no idea what, though. When I was using Javascript to upload files I never had to write code to convert to and from Base64, the browser took care of that for me. You're using some technique which is unfamiliar to me.
 
Dave Tolls
Master Rancher
Posts: 4698
49
  • Likes 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First (as people have been saying) don't upload files as Strings.
There is a whole mechanism for uploading files to a web server. Use that mechanism, which streams the file as bytes.

As for download, again, don't treat the downloads as Strings. That download javascript is completely unnecessary.
Stream the file back from the server as bytes.  That's all there is to it.

If you find yourself at any point in this process thinking of the files your are up or downloading as Strings, then take a step back and say to yourself "that's not right".
They are simply a collection of bytes.
 
Paul Clapham
Marshal
Posts: 25927
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:There is a whole mechanism for uploading files to a web server. Use that mechanism, which streams the file as bytes.



Indeed, it's built into HTML. (I misspoke when I said I used JavaScript to upload files -- it's just plain old HTML)
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul and Dave,

I assure you, when I started I was not doing anything different. However, following the advice above, it is now different.

Anyway, I cut out the middle man and used the "download" function to display the image/file immediately after loading it (i.e., not storing and retrieving it). This worked for pdf, doc, jpeg, jpg, png, and gif; however, not xlsx. So I then used the bytes and this worked for all the file formats I am testing. So, the issue is with the "download" function. As I have not been able to find an alternative to the "download" function I will need to pass bytes between the client side and the server side. This will also mean that I will need to download and upload all images currently in the database to re-attach the mime part that I removed.

Why did I do it this way in the first place. Because that is the advice I received at the time in the forums. :-)

I very much appreciate all your help and advice.

Kind regards,

Glyn
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK. I am now trying to pass the byte array from the client side (javascript) to the server side (java) using ajax. However, the ajax post is not working and no error is displayed. The only change to the post is that the image is a byte array instead of a string.




     

On the server side:



I suspect ajax has an issue with passing byte arrays? How do I get around this please.

Kind regards,

Glyn
 
Tim Holloway
Saloon Keeper
Posts: 22630
153
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's not just AJAX, it's jQuery running AJAX. And as a side note, I must mention that using "$" jQuery syntax on a JSP can be hazardous, since "$" also is a JSP Expression Language meta-character.

AJAX is used to send HTTP requests, and yes, HTTP is a text-only protcol so trying to use AJAX for binary file uploads is a problem. You are far better off doing a straight HTTP FORM file upload where the sordid details of such things are managed more neatly.

The bigger issue is that you're over-complicating your solutions to common problems. We do HTTP-based file transfers and storage routinely and never get anywhere near explicit Bas64 translations ourselves and it works just fine.
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tim, Paul and Dave,

You guys have been absolutely amazing with your help; thank you. The issue is on my side that I am not a programmer and do not understand a lot of this. I rely on forums for help and have no other access to help. So what would take you a day or two takes me a month or two at times. I wrote this program some time ago (I am now expanding the file types) and was quite vexed that it took me months and found it so hard to find help for what I though was a simple requirement that must be done by millions of programmers every day. This is one of hundreds of programs in my application written in my spare time for free.

I am using HTML and javascript. I separate my html and javascript files (e.g., Resource.html and resource.js) and using '$' instead of 'jQuery' as that is what the articles I read advised. This is the first I have heard of a conflict and have never encountered it. Should I do a global replace of '$' with 'jQuery' through all my code?

It seems that the conversion of the byte data to string before sending it to the server side and storing it is working. The issue is occurring when I send it from server side to client side and try to display it using the 'download' function. So is it possible for me to continue doing the down load as I have been doing it and only change the upload from server side to client side? I would change:



Change 'private String resourseImage;' to 'private byte[] resourseImage;'.

Would this offend or upset anyone? I have never used 'a straight HTTP FORM file upload'. So should I just learn how to do it properly in the first place? Do you have a simple HTML, jQuery, Java example I can look at?

My current HTML/ Bootstrap 3 is:



As I said  you guys have been amasing with your help and I do not wish to upset you by seeming obtuse, set in my ways or unwilling to listen and learn.

Kind regards,

Glyn

 
Paul Clapham
Marshal
Posts: 25927
69
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here's an example of uploading a file: Uploading Files. It actually looks simpler than when I did it.

You'll need to change it to store your file in a database instead of just writing it to a location on the server. When I did that I never used Blobs, I just used the PreparedStatement method setBinaryStream(), using an InputStream which you can get from the uploaded file in your servlet.

Likewise in your file download servlet you can use the ResultSet getBinaryStream() method and then stream bytes from that stream to the servlet's response stream.

 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Paul,

So I am working my way backwards. I know the file is being stored correctly, so first I will concentrate on displaying the file. So I changed "private String resourseImage;" to "private InputStream resourseImage;" and retrieve it with:









The console log contains:

responseJson1a['resourseImage']: {"buf":[-4,-17,124,117,-85,90,-3,-86,105,-106,39,26,-74,42,39,-2,-7,-35,-2,-118,94,-97,25,-91,126,-118,-26,106,-37,63,-95,-9,-30,113,-25,104,114,-23,-98,-9

I also tried:


The downloaded file does not work (I tested jpg and xlsx).

Any ideas please?

Kind regards,

Glyn

 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I also tried:



and

 
Rancher
Posts: 912
22
Netbeans IDE Oracle MySQL Database Tomcat Server C++ Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Glyndwr Bartlett,

the many people that have suggested storing your file in the file system and not the DB are quite correct: store the meta in the database and assemble that file when you read it into an actual XLM file and put it out on your network.  

I speak from experience there: we did an early image project and the trials we ran showed that the file system for the images and actual file data was much faster and less maintenance than when stored in the DB.  We pulled the name and the related reference links to supporting files and read directly from the network for the actual files.  The saved us huge time over manual lookup and the file system saved at least 30% per document that we had saved.  The project wasn't all that large around 1/4 million images and supporting documents.  It was a good solution and may still be in production today--some 25+ years later.

Les
 
Glyndwr Bartlett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Les and everyone else,

I am overwhelmed by your generosity of help. While I have read that it is faster to store in the database and retrieve from the database I find the advice on this forum more credible. So I will take your advise and start from scratch. I am feeling mentally drained at the moment and will take a break for a while and then get back to you. If you can provide me with some links to html, jQuery, ajax, java examples that would be appreciated.

Kind regards,

Glyn
 
Tim Holloway
Saloon Keeper
Posts: 22630
153
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Beware what you read on the Internet. Not only are some of the "experts" not all that expert, but the information can be horribly out of date.

Though I have never heard of a database BLOB being more efficient than an independent file. As I said, it's quite common for databases to store BLOBs in discrete files anyway.

On the "$" jQuery notation. It's safe to use $-notation in .js and HTML files. It's risky to use it in JSPs or other JEE resources that support the Unified Expression Language like JavaServer Faces templates. In cases of doubt, the "jQuery." form always works, though.
 
If I'd had more time, I would have written a shorter letter. -T.S. Eliot such a short, tiny ad:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic