Duplicate Key problem (UPS software)
I'm having a problem dealing with a 3rd-party app that is connecting to my database via ODBC (using the windows connector). This software (UPS Worldship) creates shipment rows and sends INSERT commands on one table. This table has a primary key that should be unique. Each row in the table has a void column that is either Y or N. When someone voids a shipment in the UPS software, it sends another INSERT to the database, with the same data as the original row, the only difference being the void column is now Y; This of course doesn't work because Duplicate entry 'BLAHBLAH' for key 1 Is there any way to set a default policy for a table that upon an attempted INSERT, the original row should be trashed? Should I get rid of the primary key--I should add that this is my temporary solution. Ideally the original row should be replaced, updated, whatever. I unfortunately can't modify the UPS software to make it operate differently. thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate Key problem (UPS software)
If there is a way for you to capture INSERT commands and convert them to REPLACE commands, you'll have your solution. - Original Message - From: css [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 20, 2006 6:44 PM Subject: Duplicate Key problem (UPS software) I'm having a problem dealing with a 3rd-party app that is connecting to my database via ODBC (using the windows connector). This software (UPS Worldship) creates shipment rows and sends INSERT commands on one table. This table has a primary key that should be unique. Each row in the table has a void column that is either Y or N. When someone voids a shipment in the UPS software, it sends another INSERT to the database, with the same data as the original row, the only difference being the void column is now Y; This of course doesn't work because Duplicate entry 'BLAHBLAH' for key 1 Is there any way to set a default policy for a table that upon an attempted INSERT, the original row should be trashed? Should I get rid of the primary key--I should add that this is my temporary solution. Ideally the original row should be replaced, updated, whatever. I unfortunately can't modify the UPS software to make it operate differently. thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate Key problem (UPS software)
On Tue, 20 Jun 2006, css wrote: } When someone voids a shipment in the UPS software, it sends another } INSERT to the database, with the same data as the original row, the only } difference being the void column is now Y; } } This of course doesn't work because Duplicate entry 'BLAHBLAH' for key } 1 We use Worldship with MySQL via MyODBC and FileMaker, so I have a good amount of expierence using this software. What I did to avoid this issue was I created a MySQL table that had a different primary key than the UPS tracking number. Eg: I just made a simple auto-inc ID column that counts upward from 1. Using the Worldship's ODBC mapping preference, I made sure that *no* UPS field was mapped to this ID column, so that way when Worldship sends an insert, MySQL will auto-populate the primary key. This avoids the duplicate error messages you are getting, and makes it quite simple to figure out voided transactions from real transactions. If this didn't make sense, feel free to e.mail me and I can give more details off list, and include some screenshots from Worldship showing how I set it up to talk to MySQL. /vjl/ -- Vince J. LaMonica Knowledge is knowing a street is one way. [EMAIL PROTECTED] * Wisdom is still looking in both directions. When there's nothing else to read: http://w3log.vjl.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
