Google Apps Script write to JDBC database

Question

I need some help with the syntax on how to write to and read from a MS SQL database.

I figured out the connection string, now I just need some help on how to write to the database and read from. Any help would be greatly appreciated.

Answer 1

Here are some simple examples for writing (and then getting IDs back), simple scalar read and a tabular read.

function write() {
  var conn = Jdbc.getCloudSqlConnection("jdbc:google:rdbms://your-connection-string-helloworld/mysql");
  var stmt = conn.createStatement();
  var sql = "INSERT INTO animals (name) VALUES ('lion')";
  var count = stmt.executeUpdate(sql,1)//pass in any integer to get auto incremented IDs back
  //check to see any auto generated keys come back. that is optional. 
  var rs = stmt.getGeneratedKeys();
  while(rs.next()) {
    Logger.log(rs.getString(1));
  }
  rs.close();
  stmt.close();
  conn.close();
}


function read() {
var conn = Jdbc.getCloudSqlConnection("jdbc:google:rdbms://your-connection-string-helloworld/mysql");
  var stmt = conn.createStatement();
  var rs = stmt.executeQuery("select * from animals");

  var doc = SpreadsheetApp.create('gdg test');
  var cell = doc.getRange('a1');
  var row = 0;
  while(rs.next()) {
    cell.offset(row, 0).setValue(rs.getString(1));
    cell.offset(row, 1).setValue(rs.getString(2));
    row++;
  }
  rs.close();
  stmt.close();
  conn.close();
}

function simpleRead() {
  var conn = Jdbc.getCloudSqlConnection("jdbc:google:rdbms://your-connection-string-helloworld/mysql");
  var stmt = conn.createStatement();
  var rs = stmt.executeQuery("select database()");
  rs.next(); //if you expect multiple rows, then do this in while(rs.next()) loop
  Logger.log(rs.getString(1));
  rs.close();
  stmt.close();
  conn.close();
}