Hi all,
we are in the process of porting a legacy application (AS/400) to JEE (JBoss).
For the time being, we need to be able to run against the existing DB2/400 database (with its unbelievably knotty schema), so that old (RPG) and new (Java) can run side by side.
Problem is, many database columns have a hash mark ("#", for "number") in their names, like "FLLAG#" (for "vehicle storage number"). This works fine with the DB2400Dialect, but not with the MySQL5Dialect, which causes SQL syntax exceptions (we want to use MySQL for local development/testing, since not all developers have VPN access to the DB2/400 database).
When pasting the SQL query into MySQL Query browser, everything after the "#" is grayed (commented?) out. Quoting the qualified column name resolves the problem, the following query runs fine:
Code:
select count('compound0_.FLLAG#') as col_0_0_ from H_HOEDTA.FZLLAGP compound0_
Is this a bug in MySQL5Dialect? What if MySQL5Dialect would simply quote all column names?
many thanks,
Karl
Hibernate version: Hibernate EntityManager 3.2.1.GA
Hibernate Annotations 3.2.1.GA
Hibernate 3.2.4.sp1.cp03
Mapping documents:Code:
@NamedQuery(name = Compound.GET_NR_ROWS, query = "select count(c.id) from Compound c", hints = { @QueryHint(name = "org.hibernate.cacheable", value = "true") })
Code between sessionFactory.openSession() and session.close():Code:
Query query = em().createNamedQuery(Compound.GET_NR_ROWS);
return ((Long) query.getSingleResult()).intValue();
Full stack trace of any exception that occurs:Code:
16:36:46,468 DEBUG [JDBCExceptionReporter] could not execute query [select count(compound0_.FLLAG#) as col_0_0_ from H_HOEDTA.FZLLAGP compound0_]
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:255)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2136)
at org.hibernate.loader.Loader.list(Loader.java:2096)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:80)
at com.hji.common.dao.GenericDAO.getNrRows(GenericDAO.java:99)
at com.hji.common.domain.repository.AbstractRepository.getNrItems(AbstractRepository.java:107)
Name and version of the database you are using:MySQL 5.1.22-rc-community
The generated SQL (show_sql=true):HQL:
Code:
select count(c.id) from com.hji.compound.domain.Compound c
SQL:
Code:
select count(compound0_.FLLAG#) as col_0_0_ from H_HOEDTA.FZLLAGP compound0_
Debug level Hibernate log excerpt:Code:
2008-08-17 16:36:46,226 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
2008-08-17 16:36:46,303 DEBUG [org.hibernate.SQL]
select
count(compound0_.FLLAG#) as col_0_0_
from
H_HOEDTA.FZLLAGP compound0_
2008-08-17 16:36:46,454 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2008-08-17 16:36:46,454 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
2008-08-17 16:36:46,455 DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2008-08-17 16:36:46,468 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select count(compound0_.FLLAG#) as col_0_0_ from H_HOEDTA.FZLLAGP compound0_]
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:255)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2136)
at org.hibernate.loader.Loader.list(Loader.java:2096)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:80)
Login
FAQ
Search
