如何将ISO8601 TSQL DATETIME参数与PDO绑定? [英] How to bind ISO8601 TSQL DATETIME parameter with PDO?

问题描述

PDO似乎与 ISO 8601 格式的时间戳有关.

It seems that PDO has a problem with ISO 8601 formatted timestamps.

我正在使用用于SQLServer®的Microsoft®ODBC驱动程序13(预览版)

这是我的简单桌子:

CREATE TABLE dtest (
    "stamp" DATETIME
);

作品:

$pdoDB = new PDO('odbc:Driver=ODBC Driver 13 for SQL Server;
  Server='.DATABASE_SERVER.';
  Database='.DATABASE_NAME,
  DATABASE_USERNAME,
  DATABASE_PASSWORD
);
$pdoDB->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql = "INSERT INTO dtest (stamp) VALUES ('2011-03-15T10:23:01')";
$stmt = $pdoDB->prepare($sql);
$params = [];
$stmt->execute($params);

不起作用:

$sql = "INSERT INTO dtest (stamp) VALUES (?)";
$stmt = $pdoDB->prepare($sql);
$params = ['2011-03-15T10:23:01'];
$stmt->execute($params);

致命错误:未捕获的PDOException:SQLSTATE [22018]:强制转换规范的字符值:0 [Microsoft] [SQL Server的ODBC驱动程序13]强制转换规范的字符值(/build/php7中的SQLExecute [0]. 0-lPMnpS/php7.0-7.0.8/ext/pdo_odbc/odbc_stmt.c:260)

Fatal error: Uncaught PDOException: SQLSTATE[22018]: Invalid character value for cast specification: 0 [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for cast specification (SQLExecute[0] at /build/php7.0-lPMnpS/php7.0-7.0.8/ext/pdo_odbc/odbc_stmt.c:260)

这有效，如果我删除T以便'2011-03-15T10:23:01'变为'2011-03-15 10:23:01'

This works if I delete the T so '2011-03-15T10:23:01' becomes '2011-03-15 10:23:01'

$sql = "INSERT INTO dtest (stamp) VALUES (?)";
$stmt = $pdoDB->prepare($sql);
$params = ['2011-03-15 10:23:01'];
$stmt->execute($params);

但是我正在编写一个脚本，该脚本每晚运行约200万条记录，因此我会真的而不是承担运行数百万个

But I'm writing a script that runs nightly on about 2 million records, so I'd really rather not bear the overhead of running millions of str_replace('T', ' ', $param)

我也尝试使用 bindParam ，但是它给出了相同的错误:

$sql  = "INSERT INTO dtest (stamp) VALUES (:tdate)";
$stmt = $pdoDB->prepare($sql);
$date = '2011-03-15T10:23:01';
$stmt->bindParam(':tdate',$date,PDO::PARAM_STR);
$stmt->execute();

是否仍然可以按原样绑定和执行此参数?我对错误消息有点怀疑，因为它似乎是从SQL Server发出的，好像PDO可以很好地完成工作，但这没有任何意义，因为它可以处理类型转换而无需参数化.

Is there anyway to bind and execute this parameter as is? I'm a little dubious of the error message because it appears to be coming from SQL Server as if PDO did its job fine, but that doesn't make sense since it's able to handle the type conversion without parameterization.

我也尝试过SQL转换:

I've also tried SQL conversion:

$sql = "INSERT INTO dtest (stamp) VALUES (CONVERT(DATETIME, '2011-03-15T10:23:02', 126))";
$stmt = $pdoDB->prepare($sql);
$params = [];
$stmt->execute($params);

不起作用:

$sql = "INSERT INTO dtest (stamp) VALUES (CONVERT(DATETIME, ?, 126))";
$stmt = $pdoDB->prepare($sql);
$params = ['2011-03-15T10:23:02'];
$stmt->execute($params);

推荐答案

花了半天时间尝试解决同一问题之后，我最终放弃了odbc并改用dblib.我安装了php7.0-sybase软件包，改编了我的PDO连接的数据源名称，并彻底解决了一次. 现在每个绑定都在起作用.

After half a day spent trying to resolve the same issue, I ended up dropping odbc and using dblib instead. I installed php7.0-sybase package, adapted the data source name of my PDO connection and resolved once for all. Now every bind is working.

这篇关于如何将ISO8601 TSQL DATETIME参数与PDO绑定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！