These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: SQLGrammarException: I can't seem to figure this one out.
PostPosted: Thu Sep 04, 2008 10:11 am 
Newbie

Joined: Thu Sep 04, 2008 10:02 am
Posts: 2
Hey guys,

I have to say I'm fairly comfortable with Hibernate, as we use it quite extensively at work. Myself, I was introduced to it about 4 months ago, and having learned how it works, I can't say I've had many issues. Nevertheless, I am now running into an error that I just can't seem to find a solution to.

Essentially, I have a class called Unit with some parameters, getters and setters. This class has its own hbm.xml file to map it to the "unit" table in the database. There is also a UnitDAO class which takes care of all the various transactions back and forth.

I am attaching all the code here for you guys to get a better look.

Code:
public class Unit{
   
   //Static variables representing the different unit types
   public static int MILLISECONDS_ID = 1,
                 SECONDS_ID = 2,
                 MILLIMETER_ID = 3,
                 CENTIMETER_ID = 4,
                 METER_ID = 5,
                 INCHES_ID = 6,
                 PSI_ID = 7,
                 MPA_ID = 8,
                 POROSITY_PERCENT_ID = 9,
                 POROSITY_FRACTION_ID = 10,
                 MILLIDARCY_ID = 11,
                 MICROMETER_SQUARED_ID = 13,
                 CUBIC_CENTIETERS_ID = 14,
                 MILLILETERS_ID = 15,
                 PERCENT_GAIN_ID = 16,
                 REVOLUTIONS_PER_MINUTE_ID = 17,
                 RADIANS_PER_SECOND_ID = 18,
                 GRAMS_PER_MILLILETER_ID = 19,
                 GRAMS_PER_CUBIC_CENTIMETER_ID = 20,
                 KILOGRAMS_PER_METER_CUBED_ID = 21,
                 GRAMS_ID = 22,
                 KILOGRAMS_ID = 23,
                 POUNDS_ID = 24,
                 OUNCES_ID = 25,
                 DEGREES_CELSIUS_ID = 26,
                 DEGREES_FAHRENHEIT_ID = 27,
                 COATES_COEFFICIENT_ID = 28,
                 SATURATION_PERCENT_ID = 29,
                 SATURATION_FRACTION_ID = 30;

                
   
   private int id;   
   private String label;   
   private String description;
   private double multiplicationFactor;
   private int precision;
   private boolean isUserUnit;
   private boolean isSystemUnit;
   private UnitType unitType;
   private Date createdDate;
   
   /** default constructor */
   public Unit() {
   }
   
   /** minimal constructor */
   public Unit(UnitType unitType, String label, String description, double multiplicationFactor, int precision, boolean isUserUnit, boolean isSystemUnit) {
      this.unitType = unitType;
      this.label = label;
      this.description = description;
      this.multiplicationFactor = multiplicationFactor;
      this.precision = precision;
      this.isUserUnit = isUserUnit;
      this.isSystemUnit = isSystemUnit;
   }

   public UnitType getUnitType() {
      return unitType;
   }

   public void setUnitType(UnitType unitType) {
      this.unitType = unitType;
   }
   
   public int getId() {
      return this.id;
   }

   public void setId(int id) {
      this.id = id;
   }
   
   public String getLabel() {
      return this.label;
   }

   public void setLabel(String label) {
      this.label = label;
   }
   
   public String getDescription() {
      return this.description;
   }

   public void setDescription(String description) {
      this.description = description;
   }
   
   public double getMultiplicationFactor() {
      return this.multiplicationFactor;
   }
   
   public int getPrecision() {
      return this.precision;
   }

   public void setPrecision(int precision) {
      this.precision = precision;
   }

   public void setMultiplicationFactor(double multiplicationFactor) {
      this.multiplicationFactor = multiplicationFactor;
   }
   
   public boolean getIsUserUnit() {
      return this.isUserUnit;
   }

   public void setIsUserUnit(boolean isUserUnit) {
      this.isUserUnit = isUserUnit;
   }
   
   public boolean getIsSystemUnit() {
      return this.isSystemUnit;
   }

   public void setIsSystemUnit(boolean isSystemUnit) {
      this.isSystemUnit = isSystemUnit;
   }
   
   public Date getCreatedDate() {
      return this.createdDate;
   }

   public void setCreatedDate(Date createdDate) {
      this.createdDate = createdDate;
   }

   public String toString()
   {
      return this.label;
   }
   
}





Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Aug 1, 2006 4:27:49 PM by Hibernate Tools 3.1.0.beta5 -->
<hibernate-mapping>
    <class name="org.git.systems.data.Unit" table="unit" catalog="gitcap">
   
        <comment></comment>
        <id name="id" type="int">
            <column name="ID" />
            <generator class="native" />
        </id>
       
        <many-to-one name="unitType" class="org.git.systems.data.UnitType" fetch="select" lazy="false">
            <column name="UnitTypeID" not-null="true">
                <comment></comment>
            </column>
        </many-to-one>
       
        <property name="label" type="string">
            <column name="Label" length="45" not-null="true">
                <comment></comment>
            </column>
        </property>
       
        <property name="description" type="string">
            <column name="Description" length="255" not-null="true">
                <comment></comment>
            </column>
        </property>
       
        <property name="multiplicationFactor" type="double">
            <column name="MultiplicationFactor">
                <comment></comment>
            </column>
        </property>
       
        <property name="precision" type="int">
            <column name="Precision" not-null="true">
                <comment></comment>
            </column>
        </property>
       
        <property name="isUserUnit" type="boolean">
            <column name="IsUserUnit">
                <comment></comment>
            </column>
        </property>
       
        <property name="isSystemUnit" type="boolean">
            <column name="IsSystemUnit">
                <comment></comment>
            </column>
        </property>
       
        <property name="createdDate" type="timestamp">
            <column name="CreatedDate" not-null="false">
                <comment></comment>
            </column>
        </property>
       
       
    </class>
