MySQL Forums
Forum List  »  Quality Assurance

IF NOT EXISTS() THEN
Posted by: matthew Baynham
Date: October 11, 2020 04:47AM

Bug report.

I've written a stored procedure and it crashed but it didn't give any error message.

I've copied my stored procedure below.

I've also copied the code to run my stored procedure.

I've found out that if I comment out the if statements that contain "IF NOT EXISTS () THEN" then my stored procedure will run. And if I just remove the word "NOT" then it'll run.

Maybe the use of the word "NOT" was incorrect, however I would expect an error message.

I now know how to rewrite my stored procedure but I am still annoyed that I wasn't given an error message telling me that the use of the word "not" was wrong.

In fact there was no error message at all, the only thing that happened was that the stored procedure didn't give me any data at the end. It just crashed out without getting to the end. And it didn't say it crashed.

Can we have better error reporting please, it saves so much stress.



------------------------------------------------------------------------------------------
My Stored procedure


use db_settings;

DROP PROCEDURE if exists insertProduct;

DELIMITER //

CREATE PROCEDURE insertProduct(OUT p_bIsOk boolean,
OUT p_prd_product_id int,
IN p_language_id int,
IN p_prd_model varchar(64),
IN p_prd_sku varchar(64),
IN p_prd_upc varchar(12),
IN p_prd_ean varchar(14),
IN p_prd_jan varchar(13),
IN p_prd_isbn varchar(17),
IN p_prd_mpn varchar(64),
IN p_prd_location varchar(128),
IN p_prd_quantity int,
IN p_prd_stock_status_id int,
IN p_prd_image varchar(255),
IN p_prd_manufacturer_id int,
IN p_prd_shipping tinyint,
IN p_prd_price decimal(15,4),
IN p_prd_points int,
IN p_prd_tax_class_id int,
IN p_prd_date_available date,
IN p_prd_weight decimal(15,8),
IN p_prd_weight_class_id int,
IN p_prd_length decimal(15,8),
IN p_prd_width decimal(15,8),
IN p_prd_height decimal(15,8),
IN p_prd_length_class_id int,
IN p_prd_subtract tinyint,
IN p_prd_minimum int,
IN p_prd_sort_order int,
IN p_prd_status tinyint,
IN p_desc_name varchar(255),
IN p_desc_description text,
IN p_desc_tag text,
IN p_desc_meta_title varchar(255) ,
IN p_desc_meta_description varchar(255),
IN p_desc_meta_keyword varchar(255),
IN p_Concat_image varchar(4000),
IN p_Concat_image_sort_order varchar(4000),
IN p_flag_new_language boolean,
IN p_flag_overwrite_product boolean,
IN p_flag_overwrite_description boolean,
IN p_flag_remove_old_images boolean
)
BEGIN

declare sDelimiterTab varchar(10) default "-";
declare iCounter int;
declare iPosA int;
declare iPosB int;
declare sTemp varchar(4000) default "";
declare bIsFinished boolean default false;

set p_bIsOk = true;


/************************************
* Drop and recreate temp tables *
************************************/
DROP TABLE IF EXISTS temp_errors;
DROP TABLE IF EXISTS temp_status;
DROP TABLE IF EXISTS temp_product;
DROP TABLE IF EXISTS temp_product_image;
DROP TABLE IF EXISTS temp_images;

CREATE TEMPORARY TABLE temp_errors (
err_ID INT AUTO_INCREMENT PRIMARY KEY,
err_Category varchar(1024) not null default '',
err_Name varchar(1024) not null default '',
err_Long_Description varchar(1024) not null default '',
err_Values varchar(1024) not null default ''
);

CREATE TEMPORARY TABLE temp_status (
tmpSt_ID INT AUTO_INCREMENT PRIMARY KEY,
tmpSt_Category varchar(1024) not null default '',
tmpSt_Name varchar(1024) not null default '',
tmpSt_Long_Description varchar(1024) not null default '',
tmpSt_Values varchar(1024) not null default ''
);


