Caveats when using Script runner Scripted Fields for fetching data from external databases?

[Monika Danielsson]

I have a scripted field fetching data from an external database. This works nicely, but before taking it live, I thought I should check if there are any obvious problems (like using up all memory, opening too many connections to the db, whatever), I should be wary of. 

I do use the Database Custom Field plugin (Power Database Fields nowadays) for my normal interactions with the external database, but in this case, I want the current value from the database, not what was saved for the issue the last time, and I don't manage to get that with the DBCF, so I decided to try Script Runner. 

I'm making sure I close my db Connection and my Sql in the fetch function, but I'm still worried that something might not be cleaned up properly. If anyone knows any reason why I should stay away from this solution, feel free to point it out. 

[Daniel Yelamos [Adaptavist] · Accepted solution]

Hi Monika.

Without seeing your code, there isn't much that I can tell you. As far as I know, so long as you close the DB connections that you open, and so long as you only fetch and not write, you should be in the clear. 

Do you have a test instance that you can stress test it for?

Cheers!

Dyelamos

[Monika Danielsson]

Hi,

Thanks for the reply! Yes, I'm only reading from the external database, and I close the connections , so then it sounds reasonably safe. 

Main parts of code: 

static String getSomething(Properties props, Driver driver) {
  Connection conn = driver.connect(<db>, props)
  Sql sql = new Sql(conn)
  try {
    def <result> = sql.firstRow("SELECT ... ");
    <do something w result>
  }
  finally {
    sql.close();
    conn.close();
  }
  return <something>;
}

..and thanks to this, I got around to set up a test case which runs a bunch of parallel threads, just to check if something seems to get out of hand, which it doesn't. 

Thanks!