topic Re: Dynamic SQL in Stored proc not working in Talend Studio

Dynamic SQL in Stored proc not working

Anonymous — Tue, 17 May 2016 12:23:35 GMT

Hi,
I am using a stored procedure, that truncates a partitioned table.
And I pass the table name as a parameter to the stored proc. And inside, the stored proc, there is a dynamic sql that truncates the table as per the table name 'in' parameter. The procedure is as follows
DELIMITER $$
CREATE DEFINER=`mpadmin`@`%` PROCEDURE `mysqlproc`(in isactivetoproc int, in tablename varchar(255))
begin
set @sql = concat('alter table ',tablename, ' truncate partition pactive');
if isactivetoproc = 1
then
prepare stmt from @sql;
execute stmt ;
deallocate prepare stmt;
end if;
end$$
DELIMITER ;
I have tested this on mysql and it works fine. But when I call it through talend , it keeps saying there is a syntax error.
However, when I don't use the dynamic sql and use regular SQL, and call through talend it works.
DELIMITER $$
CREATE DEFINER=`mpadmin`@`%` PROCEDURE `mysqlproctest`(in isactivetoproc int, in tablename varchar(255))
begin
alter table farmer truncate partition pactive;
end$$
DELIMITER ;
My intention is to reuse the procedure for all tables.
Any thoughts on why the first proc is not working
Thanks
Rathi

Re: Dynamic SQL in Stored proc not working

Anonymous — Wed, 18 May 2016 04:22:32 GMT

Hi,
Did you use component tMysqlSP to call your stored procedure in MySql DB? Could you please show us your setting screenshot?
Have you already checked this KB article about: TalendHelpCenter:Calling a stored procedure or function?
Best regards
Sabrina

Re: Dynamic SQL in Stored proc not working

Anonymous — Wed, 18 May 2016 09:27:23 GMT

Hi Sabrina,
Yes I am using tmysqlsp and mysql db. I have read the article too.
Please find below the screenshot

Regards
Rathi

Re: Dynamic SQL in Stored proc not working

Anonymous — Wed, 18 May 2016 09:57:43 GMT

Hi,
And could you post your tMySqlSP component setting screenshot, please?
Best regards
Sabrina

Re: Dynamic SQL in Stored proc not working

Anonymous — Wed, 18 May 2016 10:14:13 GMT

Hi Sabrina,
PFA the screenshot

Re: Dynamic SQL in Stored proc not working

Anonymous — Thu, 19 May 2016 09:50:52 GMT

Hi,
Any update on this?
Thanks
Rathi