aspose file tools*
The moose likes JDBC and the fly likes Q about java.sql.ResultSet: how to store text in LONG column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Q about java.sql.ResultSet: how to store text in LONG column" Watch "Q about java.sql.ResultSet: how to store text in LONG column" New topic
Author

Q about java.sql.ResultSet: how to store text in LONG column

Gian Franco
blacksmith
Ranch Hand

Joined: Dec 16, 2003
Posts: 977
Hi,

I've got a ResultSet that I need to update in order to
insert some data in the corresponding database table.
The database table has amongst others a column of the
LONG datatype. This datatype is giving me some trouble
since it should be able to store 4Gb, but when the
application tries to insert some textual data the LONG
column won't accept 1Kb data, giving me the JDBC error
ORA-17070 'Data size bigger than max size for this type'.

My application is using
java.sql.ResultSet.updateObject(int arg0, Object arg1).

What method should I use for storing my LONG data?

Cheers,

Gian Franco
[ August 18, 2004: Message edited by: Gian Franco Casula ]

"Eppur si muove!"
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Have you tried updateLong()?


GenRocket - Experts at Building Test Data
Gian Franco
blacksmith
Ranch Hand

Joined: Dec 16, 2003
Posts: 977
Originally posted by Gregg Bolinger:
Have you tried updateLong()?


The column has the SQL datatype LONG, not
the Java primitive long datatype.
updateLong() is used for the latter.
My LONG column in the database should accept
textual data as well.

Cheers,

Gian Franco
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Gianfranco,

What you effectively have is a Clob (I believe). I think the easiest way to get your data in is using setBytes(), though I've never done it myself. You could also use the setClob() method on PreparedStatement though I've never worked out how to create a Blob or Clob to do that...

Jules
Gian Franco
blacksmith
Ranch Hand

Joined: Dec 16, 2003
Posts: 977
Originally posted by Julian Kennedy:
What you effectively have is a Clob (I believe). I think the easiest way to get your data in is using setBytes(), though I've never done it myself. You could also use the setClob() method on PreparedStatement though I've never worked out how to create a Blob or Clob to do that...


Hi Jules,

I'm afraid it's not a Clob, it's a LONG (p.s.
uppercase is not because I'm shouting ). I've read
that LONG is being deprecated in newer Oracle versions
and still there for backward compatibility only.
Do you mean setClob can be used for LONG as well?

Cheers,

Gian Franco
[ August 18, 2004: Message edited by: Gian Franco Casula ]
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
I would guess so. After all a Clob is just a "Character Large Object". Does that not describe an Oracle LONG? The JDBC API abstracts these things; it doesn't care what Oracle calls it. I would expect both setBytes() and setClob() to work, but I'm out of my area of experience here. It shouldn't take a few minutes to try it...give it a go!

Jules
Gian Franco
blacksmith
Ranch Hand

Joined: Dec 16, 2003
Posts: 977
Thanks, but it does not work in my case

Any other suggestion is welcome.

Cheers,

Gian Franco
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
In what way did those methods fail? Did you get the same ORA-17070 'Data size bigger than max size for this type' error? How did you create the Clob to pass to setClob()? Did you try using a PreparedStatement rather than the updateBytes() and updateClob() methods on ResultSet? There's another interesting method on ResultSet that might be worth a try: updateCharacterStream(), which takes a Reader and a length.

Failing that, have you checked your Oracle JDBC driver documentation to see what they say about adding data into LONG columns? You may find you need to change/update your JDBC driver. I've had to use PreparedStatements in the past to get round similar limitations in Sybase stored procedures with the TEXT datatype (not quite the same situation).

After that I'm all out of ideas. Change database to one that works with you Java code.

Jules
Gian Franco
blacksmith
Ranch Hand

Joined: Dec 16, 2003
Posts: 977
Hi Jules,

It works now . After looking at many possible
causes the solution was simple really.
I use
PreparedStatement prepareStatement(String arg0)
and
setCharacterStream(int arg0, Reader arg1, int arg2)

The reason why this combination wouldn't work the
first time I tried was the use of an overloaded
version of prepareStatement(). In my case this
just wouldn't allow me to update the LONG field
and gave me ORA17070.

Thanks for bearing with me

Cheers,

Gian Franco
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Q about java.sql.ResultSet: how to store text in LONG column