I am trying to work with SQL SERVER 2000, Hibernate 2.0 and running on XP Pro.

Can anyone please point me to an example that I can quickly deploy and use ? My eventual goal is to get hibernate to work with Websphere 5.0 and SQL Server. Are there any good examples out there that guide you through this?

Following is the example that I am working with from the Professional Hibernate book (by Wrox) [does anyone have any other recommendations?]


I started up an example with a Swing interface and a connection to SQL SERVER 2000 from the command line like so :

java -classpath .;C:\Hibernate\hibernate2.jar;C:\Hibernate\lib\dom4j-1.4.jar;C:\Hibernate\lib\commons-logging-1.0.4.jar;C:\Hibernate\lib\commons-collections-2.1.1.jar;C:\SQLServerJDBC\lib\msbase.jar;C:\SQLServerJDBC\lib\mssqlserver.jar;C:\SQLServerJDBC\lib\msutil.jar;C:\Hibernate\lib\ehcache-0.9.jar;C:\Hibernate\lib\cglib-full-2.0.2.jar;C:\Sun\AppServer\lib\j2ee.jar CDTest

Please note that I had to include that I have to include everything when calling from the commandline even though when I echo %CLASSPATH% the above jars are present. As you can see in the following:


echo %CLASSPATH%

C:\Sun\AppServer\lib\j2ee.jar;C:\jakarta-struts-1.2.4\lib\struts.jar;%HIBERNATE_
HOME%\hibernate2.jar;C:\SQLServerJDBC\lib\msbase.jar;C:\SQLServerJDBC\lib\msutil
.jar;C:\SQLServerJDBC\lib\mssqlserver.jar;C:\j2sdk1.4.2_06\bin;C:\Hibernate\hibe
rnate2.jar;C:\Hibernate\lib\dom4j-1.4.jar;C:\Hibernate\lib\commons-logging-1.0.4
.jar;C:\Hibernate\lib\commons-collections-2.1.1.jar;C:\Hibernate\lib\ehcache-0.9
.jar;C:\Hibernate\lib\cglib-full-2.0.2.jar;C:\mysql\connector\mysql-connector-ja
va-3.0.16-ga-bin.jar

When I start the application and then try to add something to the SQL Server "products" database's CD table. I get the following error regarding the ID primary key which is an int:

Hibernate: insert into cd (title, artist, purchasedate, cost, ID) values (?, ?,
?, ?, ?)
Mar 28, 2005 10:34:02 AM net.sf.hibernate.util.JDBCExceptionReporter logExceptio
ns
WARNING: SQL Error: 245, SQLState: 22018
Mar 28, 2005 10:34:02 AM net.sf.hibernate.util.JDBCExceptionReporter logExceptio
ns
SEVERE: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error conve
rting the nvarchar value '#9492;¿#9787;#9829;éiK~?éiL#9568;#9786;Ç?' to a column of data type int.
Mar 28, 2005 10:34:02 AM net.sf.hibernate.impl.SessionImpl execute
SEVERE: Could not synchronize database state with session
net.sf.hibernate.exception.GenericJDBCException: could not insert: [CD##9492;¿#9787;#9829;éiK~?
éiL#9568;#9786;Ç?]
at net.sf.hibernate.exception.SQLStateConverter.handledNonSpecificExcept
ion(SQLStateConverter.java:80)
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverte
r.java:69)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionH
elper.java:29)
at net.sf.hibernate.persister.AbstractEntityPersister.convert(AbstractEn
tityPersister.java:1331)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav
a:472)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav
a:436)
at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.j
ava:37)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2449)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2435)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2392)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2261)
at CDTest$3.actionPerformed(CDTest.java:113)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknow
n Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Sour
ce)
at java.awt.Component.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(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: [Microsoft][SQLServer 2000 Driver for JDBC][SQ
LServer]Syntax error converting the nvarchar value '#9492;¿#9787;#9829;éiK~?éiL#9568;#9786;Ç?' to a colum
n of data type int.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source
)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unkn
own Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Sour
ce)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType
(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)

at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown S
ource)
at com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate(Unknown S
ource)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatche
r.java:22)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav
a:462)
... 30 more
Mar 28, 2005 10:34:02 AM net.sf.hibernate.impl.SessionImpl finalize
WARNING: unclosed connection, forgot to call close() on your session?


