AnsweredAssumed Answered

ODBC update MySQL error result?

Question asked by greaterthandata on Sep 25, 2018
Latest reply on Sep 29, 2018 by beverly

I have a solution where we connect using ODBC with a Execute SQL script step to update records in SQL, and now are moving the solution over to a new host with MySQL.


The script basically tried to run an update and if it failed (with error 1409) it would then run an insert instead.


the script looks like this ...

set error capture (on)

execute SQL ( "update <tablename> set <fieldname> = '14' where ID = <somenumber>")

if (get(lasterror) = 1409

     executeSQL ( "insert ...")

end if


after moving the solution to connect to MySQL, I no longer receive a 1409 error.  It basically results in no error.  And using Get(LastExternalErrorDetail) also results in no error.  Trying the same update in MySQL Workbench, it results in "0 row(s) affected Rows matched: 0  Changed: 0  Warnings: 0"


Is there a way to see if the row exists, and if so ... run the update, else insert?


I dont want to use ESS since we are talking about hundreds of thousands of records in this situation.