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

On 02/10/2017 02:14 PM, Roberto Balarezo wrote:
> 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!

Start here:

https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html

Set up logging and then set :

log_statement = 'all'

This will generate a lot of logs so you will probably not want to keep
it that way.

A Python example:

In [6]: date.today()
Out[6]: datetime.date(2017, 2, 10)

In [7]: cur.execute('select %s', [date.today()])

 From Postgres log;

aklaver-2017-02-10 14:35:42.842 PST-0LOG:  statement: BEGIN
aklaver-2017-02-10 14:35:42.842 PST-0LOG:  statement: select
'2017-02-10'::date


>
> 2017-02-10 16:57 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.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
>     <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>
>         <mailto: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>
>
>         <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>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.com>>
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com