PxPlus User Forum

Twitter Twitter Twitter

Author Topic: MySQL write via ODBC  (Read 1944 times)

michaelgreer

  • Diamond Member
  • *****
  • Posts: 129
    • View Profile
MySQL write via ODBC
« on: October 17, 2018, 04:10:00 PM »
I have a standard interface to various MySQL databases that have the same table format.  In one instance I am getting duplicate rows when I write the same record rather than overwriting.  The MySQL database is not under my control.  It is my understanding the PxPlus handled the determination of whether to do an update or an insert.  I am opening the table with a unique key specified. The insert/duplication occurs whether I write with a key or not.  Is there some open option that I did not notice that will help with this behavior?

Mike King

  • Diamond Member
  • *****
  • Posts: 3811
  • Mike King
    • View Profile
    • BBSysco Consulting
Re: MySQL write via ODBC
« Reply #1 on: October 17, 2018, 10:49:36 PM »
The system generally determines if the record already exists by detecting an error on an INSERT SQL command.  Make sure your table definition includes a constraint on the primary key that it must be unique.

Mike King
President - BBSysco Consulting
eMail: mike.king@bbsysco.com

Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: MySQL write via ODBC
« Reply #2 on: October 18, 2018, 08:52:56 AM »
If you know that you are updating a record and not inserting a new one you can use the UPDATE directive instead of the WRITE. This would be more efficient as well as PxPlus would not have to try and do an insert fail and then do an update.
Principal Software Engineer for PVX Plus Technologies LTD.

michaelgreer

  • Diamond Member
  • *****
  • Posts: 129
    • View Profile
Re: MySQL write via ODBC
« Reply #3 on: October 18, 2018, 02:20:46 PM »
Curiously, I just tried the update option and it a) created a second row b) added a row after I had deleted the existing record(s).  This must be telling us something about the target table and how it is configured.