Hi Guys

This is a formulisation of an earlier post on failing insert but more specific...

Basically I have found that Hibernate is sending SQL calls (inserts for example) as schemaName.tableName.

I tried this in SQL Server and it is invalid. SQL Server expects

schemaName.owner.tableName

where owner is pretty much always "dbo".

We've not had this problem before using ouir JDBC JNDI resources since normlly we call connection.setCatalog(schemaName) and calls work after this because JDBC will not prepend statemens with schemaName thereafter.

Is there any way to turn off the prepending of schemaName since this is already set by the JNDI resource anyway. Or is there any other solution?

Thanks!

Well if you don't specify any schema anywhere, hibernate will not prepend it ...

OK, I am not telling Hibernate to use a schema. The only place the schema occurs is on the JDBC connection URL. Because I have to tell the connection _somehow_ which schema to use. We have about 15 databases, so Hibernate still has to switch to the right database schema, it can't just issue a straight table call because different schemas may have the same table names.

Like I say, there is not a problem with general connection.setCatalog method in JDBC .. it seems to be that Hibernate is issuing the schema name from the JDBC JNDI connection URL, but if I remove that, Hibernaet will not know _at all_ which schema to use!!

thanks :)

Okay, some ideas:

- Hibernate3 will provide not only the possibility to configure the schema, but also the catalog.
- do you need one schema/catalog per SessionFactory? Consider building your own ConnectionProvider which calls setSchema(), setCatalog() before handing out the connection.
- You can still do session.connection().setSchema() ...

OK well I did the following to solve it ... instead of



i used



And the output of Hibernate's calls are now without Schema (but I do session.connection().setCatalog("dbSchedules") in my code.

Hibernate: insert into Schedules (status, title, summary, description, created, last_updated) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into DataAcquisitionSchedules (productName, productRelease, schedule_id) values (?, ?, ?)

I cannot understand why specifying the JNDI datasource causes Hibernate to add the schema onto the sql statements yet using Hibernate's internal connection settings removes it. The settings are identical in their values. Is this intended behaviour?

Cheers, ADC

I find that quite hard to believe ... the ConnectionProvider surely does not affect the sql query generation.

No, but I very much doubt it is actual behavior either. I expect you are mistaken.

B.T.W. guys, it is probably possible to do schema="foo.bar" in Hibernate2.

I'm not a total novice in these matters, but I appreciate I may be providing you with a red herring certainly.

All I know for sure is :

Using a Tomcat JNDI Resource and telling Hibernate to use the dataSource shows that Hibernate prepends all SQL statements with the schema name. I infer from ths that Hibernate is somehow getting the schema name from the URL connection string itself because I specify is absolutely nowhere else!

I now remove the dataSource method and specify the connection settings for Hibernate instead and still using the very same URL connection string. Hibernate now does not prepend statements with schema name.

These 2 items I have tested and know to be true.

Negative. You are mistaken.