External Database Connection with cross databases type

Document ID : KB000097135
Last Modified Date : 29/05/2018
Show Technical Document Details
Introduction:
After configuring an External Database Connection to Service Desk Database (ORACLE) through NSA console; can I  execute a GEL script test this connection?



 
Environment:
Windows 2012 with MSSQL 2016 as PPM 15.4 database
Windows 2012 with ORACLE 11.0.2 as Service Desk Manager database
Instructions:
Example: Read a table on SDM (ORACLE) from PPM; executing a GEL script fired a PPM process. 


<gel:script    
xmlns:core="jelly:core" 
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" 
xmlns:sql="jelly:sql">

<gel:parameter default="svong" var="ClarityUser"/> 
<gel:parameter default="svong" secure="true" var="ClarityPassword"/>

<sql:setDataSource url="jdbc:oracle:thin:@//<ORACLE_HOST_NAME>:<PORT#>/<ORACLE_SERVICE_NAME>" driver="oracle.jdbc.driver.OracleDriver" user="${ClarityUser}" password="${ClarityPassword}"/>

<!-- read category table from CA SDM server--> 
<sql:query var="result">
select * from mdbadmin.prob_ctg 
</sql:query> 

<!-- By Index --> 
<core:forEach trim="true" items="${result.rowsByIndex}" var="row"> 
<gel:out>Resource Name: ${row[0]}</gel:out> 
</core:forEach> 
</gel:script>

Once this gel script is executed successfully, you can execute the External Database Configuration correctly.
Additional Information:
If you get an error like the one below:

Calling Gel Script gives error: BPM-0704: An error occurred while executing custom script: 

org.apache.commons.jelly.JellyTagException: null:3:86: Unable to get connection, DataSource invalid: "No suitable driver found for SDM" 
at org.apache.commons.jelly.tags.sql.SqlTagSupport.getConnection(SqlTagSupport.java:156) 
at org.apache.commons.jelly.tags.sql.QueryTag.doTag(QueryTag.java:134) 
at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:247) 
at org.apache.commons.jelly.impl.ScriptBlock.run(ScriptBlock.java:95) 
at org.apache.commons.jelly.TagSupport.invokeBody(TagSupport.java:186) 
at com.niku.union.gel.tags.ScriptTag.doTag(ScriptTag.java:20) 
at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:247) 
at com.niku.union.gel.GELScript.run(GELScript.java:67) 
at com.niku.union.gel.GELController.invoke(GELController.java:74) 
at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:207) 
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) 
at java.lang.Thread.run(Thread.java:748)

Please review the configuration on NSA console and the GEL script created.
NOTE: To connect to an Oracle database from a CA PPM (in this example installed with SQL Server) we need to use the Oracle service name.