using two databases

fwendt · **Posted:** Thu Feb 19, 2004 4:18 am

Hi!

I've failed to find anyone telling anything about using two databases for their application. I have the situation where I have read only-access to enterprise legacy system, and another database where most of the O/R-mapping will occur. However, obviously we would like _not_ having to batch information or somehow have duplicate data, hence some information - such as customers, distributors and alikes - is located in another database.

Is there a way for Hibernate to work on two databases/schemas simultaneously using only one SessionFactory? The two databases live in the same "software instance" of Microsoft's SQL Server.

Intend to use Hibernate 2.1.2, instead of doing it all manually.

emmanuel · **Posted:** Thu Feb 19, 2004 4:31 am

Setting the schema attributes wuuld do the job I guess.

fwendt · **Posted:** Thu Feb 19, 2004 4:43 am

Just downloaded the DTD and started reading it. Got as far as three rows down. Thanks anyway!

fwendt · **Posted:** Wed Feb 25, 2004 11:54 am

Unfortunately this doesn't work and I've failed to understand why. So:

The DBMS we're using is MS SQL Server 2000.
The JDBC driver is jTDS, a connection pool is set up in Tomcat's server.xml and distributed with JNDI - Hibernate has no problems connecting.

I get valid SQL queries from Hibernate in the logs and I can execute them successfully in Microsoft Management Console.

The error I get is "Invalid object name" as seen below and it occurs for any class that I specify the schema-attribute for in the hibernate mapping file.

Before reading on, is the JDBC-driver from jTDS insufficent since it implements JDBC 2.0 and not 3.0?
Are there any known issues with MS SQL and/or jTDS when using multiple schemas/catalogs?

Code:

14:07:27,965  INFO DatasourceConnectionProvider:51 - Using datasource: java:comp/env/jdbc/novaq
14:07:27,975  INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
14:07:28,165  INFO SettingsFactory:102 - Use scrollable result sets: true
14:07:28,165  INFO SettingsFactory:105 - Use JDBC3 getGeneratedKeys(): false
14:07:28,165  INFO SettingsFactory:108 - Optimize cache for minimal puts: false
14:07:28,165  INFO SettingsFactory:117 - Query language substitutions: {}
14:07:28,165  INFO SettingsFactory:128 - cache provider: net.sf.ehcache.hibernate.Provider
14:07:28,175  INFO Configuration:1080 - instantiating and configuring caches
14:07:28,426  INFO SessionFactoryImpl:119 - building session factory
14:07:30,829  INFO SessionFactoryObjectFactory:82 - no JNDI name configured
QUERY: FROM ReceiptGoods r WHERE r.documentNumber LIKE 'IL06209'
14:07:31,010 DEBUG SQL:237 - select receiptgoo0_.[No.] as y1_, receiptgoo0_.[Document Date] as y2_, receiptgoo0_.vendor as vendor from Attain.[NovAseptic AB Skarp$Purch_ Rcpt_ Header] receiptgoo0_ where (receiptgoo0_.[No.] LIKE 'IL06209' )
14:07:31,070  WARN JDBCExceptionReporter:38 - SQL Error: 208, SQLState: S1000
14:07:31,070 ERROR JDBCExceptionReporter:46 - Invalid object name 'Attain.NovAseptic AB Skarp$Purch_ Rcpt_ Header'.
14:07:31,070  WARN JDBCExceptionReporter:38 - SQL Error: 208, SQLState: S1000
14:07:31,080 ERROR JDBCExceptionReporter:46 - Invalid object name 'Attain.NovAseptic AB Skarp$Purch_ Rcpt_ Header'.
14:07:31,080 ERROR JDBCExceptionReporter:38 - Could not execute query
java.sql.SQLException: Invalid object name 'Attain.NovAseptic AB Skarp$Purch_ Rcpt_ Header'.
        at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(Unknown Source)
        at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(Unknown Source)

        at net.sourceforge.jtds.jdbc.TdsStatement.getMoreResults(Unknown Source)

        at net.sourceforge.jtds.jdbc.TdsStatement.executeCallImpl(Unknown Source)
        at net.sourceforge.jtds.jdbc.TdsStatement.internalExecuteCall(Unknown Source)
        at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(Unknown Source)
        at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(Unknown Source)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
        at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
        at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795)
        at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
        at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
        at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
        at net.sf.hibernate.loader.Loader.list(Loader.java:941)
        at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1512)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1491)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1483)

