I'm trying to save an Order object in a table called "Order". Order is a keyword in SQL Server, so in order for hibernate to work with this, I put the backtick (`) character around the table element in my mapping file (as below). Order has a 1:1 unidirectional relationship with a Report object, and in the database, Order and Report have a 1:M relationship.

However, it does not save the object and keeps throwing the exception below. Looking at the SQL, it appears that the SQL statement is too ambigious for hibernate to interpret, which is understandable given that Order is a reserved word. How can I make Order work in this case. I've read the doc about using the backtick, but have I missed anything else here?


PS. Another case, I had used a reserved word (freeText) in an attribute for another table and used the same backtick as follows <property name="freeText" column="`freeText`"/> This works, but my above scenario does not. Can anyone assist?

[b]Hibernate version:2.1.4[/b]

[b]Mapping documents:
<hibernate-mapping>

<class name="com.orchestral.cdr.element.Order" table="`Order`">
...
...
...
</class>

</hibernate-mapping>

In Report.hbm.xml the association is modelled
<many-to-one
name="order"
class="com.orchestral.cdr.element.Order"
cascade="all"
column="orderFK">
</many-to-one>

[/b]


[b]Full stack trace of any exception that occurs:
net.sf.hibernate.JDBCException: Could not save object
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:783)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:738)
at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1387)
at net.sf.hibernate.engine.Cascades$4.cascade(Cascades.java:114)
at net.sf.hibernate.engine.Cascades.cascade(Cascades.java:436)
at net.sf.hibernate.engine.Cascades.cascade(Cascades.java:503)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:890)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:857)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:779)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:738)
at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1387)
at net.sf.hibernate.engine.Cascades$4.cascade(Cascades.java:114)
at net.sf.hibernate.engine.Cascades.cascade(Cascades.java:436)
at net.sf.hibernate.engine.Cascades.cascadeCollection(Cascades.java:526)
at net.sf.hibernate.engine.Cascades.cascade(Cascades.java:452)
at net.sf.hibernate.engine.Cascades.cascade(Cascades.java:503)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:951)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:857)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:779)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:738)
at com.orchestral.cdr.CDRDBBaseImpl.persist(CDRDBBaseImpl.java:287)
at com.orchestral.cdr.element.PersistReportTest.testPersistReport(PersistReportTest.java:69)
at java.lang.reflect.Method.invoke(Native Method)
at junit.framework.TestCase.runTest(TestCase.java:166)
at junit.framework.TestCase.runBare(TestCase.java:140)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:131)
at junit.framework.TestSuite.runTest(TestSuite.java:173)
at junit.framework.TestSuite.run(TestSuite.java:168)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
Caused by: java.sql.SQLException: [CONTRACT1]Incorrect syntax near the keyword 'Order'.
at com.inet.tds.e.a(Unknown Source)
at com.inet.tds.e.a(Unknown Source)
at com.inet.tds.b.int(Unknown Source)
at com.inet.tds.b.executeQuery(Unknown Source)
at net.sf.hibernate.id.IncrementGenerator.getNext(IncrementGenerator.java:67)
at net.sf.hibernate.id.IncrementGenerator.generate(IncrementGenerator.java:42)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:765)
... 33 more


[/b]

[b]Name and version of the database you are using: SQL Server 2000[/b]

[b]The generated SQL (show_sql=true):
select max(id) from Order
[/b]

[b]Debug level Hibernate log excerpt:
[DEBUG] (hibernate.impl.SessionImpl ) - opened session
[DEBUG] (bernate.transaction.JDBCTransaction) - begin
[DEBUG] (ion.DriverManagerConnectionProvider) - total checked-out connections: 0
[DEBUG] (ion.DriverManagerConnectionProvider) - using pooled JDBC connection, pool size: 0
[DEBUG] (bernate.transaction.JDBCTransaction) - current autocommit status:false
[DEBUG] (hibernate.id.IncrementGenerator ) - fetching initial value: select max(id) from Patient
[DEBUG] (hibernate.id.IncrementGenerator ) - first free id: 1
[DEBUG] (hibernate.impl.SessionImpl ) - generated identifier: 1
[DEBUG] (hibernate.impl.SessionImpl ) - saving [com.orchestral.cdr.element.Patient#1]
[DEBUG] (hibernate.engine.Cascades ) - processing cascades for: com.orchestral.cdr.element.Patient
[DEBUG] (hibernate.engine.Cascades ) - done processing cascades for: com.orchestral.cdr.element.Patient
[DEBUG] (hibernate.impl.WrapVisitor ) - Wrapped collection in role: com.orchestral.cdr.element.Patient.reports
[DEBUG] (hibernate.engine.Cascades ) - processing cascades for: com.orchestral.cdr.element.Patient
[DEBUG] (hibernate.engine.Cascades ) - cascading to collection: com.orchestral.cdr.element.Patient.reports
[DEBUG] (hibernate.engine.Cascades ) - cascading to saveOrUpdate()
[DEBUG] (hibernate.impl.SessionImpl ) - saveOrUpdate() unsaved instance
[DEBUG] (hibernate.id.IncrementGenerator ) - fetching initial value: select max(id) from Report
[DEBUG] (hibernate.id.IncrementGenerator ) - first free id: 1
[DEBUG] (hibernate.impl.SessionImpl ) - generated identifier: 1
[DEBUG] (hibernate.impl.SessionImpl ) - saving [com.orchestral.cdr.element.Report#1]
[DEBUG] (hibernate.engine.Cascades ) - processing cascades for: com.orchestral.cdr.element.Report
[DEBUG] (hibernate.engine.Cascades ) - cascading to saveOrUpdate()
[DEBUG] (hibernate.impl.SessionImpl ) - saveOrUpdate() unsaved instance
[DEBUG] (hibernate.id.IncrementGenerator ) - fetching initial value: select max(id) from Order
[DEBUG] (ibernate.util.JDBCExceptionReporter) - SQL Exception
[/b]

Hi, does anyone have an answer to this?

I've figured it out.

Rewrite the following to be
<class name="com.orchestral.cdr.element.Order" table="[Order]">

instead of using a backtick (`) when doing this for fieldnames, use square brackets [] when doing the same for table names.

confusing yes, I managed to stumble on this but the documentation doesn't seem to mention this fact. can this be updated or clarified, so others dont go through the same frustration that I have.

More peculiarity I've found

For Oracle, when defining a table definition use the following line of code
<class name="com.orchestral.cdr.element.Order" table='"ORDER"'>

The SQL for Oracle needs the double quotes, so you must encapsulate the double quote inside a pair of single quotes. Please make this aware to everyone in the reference docs.

Please report this to JIRA, the generic escaping should work for all DB catalog identifiers.

[quote="christian"]Please report this to JIRA, the generic escaping should work for all DB catalog identifiers.[/quote]

Hi
How do I report to JIRA?

I looked at the posted bug and it isn't clear to me that it has been addressed. There was a great deal of discussion, including, whether there was even a bug here. I am trying to figure out what version fixes the problem ... I see exactly what was originally reported: the Increment Generator is not using the proper table and column case-sensitivity wrappers. I think my company is using Hibernate 2.1.8 ... but the hibernate2.jar metadata indicates 2.1.7. So, is there a fix for the Increment Generator?