当通过 PHP 脚本从源表中选择时间戳(可通过定义 null 为空(并尝试插入到相同的目标表中时, 我收到错误"日期,时间或时间戳值的语法无效SQL状态22008"。我相信这是因为某些源记录为空。当脚本运行时,我成功将大约一半的记录插入到目标表中,但只有具有实际时间戳值的记录。尚未插入空记录。
我的陈述:
SELECT
TIMESTAMP_EXPIRATION
FROM TABLE1;
INSERT INTO TABLE2 (EXPIRATION_T)
VALUES ($TIMESTAMP_EXPIRATION);
问题是我的源记录中有一半是空的,另一半是 TIMESTAMP,度数为 6
源表和目标表的定义将列TIMESTAMP_EXPIRATION 表示列 0011TTT 时间戳默认值 NULL"和"EXPIRATION_T 表示列 00EETTT 时间戳默认值空">
我该怎么做才能插入空值?
更新:创建语句
CREATE TABLE TABLE1 (
ID INTEGER GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1 ),
TIMESTAMP_EXPIRATION FOR COLUMN 0011TTT TIMESTAMP DEFAULT NULL )
RCDFMT TABLE1 ;
CREATE TABLE TABLE2 (
ID INTEGER GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1 ),
EXPIRATION_T} FOR COLUMN 00EETTT TIMESTAMP DEFAULT NULL )
RCDFMT TABLE2 ;
更新:PHP 脚本
$getDev = "
SELECT
ID,
TIMESTAMP_EXPIRATION
FROM TABLE1
";
$stmt = odbc_exec($DB2connDEV, $getDev);
while($gettingItems = odbc_fetch_array($stmt)){
$rows[] = $gettingItems;
}
foreach($rows as $row){
$originalID = $row['ID'];
$expiration = $row['TIMESTAMP_EXPIRATION'];
$insertTable = "INSERT INTO table2 (id,expiration_t) VALUES (
$originalID,
'$expiration')";
$stmt = odbc_exec($DB2connPROD, $insertTable);
}
考虑使用预准备语句进行参数化,而不是字符串内插,例如将null
呈现为空字符串的以下'$expiration'
:
$apn_stmt = odbc_prepare($DB2connPROD, "INSERT INTO table2 (id, expiration_t) VALUES (?, ?)");
$sel_stmt = odbc_exec($DB2connDEV, "SELECT ID, TIMESTAMP_EXPIRATION FROM TABLE1");
while($gettingItems = odbc_fetch_array($sel_stmt)) {
$rows[] = $gettingItems;
}
foreach($rows as $row) {
$originalID = $row['ID'];
$expiration = ($row['TIMESTAMP_EXPIRATION'] == '' ? NULL : $row['TIMESTAMP_EXPIRATION']);
$insertTable = odbc_execute($apn_stmt, array($originalID, $expiration));
}