You are not logged in.
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. [statistics] connecting to socket on port 4321 [statistics] 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) [statistics] 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. [exit code=0]
Why does the job stops ?
What's the difference betwween "insert or update" and "update or insert" ?
can't help you for your error, we need more info about what you are actually doing.
But the difference between update or insert and insert or update is the following :
update or insert : Will first try to update, if it can't (because record doesn't exist), it will try to insert.
insert or update : Will first try to insert, if records already exists (primary key already exists), it will try to update the record.
Project details :
I try to bring data from temporary database into statistical database (both are under MySQL 5).
Into temporary space, I just import fields since last transaction. Then, I update statistical space using preceding data.
You can find a pic of my job here :
If necessary I can post tMap print screen.
Hi, into "Run" tab you just have to check "Statistics" box.
Update is done on the key selected in the schema of the tMySQLOutput_5
Is she well setted ?
Concerning "Insert or update" and "Update or insert" options, they will have exactly the same results.
But you can choose between them for better performance if you know your process.
I have two keys into tMySQLOutput. How can I avoid my problem ?
Here's a pic of tMap :
It's really strange ; it works fine for others link but not for this one. Even if it's the same architecture.
That's exactly your problem : if you have two columns and if all of them are keys, you have nothing to update...
Imagine two columns : x and y
if x='a' and y='b' in flow.
If x is key in TOS : generated request will be "update table myTable set y='b' where x='a'"
If y is key in TOS : generated request will be "update table myTable set x='a' where y='b'"
but if x and y are keys, what would you like the request to be ? "update table myTable set x='a' and y='b" where x='a' and y='b'" ?
This won't change anything in the db and might be considered as a business error.
That's why we have chose to generate "update table myTable set where x='a' and y='b'" which generates an error... !!!
Hope it helps,
I have deleted primary keys and now it works.
Thank you for your support.
this is a very old post but very actual for me...
I've a table with two columns, both keys. Removing primary keys is not a solution for me. I changed the Action on data to "Insert", but now I get a long list of duplicated keys. Is there a way to suppress them without loading the data into memory (=> join in tMap)?
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.