labtech Database

Parameters

Name	Type	Mode
incoming	text	IN

Definition

BEGIN
DECLARE table_name varchar(50) DEFAULT '';
DECLARE columns_list varchar(2000) DEFAULT '';
DECLARE values_list text DEFAULT '';
DECLARE cur_position INT DEFAULT 1;
DECLARE cur_string VARCHAR(2000);
DECLARE cur_table varchar(50) DEFAULT '';
DECLARE cur_column varchar(50);
DECLARE cur_value VARCHAR(2000);
DECLARE delimiter_length TINYINT UNSIGNED;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
START TRANSACTION;
-- Reset the cursor position to the 1st character, needed for the while loop below.
SET cur_position = 1;
-- This identifies the length of a space so that we can remove them. Links are separated by spaces.
SET delimiter_length = CHAR_LENGTH('|');
/* Begin the while loop. This loop iterates through the pipe, colon delimited input and parses it into insert statements.
Key/value pairs are split by pipes, keys and values are split by colons.
Keys have both the tablename, and the column name.*/
WHILE CHAR_LENGTH(incoming) > 0 AND cur_position > 0 DO
SET cur_position = INSTR(incoming, '|');
	IF cur_position = 0 THEN
	SET cur_string = incoming;
	ELSE
	SET cur_string = LEFT(incoming, cur_position - 1);
	END IF;
/* 
Now that we have the singular key/value pair, split it into keys and value insert statements for each table.
The odd LENGTH statement you see below is to get the negative number of occurrences of the '.' symbol in case it's recurring in the string.
This would happen if a value being inserted had a '.' as well (since this is the separator for the table name.
*/
	IF TRIM(cur_string) != '' THEN
	SET cur_table = SUBSTRING_INDEX(cur_string, '.', 1);
	SET cur_column = SUBSTRING_INDEX(SUBSTRING_INDEX(cur_string, '.', (LENGTH(REPLACE(cur_string, '.', '')) -LENGTH(cur_string))), ':', 1);
	SET cur_value = CONCAT(''', SUBSTRING_INDEX(cur_string, ':', (LENGTH(REPLACE(cur_string, ':', '')) - LENGTH(cur_string))), ''');
	-- If the table name changed, run the insert statement for the previous table.
	If table_name <> cur_table and table_name <> '' THEN
		SELECT CONCAT('REPLACE INTO ', table_name, '(', columns_list, ') VALUES (',  values_list, ');') into @executestatement;
		PREPARE stmtinsert FROM @executestatement;
		EXECUTE stmtinsert;
		SET @executestatement = '';
		SET columns_list = '';
		SET values_list = '';
	END IF;
	SET table_name = cur_table;
	IF columns_list = '' THEN 
		SET columns_list = CONCAT(columns_list, cur_column);
	ELSE
		SET columns_list = CONCAT(columns_list, ',', cur_column);
	END IF;
	IF values_list = '' THEN
		SET values_list = CONCAT(values_list, cur_value);
	ELSE
		SET values_list = CONCAT(values_list, ',', cur_value);
	END IF;
	END IF;
SET incoming = SUBSTRING(incoming, cur_position + delimiter_length);
END WHILE;
-- If there's nothing left, make sure to submit what was just done.
SELECT CONCAT('REPLACE INTO ', table_name, '(', columns_list, ') VALUES (',  values_list, ');') into @executestatement;
PREPARE stmtinsertfinal FROM @executestatement;
EXECUTE stmtinsertfinal;
SET @executestatement = '';
COMMIT;
END

HVDataInsert

Parameters

Definition