Error passing null values to receiver stored procedure

[Former Member]

Hi Experts,

       currently i am working on proxy to jdbc scenario, data coming from sap to pi and pi to receiver third party JDBC. in receiver side we are using stored procedure. some fields values are not coming from SAP, but all fields are mandatory in target database table. so that we are passing null values to avoid those things. i am using mappwithdefault node function to avoid this and i have enable Receiver Communication Channel, in tab "Processing" and option "Interpretation of empty String values" = NULL value  or Empty value but no luck . we are getting error like data not converting nvarchar to date. i have checked the sequence of fields in PI and stored procedure filed sequence bot should be same, in our mapping we are using 2 date fields where we are getting this we not finding. please help me out of this issue and how to pass the null values to Stored procedure.

Regards,

Sanjay.

[baskar_gopalakrishnan2 · Accepted solution]

> but all fields are mandatory in target database table.

The Stored Procedure is coded to convert the date field  to equivalent value in backend. Since it is mandatory and you dont send from PI , you are getting issues. The problem is at the sender side. You need to talk to business requirement to handle this situation. If your SAP system does not send how do you want to handle in PI. I would say comeup with some default date like current date or so  and pass it to SP. You cannot modify SP logic since that field is mandatory in database. At the same time you cannot pass null values though.

Hope that helps.

[Former Member · Accepted solution]

Hi Baskar,

          the database people said that the table fields are accept null values also that the reason i am trying to send null values. i have passed null values to some fields it is processed, while sending date filed we are facing problem. please help me out.

Regards,

Sanjay.

[baskar_gopalakrishnan2 · Accepted solution]

We can insert  NULL for any datatypes. In this case the conversion function from the value to date fails in the SP. Passing Null value to the string data type is not an issue. Same we cannot do it for date data type field. I think you pass date as string and SP converts into equivalent date. Is that so? If that is the case send some dummy date string in the event of no data from SAP to fix this issue.

[Former Member · Accepted solution]

Hi Baskar,

     thanks for your inputs. i am not passing null value to string. am passing null value to date data type.

     example: i have mapped

             joining date(Date)---->mappwithdefault----->joining date(date).

is it possible to send null value to date function.please help me.

regards,

sanjay.

[baskar_gopalakrishnan2 · Accepted solution]

What do you send as default value in MapwithDefault function? Send some default date instead empty value. This way you can fake the convert function that convert the value to date to avoid this conversion error as you mentioned "data not converting nvarchar to date".

[Former Member · Accepted solution]

thank you very much for your inputs, can i give default date value "0000-00-00" like this or any date i ll pass.

Regards,

Sanjay.

[baskar_gopalakrishnan2 · Accepted solution]

Yes. Just for clarity go with some standard date format (may be very old date or future date) and document it. If the sender system does not send values, you go with this type of default date.

Example: "9999-09-09"

[Former Member · Accepted solution]

Hi Baskar,

          1. i have passed default date like "9999-12-31". but i am getting error like data not converting nvarchar to date. i asked to Abaper to pass default date at that time also we are facing same conversion error.

        2. if message is successfully processed, in RWB receiver JDBC communication channel status is RED. what might be the problem. i have checked in audit log it showing message delivered.

please help me out the both issues.

Regards,

Sanjay.

[baskar_gopalakrishnan2 · Accepted solution]

>. i have passed default date like "9999-12-31". but i am getting error like data not converting nvarchar to date. i asked to Abaper to pass default date at that time also we are facing same conversion error.

I believe you sent the value as string data type and using Stored Procedure which has convert date function that converts your datestring to date.  Is that right?  Check in StoredProcedure what format do you use?

Example YYYY-MM-DD or YYYY/MM/DD or any format

Based on the dateformat used in SP, you accordingly send the date string.

>    2. if message is successfully processed, in RWB receiver JDBC communication channel status is RED. what might be the problem. i have checked in audit log it showing message delivered.

Do you see any error in the communication channel log?  Also you can set logSQLStatement = true in the reciever jdbc channel in the advanced tab.  This will help for the logging.  If you see continuously red, stop and restart the channel and see the behavior.

[baskar_gopalakrishnan2]

Since you handle convert function in stored procedure and just passing string value to it, you might want to check this link

http://stackoverflow.com/questions/3878072/sql-stored-procedure-convert-date-parameter

But if you embed any convert function in the jdbc sql statement itself then check above SAP Note.

[iaki_vila]

Hi Sanjai

Have you checked Note 831162 - FAQ: XI 3.0 / PI 7.0 / PI 7.1 JDBC Adapter, the third question 3. Date Field Handling. May be it helps you.

Regards.