topic Re: tpostgresqlRow and return values? in Talend Studio

tpostgresqlRow and return values?

_AnonymousUser — Wed, 10 Sep 2014 15:02:19 GMT

Hello.
I'm trying to figure out how to catch a return value from of a
PostgreSQL function using tPostgresqlRow component. My job
is roughly:

tFileList_1 --(iterate1)-> tFileInputDelimited_1 --(row1)-> tMap_1 --(row2)-> tPostgresqlOutput_1

so pretty much the definition of ETL, eh?
Anyway, after (each iteration) I have loaded the data to a table,
I should call a PostgreSQL function to process the loaded data:

CREATE OR REPLACE FUNCTION flush_the_bin(customer character varying) RETURNS integer AS
--...
RETURN some_code;

In SQL I would:

SELECT flush_the_bin('Customer Inc.');

I assume the call to the function is done with tPostgresqlRow that
is connected to tFileList_1 with another iterate arrow. But how
do I catch the return value from the called function?
JB

Re: tpostgresqlRow and return values?

Anonymous — Wed, 10 Sep 2014 15:51:30 GMT

You could call this procedure indeed with a tPostgresqlInput component this way. Because of the select you will get an ResultSet and thats what the input component depends on. The schema in this case has only one column (name does not matter) and the data type Integer.

SELECT flush_the_bin('" + ((String) globalMap.get("tFileList_1_CURRENT_FILE")) + "')

Please avoid the ; at the end, this will cause problems in PostgreSQL. This example provides the file name of one file read be tFileList. You can easily adapt that for any other source of parameters.

Re: tpostgresqlRow and return values?

Anonymous — Thu, 11 Sep 2014 05:17:35 GMT

Hi,
If you use t<DB>Row to execute a select query, you have to use a tParseRecordSet after t<DB>Row to parse the recordset.

Best regards
Sabrina

Re: tpostgresqlRow and return values?

Anonymous — Thu, 11 Sep 2014 07:28:07 GMT

My suggested new tPostgresqlInput component should be triggered in your case starting from tPostgresqlOutput_1 and with OnComponentOk.
I guess there might be a problem because as long as I know you cannot do any insert/updates within a select. We should think about this if it the case. At the moment I see only an ugly solution with tJDBCSP.

Re: tpostgresqlRow and return values?

_AnonymousUser — Fri, 12 Sep 2014 09:41:52 GMT

You could call this procedure indeed with a tPostgresqlInput component this way.

Thank you for the tip. I implemented it with tPostgresqlInput indeed.

My suggested new tPostgresqlInput component should be triggered in your case starting from tPostgresqlOutput_1 and with OnComponentOk.

I attached the tPostgresqlinput to tFileList with an iterate arrow and
that seemed to work also.

tFileList_1 --(iterate1)-> tFileInputDelimited_1 --(row1)-> tMap_1 --(row2)-> tPostgresqlOutput_1
     \
      --------(iterate2)-> tPostgresqlInput_1 --(row2)-> tMap2 --(row3)-> ...

I guess there might be a problem because as long as I know you cannot do any insert/updates within a select.

That isn't a problem.
Thank you again.
JB

Re: tpostgresqlRow and return values?

Anonymous — Mon, 24 Jun 2019 07:07:57 GMT

I use t<DB>Row to execute a select query, but after use a tParseRecordSet with t<DB>Row to parse the recordset.but how to retrieve column data from tParseRecordSet data i am getting error of column not found