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

Here is the stored procedure in question:

CREATE FUNCTION athlete_add(IN new_email TEXT, IN fname TEXT, IN lname
TEXT, IN pw TEXT, IN sex TEXT, IN bdate DATE)
RETURNS BOOLEAN AS $$
BEGIN
INSERT INTO
athlete (email, first_name, last_name, password, gender, dob)
VALUES
(new_email, fname, lname, pw, sex, bdate);

IF FOUND THEN
RETURN TRUE;
END IF;

RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

As an aside, how do most people name their IN parameters so as not to
have them conflict with actual column names?

Thanks,

-- Matt

On Thu, Feb 18, 2010 at 12:34 AM, Kris Jurka <books(at)ejurka(dot)com> wrote:
>
>
> 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
>