In this post, we will see how to configure DBAdapter with Different Schema Names across Environments.
In our environment , we had a Database Integration. Access was through a common username (e.g “soadb”). Table (e.g INTDATA) that was holding the data was in different schema in each environment (e.g TSTDTA in Test, PRDDTA in Prod)
In Test, we need to do “select * from TSTDTA.INTDAT where.. ”
In Prod, we need to do “select * from PRDDTA.INTDATA where .. ”
DBAdapter creates two files when we use the wizard.
Properties file is not used in run time environment. So, we don’t have to worry about it much. Mapping file will have the information about table , fields & other information specified in the wizard.
If schema name used for connection and schema name accessed are equal, mappings.xml file will have reference to table as just “INTDATA”. However, if the schemas are not equal , as in our case, mappings.xml will have reference to table as “TSTDTA.INTDATA”.
As mappings.xml file is used during runtime, our composite will fail, if it is deployed as such in production. Reason being mappings.xml is referring to TSTDTA instead of PRDDTA. Further mappings.xml cannot be changed using configuration plans.
Different mapping files for different environments
Reference to table names will be in two places in mappings.xml file.
<class-mapping-descriptor xsi:type="object-relational-class-mapping-descriptor"> <class>DBREF.INTDATA</class> <alias>TSTDTA.INTDATA</alias>
<tables> <table name="TSTDTA.INTDATA"/> </tables>
Create Environment specific mappings.xml file with correct schema names in above mentioned places. Create Configuration plans & specify value of environment specific mappings.xml file in property “MappingsMetaDataURL”.
<binding type="jca"> <property name="MappingsMetaDataURL"> <replace>DBREF-or-mappings_PRD.xml</replace> </property> </binding>
Expose & Modify “TableQualifier” property
Another method to solve the issue is to expose and modify “TableQualifier” property.
- uncomment the following lines in META-INF\ra.xml present in DBAdapter.rar file and save it back in DBAdapter.rar file.
<config-property> <config-property-name>tableQualifier</config-property-name> <config-property-type>java.lang.String</config-property-type> <config-property-value></config-property-value> </config-property>
- Restart the server
- Provide value for “TableQualifier” in DBAdapter’s connection factory used to connect to database. In our case, for test, it will be TSTDTA; for prod it will PRDDTA. Save and Update “DBAdapter” for changes to take effect.
- Remove references of Schema name (TSTDTA/PRDDTA) in mappings.xml. Ensure only table name is mentioned.
- Deploy the composite and test to make sure it connects to correct schema in each environment. During run-time, table name will be qualified with value specified in “TableQualifier” property.
In our environment , we went with first solution.
If second solution is implemented
- Ensure mappings.xml is modified to have schema reference before DBAdapter wizard is opened in Jdeveloper.
- Ensure schema references are removed in mappings.xml before the composite is deployed.