Using DBAdapter with Different Schema Names across Environments

In this post, we will see how to configure DBAdapter with Different Schema Names across Environments.

Scenario

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 Files

DBAdapter creates two files when we use the wizard.

{DBRefName}-or-mappings.xml  
{DBRefName}-properties.xml.

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.

Solution

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.
DBAdaper Different Schemas
TableQualifier Property
  • 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.

4 thoughts on “Using DBAdapter with Different Schema Names across Environments

  1. Hi
    Oracle has a Document describing the first solution when we know it can also be done by using the config plan and specifying a different mappings file based on the environment specific deployments. However. i wanted to understand which is the preferred solution? If we do it in Weblogic, it requires less number of files in the code ( one mapping file for each environment), developer specific configuration, implementing the same workaround in different BPEL processes as it’s a one time activity per SOA Environment.
    So, which one would you recommed ?
    Any ideas would be highly appreciated

    1. Abhinav,
      If we go with “TableQualifier”, as you have mentioned, it is less number of files to manage in the source code. This approach is best , if there are no more modifications expected in DBAdapter files.

      We went with first approach , as we had few iterations of changes with respect to DBAdapter. Every time adding schema name to open in JDeveloper and subsequently removing to deploy was error prone.

      Regards
      Krishna

Leave a Reply

Your email address will not be published. Required fields are marked *