JDBC Receiver does not convert empty string to NULL

[former_member286225]

Hello experts,

I am facing a problem with an INSERT statement to ORACLE DB where some of the fields are empty, for example:

- <STATEMENT_VLP_CONT_DETAILS>

- <TABLE_VLP_CONT_DETAILS ACTION="INSERT">

<TABLE>VLP_CONT_DETAILS</TABLE>

- <ACCESS>

<VLP_HDR_ID>43</VLP_HDR_ID>

<VLP_CONT_LINE_ID>1</VLP_CONT_LINE_ID>

<QUANTITY>1</QUANTITY>

<OWNER>CBHU</OWNER>

<SERIAL_NO>3372739</SERIAL_NO>

<CONT_ISO_CODE>2300</CONT_ISO_CODE>

<WEIGHT>6.44</WEIGHT>

<POL>ILASH</POL>

<POD>FRFOS</POD>

<FD>FRFOS</FD>

<OPER_CODE>COS</OPER_CODE>

<CUR_STOW_LOC>150102</CUR_STOW_LOC>

<PRV_STOW_LOC />

<HAZARDOUS>N</HAZARDOUS>

...

In the JDBC Receiver comm channel I selected: Interpretation of Empty String Values = NULL Value.

It seems the empty strings are not converted to NULL values as can be seen in the reflected SQL statement in RWB:

INSERT INTO VLP_CONT_DETAILS (VLP_HDR_ID, VLP_CONT_LINE_ID, QUANTITY, OWNER, SERIAL_NO, CONT_ISO_CODE, WEIGHT, POL, POD, FD, OPER_CODE, CUR_STOW_LOC, PRV_STOW_LOC, HAZARDOUS, EMPTY, REFER, OOG, HAZARD_IMDG, TEMP_SET, TEMP_MIN, TEMP_MAX, FRONT_OVL, REAR_OVL, RIGHT_OVW, LEFT_OVW, OVERHEIGHT, COMMODITY, BOOKING_REF, ALLOTMENT, YARD_LOC, LD_SEQ_CRANEID, LD_SEQ_NUMBER, LD_STATUS, STACKABLE, OPT_PORT_CODE1, OPT_PORT_CODE2, OPT_PORT_CODE3) VALUES (44, 1, 1, CBHU, 3372739 , 2300, 6.44, ILASH, FRFOS, FRFOS, COS, 150102, , N, N, N, N, , , , , , , , , , , , , , , 0, 64, N, , , )

So, I am getting the following error:

Could not execute statement for table/stored proc. "VLP_CONT_DETAILS" (structure "STATEMENT_VLP_CONT_DETAILS") due to java.sql.SQLException: ORA-00936: missing expression

Help would be appreciated.

[former_member286225]

Hi Dennis,

We have implemented the note without possitive result.

Thanks.

[former_member286225]

Hi Suraj,

The logSQLStatement is already switched on (this is how I sent the SQL statement with my first post).

Also, I already tested the SQL statement using TOAD. thats how I came up with the NULL issue because when I replced the empty places with NULL values, the statement completed successfuly.

Do you happen to have more suggestions?

Regards,

Effi.

[Former Member]

Hi Effi,

Did you implement the SAP Note 1350830, as mentioned by Suraj? It seems to cover exactly the problem you mention.

I also had this problem a while ago (is was a date field in my case, though).

Thanks, Suraj! Now I'm confident it wasn't my fault back then.

Best regards,

Dennis

[former_member286225]

Hi Dennis,

The note you gave (1350830) handles a problem with a stored procedure.

In my case, it is a plain and direct INSERT query.

Nevertheless, I will ask our basis team to look into it.

Regards,

Effi.

[Former Member]

Hi Effi,

>

> The note you gave (1350830) handles a problem with a stored procedure.

> In my case, it is a plain and direct INSERT query.

>

> Nevertheless, I will ask our basis team to look into it.

I see... my bad!

I guess the patch will be useless for you then.

I wonder whether the "Interpretation of Empty String Values" parameter even applies to your situation. After all, it is no text field.

As data fields (e.g. INT) don't have to distinguish between empty values and NULL values (both is the same), I'm surprised it does not work right from the start.

