2 Replies Latest reply on Jun 11, 2007 9:26 AM by wayne baylor

    error in your SQL syntax

    Alberto Torres Newbie

      Hello,

      I,am trying to execute a query in a stateless bean, with jboss seam

      The query is

      List existing = em.createQuery("select username from Users")
      .getResultList();

      and I get this error

      You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.users users0_' at line 1

      My entity bean is defined as

      @Entity
      @Name ("users")
      @Scope (SESSION)
      @Table(name = "users")
      public class Users implements java.io.Serializable {

      private static final long serialVersionUID = 1881413500711441951L;

      private String username;
      private String password;
      .........

      What is wrong??
      I have configue my app with jboss seam
      seam setup
      seam new-project

      My database is mysql 4.1.21 and if I connect by jdbc all its ok.

      thank you

        • 1. Re: error in your SQL syntax
          mazz Master

          Look at the name of the table its using:

          '.users users0_'

          See the "." right before users? I don't know for sure, but it looks to me like that is missing the schema name (not sure what MySQL calls it - database name? username?)

          You probably just have to configure your persistence.xml a little more to tell Hibernate the schema name.

          Just a thought - I have no idea if what I just said is right :) But for sure it seems like ".users" isn't right - table names don't have dots as their first character.

          • 2. Re: error in your SQL syntax
            wayne baylor Apprentice

            You can't use "normal" SQL in the createQuery method, you have to use JPA QL.

            So, try this:

            List<User> users = em.createQuery("from "+User.class).getResultList();


            You'll get a list of User objects and you can filter out the usernames from that.