Do you know if there is any property that can be set to Other Properties in an Oracle JDBC connection in order to override the current schema?
To give you a context, our 10.7 platform is using CI&CD pipelines and we have a situation in which the schema for the tables where we have to insert data using adapter services is different per environment meaning that using <current catalog>.<current schema> works for instance on dev but in prod we would have to manually change <current schema> which is not ok. Therefore I was wondering if we can maybe set the schema to use somewhere in the JDBC Adapter connection settings. Do you know if this is possible?
current schema is always referring to the schema related to the user configured in the JDBC Adapter Connection.
As long as the connections are not part of the CI/CD, but created per environment before deployment, there should be no issues when sticking with current schema in the services.
On the other side there is an internal generated variable in the services $schema which can be used to specify a different schema during runtime.
Additional informations can be found in the JDBC Adapter Users Guide.
Hi,
I tried to use Other Properties currentSchema=MySchema;driverType=thin but it does not seem to work. This should also work for dynamic sqls? For instance if I want to do a query like:
select *
from myTable
This should work without having to specify the schema like:
select *
from MySchema.myTable
(myTable is not inside the schema of the User provided in the Connection Proprties)
For DataSource Class I got oracle.jdbc.pool.OracleDataSource
Unfortunately after setting this property, disabling and re-enabling the JDBC Adapter connection, when trying to query a table found in another schema it’s giving me that the table does not exist. If I manually set the schema in the query it works.
Hi,
The problem is that on dev and test the tables are inside the schema of the user specified in the JDBC Adapter connection but in prod they are found in a different schema. I will have a look in the official documentation to see if I can find some workaround.
I had a look in the latest documentation and saw that property currentSchema=YourSchemaName is depending on the DB driver and it is only shown for DB2 drivers and I have Oracle
I also found service pub.jdbcAdapter:updateServiceSchema but I was looking for something that might be visible somehow in the JDBC Adapter Connection. It does not make any sense that this service exists but there is no way of setting this in the JDBC Adapter connection admin page.
A bit off-topic, but I’d be interested in the rationale behind the DB using different schemas in different environments. What is the intended benefit of doing so?
Hi,
For the target dbs, the guys managing them are using a strange approach when they want to set up a new test environment. Instead of just creating a new database, they just clone the PROD schema in a new schema. So they basically have below setup:
DEV DB with schemas A, B, C, D, PROD
TEST DB with schemas A, B, C, D, PROD
PROD DB with PROD schema
A, B, C, D are just clones of PROD
This means, that we can expect to get requests like please repoint your JDBC connection on TEST to use D schema, and we do not want to manually set the schema in adapter services as this is against our CI&CD policy
We tried to make them change their approach but we did not succeed and now we are struggling to find a solution
The problem is that now we are in the UAT phase with our project and for such a change we would have to redo our functional tests. I will give it a try however.
Synonyms seem to be the least intrusive way to handle this. An additional object on the DB but not onerous and lets the JDBC services and connection pools remain as is (though there may need to a property to support synonym lookup for Designer support).
Needed only for dev environment to support getting column names for objects via synonym. At run-time, not needed so don’t necessarily need this setting in prod since one is unlikely to do column lookups in prod.
Yes, I thought about synonyms, but it’s still not that visible and it’s easy to overlook the step of repointing the synonym on DB level in case we are requested to change the connection on a specific environment.
This is kind of hinky, but your company seems determined to do things in a hinky way already, so…
In your Dev and Test databases, are you able to connect as the users A, B, C, D, PROD (to match the schemas)? And just can’t connect as “PROD” in Production?
If so, you can create synonyms in whatever user you are connecting to Production as. For non-prod, you can just change the username you use whenever they ask you to change connection to database X.
as long as the needed objects exist in the target database schema it should not matter if the schema is named A, B, or PROD as long as you specify the right user in the JDBC connection.
For our custom schemas, they are named the same across all databases, but for wM internal schema they follow a certain naming convention, but even for those we use “.” when we need to access them from our custom implementation for some reason. We only have to specify the appropriate user in the connection parameters and we do not have the neccessity to specify custom schema names anywhere except for custom sqls spanning more than one schema.
