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

[Gian Franco]

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 ]

[Gregg Bolinger]

Have you tried updateLong()?

[Gian Franco]

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

[JulianInactive KennedyInactive]

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]

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 ]

[JulianInactive KennedyInactive]

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]

Thanks, but it does not work in my case

Any other suggestion is welcome.

Cheers,

Gian Franco

[JulianInactive KennedyInactive]

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]

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