Issues in JDBC Receiver Scenario..

[Former Member]

Dear Experts,

      Now i am doing Proxy to JDBC (MYSQL Data Base) scenario.  My Requirement is need to send Students list to Hostel Management System,  First time i have sent students data to the database and its inserted correctly, Now the data which i have sent earlier has some updates 'say change in phone number, etc ' and additional student lists as well,

      when i am trying to send data from ECC its executed successfully, in JDBC receiver channel i am getting following error is given bellow.

JDBC message processing failed; reason Error processing request in sax parser: Error when executing statement for table/stored proc. 'student_sap' (structure 'Statement'): com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Upholding foreign key constraints for table 'student_sap', entry '10001265', key 1 would lead to a duplicate entry.

MP: exception caught with cause com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'student_sap' (structure 'Statement'): com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Upholding foreign key constraints for table 'student_sap', entry '10001265', key 1 would lead to a duplicate entry.

Here '10001265' is student roll number and its primary Key.. i am using UPDATE_INSERT sql statement.

Kindly suggest the way forward.

Thanks in advance..

Regards,

Senthil kumar .j

[iaki_vila]

Hi Senthil,

This is a known issue explained the solution in the question 19 in this note 831162 - FAQ: XI 3.0 / PI 7.0 / PI 7.1 / PI 7.3 JDBC Adapter

Regards

[Former Member]

Hi Senthil,

Please check the below content of s note: 831162 as mentioned by Inaki

19. Using UPDATE_INSERT with MS SQL Server:java.sql.SQLException Cannot insert duplicate key

Q: When I construct payload using UPDATE_INSERT, I get Cannot insert duplicate key in object XXX. How do I resolve this?

A: You need to create UNIQUE INDEX for the table with 'IGNORE_DUP_KEY' option.

For instance, if the table name is TEST and its primary key is id, then following statement may be executed using Query analyzer tool.CREATE UNIQUE INDEX TEST_INDEX ON TEST(id) WITH IGNORE_DUP_KEY After executing the above command, please re-run the scenario.

Regards

Raj

[Former Member]

Dear Raj,

     Thanks for the suggestion, in my case the table name is student_sap and primary key is roll.

CREATE UNIQUE INDEX TEST_INDEX ON student_sap(roll) WITH IGNORE_DUP_KEY

i am trying to execute the above query in mysql server it throwing an error..

sql error (1064) : you have an error in yours sql syntax;

check the manual that corresponds to yours MYSQL server version for the right syntax to use near 'IGNORE_DUP_KEY' at line 1

Kindly suggest me.

Regards,

Senthil.

[iaki_vila]

Hi Senthil,

I think in your version you must put: CREATE UNIQUE INDEX TEST_INDEX ON student_sap(roll) WITH IGNORE_DUP_KEY = ON

Check the sintaxis here: http://technet.microsoft.com/es-es/library/ms186869.aspx

Regards.

[Former Member]

Hi inaki vila,

I have tried the above query also but i am facing the same error.

Kindly suggest me.

Regards,

senthil.

[iaki_vila]

Hi,

And with this?:

ALTER TABLE student_sap ADD CONSTRAINT TEST_INDEX UNIQUE (roll) WITH (IGNORE_DUP_KEY = ON)

Regards

[Former Member]

Hi,

i tried with this also ---- ALTER TABLE student_sap ADD CONSTRAINT TEST_INDEX UNIQUE (roll) WITH (IGNORE_DUP_KEY = ON)  but showing same error.

Regards.

[iaki_vila]

Hi,

Is it always a syntax error?, why is your MSSql version?

Regards.

[former_member184720]

Hi Senthil - That is already defined as primary key so i dont think you can add unique index to it.

Possible options i see here -

1)

Chage your receiver structure to type 6 as explained in the below document so that you can execute the your own DML statements directly on the database.

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

and this way now you have two options to handle your primary key i.e. INSERT IGNORE or REPLACE

INSERT IGNORE and REPLACE should be chosen according to the duplicate-handling behavior you want to effect.

INSERT IGNORE keeps the first of a set of duplicated records and discards the rest. REPLACE keeps the last of a set of duplicates and erase out any earlier ones.

----------------------------------

2) Add a UNIQUE index rather than a PRIMARY KEY to a table that means you have to drop the primary key and add unique key.

Please refer to the below document to understand the behaviour os mySQL in handling the duplicates.

http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm