Iterating over all tables in one db to create in another db

One Star

Iterating over all tables in one db to create in another db

Hi,
I was wondering if someone could give me some pointers (in perl if possible, but in java would be ok as well) on how to go about iterating over a list of tables derived from an Oracle db query:
select TABLE_NAME FROM all_tab_columns where OWNER='AzR' and TABLE_NAME LIKE 'DATE%' and COLUMN_NAME='CHANGED';
so that I can create the tables in mysql. I can do it individually by using tOracleInput, tMap, and tMysqlOutput components, but the whole setup is manual for each table. I was hoping to be able to parameterize the table names and queries so I could loop through them all without the manual set up.
I'm using:
"Talend Open Studio"
Version: 4.0.0
Build id: r39896-20100406-0827

Thanks,
Eric
One Star

Re: Iterating over all tables in one db to create in another db

After working on this for awhile, I don't think this would be possible without a good amount of custom coding. I was thinking that I would just iterate through each table name which I would set as a variable for all the rest of the components, but I forgot about the mapping, which is different for each component.
If there is a way to dynamically map the input and output schemas, please let me know.
Thanks,
Eric
Seven Stars

Re: Iterating over all tables in one db to create in another db

I've done something like this from DB2 or ODBC to SQL Server or Pervasive but it's tricky.
First you need to construct empty tables in MySQL that match those in Oracle by iterating over tJDBCTableList and tJDBCColumnList (unfortunately there is no tOracleColumnList component at present) to tJava wherein you construct a Create Table SQL statement by appending each column's details to a globalMap variable. From the tOracleColumnList component use an OnSubjobOk trigger to a tMySqlRow component wherein you put your final Create Table statement.
Then you need to read the data from each table (OnSubjobOK from the tMySqlRow component to a tOracleInput component) to a dummy table in MySQL using a dummy schema that is all Strings and has at least as many columns as the maximum number of fields in any of your tables (just called Field1, Field2 etc.). (Any excess fields for a given table will just be null.)
Then you need to use another tMySqlRow component (OnSubjobOK from the tOracleInput component) to execute an "Insert Into <real table> Select <correct number of fields> From <dummy table>" statement to copy and auto-cast the data from the dummy table into the correct data types in your real table. (Note that, to go backwards a few steps, you will need to construct the <correct number of fields> part of the statement in the same tJava as you constructed the Create Table SQL statement.)
This approach means you have one job that iterates over every table in your source database and copies the table structure and data to your destination database without having to pre-define the true schema for any of your tables because the data goes via a dummy table that is all strings and has more fields than your real tables and MySQL auto-casts the data back to your true data-types when you insert it into your true destination tables.
Good luck.
One Star

Re: Iterating over all tables in one db to create in another db

Thank you! That looks like just the type of help I was seeking.
One Star

Re: Iterating over all tables in one db to create in another db

Hi , i am facing a problem in gathering the query for each table ,what should i do in the tjava component specifically  in order to run the queries in tmysqlrow ? please i need pictures.
One Star

Re: Iterating over all tables in one db to create in another db

Hi , i am facing a problem in gathering the query for each table ,what should i do in the tjava component specifically  in order to run the queries in tmysqlrow ? please i need pictures.