From: | Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> |
---|---|
To: | Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com> |
Cc: | Dave Cramer <pg(at)fastcrypt(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, List <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: "could not determine data type of parameter" with timestamp |
Date: | 2017-12-07 10:26:09 |
Message-ID: | CAG2M1fc69LNtP=ELJjkd1Q4zZM9knnHz4TfahezuCGRg4E68Kw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Thank you for this explanation. Of course, it makes sense.
It just means a lot of work to adapt our software.
Thanks and regards,
Rémi.
Le mer. 6 déc. 2017 à 14:49, Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>
a écrit :
> >So, if my parameter is null, I have no way to guess its type.
>
> TL;DR: always use typed nulls in JDBC; use consistent data types (avoid
> date/varchar flips) for the same sql text; use "?::timestamptz" for edge
> cases.
>
> >I tried ps.setNull(1, Types.VARCHAR) as a fallback
>
> Please, avoid that. It will backfire with both PostgreSQL and Oracle DB.
>
> 1) That's very common pitfall with SQL and JDBC.
> nulls are typed in SQL.
>
> Suppose you have a couple of procedures: "procedure test(v varchar)" and
> "procedure test(v number)".
>
> ps.setNull(1, Types.VARCHAR) and ps.setNull(1, Types.NUMERIC) should
> result in execution of _different_ procedures.
>
> JDBC driver has no way which one do you want to execute if you "just pass
> null".
>
> Unfortunately, we cannot go very strict there since everybody just assumes
> "date is pretty much the same thing as timestamp", so in case client passes
> "timestamp, date, etc", pgjdbc just passes "unknown type" formatted as full
> timestamp and assumes backend would parse and use accordingly. For
> instance, if just a date was expected, it would just ignore time/timezone
> part.
>
> That backfires for the case "select ?" when backend has no way to tell
> what was the "expected bind type". Of course it cannot identify datatype. I
> have no idea how that can be healed and I do think the current behaviour is
> sane.
>
> 2) On top of that, different datatypes might result in different execution
> plans, thus JDBC/DB has to adapt to "new datatypes" in case you flip types
> of bind parameters for the same SQL text back and forth.
> You can find a bit on that around slides 25-27:
> https://www.slideshare.net/VladimirSitnikv/postgresql-and-jdbc-striving-for-high-performance/25
>
>
>
> Vladimir
>
--
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2017-12-11 14:17:20 | Driver version 42.1.4 and Java 9 |
Previous Message | Vladimir Sitnikov | 2017-12-06 13:49:44 | Re: "could not determine data type of parameter" with timestamp |