Need help with CSV to JDBC scenario

[Former Member]

Hi experts,

We have a CSV file with data which has to be inserted into a SQL server database.

The sender data type is:

snd_dt complex type

row element 0..unbounded

name element xsd:string 0..1

date element xsd:string 0...1

ratio element xsd:string 0...1

The receiver data type is :

rcv_dt complex type

STATEMENT element

ROW element

action Attribute

TABLE element

access element 0...unbounded

name element xsd:string 0...1

date element xsd:string 0...1

ratio element xsd:string 0...1

Now I did the mapping and also did the steps in ID.

In the database, for the table in the SQL server, the datatype for name is "char(3)", for date is "date" and for ratio is "decimal(9,2)"

When I run the scenario, this gives me an error saying:

MP: Exception caught with cause com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'NAME' (structure 'STATEMENT'): com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.

I am doing a direct "INSERT" action into the table.

How do I resolve this error?

ANy help mwould be greatly appreciated.

[abhijitbolakhe · Accepted solution]

Hi in JDBC adapter...in advance tab

provide the details as

logSQLStatement true.

Now you can check in Message display tool in message monitoring (Adapter engine).

You can see all the values coming from source file for each field.

Now see the log what error you are getting it will show you in detail format

Check what value is coming for NAME .

Regards

Abhijit

Edited by: Abhijit Bolakhe on Sep 12, 2009 11:57 AM

[Former Member · Accepted solution]

I have the logSQLStatement done and in RWB, the SQL statement is

INSERT INTO NAME VALUES()

I see that the VALUES is empty which has to be filled with the field names right.

I feel that it is the data type declaration that is causing this problem

Please shed some light.

Regards.

[Former Member · Accepted solution]

Hi,

Try to enter some empty space in column NAME in DATABASE (Receiver). If it will not allow empty sapce you should should send some default value from your mappig.

Regards,

Ramesh.

[abhijitbolakhe · Accepted solution]

Hi

Check in sxmb_moni ...check the values when data comes to XI ..also check in Request message mapping for values after mapping.

if Name field is having empty value...then database table is not allowing to insert empty values.

For test purpose ..give some constant value for Name and test again.

If the above test is successful,that means datatype declaration is OK.

Abhijit

[Former Member · Accepted solution]

Your receiver JDBC structure should be like this

<StatementName2>

<dbTableName action=u201DINSERTu201D>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<access>

<col1>val11</col1>

</access>

</dbTableName>

</StatementName2>

refer below link

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

[Former Member · Accepted solution]

This is the exact error I am running into.

SQL log statement:

INSERT INTO NAME() VALUES ()

Error:

Unable to execute statement for table or stored procedure. 'NAME' (Structure 'STATEMENT') due to com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.

When I put this SQL statement in SQL server, there are field names inside the parantheses of VALUES. Is this the reason why there is error??

Also, the receiver pay load looks like this:

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

- <ns0:MT_NAME_TABLE xmlns:ns0="http://jdbc2jdbc">

- <STATEMENT>

- <ROW action="INSERT">

<TABLE>NAME</TABLE>

<access />

<access />

<access />

</ROW>

</STATEMENT>

</ns0:MT_NAME_TABLE>

Regards.

[Former Member · Accepted solution]

Hi,

If you are using the table name as NAME then pl. change that and give a try. NAMES is a keyword in sql. Please check this link:

http://msdn.microsoft.com/en-us/library/aa238507(SQL.80).aspx

Regards,

---Satish

[Former Member · Accepted solution]

I will give it a try too..!!!

[Former Member · Accepted solution]

I found where the problem was.

In the sender CC, for Row.fieldNames, I gave the wrong field names which were not the same as mapping.

Now, I can see the data in the table.

I am doing a similar scenario in which there is a date field in the database table. But the source field is a string with a length of 8(yyyymmdd).

When I am pushing the data, it is giving me the following error.

com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

[Former Member · Accepted solution]

Hi,

It complains about the conversion format for the date function. Probably you mentioned date as string in your datatype. So try changing it to date and see.

Else convert the date function in your mapping to sql server date function. You can see some help here:

http://www.sql-server-helper.com/tips/date-formats.aspx

So go to your database and see the format of your date column. Then try to send the date value in the same format as it is. Then it should solve your issue.

Regards,

---Satish

[Former Member · Accepted solution]

Hi Satish,

Thank you for the reply. After doing some research, I found that the CSV file contains date values with "00000000".

And SQL server 2008 doesnot accept this date because the range starts from 0001-01-01 in SQL server. I will continue to look for the solution. In the mean time, please let me know if you guys find any.

Regards.

[Former Member · Accepted solution]

Hi,

Now nobody can help you because its your business decision. Check with your functional guys or legacy system users how to deal with those kind of issues. if you ask if you have those format then send some default date or currnet date or whatever it is then in mapping condition check with an if else condition for date.

Regards,

---Satish

[Former Member · Accepted solution]

Hi Satish,

Thank you very much for all the help. I think that is the only way to do it. Send in a default value into that field in the mapping itself.

Regards.

[Former Member]

Hi,

Make sure the database column names and datatypes names are exactly equal. I see NAME is creating problem. Please check the database for this column. Probably they would have defined NAME and your datatype is having name.

Regards,

---Satish

[abhijitbolakhe]

Hi

Are you passing Null value for NAME into database.

If yes. then change setting in database to allow null .

otherwise..use IF function.....if name existes then pass Name..else pass space.

Regards

Abhijit

[Former Member]

Hey Abhijit,

In the database all the fields allow NULL values.

Any other ideas?

Regards.