Database connections in GEL scripts best practices

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

As a developer of a custom process, what to be aware of when opening database connections in your GEL script? 

Environment

Release : Any

Resolution

Regular Connections

Regular connections such as sql:update /sql:query do not have to be managed. 

Using <core:invoke>, <core:invokeStatic> and <core:new>

If you are using <core:invoke>, <core:invokeStatic> and <core:new>, then you have to manage the connection yourself from start to finish exactly as you would in Java:

• Those connections should be opened, and then closed every time, even in the event of the process being in error, the logic should account for that too
• You can use dummy loop and "try and catch" block that would close the connections even if the script errors out
• Not closing open connections, may result in too many database connections being opened in a short time, and running out of possible database connections as described at Custom Processes stuck due to database connection pool issue.
• This is specifically important on PostgreSQL and MSSQL due to the way those vendors handle the database connections