Re: Java and Postgres aren't too happy - Mailing list pgsql-general

Okay, I fixed this, here's what I found out.

1. SMALLINT on the parameter's of a function must EXPLICITLY be cast to
from the SQL. e.g: SELECT fn_unit_ins_new_vehicle( ?, ?, ?, ?, ?,
?::smallint, ?::smallint, ?, ? );

Even IF you're using setObject( n, xxxxx, SQLTypes.SMALLINT ), why I'm
not sure, and can only assume it's a problem with the driver.

2. The Main exception I was worried about
SQL EXCEPTION "The table for java.math.BigInteger is not in the
database. Contact the DBA, as the database is in an inconsistent state."

Was actually caused by me, not setting the type on the setObject.
setObject( 10, vehicle.getUnitNumber() ) where unitNumber is a
java.math.BigInteger, whereas it now works that I'm using,
SQLTypes.BIGINT.

So there ends that.

H

On Wed, 2003-04-16 at 14:37, Hadley Willan wrote:
> SQL EXCEPTION "The table for java.math.BigInteger is not in the
> database. Contact the DBA, as the database is in an inconsistent state."
>
> I've just recieved this exception from my application while performing
> what I would view a routine call into a PLPSQL function.
>
> I'm using:
>     Postgresql 7.3.2,
>     Postgresql 7.3 JDBC 3 as a driver
>     and I've quickly tested on both jdk 1.4.1 and jdk 1.4.2.
>
>
> Earlier on I'd been trying to call my PLPGSQL function and got the
> following exception
>
> EARLIER SQL EXCEPTION "Function fn_unit_ins_new_vehicle(integer,
> integer, integer, integer, integer, integer, integer, "unknown",
> "unknown") does not exist. Unable to identify a function that satisfies
> the given argument types. You may need to add explicit typecasts.
>
> Strange? I thought to myself, none of my other Postgresql functions
> called through Java are need explicit type casts. I briefly played
> around with the bind parameters, and checked to ensure that I was using
> SQLTypes.BIGINT, and setObject. These are the parameters and bindings
>
> stmt.setObject(1, userId, SQLTypes.BIGINT);
> stmt.setObject(2, vehicle.getUnitNumber(), SQLTypes.BIGINT);
> stmt.setObject(3, vehicle.getUnitDataEntryStateType(), SQLTypes.BIGINT);
> stmt.setObject(4, vehicle.getBodyType().getId(), SQLTypes.BIGINT);
> stmt.setObject(5, vehicle.getMakeModel().getId(), SQLTypes.BIGINT);
> stmt.setObject(6, vehicle.getManufactureYear(), SQLTypes.SMALLINT);
> stmt.setObject(7, vehicle.getDoorCount(), SQLTypes.SMALLINT);
> stmt.setObject(8, vehicle.getChassisNumber(), SQLTypes.VARCHAR);
> stmt.setObject(9, vehicle.getBodyColour(), SQLTypes.VARCHAR);
>
> Normally I would execute "SELECT fn_unit_ins_new_vehicle( ?, ?, ?, ?, ?,
> ?, ?, ?, ? )"
>
> But, again this failed.
>
> So I then tried to explicitly cast the parameters,
>  "SELECT fn_unit_ins_new_vehicle( ?::bigint, ?::bigint, ?::bigint,
> ?::bigint, ?::bigint, ?::smallint, ?::smallint, ?::varhcar, ?::varchar);
>
> That's when the most current and fatal exception noted above occured.
> However! The function actually HAS run, and there is data in the db???
>
> Can anybody shed a bit more light on what to do now?
>
> Help!
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.