Multiple database connections in one Gel Script

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

I need to connect to both CA PPM Database and external databases. How can I accomplish this using GEL?

Resolution

You can define multiple datasources first, then specify which one to use in each query later by nominating "var". Or, you can set datasources one by one when it's used. Both of the following chunks of code work, with same result and performance cost.  

<gel:setDataSource dbId="niku" var="a"/>
<sql:setDataSource url="jdbc:sqlserver://sql-server-we-use:1433;DatabaseName=niku;SelectMethod=cursor"
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
  user="cappm"
  password="our-password" var="b"/>

<sql:query var="results" dataSource="${a}">
  select count(1) from cmn_sch_jobs
</sql:query>
<gel:out>${results.rows[0]}</gel:out>

<sql:query var="results" dataSource="${b}">
  select count(1) from cmn_sch_jobs
</sql:query>
<gel:out>${results.rows[0]}</gel:out>

=== OR ===

<gel:setDataSource dbId="niku"/>
<sql:query var="results" >
  select count(1) from cmn_sch_jobs
</sql:query>
<gel:out>${results.rows[0]}</gel:out>

<sql:setDataSource url="jdbc:sqlserver://sql-server-we-use:1433;DatabaseName=niku;SelectMethod=cursor"
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
  user="cappm"
  password="our-password"/>
<sql:query var="results">
  select count(1) from cmn_sch_jobs
</sql:query>
<gel:out>${results.rows[0]}</gel:out>

No matter which way you choose, you cannot execute a query on multiple databases. If your script needs to switch between databases frequently, using the first method will make your code looks better. If databases are used one by one, using the second make the code a little shorter. Internally, they are all same.

Additional Information

If an encryption related error is obtained, the following can be used against the "url" attribute of the "sql:setDataSource" tag:

url="jdbc:sqlserver://sql-server-we-use:1433;DatabaseName=niku;SelectMethod=cursor;encrypt=false"