Question:

Is Hibernate able to save objects where one of the fields is a database keyword?

I have a class (SpecimenSource.java). In this class I have a String field called freeText. In my SpecimenSource table, I have an attribute called freeText. Under SQL Server, freeText is a reserved keyword, so to overcome this, the square bracket is used, [freeText].

What I want is to save some freeText data to this column in the SpecimenSource table, however it's not letting me because of this keyword issue. The trace is as follows

org.dbunit.dataset.DataSetException: java.sql.SQLException: [CONTRACT1]Incorrect syntax near the keyword 'freeText'.
at org.dbunit.database.DatabaseDataSet.getTable(DatabaseDataSet.java:234)
at com.orchestral.cdr.CDRDBBaseImpl.assertTableAsExpected(CDRDBBaseImpl.java:138)
at com.orchestral.cdr.PersistPatientTest.testSpecimenSource(PersistPatientTest.java:162)
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)
java.sql.SQLException: [CONTRACT1]Incorrect syntax near the keyword 'freeText'.

Is there any way in Hibernate to overcome this? Or must I set my database field names to not include any platform specific keywords??

[b]Hibernate version:2.1.4[/b]

[b]Mapping documents:
<property name="freeText" column="[freeText]"/>
[/b]

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

Thanks for the reference. But its still not working for me.

In my mapping file under the property element containing the problematic SQL Server keyword, I have the following line

<property name="freeText" column="[freeText]"/>

According to the reference, brackets are used in SQL Server, as what I've typed above, but it still gives the beforementioned stack trace. I've also tried the single quote mark (') and the backtick (`), without success. What could i have missed?

<property name="freeText" column="`freeText`"/>

if not - then please show dialect plus generated sql (and exception)

Its still not working unfortunately. I use the backtick, which on my keyboard is on the same key as the tilde (~) character.

Here is the generated SQL after setting the property to TRUE

Hibernate: insert into SpecimenSource ([freeText], nameOrCodeCode, nameOrCodeDescription, nameOrCodeCodingSystem, id) values (?, ?, ?, ?, ?)

The exception trace
org.dbunit.dataset.DataSetException: java.sql.SQLException: [CONTRACT1]Incorrect syntax near the keyword 'freeText'.
at org.dbunit.database.DatabaseDataSet.getTable(DatabaseDataSet.java:234)
at com.orchestral.cdr.CDRDBBaseImpl.assertTableAsExpected(CDRDBBaseImpl.java:138)
at com.orchestral.cdr.PersistPatientTest.testSpecimenSource(PersistPatientTest.java:162)
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)
java.sql.SQLException: [CONTRACT1]Incorrect syntax near the keyword 'freeText'.
at com.inet.tds.e.a(Unknown Source)
at com.inet.tds.e.a(Unknown Source)
at com.inet.tds.a.if(Unknown Source)
at com.inet.tds.a.do(Unknown Source)
at com.inet.tds.a.executeQuery(Unknown Source)
at org.dbunit.database.AbstractResultSetTable.<init>(AbstractResultSetTable.java:91)
at org.dbunit.database.ForwardOnlyResultSetTable.<init>(ForwardOnlyResultSetTable.java:50)
at org.dbunit.database.CachedResultSetTable.<init>(CachedResultSetTable.java:47)
at org.dbunit.database.CachedResultSetTableFactory.createTable(CachedResultSetTableFactory.java:46)
at org.dbunit.database.DatabaseDataSet.getTable(DatabaseDataSet.java:230)
at com.orchestral.cdr.CDRDBBaseImpl.assertTableAsExpected(CDRDBBaseImpl.java:138)
at com.orchestral.cdr.PersistPatientTest.testSpecimenSource(PersistPatientTest.java:162)
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)

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

<hibernate-mapping>

<class name="com.orchestral.cdr.element.SpecimenSource">

<id type="int" unsaved-value="null">
<column name="id"/>
<generator class="increment"/>
</id>

<property name="freeText" column="`freeText`"/>
<property name="nameOrCodeCode" column="nameOrCodeCode"/>
<property name="nameOrCodeDescription" column="nameOrCodeDescription"/>
<property name="nameOrCodeCodingSystem" column="nameOrCodeCodingSystem"/>
</class>

</hibernate-mapping>

Other config properties

hibernate.dialect=net.sf.hibernate.dialect.SQLServerDialect
hibernate.connection.driver_class=com.inet.tds.TdsDriver
hibernate.connection.url=jdbc:inetdae:CONTRACT1:1433?database=CDRAaron&sql7=true

Thanks for any assistance.

huh ?

First: the sql is correct as i see it, so no problems there with hibernate (looks more like a problem with the database)

Second: your stacktrace does not contain anything hibernate related, but only dbunit stuff.....so how does that have anything to do with hibernate ? :)

Hi,

Thanks for your assistance. I was running unit tests and forgot to check the state of the database. They were failing and I didn't check the DB after the failed test. Sorry for my mistake!

okay one more question, sorry!

would the backtick(`) character also work under the Oracle platform??? There is a keyword called COMMENT and it just happens to be a field name in my table and my class. Can I still apply the same thing here, or must I do something different. We're using Oracle 10g here.

the whole idea of this backtick is portability...so yes, this work on any platform with a proper dialect and support for some kind of quotes