Thread: Column is of type date but expression is of type text
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@bar.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.
On Wed, 17 Feb 2010, list_usr@spacebox.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@bar.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
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@ejurka.com> wrote:
>
>
> On Wed, 17 Feb 2010, list_usr@spacebox.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@bar.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
someparameters around from the function arguments to the insert call? The JDBC driver rearranges the output parameter
tobe in the argument list for it's own internal convenience. This is OK and doesn't affect the parameter numbering or
thecalling of the function.
>
> Kris Jurka
>
On Thu, 18 Feb 2010, list_usr@spacebox.net wrote: > Here is the stored procedure in question: The attached testcase works fine for me. What driver and server versions do you have? > > As an aside, how do most people name their IN parameters so as not to > have them conflict with actual column names? > Some people prefix them with v_ to indicate that they are variables. Kris Jurka
Attachment
Re: version, good question - I have both postgresql-8.4-701.jdbc3.jar
and postgresql-8.4-701.jdbc4.jar in my Apache Tomcat 5.5 common/lib
directory. Would that cause a problem?
I'm using PostgreSQL 8.4. Java 1.6.
Your example code worked with both jdbc3 and jdbc4, but my code still
isn't working.
I thought it might have to do with the way I instantiate the date, but
I tried that in your example code and it worked fine:
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
birthdate.setTime(format.parse(request.getParameter("birthdate")).getTime());
// getParameter("birthdate") would return "1978-02-13" for example
In my app the PostgreSQL logs show the birthdate parameter as
"1978-02-13 -04:00:00", with the same code in your test program the
time is omitted. Would that make a difference?
On Thu, Feb 18, 2010 at 5:11 PM, Kris Jurka <books@ejurka.com> wrote:
>
>
> On Thu, 18 Feb 2010, list_usr@spacebox.net wrote:
>
>> Here is the stored procedure in question:
>
> The attached testcase works fine for me. What driver and server versions do
> you have?
>
>>
>> As an aside, how do most people name their IN parameters so as not to
>> have them conflict with actual column names?
>>
>
> Some people prefix them with v_ to indicate that they are variables.
>
> Kris Jurka
Would the problem be that the timezone is included with the date
passed in the stored procedure parameter?
I don't understand why the timezone shows up in my J2EE app but not in
the test code you sent.
On Fri, Feb 19, 2010 at 8:21 PM, <list_usr@spacebox.net> wrote:
> Re: version, good question - I have both postgresql-8.4-701.jdbc3.jar
> and postgresql-8.4-701.jdbc4.jar in my Apache Tomcat 5.5 common/lib
> directory. Would that cause a problem?
>
> I'm using PostgreSQL 8.4. Java 1.6.
>
> Your example code worked with both jdbc3 and jdbc4, but my code still
> isn't working.
>
> I thought it might have to do with the way I instantiate the date, but
> I tried that in your example code and it worked fine:
>
> SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
> birthdate.setTime(format.parse(request.getParameter("birthdate")).getTime());
> // getParameter("birthdate") would return "1978-02-13" for example
>
> In my app the PostgreSQL logs show the birthdate parameter as
> "1978-02-13 -04:00:00", with the same code in your test program the
> time is omitted. Would that make a difference?
>
>
> On Thu, Feb 18, 2010 at 5:11 PM, Kris Jurka <books@ejurka.com> wrote:
>>
>>
>> On Thu, 18 Feb 2010, list_usr@spacebox.net wrote:
>>
>>> Here is the stored procedure in question:
>>
>> The attached testcase works fine for me. What driver and server versions do
>> you have?
>>
>>>
>>> As an aside, how do most people name their IN parameters so as not to
>>> have them conflict with actual column names?
>>>
>>
>> Some people prefix them with v_ to indicate that they are variables.
>>
>> Kris Jurka
>
On Fri, 19 Feb 2010, list_usr@spacebox.net wrote: > Re: version, good question - I have both postgresql-8.4-701.jdbc3.jar > and postgresql-8.4-701.jdbc4.jar in my Apache Tomcat 5.5 common/lib > directory. Would that cause a problem? > > I'm using PostgreSQL 8.4. Java 1.6. > > Your example code worked with both jdbc3 and jdbc4, but my code still > isn't working. Well those versions are all recent enough not to have any problems. If my code works and yours doesn't it's likely you're not telling us something about your code. If you can create a self-contained testcase like I did then I'd be happy to look at it, but it doesn't look like a driver problem. Kris Jurka
Kris Jurka wrote: > If my code works and yours doesn't it's likely you're not telling us > something about your code. If you can create a self-contained testcase > like I did then I'd be happy to look at it, but it doesn't look like a > driver problem. For advice on creating examples for Usenet or other support environments, see <http://sscce.org/> -- Lew
So I don't know what happened.. but I dropped the database and
reloaded it and now it works. I had made some small updates to the
stored procedure but had been recreating it (CREATE OR REPLACE
FUNCTION) with the updates - should I have been restarting Postgres
after recreating the stored procedure?
I think the issue was the ordering of the parameters - after your
initial answer to my question I updated the stored procedure so the
ordering of the parameters matched the order that they appeared in the
insert statement. I recreated the stored procedure and continued to
encounter the problem which led me to believe that wasn't the issue.
Anyhow, after recreating the database it's now working.
Thanks for all your help!
-- Matt
On Sat, Feb 20, 2010 at 2:21 PM, Kris Jurka <books@ejurka.com> wrote:
>
>
> On Fri, 19 Feb 2010, list_usr@spacebox.net wrote:
>
>> Re: version, good question - I have both postgresql-8.4-701.jdbc3.jar
>> and postgresql-8.4-701.jdbc4.jar in my Apache Tomcat 5.5 common/lib
>> directory. Would that cause a problem?
>>
>> I'm using PostgreSQL 8.4. Java 1.6.
>>
>> Your example code worked with both jdbc3 and jdbc4, but my code still
>> isn't working.
>
> Well those versions are all recent enough not to have any problems. If my
> code works and yours doesn't it's likely you're not telling us something
> about your code. If you can create a self-contained testcase like I did
> then I'd be happy to look at it, but it doesn't look like a driver problem.
>
> Kris Jurka
>
list_usr@spacebox.net writes:
> I think the issue was the ordering of the parameters - after your
> initial answer to my question I updated the stored procedure so the
> ordering of the parameters matched the order that they appeared in the
> insert statement. I recreated the stored procedure and continued to
> encounter the problem which led me to believe that wasn't the issue.
Note that two functions with the same names and differently ordered
parameter lists are different objects to Postgres. I think maybe you
forgot to drop the first version and it was still getting invoked.
regards, tom lane
But wouldn't the stored procedure with parameters matching the order of the parameters in the call be the stored procedure to be called? On Sun, Feb 21, 2010 at 1:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > list_usr@spacebox.net writes: >> I think the issue was the ordering of the parameters - after your >> initial answer to my question I updated the stored procedure so the >> ordering of the parameters matched the order that they appeared in the >> insert statement. I recreated the stored procedure and continued to >> encounter the problem which led me to believe that wasn't the issue. > > Note that two functions with the same names and differently ordered > parameter lists are different objects to Postgres. I think maybe you > forgot to drop the first version and it was still getting invoked. > > regards, tom lane >
list_usr@spacebox.net writes:
> But wouldn't the stored procedure with parameters matching the order
> of the parameters in the call be the stored procedure to be called?
[ shrug... ] If you want an exact diagnosis of what happened then
you'd need to actually show us exactly what happened. I was merely
suggesting a possible contributing factor.
regards, tom lane