Talend community forum / Get no results when running dynamic SQL example

jules · 2009-06-03 15:32:07

Hi there

I am trying to work out how to execute sql commands, based on the results of a select statement. I am also trying to use information from the select statement as part of my sql command. Basically trying to work out how to implement "dynamic" sql from within TALEND.

So I am using a simple example, the idea being that if I know how to do this simple example, I can use the concept for more complex scenarios:

1. Setup a database connection to use
2. Oracle input to "Select table_name from user_tables" -----this gives me my list of tables to process
3. OracleRow to execute an analyze command on each table returned.

I have no idea if I have done this correctly, I have cobbled it together from things I have seen in the forums and basically just done a bit of thumbsucking:)

Problem is....it doesn't work! Don't get any errors, the log shows its successful, but in the database I don't see that the tables have been analyzed.

Below are some print screens of what I have done

As I said, I might be completely barking up the wrong tree...if so, please have patience with a beginner, and could somebody recommend a more realistic/practical way of doing this?

jules · 2009-06-03 15:39:28

sorry...struggling to upload jpegs...lets try 1 at a time

jules · 2009-06-03 15:42:04

1 last time

Uploaded Images

shong · 2009-06-04 11:20:36

Hello

You can use the tFlowToIterate component, please see my screenshot.
Also, if you want to iterate some tables, you can try the tOracleTableList component.

Best regards

shong

Uploaded Images

jules · 2009-06-04 12:56:00

This is what my sql looks like:

"analyze table "+(String)globalMap.get("tableName")+" estimate statistics"

i have tried numerous variations on this, but I keep getting the following syntax error on it:

starting job TEST at 12:53 04/06/2009.

Bareword found where operator expected at C:\TIS\TOS-Win32-r24830-V3.1.1\workspace\.Perl\TESTING.job_TEST_0.1.pl line 449, near ")globalMap"
(Missing operator before globalMap?)
Bareword found where operator expected at C:\TIS\TOS-Win32-r24830-V3.1.1\workspace\.Perl\TESTING.job_TEST_0.1.pl line 473, near ")globalMap"
(Missing operator before globalMap?)
Bareword found where operator expected at C:\TIS\TOS-Win32-r24830-V3.1.1\workspace\.Perl\TESTING.job_TEST_0.1.pl line 482, near ")globalMap"
(Missing operator before globalMap?)
syntax error at C:\TIS\TOS-Win32-r24830-V3.1.1\workspace\.Perl\TESTING.job_TEST_0.1.pl line 449, near ")globalMap"
syntax error at C:\TIS\TOS-Win32-r24830-V3.1.1\workspace\.Perl\TESTING.job_TEST_0.1.pl line 473, near ")globalMap"
syntax error at C:\TIS\TOS-Win32-r24830-V3.1.1\workspace\.Perl\TESTING.job_TEST_0.1.pl line 482, near ")globalMap"
Execution of C:\TIS\TOS-Win32-r24830-V3.1.1\workspace\.Perl\TESTING.job_TEST_0.1.pl aborted due to compilation errors.
Job TEST ended at 12:53 04/06/2009. [exit code=9]

jules · 2009-06-04 13:10:39

ok, I think i know what that problem was...i think your example was based on java? whereas mine needs to be for perl....changed it to look as follows:

"analyze table "+$_globals{tFlowToIterate_1}{tableName}+" estimate statistics"

problem being, it still doesn't do anything!

and i have no idea how to figure out what it is trying to do! is there no way that i can change the log level of this thing to show me what it is physically trying to execute?

jules · 2009-06-04 14:12:08

Ok, so I thought there must be something wrong with my variable, i.e.:

$_globals{tFlowToIterate_1}{tableName}

so I tried to, instead of doing a tOracleRow, to do a tLogRow instead so that I could try to display the contents of the variable above.

I don't seem to be able to do this though, as TALEND does not allow me to link a tFlowIterate to a tLogRow....why is this? How else can I get TALEND to display the contents of the above variable, so that I can confirm that my sql statement is being built correctly?

