javascript - Google Apps Script - JDBC Connection Failed

Question

I am attempting to open a connection from a Google Apps Spreadsheet without success, using example code from the Google Apps Developers reference (https://developers.google.com/apps-script/jdbc)

function insert() {
    var conn = Jdbc.getConnection("jdbc:mysql://localhost:3306/test", "a", "a");

ERROR: Failed to establish a database connection. Check connection string, username and password. (line 56)

I have tried replacing "localhost" with my specific IP address.

I am able to connect successfully to my database using Access, ODBC and the above credentials so I know it's a JDBC / Google-apps-script issue.

CLASSPATH points to the correct jar file as shown by:

C:>echo %CLASSPATH%
.;C:Program FilesJavamysql-connector-java-5.1.22mysql-connector-java-5.1.22-
bin.jar

I am not sure if I have to do anything else, either within my script or google-doc-spreadsheet, or within Windows to set things up correctly.

I understand from one thing I read, that it is not necessary to install the JDBC driver as such but I would appreciate confirmation of same.

I have spent a long time searching the internet for solutions but to no avail.

I am having trouble diagnosing where the problem lies and would greatly appreciate any thoughts anyone may have in that regard.

Answer 1

The problem is that the localhost is used as the host. Google Apps Scripts are executed on Google servers and not on local computers. This means that the scripts cannot access the localhost and the error is logical.

To get rid of the error is necessary to have an SQL Server accessible outside of a local network (public in internet). It can be a MySQL port forwarded behind of a firewall or a 3rd part public SQL service.

Searching in internet, I found a few public MySQL Servers, but I don not know either they are online or not (link #1, link #2, link #3)

Update 00: The JDBC Service has a bug with impossibility to resolve host name to IP. I tried to test using the following code. It makes a connection to the DB.

function test() {
  var connection = Jdbc.getConnection("jdbc:mysql://193.62.203.76:3306/", "anonymous", "");
  debugger;
}