Hello everyone, hope you are all doing fine. What I'm attempting to do and have been tearing my hair out over for the last few weeks is trying to update timestamps in my MySQL database when this data gets fed in by an XML data feed. The data comes in as a group of slices. The number of slices per feed could be different so I have to dynamically find a way to take my existing stored procedure and expand on it. The problem I have is the timestamps can be incorrect. I am capturing the time/date that the record gets written to the database in a separate field. What I'm trying to do is:
1. Count the number of records that come in from the raw XML feed.
2. Compare the time difference between two of the record slices.
3. Take the last record of the batch and apply the time/date in the database.
4. Apply that time/date stamp less the difference for the previous records.
Onto my Stored Procedures:
There are two SPs involved, one is named parse_raw and the other get_node_conten t. The SP parse_raw calls get_node_conten t to break the raw XML into individual slices.
I have been able to get the time stamp to be set but it sets on all the slices. The record format is like this:
(Line breaks for clarity, actual record has none)
Any assistance from the GURUs here would be most appreciated, thank you in advance!
1. Count the number of records that come in from the raw XML feed.
2. Compare the time difference between two of the record slices.
3. Take the last record of the batch and apply the time/date in the database.
4. Apply that time/date stamp less the difference for the previous records.
Onto my Stored Procedures:
There are two SPs involved, one is named parse_raw and the other get_node_conten t. The SP parse_raw calls get_node_conten t to break the raw XML into individual slices.
Code:
###################
delimiter ;
DROP PROCEDURE IF EXISTS `parse_raw`;
DELIMITER $$
CREATE PROCEDURE `parse_raw`()
BEGIN
DECLARE x_id BIGINT;
DECLARE xtext text;
DECLARE useless1 text;
DECLARE useless2 text;
DECLARE x_device_string varchar(30);
DECLARE x_device_id BIGINT;
DECLARE slice_text text;
DECLARE time_text text;
DECLARE new_slice_id BIGINT;
DECLARE msmnt_tag VARCHAR(30);
DECLARE value_text text;
DECLARE x_msmnt_type_id BIGINT;
#Loop for all rows in table
main_loop: LOOP
#Get a row, if we can't we're done
SET x_id = -1;
SELECT ewise_raw_data_in.raw_id, ewise_raw_data_in.raw_data_xml FROM ewise_raw_data_in LIMIT 1 INTO x_id, xtext;
IF (x_id = -1) THEN
LEAVE main_loop;
END IF;
CALL get_node_content(xtext, 'energywise', xtext, useless1, useless2);
CALL get_node_content(xtext, 'device', x_device_string, useless1, xtext);
set x_device_id = 0;
SELECT ewise_device.device_id FROM ewise_device WHERE ewise_device.device_string = x_device_string INTO x_device_id;
IF (x_device_id = 0) THEN
INSERT INTO ewise_device VALUES (default, NULL, x_device_string, NULL, NULL, NULL, NULL);
SELECT LAST_INSERT_ID() INTO x_device_id;
END IF;
WHILE length(xtext) > 0 DO
CALL get_node_content(xtext, 'slice', slice_text, useless1, xtext);
IF length(slice_text) >0 THEN
CALL get_node_content(slice_text, 'time', time_text, useless1, slice_text);
INSERT INTO ewise_slice VALUES (default, x_device_id, time_text);
SELECT LAST_INSERT_ID() INTO new_slice_id;
WHILE length(slice_text) > 0 DO
set x_msmnt_type_id = 0;
CALL get_first_node_name(slice_text, msmnt_tag);
CALL get_node_content(slice_text, msmnt_tag, value_text, useless1, slice_text);
IF length(msmnt_tag) >0 THEN
SELECT ewise_msmnt_type.msmnt_type_id FROM ewise_msmnt_type WHERE ewise_msmnt_type.msmnt_type_code=LEFT(msmnt_tag,8) INTO x_msmnt_type_id;
IF (x_msmnt_type_id = 0) THEN
INSERT INTO ewise_msmnt_type VALUES (default, LEFT(msmnt_tag, 8), NULL, NULL);
SELECT LAST_INSERT_ID() INTO x_msmnt_type_id;
END IF;
INSERT INTO ewise_slice_msmnt VALUES (default, new_slice_id, x_msmnt_type_id, value_text);
END IF;
END WHILE;
END IF;
END WHILE;
#Delete the data row
DELETE FROM ewise_raw_data_in WHERE ewise_raw_data_in.raw_id=x_id;
END LOOP main_loop;
END
$$
###################
delimiter ;
DROP PROCEDURE IF EXISTS `get_node_content`;
DELIMITER $$
CREATE PROCEDURE `get_node_content`(IN in_xml text, IN in_node_tag VARCHAR(30), OUT node_contents text, OUT prev_string text, OUT post_string text )
BEGIN
DECLARE node_start int;
DECLARE node_content_start int;
DECLARE node_end int;
DECLARE node_tag_start varchar(30);
DECLARE node_tag_end varchar(30);
set node_tag_start = concat('<',in_node_tag,'>');
set node_tag_end = concat('</',in_node_tag,'>');
set node_start = locate(node_tag_start, in_xml) ;
set node_end = locate(node_tag_end, in_xml);
set node_content_start = node_start + length(node_tag_start);
set node_contents = if(node_start=0,'', if(node_end <= node_content_start, '', substring(in_xml, node_content_start, node_end - node_content_start)));
set prev_string = if(node_contents = '','',substring(in_xml, 1, node_start-1));
set post_string = if(node_contents = '','',substring(in_xml, node_end + length(node_tag_end), length(in_xml)));
END
$$
(Line breaks for clarity, actual record has none)
Code:
<energywise> <device>718270087784420</device> <slice> <time>20000101050438</time> <t1>69.574</t1> <t2>64.287</t2> <c1>0</c1> </slice> <slice> <time>20000101050938</time> <t1>73.737</t1> <t2>63.275</t2> <c1>0</c1> </slice> </energywise>
Comment