我让PHP通过ODBC和EasySoft驱动程序与DB2 (Ubuntu 14.04上的v10.5)对话。当使用select id from new table(insert ....)
格式查询时,我遇到了时间戳格式的问题。
这是我的表:
$create = "create table permissions (
id int not null generated always as identity,
name varchar(255) not null,
display_name varchar(255),
description varchar(255),
created_at timestamp default current_timestamp not null,
updated_at timestamp default current_timestamp not null
)";
$pdo->prepare($create)->execute();
这很好,就像原来的PDO insert一样:
$sth = $pdo->prepare("insert into permissions (name, display_name, description, updated_at, created_at) values (?, ?, ?, ?, ?)");
$sth->execute([
"client_admin_dashboard",
"Client Admin Dashboard",
"Can view overview of team",
"2015-07-09 14:10:50.000",
"2015-07-09 14:10:50.000"
]);
但是如果我尝试做select id
风格的查询(具有相同的绑定):
$sth = $pdo->prepare("select id from new table(insert into permissions (name, display_name, description, updated_at, created_at) values (?, ?, ?, ?, ?))");
得到以下错误:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22007]: Invalid datetime format: -180
[Easysoft][ODBC-DB2 Driver][DRDA] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007 (SQLExecute[4294967116] at
/build/php5-RvVZKb/php5-5.6.10+dfsg/ext/pdo_odbc/odbc_stmt.c:254)' in
/home/vagrant/repos/throwaway/test.php on line 65
PDOException: SQLSTATE[22007]: Invalid datetime format: -180 [Easysoft][ODBC-DB2 Driver][DRDA] SQL0180N
The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
(SQLExecute[4294967116] at /build/php5-RvVZKb/php55.6.10+dfsg/ext/pdo_odbc/odbc_stmt.c:254) in /home/vagrant/repos/throwaway/test.php
on line 65
Call Stack:
0.0007 226848 1. {main}() /home/vagrant/repos/throwaway/test.php:0
0.1021 230648 2. PDOStatement->execute() /home/vagrant/repos/throwaway/test.php:65
任何帮助将非常感激!
多谢编辑:
将变量移动到查询本身而不是使用绑定可以很好地工作:
$sth = $pdo->prepare("select id from new table(insert into permissions (name,
display_name, description, updated_at, created_at) values (
'client_admin_dashboard', 'Client Admin Dashboard', 'Can view overview of
team', '2015-07-09 14:10:50.000', '2015-07-09 14:10:50.000'))")->execute();
真的很奇怪。如果我尝试从CLP静态地执行命令,则一切正常:
select id from new table(insert into permissions (name, display_name, description, updated_at, created_at) values ( 'client_admin_dashboard', 'Client Admin Dashboard', 'Can view overview of team', '2015-07-09 14:10:50.000', '2015-07-09 14:10:50.000'))
ID
-----------
2
1 record(s) selected.
可能是ODBC驱动程序(或区域设置)在某种程度上搞乱了时间戳格式吗?为了确认这一点,我能想到的唯一方法是使用DB2 CLI和服务器跟踪,它们应该显示从客户机接收到的时间戳数据。
在旁注中,我注意到created_at
和updated_at
的顺序在失败的情况下是颠倒的:
...(insert into permissions (..., updated_at, created_at)...
一个愚蠢的问题,但是如果你改变了顺序会发生什么?
驱动程序供应商发布修复。他们很难找到问题所在:
这需要一些跟踪,因为这个问题只发生在您的PHP版本与确切的PDO-ODBC版本。即使你只差0.1个版本,也不会得到相同的结果。