动态SQL使用JDBC和MySQL在Google Apps脚本上返回“语法错误" [英] Dynamic SQL returns 'syntax error' on Google Apps Script using JDBC and MySQL

问题描述

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

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

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

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

您的SQL语法有错误；在第1行的'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN survey_field_values.survey_fi''

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

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

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/

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

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

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

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

推荐答案

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

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.

这篇关于动态SQL使用JDBC和MySQL在Google Apps脚本上返回“语法错误"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！