Hi there,

I'm using PostgreSQL/Hibernate to develop an application. I have created an schema named hivits-ts and PostgreSQL doesn't allow to use the - character unless you put the schema name in double quotes (select * from "hivits-ts".Table ...). Hibernate doesn't seem to do this by default, so every time I try to access my database I get an exception due to the generated SQL:

ERROR: syntax error at or near "-"

Is there anyway to tell Hibernate to use double quotes surrounding the schema name?? By the way, will I have the same problem with column names (mixed case)?

Thanks in advance, best regards
Jose

Hibernate version: 3.0.5

Mapping documents:
<?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.ceescat.hivitsts.dominio.Lugar" table="Lugar" schema="hivits-ts" lazy="false">
<id name="Codigo" type="java.lang.Integer">
<column name="codigo" scale="4" precision="0" not-null="true" unique="true" sql-type="int4" />
<generator class="assigned" />
</id>
<property name="Descripcion" type="java.lang.String">
<column name="descripcion" scale="255" precision="0" not-null="true" unique="true" sql-type="varchar" />
</property>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
Query query = session.createQuery( "from Lugar as l" );
List lugares = query.list();


Full stack trace of any exception that occurs:
Exception in thread "AWT-EventQueue-0" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.ceescat.hivitsts.gui.AplicacionEntradaDatos.linkComponents(AplicacionEntradaDatos.java:36)
at org.ceescat.hivitsts.gui.AplicacionEntradaDatos.<init>(AplicacionEntradaDatos.java:30)
at org.ceescat.hivitsts.gui.AplicacionEntradaDatos$2.run(AplicacionEntradaDatos.java:7716)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(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.SQLException: ERROR: syntax error at or near "-"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 15 more


Name and version of the database you are using:
PostgreSQL 8

The generated SQL (show_sql=true):
Hibernate: select lugar0_.codigo as codigo, lugar0_.descripcion as descripc2_31_ from hivits-ts.Lugar lugar0_

Debug level Hibernate log excerpt:

Surely schema="&quot;foo-bar&quot;" or something like that must work?

Yes, that works, but this is an existing database with more than three hundred columns in several tables, all of them (tables and columns) with mixed case names, and that also makes Hibernate fail. Please, please tell me there's some global preference I can enable, so I don't have to rename all the tables and columns in the database.

Thanks a lot, best regards
Jose

Read the documentation, NamingStrategy.

Ok, thanks a lot for the tip... it's hard to keep track of all those new and great features... I think it's time to re-read the docs ;o)

By the way, NamingStrategy seems to take into account table and column names, but not schema names... why? Is this included anywhere else? If not, should I post a feature request on JIRA?

Thanks a lot, best regards
Jose

bump Any comments on this?

Best regards
Jose