[teiid-issues] [JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value

Mon Jul 15 17:44:00 EDT 2019

    [ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13759345#comment-13759345 ] 

Christoph John edited comment on TEIID-5782 at 7/15/19 5:43 PM:
----------------------------------------------------------------

Hello Steven,
I am currently running in the next issue with the insert trigger. When trying to set the key value, I run into the following error:
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context

Have you an idea what is going wrong here? Could it be related to the compound primary key that I am using? Let me know if I should provide a docker-compose example. The code:

{noformat}
-- ///////////
CREATE VIEW UserDefinedProducts (
	fkProduct long NOT NULL AUTO_INCREMENT,
	fkProfile long NOT NULL,
	idCode long,
	product_name string(48) NOT NULL,
	origins string(64),
	brands string(64) NOT NULL,
	quantity string(64),
	serving_quantity double,
	nova_group char(1),
	nutrition_grade_fr char(1),
	energy_100g double NOT NULL,
	carbohydrates_100g double NOT NULL,
	sugars_100g double NOT NULL,
	proteins_100g double NOT NULL,
	fat_100g double NOT NULL,
	saturated_fat_100g double NOT NULL,
	saturated_fat_modifier char(1),
	salt_100g double NOT NULL,
	salt_modifier char(1),
	fiber_100g double,
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
	CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
	CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
	CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
	CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
	CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
	udp.fkProduct as fkProduct,
	udp.fkProfile as fkProfile,
	udp.idCode as idCode,
	udp.product_name as product_name,
	udp.origins as origins,
	udp.brands as brands,
	udp.quantity as quantity,
	udp.serving_quantity as serving_quantity,
	udp.nova_group as nova_group,
	udp.nutrition_grade_fr as nutrition_grade_fr,
	udp.energy_100g as energy_100g,
	udp.carbohydrates_100g as carbohydrates_100g,
	udp.sugars_100g as sugars_100g,
	udp.proteins_100g as proteins_100g,
	udp.fat_100g as fat_100g,
	udp.saturated_fat_100g as saturated_fat_100g,
	udp.saturated_fat_modifier as saturated_fat_modifier,
	udp.salt_100g as salt_100g,
	udp.salt_modifier as salt_modifier,
	udp.fiber_100g as fiber_100g 
FROM UserDefinedProducts_SRC udp;

CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
	FOR EACH ROW 
	BEGIN ATOMIC
   		DECLARE long vIdProduct;
 		DECLARE integer vNumRecords;
 		-- if a product code was given, check that the user has not already created a product for this code
 		-- this situation should actualy never appear
 		IF (new.idCode IS NOT NULL)
		BEGIN
			vNumRecords = 	SELECT 1 FROM UserDefinedProducts_SRC WHERE 
								fkProfile = new.fkProfile AND idCode = new.idCode
							LIMIT 1;
			IF(vNumRecords > 0)
			BEGIN
				DECLARE EXCEPTION e = SQLEXCEPTION 'User defined product already exists' SQLSTATE '45000';
				RAISE e;
			END
 		END
 		-- check if the user has already reached his MAX amount of UserDefinedProducts;
 		vNumRecords = SELECT COUNT(*) FROM UserDefinedProducts_SRC WHERE fkProfile = new.fkProfile;
 		IF(vNumRecords >= 1000)
		BEGIN
			DECLARE EXCEPTION e = SQLEXCEPTION 'MAX amount of user defined products exceeded for this account' SQLSTATE '45000';
			RAISE e;
		END
 		-- if all checks have been passed we can safely add the product to the database
		INSERT INTO 
			Products(fkDatabaseKey)
		VALUES
			(0);
		-- create a new record for the user defined product
		vIdProduct = cast(generated_key('idProduct') as long);

		INSERT INTO 
			UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity, 
									serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g, 
									sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier, 
									salt_100g, salt_modifier, fiber_100g)
		VALUES
			(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, 
			new.serving_quantity, new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, 
			new.sugars_100g, new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, 
			new.salt_100g, new.salt_modifier, new.fiber_100g);

		-- supply the key value
		key.fkProduct = vIdProduct;		
	END;
{noformat}

was (Author: cjohn001):
Hello Steven,
I am currently running in the next issue with the insert trigger. When trying to set the key value, I run into the following error:
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context

Have you an idea what is going wrong here? Could it be related to the compound primary key that I am using? The code:

{noformat}
-- ///////////
CREATE VIEW UserDefinedProducts (
	fkProduct long NOT NULL AUTO_INCREMENT,
	fkProfile long NOT NULL,
	idCode long,
	product_name string(48) NOT NULL,
	origins string(64),
	brands string(64) NOT NULL,
	quantity string(64),
	serving_quantity double,
	nova_group char(1),
	nutrition_grade_fr char(1),
	energy_100g double NOT NULL,
	carbohydrates_100g double NOT NULL,
	sugars_100g double NOT NULL,
	proteins_100g double NOT NULL,
	fat_100g double NOT NULL,
	saturated_fat_100g double NOT NULL,
	saturated_fat_modifier char(1),
	salt_100g double NOT NULL,
	salt_modifier char(1),
	fiber_100g double,
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
	CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
	CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
	CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
	CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
	CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
	udp.fkProduct as fkProduct,
	udp.fkProfile as fkProfile,
	udp.idCode as idCode,
	udp.product_name as product_name,
	udp.origins as origins,
	udp.brands as brands,
	udp.quantity as quantity,
	udp.serving_quantity as serving_quantity,
	udp.nova_group as nova_group,
	udp.nutrition_grade_fr as nutrition_grade_fr,
	udp.energy_100g as energy_100g,
	udp.carbohydrates_100g as carbohydrates_100g,
	udp.sugars_100g as sugars_100g,
	udp.proteins_100g as proteins_100g,
	udp.fat_100g as fat_100g,
	udp.saturated_fat_100g as saturated_fat_100g,
	udp.saturated_fat_modifier as saturated_fat_modifier,
	udp.salt_100g as salt_100g,
	udp.salt_modifier as salt_modifier,
	udp.fiber_100g as fiber_100g 
FROM UserDefinedProducts_SRC udp;

CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
	FOR EACH ROW 
	BEGIN ATOMIC
   		DECLARE long vIdProduct;
 		DECLARE integer vNumRecords;
 		-- if a product code was given, check that the user has not already created a product for this code
 		-- this situation should actualy never appear
 		IF (new.idCode IS NOT NULL)
		BEGIN
			vNumRecords = 	SELECT 1 FROM UserDefinedProducts_SRC WHERE 
								fkProfile = new.fkProfile AND idCode = new.idCode
							LIMIT 1;
			IF(vNumRecords > 0)
			BEGIN
				DECLARE EXCEPTION e = SQLEXCEPTION 'User defined product already exists' SQLSTATE '45000';
				RAISE e;
			END
 		END
 		-- check if the user has already reached his MAX amount of UserDefinedProducts;
 		vNumRecords = SELECT COUNT(*) FROM UserDefinedProducts_SRC WHERE fkProfile = new.fkProfile;
 		IF(vNumRecords >= 1000)
		BEGIN
			DECLARE EXCEPTION e = SQLEXCEPTION 'MAX amount of user defined products exceeded for this account' SQLSTATE '45000';
			RAISE e;
		END
 		-- if all checks have been passed we can safely add the product to the database
		INSERT INTO 
			Products(fkDatabaseKey)
		VALUES
			(0);
		-- create a new record for the user defined product
		vIdProduct = cast(generated_key('idProduct') as long);

		INSERT INTO 
			UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity, 
									serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g, 
									sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier, 
									salt_100g, salt_modifier, fiber_100g)
		VALUES
			(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, 
			new.serving_quantity, new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, 
			new.sugars_100g, new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, 
			new.salt_100g, new.salt_modifier, new.fiber_100g);

		-- supply the key value
		key.fkProduct = vIdProduct;		
	END;
{noformat}

> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5782
>                 URL: https://issues.jboss.org/browse/TEIID-5782
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: OData
>    Affects Versions: 12.3
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.

--
This message was sent by Atlassian Jira
(v7.12.1#712002)