我有一个Python脚本，它对一些 SELECT 查询分配各自的定时器并使用线程，每个查询都在设定的间隔内定期执行。
我有一个SQL Server Express数据库来存储我获取的表，所有表都来自IBM DB2数据库。
当脚本运行时，我得到如下错误：

查询错误发生在TABLE1：('22001', '[22001] [IBM][System i Access ODBC Driver]列1：COLUMN1 - 输入数据太大，无法放入字段（30207）（SQLExecDirectW）；[22001] [IBM][System i Access ODBC Driver]列1：字符数据右截断。 (30126)')

几乎所有的列和表都显示同样的错误。

这是我的代码：

import threading
import time
import pyodbc

class DB2Worker(threading.Thread):
    def __init__(self, interval, query, params, target_table, db2_conn_str, local_conn_str):
        super().__init__()
        self.interval = interval
        self.query = query
        self.params = params
        self.target_table = target_table
        self.db2_conn_str = db2_conn_str
        self.local_conn_str = local_conn_str
        self.daemon = True
        self.db2_conn = None

    def _get_db2_connection(self):
        """Maintains the connection to the remote DB2 source."""
        while self.db2_conn is None:
            try:
                self.db2_conn = pyodbc.connect(self.db2_conn_str)
                self.db2_conn.autocommit = True
            except Exception as e:
                print(f"DB2 Connection Error: {e}. Retrying in 5s...")
                time.sleep(5)

    def _save_to_local_sql(self, data, db2_description):
        """Dynamic mapping to Local SQL Express."""
        if not data:
            return

        try:
            column_names = [col[0] for col in db2_description]
            cols_str = ", ".join(column_names)
            placeholders = ",".join(["?" for _ in column_names])


            insert_sql = f"INSERT INTO {self.target_table} ({cols_str}) VALUES ({placeholders})"

            with pyodbc.connect(self.local_conn_str) as local_conn:
                with local_conn.cursor() as cursor:
                    cursor.fast_executemany = True

                    cursor.executemany(insert_sql, data)
                    local_conn.commit()
                    print(f"[{self.target_table}] Successfully synced {len(data)} rows.")
        except Exception as e:
            print(f"Local SQL Save Error for {self.target_table}: {e}")

    def run(self):
        self._get_db2_connection()

        while True:
            try:
                cursor = self.db2_conn.cursor()

                if self.params:
                    cursor.execute(self.query, self.params)
                else:
                    cursor.execute(self.query)

                if cursor.description:
                    results = cursor.fetchall()
                    if results:
                        self._save_to_local_sql(results, cursor.description)

                cursor.close()
            except (pyodbc.Error, Exception) as e:
                print(f"Query Error on {self.target_table}: {e}")
                self.db2_conn = None
                self._get_db2_connection()

            time.sleep(self.interval)

DB2_CONNECTION_STRING = (
    "DRIVER={iSeries Access ODBC Driver};"
    "DATABASE=DB2;"
    "SYSTEM=192.168.10.1;"
    "PORT=446;"
    "PROTOCOL=TCPIP;"
    "UID=USERNAME;"
    "PWD=PASSWORD;"
    "ConnectTimeout=10;"
    "LongDataCompat=1;"
    "TrimChar=1;"
)

LOCAL_SQL_CONNECTION_STRING = (
    "Driver={ODBC Driver 18 for SQL Server};"
    "Server=SERVER\\SERVER;"
    "Database=DB;"
    "Trusted_Connection=yes;"
    "Encrypt=yes;"
    "TrustServerCertificate=yes;"
)

query_tasks = [
    # 1. Queries WITH tags (using '?' and raw strings)
    (1, 'SELECT "G1ST" FROM "DB2"."TABLE1" WHERE "G1R" = ?', (r'192.168.64.1\Tag1',), "TABLE1"),
    (0.5, '''SELECT "FWF","FWCF","FWI","FWWSF3","FWWSI3" FROM "DB2"."TABLE2" WHERE "FWM" = 'LOC1' AND "FWR" = ? ORDER BY "FWF" ASC''', (r'192.168.64.1\Tag2',), "TABLE2"),
    (1, 'SELECT "G1SB" FROM "DB2"."TABLE1" WHERE "G1R" = ?', (r'192.168.64.1\Tag3',), "TABLE1"),

    # 2. Queries WITHOUT tags (Standard SQL)
    (1.0, '''SELECT "B1R","B1B" FROM "DB2"."TABLE3" WHERE "B1W" = 'LOC3' ORDER BY "B1X" DESC , "B1T" DESC''', None, "TABLE3"),
    (1.0, '''SELECT "GSR","GSS" FROM "DB2"."TABLE4" WHERE "GSM" = 'LOC1' AND "GSA" < 7 ORDER BY "GSS" ASC''', None, "TABLE4"),
    (1.0, '''SELECT "GSR" FROM "DB2"."TABLE4" WHERE "GSM" = 'LOC1' AND "GSA" = 5 ORDER BY "GSS" DESC''', None, "TABLE4"),
    (0.5, '''SELECT "G7G","G7F" FROM "DB2"."TABLE5" WHERE "G7CM" = 'LOC1' ORDER BY "G7CJ" DESC , "G7T" DESC''', None, "TABLE5"),
    (1.5, '''SELECT "G7W","G7R","G7MAP" FROM "DB2"."TABLE6" WHERE "G7MAC" = 'LOC1' ''', None, "TABLE6"),
    (1.5, '''SELECT "G7W","G7R","G7MAP" FROM "DB2"."TABLE6" WHERE "G7MAC" = 'LOC1' ''', None, "TABLE6")
]

