Column is of type date but expression is of type text

From: list_usr(at)spacebox(dot)net
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Column is of type date but expression is of type text
Date: 2010-02-18 02:14:50
Message-ID: 1e11c2271002171814i704d31f4oca1d819dc5a02103@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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?

Thanks.

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2010-02-18 05:17:29 Re: Correct way to send a composite type to Postgres
Previous Message Jason Tesser 2010-02-16 11:13:25 Correct way to send a composite type to Postgres