[resolved] MySQL Insert/Update with bad data

[Anonymous]

I am trying to understand how put data into a DB table.
I am using TOS 3.2.3.r35442 with Perl and using MySQL for the DBMS.
I have set up a simple test scenario where I use a single table with a primary key defined in the schema.
The table initially contains some predefined data.
I then have a TOS job which reads some further data from a file and attempts to load it into the table.
The catch is that the schema includes a field of "date" type and one row in the file has an invalid date value (deliberately!)
The remaining rows contain valid data, some with keys matching the data already in the table, others with new key values.
(The primary key in the schema is the same as the key for insert and update.)
I have tried various values of "Action on Data" in the tMySQLOutput component:
- with "Update or Insert" I get a message which correctly reports the data error, but that causes the whole operation to stop (or be rolled back) and none of the new data is added.
- with "Insert or Update" the valid data rows are loaded (inserted or updated as expected), the invalid row is ignored, but there is no error message at all to indicate the problem.
I am puzzled why the two "Action" settings give such dramatically different effects, since (given a unique key) I would have expected them to do the same thing. More important though, neither is the behaviour I am looking for, which is to get an error on the erroneous data, but for the remaining records to work normally.
I have also tried wrapping the job with Connection, Commit and Rollback components. This gives similar results, except that the "Insert or update" case gives additional error messages about "duplicate entry" for the rows in the file which match existing keys (i.e. which I expect to be updated).

[Anonymous]

Hello
First, about the difference between insert or update and update or insert, see 10017.
In Java project, there is a 'die on error' option on the compopnent(it does not exist in Perl project), it is uncheck by default so as to catch the error and can continues to process the remaining records if there are some error. And you can see the error rows from the reject link.
Best regards
Shong

[Anonymous]

Thanks Shong.
I take the point about choosing "Insert or Update" or "Update or Insert" depending on which is likely to be the greater volume, but the forum topic you mention says they should give the same result - which is not what I found at all.
I had noticed a reference to "die on error" but couldn't find it - maybe I need to use Java rather than Perl.
But, how do I get a reject link out of a tMySQLOutput component?

[Anonymous]

Hello

I had noticed a reference to "die on error" but couldn't find it - maybe I need to use Java rather than Perl.

I suggest you to use Java project, because there are more feature on Java project than perl.

But, how do I get a reject link out of a tMySQLOutput component?

Right click on tMysqlOutput, then choose 'row'-->'rejects' and link it to a output component, such as tLogRow. Note that, make sure uncheck the 'Extend insert' box on the advanced setting tab if you are using the reject link.
Best regards
Shong

[Anonymous]

But tMySQLOutput doesn't offer a "Row -> Rejects" option - the only item under "Row" is "Main"

[Anonymous · Accepted solution]

Hello
Sorry, it is in a Java project.
Best regards
Shong