• 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

upload file and into database

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi JavaRanch,
My question is how would I upload a file, say a text file and insert it into my database. Firstly, I am aware that I need to use the Commons FileUpload from Apache. The only thing I could not find a example of was to use the Commons FileUpload in conjunction with a database. The code shown here... is what i have so far, I just don't understand where I should go on from here. I know that I need to convert the file into a Blob data type but how do I do that? Any help is greatly appreciated!
 
Rancher
Posts: 4803
7
Mac OS X VI Editor Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Its really simple if you separate the actions logically.

Inside your doGet() method, do two steps:

1) upload the file. If its small, you can just read it into memory. If its too large for memory, write it out to a temporary place.
2) read the contents back and call the appropriate DBMS code to store each row.

This also lets you debug it in steps. First get the upload to work. Then make the process to work going from the contents to the DBMS. Finally, glue them together.
 
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
To create the blob, call Connection.createBlob to get a blob object. You can then either set the bytes directly into the blob, or get an output stream that you can stream the bytes to.
 
Pat Farrell
Rancher
Posts: 4803
7
Mac OS X VI Editor Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Also, all your fooling around with Calendar, Date and Timestamp creation is unnecessary and probably won't work the way you really want. If you are happy using the JVM's default locale and timezone, you can simply create a Date and then use its getTime() as the argument to the Timestamp constructor. It would, of course, be cleaner if Java let you simply call an empty constructor on the Timestamp and have it do the same thing, but Date is a four letter word in Java.

If you think you really need a Calendar, you should really pass in a TimeZone so that the Calendar's date has real meaning. I run all my servers on UTC and then apply the user's local timezone and locale for formatting output.
 
Tom Reese
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you for the suggestions, I think I am on the right track. I will debug and get a sense of whats happening. I will post my solution once i get it.
 
Tom Reese
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have done some head scratching and have ran into some issues. I can't use the method Connection.createBlob() because the Postgresql jdbc4 drivers do not support this, that is literally the error I am receiving. Here is my code currently...I am up for any other suggestions on how I can upload a file into my database. What other methods are out there? Thanks for any assistance you can provide.
 
Pat Farrell
Rancher
Posts: 4803
7
Mac OS X VI Editor Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tom Reese wrote:I can't use the method Connection.createBlob() because the Postgresql jdbc4 drivers do not support this



You need to read the JDBC driver's documentation and see how it describes supporting Blob fields. Perhaps there is another approach that is documented.

Folks here who work with Posttresql will know, but you will learn more if you dive into it yourself. Or perhaps you can google for postgres and blob.
 
Jayesh A Lalwani
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm moving this thread to the JDBC forum, because the question primarily is how to save a BLOB in PostgreSQL
 
Tom Reese
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok after some very emotionally trying times I have arrived at a good place in my current code. I figured out the fix for getting around the Connection.createBlob() function. I found the solution here, http://www.xinotes.org/notes/note/943/ I also had to fix some other things revolving around getting the field values along with the file. The problem I am having now is simple, I am not reading in the file bytes correctly and I could some suggestions. I may be doing something really bogus because I have not messed with bytes a whole lot. To test if the bytes are being read I export the test file (small image) to a place on my disk. Currenty the file I export is empty. Sorry for the lengthy code...
 
Jayesh A Lalwani
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tom,

In all of your code I don't see where you are saving the blob. Also, the code reads like a big wall of code.. The official term that I would use to describe here is holy moley.. Yes that's an official term.

You might find it easier to debug and understand your own code if you modularize it a bit. Also it will help you create a SSCCE which will help you test your problem isolation and also help us help you better

And please don't think of this as piling on, but I don't like the way you comment your code. You have lot of comments which is good, but most of the comments just describe what the next line of code is doing. Comments are supposed to explain what the code is doing from a functionality point of view. I'd don't need the comment to tell me that the result set is moving to the last row; I know what rs.last does, or can look it up. I need to know why you are moving to the last row
 
Tom Reese
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well for a moment there I thought I would have to sacrifice a goat to get this working but I finally found a guide that told me exactly what I needed, the guide found here http://jdbc.postgresql.org/documentation/80/binary-data.html uses deprecated methods but simply find the most recent ones which simply use a long type variable instead of a int. Sometimes you just need the right documentation and examples in which my case I did. I understand how file uploading works and it doesn't seem so complicated. Thanks for the suggestions to modularize the code, it made it more readable and easier to understand. Here is my working code... and thanks to everyone who replied, sometimes you just need a little criticism and suggestions. Thread solved, case closed! Code is still lengthy but is much more readable I think. This will be going up on my website!

 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There is still at least one problem - the getLastRowResultSet method. Rows in most (for all practical purposes "all") relational databases do not have any intrinsic order. In simple cases it might appear that SELECT * returns rows in the order in which they were created, but in real life conditions that is almost never true (see our FAQ page regarding the database ordering). Another problem is that ResultSet.last() is expensive operation, it fetches all rows from the query, which gets worse as the table grows. Again, this is easy to miss when testing with a low volume of data.

You need to find another way of locating the row which was just inserted. I don't know PostgreSQL, so I cannot really advice what works best there. One of possible approaches should be to generate the primary key value in a separate call (usually using a sequence) and use it then to insert the new row, which can then be obtained by its primary key. By the way, it looks like your table doesn't have a primary key, so you should perhaps add one.
 
Tom Reese
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yeah I agree, my table has a sequence as a primary key, PostgreSQL increments it automatically for me. I will try to figure out something else instead of using rs.last(). Thanks for your input!
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You'll notice in the API documentation that Connection has a method "prepareStatement(String sql, int autoGeneratedKeys)" which gives you a PreparedStatement with the ability to retrieve auto-generated keys. You may find that PostgreSQL doesn't support this feature properly, but if it does then that would be the way to go.

Although Martin says your table doesn't have a primary key? In that case, like he says, you should add one and let the database auto-generate its value when you insert a row.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:Although Martin says your table doesn't have a primary key? In that case, like he says, you should add one and let the database auto-generate its value when you insert a row.


Tom says the table has a primary key. I just wasn't able to identify it in the code.
 
Greenhorn
Posts: 1
Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Nice. Is there anyway I can upload files using Ajax?
 
After some pecan pie, you might want to cleanse your palatte with this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic