DB2 SQL Error: SQLCODE=-302 while executing prepared statement

Question

I have a SQL query which takes user inputs hence security flaw is present.

The existing query is:

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+ ");

I changed it to accept TriggerType from setString like:

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 (?);

Setting trigger type as below:

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

Getting error as

Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.19.26

Answer 1

The -302 SQLCODE indicates a conversion error of some sort.

SQLSTATE 22001 narrows that down a bit by telling us that you are trying to force a big string into a small variable. Given the limited information in your question, I am guessing it is the XMLCAST that is the culprit.

DB2 won't jam 30 pounds of crap into a 4 pound bag so to speak, it gives you an error. Maybe giving XML some extra room in the cast might be a help. If you need to make sure it ends up being only 4 characters long, you could explicitly do a LEFT(XMLCAST( ... AS VARCHAR(64)), 4). That way the XMLCAST has the space it needs, but you cut it back to fit your variable on the fetch.

The other thing could be that the variable being passed to the parameter marker is too long. DB2 will guess the type and length based on the length of ALERT_TYPE. Note that you can only pass a single value through a parameter marker. If you pass a comma separated list, it will not behave as expected (unless you expect ALERT_TYPE to also contain a comma separated list). If you are getting the comma separated list from a table, you can use a sub-select instead.

Answer 2

Wrong IN predicate use with a parameter.
Do not expect that IN ('AAAA, M250, ABCD') (as you try to do passing a comma-separated string as a single parameter) works as IN ('AAAA', 'M250', 'ABCD') (as you need). These predicates are not equivalent.
You need some "string tokenizer", if you want to pass such a comma-separated string like below.

select t.*
from
(
select XMLCAST(XMLQUERY('$A/alertTypeConfig/biqCode/text()' passing IAC.INTL_ALERT_TYPE_CONFIG as "A") AS CHAR(4)) ALERT_TYPE
from table(values xmlparse(document '<alertTypeConfig><biqCode>M250, really big code</biqCode></alertTypeConfig>')) IAC(INTL_ALERT_TYPE_CONFIG)
) t
--WHERE  ALERT_TYPE IN ('AAAA, M250, ABCD')
join xmltable('for $id in tokenize($s, ",\s?") return <i>{string($id)}</i>' 
passing cast('AAA, M250 , ABCD' as varchar(200)) as "s" 
columns token varchar(200) path '.') x on x.token=t.ALERT_TYPE
;

Run the statement as is. Then you may uncomment the string with WHERE clause and comment out the rest to see what you try to do.

P.S.:
The error you get is probably because you don't specify the data type of the parameter (you don't use something like IN (cast(? as varchar(xxx))), and db2 compiler assumes that its length is equal to the length of the ALERT_TYPE expression (4 bytes).