Thanks for taking the time to consider my question and posting such a
lengthy reply Rick. Luckily, I have found a way to force a default
schema for my project's hibernate mappings and get things going just
like I want them to be. For now anyway. Thanks again.
Andreas
Rick Hillegas wrote:
Hi Andreas,
I can see that for many applications it would be useful to have a
concept of a database-wide default schema, that is, a schema that all
users start out in when they get a fresh connection. That is not the
way Derby works. For Derby, the default schema is user-specific, not
database-wide. This is true even in the degenerate case of an embedded
application which does not run with authentication turned on: in that
case the username defaults to APP and the default schema is APP's schema.
The 2003 SQL standard does not offer any guidance on this topic.
According to part 2, section 4.37.2 (SQL-session identification), the
default schema of a session is vendor-defined.
I don't remember any deep reason about why Derby has this default
behavior. It may be nothing more complicated than the fact that the
original programmers were familiar with this default from previous
databases which they had worked on.
Some databases let you bind a default schema to a username. See for
instance http://msdn.microsoft.com/en-us/library/ms190387.aspx. I
think it would be straightforward to build such a mechanism for
Derby--although this would take you into session startup logic and the
devil could be in the details. I think there are plenty of people
around who understand that logic and who could coach a newcomer
through this project.
In the meantime, the only workaround I see is for your application to
force the schema to a default value when your application hands out
connections.
Please pardon me if I have talked past your real concern.
Hope this helps,
-Rick
