JDBC receiver adapter - sqlBindMode

[former_member185846]

Hi,

I need to perform Delete all existing records from a table (statement1) and Insert the new set of records into the same table (statement2). And in order to improve the performance, I've tried to use sqlBindMode in the JDBC receiver adapter. But, sqlBindMode is throwing the below error for delete statement.  Is it because the delete statement missing the where clause? Any idea how this can be fixed?

(structure 'Statement_Delete'): java.lang.NullPointerException: while trying to invoke the method java.util.ArrayList.size() of an object loaded from local variable 'whereArray'

Thanks

[former_member185846]

Thanks for the details, Greg. But, unfortunately I don't have to access service marketplace now. And I've not checked 'Database Auto-Commit-Enabled'.

[baskar_gopalakrishnan2]

Please make sure there is no case sensitive issues in your configuration

SAP Note 1404778 - Description

To leverage the Bind variables functionality in JDBC Adapter, we have an option in the advanced option table #sqlBindMode# (This is case sensitive and value of this option should be #true# or #false# without quotes) in the receiver channel configuration.

We can use bind variables for those payloads that are similar in modifying/querying the fields. Every action tag for single table has to be exactly similar in modifying/querying column fields. We can not use bind variables if two action tags are not accessing/modifying the same columns, although two action tags referring to a single table.

This option should be used only for UPDATE, INSERT, DELETE and SELECT Action types and shouldn#t be used with other action types(like UPDATE_INSERT, SQL EXECUTE).

[former_member185846]

No, there's no problem with the case..not sure if its failing bcoz in delete statement no column name is mentioned whereas in insert column names are specificed??

[former_member184681]

Hi,

You just suggested me some idea. Maybe your receiver structure for DELETE statement is not in agreement with PI expecatations? Have a look at the examples in SAP Help here to verify:

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards,

Greg

[former_member185846]

It works well without sqlBindMode..so, the statement structure is valid

[baskar_gopalakrishnan2]

> bcoz in delete statement no column name is mentioned whereas in insert column names are specificed??

I dont understand this. You specify ? for column names irrespective of delete or insert statements, right? This should work for both. As per SAP notes this should work as explained. Please provide examples. At the same time I don't see any document/blog related to preparedstatement vs jdbc adapter scenario. You might want to check with SAP about this.

[former_member185846]

syntax for delete statement is "Delete from tablename"..so no column names mentioned here..whereas in Insert statement we specify column names

[baskar_gopalakrishnan2]

Just for testing purpose.. Why don't you add where clause and specify column names and see if that works..  Example..

"Delete from Tablename where column_name =?"

[former_member185846]

that's what i've been thinking..but, the document says both the statements should contain equal columns..so, whether all columns to be specified in the delete statement?

[baskar_gopalakrishnan2]

Specify the columns as desired in the delete statment and see how that helps...

[Former Member]

This message was moderated.

[former_member184681]

Hi,

Have a look at SAP Note 1404778 - Prepared Statement usage in JDBC Reciever Adapter, which descibes the sqlBindMode in details. Additionally, make sure you are not using the "Database Auto-Commit-Enabled(No Transaction Handling)" option, since it should be disabled when using sqlBindMode (refer to Note 831162).

Regards,

Greg