So I went ahead and changed the ID primary key to numeric with the same error :

Hibernate: insert into cd (title, artist, purchasedate, cost, ID) values (?, ?,
?, ?, ?)
Mar 28, 2005 10:18:17 AM net.sf.hibernate.util.JDBCExceptionReporter logExceptio
ns
WARNING: SQL Error: 8114, SQLState: HY000
Mar 28, 2005 10:18:17 AM net.sf.hibernate.util.JDBCExceptionReporter logExceptio
ns
SEVERE: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Error converting d
ata type nvarchar to numeric.
Mar 28, 2005 10:18:17 AM net.sf.hibernate.impl.SessionImpl execute
SEVERE: Could not synchronize database state with session
net.sf.hibernate.exception.GenericJDBCException: could not insert: [CD##9492;¿#9787;#9829;éi=)?
éi=_PÇ?]
at net.sf.hibernate.exception.SQLStateConverter.handledNonSpecificExcept
ion(SQLStateConverter.java:80)
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverte
r.java:69)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionH
elper.java:29)
at net.sf.hibernate.persister.AbstractEntityPersister.convert(AbstractEn
tityPersister.java:1331)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav
a:472)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav
a:436)
at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.j
ava:37)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2449)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2435)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2392)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2261)
at CDTest$3.actionPerformed(CDTest.java:113)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknow
n Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Sour
ce)
at java.awt.Component.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(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: [Microsoft][SQLServer 2000 Driver for JDBC][SQ
LServer]Error converting data type nvarchar to numeric.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source
)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unkn
own Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Sour
ce)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType
(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)

at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown S
ource)
at com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate(Unknown S
ource)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatche
r.java:22)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav
a:462)
... 30 more


So I went ahead and changed the ID primary key into a nvarchar and this time the application froze SQL Server's Enterprise Manager. I was UNABLE to open any of the tables because there was a sharing violation error and then when I had shut down the console window (or the command line window that I used to start CDTest), I looked into the database and it was neither updated nor inserted into but I did get the following warnings :


Hibernate: insert into cd (title, artist, purchasedate, cost, ID) values (?, ?,
?, ?, ?)
Mar 28, 2005 10:19:48 AM net.sf.hibernate.util.JDBCExceptionReporter logWarnings

WARNING: SQL Warning: 0, SQLState:
Mar 28, 2005 10:19:48 AM net.sf.hibernate.util.JDBCExceptionReporter logWarnings

WARNING: [Microsoft][SQLServer 2000 Driver for JDBC]Database changed to products

Mar 28, 2005 10:19:48 AM net.sf.hibernate.util.JDBCExceptionReporter logWarnings

WARNING: SQL Warning: 0, SQLState:
Mar 28, 2005 10:19:48 AM net.sf.hibernate.util.JDBCExceptionReporter logWarnings

WARNING: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Changed database
context to 'products'.
Mar 28, 2005 10:19:48 AM net.sf.hibernate.util.JDBCExceptionReporter logWarnings

WARNING: SQL Warning: 0, SQLState:
Mar 28, 2005 10:19:48 AM net.sf.hibernate.util.JDBCExceptionReporter logWarnings

WARNING: [Microsoft][SQLServer 2000 Driver for JDBC]Language changed to us_engli
sh
Mar 28, 2005 10:19:48 AM net.sf.hibernate.util.JDBCExceptionReporter logWarnings

WARNING: SQL Warning: 0, SQLState:
Mar 28, 2005 10:19:48 AM net.sf.hibernate.util.JDBCExceptionReporter logWarnings

WARNING: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Changed language
setting to us_english.