</hibernate-mapping>





Code:
public class UnitDAO{

   // Fields   

   // Constructors

   /** default constructor */
   public UnitDAO() {
   }
   
   
   /**
    * Method for saving the given User to the database.
    *
    * @param user to be saved.
    */
   public static void save(Unit unit)
   {
      try
      {                        
         Session sess = HibernateUtil.currentSession();         
         sess.beginTransaction();
         
         //Save the user
         sess.save(unit);
                  
         sess.getTransaction().commit();
      
         
         HibernateUtil.closeSession();
      }
      catch(Exception ex)
      {
         ex.printStackTrace();   
      }   
   }
   
   /**
    * Method for updating the given user to the database.
    *
    * @param user to be updated.
    */
   public static void update(Unit unit)
   {
      try
      {                        
         Session sess = HibernateUtil.currentSession();         
         sess.beginTransaction();
         
         sess.update(unit);   
         
         sess.getTransaction().commit();         
         
         
         HibernateUtil.closeSession();
      }
      catch(Exception ex)
      {
         ex.printStackTrace();   
      }   
   }
   
   /**
    * Method for retrieving the Units
    *
    */
   public static Unit getUnit(int unitID)
   {
      try
      {                           
         Session sess = HibernateUtil.currentSession();         
         sess.beginTransaction();         
            
         Unit u = (Unit) sess.get(Unit.class, unitID);
         
         sess.getTransaction().commit();
         HibernateUtil.closeSession();
         return u;
      }
      catch(Exception ex)
      {
         ex.printStackTrace();   
      }   
      
      return null;
   }

}


I am having issues with the update method for some reason. When I get the unit object from the database, modify its description (for instance), and then update the change to our mySQL DB via the DAO class, it just fails. This is the error I get:


Code:
esting description change.
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:333)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at org.git.systems.data.dao.UnitDAO.update(UnitDAO.java:74)
   at org.git.systems.gui.PreferencesJDialog.createPreferencesDialog(PreferencesJDialog.java:263)
   at org.git.systems.gui.PreferencesJDialog.<init>(PreferencesJDialog.java:132)
   at org.git.systems.gui.mainwindow.GITCapMainMenuBar$7.actionPerformed(GITCapMainMenuBar.java:478)
   at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
   at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
   at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
   at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
   at javax.swing.AbstractButton.doClick(Unknown Source)
   at javax.swing.plaf.basic.BasicMenuItemUI.doClick(Unknown Source)
   at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(Unknown Source)
   at java.awt.Component.processMouseEvent(Unknown Source)
   at javax.swing.JComponent.processMouseEvent(Unknown Source)
   at java.awt.Component.processEvent(Unknown Source)
   at java.awt.Container.processEvent(Unknown Source)
   at java.awt.Component.dispatchEventImpl(Unknown Source)
   at java.awt.Container.dispatchEventImpl(Unknown Source)
   at java.awt.Component.dispatchEvent(Unknown Source)
   at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
   at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
   at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
   at java.awt.Container.dispatchEventImpl(Unknown Source)
   at java.awt.Window.dispatchEventImpl(Unknown Source)
   at java.awt.Component.dispatchEvent(Unknown Source)
   at java.awt.EventQueue.dispatchEvent(Unknown Source)
   at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
   at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
   at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
   at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
   at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
   at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: 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 'Precision=3, IsUserUnit='1', IsSystemUnit='1', CreatedDate='2008-08-25 10:37:25'' at line 1
   at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1099)
   at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:849)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
   ... 38 more


I've made sure that there are no type mismatches, that all the names are correctly specified, etc.

I'm not really sure what the heck is going on, so any help guys would be greatly appreciated. I've banged my head on this one for a while now before posting...

Thanks in advance!


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 04, 2008 1:21 pm 
Beginner
Beginner

Joined: Wed Apr 20, 2005 9:30 am
Posts: 39
Well... I would start with at least turning on the SQL output option so you can see what Hibernate is generating. You might find a big clue there...

It might be possible that your field name - precision - is a reserved word for mysql when using the update statement? The part of the exception:

Code:
check the manual that corresponds to your MySQL server version for the right syntax to use near 'Precision=3

I think that might be a clue.

Also noticed that your update doesn't have a transaction rollback. Not trying to be pedantic, but the way you've coded that makes it hard to debug and you've got no ability to rollback that transaction should an exception occur.

Would you not want to do similar to this (minus my crap formatting)?

Code:
public static void update(Unit unit) {
  Transaction tx = null;
  try {                       
      Session sess = HibernateUtil.currentSession();         
      tx = sess.beginTransaction();
      sess.update(unit);   
      tx.commit();         
  } catch(Exception ex) {
      ex.printStackTrace();
      if (tx != null)
        tx.rollback();
  } finally {
      HibernateUtil.closeSession();
  }
}


This gives you the ability to rollback a horked transaction AND examine the transaction in the debugger.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 04, 2008 2:41 pm 
Newbie

Joined: Thu Sep 04, 2008 10:02 am
Posts: 2
That was it!

Damn, I should really check all the reserved words before naming my columns.

Thanks so much mate, that was a wonderful answer.

Cheers.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.