How to use dynamic DB name with mysql

One Star

How to use dynamic DB name with mysql

I have a job that exports data from Salesforce. Getting the data is easy, saving to mysql is easy. The challenge: Each time the job runs, I want to use a dynamically generated database name in the mysql database. Something like SFData-yyyy-mm-dd. How can I, in Talend, at the time the job runs generate a database name, create the database, and use it so that the data export from Saleforce goes into this database? Each time the job runs I want it to create a new database and put the data there.
Seventeen Stars

Re: How to use dynamic DB name with mysql

This should be possible.
I would use tMysqlRow component to execute the create database command.
All database properties should be read from context variables.
After creating the database, overwrite the context.database variable with your new name.
After that you start your data fetching and saving in a separate sub job.
In your database output components you can use the table action "Create table if not exists".
BTW. Each time the job runs? I guess you should think about it, because you can probably run a job in one day twice (e.g. for testing purposes).
One Star

Re: How to use dynamic DB name with mysql

Thank you - this is clear except how do you over write the context.database variable? I see where it holds the database name, but I'm not sure how I would over write it.
As for DB name, yyyy-mm-dd was just an example, I actually use yyy-mm-dd-hh-mm-ss for things like this
Seventeen Stars

Re: How to use dynamic DB name with mysql

You can overwrite it simply in any tJava components with this code:
context.database = "SFData_" + TalendDate.format("yyyy-MM-dd-HH-mm-ss", TalendDate.getCurrentDate());
Please take care you overwrite this before the subjob using this database started.
One Star

Re: How to use dynamic DB name with mysql

I'm stumped with the tJava component. I read the documentation, and it tells me next to nothing.
I have output from a tSystem component going into a tJava component, and I know this works because I run it through a tLogRow and I see the expected output (my DB name that I want to use). I want to capture this in the tJava component and use it to set the name of the database that the tMysqlRow component will use. I do this to see if I'm getting it:
System.out.println("->" + (String)globalMap.get("tSystem_1_OUTPUT"));
And it gives me null. Am I missing some way to capture the output of the tSystem component, or is there something special I have to do to pass the output of the tSystem to the tJava?
Secondly, if I just try this:
context.database = "testDB";
In the tJava, I get "database cannot be resolved or is not a field"
I'm getting nowhere with the tJava component - are there any better docs that cover it in more detail, or some more extensive examples of how to use it?
Seven Stars

Re: How to use dynamic DB name with mysql

tJava is for pure Java - whatever you want. But it's not for use in a flow as all code in it is executed before the flow starts.
If you want to capture a globalMap variable set by a component (e.g. tSystem_1_OUTPUT) then tJava should be joined with OnComponentOK from the component in question.
If you want to write Java to act on a flow, you should use tJavaRow instead of tJava.