1
votes

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 Files\Java\mysql-connector-java-5.1.22\mysql-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.

1
Do you use a Public IP or an internal IP to point to your mysql database? Your database must be over a public IP so that Google Servers can reach to your database and execute the query.Waqar Ahmad
Is there any way to test using localhost? If not, can anyone give me details of a server in the cloud that I could test with?Gram
If you Google for "free mySQL database", you will find a lot of listings. Erlier I tried with freemysql.net and it worked perfect.Waqar Ahmad
Are you able to provide the connection string that you tried?Gram
Please check my comment in the answer, and update by megabyte1024.Waqar Ahmad

1 Answers

0
votes

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;
}