topic Re: tMySQLOutput Update problem in Installing and Upgrading

tMySQLOutput Update problem

_AnonymousUser — Sat, 16 Nov 2024 14:34:32 GMT

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.

Re: tMySQLOutput Update problem

Anonymous — Thu, 19 Jul 2007 14:27:55 GMT

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.

Re: tMySQLOutput Update problem

_AnonymousUser — Thu, 19 Jul 2007 14:50:00 GMT

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.

Re: tMySQLOutput Update problem

Anonymous — Thu, 19 Jul 2007 15:02:11 GMT

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

Re: tMySQLOutput Update problem

_AnonymousUser — Thu, 19 Jul 2007 15:09:41 GMT

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

Re: tMySQLOutput Update problem

Anonymous — Thu, 19 Jul 2007 22:39:23 GMT

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.

Re: tMySQLOutput Update problem

_AnonymousUser — Fri, 20 Jul 2007 07:50:29 GMT

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.

Re: tMySQLOutput Update problem

Anonymous — Fri, 20 Jul 2007 23:58:57 GMT

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.

Re: tMySQLOutput Update problem

_AnonymousUser — Mon, 23 Jul 2007 10:27:44 GMT

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

Re: tMySQLOutput Update problem

Anonymous — Tue, 26 Feb 2008 13:35:16 GMT

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

Re: tMySQLOutput Update problem

Anonymous — Tue, 26 Feb 2008 21:42:05 GMT

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.

Re: tMySQLOutput Update problem

Anonymous — Wed, 27 Feb 2008 07:09:16 GMT

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