Hi there,

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?

Br,
n23

Hi

It depends on the type of driver you are using.

If you are using oracle drivers then you should use - ‘currentSchema=myschema’ in other properties.

If you are using Data Direct drivers then you can use InitializationString as "ALTER USER user_name SET search_path to ‘schema’‘’

https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/7604ab7513ca8628d7981983896a69dae28baf59.png 1.5x, https://higherlogicdownload.s3.amazonaws.com/IMWUC/webMethods/Files/7604ab7513ca8628d7981983896a69dae28baf59.png 2x" data-dominant-color="F4F4F4">

image675×657 123 KB

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

currentSchema=myschema should definitely work.

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.

You should try this.

I have driverType=thin. I added the InitializationString as advised but I think it’s ignoring it altogether.

Hi

You should use data direct drivers shipped out of the box to get this working. not Ojdbc driver.

For Oracle the driver class is “com.wm.dd.jdbcx.oracle.OracleDataSource”

https://documentation.softwareag.com/webmethods/adapters_estandards/Adapters/JDBC/JDBC_10-3/10-3_Adapter_for_JDBC_webhelp/index.html#page/jdbc-webhelp%2Fta-config_adap_jdbc_conn.html%23

image811×405 24.6 KB

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).

(edit): Example otherProperties: url=jdbc:oracle:thin:@//myserver.com:1521/my.service.com;driverType=thin;connectionProperties={includeSynonyms=true}

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.

Hi,
This is precisely the issue I am encountering. On dev and test I can connect as user A, B, C, D and PROD but in prod it seems that user PROD is way too powerful which is why I cannot use it. Unfortunately this solution using synonyms is not that liked by my client as it is deemed as a last resort solution and I am being asked to find another solution which does not entail any change on db level since the DB is not managed by us which are managing only the middleware part.

n23

Do they have specific concerns about synonyms beyond “don’t like” or “last resort?” IMO, changing the wM IS JDBC connection pool all the time is the more risky proposition.

This would be just a connection change - no adapter service changes required.

Hi,

just for understanding:

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.

Regards,
Holger

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.

n23

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:

1. DEV DB with schemas A, B, C, D, PROD
2. TEST DB with schemas A, B, C, D, PROD
3. 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

If you’re using Oracle DB, could you create synonyms in your prod user’s schema?

Hi,

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.

Regards,
Holger