HB3 Tools - Could not execute query

forehead · **Joined:** Mon Feb 21, 2005 4:19 am **Posts:** 6

Hi,

I use Hibernate 3 Tools to execute HQL statement, but it shows that:
org.hibernate.exception.SQLGrammarException: could not execute query.

I found it caused by: java.sql.SQLException: General error message from server: "Table 'test.__menu' doesn't exist"

I set the default_catalog and default_schema to null in hibernate.cfg.xml, and I want to know where can I set the table name format?

I guess the format is : db_name.catalog_schema_tablename. right?

thanks.

max · **Posted:** Wed Feb 23, 2005 3:55 am

how can you set it to null ?

you need to set it to blank (empty string)...if that does not work then simply dont put anything in there.

/max

forehead · **Joined:** Mon Feb 21, 2005 4:19 am **Posts:** 6

Yes, I set them to empty string in hibernate.cfg.xml.

How can I change the table format to omit the prefix? Just like dbname.tablename.

max · **Posted:** Wed Feb 23, 2005 10:17 am

just *dont* set them

forehead · **Joined:** Mon Feb 21, 2005 4:19 am **Posts:** 6

My hibernate.cfg.xml contains the following contents:

Code:

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
        <property name="hibernate.cglib.use_reflection_optimizer">true</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.driver_class">org.gjt.mm.mysql.Driver</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.default_schema"></property>
        <property name="hibernate.default_catalog"></property>
        
        <mapping resource="org/ccut/Menu.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

I set them to empty string. But the error still exists, like :"Table 'test._menu' doesn't exist".

If i remove them from the file, the tools would generate catalog property automatically in each .hbm.xml. The default value is the database name.
And the error still exists, like ""Table 'test.test__menu' doesn't exist".

But if I remove the catalog property from the .hbm.xml file manully, it works.
I just want to know why?

max · **Posted:** Thu Feb 24, 2005 5:22 am

please show me the generated hbm.xml files.

we did a fix for mysql before the release that worked in our test cases,
so dont know why it fails in your case.

/max

max · **Posted:** Thu Feb 24, 2005 6:00 am

please show me the generated hbm.xml files. I havent seen this stuff happening on the mysql we did a quick test on....and we had added some code to handle mysqls lack of schema/catalog.

duane5000 · **Posted:** Fri Feb 25, 2005 10:34 am

I'm using MySQL and hibernate 2.1.8
I generated the table.hbm.xml with IntelliJ Tools plugin.

The SQL works if I have anything in the where clause, but I need a "findAll" method.

The HSQL looks like:
"from com.ccistech.rme.portal.promotions.SponsorInfo"

The hbm.xml is
<hibernate-mapping>
<class name="com.ccistech.rme.portal.promotions.SponsorInfo" schema="" table="SPONSOR_INFO">
<cache usage="read-write" />
<id column="SPONSOR_ID" name="sponsorId" type="java.lang.String">
<generator class="native"/>
</id>
<property column="SPONSOR_NAME" length="30" name="sponsorName" type="java.lang.String"/>
<property column="USER_DEFINED_KEY" length="20" name="userDefKey" type="java.lang.String"/>


</class>
</hibernate-mapping>

In my case, there is no reference to default schema.

Again, the following HSQL works fine:
"from com.ccistech.rme.portal.promotions.SponsorInfo as e where e.sponsorName like ?"

Caused by: java.sql.SQLException: General error message from server: "Table 'rmedb._sponsor_info' doesn't exist"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2234)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1412)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1494)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1909)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1847)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1711)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1559)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)

duane5000 · **Posted:** Fri Feb 25, 2005 11:11 am

I tried a hack to no avail, but I'd like to recommend support for these formats..

select * from tablename where 1
select * from tablename where column like '%'
//at worst, should wrap % with percents and break nothing
// not pretty, but would have been a workaround for the issue

This stack is thrown for one of my examples, included only as it differs from the first reported stack...
net.sf.hibernate.exception.SQLGrammarException: Could not execute query
at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:69)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1557)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1531)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1527)
at com.ccistech.rme.portal.promotions.SponsorRW.findAllSponsorsHibernate(SponsorRW.java:434)
at com.ccistech.rme.portal.promotions.SponsorHBSearchAction.getSponsorList(SponsorHBSearchAction.java:144)
at com.ccistech.rme.portal.promotions.SponsorHBSearchAction.execute(SponsorHBSearchAction.java:64)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)

max · **Posted:** Fri Feb 25, 2005 1:20 pm

duane5000 - I have no clue on what you are talking about.

You mention IntelliJ Tools plugin and some info about query syntax ....

what are you asking/saying ? ;)

-max

duane5000 · **Posted:** Fri Feb 25, 2005 2:12 pm

