topic Re: Run postgresql stored procedure in Talend Studio

Run postgresql stored procedure

Anonymous — Tue, 17 Sep 2013 18:21:54 GMT

Hi guys!
Could you please help me to figure out if it's possible or not to run a postgresql stored procedure within a Talend Job? And if yes I'll be very grateful for an example.
Thank you a lot in advance!!!

Re: Run postgresql stored procedure

Anonymous — Wed, 18 Sep 2013 03:52:31 GMT

Hi,
Usually, we use tXXXSP component to call stored procedure.
See the related KB article TalendHelpCenter:Calling a stored procedure or function.
So far, there is no available tpostgresqlSP official component in Talend product.
You can have a try on component tPostgresqlRow, if it can't meet your needs, please open a jira bug of DI project on Talend Bug Tracker for a new feature(tpostgresqlSP).
In addition that, I have searched a talend exchange and found a custom component tPostgresqlSP.
Best regards
Sabrina

Re: Run postgresql stored procedure

Anonymous — Wed, 18 Sep 2013 09:04:38 GMT

Hi Sabrina,
Thank you a lot for the response! I also thought about tPostgresqlRow component but I didn't see the custom SP component for PostgreSQL.
Kind regards,
Mariia

Re: Run postgresql stored procedure

Anonymous — Wed, 18 Sep 2013 11:26:42 GMT

Hi,
Click the link I given you and you will find the custom component tPostgresqlSP, but the revision is compatible with: 2.3.1.
Best regards
Sabrina

Re: Run postgresql stored procedure

Anonymous — Wed, 18 Sep 2013 13:15:57 GMT

Yes, already done, thank you!!
I'll try it and will update this post if it works with 5.2
Kind regards,
Mariia

Re: Run postgresql stored procedure

Anonymous — Wed, 18 Sep 2013 17:19:09 GMT

Unfortunately as I'm using Talend DI 5.2 I didn't manage to integrate mentioned custom component in my JOB. Instead I'm using tPostgresqlRow calling needed stored procedure within it.
Thank you a lot, Sabrina, for helping me
Sincerely,
Mariia

Re: Run postgresql stored procedure

Anonymous — Sun, 22 Sep 2013 10:26:44 GMT

Hi,
Thanks for your feedback, feel free post your issue on forum.
Best regards
Sabrina

Re: Run postgresql stored procedure

Anonymous — Mon, 16 Dec 2013 05:37:55 GMT

being a beginner............how to execute a sp in postgresql. i am not familiar with talend. as i am seeing the post postgresqlrow is good enough for procedure..if so can anyone tell me how to use this one.

Re: Run postgresql stored procedure

Anonymous — Mon, 16 Dec 2013 06:06:23 GMT

Hi,
So far, there is no tPostgresqlSP in talend official components. You have to manage to integrate custom component http://www.talendforge.org/exchange/index.php?eid=41&product=tos&action=view&nav=1,1,1 so that it can compatible with your current talend build version.
Or take the solution by @Mariia_Kliuchnikova: using tPostgresqlRow calling needed stored procedure within it.
Best regards
Sabrina

Re: Run postgresql stored procedure

Anonymous — Mon, 16 Dec 2013 07:40:30 GMT

I am able to call the function by using the following query
" select my_test ( "+ ((Integer)globalMap.get("PropRow.in_var")) + ");"
Still i am not sure what is globalMap and what is PropRow. Any document regarding these is going to be really helpful for me.
Thanks,
Debashis

Re: Run postgresql stored procedure

Anonymous — Mon, 16 Dec 2013 08:04:40 GMT

Hi,
globalMap is a hashtable which has been declared in Java code.
globalMap.put("key","value"), globalMap.get("key","value").
Best regards
Sabrina

Re: Run postgresql stored procedure

Anonymous — Mon, 16 Dec 2013 08:18:09 GMT

thanks really appreciate your support ....
what is PropRow then? and what are the other alternatives of proprow?? if any??
Thanks,
Debashis

Re: Run postgresql stored procedure

Anonymous — Mon, 16 Dec 2013 08:21:06 GMT

Hi,
Would you mind giving us your current job design screenshot into forum so that I can get your situation more precisely to see if there is any other alternative solution for your case.
Best regards
Sabrina

Re: Run postgresql stored procedure

Anonymous — Mon, 16 Dec 2013 11:26:02 GMT

Here i am attaching the screenshot for my job.
In the component of tpostgresql1 i have mentioned the function query as i have discussed with you earlier.
what is PropRow then? and what are the other alternatives of proprow?? if any??

Re: Run postgresql stored procedure

Anonymous — Tue, 17 Dec 2013 03:52:29 GMT

Hi,
It seems the screenshots are missing.
Please click on "post reply" (not through the quick post") and pay more attention on the limitation of Image Upload Box:20 images per post, each image must be less then 1024x768 pixels and 200 KB(.png is preferred).
Best regards
Sabrina

Re: Run postgresql stored procedure

Anonymous — Tue, 17 Dec 2013 12:25:32 GMT

hi Sabrina
I am able to complete the job with the requirement.
Now in a second job i want to update one table like
update pricing_group set pricing_group_type='DEBASHIS' where ver_num=10
as you see i am updating the field with all capital letters, now i want to use the system routine
StringHandling.DOWNCASE to make it to lower case.
in my job i am only using a table metadata that's it. How to use the routine for this purpose.

Re: Run postgresql stored procedure

Anonymous — Wed, 18 Dec 2013 12:28:07 GMT

Hi sabrina
please treat this one as urgent.
I want to be my database as it is before 10 hours. Is it possible from talend to do so and if it is possible then how to do it.

Re: Run postgresql stored procedure

_AnonymousUser — Thu, 07 Aug 2014 12:40:26 GMT

Hi people,
I am new to this and i was making a update procedure, But I am constantly getting syntax error for the set line, and dont know what is wrong. This is the code:
CREATE FUNCTION update_sba_sales("$IncrementID" numeric, "$EntryID" numeric, "$Soa_selectorID" character varying) RETURNS void AS
$BODY$BEGIN
update sba_sales
set sales_gc= sales_gc*$IncrementID
where entryid=$EntryID
and soa=$Soa_selectorID;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
ALTER FUNCTION public.update_sba_sales(numeric, numeric, character varying)
OWNER TO mstrwh;
GRANT EXECUTE ON FUNCTION public.update_sba_sales(numeric, numeric, character varying) TO public;

Re: Run postgresql stored procedure

Anonymous — Thu, 07 Aug 2014 12:56:41 GMT

You are getting error on Pgsql client or in Talend job?
Vaibhav

Re: Run postgresql stored procedure

Anonymous — Thu, 03 Sep 2015 20:36:25 GMT

Hello guys!
Could you please help me to figure out if it's possible or not to create stored procedure within a Talend Job? and i am using MySQL server as back end ,in my job i used tsqlscriptparser component is iterated to tmysqlrow component and i fail to create stored procedure in MySQL DB,please help me, I'll be very grateful for an example.
Thank you a lot in advance!!!