Hi,
I have a problem using tMySQLOutput component : I make a transformation with tMap component. On differents Outputs, I want to update data or insert if they are non-existent. One of them generate an error when data with same primary key values are already inserted :
Starting job Job5a at 14:54 19/07/2007. connecting to socket on port 4321 connected Exception in component tMysqlOutput_5 Exception in component tMysqlOutput_5 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ID_NUM_NATIONAL=9 AND ID_NUM_NATIONAL_2=9' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) disconnected at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:353) at com.mysql.jdbc.Util.getInstance(Util.java:336) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2938) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1601) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1710) at com.mysql.jdbc.Connection.execSQL(Connection.java:2436) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1402) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1694) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1608) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1593) at pmi_stat.job5a.Job5a.tMysqlInput_1Process(Job5a.java:5503) at pmi_stat.job5a.Job5a.tFileList_1Process(Job5a.java:464) at pmi_stat.job5a.Job5a.main(Job5a.java:9098) Job Job5a ended at 14:54 19/07/2007.
Why does the job stops ?
What's the difference betwween "insert or update" and "update or insert" ?
Thank you.
Hello Volker,
Since 2.3.0, db output components have a "field option" in Advanced Settings configuration.
In this table, you can choose some different keys in update.
This will also allow you to generate all update request you might want.
Regards,
Mike.
