fg4bpexK

1DELIMITER $$
2
3CREATE PROCEDURE `dts_data`.`InsertTemperatureData` (   IN in_serial_number VARCHAR(45),
4                                                        IN z0 INT,
5                                                        IN dz INT,
6                                                        IN in_ts TIMESTAMP,
7                                                        IN csv_string MEDIUMTEXT)
8BEGIN
9    DECLARE measurement_id INT DEFAULT NULL;
10    DECLARE var_number INT DEFAULT 0;
11    
12    DECLARE EXIT HANDLER
13        FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
14            ROLLBACK;
15            
16    START TRANSACTION;    
17
18    -- check if a Measurement record has been created
19    SELECT COUNT(*)
20    INTO var_number
21    FROM Measurement 
22    WHERE serial_number=in_serial_number AND ts=in_ts;
23    IF (var_number=0) THEN
24        SIGNAL SQLSTATE '45000'
25            SET MESSAGE_TEXT = 'A Measurement record does not exist for this serial number and timestamp';     
26    END IF; 
27
28    -- load the csv into a temporary table
29    DROP TEMPORARY TABLE IF EXISTS temp_explode;
30    CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, word VARCHAR(256));
31    SET @sql := CONCAT('INSERT INTO temp_explode (word) VALUES (', REPLACE(QUOTE(pStr), pDelim, '\'), (\''), ')');
32    PREPARE myStmt FROM @sql;
33    EXECUTE myStmt;
34     
35    -- get the relevant measurement_id
36    SELECT idMeasurement 
37    INTO measurement_id
38    FROM Measurement 
39    WHERE serial_number=in_serial_number AND ts=in_ts
40    ORDER by idMeasurement DESC;
41    
42    INSERT INTO Temperature_Data (measurement,distance,temperature) SELECT measurement_id,z0+dz*(id-1), word FROM temp_explode;
43    
44    COMMIT;
45END