Error in JDBC receiver communication channel

[former_member221434]

Hi Experts ,

I am using SAP PI 7.31 and created   proxy to jdbc scenario .When I am sending data form Ecc Server to JDBC  server but data is not reaching to JDBC server   .

I have checked SXMB_MONI  and whole system  but I did not find any  error  while  I have checked receiver channel then I found some error 

Error is 

Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException

Error processing request in sax parser: Error when executing statement for table/stored proc

Error while parsing or executing XML-SQL document: Error processing request in sax parser

Error Screen shot

[former_member182412 · Accepted solution]

Hi Pradeep,

It looks like your receiver structure is incorrect, please create according to below document.

Defining XML Documents for Message Protocol XML SQL Format - Advanced Adapter Engine - SAP Library

Regards,

Praveen.

[former_member221434 · Accepted solution]

Hi Praveen ,

This is my receiver  payload  if any issue in this payload please tell me .

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_Moodle_UserEnrolDataTransfer_Receiver xmlns:ns0="http://Moodle_UserEnrolDataTransfer">

<statement>

<dbTableName action="UPDATE_INSERT">

<table>userenrol</table>

<access>

<userid></userid>

<courseid></courseid>

<role></role>

<enrolperiod></enrolperiod>

<session></session>

<timecreated></timecreated>

<datecreated></datecreated>

<timemodified></timemodified>

<datemodified></datemodified>

<coursename></coursename>

</access>

</dbTableName>

</statement></ns0:MT_Moodle_UserEnrolDataTransfer_Receiver>

Regards ,

pradeep

[RaghuVamseedhar · Accepted solution]

Pradeep,

There is no key defined in your UPDATE_INSERT structure. There is no data in XML elements.

FYI. To use UPDATE_INSERT

<StatementName>
  <dbTableName action=”UPDATE_INSERT”>
    <table>realDbTableName</table>
    <access>
      <col1>val1</col1>
      <col2>val2new</col2>
    </access>
    <key1>
      <col2>val2old</col2>
      <col4>val4</col4>
    </key1>
    <key2>
      <col2>val2old2</col2>
    </key2>
  </dbTableName>
</StatementName>

To use INSERT (if you do not have key, and just need to insert into table).

<StatementName>
  <dbTableName action=”INSERT”>
    <table>realDbTableName</table>
    <access>
     <col1>val1</col1>
     <col2>val2</col2>
    </access>
   <access>
      <col1>val11</col1>
      </access>
   </dbTableName>
</StatementName>

[former_member221434 · Accepted solution]

hello Raghu ,

I have change action as  "INSERT" but still data is not reaching in database . And when I checked message details in Communication Channel Monitor , then I found that error .

Status Details	JDBC adapter: Processing error (General processing error in the JDBC adapter)
Error Category	XI_J2EE_ADAPTER_JDBC
Error Code	JDBC_ADAPTER_PROCESSING_ERROR

[former_member221434 · Accepted solution]

Hello Experts ,

This error is coming in message log . Please give the solution 

Unable to execute statement for table or stored procedure. 'userenrol_replica' (Structure 'statement') due to java.sql.SQLException: Field 'coursename' doesn't have a default value

JDBC message processing failed; reason Error processing request in sax parser: Error when executing statement for table/stored proc. 'userenrol_replica' (structure 'statement'): java.sql.SQLException: Field 'coursename' doesn't have a default value

Regards ,

Pradeep

[iaki_vila · Accepted solution]

Hi Pradeep,

There are a fields that they have to be fill with a value (not null value allowed), in your case coursename, please set a value to it.

Regards.

[former_member221434 · Accepted solution]

Hi Inaki

   Please find the attachment of my screen shots and kindly tell me where should I change.

• 

[manoj_khavatkopp · Accepted solution]

Hi Pradeep,

You have mentioned Coursename filed as not mandatory (0..1) but in DB side they would have defined it as mandtory , try passing some value to that filed and check whether you are getting the same error or else you need to make that filed as Allow Null on DB side.

Br,

Manoj

[iaki_vila · Accepted solution]

Hi Pradeep,

Your DB screenshoot is self explanatory, the problem is in the payload that you are generating you need to set always a value for Coursename or to follow Manoj suggestion.

Regards.

[former_member221434 · Accepted solution]

Hello All ,

Thank You All , My problem is solve 

Regards ,

Pradeep

[former_member191216]

Hi Pradeep,

You have made COURSENAME field as not mandatory, but it can be mandatory at database side, thats why you are getting this error.

1. Make the occurence 1-unbounded, and pass some value to it.
2. If it doesnt work, then check on ALLOW NULL VALUE on database side.

Thanks,
varsha.