使用Python ibm_db更新DB2时间戳报错SQL0420N求助

Hey there, let's break down and fix your issue step by step!

Root Cause of the SQL0420N Error #

The main problem here is a syntax mistake in your UPDATE statement. You used AND to separate columns in the SET clause, which is incorrect. DB2 interprets this as an attempt to evaluate a boolean expression, and tries to convert your timestamp value into a boolean—hence the "invalid character in a character string argument of the function BOOLEAN" error.

Solution 1: Use DB2's Built-in Timestamp Function (Simplest Approach) #

Instead of generating the timestamp in Python and passing it as a parameter, let DB2 handle this directly with its CURRENT_TIMESTAMP function. This avoids any parameter binding issues entirely:

# Corrected SQL: use comma to separate SET columns, and CURRENT_TIMESTAMP for the timestamp
sql = "UPDATE salesorder SET LASTUPDATEUSER = 'Testing', LASTUPDATEDATETIME = CURRENT_TIMESTAMP WHERE code = '0888'"
prepared = ibm_db.prepare(conn, sql)

returnCode = ibm_db.execute(prepared)
if returnCode == False:
    print("unable to execute")

Solution 2: Properly Bind a Python Datetime Object #

If you need to control the timestamp value (instead of using DB2's current time), fix the SQL syntax and bind the datetime object correctly with the right parameter type:

from datetime import datetime

# Get current datetime directly—no need to format/parse it back
current_datetime = datetime.now()

# Fix SQL syntax: replace AND with comma in SET clause
sql = "UPDATE salesorder SET LASTUPDATEUSER = 'Testing', LASTUPDATEDATETIME = ? WHERE code = '0888'"
prepared = ibm_db.prepare(conn, sql)

# Bind using SQL_TIMESTAMP type instead of SQL_CHAR—this matches DB2's timestamp column
returnCode = ibm_db.bind_param(prepared, 1, current_datetime, ibm_db.SQL_PARAM_INPUT, ibm_db.SQL_TIMESTAMP)
if returnCode == False:
    print("unable to bind")

returnCode = ibm_db.execute(prepared)
if returnCode == False:
    print("unable to execute")

Why Your Original Code Failed #

1. Incorrect SQL Syntax: Using AND instead of a comma between columns in SET confused DB2 into treating the timestamp assignment as a boolean function argument.
2. Unnecessary Datetime Conversion: Converting the datetime to a string and back to a datetime object was redundant, and binding it as SQL_CHAR forced DB2 to do an implicit type conversion that could fail with formatting mismatches.

内容的提问来源于stack exchange，提问作者JJJ