problem in File to JDBC scenario

[Former Member]

Hi,

I am new to XI. I am trying a file to JDBC scenario. It works only for single occurence when i give multiple occurences the output at the receiver table does not occur.

I am getting all confused.

my Datatypes are:

DT_SENDER

EMPID STRING 1:UNBOUNDED

EMPNAME STRING 1:UNBOUNDED

EMPAGE INTEGER 1:UNBOUNDED

FLAG STRING 1:UNBOUNDED

DT_RECEIVER

STATEMENTNAME

-> TEST

ACTION ATTRIBUTE OPTIONAL

TABLE ELEMENT 1:UNBOUNDED

->ACCESS

EMPID STRING 1:UNBOUNDED

EMPNAME STRING 1:UNBOUNDED

EMPAGE INTEGER 1:UNBOUNDED

FLAG STRING 1:UNBOUNDED

Thank you in Advance

[dharamveer_gaur2]

Hi

Check your generated SQL query format is correct

At runtime you can find the genereated sql statements by doing configuraitons in Receiver JDBC adapter.

In the JDBC Receiver adapter you have the Advanced Properties .

Over there enter the following

left column logSQLStatement

right column true

To see the query created ..

Login to adapter monitoring ..select the relevant jdbc adapter.

Now when any message is processed by the jdbc adapter in adapter monitoring at that time you will see a message link. When you click on that link a new window will open. In that window if you click on page down you will get to see the sql statement generated by the jdbc adapter.

[dharamveer_gaur2]

HI

Is this your input Structure? It's structure is Receiver JDBC. I think this is your target structure

[Former Member]

Hi Shwetha,

Have you tried keeping occurance for Statement as 0 to unbounded and mapping it with your MT_Sender node from sender.

Try this and let me know.

Regards,

Rookie.

[Former Member]

hi,

yes I did try with the occurence you mentioned.

But still I am not getting the output file.

Is this the structure i need to give as the sender xml file???

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

<ns0:MT_FILESENDER xmlns:ns0="http://www.f2jdbc.com">

<StatementName2>

<dbTableName action=u201DINSERTu201D>

<table>EMPLOYEE</table>

<ACCESS>

<EMPID>8989</EMPID>

<EMPNAME>asha</EMPNAME>

<EMPAGE>21</EMPAGE>

<FLAG>N</FLAG>

</ACCESS>

<ACCESS>

<EMPID>6565</EMPID>

<EMPNAME>shar</EMPNAME>

<EMPAGE>24</EMPAGE>

<FLAG>Y</FLAG>

</ACCESS>

</dbTableName>

</ns0:MT_FILESENDER>

The xml file is being picked up. The Database is not displaying the values in it.

[dharamveer_gaur2]

Hi

In your Sender Structure as posted earlier, i think you created duplicate subtree(All element like EMPID and other are reapeating) at Mapping Test time. So now you have map exacty source node which creating your same structure again(The node that you are making duplicate) with Target Access node.

[Former Member]

I am still not getting the output.

[prateek]

Paste here your sender and receiver structure before and after mapping from sxmb_moni.

Regards,

Prateek

[Former Member]

Make the data type as :

my Datatypes are:

DT_SENDER 0:UNBOUNDED
EMPID STRING 0:1
EMPNAME STRING  0:1
EMPAGE INTEGER  0:1
FLAG STRING  0:1

DT_RECEIVER

STATEMENTNAME
-> TEST
ACTION ATTRIBUTE OPTIONAL
TABLE ELEMENT 0:1
->ACCESS 0:UNBOUNDED
EMPID STRING 0:1
EMPNAME STRING  0:1
EMPAGE INTEGER  0:1
FLAG STRING  0:1

<StatementName2>

<dbTableName action=u201DINSERTu201D>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<access>

<col1>val11</col1>

</access>

</dbTableName>

</StatementName2>

[Former Member]

what should be my XML input file.????

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

<ns0:MT_FILESENDER xmlns:ns0="http://www.f2jdbc.com">

<StatementName2>

<dbTableName action=u201DINSERTu201D>

<table>EMPLOYEE</table>

<ACCESS>

<EMPID>8989</EMPID>

<EMPNAME>asha</EMPNAME>

<EMPAGE>21</EMPAGE>

<FLAG>N</FLAG>

</ACCESS>

<ACCESS>

<EMPID>6565</EMPID>

<EMPNAME>shar</EMPNAME>

<EMPAGE>24</EMPAGE>

<FLAG>Y</FLAG>

</ACCESS>

</dbTableName>

</ns0:MT_FILESENDER>

I have given the datatypes as u said and gave the above input XML file..

But still output does not appear.

[Former Member]

what should be my XML input file.????

????

I think your input will be like..

<DT_SENDER>

 <DETAILS>
           <EMPID>1213 </EMPID >
           <EMPNAME> Shewta</EMPNAME >
           <EMPAGE>xwy</EMPAGE>
           <FLAG>X</FLAG>
</DETAILS>
<DETAILS>
          <EMPID>12134</EMPID >
          <EMPNAME> Shewta</EMPNAME >
          <EMPAGE>xwy</EMPAGE>
         <FLAG>X</FLAG>
</DETAILS>

</DT_SENDER>

THE details node should be mapped to ACCESS

Edited by: Sunil John on Dec 8, 2008 6:12 PM

[Former Member]

