null in a SQL_DML action

[Former Member]

Hi everyone...

I've one scenario JP <> XI <> JDBC in wich the statement has a SQL_DML action, in the access string I've placeholder with all the fields, but some fields could be empty or with null value, and when the DBM execute the statement is gave me the following exception missing expresion, that is because the null value.

How can I handle the fields with null value when the DMS execute it?

I supose that the final statement is something like that:

UPDATE XXX SET FIELD1 = , FIELD2 = hisValue WHERE XXX

Any ideas?

Thanks

[Former Member]

HI,

Use ACTION = SQL_QUERY ,

or write a stored procedure and use it in JDBC Adapter exec storedprocedure name.

in procedure

update (tablename) set field1 = &field1value,field2 =&field2 value .. somithing like this

see more on stored procedures

see more details in the following links..

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/1d34d51d-0b01-0010-eeb0-9856a143...

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/4d7aeb7d-0c01-0010-fa8a-a4a8e896...

Reagards

Chilla..

[Former Member]

Sorry...

in the example the tab for the field1 is something like that

<FIELD1 />

that is the field has a empty value.

Any idea?

[Former Member]

Can you try setting the logSQLStatement parameter as described in the OSS note - 801367 - and see how the SQL statement is formed by the XI jdbc adapter?

Thanks.

[prabhu_s2]

can u check with:

<FIELD1 compareOperation=”EQ”> </FIELD1>

[Former Member]

OK Luis let me explain you...

I've one scenario in which the inbound interface is JDBC, the action used is SQL_DML, that is in the access tab I've to put a string with the statement that I want to execute in the DBM, specificly is an UPDATE statement. In the SET part of the statement there are many placeholders that look in the key tab for its value, the problem is when the field has a empty value, and when the DMS excecute it in the placeholder assing the empty value but I need to assing null value, for example:

...

<access>UPDATE tableName SET FIELD1=$FIELD1$, FIELD2=$FIELD2$ WHEN FIELD3=$FIELD3$</access>

<key>

<FIELD1>value1</FIELD1>

<FIELD2>value2</FIELD2>

<FIELD3>value3</FIELD3>

</key>

[Former Member]

Thanks Sudharshan for you reply...

The parameter has the value "NULL value", and in this way is that I need it.

I did the following tests:

1.- Assing null to a constant and then assing it to the field, and it doesn't work.

2.- Put the null value in the access string directly, like this FIELD1 = null and work, but I need to put the current value and probably is diferent of null.

How could I fix that problem?

[Former Member]

Hector,

Don't give directly null to ur field.

[code]

<root>

<StatementName6>

<anyName action= ”SQL_DML”>

<access>UPDATE XXX SET FIELD1 = ’$f1$’ , FIELD2 = hisValue WHERE XXX</access>

<key>

<f1></f1>

</key>

</anyName >

</StatementName6>

</root>[/code]

For the fiels f1 which u want to denote as null. Map Empty Constant. Use built in Constant Function, double click on it and delete the value,so that it denotes empty string.In receiver JDBC channel give Interpretation of Empty String as null.

check this out.

Regards,

Sudharshan

[Former Member]

Hi Hector,

Could you explain your scenario more?

Regards,

Luis Diego

[Former Member]

Hi Hector,

In Receiver JDBC Communication channel, there is parameter called Interpretation of Empty String. Try changing that.

Regards,

Sudharshan