Toggle navigation
PxPlus User Forum
PxPlus User Forum
»
Main Board
»
Discussions
»
ODBC
»
MySQL write via ODBC
Likes
« previous
next »
Print
Pages: [
1
]
Author
Topic: MySQL write via ODBC (Read 2415 times)
michaelgreer
Diamond Member
Posts: 129
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?
Logged
Mike King
Diamond Member
Posts: 3824
Mike King
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.
Logged
Mike King
President - BBSysco Consulting
eMail: mike.king@bbsysco.com
Devon Austen
Administrator
Diamond Member
Posts: 386
Don’t Panic
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.
Logged
Principal Software Engineer for PVX Plus Technologies LTD.
michaelgreer
Diamond Member
Posts: 129
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.
Logged
Print
Pages: [
1
]
« previous
next »
PxPlus User Forum
»
Main Board
»
Discussions
»
ODBC
»
MySQL write via ODBC