2y7rGWGB

1CREATE DEFINER=`root`@`localhost` FUNCTION `strSplit`(textIn longtext, delim varchar(12), count int) RETURNS int(11)
2BEGIN
3declare splitString INT(11);
4
5SET splitString = replace(substring(substring_index(textIn, delim, count), length(substring_index(textIn, delim, count - 1)) + 1), delim, '');
6
7RETURN splitString;
8END
9	
10CREATE TABLE IF NOT EXISTS `myguide`.`objectlocation` (
11`ObjectLocationId` INT(11) NOT NULL AUTO_INCREMENT,
12`ValueObject` LONGTEXT NOT NULL,
13`DescriptorSize` INT(11) NOT NULL,
14`DescriptionObject` VARCHAR(45) NOT NULL,
15`DataInsert` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
16`InsertBy` VARCHAR(45) NULL DEFAULT NULL,
17PRIMARY KEY (`ObjectLocationId`))
18ENGINE = InnoDB
19AUTO_INCREMENT = 2
20DEFAULT CHARACTER SET = utf8
21	
22CREATE DEFINER=`root`@`localhost` PROCEDURE `myguide_sp_info_imageId`(descriptorIn longtext, sizeDescriptor INT)
23BEGIN
24
25 declare sizeImagesTable INT DEFAULT (select count(*) from objectLocation);
26 declare descriptorSizeImage INT;
27 declare descriptor INT;
28 declare sizeDescriptorImage INT DEFAULT sizeDescriptor;
29 declare contorInsertImage INT default 1;
30 declare descriptorForSplit longtext;
31 declare descriptorImageSaved longtext;
32 declare descriptorForSplitImageSaved longtext;
33
34/* check if table exist, drop*/
35DROP TEMPORARY TABLE IF EXISTS backupObjectLocation;
36/* Create temporar table for store info about objectLocation*/
37CREATE TEMPORARY TABLE backupObjectLocation (
38    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
39    idImage int NOT NULL,
40    descriptorSaved longtext not null,
41    sizeDescriptorSaved float not null
42    );
43
44/* check if table exist, drop*/
45DROP TEMPORARY TABLE IF EXISTS processImage;
46/* Create temporar table for store info about objectLocation*/
47CREATE TEMPORARY TABLE processImage (
48    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
49    descriptorSaved varchar(255) ,
50    descriptorReceived varchar(255)
51    );
52
53SET descriptorImageSaved = RTRIM(descriptorIn);
54SET descriptorForSplit = REPLACE(descriptorImageSaved, ';', ',');
55
56INSERT INTO backupObjectLocation (idImage, descriptorSaved, sizeDescriptorSaved)
57SELECT ObjectLocationId, ValueObject, DescriptorSize FROM objectLocation;
58
59loop_insertDescriptorImage: LOOP
60
61    if contorInsertImage > sizeDescriptorImage then
62        leave loop_insertDescriptorImage;
63    end if;
64
65    SET descriptor = strSplit(descriptorForSplit, ',', contorInsertImage);
66
67    INSERT INTO processImage (descriptorReceived) VALUES (descriptor);
68
69    SET contorInsertImage = contorInsertImage + 1;
70
71    ITERATE loop_insertDescriptorImage;
72end LOOP;
73
74loop_table: LOOP
75
76    if sizeImagesTable > 1 then
77        leave loop_table;
78    end if;
79
80    SET descriptorSizeImage = (SELECT sizeDescriptorSaved from backupObjectLocation where id = sizeImagesTable);
81
82    loop_image: LOOP
83
84        if descriptorSizeImage > 1 then
85            leave loop_image;
86        end if;
87
88        SET descriptorImageSaved = (SELECT descriptorSaved from backupObjectLocation where id = sizeImagesTable);
89        SET descriptorForSplitImageSaved = REPLACE(descriptorImageSaved, ';', ',');
90
91        SET descriptorSizeImage = descriptorSizeImage + 1;
92
93        ITERATE loop_image;
94
95    end LOOP;
96
97    SET sizeImagesTable = sizeImagesTable + 1;
98
99    ITERATE loop_table;
100end LOOP;
101
102select descriptorImageSaved;
103select * from backupObjectLocation;
104select * from processImage;
105END