Re: SQLException on inserting string using JDBC

From: Shaun Tancheff <shaun_at_tancheff.com>
Date: Tue, 08 Feb 2000 18:05:47 GMT
Message-ID: <38a05833.98810@news>

On Tue, 08 Feb 2000 01:10:17 GMT, shaun_at_tancheff.com (Shaun Tancheff) wrote:

>On Fri, 04 Feb 2000 15:44:11 -0500, Mukul Sood <msood_at_iframe.com>
>wrote:
>
>>Hello All,
>>
>>I am having problems inserting a large string, size 64kb, in Oracle
>>8.0.5
>>database.
>
>I'm currently fight the same 'feature'
>AFAIK you have to use what Oracle calls 'streams' to do this.
>
> http://www.hvcc.edu/admin/hvccinfo/oracle8doc/a58237/toc.htm
>
>
>>I'm using a PreparedStatement to do the insert.
>>pstmt.setString(1, myString)
>
> String longString = "really long ... ";
> int len = longString.length();
> ByteArrayInputStream stream =
> new ByteArrayInputStream( longString.getBytes() );
> pstmt.setBinaryStream( 1, stream, len );
This is what I'm using now, and it appears to work:

   pstmt.setAsciiStream( 1, stream, len );

One more caveat is that on Linux (debian potato) the 'streams' hack  doesn't work when you try to insert more that 4000 bytes. (NOT 4K  mind you). I don't know if other version of SunOS exhibit this bug  or not. And I have no idea *why* Oracle can't write software that  doesn't suck either. There is no *technical* reason for this hack  around to be *required*, ie:
   setString(int cn, String val ) {

      if ( val.length() > 4000 ) {
         // Convert to stream()
         setItem( cn, stream, sz );
      } else {
         setItem( cn, val )
      }

   }

I have noticed that classes12.zip is ever so slightly different   from classes111.zip but neither appears to do the above. FWIW I think classes111.zip is a slightly better driver.

>>This is the error message -
>>java.sql.SQLException: data size bigger than max size for this type
>
>Change it to this and you get:
> Works fine when I send 3753 characters.
> Get this error with 4163 characters, hmm I smell a
> 4K limit (or is that 8K w/Java?).
>
>java.sql.SQLException:
> ORA-01461: can bind a LONG value only for insert into a LONG column
>
>>I'm using Oracle thin driver for Oracle 8.0.5 - classes111b.zip
>>My schema looks like this -
>>Table Name - TestTable
>>Column Name - TestString
>>datatype - LONG
>
>It's an easy enough (code) tweek, if you have better luck I'd love
> to hear it.
>
>>The Oracle LONG datatype supports upto 2gb of char data,
>>so am not sure why I get this error.
>
>>Pls. let me know if there is any workaround, or there's some version
>>of Oracle driver which fixes this problem.
>>

 http://shaun.tancheff.com/       /?/                 -_-

  :-) shaun ;-] shaun_at_tancheff.com (Shaun Tancheff) .sigs freed Received on Tue Feb 08 2000 - 12:05:47 CST