CREATE TEMPORARY TABLE temp_product (
tmpP_id INT AUTO_INCREMENT PRIMARY KEY,
tmpP_product_id int NOT NULL,
tmpP_model varchar(64) NOT NULL,
tmpP_sku varchar(64) NOT NULL,
tmpP_upc varchar(12) NOT NULL,
tmpP_ean varchar(14) NOT NULL,
tmpP_jan varchar(13) NOT NULL,
tmpP_isbn varchar(17) NOT NULL,
tmpP_mpn varchar(64) NOT NULL,
tmpP_location varchar(128) NOT NULL,
tmpP_quantity int NOT NULL DEFAULT '0',
tmpP_stock_status_id int NOT NULL,
tmpP_image varchar(255) DEFAULT NULL,
tmpP_manufacturer_id int NOT NULL,
tmpP_shipping tinyint NOT NULL DEFAULT '1',
tmpP_price decimal(15,4) NOT NULL DEFAULT '0.0000',
tmpP_points int NOT NULL DEFAULT '0',
tmpP_tax_class_id int NOT NULL,
tmpP_date_available date NOT NULL DEFAULT '2020-01-01',
tmpP_weight decimal(15,8) NOT NULL DEFAULT '0.00000000',
tmpP_weight_class_id int NOT NULL DEFAULT '0',
tmpP_length decimal(15,8) NOT NULL DEFAULT '0.00000000',
tmpP_width decimal(15,8) NOT NULL DEFAULT '0.00000000',
tmpP_height decimal(15,8) NOT NULL DEFAULT '0.00000000',
tmpP_length_class_id int NOT NULL DEFAULT '0',
tmpP_subtract tinyint NOT NULL DEFAULT '1',
tmpP_minimum int NOT NULL DEFAULT '1',
tmpP_sort_order int NOT NULL DEFAULT '0',
tmpP_status tinyint NOT NULL DEFAULT '0',
tmpP_viewed int NOT NULL DEFAULT '0',
tmpP_date_added datetime NOT NULL,
tmpP_date_modified datetime NOT NULL
);

CREATE TEMPORARY TABLE temp_product_image (
tmpI_id INT AUTO_INCREMENT PRIMARY KEY,
tmpI_product_image_id int NOT NULL,
tmpI_product_image_id_Orig int NOT NULL,
tmpI_product_id int NOT NULL,
tmpI_image varchar(255) DEFAULT NULL,
tmpI_sort_order int NOT NULL DEFAULT '0'
);

CREATE TEMPORARY TABLE temp_images (
tmpM_id int AUTO_INCREMENT PRIMARY KEY,
tmpM_image varchar(255) DEFAULT NULL
);

CREATE TEMPORARY TABLE temp_images_sort_order (
tmpS_id int AUTO_INCREMENT PRIMARY KEY,
tmpS_sort_order varchar(255) DEFAULT NULL
);


