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

Re: uuid values as parameters

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 (view raw or flat)
Thread:
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

In response to

Responses

pgsql-jdbc by date

Next:From: James William PyeDate: 2010-08-28 18:37:38
Subject: Re: [HACKERS] Trouble with COPY IN
Previous:From: vtkstefDate: 2010-08-19 16:10:01
Subject: uuid values as parameters

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