Working with Google Apps Script, I’d been given a mission – create reports from our database. Having never used JDBC, or Java for that matter (except an app I’ve written for Android last year, but never published), I went along and researched.
Why Java, you might be asking. Isn’t Google Apps Script purely Javascript?
Apparently, it isn’t. My first hunch was Google using GWT to build Apps Script. I quickly understood that’s not what GWT is for – it allows you to build web applications with Java, not the other way around. The other viable option I could think of, is an opposite of GWT – that is, you code in Javascript (or Apps Script), which gets translated to Java. That makes sense, as Java libraries are available to your disposable on Apps Script.
The one I was using was JDBC, “an API for the Java programming language that defines how a client may access a database” as Wikipedia kindly states. It’s basically a class that allows you to connect to a database of your choice.
Well, not every database. Google’s JDBC is a limited version. You can’t use a third-party driver like PostgreSQL JDBC Driver. MongoDB? Not a chance.
But the worst I’ve come across is the internal JDBC limitations. About a month ago, I posted on Stackoverflow – “Dynamic SQL returns ‘syntax error’ on Google Apps Script using JDBC and MySQL”. I built a complex SQL query which included Dynamic SQL, VARs and multiple queries. Here’s the code:
function selectAllUserSurveys() {
var query ="SET@sql =NULL;\
SELECT GROUP_CONCAT(DISTINCT\
CONCAT('MAX(CASE WHEN survey_field_values.survey_field_option_id = ', survey_field_option_id,\' THEN survey_field_values.value END) AS ', CONCAT('`survey_field_option_id', survey_field_option_id,'`'))\)INTO@sql \
FROM survey_field_values \
LEFTJOIN surveys \
ON survey_field_values.survey_id = surveys.id \
WHERE surveys.survey_type ='client';\
SET@sql = CONCAT('SELECT surveys.bid_id, user.rep_name, 0, ',@sql,'\
FROM surveys \
INNERJOINuser\
ON surveys.user_id =user.username \
LEFTJOIN survey_field_values \
ON surveys.id = survey_field_values.survey_id \
WHERE surveys.survey_type =\'client\'\
AND surveys.is_filled =1\
GROUPBY surveys.id \
ORDERBY surveys.date_filled ASC');\
PREPARE stmt FROM@sql;\
EXECUTE stmt;\
DEALLOCATE PREPARE stmt;";
return executeSelectMultipleRowsQuery(query);
}
function executeSelectMultipleRowsQuery(query) {
var conn = Jdbc.getConnection(dbUrl,user, userPwd);
var start= new Date();
var stmt = conn.createStatement();
var results = stmt.executeQuery(query);
var numCols = results.getMetaData().getColumnCount();
return results;
results.close();
stmt.close();
}
Having run that using JDBC on Apps Script, I got the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN survey_field_values.survey_fi' at line 1
Marc B was kind enough to bring his expertise in, and pointed me into the right direction – running multiple queries was were I was falling. Apps Script’s implementation of JDBC doesn’t allow you to execute multiple queries in a single execute call. Trying to pass the allowMultiQueries parameter to the JDBC connection string would return the following error:
The following connection properties are unsupported: allowMultiQueries. (line X, file "foo", project "bar")
All was not lost though. Marc suggested executing each query separately with it’s own execute call. My final code would look like so:
function DBHelper() {
this.conn = Jdbc.getConnection(dbUrl,user, userPwd);
this.stmt = this.conn.createStatement();
}
DBHelper.prototype.executeSelectMultipleRowsQuery =function(query) {
var results = this.stmt.executeQuery(query);
var numCols = results.getMetaData().getColumnCount();
return results;
}
function selectAllUserSurveys(){
dbHelper = new DBHelper();
var setsql ="SET @sql = NULL";
dbHelper.executeSelectMultipleRowsQuery(setsql);
var dynamicColumns ="SELECT GROUP_CONCAT(DISTINCT\
CONCAT('MAX(CASE WHEN survey_field_values.survey_field_option_id = ', survey_field_option_id,\' THEN survey_field_values.value END) AS ', CONCAT('`survey_field_option_id', survey_field_option_id,'`'))\)INTO@sql \
FROM survey_field_values \
LEFTJOIN surveys \
ON survey_field_values.survey_id = surveys.id \
WHERE surveys.survey_type ='client'";
Logger.log(dbHelper.executeSelectMultipleRowsQuery(dynamicColumns));
var mainquery = "SET@sql = CONCAT('SELECT surveys.bid_id, user.rep_name, 0, ',@sql,'\
FROM surveys \
INNERJOIN user\
ON surveys.user_id =user.username \
LEFTJOIN survey_field_values \
ON surveys.id = survey_field_values.survey_id \
WHERE surveys.survey_type =\\'client\\'\
AND surveys.is_filled =1\
GROUPBY surveys.id \
ORDERBY surveys.date_filled ASC')";
dbHelper.executeSelectMultipleRowsQuery(mainquery);
var prepare = "PREPARE stmt FROM@sql";
dbHelper.executeSelectMultipleRowsQuery(prepare);
var execute = "EXECUTE stmt";
var return_value = dbHelper.executeSelectMultipleRowsQuery(execute);
var deallocate = "DEALLOCATE PREPARE stmt;";
dbHelper.executeSelectMultipleRowsQuery(deallocate);
Logger.log(return_value);
return return_Value;
}
VARs are saved per connection, rather than per execution, which made them available in the following execution calls after creating them, and allowed me to use them in the following calls.
I’m not sure why Google would disallow allowMultiQueries in their implementation of JDBC. Ring me up if you know. If not, use the solution above.