/****************************************************
* put all images in parameter into a temp table *
****************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "One", "put all images in parameter into a temp table", "");

set iCounter = 1;
set iPosA = 1;
set iPosB = 1;
set sTemp = p_Concat_image;
set bIsFinished = false;

while iCounter < 10 and bIsFinished = false DO
set iPosA = LOCATE(sDelimiterTab, sTemp, iPosB - 1);
set iPosB = LOCATE(sDelimiterTab, sTemp, iPosB);

if iPosA > 0 and iPosB = 0 then
set sTemp = mid(sTemp, iPosA + 1);
set bIsFinished = true;
else
set sTemp = mid(sTemp, iPosA + 1, iPosB - iPosA - 1);
end if;

if sTemp != "" then
insert into temp_images (tmpM_image)
values (LOWER(TRIM(IFNULL(sTemp, ""))));
end if;

set iCounter = iCounter + 1;
set iPosB = iPosB + 1;
end while;

/***************************************************************
* put all images sort order in parameter into a temp table *
***************************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Two", "put all images sort order in parameter into a temp table", "");

set iCounter = 1;
set iPosA = 1;
set iPosB = 1;
set sTemp = p_Concat_image_sort_order;
set bIsFinished = false;

while iCounter < 10 and bIsFinished = false DO
set iPosA = LOCATE(sDelimiterTab, sTemp, iPosB - 1);
set iPosB = LOCATE(sDelimiterTab, sTemp, iPosB);

if iPosA > 0 and iPosB = 0 then
set sTemp = mid(sTemp, iPosA + 1);
set bIsFinished = true;
else
set sTemp = mid(sTemp, iPosA + 1, iPosB - iPosA - 1);
end if;

if sTemp != "" then
insert into temp_images_sort_order (tmpS_sort_order)
values (LOWER(TRIM(IFNULL(sTemp, ""))));
end if;

set iCounter = iCounter + 1;
set iPosB = iPosB + 1;
end while;

/****************************************
* check all sort orders are integer *
****************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Three", "check all sort orders are integer", "");

if not exists (SELECT *
FROM temp_images_sort_order
WHERE tmpS_sort_order LIKE '[0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
OR tmpS_sort_order LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') then
insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
SELECT 'image', 'sort order for images is not a integer (8 digits or less) ', concat('Sort order ', tmpS_sort_order), tmpS_sort_order
FROM temp_images_sort_order
WHERE tmpS_sort_order LIKE "[0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9][0-9][0-9][0-9]"
OR tmpS_sort_order LIKE "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]";
set p_bIsOk = false;
end if;

/***********************************
* check sort orders are unique *
***********************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Four", "check sort orders are unique", "");

if not exists (SELECT tmpM_image, count(*) As Count_of_numbers
FROM temp_images
GROUP BY tmpM_image
HAVING count(*) > 1) then
insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
SELECT 'image', 'images are not unique ', concat('Sort order ', T.tmpM_image, " repeats ", CONVERT(T.Count_of_numbers, CHAR(10)), " times."), T.tmpM_image
FROM (SELECT tmpM_image, count(*) As Count_of_numbers
FROM temp_images
GROUP BY tmpM_image
HAVING count(*) > 1) As T;
set p_bIsOk = false;
end if;

/******************************
* check images are unique *
******************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Five", "check images are unique", "");

if not exists (SELECT tmpS_sort_order, count(*) As Count_of_numbers
FROM temp_images_sort_order
GROUP BY tmpS_sort_order
HAVING count(*) > 1) then
insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
SELECT 'image', 'sort order for images is not unique ', concat('Sort order ', T.tmpS_sort_order, " repeats ", CONVERT(T.Count_of_numbers, CHAR(10)), " times."), T.tmpS_sort_order
FROM (SELECT tmpS_sort_order, count(*) As Count_of_numbers
FROM temp_images_sort_order
GROUP BY tmpS_sort_order
HAVING count(*) > 1) As T;
set p_bIsOk = false;
end if;

/***********************************************************
* check number of images matches number of sort orders *
***********************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Six", "check number of images matches number of sort orders", "");

if EXISTS (select * FROM temp_images) AND EXISTS (SELECT * FROM temp_images_sort_order) THEN
if (select max(tmpM_id) FROM temp_images) = (SELECT MAX(tmpS_id) FROM temp_images_sort_order) THEN
insert into temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
values ('image', 'number of images does not equal number of sort order for those images', '', '');
set p_bIsOk = false;
END IF;
END IF;

/*********************************************************
* check if item is in db_shoes_product from image *
* also check image isn't in shoes_product_image *
* also check name isn't in shoes_product_description *
*********************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Seven", "check if item is in db_shoes_product from image, also check image isnt in shoes_product_image, also check name isnt in shoes_product_description", "");

INSERT INTO temp_product_image (
tmpI_product_image_id_Orig,
tmpI_product_id,
tmpI_image,
tmpI_sort_order)
SELECT product_image_id, product_id, image, sort_order
FROM db_shoes.shoes_product_image
WHERE trim(lower(image)) in (SELECT trim(lower(tmpM_image)) FROM temp_images);

IF EXISTS (SELECT *
FROM db_shoes.shoes_product_description
WHERE language_id = p_language_id
AND product_id in (SELECT tmpI_product_id FROM temp_product_image)
) THEN
INSERT INTO temp_errors (err_Category, err_Name, err_Long_Description, err_Values)
SELECT DISTINCT 'settings', 'Duclicate product description found from picture ', concat('check pictures for product id ', TRIM(CONVERT(tmpI_product_id, CHAR(10)))), TRIM(CONVERT(tmpI_product_id, CHAR(10)))
FROM temp_product_image;
set p_bIsOk = false;
END IF;

/***********************************************************
* If an error has been flagged return error temp table *
* or else return details of language *
***********************************************************/

