[prev in list] [next in list] [prev in thread] [next in thread] 

List:       forgerock-openidm
Subject:    [OpenIDM] =?iso-8859-1?q?Custom_schema_name_for_JDBC_Repository_?= =?iso-8859-1?q?=28dbSchema=29_pos
From:       "=?iso-8859-1?Q?Wolfram=2C_Thomas?=" <thomas.wolfram () tik ! uni-stuttgart ! de>
Date:       2016-10-07 15:46:45
Message-ID: zarafa.57f7c365.12f7.47d64b871330ec88 () mail-znode-ma1 ! rus ! uni-stuttgart ! de
[Download RAW message or body]

Hi,

there is an open request titled "Allow custom schema for JDBC Repository"
(https://bugster.forgerock.org/jira/browse/OPENIDM-5340)

With the following description and comment:

Quote: " Description: Currently JDBC Repository silently assumes that database schema \
equals the database name. It would be nice if there is a way how to choose a \
different schema name. With the current init SQL scripts, it makes sense to reuse \
openidm schema regardless of the database name. Otherwise if you create openidm_test \
database, you would need to update the init scripts or tamper with schema when \
transfering data from a different database."


Quote: "(Comment) Pavel Horal added a comment - 03/Mar/16 2:31 PM
Found out, I can change databaseName to match the schema, because the real database \
name is contained within jdbcUrl. So the following data source configuration is \
valid: {
    "driverClass" : "org.postgresql.Driver",
    "jdbcUrl" : "jdbc:postgresql://localhost:5432/openidm_test",
    "databaseName" : "openidm",
    "username" : "openidm",
    "password" : "openidm",
    "connectionTimeout" : 30000,
    "connectionPool" : {
        "type" : "bonecp"
    }
}

Not sure if this is a valid configuration and if so, this setup is not apparent from \
the documentation:
> "databaseName"
> The name of the database to which OpenIDM connects. By default, this is openidm.
So this improvement might be just about rephrasing documentation."
(Ende quote)


I have the same problem/requirement like Pavel for DB names and schema names. I need \
multiple copies of the reposiory hosted on the same SQL server and I would like to \
avoid create a unique schema per copy by editing always the init scripts.

But Pavels workaround does not appear to work for a Microsoft SQL Server JDBC \
datasource. If I try to set the DB name in the JDBC url only ("openidm_dev" in my \
case) and set the parameter "databaseName" to the schema "openidm" like Pavel \
suggests I get an exception: "WARNING: JDBC Repository start-up experienced a failure \
getting a DB connection: Unable to open a test connection to the given database. JDBC \
url = jdbc :sqlserver://xxx.xxx.xxx.xx:2811;databaseName=openidm_dev;applicationName=OpenIDM&useSSL=true, \
username = openidm_dev. Terminating connect ion pool. Original Exception: ------
com.microsoft.sqlserver.jdbc.SQLServerException: Database 'openidm' does not exist. \
Make sure that the name is entered correctly. at \
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
 at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:279)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:99)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:36)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:2494)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2444)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:2499)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.setCatalog(SQLServerConnection.java:2850)
 [...]"

when I start OpenIDM. I checked which SQL commands OpenIDM is sending to the SQL \
server using Microsoft SQL Profiler and I saw it starts by sending a "use [openidm]" \
SQL statement which obviously fails because the DB is called "openidm_dev".

Also I found that there appears to exist a parameter "dbSchema" for the JDBC \
repository configuration (according to \
https://wikis.forgerock.org/confluence/display/openidm/JDBC+Repository). I tried to \
put that into datasource.jdbc-default.json and repo.jdbc.json but to no avail.

Also I tried to remove the "databaseName" attribute completely from \
datasource.jdbc-default.json. But this just throws other exceptions: "WARNING: JDBC \
Repository start-up experienced a failure getting a DB connection: Unable to open a \
test connection to the given database. JDBC url = jdbc \
:sqlserver://xxx.xxx.xx:2811;databaseName=openidm_dev;applicationName=OpenIDM&useSSL=true, \
                username = openidm_dev. Terminating connection pool. Original \
                Exception: ------
com.microsoft.sqlserver.jdbc.SQLServerException: Database 'openidm' does not exist. \
Make sure that the name is entered correctly. at \
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
 at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:279)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:99)

and:

SEVERE: Bundle: org.apache.felix.scr [14] FrameworkEvent ERROR
org.apache.felix.log.LogException: java.lang.RuntimeException: \
org.forgerock.json.resource.BadRequestException: Missing entry in params passed to \
query for token _dbSchema"

Is there any way to specify the JDBC schema name?


Thanks,
Thomas


--
Dipl.-Ing. Thomas Wolfram

Informations- und Kommunikationszentrum der Universität Stuttgart (IZUS)
Technische Informations- und Kommunikationsdienste (TIK)
Netze und Kommunikationssysteme

Allmandring 30a
70550 Stuttgart

Tel: +49 711 685-65812
Fax: +49 711 685-55812
Web: www.tik.uni-stuttgart.de



_______________________________________________
OpenIDM mailing list
OpenIDM@forgerock.org
https://lists.forgerock.org/mailman/listinfo/openidm


[prev in list] [next in list] [prev in thread] [next in thread]