JDBC Reciever query

[Former Member]

Hi All,

I have to update the fields with Dummy value where the field is not null.

I.e Update field1 with Dummy from table <u>where field2 is not null</u>.

Number of eligible records are zero for this, but in actual case there are some values.

Please let me know how should i handle this in my sql xml format.

Thanking you in advance.

Regards

Piyush

[udo_martens · Accepted solution]

Hi Piyush,

you find the documentation for the sql/xml here:

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

for example:

<root>
<StatementName1>
<dbTableName action=”UPDATE” 
<table>myTable</table>
<access>
<col1>val1</col1>
</access>
<key1>
<col2 compareOperation=”NEQ”></col2>
</key1>
</dbTableName>
</StatementName1>
</root>

what means

UPDATE myTable
SET col1='val1'
WHERE col2 <> null  

Regards,

Udo

[Former Member]

Hi Piyush,

Could u elaborate on the scenario and what is ur exact requirement.

Regards,

Sudharshan

[Former Member]

Hi All,

My problem is i am able to update the DB with the reciver JDBC adapter with key as field = value.

I want to do the update field ! = value.

And in my case,

field != null.

Sql query is running fine with field = value. But in the case filed ! = null, it's not working.

Thanking you in advance.

Regards

Piyush

[udo_martens]

Hi Piyush,

why are you asking if you dont read the answer?

Regards,

Udo

[Former Member]

Hi Udo,

Sorry if i missed a thing from your post ....

Please, forgive me.

As par the above post,

compareOperation "NEQ".

This means i have to add the attribute for that value.

Have i understood perfectly.

Thanking you in advance.

Regards

Piyush

[udo_martens]

Hi Piyush,

...<key1>
<col2 compareOperation=”NEQ”></col2>
</key1>
...

build the message like described in my first answer. Use attribute 'compareOperation' with value "NEQ" at key-element, to have where clause with "NOT EQUAL". Everything is described in posted link to library.

Regards,

Udo

[Former Member]

Hi Udo,

Thank you very much.

I rewarded points before checking.

I am confident that it will work as you suggested it with confidence.

Regards

Piyush

[Former Member]

Hi Piyush,

For your requirement you can use <b>Native SQL String</b> as Message protocol in your JDBC Adapter.

Steps.

1. Build your SQL based on your business requirement

2. Test the SQL in DB itself and check if it is working properly

3. Pass the SQL statement as it is(Plain text SQL and not XML) thru JDBC adapter.

Please refer the link for more details.

<a href="http://help.sap.com/saphelp_nw04/helpdata/en/14/80243b4a66ae0ce10000000a11402f/frameset.htm">XI JDBC Document Formats</a>

Regards,

Ananth