How to connect to specific database using JDBC and Google Apps Script

Question

I'm trying to connect to a Google Cloud MySQL 5.7 instance using GAS and JDBC. I'm able to run the following without error:

var conn = Jdbc.getCloudSqlConnection("jdbc:google:mysql://my-instance-111111:us-central1:mydbname", user,userPwd)

But this doesn't connect to a specific database, i.e. when I run

var stmt = conn.createStatement();
var results = stmt.executeQuery('SELECT col FROM myTable;');

I get Error Exception: No database selected.

One approach to try to fix:

The GAS docs indicate that I can use advanced parameters to include the DB name, but when I run var conn = Jdbc.getCloudSqlConnection("jdbc:google:mysql://my-instance-111111:us-central1:mydbname", user,userPwd,mydbname) ,

I get Exception: The parameters (String,String,String,String) don't match the method signature for Jdbc.getCloudSqlConnection.

A collection of getCloudSqlConnection statements that I've tried based on a number of StackOverflow posts:

var conn = Jdbc.getCloudSqlConnection("jdbc:google:mysql://my-instance-111111:us-central1:mydbname/mydbname", user,userPwd)

var conn = Jdbc.getCloudSqlConnection("jdbc:google:mysql://my-instance-111111:us-central1:mydbname:3306/mydbname", user,userPwd)

var conn = Jdbc.getCloudSqlConnection("jdbc:google:mysql://my-instance-111111:us-central1:mydbname:3307/mydbname", user,userPwd)

For the above statements, I also tried replacing my-instance-111111:us-central1:mydbname with the public IP supplied on the GC overview webpage. All return Exception: Failed to establish a database connection. Check connection string, username and password.

I'm using user root and the appropriate password. I can enter the DB via the command line with the user and password that I'm supplying in GAS, so I don't think I'm supplying the wrong user and password.

Edit: I went back to the docs like @AddonDepot mentioned and realized that I need to pass an object, but I still can't get this work....

var obj = {
             connectTimeoutSeconds: 15,
             database: "mydbname",
             instance: "my-instance-111111:us-central1:mydbname",
             password: userPwd,
             queryTimeoutSeconds: 15,
             user: user   };



var conn = Jdbc.getCloudSqlConnection("jdbc:google:mysql://my-instance-111111:us-central1:mydbname", obj);

returns

Exception: The following connection properties are unsupported: database,instance,connectTimeoutSeconds,queryTimeoutSeconds. .

Did I do something wrong in creating the object? I'm guessing not, because GAS recognizes user and password. Why wouldn't it recognize the other advanced parameters?

There are 3 variants of the getCloudSqlConnection() function with different method signatures (ie. distinguished by the number and types of parameters passed to the function). Read the documentation again to clarify usage. — TheAddonDepot
– TheAddonDepot, Commented Mar 17, 2021 at 15:53
@Martí Yes, I can fully access the DB from the terminal on my local computer. To do this, I use the exact same user and password as used in the GAS script. — user8121557
– user8121557, Commented Mar 18, 2021 at 16:54

Martí · Accepted Answer · 2021-03-19 09:12:45Z

We have to distinguish between database system instances (sometimes referred simply as database; I'll use instances) and databases within them. That means that you may have an instance but may not have created any database in it.

You can use Apps Script to create a new database in your instance:

const connectionName = 'connection-name:for-your:instance'
const dbName = 'database'
const username = 'user'
const password = 'password'

function createDB() {
  const conn = Jdbc.getCloudSqlConnection(`jdbc:google:mysql://${connectionName}`, username, password)
  conn.createStatement().execute('CREATE DATABASE ' + dbName)
}

Once you have the database created in your instance you can use it:

function useDB() {
  const conn = Jdbc.getCloudSqlConnection(`jdbc:google:mysql://${connectionName}/${dbName}`, username, password)

  // Use conn
}

References

JDBC (Google Apps Script guide)

Collectives™ on Stack Overflow

How to connect to specific database using JDBC and Google Apps Script

1 Answer 1

References

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

References

Comments

Your Answer

Sign up or log in

Post as a guest

Related