Unknown column 'id' in 'field list'

[peace76]

Hi. I have a strange problem.

I get this error ( Unknown column 'id' in 'field list' ). when Im loading an image
that i uploaded and saved into mysql db as byts[].

When I google this I found out this can be a bug.

*entity* // EventPictures

@Entity
@Table(name = "event_pictures")
@NamedQueries({
    @NamedQuery(name = "EventPictures.customByEvent", query = "SELECT e FROM EventPictures e WHERE e.events = :events")})

public class EventPictures implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = " id")
    private Integer id;
    .........
    @JoinColumn(name = "events_id", referencedColumnName = "id")
    @ManyToOne(optional = false)
    private Events events;

     public EventPictures() {
      }

     public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

*session* // EventPicturesFacade.java

@Stateless
public class EventPicturesFacade extends AbstractFacade<EventPictures> {
    @PersistenceContext(unitName = "socialeventPU")
    private EntityManager em;

    protected EntityManager getEntityManager() {
        return em;
    }

    public EventPicturesFacade() {
        super(EventPictures.class);
    }

   public List getImage(Events events) {
        Query q = em.createNamedQuery("EventPictures.customByEvent");
        q.setParameter("events", events);
        try {
            return (List) q.getResultList();
        } catch (NoResultException nr) {
            return null;
        }
    }

}

*servlet* // controller.java

else if (!userPath.isEmpty() && userPath.equals("/handlethisevent")) {

            String eventId = request.getParameter("eventid");

            Events event = eventsFacade.find(Integer.parseInt(eventId));

            // get saved posts
            List notes = eventWallFacade.readNotes(event);

            // get pictures
            List image = (List) eventPictureFacade.getImage(event); // <--- this failes with the error ( Unknown column 'id' in 'field list' )

            request.setAttribute("images", image);
            request.setAttribute("event", event);
            request.setAttribute("notes", notes);

            userPath = "/handlethisevent";

*error* // glassfish serverlog

Call: SELECT id, description, image, date_uploaded, events_id, users_id FROM event_pictures WHERE ( id = ?)
bind => [16]
Error Code: 1054
Query: ReadObjectQuery(referenceClass=EventPictures sql="SELECT id, description, image, date_uploaded, events_id, users_id FROM event_pictures WHERE ( id = ?)")
at org.eclipse.persistence.exceptions.DatabaseExcepti on.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.Da tabaseAccessor.basicExecuteCall(DatabaseAccessor.j ava:687)
at org.eclipse.persistence.internal.databaseaccess.Da tabaseAccessor.executeCall(DatabaseAccessor.java:5 30)
at org.eclipse.persistence.sessions.server.ServerSess ion.executeCall(ServerSession.java:529)
..........

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: Unknown column 'id' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:27)

Can anyone see if I done something wrong. I have 5 more mysql tables that have the same layout as above so it should work.:confused:

[Eranga]

Are you sure that your sql query is valid?

[peace76]

After hours of research and one broken phone and a lot of yelling and banging my head in the wall I found out what error this is.

It turned out that when creating a column with "blob" attribute. mysql some times dont create all column even if they shows. I had to remove the column id from inside the IDE NetBean and re create it from NetBeans to make it work.

Before that I tryed to recreate the database from mysql workbench with no success.

Problem solved.

[Eranga]

Are you creating all columns dynamically?

[peace76]

Are you creating all columns dynamically?

No just the column that failed. in this case the "id"

[Eranga]

Fine. And thanks for marking thread solved too.