在DB2中使用Python ibm_db包将时间戳更新为当前时间戳

Question

每次运行脚本部分时，我都试图将db2中的lastupdated时间戳设置为current timestamp

datetimes = datetime.now()
datetime1 = datetimes.strftime("%Y-%m-%d  %H:%M:%S")
datetime2 = datetime.strptime(datetime1,'%Y-%m-%d  %H:%M:%S')
print(type(datetime2))
print(datetime2)
sql = "update salesorder set LASTUPDATEUSER = 'Testing' and LASTUPDATEDATETIME = ? where code ='0888' "
prepared = ibm_db.prepare(conn, sql)
returnCode = ibm_db.bind_param(prepared,1,datetime2, ibm_db.SQL_PARAM_INPUT,ibm_db.SQL_CHAR)

if returnCode == False:
    print("unable to bind")
#ibm_db.execute(upstmt)
#param = param1
returnCode = ibm_db.execute(prepared)

if returnCode == False:
    print("unable to execut")

在运行这个脚本时，我得到了一个错误

Traceback (most recent call last):
  File "new.py", line 27, in <module>
    returnCode = ibm_db.execute(prepared)
 SQLCODE=-420atement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0420N  Invalid character found in a character string argument of the function "BOOLEAN".  SQLSTATE=22018

我也尝试过多种解决方案，比如将日期时间作为字符串传递，但解决不了这个问题。这是db2‘2020-02-21 13:37:37’中使用的时间戳格式

如果有人能提供一种方法来解决这个问题，这将是非常有帮助的

Answer 1

您的更新stmt错误。更改：

update salesorder 
    set LASTUPDATEUSER = 'Testing' and LASTUPDATEDATETIME = ? 
where code ='0888'

致：

update salesorder 
    set LASTUPDATEUSER = 'Testing'
      , LASTUPDATEDATETIME = ? 
where code ='0888'

或：

update salesorder 
    set (LASTUPDATEUSER, LASTUPDATEDATETIME) = ('Testing', ?) 
where code ='0888'

您得到的错误消息令人费解，但这是由于您试图从字符串计算布尔值：

db2 "values 'testing' and current_timestamp = current_timestamp"

1 
 
SQL0420N  Invalid character found in a character string argument of the 
function "BOOLEAN".  SQLSTATE=22018

比照

db2 "values true and current_timestamp = current_timestamp"

1 
 
 1

即使成功，您也会在尝试为时间戳列指定布尔值时出错

我想旧版本的Db2可能会在prepare期间反对，但现在类型检查要轻松得多，这会不时导致非常不直观的错误消息

也就是说，您可能需要考虑：

update salesorder 
    set (LASTUPDATEUSER, LASTUPDATEDATETIME) = ('Testing', current_timestamp) 
where code ='0888'

然后，您的代码将缩减为：

sql = """update salesorder 
        set (LASTUPDATEUSER, LASTUPDATEDATETIME) = ('Testing', current_timestamp) 
    where code ='0888'"""
prepared = ibm_db.prepare(conn, sql)
returnCode = ibm_db.execute(prepared)

if returnCode == False:
    print("unable to execut")

作为侧节点，我发现显式绑定参数有点麻烦，通常只需执行以下操作：

ibm_db.execute(prepared, (param1, ...))

如果目的只是在行发生更改时更新时间戳，请编写一个触发器：

create trigger trg1 
no cascade before update on salesorder 
referencing new as n 
for each row 
    set n.LASTUPDATEDATETIME = current_timestamp

如果将update语句更改为：

update salesorder 
    set LASTUPDATEUSER = 'Testing' 
where code ='0888'

触发器将为您执行以下操作：

db2 "select * from salesorder"

LASTUPDATEUSER       LASTUPDATEDATETIME         CODE 
                           -
Testing              2020-04-11-11.51.22.055602 0888 

  1 record(s) selected.

./aa.py

db2 "select * from salesorder"

LASTUPDATEUSER       LASTUPDATEDATETIME         CODE 
                           -
Testing              2020-04-11-11.58.50.222753 0888 

  1 record(s) selected.

根据您的Db2版本，您还可以将列声明为：

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

您可能希望始终生成，但我不认为您可以将该列更改为该列，因此您需要做更多的工作才能将其设置到位