jdbc receiver adapter using stored procedure

[former_member393841]

Hi All,

My requirement :

Sender : Proxy

Receiver : JDBC  with STORED PROCEDURE.

Can any one explain me the steps in ESR.

Thanks in advance,

harsha

[former_member184681 · Accepted solution]

Hi Harsha,

There is nothing really surprising here. Define the sender side as normally for Proxy (data type, message type, service interface), receiver side as in this thread for instance and the mappings, this is it. Please search the SDN on generic questions and get back if you had some more detailed questions.

Regards,

Greg

[former_member393841 · Accepted solution]

Hi Greg,

Thank you very much for your quick response.

I have created the  data types ,message types and corresponding service interfaces also.

Receiver Data type

But in message mapping i am little confused

In the above mapping which values  i need to pass for

isInput and type .

Please help me to achieve this scenario.

Thanks in advance,

harsha

[former_member184681 · Accepted solution]

isInput should be set to "true" and type should be equal to the database data type of the corresponding field, like VARCHAR or so. You have to check that with the party responsible for the receiver DB. Also, you can use constants in both cases in your mapping.

Regards,

Greg

[ayan_chaudhuri · Accepted solution]

Hi Harsha,

Please follow the following steps  assuming you have stored procedure in the receiver side.

1)In message mapping , source data type will be as usual and target data type will be as follows

    i)The top node will be  Statement..Below this node create the sub-element with name of your stored procedure..e.g Say ur stored procedure name STP , so create the sub element name as STP..Next, below this sub-element create 'action' and specify a constant value 'EXECUTE' to 'action'.....Then create one more element ( it can be more than one depending on the input parameter to stored procedure) and keep the name of this element as the input parameter of your stored procedure........Below this particular element, assign two more attributes , one is  'isInput' and the other is 'type'...Assign a constant value ('TRUE') to 'isInput' and a constant value ('VARCHAR') to 'type'...Next create the output parameter of your stored procedure as one element e. g  the name of this element is 'RETVAL' and create two more attributes under 'RETVAL' ....The two attriutes are 'isOutput' and type.....Assign a constant value ('TRUE') to 'isOutput' and a constant value ('CURSOR') to 'type'....Now complete your mapping for source to target data......

  ii) Next complete your Integration builder configuration with JDBC adapter in receiver side.....There is nothing critical in it..

Thanks

Ayan Chaudhuri

[rajasekhar_reddy14 · Accepted solution]

Hi,

  i can see your data type for stored procedure is missing table field.,

map constant value EXECUTE----action tag

Stored procedure name to---TABLE.

[former_member393841 · Accepted solution]

Thank you very much for your reply,

I have developed the scenario.

But while executing i am getting the following error in receiver communication channel.

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. 'SAP.usp_InsertEmployeeChanges' (structure 'Statement'): java.lang.NumberFormatException

Could you please explain me the error reason.

I have checked all the fields data but i didn't find any thing.

Thanks in advance.

harsha

[rajasekhar_reddy14 · Accepted solution]

Hi Harsha,

Copy past your mapping output here.

Make sure that your stored procedure active and SP name correct.

Regards,

Raj

[former_member393841 · Accepted solution]

Hi raj,

This is my message in sxmb_moni.

When checked this in message mapping its working fine.But i am unable to insert record into the table.

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

