Skip to Content
0
Former Member
Apr 22, 2008 at 03:26 PM

Stored Procedured calls

17 Views

Hi there,

I have the following as a SP in my MySQL database. It is working fine from the CLI of the database. I want to use this SP from XMII and use the results of the CALL to SP.

My SP
DELIMITER |

DROP PROCEDURE IF EXISTS `mach08`.`timediff` |

CREATE PROCEDURE `timediff`(
     myComponent char(20),
     myElement   char(20),
     myState     char(10),
     forState    char(10)
     )
BEGIN
     SELECT x.component, min(x.t1), x.t2, max(x.diff)
	FROM (
	    SELECT e1.duuid AS component, e1.timestamp01 AS t1, e2.timestamp01 AS t2, e2.timestamp01 - e1.timestamp01 AS diff
	    FROM dpwsevents e1
	    JOIN dpwsevents e2      ON e2.duuid = e1.duuid
				   AND e2.sname = e1.sname
				   AND e2.timestamp01 > e1.timestamp01
	    LEFT JOIN dpwsevents e3 ON e3.duuid = e1.duuid
			           AND e3.sname = e2.sname
				   AND e3.timestamp01 > e1.timestamp01
				   AND e3.timestamp01 < e2.timestamp01
				   AND e3.oname = myState
	    WHERE e1.duuid = myComponent
			           AND e1.sname = myElement
			           AND e1.oname = forState
			           AND e2.oname = myState
			           AND e3.id IS NULL
	  ) AS x
	GROUP BY x.t2;
     END
|

DELIMITER ;

The procdure has input parameters and the result set has 4 columns which I would like to use on an IChart.

have anyone did this before..?!

cheers

Domnic