CODEWITHSUNDEEP
mysqlgoogle-apps-scriptjdbc
Andy
Andy

Reputation: 5395

Google App Scripts won't make a Jdbc connection to MySQL on a remote server

I'm new to using Google Scripts and am trying to make a connection to a MySQL (5.5.65-MariaDB) database which is hosted on an external (non-Google) server running CentOS Linux 7.7.1908 Core.

I'm following the guide here: https://developers.google.com/apps-script/guides/jdbc#other_databases

In my script I have used the following function: 

// export_sheet_data.gs
function writeOneRecord() {
    var conn = Jdbc.getConnection('jdbc:mysql://HOST_NAME.com:3306/DB_NAME', 'DB_USER', 'DB_PASSWORD');

    var stmt = conn.prepareStatement('INSERT INTO entries '
    + '(guestName, content) values (?, ?)');
    stmt.setString('andy', 'First Guest');
    stmt.execute();
}

Obviously the HOST_NAME, DB_NAME, DB_USER and DB_PASSWORD are strings (entered literally, not variables) which correspond to the appropriate values: host name of my external server, database name, database username and database password.

When I execute this in the Scripts console (Run > Run function > writeOneRecord()) it says

Running function writeOneRecord()

for approx 10 seconds.

It then errors with

Exception: Failed to establish a database connection. Check connection string, username and password.

If I click "Details" it doesn't really elaborate on this. It says

Exception: Failed to establish a database connection. Check connection string, username and password. (line 54, file "export_sheet_data")

I have done the following:

There is nothing appearing in my firewall log on the server I'm connecting to about this.

I've checked that I'm using Jdbc.getConnection correctly.

How else can I debug this? There doesn't seem to be anything in the Google Scripts console which can help.

Upvotes: 4

Views: 1642

Answers (2)

roma
roma

Reputation: 1580

Are you using VPS Linux?

I've had the same issue, and wasn't able to fix it. It appears to be a bug of GAS JDBC implementation with combination of custom network drivers used inside VPS and/or specific MYSQL implementation.

For some Linux/Mysql combinations I was able to connect to Linux Mysql, for some not.

The error in my case was "Got an error reading communication packet"(mysql logs) - very hard to track mysql error.

If you check mysql logs on your server and that's this error I think the best you can do is follow simplified version of @TheAddonDepot advice - write a small nodejs code to run sql for you and install it as linux service(listen http on custom port)- there are a lot of examples for that. It'd be easier to spend several hours for this, cause I don't think this issue would be resolved in near future.

I haven't tested it, but something like this should fit your case.

var http = require('http');
var url = require('url');
var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected to mysql!");
});

http.createServer(function (req, res) {
  var q = url.parse(req.url, true).query;
  var result;

  let stmt = `INSERT INTO entries (guestName, content) values (?, ?)`;
  let values = [q.guestName, q.content];
  console.log('Request', q);

  // execute the insert statment
  connection.query(stmt, values, (err, results, fields) => {
    if (err) {
      res.writeHead(400, {'Content-Type': 'application/json'});
      console.error(err.message);
      result = {error:err};
    } else {
      res.writeHead(200, {'Content-Type': 'application/json'});
      console.log('Insert Id:' + results.insertId);
      result = {success:true, id:results.insertId};
    }
  });
  res.end(JSON.stringify(result));
}).listen(7733);

and call it from GAS like

UrlFetchApp.fetch('http://your-server-ip:7733/?guestName=' + encodeURIComponent(guestName) + '&content=' + encodeURIComponent(content));

Alternatively, you can try switching Linux/Mysql version but it's more complicated, and I'm not sure switch combination works exactly cause in my case error was on Debian 18.04

Upvotes: 2

Cooper
Cooper

Reputation: 64062

I have had some difficulties accessing MySql databases on websites and found this approachment to work for me.

var address=getDBData('address');//I keep the actuall data in a spreadsheet  
    var user=getDBData('user');
    var userPwd=getDBData('pass');
    var db=getDBData('db');
    var dbUrl='jdbc:mysql://' + address + '/' + db;
    s+='<table>';
    s+='<tr><th>Database Name: ' + db + '</th></tr>';
    s+='<tr><th>' + qry + '</th></tr></table>';
    try
    {
      var conn=Jdbc.getConnection(dbUrl, user, userPwd);
    }

The address that seems to work often is the ip address for the hosting account.

Upvotes: 0

Related Questions