MySQL Re-Visited - Metadata

For more details and worked examples on Google Apps Script - JDBC - MySQL, see chapter 6 in this book on Leanpub 

Two previous posts dealt with Google Spreadsheet/MySQL Cloud interaction:

Part 1

Part 2

The database instance that those posts dealt with mysteriously disappeared.  Despite this, the notes and the code in those blog posts are still valid.

This entry deals with metadata, that is, data about the database that MySQL stores in its catalogs. It demonstrates how to use JDBC in Google Apps Script to retrieve this information.  The current Google documentation on JDBC is quite sparse but most of the standard Java JDBC documentation can be used as a guide here. To demonstrate metadata retrieval, a sample database is required.

Setting Up A Sample Database

Sample databases are available on the MySQL web site, see  MySQL Documentation: Other MySQL Documentation

Set Up a Free Cloud MySQL Instance 

I used Xeround.  Other options include Google and Amazon and many others.
Install the mysql command line client on own machine.
Log in to Xeround MySQL cloud instance using local mysql client.
Download and unzip the world.sql file
While logged in to Cloud mysql database instance from local machine:
-- Login looks something like this for Xeround
$ mysql --user=tigger --host=instance<some number>.db.xeround.com --port=<portno> --password
-- Create a database
mysql> CREATE DATABASE world;
-- Switch to the newly created database ,
mysql> USE DATABASE world;
-- Populate the database,
mysql> SOURCE /<path_to_sql_file>/world.sql; 

Once the above SQL script is executed, the database is populated.

Extracting Metadata From The Database

Important metadata can be retrieved using mysql commands but this entry is concerned with how JDBC can be used to obtain the same information. Important metadata about MySQL itself, tables, views, and stored procedures can be retrieved from the JdbcConnection object using the getMetaData() method which returns a JdbcDatabaseMetaData object. This object can be interrogated via its methods to retrieve database metadata.

Function To Print Metadata To The Log
function printMySQLMetadata() {
  var conn = Jdbc.getConnection('jdbc:mysql://instance<num>.db.xeround.com:<port>/world', <usename>, <pwd>),
      dbMetadata = conn.getMetaData(),
      rsTables,
      rsColumns,
      tableNames = [],
      i,
      tableCount;
  Logger.log('Major Version:' + dbMetadata.getDatabaseMajorVersion());
  Logger.log('Minor Version: ' + dbMetadata.getDatabaseMinorVersion());
  Logger.log('Product Name: ' + dbMetadata.getDatabaseProductName());
  Logger.log('Product Version: ' + dbMetadata.getDatabaseProductVersion());
  Logger.log('Supports transactions: ' + dbMetadata.supportsTransactions());
 
  rsTables = dbMetadata.getTables(null, null, null, ['TABLE']);
  while (rsTables.next()) {
    tableNames.push(rsTables.getString(3));
  }
  tableCount = tableNames.length;
  Logger.log('The Table Names And Their Columns Are:');
  for (i = 0; i < tableCount; i += 1) {
    rsColumns = dbMetadata.getColumns(null, null, tableNames[i], null);
    while (rsColumns.next()) {
      Logger.log(tableNames[i] + ':' + rsColumns.getString(4));
    }
  }
  conn.close();
}

Code Notes

• Establish a connection to the database (reader will need to fill in the details in the connection string to match their own database).
• Return the metadata as a JdbcDatabaseMetaData object by calling the getMetaData() method of the Connection object.
• Call various JdbcDatabaseMetaData object methods to retrieve metadata and print the output to the log.
• Surprisingly, the method supportTransactrions() reports true even though the tables are MyISAM type.  I assumed you needed InnoDB table types to get this feature in MySQL.  I'll check that the tables do indeed support transactions as this code suggests.
• A list of tables is extracted using the record set (Recordset) returned by the method getTables() (record sets were covered in the earlier blog entries).  The three nulls are required in this method call.  The table type argument has to be passed as an array even though it has only one element.  Other table types that could be passed includes views.
• The column index argument used in the record set getString() method to extract the table name is 1-based.  The argument given, that is 3, was discovered by trial-and-error.   The table names are then stored in an array.
• The column names for each table are extracted by processing the columns record set returned by the getColumns() method with the table name as an argument.  Once again, three nulls are required in this method call.
• The column names are in the fourth column of the record set (argument = 4)  in the columns record set getString() method call. Once again, this was found by trial-and-error.
• Once the above function has executed, go to  Views->Logs... in the Script Editor to see the output which should begin something like this:

Major Version:5
Minor Version: 1
Product Name: MySQL
Product Version: 5.1.42
Supports transactions: true
The Table Names And Their Columns Are:
city:ID
city:Name
city:CountryCode
city:District

This was just an introduction to what is a large and important area.  I will write a full chapter on Google Spreadsheets/JDBC in this leanpub book in the coming weeks.  So if interested, keep a watch on that.  I'll also post more in future blogs.