Following is the setting and the part that prints to console before the error :

Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.Environment <clinit>
INFO: Hibernate 2.1.8
Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.Environment <clinit>
INFO: loaded properties from resource hibernate.properties: {hibernate.connectio
n.username=sa , hibernate.connection.password=sa, hibernate.cglib.use_reflection
_optimizer=true, hibernate.dialect=net.sf.hibernate.dialect.SQLServerDialect, hi
bernate.show_sql=true, hibernate.connection.url=jdbc:microsoft:sqlserver://local
host:1433;databaseName=products;selectMethod=cursor;, hibernate.connection.drive
r_class=com.microsoft.jdbc.sqlserver.SQLServerDriver}
Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.Environment <clinit>
INFO: using CGLIB reflection optimizer
Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.Configuration addClass
INFO: Mapping resource: CD.hbm.xml
Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.Binder bindRootClass
INFO: Mapping class: CD -> cd
Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing one-to-many association mappings
Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing one-to-one association property references
Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing foreign key constraints
Mar 28, 2005 10:19:09 AM net.sf.hibernate.dialect.Dialect <init>
INFO: Using dialect: net.sf.hibernate.dialect.SQLServerDialect
Mar 28, 2005 10:19:09 AM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use outer join fetching: true
Mar 28, 2005 10:19:10 AM net.sf.hibernate.connection.DriverManagerConnectionProv
ider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
Mar 28, 2005 10:19:10 AM net.sf.hibernate.connection.DriverManagerConnectionProv
ider configure
INFO: Hibernate connection pool size: 20
Mar 28, 2005 10:19:10 AM net.sf.hibernate.connection.DriverManagerConnectionProv
ider configure
INFO: using driver: com.microsoft.jdbc.sqlserver.SQLServerDriver at URL: jdbc:mi
crosoft:sqlserver://localhost:1433;databaseName=products;selectMethod=cursor;
Mar 28, 2005 10:19:10 AM net.sf.hibernate.connection.DriverManagerConnectionProv
ider configure
INFO: connection properties: {user=test , password=test}
Mar 28, 2005 10:19:10 AM net.sf.hibernate.transaction.TransactionManagerLookupFa
ctory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of process
level read-write cache is not recommended)
Mar 28, 2005 10:19:10 AM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use scrollable result sets: true
Mar 28, 2005 10:19:10 AM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Use JDBC3 getGeneratedKeys(): false
Mar 28, 2005 10:19:10 AM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: false
Mar 28, 2005 10:19:10 AM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: echoing all SQL to stdout
Mar 28, 2005 10:19:10 AM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Mar 28, 2005 10:19:10 AM net.sf.hibernate.cfg.SettingsFactory buildSettings
INFO: cache provider: net.sf.hibernate.cache.EhCacheProvider
Mar 28, 2005 10:19:10 AM net.sf.hibernate.cfg.Configuration configureCaches
INFO: instantiating and configuring caches
Mar 28, 2005 10:19:10 AM net.sf.ehcache.config.Configurator configure
WARNING: No configuration found. Configuring ehcache from ehcache-failsafe.xml f
ound in the classpath: jar:file:/C:/Hibernate/lib/ehcache-0.9.jar!/ehcache-fails
afe.xml
Mar 28, 2005 10:19:10 AM net.sf.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Mar 28, 2005 10:19:10 AM net.sf.hibernate.impl.SessionFactoryObjectFactory addIn
stance
INFO: Not binding factory to JNDI, no JNDI name configured


Attached is the mappings file:

<hibernate-mapping>
<class name="CD"
table="cd">
<id name="Id"
type="string"
unsaved-value="null">
<column name="ID"
sql-type="text"
not-null="true"/>
<generator class="uuid.string"/>
</id>

<property name="title"/>
<property name="artist"/>
<property name="purchasedate" type="date"/>
<property name="cost" type="double"/>
</class>
</hibernate-mapping>

the errors make very sense since you are using a id generator that generates strings with a column of type numeric - that wont work on any db.

The warning you see is just ms sql servers jdbc/database spitting warnings...would also occur with normal jdbc.

have you tried using the more mature jtds driver ? (from jtds.sf.net)

So what column type should I use in order to have the primary key registered properly (besides nvarchar?)

You answered something that I have found a half assed work around for but you didn't answer the more pressing question: if the numeric type is incorrect which type would work (I started using nvarchar and it worked but I can imagine scenarios where the field holds integers).

Instead of tackling the myriad of problems I quoted in my message you are directing me to use another driver??

Thanks for your help nonetheless.

you are using a key generating strategy that generates strings "uuid.hex" not numbers - use another strategy if you want numbers - like increment or native etc. read the docs ,)