Tuesday, February 9, 2010

Not all named parameters have been set

Problem : I am getting one exception while trying to execute a query using hibernate

org.hibernate.QueryException: Not all named parameters have been set: [] [from FunctionPointDO f where f.workPackage='work package : id1' and f.workPackageIdentifier='work : ref1' and f.startDate='03-Jan-2010' and f.goLiveDate='04-Feb-2010' and f.id!='1000100']
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:114)
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:101)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:70)
at com.bt.fp.server.Utils.Utils.listAndCast(Unknown Source)
at com.bt.fp.server.database.dao.FunctionPointDAO.isDuplicate(Unknown Source)
at com.bt.fp.server.AddUpdateFPData.doPost(Unknown Source)

It seems that this error is due to the “:” which is used as a value in f.workPackage='work package : id1' and f.workPackageIdentifier='work : ref1'. I checked on net and hibernate considers anything starting with colon (: ) as a variable, and here id1 and ref1 are treated as variable whose value is not set anywhere. This error comes only when colon has spaces around it.

How can I escape colon. As these values are entered by users, I don’t have any control over whatever input they provide.


  1. It is solved as below:

    i was setting values in query itself like

    String hql = "from FunctionPointDO f where f.workPackage='"+workPackage+"' and f.workPackageIdentifier='"+workPackageId+"' and f.startDate='"+startDate+"' and f.goLiveDate='"+goLiveDate+"' and f.id!='"+sequenceNo+"'";

    so when a value comes with colon in it, it is considered as hibernate variable.

    So i myself used variables in query and set the value of those variables later

    String hql = "from FunctionPointDO f where f.workPackage=:parWorkPackage and f.workPackageIdentifier=:parWorkPackageId and f.startDate =:parStartDate and f.goLiveDate=:parGoLiveDate and f.id!=:parSequenceNo";

    Query query = session.createQuery(hql);

    query.setParameter("parWorkPackage", workPackage);

    query.setParameter("parWorkPackageId", workPackageId);

    query.setParameter("parStartDate", startDate);

    query.setParameter("parGoLiveDate", goLiveDate);

    query.setParameter("parSequenceNo", sequenceNo);

    This solved the problem as values are not set directly in query but later while preparing statement as in case of PreparedStatement in JDBC

    The query should not contain colon, if it contains a colon, SQL treats it as a variable

  2. Basically, the problem was the prepare a query with values having colon in it..

    For this one must used PreparedStatement of JDBC, rather than creating a String query.