mysql - Mirth:无法执行数据库查询

Question

我的问题是:如果我将下面的查询字符串更改为select * from table;，它就可以正常工作。但如果我执行下面的查询字符串，它会抛出一个错误:

SQL error: Invalid column number. Cannot less than or equal zero.

这是代码:

columnHeader = " 'id', 'pat_name', 'pat_age' ";
var filename = $('serType') + $('serId') + ".csv"; 

    var reporting_exportQuery = "select " + columnHeader + 
                        " union all " + 
                        "select * " +
                        "from " + $('serType') + 
                        " into outfile 'C://mytest/reports/service reports/"+$('serType') + "/" + "" + filename + "' " +
                        "fields terminated by ',' " +
                        "lines terminated by '\\n';";           

    var test = "select * from Age;";
    reporting_DBConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.jdbc.Driver',:D);  
    logger.info(reporting_exportQuery);
    var result = reporting_DBConn.executeCachedQuery(reporting_exportQuery);

Answer 1

您正在添加(联合)一列 columnHeader 与表中的所有列 (*)。你可以这样做

SELECT 'columnHeader',table_alias.* FROM TABLE table_alias...


var filename = $('serType') + $('serId') + ".csv"; 

var reporting_exportQuery = 
                    "select 'columnHeader',table_alias.* " +
                    "from " + $('serType') table_alias+ 
                    " into outfile 'C://mytest/reports/service reports/"+$('serType') + "/" + "" + filename + "' " +
                    "fields terminated by ',' " +
                    "lines terminated by '\\n';";           

var test = "select * from Age;";
reporting_DBConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.jdbc.Driver','jdbc:mysql://localhost:3310/cnwlreports', :D);  
logger.info(reporting_exportQuery);
var result = reporting_DBConn.executeCachedQuery(reporting_exportQuery);