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!

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://", "read_only", "readonly");
  var stmt = conn.createStatement();
  var rs = stmt.executeQuery("SELECT pubmed_id FROM article");

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;

// 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://", "read_only", "readonly");
  var prepStmt = conn.prepareStatement(sql);
  prepStmt.setInt(1, pubmedID);
  var rs = prepStmt.executeQuery();
  var columnCount = rs.getMetaData().getColumnCount();
  var columnName;
  while( {
    for( var i = 1; i <= columnCount; i++ ){
      columnName = rs.getMetaData().getColumnName(i);
      Logger.log(columnName + ':\t' + rs.getString(i));


// 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:
  article ar
  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.


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

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

  3. Social networking sites globally have the greatest volume of traffic over 24 hours a day, seven days a week. Several social networking and other high traffic sites such as Flickr, Facebook, Wikipedia, Google, Nokia and YouTube use MySQL for data storage and logging of user data. This is used in close connection with PHP.

  4. Your website is really cool and this is a great inspiring article. Buying Google Reviews,

  5. I have been following your post for a long time. I always found it very interesting and valuable. keep posting it is really helpful.
    Cloud Migration services

    Aws Cloud Migration services

    Azure Cloud Migration services

  6. We are a part of the success story for many of our customer's successful cloud Migrations.

    Vmware Cloud Migration services

    Database Migration services

  7. Thank you for the informative post about Security challenges in AWS , Found it useful . cloud migration services have now become secured and with no-risk
    Lia Infraservices

  8. I am really impressed with the way of writing of this blog. The author has shared the info in a crisp and short way.
    Cloud Migration services

    Best Cloud Migration Tool


  9. You write this post very carefully I think, which is easily understand to me. Not only this, other post is also good. As a newbie this info is really helpful for me. Thanks to you.
    Tally Training in Chennai
    Tally ERP 9 Training
    tally classes
    Tally Training institute in Chennai
    Tally course in Chennai