Google Apps脚本，JDBC和MySQL不适用于Date 0000-00-00 [英] Google Apps Script, JDBC and MySQL does not work with Date 0000-00-00

问题描述

我正在尝试使用Google Apps脚本中的以下简单代码读取MySQL数据库记录:

I am trying to read a MySQL db record using this simple code in Google Apps Script:

function testConn(){

  // make the connection
  var connection = Jdbc.getConnection("jdbc:mysql://" + DB.URL + ":" + DB.PORT + "/" + DB.DATABASE, DB.USERNAME, DB.PASSWORD);

  // perform the query
  var SQLstatement = connection.createStatement();
  var result = SQLstatement.executeQuery("SELECT date_available FROM oc_product where model = 'B23-U57-U57'");
  var colNum = result.getMetaData().getColumnCount();

  while(result.next()) {
    var res = {};
    for (var i=1; i<= colNum; i++) {
      var label = result.getMetaData().getColumnLabel(i);
      if (result.getMetaData().getColumnTypeName(i) == "DATE") {
        var val = result.getDate(i);
      } else {
        var val = result.getString(i);
      }
      res[label] = (val);
    }    
    Logger.log(JSON.stringify(res, null, 4));
  }
}

由于date_available为"0000-00-00"，因此此代码将引发错误

Because the date_available is "0000-00-00", this code throws error

值'0000-00-00'不能表示为

Value '0000-00-00' can not be represented as

根据建议此处，我尝试使用具有参数的连接网址

As suggested here I tried to use connection URL with parameter

?zeroDateTimeBehavior=convertToNull

但这也引发了错误

不支持以下连接属性:zeroDateTimeBehavior.

The following connection properties are unsupported: zeroDateTimeBehavior.

我想念什么?这是Google错误吗?为什么不支持此连接属性?除了更改SQL查询之外，是否还有其他解决方法?

What am I missing? Is this Google bug? Why this connection property is not supported? Is there any workaround apart of altering the SQL query?

推荐答案

不幸的是，没有更改SQL.但仅供参考，这是更改SQL的方法:

Not without altering SQL unfortunately. But for reference this is the way with altering the SQL:

我使用了以下建议:如何在MySQL?.并在MySql中使用FROM_UNIXTIME()函数.唯一的缺点是，将注册日期作为1970-01-01 00:00:00.0导入到电子表格中，但对我而言这不是问题.

I have used this recommendation: How to convert timestamp to datetime in MySQL?. And use the FROM_UNIXTIME() function in MySql. The only draw back was that a 0000-00-00 date will be imported in the spreadsheet as 1970-01-01 00:00:00.0 For me however this is not a problem at the moment.

示例:

var rs = stmt.executeQuery("SELECT `Subscriptions`.`SubscriptionId`,
  `Subscriptions`.`ItemId`, `Subscriptions`.`AccountId`,
  `Subscriptions`.`ContactId`,
  from_unixtime( unix_timestamp(TIMESTAMP( `Subscriptions`.`DateStart` ) ) ),
  from_unixtime( unix_timestamp(TIMESTAMP( `Subscriptions`.`DateEnd` ) ) ),
  `Subscriptions`.`AutoRenewal`,`Subscriptions`.`Invoice` from `Subscriptions`")

这篇关于Google Apps脚本，JDBC和MySQL不适用于Date 0000-00-00的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！