0

enter image description here

Not able to load multiple tables, getting error:

Exception in component tMysqlInput_1 (MYSQL_DynamicLoading) java.sql.SQLException: Bad format for Timestamp 'GUINESS' in column 3

One table works fine. Basically after first iteration the second table trying to use the schema of the first table. Please help, how to edit the component to make it correct. Trying to load actor & country table from sakila DB mysql to a another DB on the same server. Above image is for successful one table dynamic loading.

Blauharley
  • 4,186
  • 6
  • 28
  • 47
syncdm2012
  • 445
  • 2
  • 10
  • 22
  • Your tables don't have the same structure, I'm guessing the structure you defined for your tMysqlInput corresponds to the 1st table, that's why it works. Then it's trying to load the 2nd table using the same schema, which can't possibly work. – Ibrahim Mezouar Jan 22 '18 at 12:24
  • Yes, that is correct, but how it should work. cant find any work around.How do we pass all tables without using the first table schema. – syncdm2012 Jan 22 '18 at 12:42
  • Which version of Talend do you use? Open Studio or a Subscription? Dynamic schemas are available only in Subscription versions. – mcode Jan 23 '18 at 12:18

2 Answers2

0

you should not use tMysqlInput if output schemas differ. For this case there is no way around tJavaRow and custom code. I however cannot guess what happens in tMap, so you should provide some more details about what you want to achieve.

Vladimir Samsonov
  • 1,344
  • 2
  • 11
  • 18
  • I want to load two(multiple tables) from one DB to another DB with same structure but dynamically. In short, I want to iterate each table from the file and load them in the target – syncdm2012 Jan 22 '18 at 12:35
  • so two tables in source db have different structure against each other?! . Are both of those Databases located in one and the same mysql instance/server? – Vladimir Samsonov Jan 22 '18 at 12:41
  • For the time being, both of those Databases located in one and the same mysql instance/server. But the real scenario is different server. Still same server loading is not happening. Server(local)-DB(sakila)-Table(actor&country) loading to (Server(local)-DB(db_central)-Table(actor&country) – syncdm2012 Jan 22 '18 at 12:45
  • as you may already know: dynamic flow schemas are only available in Talend Enterprise. Because your tables actor&country do not have same structure you cannot use the same flow in TOS MDM. For your case i would use tSSH or tSystem to execute mysqldump on source server, than read the dump with tFileWhatever and execute on target server with tMysqlRow. ;) – Vladimir Samsonov Jan 22 '18 at 12:58
0

If all you need is to load data from one table to another without any transformations, you can do one of the following:

  1. If your tables reside in 2 different databases on the same server, you can use a tMysqlRow and execute a query "INSERT INTO catalog.table SELECT * from catalog2.table2..". You can do some simple transformations in SQL if needed.
  2. If your tables live in different servers, check the generic solution I suggested for a similar question here. It may need some tweaking depending on your use case, but the general idea is to replicate the functionality of INSERT INTO SELECT when the tables are not on the same server.
Ibrahim Mezouar
  • 3,981
  • 1
  • 18
  • 22