Why does Jdbc.getCloudSqlConnection() return "Exception: No database selected" in one context but not another?

Question

I'm just getting started with Google Apps Script and doing development like this again after a long time. The following code returns:

Exception: No database selectedwhen I executetest202302110454()

It fails executing the createTable() function. The code is a mishmash of example code from Google for using its JDBC connector/library with a Google Apps Script Web app and my own modifications.

The back-end is a Google Cloud SQL MySQL database instance.

const debug = true;

debug ? Logger.log("I'm inside service/jdbc.gs.") : null ;

const jdbcEnv = ifSql(database);

// Check to see if we're using an SQL back-end rather than Firestore et al.
function ifSql(database) {
  if (database == "sql") {
    return initSql()
  }
  return [];
}

// If we're using SQL, set up the JDBC environment
function initSql() {
  const url = "jdbc:google:mysql://goals-and-projects:us-central1:mysql-instance";
  const db = "goalManagementApp";
  const environmentObject = {
    "instanceUrl": url,
    "db": db,
    "dbUrl": `${url}/${db}`,
    "root": "root", // TODO: Move this to the Properties service
    "rootPwd": "*****************", // TODO: Move this to the Properties service
  };

  return environmentObject;
}

function getSQLConnection(env) {
  const connection = Jdbc.getCloudSqlConnection(env.instanceUrl, env.root, env.rootPwd);

  return connection;
}

function test202302110454() {
  Logger.log("I'm running a test function.");
  Logger.log(jdbcEnv);
  Logger.log(createDatabase(`database` + Date.now(), jdbcEnv));
  Logger.log(createTable(`table` + Date.now(), jdbcEnv));
}


/**
 * Create a new database within a Cloud SQL instance.
 */
function createDatabase(databaseName, env) {
  debug ? Logger.log("Inside createDatabase()") : null;
  const db = databaseName;
  const connection = getSQLConnection(env);

  try {
    connection.createStatement().execute('CREATE DATABASE ' + db);
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log('Failed with an error %s', err.message);
  }
}


/**
 * Create a new table in the database.
 */
function createTable(tableName, env) {
  debug ? Logger.log("Inside createTable()") : null;
  try {
    const connection = getSQLConnection(env);
    //const connection = Jdbc.getCloudSqlConnection(env.dbUrl, env.root, env.rootPwd);
    debug ? Logger.log(env) : null;
    connection.createStatement().execute(`CREATE TABLE ${tableName} ` +
      '(guestName VARCHAR(255), content VARCHAR(255), ' +
      'entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));');
  } catch (err) {
    // TODO(developer) - Handle exception from the API
    console.log('Failed with an error %s, %s', err.message, err);
  }
}

However, this modification makes the createTable() function work as expected, without error:

    //const connection = getSQLConnection(env);
    const connection = Jdbc.getCloudSqlConnection(env.dbUrl, env.root, env.rootPwd);

When I try to use my own function getSQLConnection to create a connection object, it fails, but when I use Jdbc.getCloudSqlConnection it succeeds. Unless I'm missing something, this is not the case when running createDatabase() even though I think I'm setting them up the same way. I am outputting the connection objects that are used in each case and they both appear identical to me; there is no missing database name that I can tell.

What am I missing?

For more context:

The way I'm executing this is within the Google Apps Script scripting environment on the website. I'm selecting the test function (test202302110454) in the UI and running it there. I created the test202302110454 function to test out my other functions and ensure I know what they are doing.

The exact logging console output is:

11:20:10 PM    Info    I'm inside service/jdbc.gs.
11:20:10 PM    Info    I'm running a test function.
11:20:10 PM    Info    {dbUrl=jdbc:google:mysql://goals-and-projects:us-central1:mysql-instance/goalManagementApp, root=root, rootPwd=**********, db=goalManagementApp, instanceUrl=jdbc:google:mysql://goals-and-projects:us-central1:mysql-instance}
11:20:10 PM    Info    Inside createDatabase()
11:20:10 PM    Info    null
11:20:10 PM    Info    Inside createTable()
11:20:10 PM    Info    {rootPwd=***********, dbUrl=jdbc:google:mysql://goals-and-projects:us-central1:mysql-instance/goalManagementApp, db=goalManagementApp, instanceUrl=jdbc:google:mysql://goals-and-projects:us-central1:mysql-instance, root=root}
11:20:10 PM    Info    Failed with an error No database selected, Exception: No database selected
11:20:10 PM    Info    null

Maybe helpful references:

• https://developers.google.com/apps-script/reference/jdbc/jdbc#getcloudsqlconnectionurl,-username,-password

Answer 1

The two calls differ in the first parameter:

  const connection = Jdbc.getCloudSqlConnection(env.dbUrl, env.root, env.rootPwd);

  const connection = Jdbc.getCloudSqlConnection(env.instanceUrl, env.root, env.rootPwd);

The env.dbUrl property adds /${db}, so that is probably what's missing.