Dynamic SQL returns 'syntax error' on Goog

Question

I'm using Google Apps Scripts inside a Spreadsheet project, connecting to a MySQL DB using JDBC. I'm trying to run the following function, which is supposed to return some data from my DB:

function selectAllUserSurveys() {
  var query = "SET @sql = NULL; \
SELECT GROUP_CONCAT(DISTINCT \
CONCAT('MAX(CASE WHEN survey_field_values.survey_field_option_id = ', survey_field_option_id, \
' THEN survey_field_values.value END) AS ', CONCAT('`survey_field_option_id', survey_field_option_id, '`')) \
) INTO @sql \
FROM survey_field_values \
LEFT JOIN surveys \
ON survey_field_values.survey_id = surveys.id \
WHERE surveys.survey_type = 'client'; \
SET @sql = CONCAT('SELECT surveys.bid_id, user.rep_name, 0, ', @sql, ' \
                   FROM surveys \
                   INNER JOIN user \
                        ON surveys.user_id = user.username \
                   LEFT JOIN survey_field_values \
                        ON surveys.id = survey_field_values.survey_id \
                   WHERE surveys.survey_type = \'client\' \
                   AND surveys.is_filled = 1 \
                   GROUP BY surveys.id \
                   ORDER BY surveys.date_filled ASC'); \
PREPARE stmt FROM @sql; \
EXECUTE stmt; \
DEALLOCATE PREPARE stmt;";
  return executeSelectMultipleRowsQuery(query);
}

function executeSelectMultipleRowsQuery(query) {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  var start = new Date();
  var stmt = conn.createStatement();
  var results = stmt.executeQuery(query);
  var numCols = results.getMetaData().getColumnCount();
  return results;
  results.close();
  stmt.close();
}

When running the query through Google Apps Script, I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN survey_field_values.survey_fi' at line 1

However, running the query directly through the MySQL console does not return an error, and the correct data is returned.

I have a feeling I'm missing something with the delimiters, but not quite sure what it is.

/EDIT/

Using Marc's advice, I've restructure the code to run each statement in a separate query. Here's the updated project:

function selectAllUserSurveys() {
  dbHelper = new DBHelper();
  var setsql = "SET @sql = NULL";
  dbHelper.executeSelectMultipleRowsQuery(setsql);
  var dynamicColumns = "SELECT GROUP_CONCAT(DISTINCT \
CONCAT('MAX(CASE WHEN survey_field_values.survey_field_option_id = ', survey_field_option_id, \
' THEN survey_field_values.value END) AS ', CONCAT('`survey_field_option_id', survey_field_option_id, '`')) \
) INTO @sql \
FROM survey_field_values \
LEFT JOIN surveys \
ON survey_field_values.survey_id = surveys.id \
WHERE surveys.survey_type = 'client'";
  Logger.log(dbHelper.executeSelectMultipleRowsQuery(dynamicColumns));

  var mainquery = "SET @sql = CONCAT('SELECT surveys.bid_id, user.rep_name, 0, ', @sql, ' \
                   FROM surveys \
                   INNER JOIN user \
                        ON surveys.user_id = user.username \
                   LEFT JOIN survey_field_values \
                        ON surveys.id = survey_field_values.survey_id \
                   WHERE surveys.survey_type = \\'client\\' \
                   AND surveys.is_filled = 1 \
                   GROUP BY surveys.id \
                   ORDER BY surveys.date_filled ASC')"
  dbHelper.executeSelectMultipleRowsQuery(mainquery);
  var prepare = "PREPARE stmt FROM @sql";
  dbHelper.executeSelectMultipleRowsQuery(prepare);
  var execute = "EXECUTE stmt";
  var return_value = dbHelper.executeSelectMultipleRowsQuery(execute);
  var deallocate = "DEALLOCATE PREPARE stmt;";
  dbHelper.executeSelectMultipleRowsQuery(deallocate);
  Logger.log(return_value);
  return return_Value;
}

function DBHelper() {
  this.conn = Jdbc.getConnection(dbUrl, user, userPwd);
  this.stmt = this.conn.createStatement();
}

DBHelper.prototype.executeSelectMultipleRowsQuery = function(query) {
  var results = this.stmt.executeQuery(query);
  var numCols = results.getMetaData().getColumnCount();
  return results;
}

When reaching this line: dbHelper.executeSelectMultipleRowsQuery(prepare);, I now recieve the following error, which pretty much means the @sql variable is reset on each query:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Answer 1

The solution was indeed like Marc B described, and what my edit shows. That is, creating a single connection, and executing multiple SQL selects instead of running two selects on the same execution.