Обсуждение: uuid values as parameters
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
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
* 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@bfk.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