I am getting an exception running the following query. It appears to be related to the fact that MySQL Dialect is using an underscore as the schema seperator (In this case nmcibsa is the schema and SEAT_INFO is the table name:

HQL:
select count(seatInfo) from SeatInfo as seatInfo where seatInfo.seatStatus=:seatStatus

Generated SQL:
Hibernate: select count(seatinfo0_.ID) as x0_0_ from nmcibsa_SEAT_INFO seatinfo0_ where (seatinfo0_.SEAT_STATUS=? )

Stack Trace(Only down to start of hibernate code to save space):
Feb 18, 2004 3:43:07 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1146, SQLState: S1000
Feb 18, 2004 3:43:07 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: General error, message from server: "Table 'nmcibsa.nmcibsa_seat_info' doesn't exist"
Feb 18, 2004 3:43:07 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1146, SQLState: S1000
Feb 18, 2004 3:43:07 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: General error, message from server: "Table 'nmcibsa.nmcibsa_seat_info' doesn't exist"
Feb 18, 2004 3:43:07 PM net.sf.hibernate.JDBCException <init>
SEVERE: Could not execute query
java.sql.SQLException: General error, message from server: "Table 'nmcibsa.nmcibsa_seat_info' doesn't exist"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.hql.QueryTranslator.iterate(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1540)
at net.sf.hibernate.impl.QueryImpl.iterate(QueryImpl.java:27)


Hibernate Version: 2.1.2

mapping document:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>

<meta attribute="extends">Persistent</meta>

<meta attribute="session-method">
mil.spawar.bsa.dataaccess.hibernate.HibernateSession.currentSession();
</meta>

<class name="mil.spawar.bsa.businessobjects.hibernate.SeatInfo" schema="nmcibsa" table="SEAT_INFO">

<cache usage="read-write"/>

<id name="id" type="java.lang.Long">
<column name="ID" not-null="true"/>

<!-- Use for MySQL
-->
<generator class="native"/>


<!-- Use for Oracle

<generator class="sequence">
<param name="sequence">SEAT_INFO_SEQUENCE</param>
</generator>
-->

</id>
<property name="seatId" type="java.lang.Long">
<meta attribute="finder">findBySeatId</meta>
<column name="SEAT_ID" length="7" not-null="true"/>
</property>
<property name="seatNumber" type="java.lang.Long">
<meta attribute="finder">findBySeatNbr</meta>
<column name="SEAT_NBR" length="7" not-null="true"/>
</property>
<property name="seatStatus" type="string">
<meta attribute="finder">findBySeatStatus</meta>
<column name="SEAT_STATUS" length="1"/>
</property>
<property name="seatOrgCode" type="string">
<meta attribute="finder">findBySeatOrgCode</meta>
<column name="SEAT_ORGCODE" length="25"/>
</property>
<property name="seatName" type="string">
<meta attribute="finder">findBySeatName</meta>
<column name="SEAT_NAME" length="50"/>
</property>
<property name="seatClin" type="string">
<meta attribute="finder">findBySeatClin</meta>
<column name="SEAT_CLIN" length="6"/>
</property>
<property name="seatType" type="string">
<meta attribute="finder">findBySeatType</meta>
<column name="SEAT_TYPE" length="1"/>
</property>
<property name="seatFee" type="java.lang.Double">
<meta attribute="finder">findBySeatFee</meta>
<column name="SEAT_FEE" sql-type="NUMERIC(9, 2)"/>
</property>
<property name="supportFee" type="java.lang.Double">
<meta attribute="finder">findBySupportFee</meta>
<column name="SUPPORT_FEE" sql-type="NUMERIC(9, 2)"/>
</property>
<property name="seatChargeJon" type="string">
<meta attribute="finder">findBySeatChargeJon</meta>
<column name="SEAT_CHARGE_JON" length="10"/>
</property>
<property name="seatErrorJon" type="string">
<meta attribute="finder">findBySeatErrorJon</meta>
<column name="SEAT_ERROR_JON" length="10"/>
</property>
<property name="auditUserId" type="string">
<meta attribute="finder">findByAuditUserId</meta>
<column name="AUDIT_USER_ID" length="75"/>
</property>
<property name="auditDate" type="timestamp">
<meta attribute="finder">findByAuditDate</meta>
<column name="AUDIT_DATE"/>
</property>

<set name="nmciFeesHistory" order-by="id asc" inverse="true" lazy="true">
<key column="SEATINFO_ID"/>
<one-to-many class="mil.spawar.bsa.businessobjects.hibernate.NmciFeesHistory"/>
</set>

</class>
</hibernate-mapping>

Code:

public static int countBy(SeatInfo seatInfo, String operator) throws SQLException, HibernateException {
Session session = mil.spawar.bsa.dataaccess.hibernate.HibernateSession.currentSession();

StringBuffer queryBuffer = new StringBuffer();

NmciFeesHistory history = null;

/*--------------------------------------------------------------------*
* Create appropriate select clause and append join alias for *
* component object properties that are not null *
*--------------------------------------------------------------------*/
if (seatInfo.getNmciFeesHistory() != null && seatInfo.getNmciFeesHistory().size() > 0) {
history = (NmciFeesHistory) seatInfo.getNmciFeesHistory().iterator().next();
queryBuffer.append(
"select count(seatInfo) from NmciFeesHistory as history " + "inner join history.seatInfo as seatInfo ");
} else {
queryBuffer.append("select count(seatInfo) from SeatInfo as seatInfo ");
}

String whereClause = buildWhereClause(seatInfo, operator);
if (StringUtils.substringAfter(whereClause, "where").length() > 1)
queryBuffer.append(whereClause);
else
logger.severe("where clause empty for this query");

Query query = session.createQuery(queryBuffer.toString());

/*--------------------------------------------------------------------*
* Set query parameters. *
*--------------------------------------------------------------------*/
query.setProperties(seatInfo);
if (history != null) {
query.setProperties(history);
}

Iterator iter = query.iterate();
Integer count = (Integer) iter.next();
return count.intValue();
}

stack trace above
database and version: MySQL version 4

nmbisca is not a schema, it is a database. The schema property is not intended to be used for this. In fact MySQL has no schma support at all. You might work around by creating your own Dialect extending MySQL Dialect and override getSchemaSeperator()[/code]

You're correct that it is not a schema but it in practical terms it is part of the qualified name of the table used by MySQL and the SQL <databasename>_<tablename> will cause an error. Given that MySQL does not support schema, do you know the purpose of the addition of the schema separator to the Dialect superclass and the fact that MySQL dialect is the only one that overrides the period and uses an underscore?

This will just cause an error if you set the schema property.

in this HQL statement
select count(seatInfo) from SeatInfo as seatInfo where seatInfo.seatStatus=:seatStatus

you should not use "as"


it should be like the one below

select count(seatInfo) from SeatInfo seatInfo where seatInfo.seatStatus=:seatStatus

Ok, I looked thru CVS for the reasoning for this change. It says it was done to make it more portable?? I see that the schema generator will generate correct table names now too but I created my tables with an earlier version. And I develop in MySQL and deploy to Oracle and now it is not portable at all. I don't understand the use of the underscore because the dot notation IS supported in MySQL and in fact serves almost the same purpose as it does in schema driven databases like Oracle -- it qualifies the table name. I think the reported issue HB-176 was mistaken in stating that MySQL does not support dot notation. I can easily change the code but it makes my project harder to maintain as I will have a custom Hibernate installation. Does anyone else agree that this is a problem and MySQL does in fact support "schema-like" table name qualification using the dot? If not, what am I missing?

You can use as, this is no problem.

Have you removed "as" from the HQL query and tested it??

y'know, it is incredibly trivial to implement MyMySQLDialect extends MySQLDialect and override one method....

My god hibernateuser, at least read some of the manual before you post such wrong stuff about "as": http://www.hibernate.org/hib_docs/reference/html/query-language.html#query-language-s2

No problem extending it at all. As a matter of fact I just removed the method from the Mysql dialect because my argument is it is not necessary and I am missing what the problem that the fix solved was. I love Hibernate and would like to use the base version 100% if possible to make upgrading easier. Again, just trying to get a handle on what the purpose of the "fix" was; if it isn't necessary, couldn't the product be made better by pulling the dialect specific change out -- it would make it more trivial to change the underlying database a project uses which goes back to one of the nice things about using a framework on top of the db anyway, etc. etc. ... ...