Re: Column is of type date but expression is of type text

[Kris Jurka <books(at)ejurka(dot)com>]

On Wed, 17 Feb 2010, list_usr(at)spacebox(dot)net wrote:

> I'm trying to call a stored procedure from a Java app; the stored procedure
> just makes an insert on a table and returns true or false. It seems there's
> a problem when a java.sql.Date type parameter is passed from Java to the
> PostgreSQL stored procedure, and then to the insert: LOG: execute <unnamed>:
> select * from athlete.create_athlete($1,$2,$3,$4,$5,$6,$7) as result DETAIL:
> parameters: $1 = '', $2 = 'foo(at)bar(dot)com', $3 = 'Joe', $4 = 'Blow', $5 =
> 'foobar', $6 = 'M', $7 = '1979-03-22 -04:00:00' ERROR: column "dob" is of
> type date but expression is of type text at character 122 HINT: You will
> need to rewrite or cast the expression. QUERY: INSERT INTO athlete.athlete
> (email, first_name, last_name, password, gender, dob) VALUES ( $1 , $2 , $3
> , $4 , $5 , $6 ) CONTEXT: PL/pgSQL function "create_athlete" line 2 at SQL
> statement STATEMENT: select * from
> athlete.create_athlete($1,$2,$3,$4,$5,$6,$7) as result The Java code, in
> brief: registerQuery = "{? = call athlete.create_athlete(?,?,?,?,?,?)}";
> .... CallableStatement cs = conn.prepareCall(registerQuery);
> cs.registerOutParameter(1, Types.BOOLEAN); cs.setString(2, email); ....
> createAthlete.setDate(7, birthdate); // birthdate is of type java.sql.Date
> I've asked elsewhere, and it seems my Java code is correct for a stored
> procedure that returns a single value. However I'm still uncertain because
> the logs show 7 parameters in the call to create_athlete() when there should
> only be 6 - if parameters $1-$6 in the call to create_athlete() are the
> parameters passed to the insert statement, then the dob parameter would be
> mismatched with the gender parameter. Are parameters $2-$7 actually passed
> to the insert? If yes, where am I going wrong?

Without seeing what your function is doing it's tough to tell where things 
are going wrong.  Perhaps you've switched some parameters around from the 
function arguments to the insert call?  The JDBC driver rearranges the 
output parameter to be in the argument list for it's own internal 
convenience.  This is OK and doesn't affect the parameter numbering or 
the calling of the function.

Kris Jurka