JDBC receiver: How to send empty/null dates?

[Former Member]

Hi all,

I can't find a way to send an empty date using the JDBC receiver adapter; I always get a com.sap.aii.af.ra.ms.api.DeliveryException.

I've tried to put in no value (""), the value "null", and removing that node at all (occurrence is 0..1).

But nothing worked. When I put in a valid date, it works again. I don't know whether it matters, but this is a Stored Procedure call. The "valid date" is formatted according to the format I specified in the "Date/Time Format For Stored-Procedure Calls" block of the configuration.

For me, it seems like the "Interpretation of Empty String Values" option in the Configuration is indeed only suitable for Strings, not Dates.

Thanks in advance for your help!

Best regards,

Dennis

[Former Member · Accepted solution]

Hi,

Have you checked at receiver side if the date field is not a mandatory field. Also try within the receiver side by manually or with some select query to inseret the empty value in date field.

I guess for JDBC communication channel it doesn't matter if it is a date field or string.

Better to check it at receiver database as I said above.

Regards,

Sarvesh

[Former Member · Accepted solution]

Hi Sarvesh,

Thanks for your reply.

Sorry that it took so long to get access to the DB trace and the rights to execute SQL statements directly against the DB.

Using the SQL script, I was able to create an empty date by using the value "null". Empty strings resulted in Janury 1st, 1990 (probably specific for MS SQL Server).

Now, using XI, I get "unparseable date" errors in all cases except two:

1. The date is correctly formatted as specified in the configuration (yyyy-MM-dd) - and in a valid range ("1000-01-01" gives me a "not a valid instance of data type datetime, for example). We use this case when the input date is filled (obviously) as well as a workaround (using a special date to indicate that the input date is empty).

2. The node gets suppressed in the output structure (using e.g. IfWithoutElse). In this case, the message is sent to the target system. But as that system expects the additional parameter, it cannot process the message (as mentioned: I'm calling a Stored Procedure - hence, I have to provide all defined fields).

Dennis

[Former Member · Accepted solution]

Hi,

In msg mapping for your input date field do the below:

Input Date -> Exists -> If then else -> Target Date

in if_then_else, send Input Date -> DateTransform in then and send some constant Date in Target date format in else.

Then activate and re-run your scenario.

Regards,

Rajeev Gupta

Edited by: RAJEEV GUPTA on Feb 25, 2009 1:58 PM

[Former Member · Accepted solution]

Hi Rajeev,

Actually, that's what I'm currently doing as a workaround. The constant value then gets replaced by an empty value within the Stored Procedure.

Although we took a very uncommon date, there's still a certain chance that this date will the original input value at some point in time. In this case, the user will wonder why the date does not arrive at the target system. That's why I try to find a way to send over empty dates to the Stored Procedure directly.

Best regards,

Dennis