开发者

Calling MySql functions remotely fails

When I try to call user defined functions using remote connection it gives me error.

The thing is query without any functions runs fine for remote connection.

So how can I call functions remotely.

Query : select jobcardid,sonno,sonnumber,getSalesOrderCountByStatus('completed',1)as finished,getSalesOrderCountBySta开发者_运维问答tus('pending',sonnumber)as inprocess,getSalesOrderCountByStatus('',1)as total from tblm_jobcard where sonnumber like 'A121';

Function :

CREATE DEFINER=root@localhost FUNCTION getSalesOrderCountByStatus(v_status varchar(12), v_salesorderid integer) RETURNS int(11)
    READS SQL DATA
BEGIN
DECLARE cnt integer(10);
if(length(v_status)>0) then 
select count(1) into cnt from tblm_jobcard where sonno = v_salesorderid and status = v_status;
else
select count(1) into cnt from tblm_jobcard where sonno = v_salesorderid ;
end if;
RETURN cnt;

On remote connection not getting anything just hangs up.


try

DELIMITER $$

CREATE FUNCTION getSalesOrderCountByStatus(v_status VARCHAR(12), v_salesorderid INTEGER) RETURNS INT(11)
BEGIN
DECLARE cnt INTEGER(10);
IF(LENGTH(v_status)>0) THEN 
SELECT COUNT(1) INTO cnt FROM tblm_jobcard WHERE sonno = v_salesorderid AND STATUS = v_status;
ELSE
SELECT COUNT(1) INTO cnt FROM tblm_jobcard WHERE sonno = v_salesorderid ;
END IF;
RETURN cnt;
END$$
DELIMITER ;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