if __name__ == "__main__":
    for interval, sql, params, target_table in query_tasks:
        worker = DB2Worker(interval, sql, params, target_table, 
                           DB2_CONNECTION_STRING, LOCAL_SQL_CONNECTION_STRING)
        worker.start()
        time.sleep(0.5)

    while True:
        time.sleep(10)

我尝试把数据库中的数据类型从 NVARCHAR(MAX) 改为 NVARCHAR(255) 或 NVARCHAR(100)。我检查过，能正常工作的这两个表所使用的每种数据类型，在其他表中也都存在。我也尝试使用trim去除空格，但都不起作用。

这是我用来生成SQL Server Express数据库的SQL文件：

USE DB;
GO

IF OBJECT_ID('TABLE3', 'U') IS NOT NULL 
    DROP TABLE TABLE3;

CREATE TABLE TABLE3 
(
        B1R NVARCHAR(100),
        B1B NVARCHAR(100),
        B1W NVARCHAR(100),
        B1X INT,
        B1T INT
);

IF OBJECT_ID('TABLE1', 'U') IS NOT NULL 
    DROP TABLE TABLE1;

CREATE TABLE TABLE1 
(
        G1R NVARCHAR(100),
        G1ST NVARCHAR(100),
        G1SB  NVARCHAR(100),
        G1AF FLOAT, G1AI FLOAT, G1AT FLOAT, G1AD FLOAT,
        G1CL FLOAT, G1DTYP NVARCHAR(100), G1TDTE INT, G1TMCH NVARCHAR(100),
        G1DDTE INT, G1DMCH NVARCHAR(100), G1DLOT NVARCHAR(100),
        G1TWGT FLOAT, G1PRDG NVARCHAR(100), G1SIZE NVARCHAR(100),
        G1CM NVARCHAR(255)
);

IF OBJECT_ID('TABLE7', 'U') IS NOT NULL 
    DROP TABLE TABLE7;

CREATE TABLE TABLE7 
(
        FYS NVARCHAR(100),
        FYC  NVARCHAR(100)
);

IF OBJECT_ID('TABLE8', 'U') IS NOT NULL 
    DROP TABLE TABLE8;

CREATE TABLE TABLE8 
(
        FZST NVARCHAR(100),
        FZSI NVARCHAR(100),
        FZB NVARCHAR(100),
        FZR INT,
        FZG FLOAT, FZTW FLOAT, FZTS FLOAT, FZTH FLOAT, 
        FZT FLOAT, FZTL FLOAT, FZSTE INT, FZPAT  NVARCHAR(100), 
        FZL FLOAT, FZQTY1 FLOAT, FZL2 FLOAT, FZY FLOAT,
        FZI INT
);

IF OBJECT_ID('TABLE9', 'U') IS NOT NULL 
    DROP TABLE TABLE9;

CREATE TABLE TABLE9 
(
        FZS NVARCHAR(100),
        FZD NVARCHAR(100),
        FZF NVARCHAR(100)
);

IF OBJECT_ID('TABLE10', 'U') IS NOT NULL 
    DROP TABLE TABLE10;

CREATE TABLE TABLE10 
(
        FYM NVARCHAR(100),
        FYS NVARCHAR(100),
        FYO FLOAT
);

IF OBJECT_ID('TABLE11', 'U') IS NOT NULL 
    DROP TABLE TABLE11;

CREATE TABLE TABLE11 
(
        G51S NVARCHAR(100),
        G51I NVARCHAR(100)
);

IF OBJECT_ID('TABLE4', 'U') IS NOT NULL 
    DROP TABLE TABLE4;

CREATE TABLE TABLE4 
(
        GSM NVARCHAR(100),
        GSR NVARCHAR(100),
        GSS INT,
        GSA  INT
);

IF OBJECT_ID('TABLE5', 'U') IS NOT NULL 
    DROP TABLE TABLE5;

CREATE TABLE TABLE5
(
    G7G NVARCHAR(100),
    G7F NVARCHAR(100),
    G7CM NVARCHAR(100),
    G7CJ INT,
    G7T  INT
);

IF OBJECT_ID('TABLE2', 'U') IS NOT NULL 
    DROP TABLE TABLE2;

CREATE TABLE TABLE2 
(
    FWM NVARCHAR(100),
    FWR NVARCHAR(100),
    FWF INT,
    FWCF FLOAT, FWI FLOAT, FWWSF3 FLOAT, FWWSI3 FLOAT
);

IF OBJECT_ID('TABLE6', 'U') IS NOT NULL 
    DROP TABLE TABLE6;

CREATE TABLE TABLE6 
(
    G7MAC NVARCHAR(100),
    G7W NVARCHAR(100),
    G7R FLOAT,
    G7MAP FLOAT
);

解决方案

错误来自IBM iSeries ODBC驱动，在 cursor.execute() 的参数绑定阶段，在还未提取任何行时就发生。驱动程序会检查DB2的列定义（例如 G1R CHAR(10)），如果绑定的参数长度超过该列声明的大小，就会被拒绝。错误消息中的 SQLExecDirectW 表明它是在执行阶段失败，而不是在获取阶段失败。

你的参数 r'192.168.64.1\Tag1' 长度为18个字符。如果它绑定的DB2列比18少，是 CHAR 或 VARCHAR 类型的列，那么驱动就会抛出22001。大多数其他ODBC驱动会默默截断或强制转换；而IBM iSeries驱动则更严格。

在WHERE子句中将列CAST为更宽的类型

这既保持参数化查询（有助于类型安全），又移除了大小约束：

# Before
'SELECT "G1ST" FROM "DB2"."TABLE1" WHERE "G1R" = ?'

# After
'SELECT "G1ST" FROM "DB2"."TABLE1" WHERE CAST("G1R" AS VARCHAR(100)) = ?'

将此应用于所有在字符串列上使用 = ? 的查询。