java - JDBC SqlSyntaxErrorException but statement works in SQL tool

Question

I have a DB2 MERGE INTO statement that gives me a sql syntax exception when I try to execute it from Java, but when I put in the parameters in my SQL tool it runs just fine. I've run this repeatedly both in my SQL tool and in Java, and it always works in the former but fails in the latter. This is the SQL (table and column names have been changed to protect the innocent):

merge INTO sales_table AS target 
USING (VALUES ( CAST('2020' AS CHAR(4)), CAST('12' AS CHAR(2)), CAST('AB01' AS CHAR(4)), CAST 
      (0555.0 AS 
      DECIMAL(9)), CAST(0.000 AS DECIMAL(9)), CAST('DP079616' AS CHAR(8)) )) AS input_data ( 
      year, month, location, no, orig_no, last_upd_userid ) 
ON ( target.year = input_data.year 
     AND target.month = input_data.month 
     AND target.location = input_data.location ) 
WHEN matched THEN 
  UPDATE SET no = input_data.no, 
             orig_no = input_data.orig_no, 
             last_upd_userid = input_data.last_upd_userid, 
             last_upd_tmstmp = CURRENT TIMESTAMP 
WHEN NOT matched THEN 
  INSERT ( year, 
           month, 
           location, 
           no, 
           orig_no, 
           creation_userid, 
           creation_tmstmp, 
           last_upd_userid, 
           last_upd_tmstmp ) 
  VALUES ( input_data.year, 
           input_data.month, 
           input_data.location, 
           input_data.no, 
           input_data.orig_no, 
           input_data.last_upd_userid, 
           CURRENT TIMESTAMP, 
           input_data.last_upd_userid, 
           CURRENT TIMESTAMP )

In the query above I have replaces the ?s with hard-coded parameters that Java sets with the set... methods.

I get this exception in Java: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=null, DRIVER=4.21.29

This is the relevant Java part:

req.setString(i++, roaa.getYear());
req.setString(i++, roaa.getMonth());
req.setString(i++, roaa.getLocatoin());
req.setFloat(i++, Float.parseFloat(roaa.getSalesNumber()));
req.setFloat(i++, Float.parseFloat(roaa.getOrigSalesNumber()));
req.setString(i++, userId);
req.executeUpdate();

Any thoughts on why I'd get a syntax exception on something that works in my SQL tool? Obviously the syntax is right if it works. I've Googled the SQL code -270, and various results say it's because it violates a constraint somewhere, but again, it works in the SQL tool, so I can't help thinking I'm overlooking something simple that should be obvious to me.

question from:https://stackoverflow.com/questions/65829073/jdbc-sqlsyntaxerrorexception-but-statement-works-in-sql-tool

Answer 1

Waitting for answers