Forums Register Login

inserting BLOBs into MySQL database

+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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!
+Pie Number of slices to send: Send
You can use PreparedStatenment's setBinaryStream.
+Pie Number of slices to send: Send
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!
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
get ... Binary... Stream!
+Pie Number of slices to send: Send
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?
+Pie Number of slices to send: Send
I didn't mean to be so down on using Blob. When using Hibernate, I've used BlobImpl.
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
 

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?
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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);
+Pie Number of slices to send: Send
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 ?
We cannot change unless we survive, but we will not survive unless we change. Evolving tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 6625 times.
Similar Threads
Reading a blob object from a database
How to upload a byte array into BLOB column?
JDBC updates using result sets
Problem inserting Blob into Oracle DB
Reading From a Binary Large Object(BLOB)
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 07:55:17.