Hi,
I used Hibernat 2.1 beta 3 to build my project with java 1.4.2 and mysql 3 with multischema databases and now when I upgrade Hibernate to final version there are problem with schema resolver.
I have in hibernate properties default schema set to "repository" and in other classe the schema is in xml definition set to "data_report".
With Hibernate beta version all works ... after the upgrade when use entity with nodefault schema , Hibernate create the SQL with "defaultschema"_tablename ... and not "defaultschema".tablename returning a SQL error.
The "_" is wrong !!!!!!!!!!!!!!!!!

I red in Changes in version 2.1 beta 4 (3.10.2003) ..
- support schema attribute in MySQL, by using an underscore in the table name (Chris Hane)


Any suggestion or help ?
Thanks a lot for all

What do you mean by "mutischema database". Apparently, MySQL 3.22 introduced the ability to query a different "database" by using a qualified name. Is this what you mean?

Anyway, you can get the old behavior back by simply overriding getSchemaSeperator() on a subclass of MySQLDialect to return a ".".

Yes, I patched the source and now work all .. thank.
The SQL created is malformed for all version of Mysql I think or not ?
I use mysql 3 and 4 (in Oralce and SQL Server it does not work too) and this SQL does not work.
The answer is .... Why the "_" is used ??

If you run SchemaExport, it creates tables name schema_table. This patch was proposed by a user, since MySQL has no concept of a schema.

So its all quite internally consistent. Its just different to what *you* were using the schema attribute for.

Ok now I understand the "fix".
The schema concept is different in Mysql from Oracle I know ... I call "schema" incorrectly another database used by the same DB engine.
Now the problem is fixed for SchemaExport but is "born" for "normal use"

I am the first developer reporting this "problem" ??
For other developer in the future is possible create a doc or a note for Mysql recompile or another solution was reported / created ??

This happens when use specify a blank schema with hibernate.default_schema=

Hi,

I observed the same problem with hibernate 2.1.1 and mySQL 4:

If I specify a schema name in the xml mapping descriptor file,
hibernate generates table names of the form
<schema name>_<table name> (eg. with the hbm2ddl tool).

Since mySQL has no schema concept, I normally use different database
names within the same mySQL instance for a schema replacement.

The hibernate way of "simulating" schematas in mySQL by qualifying
the table names within the same database by using
<schema name>_<table name> is also a possible solution,
but I also want the possibility to use the schema name as database name.

-->

A possible solution is:
- Add a property to the hibernate configuration file to specify
the schema separation charakter, eg.
"schema.separation.charakter=..."

This seems to me the best solution, since changing the schema
separation charakter by patching the code is more difficult.

Is it possible to do this a new release of hibernate?

thx,
Juergen

PS: A Quote from the community wiki of hibernate (database section):

>> mySQL
>>
>> Consider setting schema name to database name however with
>> Hibernate 2.1B4 and later you must override the MySql dialect
>> schema seperation character to dot rather than underscore

Can anybody add a short description how to do this?

It is actually very easy:


Then use this as Dialect.

Really, "database" and "schema" are VERY different concepts.

I am considering adding a notion of "catalog" (I think that is the ANSI name for the concept of a database), since some dbs like SQL Server allow double-qualified names.

Thanks a lot for your comments (I can live with that solution)

Juergen

I found spelling error - incorrect method is overriden getSchemaSeperator(). Instead getSchemaSeparator() method should be overriden.


Then use this as Dialect.[/quote]