PersistenceException

[waltc]

I am receiving a persistence exception, could not execute JDBC batch update. Here are the particulars.

I am using JBOSS AS 5.00 and Seam 2.1.1, both GA versions. I have a simple entity relationship

a reservation entity which has a from and to date field plus a foreign key (id) filed for the user doing the reservation and the server that is being reserved.I built the CRUD application for this with seam-gen and I am updating the to field of a reservation. The lists and displays look and work great, btw.

When looking deeper into the reason (and the stack trace) I see the following:

"javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update"

still further down...
"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 'from='2008-12-15 00:00:00', server=1, to='2009-01-05 00:00:00', user=1 where id=' at line 1"

Looking back in the log I see the following:

2009-01-02 16:14:48,238 DEBUG [org.hibernate.jdbc.ConnectionManager] (http-127.0.0.1-8080-1) opening JDBC connection
2009-01-02 16:14:48,239 DEBUG [org.hibernate.SQL] (http-127.0.0.1-8080-1)
    update
        ServerReservations.reservations
    set
       
    from
        =?,
        server=?,
        to=?,
        user=?
    where
        id=?
2009-01-02 16:14:48,239 INFO  [STDOUT] (http-127.0.0.1-8080-1) Hibernate:
    update
        ServerReservations.reservations
    set
       
    from
        =?,
        server=?,
        to=?,
        user=?
    where
        id=?
2009-01-02 16:14:48,243 DEBUG [org.hibernate.jdbc.AbstractBatcher] (http-127.0.0.1-8080-1) Executing batch size: 1
2009-01-02 16:14:48,254 DEBUG [org.hibernate.jdbc.AbstractBatcher] (http-127.0.0.1-8080-1) about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2009-01-02 16:14:48,254 DEBUG [org.hibernate.jdbc.ConnectionManager] (http-127.0.0.1-8080-1) skipping aggressive-release due to flush cycle
2009-01-02 16:14:48,255 DEBUG [org.hibernate.util.JDBCExceptionReporter] (http-127.0.0.1-8080-1) Could not execute JDBC batch update [update ServerReservations.reservations set from=?, server=?, to=?, user=? where id=?]
java.sql.BatchUpdateException: 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 'from='2008-12-15 00:00:00', server=1, to='2009-01-05 00:00:00', user=1 where id=' at line 1

Why has it lost the id of the current row being updated?  And, more importantly, what should I change to fix this and similar issues in the future?

Thanks,

Walt

[joblini]

Try setting org.hibernate.* to TRACE in log4j config.

[joblini]

Also, test with JBOSS 4.2.3, which is, I believe, recommended version for Seam 2.1.1.

[arshadm]

Hi,

You are using a few reserved words (e.g. from). Are you sure these are not causing you problems. Also columns like 'user' are probably not a good idea either because that is a reserved word in Postgres (I know you are using mysql at the moment).

Regards.

[waltc]

The only log4j properties file I found was jboss-log4j.xml under jobss/server/default//conf. I added

<!-- Set Hibernate to trace -->

<category name="org.hibernate.*">
<priority value="TRACE"/>
</category>

yet I did not get any trace entries. Should I be looking for another log4j.properties?

Walt

[waltc]

Thank you for the suggestion on 4.3. Here is my thinking. The issue is not with the container but either with seam (2.1.1) or Hibernate. I vote for Seam, well, maybe JPA which is Hibernate.

[waltc]

Thanks for your suggestion! I was confused by the from field as seam-gen built successfully. When I was in Eclipse it did show the use of 'from' as an error, stating it was reserved in JPA. I thought, since I was using Hibernate it wasn't a problem which is why, perhaps, the seam-gen build completed successfully. I changed it anyway and saw a remarkable thing. Aside from it still failing it now fails differently.

Recall the original error text was:

|check the manual that corresponds to your MySQL server version for the right syntax to use near 'from='2008-12-15 00:00:00', server=1, to='2009-01-05 00:00:00', user=1 where id=' at line 1
|
After eliminating from (fromDate) and user (rsvrTo) I got the following error back. Note the subtle differences.

check the manual that corresponds to your MySQL server version for the right syntax to use near 'toDate='2009-01-05 00:00:00', rsvrTo=1 where id=1' at line 1

It dropped the from and server columns altogether and completed the where clause properly. I would have been way happier if it also dropped the rsvrTo field and if it worked. It only needed to change the single column, toDate.

[joblini]

Should be <category name="org.hibernate">

[waltc]

Thanks!

Actually I did run across another log4j properties under jboss-seam/bootstrap. How does that relate to the logging done within JBOSS? It, JBOSS logging, is incredibly noisy. I noticed in the jboss-seam log4j.xml multiple references to 'too noisy". It doesn't get carried over to jboss somehow does it? The other log4j was under jboss/server/default/conf.


Is it me or is this (seam /jboss) really fragile? I was having way better luck when I was specifying ear not war. Yet I was not, to my knowledge, picking up any EE specific components so I figured war would be lighter weight and just better. All I am doing is a simple seam generated CRUD on a few tables and this is just painful. The only table I seem to be having issues with is that reservation table. But the biggest faults I've noticed is when after a restart the seam-home context produces a project name and only a login. It was more consistent as an ear. The other major fault I've noticed is errors relating to (object) not mapped. One has to scroll to the end of the stack trace to find that as the issue as seam produces a large font error screen with some text about no result set.

Yet their booking example seems relatively solid. I haven't tried to break it though. So I am guessing it's either largely me, or the code seam-gen generates, or both.

Comments?

[arshadm]

Hi,

toDate is a function in Mysql so that is reserved as well. I know it's a pain.

Regards.

[joblini]

The one you want is under server/default/conf.  Bootstrap is a lightweight container for unit testing.

[arshadm]

oops. no it isn't. I am not sure what the problem with your last query is.

[waltc]

It does not make sense this is the issue but when I switched to the output being an ear the problem seemed to clear up.  I had been sporadically seeing errors that originated with a class not bound exception. I'd rebuild things in a different order or some subtle change and it would work. The thing of it is I can't see anything that would require this 'project' to be an ear archive vs. a war. It appears Seam is way happier when it's an ear though. Any thoughts on that? It just feels really fragile. Of course my inexperience with it isn't helping matters.

Walt

[joblini]

Copying the generated SQL to a client such as the one provided with MySQL will help you to pinpoint the syntax error.

[arshadm]

Hi,

I have to be honest I have really struggled with Seam 2.1.1 and JBoss AS 5.0.0. I have finally gotten a working web application with all the features I want inclusing managed persistence contexts, et al.

How are you getting access to the entity manager are you using @PersistenceContext or are you using managed persistence contexts and @In EntityManager.

The problem with Seam 2.1.1 and JBoss AS 5 is that we don't get a clear answer on how things should integrate. All the books are about JBoss 4 (e.g. Dan Allen excellent book). But then you try the same thing with the latest version and the configuration is not the same.

Now that I have a working webapp, I can say that virtually all the errors were faults in misconfiguration and or build problems and not bugs in seam.

The error messages don't really help though. I spent half a day trying to work out why hibernate was saying there was no such parameter name in a query when I could quite clearly see the parameter in the query. Turns out I was using an entity manager which didn't have some entities mapped so that the error really was that I was using entities that were unknown to the entity manager !

Also, I don't tend to use hot deploy. My experience with that has not been good. It's a pain to shutdown the server and restart, but I have always found that causes me less headache.

Regards.