Thanks for any help.

emmanuel · **Posted:** Wed Feb 25, 2004 12:20 pm

What the hell is that name ?
NovAseptic AB Skarp$Purch_ Rcpt_ Header

did you set that in your mapping ?

fwendt · **Posted:** Wed Feb 25, 2004 12:44 pm

Thats the name of the table, comes from Navision, a Microsoft owned product. But If I just remove the schema-attribute in the mappings file it works, so the table's darn ugly name isn't the problem.

emmanuel · **Posted:** Wed Feb 25, 2004 12:51 pm

Try to ask jTDS guys, they will probably answer better than me.

bheineman · **Joined:** Sat Feb 14, 2004 1:36 pm **Posts:** 7

I think the issue may be with the way Hibernate is creating the query. You can follow the jTDS thread on this topic here:

http://sourceforge.net/forum/forum.php?thread_id=1029486&forum_id=104389

-Brian

bheineman · **Joined:** Sat Feb 14, 2004 1:36 pm **Posts:** 7

I believe I misspoke earlier. The problem most likely exists with the way the request is being made of Hibernate and not the way Hibernate is generating the query.

-Brian

fwendt · **Posted:** Thu Feb 26, 2004 5:23 pm

bheinman: Could you please explain? As you see in the exception-log above, the query printed by Hibernate looks ok and is runs as exptect, but when jTDS complains the query appears to be rewritten (if you judge by the exception's message).

fwendt · **Posted:** Thu Feb 26, 2004 5:30 pm

bheinman: I read up, sorry for being to fast here. But I still ain't sure what this implies:
- Have I misunderstood the typical usage of the schema-attribute?
- If so, is it because of me or is Hibernate suggesting a typical usage that either isn't widely supported or supported by MS SQL in particular?

emmanuel: Do you have any comment on this - I mean, how should I set up Hibernate to generate properly working queries?

jfifield · **Joined:** Tue Aug 26, 2003 3:09 pm **Posts:** 58

I'll take a shot. I'm not entirely clear if that whole thing is the actual table name, but when you use fully qualified names in SQL Server, you have to include the owner name as well: db.owner.table (the default owner is "dbo"). It doesn't look like there's an owner name included. I put db.owner in the schema attribute and it works fine.

bheineman · **Joined:** Sat Feb 14, 2004 1:36 pm **Posts:** 7

fwendt,

I am not a hibernate user myself so my knowledge of how it works is limited. However, as Alin mentioned on the jTDS forum; your problem probably stems from the fact that you are trying to reference a database "Attain" as a user. Instead of using the schema-attribute; is there a catalog-atribute you can use? I believe this is what you are looking for.

-Brian

fwendt · **Posted:** Sat Feb 28, 2004 11:56 am

First of all, thanks for all your help.

To summarize, this is what I get from all postings:

1) Hibernate's schema attribute available in the class element is meant to correspond to the JDBC "catalog", enabling O/R-mapping tables across databases/catalogs/schemas (choose whatever words suitable to your technique) - working over one DataSource.

2) MS SQL doesn't comply with the current naming translation - it expects you to specify tables with this syntax: [<schema/catalog/database>.<user>.]<table name>, where Hibernate produces [<schema>.]<table name>.

I've got it all working by putting "<schema/catalog/database>." (notice the dot/period) in the schema attribute but I'm pretty sure the Hibernate team agrees that it doesn't look very good. The resulting queries with attribute values like this, becomes <schema/catalog/database>..<table name>, which works well.

The question is, should jTDS add these dots as needed, or should the MSSQLDriver in Hibernate add the dot whenever the schema attribute is defined, or should it be in some other way? I feel that appending a dot manually in the schema attribute is a bad solution.

Once again, thank you all.

emmanuel · **Posted:** Mon Mar 01, 2004 12:23 pm

Please post that to JIRA, in order not to be lost