It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Execute a SQL on multiple resources using a scriptrunner macro

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 ->

 

1 answer

1 accepted

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.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Posted in Confluence

How is your team having fun and bonding, remotely, utilizing Confluence?

Thanks everyone for answering last week’s question. The winner of the random drawing from those who commented is: @LarryBrock I’ll contact you separately with your prize details. This wee...

333 views 9 7
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you