topic Re: Talend ETL - Mapping Java Object to Oracle Connection in Talend Studio

Talend ETL - Mapping Java Object to Oracle Connection

Anonymous — Thu, 30 Aug 2018 11:01:13 GMT

Hi, i am new in using Talend.

I have a java class that call gmail api and return the emails. Then i want to insert the emails to the database (Oracle). How to map the email object that returned from java code to Database?

Re: Talend ETL - Mapping Java Object to Oracle Connection

fdenis — Thu, 30 Aug 2018 13:09:55 GMT

you can directly use tPOP to retrieve mails.
Regards,

Re: Talend ETL - Mapping Java Object to Oracle Connection

Anonymous — Fri, 31 Aug 2018 02:06:27 GMT

Thank you for your answer.

However, i need to do some formatting, transform and processing email content also get all email information such as from, to, in-reply-to, etc with java code so I think I still need using java code then insert the processed data to DB. Is there any way to mapping between java object and oracle? What component i need to do this job

Here is the scenario i want:

1. Call Gmail Api and get list of email threads then process it, then return processed List<Thread> (each email thread contain information such as thread id, messages etc)

2. Loop and mapping each Thread to insert it to DB (one thread one row, example Thread A become insert into table_x (threadId, messages, etc) VALUES (A.getThreadId(), A.getMessages(), etc)

Re: Talend ETL - Mapping Java Object to Oracle Connection

fdenis — Fri, 31 Aug 2018 09:36:26 GMT

there is 3 components:
tJava
tJavaRow
tJavaFlex (this is the one you need)
just put component into jog select it and press F1 to have description and samples.
Good luck

Re: Talend ETL - Mapping Java Object to Oracle Connection

Anonymous — Mon, 03 Sep 2018 02:49:42 GMT

Ok, thank you for your answer.

I will try it.

Re: Talend ETL - Mapping Java Object to Oracle Connection

Anonymous — Mon, 03 Sep 2018 08:53:11 GMT

Hi, i have successfully insert to database from java component using:

tJavaFlex -> tMap -> tOracleRow

However, I need to insert the data into three table in one transaction in this sequence:

1. Table Master

2. Table Detail

3. Table Email (Have foreign key from table Master I)

So, I have two question:

1. How to pass the sequence id that got from successfully inserted Master table to the Table Email?

2. How to rollback other success transaction if there is one failed transaction in one table?

What component that I can use for this scenario?

Re: Talend ETL - Mapping Java Object to Oracle Connection

fdenis — Mon, 03 Sep 2018 09:34:48 GMT

there is multiple solutions.
The cleanest one is to write a stored procedure to insert email. so every clients app can use it.
if you have a sequence you can also call nextId from the sequence before inserting.
an other one is to insert max(id)+1

When you have got your id you can link it to your data (use tMap with link retrieving next Id for each row).

for rollback, it also depend on how it work.

Re: Talend ETL - Mapping Java Object to Oracle Connection

Anonymous — Mon, 03 Sep 2018 10:50:01 GMT

So, what is the best way for rollback one record as one transaction? Is there any way to group three tOracleRow into one transaction?

Re: Talend ETL - Mapping Java Object to Oracle Connection

fdenis — Mon, 03 Sep 2018 11:27:47 GMT

You can catch error on each components using onComponetError and link it to a rollback (tCommit)

If you are using SQL procedure to insert data you have to manage transaction into it.

You can also write a job for one mail and call it from another job who manage tRunJob error.

It depends on your requirements?

Question is how many mail do you have to insert and how often?
How many error do you expects?
What do you want to do with error?

Re: Talend ETL - Mapping Java Object to Oracle Connection

Anonymous — Tue, 04 Sep 2018 09:31:00 GMT

For this case i will try to use begin/end transaction query to group them in one transaction.

However, i have another problem. How to correctly get value from tMap in tOracleRow?

This is my schema

The thread Id in row1 and email is correct but after insert, the value in DB is become "165" not "165...". I use String type data in Tmap and my query code is:

"Insert INTO ...." + email.threadId + "," + "...."

If I insert manually with my query to the database, the data is correctly added with same value.

Is it because type of data in email.threadId become number? How to correctly call the data from tMap variable?

Screenshot_39.png

Re: Talend ETL - Mapping Java Object to Oracle Connection

fdenis — Tue, 04 Sep 2018 09:33:53 GMT

"Insert INTO ....'" + email.threadId + "'," + "...."
did you add ' around SQL String?

Re: Talend ETL - Mapping Java Object to Oracle Connection

Anonymous — Tue, 04 Sep 2018 09:59:42 GMT

Thank you, it is worked.

I made simple mistake.