I don't believe this is a tool problem, as the original post states, but I'm experiencing the same error: I am getting an error that _tablename does not exist (see stack). the underscore is valid for an alias in the sql generated, but should be dropped when referencing the table in "from tablename"

this IS working:
session.find(HQL_FIND_ALL_SPONSORS, "somestring",Hibernate.STRING);
final public static String HQL_FIND_LIKE_SPONSOR_NAME =
"from com.ccistech.rme.portal.promotions.SponsorInfo as e where e.sponsorName like ?";

this IS NOT working
session.find(HQL_FIND_ALL_SPONSORS);
final public static String HQL_FIND_ALL_SPONSORS =
"from com.ccistech.rme.portal.promotions.SponsorInfo";

I'm using Hibernate 2.1.8, the original post states he saw this symptom on 3.0
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>

More explanation(?): please see entire thread

max · **Posted:** Fri Feb 25, 2005 2:16 pm

well you have schema="" which is kinda not right.

Remove that and it will work.

forehead · **Joined:** Mon Feb 21, 2005 4:19 am **Posts:** 6

I set the default_catalog and default_schema to empty string.

The following code is Menu.hbm.xml file generated by the tool.

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">

<hibernate-mapping>
<!-- 
   Auto-generated mapping file from
   the hibernate.org cfg2hbm engine
-->
<class 
    name="org.ccut.Menu"
    table="menu"
    catalog="test"
>
   <id 
        name="Id"
        type="java.lang.Integer"
    >
       <column name="id" length="11" not-null="true" unique="true" sql-type="int" />
       <generator class="assigned" />
    </id>
   
    <property
        name="Menutype"
        type="java.lang.String"
    >
        <column name="menutype" length="25" not-null="false" sql-type="varchar" />
   </property>

    <property
        name="Name"
        type="java.lang.String"
    >
        <column name="name" length="100" not-null="false" sql-type="varchar" />
   </property>

    <property
        name="Link"
        type="java.lang.String"
    >
        <column name="link" length="65535" not-null="false" sql-type="text" />
   </property>

    <property
        name="Type"
        type="java.lang.String"
    >
        <column name="type" length="50" not-null="true" sql-type="varchar" />
   </property>

    <property
        name="Published"
        type="java.lang.Byte"
    >
        <column name="published" length="4" not-null="true" sql-type="tinyint" />
   </property>

    <property
        name="Parent"
        type="java.lang.Integer"
    >
        <column name="parent" length="11" not-null="true" sql-type="int" />
   </property>

    <property
        name="Componentid"
        type="java.lang.Integer"
    >
        <column name="componentid" length="11" not-null="true" sql-type="int" />
   </property>

    <property
        name="Sublevel"
        type="java.lang.Integer"
    >
        <column name="sublevel" length="11" not-null="false" sql-type="int" />
   </property>

    <property
        name="Ordering"
        type="java.lang.Integer"
    >
        <column name="ordering" length="11" not-null="false" sql-type="int" />
   </property>

    <property
        name="CheckedOut"
        type="java.lang.Integer"
    >
        <column name="checked_out" length="11" not-null="true" sql-type="int" />
   </property>

    <property
        name="CheckedOutTime"
        type="java.util.Date"
    >
        <column name="checked_out_time" length="19" not-null="true" sql-type="datetime" />
   </property>

    <property
        name="Pollid"
        type="java.lang.Integer"
    >
        <column name="pollid" length="11" not-null="true" sql-type="int" />
   </property>

    <property
        name="BrowserNav"
        type="java.lang.Byte"
    >
        <column name="browserNav" length="4" not-null="false" sql-type="tinyint" />
   </property>

    <property
        name="Access"
        type="java.lang.Byte"
    >
        <column name="access" length="4" not-null="true" sql-type="tinyint" />
   </property>

    <property
        name="Utaccess"
        type="java.lang.Byte"
    >
        <column name="utaccess" length="4" not-null="true" sql-type="tinyint" />
   </property>

    <property
        name="Params"
        type="java.lang.String"
    >
        <column name="params" length="65535" not-null="true" sql-type="text" />
   </property>

</class>
</hibernate-mapping>

max · **Posted:** Sat Feb 26, 2005 6:36 am

the catalog says "test" how can that be ?

Mysql shouldnt report a catalog name ?

forehead · **Joined:** Mon Feb 21, 2005 4:19 am **Posts:** 6

sorry,i didn't explain clearly.

I tried to remove the default_catalog property from hibernate.hbm.xml. The catalog property in .hbm.xml file has be set to "test" which i think it the database name.

When i set the default_catalog property to empty string, the catalog property would be set to "".

i don't know why. the version of MySQL i used is 4.0.