Override google apps script 32768 characters limit on Exception: Argument too large: sql

Question

I used Google apps script GAS to upload my data to mysql server. If the statement is over 32768 characters, the function returns error. I think it is because of the Google cache issue for 16 bit in length. How to increase the limit? Here is my code:

mysql_statement = "INSERT INTO table (abc_id) VALUES ( xxx "
Logger.log(mysql_statement.length) // 32769
conn.createStatement().execute(mysql_statement)

Returns : Info Exception: Argument too large: sql

Answer 1

TLDR; don't put your whole SQL statement into prepareStatement, but instead use it to create your template statement with ? as placeholders for arguments and then feed those arguments with setString()

Taking your example, your SQL string INSERT INTO table (abc_id) VALUES ( xxx probably contains a large volume of data in the VALUES statement. Instead of putting the whole thing into prepareStatement like you did, you should refactor it like so:

function main() {
  const valuesToInsert = ['a', 'b', 'c'];
  const templateAr = ['INSERT INTO table (abc_id) VALUES (', ')'];
  const sqlStatement = createStatement(templateAr, valuesToInsert.length);
  executeSql(sqlStatement, valuesToInsert, ENV.connection);
}

function createStatement(templateAr, numArgs) {
  const ar = [];
  for (let i = 0; i < numArgs; i++) ar.push('?');
  return templateAr[0] + ar.join(', ') + templateAr[1];
}

function executeSql(sqlStatement, args, connection) {
  const { dbUrl, user, userPwd } = connection;
  const conn = const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
  const sqlQy = conn.prepareStatement(sqlStatement);
  if (args) {
    for (var i = 0; i < args.length; i++) {
      sqlQy.setString(i + 1, args[i]);
    }
  }
  sqlQy.execute(args);
}

The error you encountered is only caused by putting too much data into createStatement(), but setString() can handle much higher volumes.

Check out Writing to the database in the documentation.