[jira] [Comment Edited] (FLINK-15728) JDBCUpsertOutputFormat does not set bind parameter keyFields in updateStatement

["Jingsong Lee (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-15728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17020745#comment-17020745 ] 

Jingsong Lee edited comment on FLINK-15728 at 1/22/20 3:28 AM:
---------------------------------------------------------------

Hi [~dasbh], thanks for reporting.

But I don't think this is a bug or I didn't get what are you mean. If you want to support H2/Oracle, Shouldn't H2Dialect be provided? The default implementation of {{JDBCDialect}} can not work to every DBs. Then, if H2/Oracle need new mechanism to implement upsert, we should improve {{JDBCDialect}} interface. 

So I think this ticket is support H2Dialect and OracleDialect?


was (Author: lzljs3620320):
Hi [~dasbh], thanks for reporting.

But I don't think this is a bug or I didn't get what are you mean. If you want to support H2/Oracle, Shouldn't H2Dialect be provided? The default implementation of {{JDBCDialect}} can not work to every DBs. Then, if H2/Oracle need new mechanism to implement upsert, we should improve {{JDBCDialect}} interface. 

So I think this ticket is support H2Dialect and OracleDialect?

 

> JDBCUpsertOutputFormat does not set bind parameter keyFields in updateStatement
> -------------------------------------------------------------------------------
>
>                 Key: FLINK-15728
>                 URL: https://issues.apache.org/jira/browse/FLINK-15728
>             Project: Flink
>          Issue Type: Bug
>          Components: Connectors / JDBC
>    Affects Versions: 1.9.1
>            Reporter: Bhagavan
>            Assignee: Bhagavan
>            Priority: Major
>
> When using JDBCUpsertOutputFormat custom dialect e.g. H2/Oracle which uses UpsertWriterUsingInsertUpdateStatement, code fails with below error.
> {code:java}
> Caused by: org.h2.jdbc.JdbcSQLDataException: Parameter "#6" is not set [90012-200] 
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:590) 
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) 
> at org.h2.message.DbException.get(DbException.java:205) 
> at org.h2.message.DbException.get(DbException.java:181) 
> at org.h2.expression.Parameter.checkSet(Parameter.java:83) 
> at org.h2.jdbc.JdbcPreparedStatement.addBatch(JdbcPreparedStatement.java:1275) 
> at org.apache.flink.api.java.io.jdbc.writer.UpsertWriter$UpsertWriterUsingInsertUpdateStatement.processOneRowInBatch(UpsertWriter.java:233) 
> at org.apache.flink.api.java.io.jdbc.writer.UpsertWriter.executeBatch(UpsertWriter.java:111) {code}
> This is due to UpsertWriterUsingInsertUpdateStatement#processOneRowInBatch does not set all bind paramters in case of Update.
> This bug does get surfaced while using Derby DB. 
>  In JDBCUpsertOutputFormatTest if we replace Derby with H2 we can reproduce the bug.
> The fix is trivial. Happy to raise PR.
> {code:java}
> //for update case replace below
> setRecordToStatement(updateStatement, fieldTypes, row); 
> //with
> setRecordToStatement(updateStatement, fieldTypes + pkTypes, row  + pkRow);
> //NOTE:  as prepared updateStatement contains additional where clause we need pass additional bind values and its sql Types
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)