5 Replies Latest reply on Nov 16, 2005 8:11 PM by Thomas Orr

    Native SQL Query Problem

    Thomas Orr Newbie

      For some reason the following code is driving me mad. All 2 posts in the forums that seem to relate do not have a conclusion that helps. Google searches also do not pull much up. Nonetheless, I am determined to understand why this doesn't work.

      Works:

       public User getUser(Integer userId) {
       User user = new User();
       //Query q = em.createNamedQuery("UserSelect");
       String sql = "select top 1 user_id, user_fname, user_lname from users";
       //String sql = "select user_id, user_fname, user_lname from users where user_id = ?";
       Query q = em.createNativeQuery(sql, "UserMapping");
       //q.setParameter(1,Integer.valueOf(userId));
       user = (User) q.getResultList().get(0);
       return user;
       }
      


      Doesn't work:
       public User getUser(Integer userId) {
       User user = new User();
       //Query q = em.createNamedQuery("UserSelect");
       //String sql = "select top 1 user_id, user_fname, user_lname from users";
       String sql = "select user_id, user_fname, user_lname from users where user_id = ?";
       Query q = em.createNativeQuery(sql, "UserMapping");
       q.setParameter(1,Integer.valueOf(userId));
       user = (User) q.getResultList().get(0);
       return user;
       }
      


      The trace:
      22:12:24,727 INFO [STDOUT] javax.ejb.EJBException: null; CausedByException is:
       Parameter 1 does not exist as a named parameter in [select user_id, user_fname, user_lname from users where user_id = ?]
      22:12:24,727 INFO [STDOUT] at org.jboss.ejb3.tx.Ejb3TxPolicy.handleExceptionInOurTx(Ejb3TxPolicy.java:46)
      22:12:24,727 INFO [STDOUT] at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:70)
      22:12:24,727 INFO [STDOUT] at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:134)
      22:12:24,727 INFO [STDOUT] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
      22:12:24,727 INFO [STDOUT] at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:61)
      22:12:24,727 INFO [STDOUT] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
      22:12:24,727 INFO [STDOUT] at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:39)
      ...
      


      All the documentation that I've read indicates that this would work:
      http://docs.jboss.org/ejb3/app-server/HibernateEntityManager/reference/en/html_single/#queryhql-from
      http://www.hibernate.org/hib_docs/entitymanager/reference/en/html/query_native.html#d0e2693

      Any and all assistance is really appreciated.