One Star

tMySQLOutput Update problem

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.
11 REPLIES
One Star

Re: tMySQLOutput Update problem

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.
One Star

Re: tMySQLOutput Update problem

Thank you.
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.
One Star

Re: tMySQLOutput Update problem

Hi,
I can't answer you,
but just a question:
how do you display the green statistics ?
Thanks
One Star

Re: tMySQLOutput Update problem

Hi, into "Run" tab you just have to check "Statistics" box.
Employee

Re: tMySQLOutput Update problem

Slum,
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.
Regards,
Michaël.
One Star

Re: tMySQLOutput Update problem

Hi Mhirt,
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.
Employee

Re: tMySQLOutput Update problem

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,
Regards,
Michaël.
One Star

Re: tMySQLOutput Update problem

I have deleted primary keys and now it works.
Thank you for your support.
One Star

Re: tMySQLOutput Update problem

Hello together,
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)?
Bye
Volker
Employee

Re: tMySQLOutput Update problem

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.
One Star

Re: tMySQLOutput Update problem

Hello Mike,
thanks for the information. It works (even if I do not understand it) with "Key in update" and "Updatable".
Bye
Volker