CSV to JDBC Mapping

[Former Member]

Hi Experts,

I have a CSV to JDBC scenario.

My sender data type is defined as

dt_sender

row element 1...unbounded

field1 element xsd:string 1...unbounded

field2 element xsd:string 1...unbounded

....

field15 element xsd:string 1...unbounded

My receiver data type is defined as

dt_receiver

statement element 1...1

test element 1...1

action attribute string optional

table element string 1...1

access element 1...unbounded

field1 element xsd:string 1...unbounded

field2 element xsd:string 1...unbounded

....

field15 element xsd:string 1...unbounded

I need to insert the data from the CSV file into the database.

I have done simple graphical mapping. The xml structure for the sender MT is

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

<ns0:MT_sender xmlns:ns0="http://csv2jdbc">

<row>

<field1/>

...

<field15/>

</Row>

</ns0:MT_sender>

When I test the mapping...it executes successfully. In the sender CC, I did filecontentconversion with the parameters

Recordset structure : row,*

row.fieldSeparator : \t

row.endSeparator : 'nl'

row.fieldNames : field1,...,field15

The xml file is being created successfully in the sender CC but is not being dropped into the receiver JDBC.

THe JDBC receiver channel is also connected properly. I know that there is some problem with mapping.

But I am unable to find what the error is. No data is being inserted into the table.

The actual payload from the file is

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

- <ns:MT_sender xmlns:ns="http://csv2jdbc">

- <Recordset>

- <row>

<field1>xxxxxx</field1>

...

<field15>xxxxxx</field15>

</row>

- <row>

<field1>xxxxxx</field1>

...

<field15>xxxxxx</field15>

</row>

- <row>

<field1>xxxxxx</field1>

...

<field15>xxxxxx</field15>

</row>

</Recordset>

</ns:MT_sender>

PS: I did this scenario with the blog available for Flat file to JDBC.

Please help me out finding the error.

Regards.

[Former Member · Accepted solution]

HI DNSPK,

You can go two things:-

1. Take your source payload and test it in the message mapping. I believe that you might get an error there.

2. I feel your target structure is not well-defined. Make sure the values of all fields are coming in the incoming payload. Make sure you need the required cardinality. What you need is "0.. n", change your Datatype and/or mapping and it will work.

Try these and let us know if you still have problem.

Regards,

Neetesh

[Former Member · Accepted solution]

Hi,

Thanks for the reply.

I pasted the source payload and it gave me this error:

Fatal Error: com.sap.engine.lib.xml.parser.ParserException: XML Declaration not allowed here.(:main:, row:1, col:8)

Also if I want to change the cardinality, which one were you suggesting to change?

Regards.

[Former Member · Accepted solution]

Hi.

That means XML File is not well formed when u r copy pasting in IR...

Please check the file save the payload in a file with extension xml and Opened it. Delete the ("-") In your tab "Test" mapping import the file and test it againg.

[Former Member · Accepted solution]

Hi Luis,

When I did exactly as you said, it gave me a different error.

Cannot produce target element /ns0:MT_receiver/STATEMENT/test/access. Check xml instance is valid for source xsd and target-field mapping fulfills requirements of target xsd

Please help me out.

Regards.

[RameshGulipall · Accepted solution]

Hi All,

I have same issue,Can provide any inputs to reslove the issue.

The message successfully in SXMB_MONI.

But it is error on the RWB and my target message format below.

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

- <ns1:EmployeTable xmlns:ns1="urn:sample.com:I_Legacy_JDBC_Application">

- <STATEMENT>

- <ROW action="INSERT">

<TABLE>EMP_PI</TABLE>

- <Access>

<EMPLOYENAME>1234</EMPLOYENAME>

<EMPLOYENUMBER>Ramesh</EMPLOYENUMBER>

<SALERY>30000</SALERY>

</Access>

</ROW>

</STATEMENT>

</ns1:EmployeTable>

Regards,

Ramesh

[Former Member · Accepted solution]

What is the error you are getting in RWB

also you can add the logSQLstatement in receiver jdbc channel for check the SQL statement formation trying to issue to the database

you need to set this option in advance mode of receiver jdbc adapter

HTH

Rajesh

[RameshGulipall · Accepted solution]

Hi Rajesh,

I got error in RWB below

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. 'EMP_PI' (structure 'STATEMENT'): java.sql.SQLException: ORA-00984: column not allowed here

8/25/09 12:09:01 PM 001517a9-63ac-1dee-a4a8-3f0beb53647a Error processing request in sax parser: Error when executing statement for table/stored proc. 'EMP_PI' (structure 'STATEMENT'): java.sql.SQLException: ORA-00984: column not allowed here

8/25/09 12:09:01 PM 001517a9-63ac-1dee-a4a8-3f0beb53647a Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'EMP_PI' (structure 'STATEMENT'): java.sql.SQLException: ORA-00984: column not allowed here

Regards,

Ramesh

[Former Member · Accepted solution]

Hi

You should delete the character "-" this the problem.

[Former Member · Accepted solution]

Hi,

Any help for me please...

Ramesh...you hijacked my thread ..

Jus kidding..

Regards.

[Former Member · Accepted solution]

Hi Ramesh,

The error that you are facing is because of a column name that is being used in an expression which is not permitted there. So, this is a syntax error, fix that and your problem will be resolved.

Let us know if you have more questions.

Regards,

Neetesh

[Former Member · Accepted solution]

Hi DNSPK,

Just curious to know, have you mapped your root nodes? If yes, delete that mapping and try doing a mapping test again.

Regards,

Neetesh

[Former Member · Accepted solution]

Hi Neetesh,

I did not map the root nodes.

It is still giving the error

Cannot produce target element /ns0:MT_receiver/STATEMENT/ROW/access. Check xml instance is valid for source xsd and target-field mapping fulfills requirements of target xsd

when I paste the original payload.

Any help will be greatly appreciated.

Regards.

Edited by: DNSPK on Aug 25, 2009 5:49 PM

[Former Member · Accepted solution]

Hi,

This can be the problem with your mapping or with the payload.

Are you sure you are using the correct payload? Have you tried running a test with manual entry into the Test editor?

Regards,

Neetesh

[Former Member · Accepted solution]

Hi Neetesh,

I got it solved.

The problem was that in the data type I created the element "Row" has "R" as capital and the payload has "row" where "r" is in small letter. That's where the problem was. After carefully looking at it, I tried with the small "r" and it worked like a charm.

Thank you very much for all the help.

Regards.