<nm:MOAEmployeePersonnelData xmlns:prx="urn:sap.com:proxy:DE1:/1SAI/TAS2D2D5DD53FDE25480899:702" xmlns:nm="urn:Muni.org:Time:PersonnelDataToTimeKeepingSystems">

   <EmployeePersonnelData>

      <EMPLID>00070001</EMPLID>

      <EFFDT>2012-01-02</EFFDT>

      <DEPTID>1814100</DEPTID>

      <FTE> 71.42</FTE>

      <STD_HOURS>40.00</STD_HOURS>

      <BIRTHDATE>1976-01-01</BIRTHDATE>

      <FIRST_NAME>Anchorage</FIRST_NAME>

      <MIDDLE_NAME>jhon</MIDDLE_NAME>

      <LAST_NAME>Firefighter</LAST_NAME>

      <REG_TEMP>1</REG_TEMP>

      <FULL_PART_TIME>1T</FULL_PART_TIME>

      <HIRE_DT>2012-01-10</HIRE_DT>

      <REHIRE_DT>2012-01-10</REHIRE_DT>

      <MLP_HIRE_DATE>2012-01-02</MLP_HIRE_DATE>

      <SERVICE_DT>2012-01-10</SERVICE_DT>

      <EMAIL_ADDRES>test@gmail.com</EMAIL_ADDRES>

      <STREET_HOUSENUMBER>1000 G ST  TEST</STREET_HOUSENUMBER>

      <CITY>Anchorage</CITY>

      <DISTRICT>Anchorage</DISTRICT>

      <POSTAL_CODE>99501</POSTAL_CODE>

      <COUNTRY_CODE>USA</COUNTRY_CODE>

      <USER_ID>user</USER_ID>

      <NETWORK_ID>IDSXK</NETWORK_ID>

      <SUPV>manager</SUPV>

      <WAGE_EFFDT>2012-05-15</WAGE_EFFDT>

      <ORIG_HIRE_DT>2012-02-10</ORIG_HIRE_DT>

      <CMPNY_SENIORITY_DT>2012-02-10</CMPNY_SENIORITY_DT>

      <PAY_RATE>234</PAY_RATE>

      <PAY_RATE_HOURLY>465</PAY_RATE_HOURLY>

      <EMP_STATUS>3</EMP_STATUS>

      <IS_MANAGER>1</IS_MANAGER>

      <EMERGENCY_PHNNUM/>

      <WORK_PHONE>1234567891</WORK_PHONE>

      <CELL_PHONE>1234567891</CELL_PHONE>

      <HOME_PHONE>1234567891</HOME_PHONE>

      <POSITION_NUMBER>99999999</POSITION_NUMBER>

      <JOB_CODE>1234567891</JOB_CODE>

      <JOB_CODE_DESCRIPTION>Accounting Clerk</JOB_CODE_DESCRIPTION>

      <WORK_SCH_RULE>KELLY-A</WORK_SCH_RULE>

      <WORK_SCH_RULE_DESC>5735  00</WORK_SCH_RULE_DESC>

      <COST_CENTER>6589656523</COST_CENTER>

      <PRI_LBR_ACC_SDATE>2012-02-10</PRI_LBR_ACC_SDATE>

      <DIY_WORK_HRS>M123456789</DIY_WORK_HRS>

      <INTERNAL_ORDER>1234567892</INTERNAL_ORDER>

      <WBS_ELEMENT>M1234567</WBS_ELEMENT>

      <FUND>987654321</FUND>

      <FUNCTIONAL_AREA>365214552</FUNCTIONAL_AREA>

      <GRANT>Not Applicable</GRANT>

      <BUSINESS_AREA>3400</BUSINESS_AREA>

      <PA_TEXT>FIRE</PA_TEXT>

      <PSA_TEXT>Executives EX</PSA_TEXT>

      <EG_TEXT>Regular Full-Time</EG_TEXT>

      <ESG_TEXT>w/LV No-IN &amp; Retire</ESG_TEXT>

      <POS_TEXT>Default position</POS_TEXT>

      <ORG_UNIT>00000000</ORG_UNIT>

      <ORG_UNIT_TEXT>Employment</ORG_UNIT_TEXT>

      <COST_CENTER_TEXT>Employment</COST_CENTER_TEXT>

   </EmployeePersonnelData>

</nm:MOAEmployeePersonnelData>

Please tell me how to check my stored procedure is active or not.

Thanks & regards,

harsha

[Former Member · Accepted solution]

Hi,

It seems that the target SP is expecting some integer value in one (or some) of the filed (fields) and u r passing string for the same.

Cross check the data type of PI with SP fileds.

Thanks

Amit Srivastava

[rajasekhar_reddy14 · Accepted solution]

Hi Harsha,

When you are using stored procedure, you have to send XML data correct format and your stored procedure should be active.

Please paste Target message(from MONI).

Regards,

Raj

[rajasekhar_reddy14 · Accepted solution]

Can you check <STREET_HOUSENUMBER>1000 G ST  TEST</STREET_HOUSENUMBER> field type in Data base, sring or integer?

it looks SP expecting integer value but you are sending string.

[Former Member · Accepted solution]

Hi,

In addition to above, do remember that the sequence of  fields in SP should be same as that of PI.

Thanks

Amit Srivastava

[former_member393841 · Accepted solution]

Hi  Raj,

this is my target message in sxmb_moni

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

- <nm:MOAEmployeePersonnelData xmlns:nm="urn:Muni.org:Time:PersonnelDataToTimeKeepingSystems" xmlns:prx="urn:sap.com:proxy:DE1:/1SAI/TAS2D2D5DD53FDE25480899:702">

