Skip site navigation (1) Skip section navigation (2)

Re: Fwd: Cannot pass null in Parameter in Query for ISNULL

From: "Maciek Sakrejda (msakrejd)" <msakrejd(at)cisco(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Bernard" <bht237(at)gmail(dot)com>
Cc: "PG-JDBC Mailing List" <pgsql-jdbc(at)postgresql(dot)org>, "Maciek Sakrejda" <msakrejda(at)truviso(dot)com>
Subject: Re: Fwd: Cannot pass null in Parameter in Query for ISNULL
Date: 2012-05-13 20:17:55
Message-ID: 238542D917511A45B6B8AA806E875E2508A8AB36@XMB-RCD-201.cisco.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
>If the server's parser tries to guess a type, that will simply move the
>pain from this case to other cases, namely those where the choice
really
>matters and it guesses wrong.

Well, the server already guesses parameter types, no? In a Parse
protocol message, any parameter type specification is optional. If you
leave it out, the server guesses for you (and tells you about it in the
ParameterDescription message). This is just about making the server
smarter.

JDBC spec or no, having the server process

"SELECT NULL IS NULL"

but balk at

"SELECT $1 IS NULL" ($1 = NULL)

is pretty silly. The type system is working against us here. I
understand that due to planning and so on, this leads through two very
different code paths, but I think the complaint is fundamentally sound.
Whether it's worth addressing is a separate issue, and there's also the
question of whether addressing it will resolve Bernard's complaint (I
decided not to pursue it last time because I felt it would not; maybe
I'm wrong).

>Having said that, it's interesting to wonder how much would break if
>setObject were to arbitrarily assume the data type is TEXT.

I take it you mean "assume TEXT if the parameter is a Java null?" If the
parameter is not null, the type system gives the driver enough
information to do some mapping based on the Java type of the parameter.

I tried this and one of the JDBC tests fails, but it may still be worth
considering. My (trivial) change is here:

https://github.com/deafbybeheading/pgjdbc/tree/null-parameter-type

The test that breaks is the following:

https://github.com/deafbybeheading/pgjdbc/blob/master/org/postgresql/tes
t/jdbc2/ArrayTest.java#L48

The code in the test is a simple NULL insert with no additional type
information. Should we avoid breaking that? I have no particularly
strong feelings there either way, but it indicates this won't be a
"free" change.

-Maciek

In response to

Responses

pgsql-jdbc by date

Next:From: LewDate: 2012-05-13 20:44:26
Subject: Re: Fwd: Cleanup patch: Change from Vector/Hashtable to ArrayList/HashMap
Previous:From: Radosław SmoguraDate: 2012-05-13 19:10:19
Subject: Re: Fwd: Postgres JDBC, WS and commit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group