[jboss-user] [Installation, Configuration & DEPLOYMENT] - Re: Howto move jbm and quartz tables to postgresql schema?

[PeterJ]

I am sorry, it appears that I have completely misunderstood your original question. If I now understand correctly, you are asking how to get JBoss AS to use a schema for its tables, is that correct?

Hibernate has the luxury of construction SQL statements on-the-fly and thus can support the syntax needed to specify the schema name in every SQL statement.

It would seem to me that the ideal location for specifying the desired schema would be in the connection URL. But I could find no such option in the PosgreSQL JDBC driver documentation. One would think that one of these options would be allowed:

jdbc:postgresql://host:port/database/schema

or

jdbc:postgresql://host:port/database&schema=xxx

But it seems that neither is allowed.

One of your options is modify all of the SQL statements for messaging and quartz to include the schema name. (Yes, I know it is ugly.)

But I just had an idea. According to the PostgreSQL documentation, you can specify a schema search path using syntax like:

SET search_path TO xxx,public;

Once that is done, PostgreSQL will look in schema xxx first. One way to set this is by specifying a check-valid-connection-sql entry in you *-ds.xml file:

<check-valid-connection-sql>SET search_path TO xxx,public;</check-valid-connection-sql>

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4206311#4206311

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4206311