动态SQL使用JDBC和MySQL在Google Apps脚本上返回“语法错误”

Question

我正在电子表格项目中使用Google Apps脚本，并使用JDBC连接到MySQL数据库。我正在尝试运行以下功能，该功能应该从我的数据库返回一些数据：

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();
}

通过Google Apps脚本运行查询时，出现以下错误：

您的SQL语法有误；在第1行的’SELECT GROUP_CONCAT（DISTINCT CONCAT（’MAX（CASE WHEN
survey_field_values.survey_fi’）

但是，直接通过MySQL控制台运行查询不会返回错误，并且会返回正确的数据。

我感觉我缺少分隔符，但是不太清楚它是什么。

/编辑/

根据Marc的建议，我对代码进行了重组，以在单独的查询中运行每个语句。这是更新的项目：

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;
}

当到达以下行：时dbHelper.executeSelectMultipleRowsQuery(prepare);，我现在收到以下错误，这几乎意味着该@sql变量在每个查询上都会重置：

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

解决方案的确像Marc B所述，以及我的编辑显示的内容。也就是说，创建单个连接并执行多个SQL选择，而不是在同一执行上运行两个选择。