Best regards,

Dennis

[former_member286225]

Hello Suraj,

Thank you for your quick response.

I counted the amount of values (between commas and at the edges) and it is also 37 not 35 as you suggested.

(44, 1, 1, CBHU, 3372739 , 2300, 6.44, ILASH, FRFOS, FRFOS, COS, 150102, , N, N, N, N, , , , , , , , , , , , , , , 0, 64, N, , , )

As for the empty value, if a field is indicated by an empty tag such as: <PRV_STOW_LOC /> doesn't it mean that the channel should consider it as an empty string and as a result put NULL in its place?

Also, you suggested: Give empty values for nuil fields so that they can be made NULL by jdbc adapter

What do you mean by that?

Regards,

Effi.

[Former Member]

Probably in the mapping you can do a map with default and give it as space.

Mat

[former_member187339]

Hi Effi,

I counted using Excel sheet so overlooked some spaces

>>As for the empty value, if a field is indicated by an empty tag such as: <PRV_STOW_LOC /> doesn't it mean that the channel should consider it as an empty string and as a result put NULL in its place?

yes this would be enough. I guess you can try the suggestion given by Mathews

>>Also, you suggested: Give empty values for nuil fields so that they can be made NULL by jdbc adapter

I meant the empty tags

Regards

Suraj

[former_member286225]

Hi Mat,

Do you mean put in the data type a space in the default value?

If yes, I tried it and it does not work.

If no, can you please elaborate?

Regards.

[former_member187339]

Hi Effi,

There is a standard function in message mapping (named mapwithdefault in category Node functions).

The use of this function is to pass the default value (here it should be empty), when the source field doesn't exists or is empty

The default value is passed by taking the properties of the function

Regards

Suraj

[former_member286225]

Hi Suraj,

Thanks for the info.

I tried MapWithDefault leaving it empty. also, with a space value.

No luck. The field value as reflected in RWB does not turn to NULL.

Regards.

[former_member187339]

Hi Effi..

Seems some other problem then.

I will recommend you to see the log fil by switching on as mentioned in this note

Note 801367 - XI 3.0 JDBC Adapter: Additional Parameters

This problem was common when we were using Strored Procedure, which was solved by applying patch (as per this note Note 1350830 - Empty string interpretation as Null).

So switch on the trace and take the SQL query which you will get and execute it directly in the Oracle DB.

Regards

Suraj

[Former Member]

>Hi Suraj,

>

>Thanks for the info.

>

>I tried MapWithDefault leaving it empty. also, with a space value.

>No luck. The field value as reflected in RWB does not turn to NULL.

>

>Regards.

Have u tried by using NULL in ur MapWithDefault

A stupid suggestion but worth a try

[former_member286225]

Hi Rahul,

No suggestion is stupid.

As to your suggestion, if I put a value in the default, it is being interpreted as a string and not an actual NULL.

Regards.

[former_member187339]

Hi Effi,

There is a mismatch in the field and thier value.

Here field id 37

VLP_HDR_ID, VLP_CONT_LINE_ID, QUANTITY, OWNER, SERIAL_NO, CONT_ISO_CODE, WEIGHT, POL, POD, FD, OPER_CODE, CUR_STOW_LOC, PRV_STOW_LOC, HAZARDOUS, EMPTY, REFER, OOG, HAZARD_IMDG, TEMP_SET, TEMP_MIN, TEMP_MAX, FRONT_OVL, REAR_OVL, RIGHT_OVW, LEFT_OVW, OVERHEIGHT, COMMODITY, BOOKING_REF, ALLOTMENT, YARD_LOC, LD_SEQ_CRANEID, LD_SEQ_NUMBER, LD_STATUS, STACKABLE, OPT_PORT_CODE1, OPT_PORT_CODE2, OPT_PORT_CODE3

And value is 35

44, 1, 1, CBHU, 3372739 , 2300, 6.44, ILASH, FRFOS, FRFOS, COS, 150102, , N, N, N, N, , , , , , , , , , , , , , , 0, 64, N, , ,

Give empty values for nuil fields so that they can be made NULL by jdbc adapter

Regards

Suraj