Stored Procedure Structure(Receiver JDBC).

[Former Member]

Hi Friends,

Yesterday issue was not solved. As per your suggestion i change the my Message type structure also still iam getting the error

This is my yesterday thread:

This is my scenario

JDBC(SQL Server)<--

---

>XI<--

---

>JDBC(ORACLE)

Now I implement as a simple example, In real time scenario also i follow same way.

XI fetch the date field from Source JDBC and goes to target database and selects the records based upon condition and response back to Source JDBC.

My process upto successful response mapping after that it call to Stored procedure to insert the records.

The purpose of stored procedure is We have two tables in SQL Server,one table is "Data table" and another one is "Log table".If the records are inserts successuly into Data table, Log table is update with Date and Status filed, If not inserted successfully Log table is upate Date and Error Status.

So that purpose i implemented stored procedure here.

Edited by: sateesh kumar .N on Mar 5, 2010 9:06 AM

[Former Member]

My problem was solved,

The correct XML format of Stored procedure structure is

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

- <ns0:MT_DB_STOREDPROCEDURE xmlns:ns0="urn:pg-siemens-com:POCA0013:sample1">

- <StatementName>

- <XI_KDATA_KLOGDATA action="EXECUTE">

<EMPNO isInput="TRUE" type="NUMERIC">901064</EMPNO>

<EMPNAME isInput="TRUE" type="CHAR">Sateesh</EMPNAME>

<ADDRESS isInput="TRUE" type="CHAR">Hyderabad</ADDRESS>

</XI_KANLOGDATA>

</StatementName>

- <StatementName>

- <XI_KANLOGDATA action="EXECUTE">

<EMPNO isInput="TRUE" type="NUMERIC">901000</EMPNO>

<EMPNAME isInput="TRUE" type="CHAR">Ashish</EMPNAME>

<ADDRESS isInput="TRUE" type="CHAR">Bangalore</ADDRESS>

</XI_KANLOGDATA>

</StatementName>

</StatementName>

</ns0:MT_DB_STOREDPROCEDURE>

[Former Member]

Hi ,

Continutity my specification.........

This below XML Message is target database(ORACLE) response. This XML message is mapped to Stored procedure structure(source System SQL server)

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

- <ns0:MT_KAN_SELECT_REQUEST_response xmlns:ns0="urn:pg-com:POCA0013:sample1">

- <STATEMENT_response>

- <row>

<EMPNO>901064</EMPNO>

<EMPNAME>Sateesh</EMPNAME>

</row>

- <row>

<EMPNO>901000</EMPNO>

<EMPNAME>Ashish</EMPNAME>

</row>

</STATEMENT_response>

</ns0:MT_KAN_SELECT_REQUEST_response>

after mapped to stored procedure structure,I am getting below XML format like this.

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

- <ns0:MT_DB_STOREDPROCEDURE xmlns:ns0="urn:pg-com:POCA0013:sample1">

- <StatementName>

- <XI_KDATA_KLOGDATA action="EXECUTE">

<table>XI_KDATA_KLOGDATA</table>

<EMPNO type="CHAR">901064</EMPNO>

<EMPNAME type="CHAR">Sateesh</EMPNAME>

</XI_KDATA_KLOGDATA>

- <XI_KDATA_KLOGDATA action="EXECUTE">

<table>XI_KANLOGDATA_KLOGDATA</table>

<EMPNO type="CHAR">901000</EMPNO>

<EMPNAME type="CHAR">Ashish</EMPNAME>

</XI_KDATA_KLOGDATA>

</StatementName>

</ns0:MT_DB_STOREDPROCEDURE>

after that XI call the stored procedure , and shows the below error in communication channel.

Error

*********

Error processing request in sax parser: Error when executing statement for table/stored proc. 'XI_KDATA_KLOGDATA' (structure 'StatementName'): java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'table' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)

*********

if i used above stored procedure structure with out 'table' field.

i am getting other error in communication channel:

******

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'XI_SP_KDATA_KLOGDATA' (structure 'StatementName'): com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function XI_SP_KDATA_KLOGDATA has too many arguments specified.

*******

So kindly me suggest me again your valuble approaches.

It would be very help ful me.

Thank you very much.

For your refernce please check my yesterday thread

Sateesh

Edited by: sateesh kumar .N on Mar 5, 2010 9:11 AM