Not all named parameters have been set in specific conditions

Question

I have using then following query in my Hibernate i.e createSQLQuery

SELECT to_char(dd, 'Mon YYYY') FROM generate_series('2013-01-01'::date, date_trunc('month', now()), '1 month') as dd 

which produces output when run in PostgreSQL as

1. "Jan 2013" "Feb 2013" . . "Feb 2014"

However while using it through createSQLQuery as below , it throws "org.hibernate.QueryException: Not all named parameters have been set: [:date]"

Please note that date has been used like '2013-01-01'::date

try{
            session = HibernateSessionFactory.getSession(); 
            tx = session.beginTransaction();

            Query query = session.createSQLQuery("SELECT to_char(dd, 'Mon YYYY') FROM generate_series('2013-01-01'::date, date_trunc('month', now()), '1 month') as dd");
            monthList = new ArrayList<String>();            
            monthList = query.list();
            tx.commit();
        }

Please suggest

Answer 1

Try to replace the Postgres-specific (::) type cast with a SQL-standard one - CAST ('2013-01-01' AS DATE) (or DATE '2013-01-01').

Answer 2

If you have : characters in your SQL String, the parser will treat them as named parameters unless you escape them. The alternative CAST syntax suggested by Milen A. Radev is nice for that particular case, but there are cases where there is no trivial alternative syntax, e.g. array slicing:

SELECT count(*), 
    array_to_string((array_agg(id order by whenCreated desc))[1\\:20], ',') ids 
FROM …

Different versions of the parser org.hibernate.engine.query.spi.ParameterParser allow different escaping of : characters, but they all seem to recognise \:, so that's what I've used. Some versions also allow :: to represent an escaped :, but I've not tested that.

Note that as usual, you'll also need to escape your \ in your Java String, so the escape sequence is \\: for any : character that you don't want to represent a named parameter.