Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dynamically setting schema name for native queries in hbm file

 
Niraj Gadgilwar
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ,
inside my hibernate.cfg.xml I am mentioning
hibernate.default_schema as schema name (say "testSchema") and it is working with named queries and HQL as expected.But when I am using a native sql query ,say

String queryString= 'select field1 from table1 where field2=100';
SQLQuery queryObj=session.createSQLQuery(queryString).
addScalar("field1 ",Hibernate.STRING);

In that case the schema name(i.e. testSchema)is not getting appended to the table names.
As a work around I had to add the schema name name before the table names.
So the changed code is ,
String queryString= 'select field1 from testSchema.table1 where field2=100';
.....

But it is not the correct approach I guess.If there are 100 queries
and in future my schema name changes, I have to change the schema name at 100 places.
Can anybody provide me the correct implementation ?
 
Niraj Gadgilwar
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I also tried the same thing by

<sql-query name="myQuery">
<return-scalar column="NAME" type="java.lang.String"/>
<return-scalar column="SURNAME" type="java.lang.String"/>

SELECT NAME,SURNAME FROM ${hibernate.default_schema}.TBEMPLOYEE
</sql-query>

and hibernate.default_schema value is mentioned in configuration file.
I tried this for Hibernate version 3.3.0 and 3.3.2. But it is not working.

Do anyone know correct syntax?
 
sarmaP lolla
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are writing the native SQL then hibernate just executes that sql... it would not bother to look at the schema.

Sarma
 
Niraj Gadgilwar
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sarma,

Actually I am trying to write SQL in .hbm file and my requirment is to access the table with schema name.
I have mentioned the default schema name in .cfg file of hibernate.
<property name="hibernate.default_schema">TEST_SCHEMA</property>
and now I want to use this default schema in my .hbm file.

<sql-query name="myQuery">
<return-scalar column="NAME" type="java.lang.String"/>
<return-scalar column="SURNAME" type="java.lang.String"/>

SELECT NAME,SURNAME FROM ${hibernate.default_schema}.TBEMPLOYEE
</sql-query>
But this is giving me an error.

I tried following query also
SELECT NAME,SURNAME FROM :{hibernate.default_schema}.TBEMPLOYEE

But it is giving me "Not all named parameters have been set: [{hibernate.default_schema}.TBEMPLOYEE] [SELECT NAME,SURNAME FROM :{hibernate.default_schema}.TBEMPLOYEE]" error.

Now I am not getting how to access .cfg property in my .hbm file.

Niraj
 
sarmaP lolla
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is what help says....
DefaultSchema: This option sets a schema or tablespace name used to qualify unqualified tablenames in generated SQL.

so did you define your mapping file like shown below? In that case this overwrites your default....

<hibernate-mapping default-lazy="false">
<class name="mypackage.domain.Mytable" schema="MY_SCHEMA" table="MY_TABLE">

Also looks like you can not access DefaultSchema from the config file...

Sarma

 
Niraj Gadgilwar
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sarma,

I tried to define schema in hbm file. But still it doesn't work for me.

<class name="hibernate.Contact" schema="TEST" table="TBEMPLOYEE" >
and query is

SELECT NAME,SURNAME FROM TBEMPLOYEE

I am still getting exception like "could not execute query"......
 
Niraj Gadgilwar
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

It is finally started working for me.

I downloaded Hibernate V3.5.3 and used {h-schema} tag before my table name and it started working.

SELECT * FROM {h-schema}TBEMPLOYEE

This feature is not available in V 3.2.x or 3.3.x. You should use V3.5.3 or higher to get it worked.

Thanks...
 
Niraj Gadgilwar
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

It is finally started working for me.

I downloaded Hibernate V3.5.3 and used {h-schema} tag before my table name and it started working.

SELECT * FROM {h-schema}TBEMPLOYEE

This feature is not available in V 3.2.x or 3.3.x. You should use V3.5.3 or higher to get it worked.

Thanks...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic