Hi

I am trying to setup a global schema for all queries. At first glance, it appearred it was very easy to do by setting the schema in the mapping as follow. But it does not work properly.

<hibernating-mapping schema="ABC">
<sql-query>
Select field from tableA
</sql-query>
</hibernating-mapping>

This query will not use ABC.tableA, but it uses the schema assoicated with the userID instead.

I also tried using the schema in Hibernate's properties file, but no luck, same problem.

Please advise.

Rich

I believe that that schema is only used by schema-generation tools. If you want it in a named query, you'll either have to create a new session factory with the correct configuration, or manually put the schema in the queries, tables etc. that are to use the other schema.

It does not sound right. How come the schema needs to be associated with each table name. It seems pretty easy to implement a global schema! Correct me if I am wrong.

Richard

If it's a truly global schema, that all your tables, stps, etc. are in, then that's specified by your user's login. If you've got two tables, dbo.Test and jdbc.Test, then "select * from Test" will return the contents of dbo.Test for all users except jdbc. jdbc would get the results from jdbc.Test (which is a completely different table).

I fell into the trap of thinking that the schema was like a folder or directory, and I think that you're doing the same thing. In fact, a schema is more like an ACL, a permission list specifying who can see the objects in the schema. So there's no way to "change into" or "start using" a specific schema: if you don't mention the schema, the DB looks for <youruser>.<object>; if it can't find it, it looks for <dbowner>.<object> or whatever. Specifying the schema (e.g. "select * from dbo.Test", means "select * from Test as if I was the dbo user".

Some databases, including Oracle and SQLServer2005, don't impose the schemaname = username restriction, but the idea behind them is the same. They're closer to permissions than folders.

I see where you are coming from and I agree that a schema is like a ACL.
In fact, that was my assumption until I startED working with DB2 running on a mainframe. They do not have a default schema associated with IDs.
They have different schema in dev, sys, prod etc., To cut a long story short, its so weir.
I just want to be able to change the schema on one spot within a file. And I find it hard to believe it can not be done!

The end result of doing it globally would be to prefix every sysobject with "<schema>.". There is no DB command that says what schema you want to use: if you want to change your default schema, you log out and log back in as the correct user (except maybe in DB2, apparently). Hibernate doesn't support the idea of a global prefix to all tables, views, stored procs, etc.: it's just not how most DBs are designed.

I'm afraid that your best option is to update all of your 'table="<tablename>"' strings to 'table="<schema>.<tablename>"'. Sorry.

Actually, in Hibernate 3.0.5

You can specify a global prefix to all tables.

In your hibernate.cfg.xml

set the property:hibernate.default_schema
to your schema or owner

We are using DB2 7.1 on z/OS and we had to do this because of how DB2 organizes databases and tables.

Good to know. I'd thought that that was the property that hiberni was referring to in his first post:

So I'd discounted its usefulness. I guess that's another example of me ASSuming too much.