search cancel

Unable to run GEL scripts against external databases when the setDataSource dbId is used.

book

Article ID: 242155

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

Unable to run GEL scripts against external databases when the setDataSource dbId is used.
If we try to pull information from a custom table on an external dataset, the query actually runs on the internal database and errors

STEPS TO REPRODUCE:
1. Go to the CSA > Server > Database
2. Under New External Connection, set a new connection to an external Clarity DB, let's call it testingDB
3. We have a custom table, "Test_Table" on external DB where we are querying the data using <gel:setDataSource dbId="testingDB"/>
4. Log in to the internal Clarity box where we modified the CSA
7. Administration > Data Administration > Processes
8. Create a new process with no object associated
9. On the Start step, add a script action that queries the custom table

Script as follows:
<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:core="jelly:core"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:sql="jelly:sql"
    xmlns:xog="http://www.niku.com/xog"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <gel:setDataSource dbId="niku"/>
 <core:catch var = "error">
  <sql:query var = "result">
  select *  from odf_ca_project where id = 5000001
  </sql:query>
 </core:catch>

 <gel:log> Error: ${error} </gel:log>
 <gel:log> Result: ${result} </gel:log>

 <gel:setDataSource dbId="testingDB"/>
 <core:catch var = "error">
  <sql:query var = "result1">
  select * from Test_Table
  </sql:query>
 </core:catch>

 <gel:log> ${error} </gel:log>
 <gel:log> Result: ${result1} </gel:log>
</gel:script>

10. Validate the process
11. Run the process

Expected Results: The process script to run successfully. The queries to run against the specified internal or external DB

Actual Results: The process script completes, but when looking to the details there is an error related to the query on the external DB: select * from test_table: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist

Environment

Clarity 16.0.1

Cause

This is caused by DE65135

Resolution

Use a script as follows:

<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:core="jelly:core"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:sql="jelly:sql"
    xmlns:xog="http://www.niku.com/xog"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <gel:setDataSource dbId="niku"/>
 <core:catch var = "error">
  <sql:query var = "result">
  select *  from odf_ca_project where id = 5000001
  </sql:query>
 </core:catch>

 <gel:log> Error: ${error} </gel:log>
 <gel:log> Result: ${result} </gel:log>

 <gel:setDataSource dbId="testingDB" var="connect"/>
 <core:catch var = "error">
  <sql:query dataSource="${connect}" var = "result1">
  select * from Test_Table
  </sql:query>
 </core:catch>

 <gel:log> ${error} </gel:log>
 <gel:log> Result: ${result1} </gel:log>
</gel:script>

Additional Information

The query that should run on the external DB actually runs against the internal one, that is why error saying the table does not exist. 
If the custom table is created also on the internal database, the script select query runs successfully. But it does run on the internal database, instead of the external one.
This used to work OK in Clarity 15.9 and is broken in Clarity 16