jules · 2009-06-04 14:22:09

I am really struggling to understand this tool.....I can't seem to most components as output to the tflowtoiterate component. I am trying to do the following, in an attempt to display the contents of the variable:

tOracleInput ---- tFlowtoIterate----tLogRow -----------------------NOT ALLOWED!

tOracleInput ---- tFlowtoIterate----tMap-----tLogRow ------------------NOT ALLOWED

It seems like all the functionality that you guys are talking about does not work for me.....am I just being thick or is there something wrong here?

plegall · 2009-06-04 15:34:01

In the tMysqlRow (but that would be exactly the same with a tOracleOutput), the query is:

Code:

'insert into topic6863_tablelist (tablename) values ("'.$row[tablename].'")'

The concatenation symbol is the dot ".", not the "+". You don't need to know "by heart" the syntax of $row[tablename], just use the automatic completion with CTRL+space and you will get the field list in the current row.

Uploaded Images

jules · 2009-06-04 15:39:16

shew! its working at last! thanks guys

I would still like to know how I could have displayed the values of those global variables though...nothing i did seemed to work and i had to resort to doing an insert into a table to see what the values were...which seems a bit of a bad work around.

in a scenario like that, where you need to see what the values are (i.e. need displays), but you are inside an iterate, how would you display these values? I tried to use tLogRow, but this would not allow me to link the component onto the tflowiterate component. Is this valid? How else could you do it?

vikkari · 2009-06-04 21:01:29

Hi

I'm glad that your job works...

Still I want to tell you that it would be better to use tOraclSP instead.
Analyze command is not recommended anymore, the current wayt to analyze
Oracle objects are procedures in dbms_stats package. There are procedure like
gather_schema_stats, gather_database_stats etc...

Best regards
Kari

jules · 2009-06-05 11:45:47

Hi Kari

I was only doing this as a basic test to work out how to implement generic, dynamic type coding within TOS.

I take your point though, I have not heard of this new way of doing the analyze commands, nor have I heard about the stats packages, so I will definitely do some reading on this - thanks for the info!

plegall · 2009-06-05 18:23:18

jules wrote:
in a scenario like that, where you need to see what the values are (i.e. need displays), but you are inside an iterate, how would you display these values? I tried to use tLogRow, but this would not allow me to link the component onto the tflowiterate component. Is this valid? How else could you do it?

tOracleInput --row--> tLogRow

CherryPie · 2011-08-09 12:59:12

I tried exactly this scenario today and it doesnt work due to the reason that TOS doesnt know about an array or field name $row..

Since this thread is a little bit out of date it might be that there is a new way to handle such "dynamic sql". But i cant figure out what to do...
Any responses would be great!

Thanks in advance

Alex

Announcement

Unanswered posts

#1 2009-06-03 15:32:07

Get no results when running dynamic SQL example

#2 2009-06-03 15:39:28

Re: Get no results when running dynamic SQL example

#3 2009-06-03 15:42:04

Re: Get no results when running dynamic SQL example

#4 2009-06-04 11:20:36

Re: Get no results when running dynamic SQL example

#5 2009-06-04 12:56:00

Re: Get no results when running dynamic SQL example

#6 2009-06-04 13:10:39

Re: Get no results when running dynamic SQL example

#7 2009-06-04 14:12:08

Re: Get no results when running dynamic SQL example

#8 2009-06-04 14:22:09

Re: Get no results when running dynamic SQL example

#9 2009-06-04 15:34:01

Re: Get no results when running dynamic SQL example

Code:

#10 2009-06-04 15:39:16

Re: Get no results when running dynamic SQL example

#11 2009-06-04 21:01:29

Re: Get no results when running dynamic SQL example

#12 2009-06-05 11:45:47

Re: Get no results when running dynamic SQL example

#13 2009-06-05 18:23:18

Re: Get no results when running dynamic SQL example

jules wrote:

#14 2011-08-09 12:59:12

Re: Get no results when running dynamic SQL example

Board footer