topic Re: How to view SQL executed in Talend Studio

How to view SQL executed

Anonymous — Sat, 16 Nov 2024 14:09:16 GMT

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

Re: How to view SQL executed

youssef2 — Thu, 20 Nov 2008 15:15:28 GMT

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

Re: How to view SQL executed

Anonymous — Thu, 20 Nov 2008 16:35:30 GMT

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

Re: How to view SQL executed

Anonymous — Fri, 21 Nov 2008 16:14:24 GMT

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,

Re: How to view SQL executed

Anonymous — Thu, 27 Nov 2008 09:57:00 GMT

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 ....)

Re: How to view SQL executed

Anonymous — Thu, 27 Nov 2008 18:55:32 GMT

Can you share a screenshot of your job ?

Re: How to view SQL executed

Anonymous — Tue, 02 Dec 2008 14:59:48 GMT

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.

Re: How to view SQL executed

Anonymous — Thu, 04 Dec 2008 20:43:57 GMT

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...

Re: How to view SQL executed

Anonymous — Fri, 05 Dec 2008 08:55:46 GMT

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
- ...
😉

Re: How to view SQL executed

Anonymous — Tue, 06 Jan 2009 15:31:39 GMT

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.

Re: How to view SQL executed

_AnonymousUser — Tue, 28 Sep 2010 08:34:19 GMT

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

Re: How to view SQL executed

_AnonymousUser — Thu, 03 Jan 2013 23:57:30 GMT

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

Re: How to view SQL executed

Anonymous — Wed, 06 Nov 2013 15:40:19 GMT

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

Re: How to view SQL executed

Anonymous — Tue, 10 Dec 2013 22:45:14 GMT

From this post:

https://community.talend.com/t5/Design-and-Development/Generated-Sql-Insert-statement/td-p/101412
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.