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@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>:
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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>>:
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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
