#362 Merge prepared statement doesn't allow date parameter in case of using oracle merge style

[Pavel Moukhataev]

This fails on hsql but works on oracle:

        PreparedStatement preparedStatement1 = con.prepareStatement("""
                MERGE INTO TEST1 dest 
                USING (select ? AS ID, ? AS DATE1, ? AS TIMESTAMP1, ? AS NUMBER1, ? AS CLOB1, ? AS VARCHAR2_1 FROM dual) src  
                ON (src.ID = dest.ID)  
                WHEN MATCHED THEN 
                    UPDATE SET dest.DATE1 = src.DATE1, dest.TIMESTAMP1 = src.TIMESTAMP1, dest.NUMBER1 = src.NUMBER1, dest.CLOB1 = src.CLOB1, dest.VARCHAR2_1 = src.VARCHAR2_1
                WHEN NOT MATCHED THEN 
                    INSERT (ID, DATE1, TIMESTAMP1, NUMBER1, CLOB1, VARCHAR2_1) VALUES (src.ID, src.DATE1, src.TIMESTAMP1, src.NUMBER1, src.CLOB1, src.VARCHAR2_1)
                """);

        preparedStatement1.setDate(2, new Date(System.currentTimeMillis()));
throws org.hsqldb.HsqlException: incompatible data type in conversion

This is because hsqldb treats all merge parameter types as varchar:

        ParameterMetaData parameterMetaData = preparedStatement1.getParameterMetaData();
        for (int i = 1; i <= parameterMetaData.getParameterCount(); i++) {
            log.info("Type[{}]: {}", i, parameterMetaData.getParameterTypeName(i));
        }

output:        
Type[*]: VARCHAR        

Oracle doesn't have this problem and allows setDate for merge prepared statement.

Also oracle doesn't provide types at all for merge and reports:

Unsupported feature: checkValidIndex

See examples for hsqldb, oracle and h2: https://github.com/mpashka/home-incubator/tree/master/java-tests/misc/hsqldb/src/test/java/org/test/mpashka/hsqldb

[Fred Toussi]

You can indicate the intended type using a CAST:

USING (select ? AS ID, CAST(? AS DATE) AS DATE1, CAST(? AS TIMESTAMP) AS TIMESTAMP1,

We may support setting the date and timestamp without a parameter cast in the next release.

[Fred Toussi]

Support has been committed to SVN for the next release.