MySQL Forums
Forum List  »  Stored Procedures

Return a calculated data set from a stored procedure
Posted by: Graham Cole
Date: October 25, 2007 03:16PM

Dear all,

I have written a procedure in php to calculate meeting times using unix timestamps, but in order to make it work correctly it needs to query the database at every iteration from the start of the event to the end of the event. It works but can be quite slow when the event ends several days later.

I have transposed the php code into a stored procedure but I am stuggling in getting a resultset out. I have only used stored procedures for simple database functionality, but i need this to return a datagrid.

Here is the stored procedure code:

<-- Start Code -->
DELIMITER $$

DROP PROCEDURE IF EXISTS `meetthebuyer`.`spMeetingMatrix`$$

CREATE PROCEDURE `meetthebuyer`.`spMeetingMatrix`(
spProfileID int(11),
spCompanyID int(11)
)

BEGIN
/* Variable Declaration */
declare currentEvent int(11);
declare divisor int(11);
declare eventStart int(11);
declare eventEnd int(11);
declare oneDay int(11);
declare dayStart int(11);
declare numMeetings int(11);
declare c int(11);
declare overnight int(11);
declare o int(11);

/* Initilise Variables */
set currentEvent = (select setEventID from tblcurrentevent);
set divisor = 1200;
set eventStart = (select unix_timestamp(concat(str_to_date(eventStartDate,"%d/%m/%Y")," ",str_to_date(eventStartTime,"%H:%i"))) from tblEvents where eventID=currentEvent);
set eventEnd = (select unix_timestamp(concat(str_to_date(eventEndDate,"%d/%m/%Y")," ",str_to_date(eventEndTime,"%H:%i"))) from tblEvents where eventID=currentEvent);
set oneDay = (select unix_timestamp(concat(str_to_date(eventStartDate,"%d/%m/%Y")," ",str_to_date(eventEndTime,"%H:%i"))) from tblEvents where eventID=currentEvent)-eventStart;


/* The dayStart Marker used to ensure we start each day at the correct timestamp */
set dayStart = eventStart;

/* Set the initial number of meetings multiplied by the number of seconds in the meeting */
set numMeetings = (oneDay/divisor)*divisor;

-- Get Cell count
set c = numMeetings / divisor;

-- The total number of meetings in a day must be one meeting less than that total number of meetins in the day
if (numMeetings = oneDay) then
set numMeetings = numMeetings - divisor;
end if;


-- Determine the number of seconds in the period between the end of the day - the total number of meetings
set overnight = (86400-numMeetings);

REPEAT
set o = (select delegateID from tblmeetings where eventID=currentEvent and companyID=spCompanyID and profileID=spProfileID and unixTS=eventStart);
insert into tbltmpmeetings(unixTS,delegateCode,profileID) values (eventStart,o,spProfileID);
if (eventStart < (dayStart+numMeetings)) then
-- increment to the next meeting slot
set eventStart = eventStart + divisor;
else
-- the end of the meeting day has been reached add the overnight value

set eventStart = eventStart + overnight;
-- reset the start day value
set dayStart = eventStart;
end if;

UNTIL eventStart<eventEnd
END REPEAT;
select * from tbltmpmeetings where profileID=spProfileID;
END$$

DELIMITER ;
<!-- end code -->

I tried performing multiple inserts into a temporary table and then doing a select statement at the end of the procedure but I only seem to get one row. when I am expecting 18.

Any help would be greatly appreciated.
regards
Graham Cole

Options: ReplyQuote


Subject
Views
Written By
Posted
Return a calculated data set from a stored procedure
1758
October 25, 2007 03:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.