GAS-MYSQL
accessing an online mySql database
using google apps script and JBDC
INTRO
Using google apps script with JDBC provides an alternative method for accessing your mysql database/s.
There are three main statements you use:
the general: stmt.execute(sql)
this will return true if there is a resultSet, and false for anything else
the update: stmt.executeUpdate(sql)
this will return a value for the number of records affected by the update
the query: stmt.executeQuery(sql)
this will return a resultSet, which must be parsed to extract the output of the query
It is a little slower than using php in completing statements and returning results, but not so much as to be unworkable. I use the progress dialog for the wait.
I have only included the most useful statements to ensure full CRUD on the database. I have setup the app to provide example statements which can be edited.
( I also have prepared a barebones/expert app/script solution as well, see at the end of the page)
I have not used prepared statements, but instead a secret key, which must be manually typed into the app to provide access. You could have a user login system or use Firebase authentication to control your users, and/or prepared statements..... You could also setup several secret keys for different users.....
SETUP
You will need a mysql database setup on your online server
You will need the (default) connection credentials: server/port/db/user/pass/key
You will probably need to expose port 3306 (the default, otherwise the port you are using) in your server firewall, to give JDBC access. You can, in addition, further secure your mysql by setting these IP address ranges in your firewall for port 3306
Create your Google Apps Script web app in the usual way, adding your own connection credentials and secret key
Create your AI2 app in the usual way. The script and app in this demonstration are tightly aligned
SCRIPT
// set database credentials
var server = "domain.co.uk"; //required, do not include https://
var port = 3306; //required default config is 3306
var db = "demodb"; //database name
var user = "username"; //required username
var pwd = "password"; //required password
var key = "your_secret"; //required secret key
// set global variables
var action, conn, url, msg;
// working with AI2...
function doPost(e) {
if (e.parameter.key == key) {
// set the action
action = e.parameter.action;
//set database credentials to work with, if not the default, these must be sent as parameters from the AI2 app
if (e.parameter.server ) { server = e.parameter.server;}
if (e.parameter.port ) { port = e.parameter.port;}
if (e.parameter.db ) { db = e.parameter.db;}
if (e.parameter.user ) { user = e.parameter.user;}
if (e.parameter.pwd ) { pwd = e.parameter.pwd;}
//Test mysql connection
if (action == 'testConn') {
return testConnectionMySql();
}
// ########
// CREATE #
// ########
//create database
else if (action == 'createDatabase') {
return generalMySql(e.parameter.sql);
}
//create table in database
else if (action == 'createTable') {
return generalMySql(e.parameter.sql);
}
//create record in database
else if (action == 'insert') {
return updateMySql(e.parameter.updsql);
}
// ########
// READ #
// ########
//show databases
else if (action == 'showDatabases') {
return queryListMySql("SHOW DATABASES WHERE `database` NOT IN('mysql','information_schema','performance_schema','sys','logregsys','phpmyadmin')");
}
//show tables in database
else if (action == 'showTables') {
return queryListMySql("SHOW TABLES");
}
//show columns in table
else if (action == 'showColumns') {
return showColsMySql(e.parameter.table);
}
//query records in database
else if (action == 'query') {
return queryMySql(e.parameter.sql);
}
// ########
// UPDATE #
// ########
//update record in database
else if (action == 'update') {
return updateMySql(e.parameter.updsql);
}
// ########
// DELETE #
// ########
//delete record in database
else if (action == 'delete') {
return updateMySql(e.parameter.updsql);
}
//delete table in database
else if (action == 'dropTable') {
return generalMySql(e.parameter.sql);
}
}
else {
return ContentService.createTextOutput("key incorrect");
}
}
function generalMySql(genSql) {
try {
url = "jdbc:mysql://" + server+":"+port+"/"+db;
conn = Jdbc.getConnection(url, user, pwd);
let stmt = conn.createStatement();
let result = stmt.execute(genSql);
stmt.close();
conn.close();
console.log(action + " completed");
return ContentService.createTextOutput(action + " completed");
}
catch(err) {
console.log("Error: " + err.message);
return ContentService.createTextOutput("Error: " + err.message);
}
}
function updateMySql(updSql) {
try {
url = "jdbc:mysql://" + server+":"+port+"/"+db;
conn = Jdbc.getConnection(url, user, pwd);
let stmt = conn.createStatement();
let update = stmt.executeUpdate(updSql);
msg = action + " completed, " + update;
stmt.close();
conn.close();
console.log(msg);
return ContentService.createTextOutput(msg);
}
catch(err) {
console.log("Error: " + err.message);
return ContentService.createTextOutput("Error: " + err.message);
}
}
function queryMySql(query) {
try {
url = "jdbc:mysql://" + server + ":" + port + "/" + db;
conn = Jdbc.getConnection(url, user, pwd);
let stmt = conn.createStatement();
const results = stmt.executeQuery(query);
const numCols = results.getMetaData().getColumnCount();
var resultsArray = [];
while (results.next()) {
let row = [];
for (let col = 0; col < numCols; col++) {
row.push(results.getString(col + 1));
}
resultsArray.push(row);
}
msg = JSON.stringify(resultsArray);
results.close();
stmt.close();
conn.close();
console.log(msg)
return ContentService.createTextOutput(msg);
}
catch (err) {
console.log('Failed with an error %s', err.message);
return ContentService.createTextOutput('Failed with an error %s', err.message);
}
}
function queryListMySql(query) {
try {
if (action == "showDatabases") {
url = "jdbc:mysql://" + server+":"+port;
} else {
url = "jdbc:mysql://" + server+":"+port+"/"+db;
}
conn = Jdbc.getConnection(url, user, pwd);
let stmt = conn.createStatement();
let results = stmt.executeQuery(query);
const numCols = results.getMetaData().getColumnCount();
var resultsArray = [];
while (results.next()) {
let row = "";
for (let col = 0; col < numCols; col++) {
row = results.getString(col + 1);
}
resultsArray.push(row);
}
msg = JSON.stringify(resultsArray);
results.close();
stmt.close();
conn.close();
console.log(msg);
return ContentService.createTextOutput(msg);
}
catch(err) {
return ContentService.createTextOutput('Failed with an error %s', err.message);
}
}
function testConnectionMySql() {
try {
url = "jdbc:mysql://" + server + ":" + port;
conn = Jdbc.getConnection(url, user, pwd);
if (conn) {
console.log(conn + " is successful");
return ContentService.createTextOutput(conn + " is successful");
}
conn.close();
}
catch(err) {
console.log("Error: " + err.message);
return ContentService.createTextOutput("Error: " + err.message);
}
}
function showColsMySql(table) {
try {
url = "jdbc:mysql://" + server+":"+port+"/"+db;
conn = Jdbc.getConnection(url, user, pwd);
let stmt = conn.createStatement();
let rs = stmt.executeQuery("SELECT * FROM " + table);
let rsmd = rs.getMetaData();
let countOfColumns = rsmd.getColumnCount();
let resultsList = [];
for( i = 1; i <= countOfColumns ; i++ )
resultsList.push(rsmd.getColumnName(i));
console.log(JSON.stringify(resultsList));
msg = JSON.stringify(resultsList);
stmt.close();
conn.close();
return ContentService.createTextOutput(JSON.stringify(resultsList));
}
catch(err) {
return ContentService.createTextOutput("Error: " + err.message);
}
}
BLOCKS
SCREEN
EXPERT/BAREBONES
I have left the get databases / tables / columns actions in this, special requriements in the script for these.
You are left with three options to enter your sql: QUERY (Read Data), UPDATE(Insert,Update,Delete), GENERAL(many things, you can also read/query data)
AIA - GASMYSQLEXPERT_BLANK.aia
SCRIPT - gmescript.txt
