How to view SQL executed

One Star

How to view SQL executed

Hi,
I'm new user of TOS.
I would like to view SQL generated and executed by TOS to store in log file.
I create a job to read data from spreadsheet file and insert it into database (MySQL).
It's work fine, but I don't know how to visualize SQL executed in my database.
I use the tLogRow component to view data but output isn't SQL but raw data.
I try checking Enable debug mode in advanced settings in tMySQLOutput component but nothing changes.
Does anyone know how to view/store SQL ?
Thx in advance.
Informations:
- TOS 3.0.1.r19980
- Vista SP1
- Java project
One Star

Re: How to view SQL executed

Hi Milou,
I don't think that Talend do this. Talend Team can correct me if i'm wrong. but you can use TOS to extract queries from your Mysql Log file. Have a look here:http://www.howtogeek.com/howto/database/monitor-all-sql-queries-in-mysql/
I hope this helps.
Regards,
Youssef
One Star

Re: How to view SQL executed

Thx for you quick reply.
Can Talend Team confirm that I can't do this ? (view and log SQL executed)
Employee

Re: How to view SQL executed

Hello,
There is a variiable in all db output component which partially answer to your question.
Currently, there is not a global mechanism to check somewhere and that will automatically log all processed requests.
Here is an example of what you can do to resolve manually this question.
In this example, I used
System.out.println(((String)globalMap.get("tMysqlOutput_1_QUERY")));

but this is only a starting point. You can do everything you want after..
Regards,
One Star

Re: How to view SQL executed

Thanks for your workaround but I'm not totally satisfied.
Because it works great for UPDATE sql request and not for INSERT:
It always displays "null" instead of real sql query (INSERT INTO ....)
Employee

Re: How to view SQL executed

Can you share a screenshot of your job ?
One Star

Re: How to view SQL executed

Of Course !
I put a row filter componant to split rows between insert and update mode.
tMysqlOutput_2 executes "update" SQL request.
tJavaRow_2 contains this code: System.out.println(((String)globalMap.get("tMysqlOutput_2_QUERY")));
When the job is started, I can see in console all "update" request like UPDATE MyTable SET MyColumn = MyValue; ....
tMysqlOutput_1 executes "insert" SQL request.
tJavaRow_1 contains this code: System.out.println(((String)globalMap.get("tMysqlOutput_1_QUERY")));
When the job is started, I can see in console only null instead of real SQL request.
Employee

Re: How to view SQL executed

I have tried exactly the same job on my side, it's working pretty well (in 3.0.2, but I don't think that there is any difference on this topic between 3.0.1 and 3.0.2).
I don't understand why you have a different result....
Can you tell us why you have a red cross on your tLogRow and on your tMySQLOutput ? That's a little bit strange...
One Star

Re: How to view SQL executed

the error message are:
- The schema from the input link "row8" is different from the schema defined in the component
- The schema from the input link "row10" is different from the schema defined in the component
I don't know why an error is displayed for the tMysqlOutput_2 componant, because the schema is the same for row10 and row13 when I edit it.
If I click on "sync columnc" button, the schema is not modified but the error disappears... It seems to be strange...
For tLoghRow_4, I've a difference in schema because I remove the primary key column in row11.
The column is an autoincrement and if I keep this column with 0 value in the flow, it will insert 0 value for each line inserted.
See my screenshot.
I am wrong ? Or must I keep this column in the schema and set null value instead of 0 ?
I don't know what is the good practice.
I think also it will be very useful for beginner to complete the collection of available tutorial with generic usecases.
You will have no more post like this.
i.e.
- How to merge/split flow in project (a post exists but only for perl project. It can be integreated in a tutorial and add java project)
- How to read a spreadsheet file and insert data into database in insert/update sql mode and how to get the last insert id for each row.
- How to view and log SQL request executed in database
- ...
;-)
One Star

Re: How to view SQL executed

We are on 3.0.2 and have the same issue with the global variable returning "null" for Talend generated INSERT Sql. Is anyone at Talend working on a bug fix for this?

I have been emailing Jonathan Andry at Talend about this and it looks like he found a code change that needs to be made.

Let me know if another BugTracker needs to be opened on this or whether this one is sufficient.
One Star

Re: How to view SQL executed

I confirm the null output of
System.out.println(((String)globalMap.get("tMysqlOutput_1_QUERY")));
One Star

Re: How to view SQL executed

I also confirm System.out.println(((String)globalMap.get("tMysqlOutput_1_QUERY"))); produces a bunch of nulls.
One Star

Re: How to view SQL executed

does anyone know if this is working yet i am also getting nulls when i try pass a var from SQL to tJAVA
One Star

Re: How to view SQL executed

From this post:

http://www.talendforge.org/forum/viewtopic.php?id=17550
You need to check "Enable debug mode" in the MysqlOutput_1 advanced settings, in order for:
System.out.println(((String)globalMap.get("tMysqlOutput_1_QUERY"))); to return a non-null value.