Skip site navigation (1) Skip section navigation (2)

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

From: list_usr(at)spacebox(dot)net
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Column is of type date but expression is of type text
Date: 2010-02-18 21:27:12
Message-ID: 1e11c2271002181327g52545a34u6a91c5b17cd1fdfd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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
>

In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2010-02-18 22:11:55
Subject: Re: Column is of type date but expression is of type text
Previous:From: Jason TesserDate: 2010-02-18 11:48:30
Subject: Re: Correct way to send a composite type to Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group