TORQUE: LONGVARBINARY and Oracle

[Bill Schneider <bs...@vecna.com>]

I get this error when inserting a row into a table with Oracle and a 
LONGVARBINARY column:

java.sql.SQLException: ORA-01483: invalid length for DATE or NUMBER bind 
variable

It turns out that the order the columns appear in the PreparedStatement 
  makes a difference with Oracle.  Any LONG column must be the *LAST* 
bind variable in the INSERT statement with Oracle.  This is only 
relevant if the size of the object is greater than 4000 bytes:

stmt.setBytes(1, new byte[4000]) works fine and
stmt.setBytes(1, new byte[4001]) breaks.

I tried to isolate this problem and was incredibly frustrated as I found 
that my test programs inserting into two column tables (id INTEGER, data 
LONG RAW) seemed to work fine, same driver, same properties, same Torque 
JAR files.  I only discovered the error when I started trying to 
reproduce directly at the Oracle level, by trying to figure out exactly 
what kind of statement could generate this error.  (On insertion, 
Village is not very helpful because it hides the generated SQL.)

The workaround I used was to override the "save(DBConnection)" method in 
the particular object, so I could control the SQL "INSERT" statement.

Anyone else experience this or something similar?

Also, has there been any debate on this list on the merits of LONG RAW 
vs. BLOBs (or BYTEA vs. OID on postgres)?

-- Bill


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: TORQUE: LONGVARBINARY and Oracle

[Bill Schneider <bs...@vecna.com>]

> If you put the column as last in your schema.xml file it should be the
> last
> column in the insert statement.  I could easily be wrong on that though

Sometimes it works--I did generate a table that worked, but it only had 
two columns (id, data).  I tried it with several more columns and it didn't.

I think the take-home lesson here is, large objects are not very 
portable, and you should try to isolate them by themselves to the 
greatest extent possible.

I think part of point of OID and BLOB, etc., vs BYTEA and LONG RAW, is 
to make this isolation implicit, 'cause they both store only object 
locator or id in the table itself and store the object contents 
somewhere else.  But, as you point out, this hiding complicates working 
with JDBC.

-- Bill


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: TORQUE: LONGVARBINARY and Oracle

[John McNally <jm...@collab.net>]

Bill Schneider wrote:
> 
> I get this error when inserting a row into a table with Oracle and a
> LONGVARBINARY column:
> 
> java.sql.SQLException: ORA-01483: invalid length for DATE or NUMBER bind
> variable
> 
> It turns out that the order the columns appear in the PreparedStatement
>   makes a difference with Oracle.  Any LONG column must be the *LAST*
> bind variable in the INSERT statement with Oracle.  This is only
> relevant if the size of the object is greater than 4000 bytes:
> 
> stmt.setBytes(1, new byte[4000]) works fine and
> stmt.setBytes(1, new byte[4001]) breaks.
> 
> I tried to isolate this problem and was incredibly frustrated as I found
> that my test programs inserting into two column tables (id INTEGER, data
> LONG RAW) seemed to work fine, same driver, same properties, same Torque
> JAR files.  I only discovered the error when I started trying to
> reproduce directly at the Oracle level, by trying to figure out exactly
> what kind of statement could generate this error.  (On insertion,
> Village is not very helpful because it hides the generated SQL.)
> 
> The workaround I used was to override the "save(DBConnection)" method in
> the particular object, so I could control the SQL "INSERT" statement.

If you put the column as last in your schema.xml file it should be the
last
column in the insert statement.  I could easily be wrong on that though.

> 
> Anyone else experience this or something similar?
> 
> Also, has there been any debate on this list on the merits of LONG RAW
> vs. BLOBs (or BYTEA vs. OID on postgres)?
> 

bytea has shown to be easier to deal with vs. OID as far as torque
goes.  Other than that there has not been much discussion on their
tradeoffs.

john mcnally

--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>