Problem using Hibernate and SQL Server 2008

Question

I'm having problems using Hibernate and SQL Server 2008. When I try to save an object to database Hibernate throws this:

could not retrieve snapshot: com.my.MyClass

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name `'emanagement.patient_visit'.`

The user has select, insert,update privileges in database. So I ruled that problem out.

This is the SQL generated:

select
        patientvis_.account_number,
        patientvis_.status as status1_,
        patientvis_.cpt_code as cpt3_1_,
        patientvis_.locked as locked1_,
        patientvis_.state as state1_,
        patientvis_.userid as userid1_ 
    from
        emanagement.patient_visit patientvis_ 
    where
        patientvis_.account_number=?

If I run the above SQL in SQL Server it says invalid object name emanagement.patient_visit, but if I manually add that "dbo" emanagement.dbo.patient_visit than it will get exsecuted.

So is there any other Hibernate configuration I need to make?

This is my Hibernate mapping. The below mapping works under MySQL. I can read and update patient_visit in database. But when switching to MS Server it fails. I have tried other hibernate mappings which work for both MySQL and MS Server and they both use the same declarations as below like table="db_table" schema="my_database". The only difference is that I created this new emanagement database under MS Server, so I'm thinking that I missed some specific database configuration on the MS Server management tool. The only way to prove this is for me to move the new tables from emanagement to an existing database and see if it works.

<class name="com.domain.patient.model.PatientVisit" table="patient_visit"    schema="emanagement">
        <id name="accountNumber" type="java.lang.Long">
            <column name="account_number" precision="22" scale="0" />
            <generator class="assigned"/>
        </id> 
        <property name="status" type="string">
            <column name="status"/>
        </property>
        <property name="cptCode" type="string">
            <column name="cpt_code"/>
        </property> 
        <property name="locked" type="boolean">
            <column name="locked" precision="1" scale="0"/>
        </property>  
        <property name="state" type="string">
            <column name="state"/>
        </property>  
        <property name="userId" type="string">
            <column name="userid"/>
        </property>  
               <set name="documents" lazy="false">
            <key column="account_number"/>
            <one-to-many class="com.domain.document.model.Document"/>
        </set>     
    </class>

Thanks in advance.

score 4 · Accepted Answer · edited May 23 '17 at 11:53

4

So is there any other Hibernate configuration I need to make?

With your current setup, I guess you'll have to specify the schema. For example, in the mapping:

<class name="Users" table="Users" schema="dbo" catalog="suiteaccess">

But you can also specify the default schema using the hibernate.default_schema property (see 3.4. Optional configuration properties).

Just in case, you can create your own schema.

edited May 23 '17 at 11:53

Community

1
1

answered Jul 01 '10 at 16:45

Pascal Thivent

562,542
136
1,062
1,124

1

To build on Pascal's answer, it looks like you may have specified the database name where the schema name should be in your configuration. Most ORMs I've seen don't use a 3 part naming scheme for access, so I have to imagine this is a misconfiguration. – Anon246 Jul 01 '10 at 17:12
1

Its weird cause I have other hibernate object mappings they are are saved correctly to SQL Server. But for those I'm using Spring Framework. – Marquinio Jul 01 '10 at 18:06
@Strommy @Marquinio It would be interesting to see the hibernate configuration (and maybe the mappings for two representative entities). – Pascal Thivent Jul 01 '10 at 18:15
I posted the hibernate mapping thats causing problem in MS Server. I know that this same hibernate mapping style also works for MS Server when working on different project. – Marquinio Jul 01 '10 at 18:48
@Marquinio: As I wrote, `schema` should be `dbo`, `emanagement` is the `catalog`. – Pascal Thivent Jul 01 '10 at 19:25
Ok guys thanks that worked. But what if I need my app to connect to MySQL? Then the schema="dbo" will not work for MySQL. Also on another application I'm able to load object from MS Serverdatabase without having to use the schema="dbo". How is that possible? Could it be because in this environment I'm using Spring Framework? – Marquinio Jul 01 '10 at 21:13
@Marquinio: I'd say that these settings are environment specific. So put them at a global level (i.e. use the `hibernate.default_schema` properties and so on) and change them accordingly if required when moving from one environment to the other. – Pascal Thivent Jul 01 '10 at 21:17

score 0 · Answer 2 · edited May 16 '13 at 12:16

0

Put below configuration into mapping file. It resolved the Invalid object exception.

<class name="Users" table="databaseName.dbo.Users">

Change the above line into Mapping file. It should be work. Because Mssql server have default schema (dbo). That's why use the fully classified table name.

edited May 16 '13 at 12:16

Jesse

8,605
7
47
57

answered May 16 '13 at 11:53

Murali

1
1

Please remember to look at the preview before posting. If you add code you should add 4 spaces before the code. And welcome to the Stack hope you'll like it. – CodeTower May 16 '13 at 12:16

score 0 · Answer 3 · answered Jul 01 '10 at 18:58

Change the schema to dbo. In SQL Server, schema is a container in the database, not the database itself. This may be different in MySQL, but in this case, your schema should be dbo (based on your comment about it working if you add "dbo." to your resultant query.

Thanks, Eric

Problem using Hibernate and SQL Server 2008

3 Answers3

Linked