Saying position beyond number of declared ordinal parameters

Question

I want to perform a native/raw mysql query using hibernate, I have this:

 sessionFactory.getCurrentSession().createSQLQuery(
       "update table1 set someCounter = someCounter + 1 where id = ?")
     .setParameter(1, someId)
     .executeUpdate();

I'm getting the error:

threw exception [Request processing failed; nested exception is
      org.hibernate.QueryParameterException: Position beyond number of declared ordinal
      parameters. Remember that ordinal parameters are 1-based! Position: 2] 
      with root cause
      org.hibernate.QueryParameterException: Position beyond number of declared ordinal
      parameters. Remember that ordinal parameters are 1-based! Position: 2

What's wrong here?

Yogendra Singh · Accepted Answer · 2012-10-26T01:58:57.723

20

Use index as 0 since the parameter index start from 0.

sessionFactory.getCurrentSession()
  .createSQLQuery("update table1 set someCounter = someCounter + 1 where id = ?")
  .setParameter(0, someId)
  .executeUpdate();

Since you are using Hibernate, you can use the named parameter as well i.e.

sessionFactory.getCurrentSession()
  .createSQLQuery("update table1 set someCounter = someCounter + 1 where id = :id")
  .setParameter("id", someId)
  .executeUpdate();

edited Oct 26 '12 at 01:58

answered Oct 26 '12 at 01:44

Yogendra Singh

33,927
6
63
73

2

The use of named parameters applies to the Java Persistence query language, and is not defined for native queries. – Kevin Bowersox Oct 26 '12 at 01:45
My Source: http://stackoverflow.com/questions/3144235/jpa-hibernate-native-queries-do-not-recognize-parameters – Kevin Bowersox Oct 26 '12 at 01:48
1

@kmb385: That is true for JPA. Please read this for Hibernate: [**Hibernate Native SQL**](http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#d0e13930) --> `Native SQL queries support positional as well as named parameters`. This supports the named parameter. Please look at the very first example. – Yogendra Singh Oct 26 '12 at 01:51
4

But the exception message itself is saying that it's 1-based. – Bhesh Gurung Oct 26 '12 at 02:06
1

That is in conflict with the JavaDoc, checkout the Java Doc http://docs.jboss.org/hibernate/orm/4.1/javadocs/org/hibernate/Query.html#setParameter%28int,%20java.lang.Object%29 – Kevin Bowersox Oct 26 '12 at 02:08
@YogendraSingh just read yourlink, I was getting Hibernate and JPA confused. – Kevin Bowersox Oct 26 '12 at 02:16
@kmb385: Right next to that, `Query setParameter(String name, Object val) throws HibernateException` is also there. – Yogendra Singh Oct 26 '12 at 02:34
@BheshGurung: Not sure if I understand your input. Either go through the URL or the javadoc. It says clearly, -`-the position of the parameter in the query string, numbered from **0**.` – Yogendra Singh Oct 26 '12 at 02:37
@YogendraSingh: It's correct on according to the docs. But I meant the exception message in the OP. – Bhesh Gurung Oct 26 '12 at 02:42
@BheshGurung I know thats what I'm saying the javadoc and the exception conflict each other. I guess the source code would be the only way to solve this, not sure if I want to get that indepth. – Kevin Bowersox Oct 26 '12 at 11:36
@BheshGurung I was curious and decided to checkout the source code, it appears that it is zero based. Please see my updated post. – Kevin Bowersox Oct 26 '12 at 11:48
http://shervinasgari.blogspot.com/2011/01/remember-that-ordinal-parameters-are-1.html may be helpful – rogerdpack Dec 20 '13 at 21:17
I googled the warning "Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead." and then ran into this problem. Best to use named parameters to avoid this confusion. – Adam Sep 24 '15 at 12:16

Kevin Bowersox · Answer 2 · 2012-10-26T11:47:45.740

The parameters use a zero based index. Try:

 sessionFactory.getCurrentSession().createSQLQuery("update table1 set someCounter = someCounter + 1 where id = ?")
                .setParameter(0, someId)
                .executeUpdate();

The current Hibernate JavaDocs also specify that setPosition relies on zero based indexing for positional parameters. http://docs.jboss.org/hibernate/orm/4.1/javadocs/org/hibernate/Query.html#setParameter%28int,%20java.lang.Object%29

setParameter

Query setParameter(int position,
                   Object val)
                   throws HibernateException

    Bind a value to a JDBC-style query parameter. The Hibernate type of the parameter is first detected via the usage/position in the query and if not sufficient secondly guessed from the class of the given object.

    Parameters:
        position - the position of the parameter in the query string, numbered from 0.
        val - the non-null parameter value 
    Throws:
        HibernateException - if no type could be determined

Check the out the parameters section of this document: https://access.redhat.com/knowledge/docs/en-US/JBoss_Enterprise_Web_Server/1.0/html/Hibernate_Core_Reference_Guide/querysql.html#id3043464

There has been some discussion regarding whether the setParameter() method is zero based or one based. This confusion is due to the exception received by the poster noting that parameters are 1 based, while the JavaDoc states that they are zero based. I analyzed the Hibernate source code and believe that they are in fact zero based. Assuming that I checked the right class, the underlying code uses a list to store the parameter bind values, which would imply that the setParameter method is in fact zero based. Checkout the source code for yourself: https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/internal/AbstractQueryImpl.java

score 0 · Answer 3 · answered Oct 26 '12 at 04:13

Positional parameters start from 0 not 1

Native SQL queries support positional as well as named parameters:

update your query by passing 0 instead of 1 in setParameter(1, someId)

sessionFactory.getCurrentSession().createSQLQuery("update table1 set someCounter = someCounter + 1 where id = ?")
                .setParameter(0, someId)
                .executeUpdate();

resource parameters

score 0 · Answer 4 · answered Aug 16 '19 at 15:37

To those of you who couldn't resolve based on the solution above, it seems like at times hibernate (depending on the version, I'm using 3) actually gives you the wrong error, for example the issue could be present if you make a syntax error in the hibernate q language:

find("from Student s where s.id = :id")

change it to this:

find("from Student s where s.id = ?")

actually resolved that issue. I guess my main point is that you should also look at your syntax for the issue bc it seems hibernate can incorrectly label this exception.

Saying position beyond number of declared ordinal parameters

4 Answers4