Lists: | pgsql-jdbc |
---|
From: | vtkstef <vtkstef(at)gmail(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | uuid values as parameters |
Date: | 2010-08-19 16:10:01 |
Message-ID: | 6922363a-ac17-49da-a5bd-ac5d03653dde@x42g2000yqx.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Hi,
I have a simple table foo with a column guid of data type uuid
if I execute the following query
select guid from foo where guid = '849b3d72-1400-44f1-
a965-1f4648475589'
the query returns fine
but if I parameterize the query as this
select guid from foo where guid = ?
and I set the the parameter using setString(1, '849b3d72-1400-44f1-
a965-1f4648475589')
the query bombs with the following:
[Error Code: 0, SQL State: 42883] ERROR: operator does not exist:
uuid = character varying
I understand I need to use setObject(), or that I could explicitly
cast it (select guid from foo where guid = ?::uuid) but I don't
understand why harcoding a string works, and sending a string
parameter does not
Ciao
Stefano
From: | Lew <noone(at)lewscanon(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: uuid values as parameters |
Date: | 2010-08-22 15:38:36 |
Message-ID: | i4rg9h$j8l$1@news.albasani.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
On 08/19/2010 12:10 PM, vtkstef wrote:
> Hi,
>
> I have a simple table foo with a column guid of data type uuid
>
> if I execute the following query
>
> select guid from foo where guid = '849b3d72-1400-44f1-
> a965-1f4648475589'
>
> the query returns fine
>
> but if I parameterize the query as this
>
> select guid from foo where guid = ?
>
> and I set the the parameter using setString(1, '849b3d72-1400-44f1-
> a965-1f4648475589')
>
> the query bombs with the following:
>
> [Error Code: 0, SQL State: 42883] ERROR: operator does not exist:
> uuid = character varying
>
> I understand I need to use setObject(), or that I could explicitly
> cast it (select guid from foo where guid = ?::uuid) but I don't
> understand why harcoding a string works, and sending a string
> parameter does not
When you give the SQL parser a string literal, it knows that it might have to
convert to the column type, and at that can only do so for column types that
define a conversion for string literals. When you give the parser a parameter
to a prepared statement, you are adding the feature of strong type safety.
You are actually depending on the parser to reject inputs of the wrong type.
For it to accept a string value for the parameter would be a violation of that
trust.
--
Lew
From: | Florian Weimer <fweimer(at)bfk(dot)de> |
---|---|
To: | Lew <noone(at)lewscanon(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: uuid values as parameters |
Date: | 2010-09-06 12:09:25 |
Message-ID: | 82eid76pl6.fsf@mid.bfk.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
* Lew:
> When you give the SQL parser a string literal, it knows that it might
> have to convert to the column type, and at that can only do so for
> column types that define a conversion for string literals. When you
> give the parser a parameter to a prepared statement, you are adding
> the feature of strong type safety. You are actually depending on the
> parser to reject inputs of the wrong type. For it to accept a string
> value for the parameter would be a violation of that trust.
You usually can work around that by adding a type cast, as in
"?::uuid".
To get behavior which more closely matches those of other PostgreSQL
bindings, add "?stringtype=unspecified" to the JDBC URL.
--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99