DB2 SQL错误：执行准备好的语句时出现SQLCODE=-302

Question

SELECT  BUS_NM, STR_ADDR_1, CITY_NM, STATE_CD, POSTAL_CD, COUNTRY_CD,
        BUS_PHONE_NB,PEG_ACCOUNT_ID, GDN_ALERT_ID, GBIN, GDN_MON_REF_NB,
        ALERT_DT, ALERT_TYPE, ALERT_DESC,ALERT_PRIORITY 
FROM ( SELECT A.BUS_NM, AE.STR_ADDR_1, A.CITY_NM, A.STATE_CD, A.POSTAL_CD,
              CC.COUNTRY_CD,  A.BUS_PHONE_NB, A.PEG_ACCOUNT_ID, 'I' || 
              LPAD(INTL_ALERT_DTL_ID, 9,'0') GDN_ALERT_ID, 
              LPAD(IA.GBIN, 9,'0') GBIN, IA.GDN_MON_REF_NB,
              DATE(IAD.ALERT_TS) ALERT_DT, 
              XMLCAST(XMLQUERY('$A/alertTypeConfig/biqCode/text()' passing  
                IAC.INTL_ALERT_TYPE_CONFIG as "A") AS CHAR(4)) ALERT_TYPE, 
              , ROW_NUMBER() OVER () AS "RN" 
       FROM ACCOUNT A, Other tables 
       WHERE IA.GDN_MON_REF_NB = '100'  
         AND A.PEG_ACCOUNT_ID = IAAR.PEG_ACCOUNT_ID  
         AND CC.COUNTRY_CD = A.COUNTRY_ISO3_CD 
       ORDER BY IA.INTL_ALERT_ID ASC )  
WHERE  ALERT_TYPE  IN (" +TriggerType+ ");

我将其更改为接受setString中的TriggerType，如下所示：

SELECT BUS_NM, STR_ADDR_1, CITY_NM, STATE_CD, POSTAL_CD, COUNTRY_CD,
       BUS_PHONE_NB,PEG_ACCOUNT_ID, GDN_ALERT_ID, GBIN, GDN_MON_REF_NB,
       ALERT_DT, ALERT_TYPE, ALERT_DESC,ALERT_PRIORITY 
FROM ( SELECT A.BUS_NM, AE.STR_ADDR_1, A.CITY_NM, A.STATE_CD, A.POSTAL_CD, 
              CC.COUNTRY_CD,  A.BUS_PHONE_NB, A.PEG_ACCOUNT_ID, 
              'I' || LPAD(INTL_ALERT_DTL_ID, 9,'0') GDN_ALERT_ID,
              LPAD(IA.GBIN, 9,'0') GBIN, IA.GDN_MON_REF_NB, 
              DATE(IAD.ALERT_TS) ALERT_DT, 
              XMLCAST(XMLQUERY('$A/alertTypeConfig/biqCode/text()' passing  
                IAC.INTL_ALERT_TYPE_CONFIG as "A") AS CHAR(4)) ALERT_TYPE, 
              ROW_NUMBER() OVER () AS "RN" 
       FROM ACCOUNT A, other tables 
       WHERE IA.GDN_MON_REF_NB = '100'  
         AND A.PEG_ACCOUNT_ID = IAAR.PEG_ACCOUNT_ID   
         AND CC.COUNTRY_CD = A.COUNTRY_ISO3_CD 
       ORDER BY IA.INTL_ALERT_ID ASC )  
WHERE  ALERT_TYPE  IN (?);

设置触发器类型如下：

if (StringUtils.isNotBlank(request.getTriggerType())) {
   preparedStatement.setString(1, triggerType != null ? triggerType.toString() : "");
}

获取错误为

原因:com.ibm.DB2.jcc.am.sqldataexception:DB2 SQL错误:sqlcode=-302,sqlstate=22001,sqlerrmc=null,driver=4.19.26

Answer 1

-302 SQLCODE表示某种类型的转换错误。

SQLState22001通过告诉我们您正在尝试将一个大字符串强制化为一个小变量来缩小范围。鉴于您的问题中的信息有限，我猜是xmlcast才是罪魁祸首。

DB2不会把30磅的垃圾塞进一个4磅的袋子里，也就是说，它会给您一个错误。也许在演员阵容中给XML一些额外的空间会有帮助。如果您需要确保它最终只有4个字符长，可以显式地执行left(XMLCAST(...asvarchar(64)),4)。这样，xmlcast就有了它所需要的空间，但您可以将其缩减以适应提取时的变量。