[teiid-issues] [JBoss JIRA] (TEIID-5725) Please improve documentation on definition of stored procedures, virtual functions and virtual procedures

[Christoph John (Jira)]

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

Christoph John edited comment on TEIID-5725 at 4/24/19 5:05 PM:
----------------------------------------------------------------

Hello Steven, thanks this helps a lot. However, reading through your description resulted in a further question. I am not sure if I might came over a further issue with it or if I simply have a bug in my procedure.
For my understanding of the description a virtual procedure with side effects should always be mapped to an action. However, the following is my virtual procedure and it is mapped to a function

CREATE VIRTUAL PROCEDURE copyNutritionGoalsOfWeekdayToEntireWeek(vWeekday VARCHAR(1))
	RETURNS BOOLEAN AS
	BEGIN ATOMIC
		IF ( vWeekday IN ('0', '1', '2', '3', '4', '5', '6') )
		BEGIN
			DECLARE LONG vfkProfile = SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36); 

			--##### read the relevant parameters of the selected workday into a temporary table #####
			SELECT 
				BMR_Formula, BMR_Value, ActivityLevel, CaloriesBurned, WeekGoal, GoalCaloriesDelta, 
				CarbsGoalInPercent, ProteinsGoalInPercent, FatGoalInPercent 
			INTO 
				#tmpTable 
			FROM 
				NutritionGoal 
			WHERE 
				fkProfile = vfkProfile AND Weekday = vWeekday;

			--##### update the other weekdays #####
			UPDATE 
				NutritionGoal 
			SET 
				NutritionGoal.BMR_Formula 			= (SELECT BMR_Formula FROM #tmpTable),
				NutritionGoal.BMR_Value 			= (SELECT BMR_Value FROM #tmpTable),
				NutritionGoal.ActivityLevel			= (SELECT ActivityLevel FROM #tmpTable), 
				NutritionGoal.CaloriesBurned		= (SELECT CaloriesBurned FROM #tmpTable), 
				NutritionGoal.WeekGoal				= (SELECT WeekGoal FROM #tmpTable), 
				NutritionGoal.GoalCaloriesDelta		= (SELECT GoalCaloriesDelta FROM #tmpTable), 
				NutritionGoal.CarbsGoalInPercent	= (SELECT CarbsGoalInPercent FROM #tmpTable), 
				NutritionGoal.ProteinsGoalInPercent	= (SELECT ProteinsGoalInPercent FROM #tmpTable), 
				NutritionGoal.FatGoalInPercent		= (SELECT FatGoalInPercent FROM #tmpTable)  
			WHERE 
				NutritionGoal.fkProfile = vfkProfile AND NutritionGoal.Weekday != vWeekday;

			RETURN TRUE;
		END
		RETURN FALSE;
	END

By the way, if you see how I could write this function more efficiently, I would be grateful for a comment :) The function parameter is an enum in the underlaying db. But enum seems to be not supported in teiid.

According to your description:
The procedure/function is side effect free - this is determined by the inferred or explicit value of 0 for the model update count

So do I need to add options to my procedure than. I do not understand than why there is a virtual function and virtual procedure specification. I would have expected to get a function for a virtual function and a action for a virtual procedure.

Maybe you could also add the options to an example in the documentation to make it transparent? Would this than be OPTIONS(UPDATECOUNT '1'); ?


was (Author: cjohn001):
Hello Steven, thanks this helps a lot. However, reading through your description resulted in a further question. I am not sure if I might came over a further issue with it or if I simply have a bug in my procedure.
For my understanding of the description a virtual procedure with side effects should always be mapped to an action. However, the following is my virtual procedure and it is mapped to a function

CREATE VIRTUAL PROCEDURE copyNutritionGoalsOfWeekdayToEntireWeek(vWeekday VARCHAR(1))
	RETURNS BOOLEAN AS
	BEGIN ATOMIC
		IF ( vWeekday IN ('0', '1', '2', '3', '4', '5', '6') )
		BEGIN
			DECLARE LONG vfkProfile = SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36); 

			--##### read the relevant parameters of the selected workday into a temporary table #####
			SELECT 
				BMR_Formula, BMR_Value, ActivityLevel, CaloriesBurned, WeekGoal, GoalCaloriesDelta, 
				CarbsGoalInPercent, ProteinsGoalInPercent, FatGoalInPercent 
			INTO 
				#tmpTable 
			FROM 
				NutritionGoal 
			WHERE 
				fkProfile = vfkProfile AND Weekday = vWeekday;

			--##### update the other weekdays #####
			UPDATE 
				NutritionGoal 
			SET 
				NutritionGoal.BMR_Formula 			= (SELECT BMR_Formula FROM #tmpTable),
				NutritionGoal.BMR_Value 			= (SELECT BMR_Value FROM #tmpTable),
				NutritionGoal.ActivityLevel			= (SELECT ActivityLevel FROM #tmpTable), 
				NutritionGoal.CaloriesBurned		= (SELECT CaloriesBurned FROM #tmpTable), 
				NutritionGoal.WeekGoal				= (SELECT WeekGoal FROM #tmpTable), 
				NutritionGoal.GoalCaloriesDelta		= (SELECT GoalCaloriesDelta FROM #tmpTable), 
				NutritionGoal.CarbsGoalInPercent	= (SELECT CarbsGoalInPercent FROM #tmpTable), 
				NutritionGoal.ProteinsGoalInPercent	= (SELECT ProteinsGoalInPercent FROM #tmpTable), 
				NutritionGoal.FatGoalInPercent		= (SELECT FatGoalInPercent FROM #tmpTable)  
			WHERE 
				NutritionGoal.fkProfile = vfkProfile AND NutritionGoal.Weekday != vWeekday;

			RETURN TRUE;
		END
		RETURN FALSE;
	END

By the way, if you see how I could write this function more efficiently, I would be grateful for a comment :)

According to your description:
The procedure/function is side effect free - this is determined by the inferred or explicit value of 0 for the model update count

So do I need to add options to my procedure than. I do not understand than why there is a virtual function and virtual procedure specification. I would have expected to get a function for a virtual function and a action for a virtual procedure.

Maybe you could also add the options to an example in the documentation to make it transparent? Would this than be OPTIONS(UPDATECOUNT '1'); ?

> Please improve documentation on definition of stored procedures, virtual functions and virtual procedures
> ---------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5725
>                 URL: https://issues.jboss.org/browse/TEIID-5725
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Documentation
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Critical
>
> I refer to the discussion with Ramesh in 
> https://developer.jboss.org/message/989048#989048 and would like to ask for a more detailed explanation on how to write virtual procedures and functions. 
> -Especially, how are OUT and INOUT params set and how can they be obtained via odata. 
> - How is the return value of a virtual function set?
> - How are collections and arrays of collections returned?



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