Help with Multiple Statements and Returning Recordsets

[Anonymous]

Hi,
Relatively new to talend so please bear with me. I am trying to bring some queries into the fold for automation, however they utilize temp tables and I am having issues.
Working with Talend 5.1. My database is Greenplum.
Here is a sample fake query of what I am trying to do - this is a pointless query, but it uses the concepts I am trying to use.

drop table if exists foo_temp;
create temp table foo_temp as
select * from mydb.users;
select * from foo_temp;

I put this in a tGreenplumInput Object with a simple output to tLogRow - the query runs, but it refuses to return a recordset. Same results with tGreenplumRow as well. When I run this in any other client (toad, OBDC, etc), I receive recordsets in return.
Why won't this return records and what do I need to do to get records returned?
Any help appreciated.
Thanks

[Anonymous]

each component can only have one sql statement. You have three sql statements so you need three components.
( tGreenplumRow ) -> ( tGreenplumRow ) -> ( tGreenplumInput -> tlogRow )
Also, because you are using a temp table your components must share the same database connections. So the full jobs would look like:
tGreenplumConnect
( tGreenplumRow ) -> ( tGreenplumRow ) -> ( tGreenplumInput -> tlogRow )
tGreenplumClose
and be sure to use select the checkbox "use an existing database connection" to pick up the tGreenplumConnect connection.

[Anonymous]

That seems like a lot of hoops to jump through for something like this, and it would have to be a separate talend job for every single time its done.
Currently I have a setup that pulls the SQL code in from a database and then runs. It loops through every record in the database, runs the code, sends a CSV to specified recipients, etc for daily reporting.
Not supporting standard syntax seems a rather large oversight for something that purports to do so much... surely there must be some way to get it to work? I would really be a hassle to have to create a new talend job for each and every multi-statement query.

[Anonymous]

If you supply more information of what data processing problem you are doing I may be able to suggest a different design. If the solution does not require a temp table then it will fit more naturally with the Talend way. I almost never use temp tables from a Talend job, in part because it is a bit clumsy as you rightly call out.
If you wish to stick to sql script style of processing then I suggest putting your scripts in a stored proc / database function. You can call this from Talend if you like.

[Anonymous]

I guess it just seems like a massive oversight to me to not be able to run entire blocks of code and retrieve the results. talend is literally the first software i've used that (apparently) can't do it.
As for a different design, it might be possible to just use sub-queries instead, but at great cost of efficiency and code clarity.
Stored procedures are not an option - we are creating daily reports, and only temp table write access is allowed. Additionally, if we stored every report as a procedure, we would have thousands of procedures.
Unfortunately I can't provide more detail without violating federal HIPAA laws and/or company policy.

[Anonymous]

I would not recommend Talend as a reporting tool. Talend for Data Integration is an ETL tool. I have led a number of ETL tool ( and reporting tool ) selection processes and have not encountered another ETL tool that supports running sql scripts as a single block of text. I would be interested to hear if you know of an ETL tool that does this.
If you must use Talend for reporting then it will be difficult if you cannot define database objects. Talend is great at creating a reporting database.
If you are really in a corner and have to use Talend for reporting and can never create database objects then I would create a reporting database as a set of files. Then you can create "temp" files however you like. This will be difficult to manage if you have any complexity, but may work better than your current approach.

[Anonymous]

Do you have any suggestions for other software which can pull in data from varied data sources, merge that data together to create reports, and then send those reports via email?
The primary reason we are using talend is because of its support for dissimilar database connections in the same job process to generate reports.
Clearly, talends only purpose is not ETL, or there would not be such a massive library of support for things such as exporting to a multitude of flat files, sending emails, etc.

[Anonymous]

Your request can be solved by the Talend Exchange component tSQLScriptParser:
http://www.talendforge.org/exchange/index.php?eid=724&product=tos&action=view&nav=0,1,1
This component parses a script and the single statements can be executed by a t<DB>Row component.
One of the problem you will have is the select * because talend could not know how to match the unknown fields to a schema of the output flow. In the enterprise edition there is a solution via dynamix schema but not in the open source edition.
One problem still remains, the t<DB>Row component unfortunately does not provde the information if there is a resultset or now, though you have to filter the statements for starts with select. For select iterating to a t<DB>Input component and for all other to the t<DB>Row component.
In the if trigger use for the first:

"query".equals(((String)globalMap.get("tSQLScriptParser_1_STATEMENT_TYPE"))) == false

and in the second:

"query".equals(((String)globalMap.get("tSQLScriptParser_1_STATEMENT_TYPE")))

[Anonymous]

Thanks - that's probably the closest I've seen to a solution! As for the *, that is just a placeholder/dummy - my actual query(s) don't use wildcards in selects.