- <EmployeePersonnelData>

  <EMPLID>00070001</EMPLID>

  <EFFDT>2012-01-02</EFFDT>

  <DEPTID>1814100</DEPTID>

  <FTE>71.42</FTE>

  <STD_HOURS>40.00</STD_HOURS>

  <BIRTHDATE>1976-01-01</BIRTHDATE>

  <FIRST_NAME>Anchorage</FIRST_NAME>

  <MIDDLE_NAME>jhon</MIDDLE_NAME>

  <LAST_NAME>Firefighter</LAST_NAME>

  <REG_TEMP>1</REG_TEMP>

  <FULL_PART_TIME>1T</FULL_PART_TIME>

  <HIRE_DT>2012-01-10</HIRE_DT>

  <REHIRE_DT>2012-01-10</REHIRE_DT>

  <MLP_HIRE_DATE>2012-01-02</MLP_HIRE_DATE>

  <SERVICE_DT>2012-01-10</SERVICE_DT>

  <EMAIL_ADDRES>test@gmail.com</EMAIL_ADDRES>

  <STREET_HOUSENUMBER>1000 G ST TEST</STREET_HOUSENUMBER>

  <CITY>Anchorage</CITY>

  <DISTRICT>Anchorage</DISTRICT>

  <POSTAL_CODE>99501</POSTAL_CODE>

  <COUNTRY_CODE>USA</COUNTRY_CODE>

  <USER_ID>user</USER_ID>

  <NETWORK_ID>IDSXK</NETWORK_ID>

  <SUPV>12345</SUPV>

  <WAGE_EFFDT>2012-05-15</WAGE_EFFDT>

  <ORIG_HIRE_DT>2012-02-10</ORIG_HIRE_DT>

  <CMPNY_SENIORITY_DT>2012-02-10</CMPNY_SENIORITY_DT>

  <PAY_RATE>12.00</PAY_RATE>

  <PAY_RATE_HOURLY>12.00</PAY_RATE_HOURLY>

  <EMP_STATUS>3</EMP_STATUS>

  <IS_MANAGER>1</IS_MANAGER>

  <EMERGENCY_PHNNUM />

  <WORK_PHONE>1234567891</WORK_PHONE>

  <CELL_PHONE>1234567891</CELL_PHONE>

  <HOME_PHONE>1234567891</HOME_PHONE>

  <POSITION_NUMBER>99999999</POSITION_NUMBER>

  <JOB_CODE>1234567891</JOB_CODE>

  <JOB_CODE_DESCRIPTION>Accounting Clerk</JOB_CODE_DESCRIPTION>

  <WORK_SCH_RULE>KELLY-A</WORK_SCH_RULE>

  <WORK_SCH_RULE_DESC>work shedule rulew</WORK_SCH_RULE_DESC>

  <COST_CENTER>6589656523</COST_CENTER>

  <PRI_LBR_ACC_SDATE>2012-02-10</PRI_LBR_ACC_SDATE>

  <DIY_WORK_HRS>123.00</DIY_WORK_HRS>

  <INTERNAL_ORDER>1234567892</INTERNAL_ORDER>

  <WBS_ELEMENT>M1234567</WBS_ELEMENT>

  <FUND>987654321</FUND>

  <FUNCTIONAL_AREA>365214552</FUNCTIONAL_AREA>

  <GRANT>Not Applicable</GRANT>

  <BUSINESS_AREA>3400</BUSINESS_AREA>

  <PA_TEXT>FIRE</PA_TEXT>

  <PSA_TEXT>Executives EX</PSA_TEXT>

  <EG_TEXT>Regular Full-Time</EG_TEXT>

  <ESG_TEXT>w/LV No-IN & Retire</ESG_TEXT>

  <POS_TEXT>Default position</POS_TEXT>

  <ORG_UNIT>00000000</ORG_UNIT>

  <ORG_UNIT_TEXT>Employment</ORG_UNIT_TEXT>

  <COST_CENTER_TEXT>Employment</COST_CENTER_TEXT>

  </EmployeePersonnelData>

  </nm:MOAEmployeePersonnelData>

I have checked each and every data type i didn't find any mismatch. this is my stored procedure structure.

thanks & regards,

harsha

[rajasekhar_reddy14 · Accepted solution]

I dont see your stored procedure name in your target structure.

You structure is huge , so compare every field and find the difference.

[rajasekhar_reddy14 · Accepted solution]

Share your Tragte XML message with DB team and ask them ro process them same they can easily find the error.

[Former Member]

Hi,

Plz chk this:

http://scn.sap.com/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/e0ac1a33-debf-2c10-45bf-fb19f6e15...

Thanks

Amit Srivastava