This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes inserting BLOBs into MySQL database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "inserting BLOBs into MySQL database" Watch "inserting BLOBs into MySQL database" New topic
Author

inserting BLOBs into MySQL database

Simon Ingram
Ranch Hand

Joined: Nov 30, 2003
Posts: 171
Hi folks,

Yes, this has come up before and I have read the posts, but they don't help!

I have an image that I wish to load into a BLOB field (called image) on a MySQL database. I have converted my .gif file into a byte array and was planning on loading the field as follows:



Now, the problem is to obtain the blob object and load it with my byte array before I setBlob. One cannot simply create a new blob and then:



I suppose I must get the blob object from a result set. So, I have run an sql query to retrieve the record from my database that contains the null blob field that I want to populate. With this result set I get the blob with

rs.getBlob("image");

I got some other fields from this record as well, to confirm that the query and result set are OK, but as soon as I try to call a method on my blob, for example



I get a null pointer exception! I can't get a blob object! Is my approach correct?

[ March 27, 2006: Message edited by: Simon Ingram ]
[ March 30, 2006: Message edited by: Simon Ingram ]
Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 186
You will probably have to implement the java.sql.Blob interface in order to instantiate some concrete representation of your image that you can pass to the prepared statement.
Simon Ingram
Ranch Hand

Joined: Nov 30, 2003
Posts: 171
representing the image is not the problem, the problem is having a reference to a blob object that I can call methods on! I have been searching the sun site for sample code, nothing so far. Come on guys!
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
You can use PreparedStatenment's setBinaryStream.


There is no emoticon for what I am feeling!
Simon Ingram
Ranch Hand

Joined: Nov 30, 2003
Posts: 171
of course I can. I can call any method on a blob, if only I could get the blob in the first place.


And here is what happens at the command line:

C:\Java\JDBC\classes>java com.ingasimn.EV011 5
SELECT Title, URL, Image from Titles WHERE TitleID = 5
connection established
got record
The Patriot : thepatriot.jpg
image length = 3060
byte array loaded
java.lang.NullPointerException

you see - the attempt to call a method on the blob object fails and I have no idea why!
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
Simon,

I thought you were executing an update. If you are selecting try ResultSet's getBinaryStream. Forget about Blobs. They're fussy and support for them varies from one vendor to another.
Alan Shiers
Ranch Hand

Joined: Sep 24, 2003
Posts: 237
I've encountered the same problem regarding the Blob object. It was originally declared as an Interface class with abstract methods. Therefore, one can't simply intantiate it like: Blob blob = new Blob();
I think that sucks big time!

Regardless, I have the same dilemma and want to know how to work around the problem?

In my case I have two methods:

According to the literature I found at SUN, you obtain a Blob object by first retrieving the existing Blob from the database using the RecordSet object. But what they don't tell you is that it's useless if the field you're tring to access doesn't have anything in it to begin with! Thus my method call to getCompanyLogo(...) will return null if I haven't put anything in there yet!

How do you get around this?

Alan
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
get ... Binary... Stream!
Simon Ingram
Ranch Hand

Joined: Nov 30, 2003
Posts: 171
Alan,

you are describing my problem exactly! This is why Jeff though I was executing an update. I was trying to update the empty blob field in my database. My method was to get the record with a select, retrieve the result set for this record and call getBlob on the result set to get my handle for the update, which was poised and waiting. But, as you say, it doesn't work! Perhaps Jeff is right. Blobs don't work period! Seems hard to believe that so many people have developed and documented the use of Blobs and none of it works. Can it really be the case that no one in the world has managed to get an image into a blob field. I know, Jeff...getBinaryStream.. but that's a cop out. I was hoping there was something simple that I had forgotten to do. Have any of you moderators got a view on this?
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
I didn't mean to be so down on using Blob. When using Hibernate, I've used BlobImpl.
Alan Shiers
Ranch Hand

Joined: Sep 24, 2003
Posts: 237
Originally posted by Simon Ingram:
Alan,

you are describing my problem exactly! This is why Jeff though I was executing an update. I was trying to update the empty blob field in my database. My method was to get the record with a select, retrieve the result set for this record and call getBlob on the result set to get my handle for the update, which was poised and waiting. But, as you say, it doesn't work! Perhaps Jeff is right. Blobs don't work period! Seems hard to believe that so many people have developed and documented the use of Blobs and none of it works. Can it really be the case that no one in the world has managed to get an image into a blob field. I know, Jeff...getBinaryStream.. but that's a cop out. I was hoping there was something simple that I had forgotten to do. Have any of you moderators got a view on this?


Well, it took some doing and yes Jeff I did, in the end, wind up using getBinaryStream() to make it work. I'm sure I and Simon are not the only ones who have faced this problem. How long has this problem been in existance without SUN coming up with a concrete BLOB class that we can all instantiate? That sure would save on a lot of headaches and head scratching.
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
Originally posted by Alan Shiers:

How long has this problem been in existance without SUN coming up with a concrete BLOB class that we can all instantiate? That sure would save on a lot of headaches and head scratching.


I haven't tried it, but have you taken a look at SerialBlob?
Alan Shiers
Ranch Hand

Joined: Sep 24, 2003
Posts: 237
No I wasn't aware of the existance of SerialBlob. Thanks for pointing that out. I'll work with it and see if it makes things any easier.
Simon Ingram
Ranch Hand

Joined: Nov 30, 2003
Posts: 171
I notice that SerialBlob is SE5.0. I am currently on 4.2, so I am accomodating myself to using getBinaryStream BUT.. this has to be called on a blob object and my problem has always been that I get a nullpointer exception trying to call methods on the blob. Any chance of a code sample so I can see how you clever guys made it work!

regards and thanks,
Simon
[ March 30, 2006: Message edited by: Simon Ingram ]
shobhar gupta
Greenhorn

Joined: Mar 30, 2006
Posts: 4
Hi Jeff
I am facing same problem for some days, tried everything nothing working, i have tried binaryStream as u say, but i am not able to send bigger files, it is not allowing something more than 15-20 Kb, even failing at 31 kb i tried, says Data size bigger than max size for this type: , can you please help me on this, i am getting really frustrated by this blob thing, help will be really appreciated
my code is like this:

java.io.ByteArrayInputStream byteStream = new java.io.ByteArrayInputStream(byteData);
int numBytes = byteData.length;
cstmt.setBinaryStream(parmIndex, byteStream, numBytes);
shobhar gupta
Greenhorn

Joined: Mar 30, 2006
Posts: 4
i forgot to tell i am using oracle, rather than Mysql, and having trying to set it into BLob datatype in oracle, i checked oracle specification, it says 4 GB is allowed for BLOB, and setBinaryStream is ok for BLOB as i am able to add smaller files , problem is with bigger ones. Anybody knows why ?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: inserting BLOBs into MySQL database
 
Similar Threads
JDBC updates using result sets
Problem inserting Blob into Oracle DB
How to upload a byte array into BLOB column?
Reading a blob object from a database
Reading From a Binary Large Object(BLOB)