Hi sunil,

I gave the XML file as you mentioned.

But i am not getting the output.

What can be the error????

[Former Member]

Please check that the mesage mapping is working fine or not

try to execute the mapping using test tool of IR.

[Former Member]

Hi sunil,

If i give multiple values in mapping it works. And at the receiver I get the result.

If i give this as my input:

MT_FILESENDER

DETAILS

EMPID 434

EMPNAME dsdsd

EMPAGE 23

FLAG y

DETAILS

EMPID 2132

EMPNAME rfgd

EMPAGE 22

FLAG y

My output will be.

MT_JDBCREC

STATEMENTNAME

->TEST

ACTION INSERT

TABLE EMPLOYEE

->ACCESS

EMPID434

EMPNAME dsdsd

EMPAGE 23

FLAG y

->ACCESS

EMPID 2132

EMPNAME rfgd

EMPAGE 22

FLAG y

I am not getting any error. But the values are not inserted in the table.

Is there any thing else I need to do in the scenario.

[prateek]

Make your sender structure in this manner:

<DT_FILESENDER>
     <ROOT> -- 1 to unbounded
       <EMPID>8989</EMPID>
       <EMPNAME>asha</EMPNAME>
       <EMPAGE>21</EMPAGE>
       <FLAG>N</FLAG>
    </ROOT>
</DT_FILESENDER>

Now your input should be

<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_FILESENDER xmlns:ns0="http://www.f2jdbc.com">
<ROOT>
<EMPID>8989</EMPID>
<EMPNAME>asha</EMPNAME>
<EMPAGE>21</EMPAGE>
<FLAG>N</FLAG>
</ROOT>
<ROOT>
<EMPID>6565</EMPID>
<EMPNAME>shar</EMPNAME> 
<EMPAGE>24</EMPAGE>
<FLAG>Y</FLAG>
</ROOT>
</ns0:MT_FILESENDER>

Your output datatype should be

<MT_FILERECEIVER>
     <StatementName2> 
         <dbTableName action=u201DINSERTu201D> 
         <table>EMPLOYEE</table>
         <access>
                 <EMPID>
                 <EMPNAME>
                 <EMPAGE>
                 <FLAG>
        </access>
         <access>
                 <EMPID>
                 <EMPNAME>
                 <EMPAGE>
                 <FLAG>
        </access>
  </dbTableName> 
</StatementName2> 
<MT_FILERECEIVER>

Regards,

Prateek

[Former Member]

Are you getting any errors in the Receiver JDBC communication channel?

[Former Member]

this is the error I am getting at receiver communication channel.

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'EMPLOYEE' (structure 'STATEMENTNAME'): java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]String or binary data would be truncated.

[prateek]

This may be due to improper lengths of the fields you are mapping. Recheck it in datatype as per the specifications given to you.

Regards,

Prateek

[Former Member]

Hi,

I got the output.

I guess there was some problem with my table.

I created a new table n checked , It worked.

Thank you.

[dharamveer_gaur2]

Hi

Check your Mapping, Source Parent node should be mapped with Access node.Also check occurance of Access node(Should be 0...unbounded)

[Former Member]

Hi Shwetha,

have a look in to this link.

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

Cheers

Sunil.

Edited by: Sunil John on Dec 8, 2008 4:19 PM

[prateek]

1. Make the occurrence of access node at receiver as 1 to unbounded.

2. Map the sender node DT_SENDER to access

Regards,

Prateek

[Former Member]

At the sender side the file gets picked up. At the JDBC receiver this error occurs

""""Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'EMPLOYEE' (structure 'STATEMENTNAME'): java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column name 'EMPID' appears more than once in the result column list.""""

wen i give the INPUT as:

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

<ns0:MT_FILESENDER xmlns:ns0="http://www.f2jdbc.com">

<EMPID>8989</EMPID>

<EMPNAME>asha</EMPNAME>

<EMPAGE>21</EMPAGE>

<FLAG>N</FLAG>

<EMPID>6565</EMPID>

<EMPNAME>shar</EMPNAME>

<EMPAGE>24</EMPAGE>

<FLAG>Y</FLAG>

</ns0:MT_FILESENDER>

My OUTPUT does not occur because of multiple values.

Can you tell me what is the mistake.

[Former Member]

Can you check the xml format after mapping and check the Structures are well formed as mentioned in the link below

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

[prateek]

Your input is incorrect. Within the same node MT_FILESENDER, you should not have two EMPID or other fields with same name. You should create additional root node if you want to have multiple records at sender.

Regards,

Prateek

[Former Member]

So should I have different root nodes at both sender and receiver datatype???

<access>

<empid>4343</empid>

<empname>ash</empname>

<empage>23</empage>

<flag>y</flag>

</access>

<access>

<empid>2342</empid>

<empname>dany</empname>

<empage>22</empage>

<flag>y</flag>

</access>

Is this what u asked me to do at sender datatype???

[prateek]

different root nodes at both sender and receiver datatype???

At receiver you already have the access node. You require ont at sender.

Yes. The xml structure you provided is correct for receiver. Similar node with any name should be created at sender too.

Regards,

Prateek

[dharamveer_gaur2]

Hi

Map Your source node which repeating message with statement

look this file to JDBC scenario

https://www.sdn.sap.com/irj/scn/wiki?path=/display/xi/file%252bto%252bjdbc