0

I'm just learning to use Google Sheets scripts, and trying to log into my MySQL database, below is my code

function readData() {
  var conn = Jdbc.getConnection('jdbc:mysql://hostname(or using IP Address):3306/database_name',username,password);
  var stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT * FROM tablename');
  var metaData=results.getMetaData();
  var numCols = metaData.getColumnCount();
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Sheet1');
  sheet.clearContents();
  var arr=[];

  for (var col = 0; col < numCols; col++) {
    arr.push(metaData.getColumnName(col + 1));
  }

  sheet.appendRow(arr);

while (results.next()) {
  arr=[];
  for (var col = 0; col < numCols; col++) {
    arr.push(results.getString(col + 1));
  }
  sheet.appendRow(arr);
}

results.close();
stmt.close();
sheet.autoResizeColumns(1, numCols+1);
}

and also I've already Additional MySQL Access Hosts (WHM) from cPanel-Databases-Remote MySQL

64.18.0-15.%
64.233.160-191.%
66.102.0-15.%
66.249.80-95.%
72.14.192-255.%
74.125.%
173.194.%
207.126.144-159.%
209.85.128-255.%
216.239.32-63.%

So I try to run it, but I got error message

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

Is there anything missing or not set up yet? Would please help me how to solve this?

JimmySo
  • 1
  • Does your hosting provider allow direct remote access to your mysql? – Shadow Aug 23 '20 at 12:55
  • Does your connection attempt fail immediately or does it time out (take half a minute or more to fail)? An immediate failure probably means a bad username / password. A timeout probably means the hosting provider prevents access to their MySQL server from outside their datacenter, as @shadow mentioned. In that case ask your hosting provider for help. – O. Jones Aug 23 '20 at 13:01
  • Try connecting to your database using a different client, for example [MySQLWokbench](https://www.mysql.com/products/workbench/). Take note of how long it takes to establish a connection with that other client. AppScript's JDBC connector seems to automatically timeout after around 20 to 30 seconds, so it could be that it simply takes too long to establish a connection with your MySQL server. – TheAddonDepot Aug 23 '20 at 13:17
  • How to know, if my hosting provider allow direct remote access to mysql? Is the cPanel can be found? – JimmySo Aug 23 '20 at 13:34
  • Okay, I try contact my hosting provider to ask this case. Thank you – JimmySo Aug 23 '20 at 13:36
  • Yes, I forget to try this and then I've try it and show error 10061, maybe I must to contact my hosting provider to solve this one? thank you. – JimmySo Aug 23 '20 at 13:38

0 Answers0