Can GEL support multiple database connections in one script?

Document ID : KB000028465
Last Modified Date : 14/02/2018
Show Technical Document Details

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


Solution:     
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:microsoft:sqlserver://sql-server-we-use:1433;DatabaseName=niku;SelectMethod=cursor"
  driver="com.microsoft.jdbc.sqlserver.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:microsoft:sqlserver://sql-server-we-use:1433;DatabaseName=niku;SelectMethod=cursor"
  driver="com.microsoft.jdbc.sqlserver.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.