Re: Fwd: Query parameter types not recognized

Hmmm... I didn't know PostgreSQL had a facility for query logging and
debugging of parameters to a logfile. Thought I had to execute a describe
or something like that. Thanks, I'll try it to see what's happening!

2017-02-10 16:57 GMT-05:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 02/10/2017 01:51 PM, Roberto Balarezo wrote:
>
>> Hi,
>>
>> The parameter defaultDueDate is a java.sql.Date object, an actual Date.
>> When I run the query with the value in it, it works:
>> ```sql
>> db=> select COALESCE(duedate, date '2017-02-01' + 1) from invoices order
>> by duedate desc;
>> coalesce
>> ---------------------
>> 2017-02-02 00:00:00
>> 2017-02-02 00:00:00
>> 2016-11-14 00:00:00
>> 2017-02-10 00:00:00
>> 2017-02-02 00:00:00
>> 2017-02-13 00:00:00
>> 2017-02-02 00:00:00
>> 2017-02-02 00:00:00
>> ```
>>
>> But when I send it as a parameter, it ignores it and seems to think the
>> expression is of type interger.
>>
>
> Which would indicate to me that is what is being passed in the parameter.
> If I would guess, from information here:
>
> https://docs.oracle.com/javase/7/docs/api/java/sql/Date.html
>
> milliseconds since January 1, 1970 00:00:00.000 GMT.
>
> Turn on/up logging in Postgres and run a query with that java.sql.Date
> object. I am betting that what you will see in the logs is an integer.
>
>
>
>> 2017-02-10 16:32 GMT-05:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
>>
>>
>> On 02/10/2017 07:17 AM, Roberto Balarezo wrote:
>>
>> Hi, I would like to know why this is happening and some advice
>> if there
>> is a way to solve this problem:
>>
>> I have a query like this:
>>
>> |select COALESCE(duedate, ? + 1) from invoices order by duedate
>> desc
>> limit 10; |
>>
>>
>> What is the 1 in ? + 1 supposed to represent?
>>
>>
>> where ? is a query parameter. I’m using JDBC to connect to the
>> database,
>> and sending parameters like this:
>>
>> |query.setDate(1, defaultDueDate); |
>>
>> Where defaultDueDate is a java.sql.Date object. However, when I
>> try to
>> execute the query, I get this error:
>>
>> |org.postgresql.util.PSQLException: ERROR: COALESCE types
>> timestamp
>> without time zone and integer cannot be matched |
>>
>>
>> So what is the actual value of defaultDueDate?
>>
>> Looks like it is an integer from the ERROR message.
>>
>> Might want to look in the Postgres logs to see if they show anything
>> that might help.
>>
>>
>> Why is it inferring that the type is integer, when I send it as
>> Date??
>>
>>
>> I don't use Java, but I did find the below, don't know if it helps?:
>>
>> https://jdbc.postgresql.org/documentation/94/escapes-datetime.html
>> <https://jdbc.postgresql.org/documentation/94/escapes-datetime.html>
>>
>>
>>
>> When I force the type using a cast, like this:
>>
>> |select COALESCE(duedate, CAST(? AS DATE) + 1) from invoices
>> order by
>> duedate desc limit 10; |
>>
>> I get this error:
>>
>> |org.postgresql.util.PSQLException: ERROR: could not determine
>> data type
>> of parameter $1 |
>>
>> If I’m telling PostgreSQL that the parameter is going to be a
>> Date, and
>> send through the driver a Date, why it is having trouble
>> determining the
>> datatype of the parameter??
>> What can I do to make it work?
>>
>> For reference, I’m using PostgreSQL 9.2.15 and JDBC driver
>> 9.4.1207.jre6.
>>
>> Thanks for your advice!
>>
>> ​
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>