2

Platform: Google Apps Script connecting to SQL Server using JDBC service.

How do I get the identity (id) of an inserted row? (code provided below) I believe the solution is here: https://stackoverflow.com/a/42655/3520117 but I'm struggling to implement that general solution in Google Apps Script using the JDBC service.

Code.gs:

function postNewItem(formObject) {

  var useremail = Session.getActiveUser().getEmail();

  var timeZone = CalendarApp.getDefaultCalendar().getTimeZone();
  var timestamp = Utilities.formatDate(new Date(), timeZone, "yyyyMMdd HH:mm:ss"); // get current date/time

  var conn = Jdbc.getConnection(connParams);
  var stmt = conn.prepareStatement('INSERT INTO tblOne '
      + '(email, timestamp, var1, var2, var3) '
      + 'values (?, ?, ?, ?, ?)');
  stmt.setString(1, useremail);
  stmt.setString(2, timestamp);
  stmt.setString(3, formObject.var1);
  stmt.setString(4, formObject.var2);
  stmt.setString(5, formObject.var3);

  stmt.execute();
  conn.commit();
  conn.close();

  // log event
  logEvent(id, "N/A", "New item added.", useremail);
}
Community
  • 1
  • 1
h0dges
  • 663
  • 7
  • 15
  • 1
    Possible duplicate of [How to get the insert ID in JDBC?](http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc) – Mark Rotteveel Feb 14 '17 at 17:11
  • Mark, it is a similar but distinct question as this depends on Google's implementation of the JDBC driver they use with Google Apps Script. Their documentation makes no reference to this feature, plus the example code you've linked to is Java and Google Apps Script utilises javascript on the server-side. – h0dges Feb 15 '17 at 08:27
  • I'm sure Google Apps Script's implementation of JDBC supports this. execute(sql, autoGeneratedKeys) https://developers.google.com/apps-script/reference/jdbc/jdbc-prepared-statement#execute(String,Integer) getGeneratedKeys() https://developers.google.com/apps-script/reference/jdbc/jdbc-statement#getgeneratedkeys Help! – h0dges Feb 15 '17 at 08:51
  • According to Google's documentation, it wraps a normal JDBC driver, so it should work. Have you actually tried it? If you tried it, update your question with the code used and any errors you get. BTW: You tagged "sql-server", are you sure Google Apps Script is using Microsoft SQL Server (I'd be surprised if it was). – Mark Rotteveel Feb 15 '17 at 09:20
  • Be surprised Mark - it is: "In Apps Script, the JDBC service supports Google Cloud SQL, MySQL, Microsoft SQL Server, and Oracle databases." Also, I've solved my problem and posted an answer below. Thanks for your help. – h0dges Feb 15 '17 at 09:45
  • Ah, I learn something new every day :) Glad I could be of help. I will also retract my close vote. – Mark Rotteveel Feb 15 '17 at 10:06

1 Answers1

3

Solved. I'm posting this here to benefit anyone else struggling with this.

Steps:

  1. Add autoGeneratedKeys flag

    var stmt = conn.prepareStatement(query, 1);

  2. Get a list of auto generated keys

    var results = stmt.getGeneratedKeys();

  3. Get ID:

    while (results.next()) { var id = results.getInt(1); }

Full example:

Code.gs:

function postNewItem(formObject) {

  var useremail = Session.getActiveUser().getEmail();

  var timeZone = CalendarApp.getDefaultCalendar().getTimeZone();
  var timestamp = Utilities.formatDate(new Date(), timeZone, "yyyyMMdd HH:mm:ss"); // get current date/time

  var conn = Jdbc.getConnection(connParams);
  var stmt = conn.prepareStatement('INSERT INTO tblOne '
      + '(email, timestamp, var1, var2, var3) '
      + 'values (?, ?, ?, ?, ?)', 1); // added autoGeneratedKeys flag
  stmt.setString(1, useremail);
  stmt.setString(2, timestamp);
  stmt.setString(3, formObject.var1);
  stmt.setString(4, formObject.var2);
  stmt.setString(5, formObject.var3);

  stmt.execute();

  // start of additional code
  var results = stmt.getGeneratedKeys();

  while (results.next()) {
    var id = results.getInt(1);
  }

  Logger.log(id);
  // end of additional code

  conn.commit();
  conn.close();

  // log event
  logEvent(id, "N/A", "New item added.", useremail);
Silko
  • 584
  • 1
  • 8
  • 26
h0dges
  • 663
  • 7
  • 15
  • In normal JDBC the possible values are `Statement.RETURN_GENERATED_KEYS` (= `1`) and `Statement.NO_GENERATED_KEYS` ( = `2`). – Mark Rotteveel Feb 15 '17 at 10:09