Re: [GENERAL] Fwd: Query parameter types not recognized - Mailing list pgsql-general

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.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com