problem - database select with hibernate

[sawan parihar]

Hi,

Env: Spring,Hibernate, SQLServer

I am trying to fetch the records from "Image" table. My query is pretty simple:
public List getAll() {
return getHibernateTemplate().find("from Image");
}

But the query that hibernate generates is:

select image0_.UID as UID0_ from Image image0_

Exception trace:
SEVERE: Invalid object name 'Image'.
Exception in thread "main" org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
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.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:849)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:840)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:832)
at sawa.parihar.database.HibernateTestDAO.getAll(HibernateTestDAO.java:10)
at sawa.parihar.database.HibernateSpringTest.test(HibernateSpringTest.java:21)
at sawa.parihar.database.HibernateSpringTest.main(HibernateSpringTest.java:41)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'Image'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
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)
... 14 more


Any help is highly appreciated.

Thanks,
Sawan

[Shailesh Kini]

Sawan,

Looking at the error, it seems like an issue with the mapping file. Please check if you have a table "Image" in SQLServer. If it exists please check for execute permissions.

[sawan parihar]

Thanks.

The table does exist.
It just came to my mind that the problem is the schema name .
I am using BasicDataSource to connect and not sure how can I specify the schema name.


<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName>
<value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value></property>
<property name="url">
<value>jdbc:sqlserver://testdev01:1433</value></property>
<property name="username"><value>admin</value></property>
<property name="password"><value>password</value></property>



the query should have been something like this:

select * from schema..Image as Image.


Any idea how can I specify the schema name?

Thanks.
[ July 31, 2007: Message edited by: sawan parihar ]

[sawan parihar]

I found the sol. Just in case someone might encounter this here is the sol:

Not sure this is the right way but it works.

For SQLServer the query should have been like:

select * from schema..image

Now i specified the default schema name in hibernate.properties file:
hibernate.default_schema=schema.

The schema name is 'schema' but note that '.' after the schema name. Without the '.' hibernate query will be select * from schema.name. Just one '.' this won't work. I need two '.' and I specified one in the properties file.

This is how it works.

Thanks.
[ August 01, 2007: Message edited by: sawan parihar ]