Execute a SQL on multiple resources using a scriptrunner macro

[Filip Labarque]

Hi,

How can a SQL using multiple resources be executed in a scriptrunner macro?

E.g. now I have a macro like this:

def rows = DatabaseUtil.withSql('<resourcename>') { sql ->

sql.rows("SELECT <row> FROM <table>")

Which works.

But what if I would like to execute a SQL like "SELECT <row> FROM <db1.table> INNER JOIN <db2.table>"? Can I create both resources can execute the SQL like:

def rows = DatabaseUtil.withSql('<resourcename1>, <resourcename2>') { sql ->

[Leonard Chew · Accepted solution]

If you are using Oracle Databases, I would create a DB-Link on the Database A to the Database B.

https://www.oracletutorial.com/oracle-administration/oracle-create-database-link/

In the sql query on Database A, you can then join the remote tables of Database B.

E.g.

select *
from mylocaltable a
join myremotetable@databaseB b on (a.mykey = b.myotherkey)

I suppose other databases can do the same with different Syntax.

Using database Links you only need one scriptrunner resource.

If you want to use two resources in scriptrunner, you will have to achieve your goal differently than executing one single SQL Statement on tables wich are on two different databases.