Monday, July 30, 2012

Connecting To a MySQL Database In "The Cloud" (Part 2)

 For a detailed discussion with worked examples on Google Apps Script - JDBC - MySQl, see chapter 6 in this book on Leanpub


2013-02-08: There is another blog entry on this here!


Update added 2013-01-24: All the code  here is still valid but the MySQL instance that it points to has disappeared (for some reason?).  Suggestion: Set up your own free instance with Xeround or one of the other companies, including Google, who offer cloud MySQL instances.

In my last posting, I showed how to connect to a MySQL database in the cloud from a Google App script using JDBC.  In my closing remarks I noted that this is a vast field with vast potential to match.  I have put together here a few more scripts that will hopefully highlight some of this potential.  The scripts given here use the database described in the previous post.  The main points covered here are:

  • Execution of an SQL SELECT statement and printing of its output
  • Retrieval of an SQL statement from a spreadsheet cell comment
  • Use of bind parameters in a SELECT statement


A Very Brief Overview of Elementary JDBC
Java Database Connectivity (JDBC) is the standard Java API for connecting clients to databases.  Google appear to have done an excellent job in making this API available via Google App scripting.  Database APIs for various languages all follow a fairly similar design philosophy and all try to provide a standard interface for different databases (Oracle, MySQL, etc).  Anyone who has used ADO in Visual Basic or DBI in Perl, for example, should not have too many difficulties with JDBC, at least with basic stuff, the API is very big.  The first and most important step is to establish a connection to the target database, the last post showed how to do this for a MySQL cloud database where a Connection instance called conn was created.  The Connection instance methods "createQuery()" and "prepareStatement()" can then be used to create a Statement or a PreparedStatement instance, respectively.  A ResultSet instance is   returned either by executing the "executeQuery()" method of the Statement instance or by binding a value or series of values to the PreparedStatement instance and then executing the PreparedStatement instance's "executeQuery()" method.  Once the RecordSet instance has been returned, we can loop over it to return the query rows for each call to its "next()" method and/or we can call some of its other methods such as "getMetaData()" to get column information.  Once the RecordSet instance is processed, it is, according to Google's documentation, important to call the "close()" methods defined for instances of Connection, Statement, PreparedStatement, and RecordSet.  This explicit closing is considered good database programming practice for JDBC and similar APIs.  The PreparedStatement permits bind variables, the use of which is important for avoiding "hard parsing" of SQL and for prevention of SQL injection.  Creating custom SQL SELECT, DELETE, or UPDATE statements by string concatenation is a definite no-no when it comes to database programming, ALWAYS use bind parameters for both query efficiency and database security!

Code
A:  Executing a simple SQL SELECT using A read-only Account

// Establish a connection to the MySQL cloud database using a read-only account and assign the Connection instance to the variable "conn".
// Create a Statement instance and assign it to variable "stmt"
// Execute the Statement instance "stmt" by calling its "executeQuery()" method with a simple SQL statement string as its sole argument.
// The returned RecordSet instance "rs" is processed in a "while" loop and the rows are written to the Logger.
// Important clean up by calling "close()"
function printPubmedIds() {
  var conn = Jdbc.getConnection("jdbc:mysql://instance22444.db.xeround.com:12775/pubmed_publication", "read_only", "readonly");
  var stmt = conn.createStatement();
  var rs = stmt.executeQuery("SELECT pubmed_id FROM article");
  
  while(rs.next()){
    Logger.log(rs.getLong(1));
  }
  rs.close();
  stmt.close();
  conn.close();
}

B: Get all columns from table "article" for a given pubmed_id
Using a read-only account
// Executes "getArticle()" by passing a pubmed ID as an argument.
function call_getArticle() {
  var pubmed_id = 18252227;
  getArticle(pubmed_id);
}

// Retrieve all columns from the table "article" for a given pubmed ID.
// Expects the SQL to be execute to be in a comment in cell A1 of a sheet called "sql"
// Note the code that gets the SQL from a cell comment in the call to function "getSqlFromComment()"
// Note also the two consecutive lines that create a PreparedStatement instance and that bind a value to the prepared statement
function getArticle(pubmedID) {
  var sql = getSqlFromComment('sql', 'A1');
  var conn = Jdbc.getConnection("jdbc:mysql://instance22444.db.xeround.com:12775/pubmed_publication", "read_only", "readonly");
  var prepStmt = conn.prepareStatement(sql);
  prepStmt.setInt(1, pubmedID);
  var rs = prepStmt.executeQuery();
  var columnCount = rs.getMetaData().getColumnCount();
  var columnName;
  
  while(rs.next()) {
    for( var i = 1; i <= columnCount; i++ ){
      columnName = rs.getMetaData().getColumnName(i);
      Logger.log(columnName + ':\t' + rs.getString(i));
    }

  }
  rs.close();
  prepStmt.close();
  conn.close();
}


// Given a sheet name and a cell address, return the comment text for that cell in that sheet.
// Check for two arguments and throw an error if two arguments have not been passed.
function getSqlFromComment(sheetName, cellAddress) {
  if(arguments.length !== 2) {
    throw('Function "getCellComment" expects two arguments only ' + arguments.length + ' given!');
  }
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getSheetByName(sheetName);
    var commentedCell = ws.getRange(cellAddress);
    var commentText = commentedCell.getComment();
    return commentText;
  } catch(error) {
    throw('Unable to get comment for sheet: "' + sheetName + '" cell address: "' + cellAddress + '"');
  }
  
}

Code Notes
  • The code in section A should not require any further explanation other than that already given in the code comments.
  • Section B introduces a few additional topics, namely: retrieving SQL from a cell comment and the use of bind variable.
  • To run, insert a sheet called "sql" into the active spreadsheet.  Add a comment to its cell A1 that contains the following SQL:
SELECT
  ar.*
FROM
  article ar
WHERE
  ar.pubmed_id = ?
  • Paste the code into the Script Editor After executing function "call_getArticle()", check the Log (
  • View->Logs menu in the Script Editor) to see the output.
  • The code to get the cell comment was discussed in an earlier post.  The important point to note in the SQL is the "?", it is this character that is replaced by the actual value in the line "prepStmt.setInt(1, pubmedID);" in function "getArticle()" and exemplifies the use of bind variables.
  • Placing the SQL in a spreadsheet cell comment may seem like an odd thing to do but, from past experience with Excel VBA/ADO database programming, I have found it to work well.  Creation of formatted multi-line strings is difficult in both VBA and JavaScript, we have no handy triple quoting as in Python or heredocs and useful string operators such as qq in Perl that can be used to embed pre-formatted SQL statements in scripts.  Spreadsheet cell comments can be used to retain the SQL formatting and to hide the SQL from users.
  • These are demo scripts so important code for exception handling has been omitted. Also, the code would be much more re-usable if it were packaged up in a JavaScript object.  I'll have something more to say about this again in a future post.
  • Lastly, I've left all the connection details for this read-only account in the code snippets given above so anyone should be able to run them  Word of caution: Xeround allow only five connections for these free databases so please ensure that the connections are closed as quickly as possible, otherwise the account will become unusable for everyone.


2 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete