How to pool a connection in google apps script?

Question

Previously in scripting in Google Apps Script, I opened and closed my Jdbc connection in all the functions.

In order to speed up my application, it seems to be better to leave the connection open and use it in another function. However, I don't know how I should do that in Google Apps Script.

Can anyone give me a suggestion or an example?

Answer 1

Here is an example of how to retain your connection at ScriptDB.

function myFunction() {
  var conn = ScriptDb.getMyDb().query({name : "my_connection"} );
  var connection = null;
  var itemId = null;

  if(conn.getSize() == 0) {
    Logger.log(" Connection not found, creating new one");
    connection = Jdbc.getConnection('jdbc:mysql://<host>:3306/<instance>', 'user', 'password');
    itemId = ScriptDb.getMyDb().save({name : "my_connection", connection : connection}).getId();

  }else {
    Logger.log(" Connection found, retrieving "+conn.getSize());
    var dbItem  = conn.next();
    connection = dbItem.connection;
    itemId = dbItem.getId();
  }

  //Check if it is closed
  if(connection.isClosed()) {
    connection = Jdbc.getConnection('jdbc:mysql://<host>:3306/<instance>', 'user', 'password');

    //After opening remember to save to dabatase again and retain only one connection
    ScriptDb.getMyDb().removeById(itemId);
    ScriptDb.getMyDb().save({name : "my_connection", connection : connection});
  }

  //Use your connection here

  //Remenber to close it based in some criteria
}

Example here.