INSERT INTO temp_status (tmpSt_Category, tmpSt_Name, tmpSt_Long_Description, tmpSt_Values)
VALUES ("Marker", "Eight", "If an error has been flagged return error temp table or else return details of language", "");

if p_bIsOk then
SELECT
tmpSt_ID,
tmpSt_Category,
tmpSt_Name,
tmpSt_Long_Description,
tmpSt_Values
FROM temp_status
ORDER BY tmpSt_ID;
else
select err_ID, err_Category, err_Name, err_Long_Description, err_Values
from temp_errors;
end if;

END//

DELIMITER ;

------------------------------------------------------------------------------------------
Calling Stored procedure

use db_settings;

set @bIsOk = true;
set @product_id = -1;
set @language_id = 1;
set @prd_model = "model";
set @prd_sku = "sku";
set @prd_upc = "upc";
set @prd_ean = "ean";
set @prd_jan = "jan";
set @prd_isbn = "isbn";
set @prd_mpn = "mpn";
set @prd_location = "location";
set @prd_quantity = 0;
set @prd_stock_status_id = 0;
set @prd_image = "image";
set @prd_manufacturer_id = 0;
set @prd_shipping = 0;
set @prd_price = 0.0;
set @prd_points = 0;
set @prd_tax_class_id = 0;
set @prd_date_available = "2020-01-01";
set @prd_weight = 0.0;
set @prd_weight_class_id = 0;
set @prd_length = 0.0;
set @prd_width = 0.0;
set @prd_height = 0.0;
set @prd_length_class_id = 0;
set @prd_subtract = 0;
set @prd_minimum = 0;
set @prd_sort_order = 0;
set @prd_status = 0;
set @desc_name = "name";
set @desc_description = "description";
set @desc_tag = "teg";
set @desc_meta_title = "meta title";
set @desc_meta_description = "meta description";
set @desc_meta_keyword = "meta keyword";
set @Concat_image = "catalog/demo/ipod_nano_5.jpg1\tcatalog/demo/ipod_nano_4.jpg\tcatalog/demo/ipod_nano_123.jpg";
set @Concat_image_sort_order = "1\t2\t3";
set @flag_new_language = false;
set @flag_overwrite_product = true;
set @flag_overwrite_description = true;
set @flag_remove_old_images = false;


CALL insertProduct(@bIsOk,
@product_id,
@language_id,
@prd_model,
@prd_sku,
@prd_upc,
@prd_ean,
@prd_jan,
@prd_isbn,
@prd_mpn,
@prd_location,
@prd_quantity,
@prd_stock_status_id,
@prd_image,
@prd_manufacturer_id,
@prd_shipping,
@prd_price,
@prd_points,
@prd_tax_class_id,
@prd_date_available,
@prd_weight,
@prd_weight_class_id,
@prd_length,
@prd_width,
@prd_height,
@prd_length_class_id,
@prd_subtract,
@prd_minimum,
@prd_sort_order,
@prd_status,
@desc_name,
@desc_description,
@desc_tag,
@desc_meta_title,
@desc_meta_description,
@desc_meta_keyword,
@Concat_image,
@Concat_image_sort_order,
@flag_new_language,
@flag_overwrite_product,
@flag_overwrite_description,
@flag_remove_old_images
);

SELECT @bIsOk As Is_OK, @product_id As Pruduct_ID;

Options: ReplyQuote


Subject
Views
Written By
Posted
IF NOT EXISTS() THEN
604
October 11, 2020 04:47AM
110
October 11, 2020 10:04AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.