
Anonymous
Not applicable
2009-09-02
07:55 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Update Or Insert on AS400
Hello,
I have an Excel File with 3 columns. The first and second columns are the both the key, the third is a simple text.
I would like to insert or update on a table on AS400.
In order to do that, I put my Excel File like an Input Files. I define the first and the second columns like a key.
I link my ExcelFile to a tMap and the tMap with tAS40Output.
I map the column of excel files to the table of AS400.
The job run and generate an error when i try to insert Data
for exemple
1 a toto
2 b tata
2 c titi
when I look in my table I find
1 a toto
2 b tata
but not 2 c titi
I put on As400Output ,in settings Action on Date : "Update or Insert" or "Insert or Upadte"
I select two columns as a key and set them are updatable
Could you help me please? Have you got an idea to resolve my problem?
PS: If i use a simple key (one column) that's works perfectly.
Thanks
I have an Excel File with 3 columns. The first and second columns are the both the key, the third is a simple text.
I would like to insert or update on a table on AS400.
In order to do that, I put my Excel File like an Input Files. I define the first and the second columns like a key.
I link my ExcelFile to a tMap and the tMap with tAS40Output.
I map the column of excel files to the table of AS400.
The job run and generate an error when i try to insert Data
for exemple
1 a toto
2 b tata
2 c titi
when I look in my table I find
1 a toto
2 b tata
but not 2 c titi
I put on As400Output ,in settings Action on Date : "Update or Insert" or "Insert or Upadte"
I select two columns as a key and set them are updatable
Could you help me please? Have you got an idea to resolve my problem?
PS: If i use a simple key (one column) that's works perfectly.
Thanks
383 Views
- « Previous Replies
-
- 1
- 2
- Next Replies »
10 Replies

Anonymous
Not applicable
2009-09-02
08:29 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
provide detailed description of the error
320 Views

Anonymous
Not applicable
2009-09-02
08:33 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
"Error"
Exception in component tAS400Output_1
java.lang.RuntimeException: Execution failure, there might be an error in your SQL syntax.
at transfert_exelversas400.testupdate_0_1.TestUpdate.tFileInputDelimited_2Process(TestUpdate.java:672)
at transfert_exelversas400.testupdate_0_1.TestUpdate.runJobInTOS(TestUpdate.java:879)
at transfert_exelversas400.testupdate_0_1.TestUpdate.main(TestUpdate.java:788)
Exception in component tAS400Output_1
java.lang.RuntimeException: Execution failure, there might be an error in your SQL syntax.
at transfert_exelversas400.testupdate_0_1.TestUpdate.tFileInputDelimited_2Process(TestUpdate.java:672)
at transfert_exelversas400.testupdate_0_1.TestUpdate.runJobInTOS(TestUpdate.java:879)
at transfert_exelversas400.testupdate_0_1.TestUpdate.main(TestUpdate.java:788)
320 Views

Anonymous
Not applicable
2009-09-02
08:50 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Check as400 schema and reset db types,
or look at the code with the red mark where the update string is built.
or look at the code with the red mark where the update string is built.
320 Views

Anonymous
Not applicable
2009-09-02
08:59 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I've checked it but still the error.
there no red mark where the update string is built
this is a part of the code genereted
String update_tAS400Output_1 = "UPDATE "
+ "testupdate"
+ " SET id = ?,nom = ?,prenom = ?,adresse = ? WHERE id = ? AND nom = ?";
java.sql.PreparedStatement pstmtUpdate_tAS400Output_1 = conn_tAS400Output_1
.prepareStatement(update_tAS400Output_1);
String insert_tAS400Output_1 = "INSERT INTO " + "testupdate"
+ " (id,nom,prenom,adresse) VALUES (?,?,?,?)";
there no red mark where the update string is built
this is a part of the code genereted
String update_tAS400Output_1 = "UPDATE "
+ "testupdate"
+ " SET id = ?,nom = ?,prenom = ?,adresse = ? WHERE id = ? AND nom = ?";
java.sql.PreparedStatement pstmtUpdate_tAS400Output_1 = conn_tAS400Output_1
.prepareStatement(update_tAS400Output_1);
String insert_tAS400Output_1 = "INSERT INTO " + "testupdate"
+ " (id,nom,prenom,adresse) VALUES (?,?,?,?)";
320 Views

Anonymous
Not applicable
2009-09-02
02:59 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Run with trace option enable to see if incoming data is somewhat strange
- try with different data input source
- debug the job
- try with different data input source
- debug the job
320 Views

Anonymous
Not applicable
2009-09-02
03:51 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Anyway, sometimes I've found myself in the same situation and it is a pity that the sqlexception is masked in the code when a useful hint can be given...
I see that in many sql related components talend upcast to Exception ( losing some methods )... but in the as400 case they just drop the exception throwing a runtimeexception instead.... there is some inconsistency in handling sql exception in all talend components...
anyway I've filed a bug/request, would be useful for all:
http://www.talendforge.org/bugs/view.php?id=8972
I see that in many sql related components talend upcast to Exception ( losing some methods )... but in the as400 case they just drop the exception throwing a runtimeexception instead.... there is some inconsistency in handling sql exception in all talend components...
anyway I've filed a bug/request, would be useful for all:
http://www.talendforge.org/bugs/view.php?id=8972
320 Views

Anonymous
Not applicable
2009-09-03
06:44 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot for your answer.
I tried all your propositions but the error still, I think there is some incompatibility with AS400 !!
I see the Talend does not manage well the AS400
I tried all your propositions but the error still, I think there is some incompatibility with AS400 !!
I see the Talend does not manage well the AS400
320 Views

Anonymous
Not applicable
2009-09-03
06:57 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I use as400 also, and yes there are still some quirks...
sometimes I see that there are random problems if the file is being use is under journaling or not...
sometimes I had to force in the connection "additonal parameters" in talend this string
"prompt=false;transaction isolation=none"
to be able to work
For all, the additional JDBC parameters can be found at
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzahh/javadoc/JDBCProperties.html
Would be nice to have exposed in the GUI such options during connection configuration.
sometimes I see that there are random problems if the file is being use is under journaling or not...
sometimes I had to force in the connection "additonal parameters" in talend this string
"prompt=false;transaction isolation=none"
to be able to work
For all, the additional JDBC parameters can be found at
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzahh/javadoc/JDBCProperties.html
Would be nice to have exposed in the GUI such options during connection configuration.
320 Views

Anonymous
Not applicable
2009-09-03
07:07 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have a question about the execution of Talend in AS400
When I run a job for loading a data in a table and I look in AS400 with "wrkactjob", Talend can't exeed the 20-23 % UC
Is it your case ? or do you have any idea ?
Thanks
When I run a job for loading a data in a table and I look in AS400 with "wrkactjob", Talend can't exeed the 20-23 % UC
Is it your case ? or do you have any idea ?
Thanks
320 Views

- « Previous Replies
-
- 1
- 2
- Next Replies »