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;

SET cur_position = 1;

SET delimiter_length = CHAR_LENGTH('|');

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;

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 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;

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