Monday, April 03, 2006

Hibernate Named Parameters in SQL, No Escape

This past weekend, I was working on a SQL query that I was executing using Hibernate. Part of the query required two casts to one field. In PostgreSQL, you can write the cast as:

cast( cast( field as text ) as int )

Or, you can use a shorter notation:


If you've worked with Hibernate, you likely know that instead of using question marks as placeholders for parameters in SQL and HQL queries, you can use named parameters. These named parameters are of the form ":parametername".

By now, you know what probably I ran into. When I used the short-hand notation for type casts in PostgreSQL, Hibernate interpreted it as a named parameter. Instead of "field::text::int", I ended up with the database seeing "field?".

After reviewing the code in question, I could find no way for the colons to be escaped and not interpreted as a named parameter. Maybe I'm reading the code wrong. Regardless, features like these need to have escape sequences. Now my already complex SQL query is uglier than it had to be due to the nested cast calls.



Post a Comment

<< Home