java.lang.NullPointerException when calling a stored procedure via Teiid
Hello together,
I am currently trying to get stored procedures via odata working. I am not sure if I am doing something wrong or if I am hitting a bug in TEIID. I ran into one issue as described below and have a couple of questions regarding stored procedures via Teiid Odata in general. I hope you guys can shed some light to the context.
1. The issue:
My stored procedure in a mysql8 database is given as follows:
CREATE DEFINER="root"@"%" PROCEDURE "srcProc"(IN var_fkProfile BIGINT, IN var_Weekday ENUM('0', '1', '2', '3', '4', '5', '6') )
BEGIN
DECLARE var_BMR_Formula TINYINT UNSIGNED;
DECLARE var_BMR_Value SMALLINT UNSIGNED;
DECLARE var_ActivityLevel TINYINT UNSIGNED;
DECLARE var_CaloriesBurned SMALLINT UNSIGNED;
DECLARE var_WeekGoal TINYINT UNSIGNED;
DECLARE var_GoalCaloriesDelta SMALLINT;
DECLARE var_CarbsGoalInPercent TINYINT UNSIGNED;
DECLARE var_ProteinsGoalInPercent TINYINT UNSIGNED;
DECLARE var_FatGoalInPercent TINYINT UNSIGNED;
SELECT
BMR_Formula,
BMR_Value,
ActivityLevel,
CaloriesBurned,
WeekGoal,
GoalCaloriesDelta,
CarbsGoalInPercent,
ProteinsGoalInPercent,
FatGoalInPercent
INTO var_BMR_Formula , var_BMR_Value , var_ActivityLevel , var_CaloriesBurned , var_WeekGoal , var_GoalCaloriesDelta , var_CarbsGoalInPercent , var_ProteinsGoalInPercent , var_FatGoalInPercent FROM
NutritionGoal
WHERE
NutritionGoal.Weekday = var_Weekday;
SELECT
var_BMR_Formula,
var_BMR_Value,
var_ActivityLevel,
var_CaloriesBurned,
var_WeekGoal,
var_GoalCaloriesDelta,
var_CarbsGoalInPercent,
var_ProteinsGoalInPercent,
var_FatGoalInPercent;
UPDATE NutritionGoal
SET
BMR_Formula = var_BMR_Formula,
BMR_Value = var_BMR_Value,
ActivityLevel = var_ActivityLevel,
CaloriesBurned = var_CaloriesBurned,
WeekGoal = var_WeekGoal,
GoalCaloriesDelta = var_GoalCaloriesDelta,
CarbsGoalInPercent = var_CarbsGoalInPercent,
ProteinsGoalInPercent = var_ProteinsGoalInPercent,
FatGoalInPercent = var_FatGoalInPercent
WHERE
fkProfile = var_fkProfile;
END
I export the procedure (srcProc) via the following vdb.ddl
/*
###########################################
# START DATABASE svc
###########################################
*/
CREATE DATABASE svc VERSION '1';
USE DATABASE svc VERSION '1';
--############ Translators ############
CREATE FOREIGN DATA WRAPPER mysql5 OPTIONS(EnableDependentJoins true);
--############ Servers ############
CREATE SERVER my_nutri_diary FOREIGN DATA WRAPPER mysql5 OPTIONS ("jndi-name" 'java:/my_nutri_diary');
--############ Schemas ############
CREATE SCHEMA my_nutri_diary SERVER my_nutri_diary;
--############ Roles ############
CREATE ROLE odata WITH ANY AUTHENTICATED;
--############ Schema:my_nutri_diary ############
SET SCHEMA my_nutri_diary;
--############ Stored Procedures ############
CREATE FOREIGN PROCEDURE srcProc (
IN var_fkProfile long OPTIONS(NATIVE_TYPE 'BIGINT'),
IN var_Weekday string(1) OPTIONS(NATIVE_TYPE 'ENUM')
) OPTIONS(NAMEINSOURCE '"srcProc"', UPDATECOUNT '1');
--############ Tables ############
CREATE FOREIGN TABLE Account (
idProfile long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idProfile"', NATIVE_TYPE 'BIGINT'),
uuidUser string(36) NOT NULL OPTIONS(NAMEINSOURCE '"uuidUser"', NATIVE_TYPE 'CHAR'),
CONSTRAINT "PRIMARY" PRIMARY KEY(idProfile),
CONSTRAINT uuidUser_UNIQUE UNIQUE(uuidUser)
) OPTIONS(NAMEINSOURCE '"Account"', UPDATABLE 'TRUE', CARDINALITY '2');
CREATE FOREIGN TABLE Avatar (
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
AvatarImg blob(65535) OPTIONS(NAMEINSOURCE '"AvatarImg"', NATIVE_TYPE 'BLOB'),
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),
CONSTRAINT fkAvatarToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)
) OPTIONS(NAMEINSOURCE '"Avatar"', UPDATABLE 'TRUE');
CREATE FOREIGN TABLE BodyWeight (
idBodyWeight long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idBodyWeight"', NATIVE_TYPE 'BIGINT', ANNOTATION 'We need a surrogate key here as Teiid requires a primary key on each table. fkProfile is not unique here as we can will have multiple weight measurements per person. Moreover, combining it with WeightMeasurementDateTime makes the index to compute intensive.'),
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
WeightMeasurementDateTime timestamp NOT NULL OPTIONS(NAMEINSOURCE '"WeightMeasurementDateTime"', NATIVE_TYPE 'DATETIME'),
Weight float NOT NULL OPTIONS(NAMEINSOURCE '"Weight"', NATIVE_TYPE 'FLOAT UNSIGNED'),
CONSTRAINT "PRIMARY" PRIMARY KEY(idBodyWeight),
CONSTRAINT fkBodyWeightToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)
) OPTIONS(NAMEINSOURCE '"BodyWeight"', UPDATABLE 'TRUE', CARDINALITY '1');
CREATE FOREIGN TABLE ConfigOptions (
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
MUnitWeight string(2) DEFAULT 'kg' OPTIONS(NAMEINSOURCE '"MUnitWeight"', NATIVE_TYPE 'ENUM'),
MUnitLength string(2) DEFAULT 'cm' OPTIONS(NAMEINSOURCE '"MUnitLength"', NATIVE_TYPE 'ENUM'),
MUnitEnergy string(4) DEFAULT 'kcal' OPTIONS(NAMEINSOURCE '"MUnitEnergy"', NATIVE_TYPE 'ENUM'),
MUnitLiquids string(2) DEFAULT 'ml' OPTIONS(NAMEINSOURCE '"MUnitLiquids"', NATIVE_TYPE 'ENUM'),
MUnitTime string(3) DEFAULT '24h' OPTIONS(NAMEINSOURCE '"MUnitTime"', NATIVE_TYPE 'ENUM'),
EnableDrinkReminder boolean DEFAULT '0' OPTIONS(NAMEINSOURCE '"EnableDrinkReminder"', NATIVE_TYPE 'BIT'),
PreferredCountryCodeForSearch string(2) DEFAULT 'de' OPTIONS(NAMEINSOURCE '"PreferredCountryCodeForSearch"', NATIVE_TYPE 'VARCHAR'),
EnableInternationalSearch boolean DEFAULT '1' OPTIONS(NAMEINSOURCE '"EnableInternationalSearch"', NATIVE_TYPE 'BIT'),
DaySpecificMealConfig boolean DEFAULT '0' OPTIONS(NAMEINSOURCE '"DaySpecificMealConfig"', NATIVE_TYPE 'BIT'),
DaySpecificCalorieConfig boolean DEFAULT '0' OPTIONS(NAMEINSOURCE '"DaySpecificCalorieConfig"', NATIVE_TYPE 'BIT'),
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),
CONSTRAINT fkConfigOptionsToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)
) OPTIONS(NAMEINSOURCE '"ConfigOptions"', UPDATABLE 'TRUE', CARDINALITY '2');
CREATE FOREIGN TABLE Diary (
idDiaryEntry long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idDiaryEntry"', NATIVE_TYPE 'BIGINT'),
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
AddedDateTime timestamp NOT NULL OPTIONS(NAMEINSOURCE '"AddedDateTime"', NATIVE_TYPE 'DATETIME'),
fkIdProductCode long NOT NULL OPTIONS(NAMEINSOURCE '"fkIdProductCode"', NATIVE_TYPE 'BIGINT UNSIGNED'),
DatabaseID short NOT NULL OPTIONS(NAMEINSOURCE '"DatabaseID"', NATIVE_TYPE 'TINYINT UNSIGNED'),
MealNumber string(1) NOT NULL OPTIONS(NAMEINSOURCE '"MealNumber"', NATIVE_TYPE 'ENUM'),
AmountInG double NOT NULL OPTIONS(NAMEINSOURCE '"AmountInG"', NATIVE_TYPE 'DOUBLE'),
CONSTRAINT "PRIMARY" PRIMARY KEY(idDiaryEntry),
CONSTRAINT fkDiaryToFDBProducts FOREIGN KEY(fkIdProductCode) REFERENCES FDBProducts(idCode),
CONSTRAINT fkDiaryToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)
) OPTIONS(NAMEINSOURCE '"Diary"', UPDATABLE 'TRUE', CARDINALITY '5000');
CREATE FOREIGN TABLE FDBProducts (
idCode long NOT NULL OPTIONS(NAMEINSOURCE '"idCode"', NATIVE_TYPE 'BIGINT UNSIGNED'),
lc string(5) OPTIONS(NAMEINSOURCE '"lc"', NATIVE_TYPE 'CHAR'),
product_name string(256) OPTIONS(NAMEINSOURCE '"product_name"', NATIVE_TYPE 'VARCHAR'),
origins string(512) OPTIONS(NAMEINSOURCE '"origins"', NATIVE_TYPE 'VARCHAR'),
brands string(384) OPTIONS(NAMEINSOURCE '"brands"', NATIVE_TYPE 'VARCHAR'),
quantity string(256) OPTIONS(NAMEINSOURCE '"quantity"', NATIVE_TYPE 'VARCHAR'),
nova_group char(1) OPTIONS(NAMEINSOURCE '"nova_group"', NATIVE_TYPE 'CHAR'),
nutrition_grade_fr char(1) OPTIONS(NAMEINSOURCE '"nutrition_grade_fr"', NATIVE_TYPE 'CHAR'),
energy_100g double OPTIONS(NAMEINSOURCE '"energy_100g"', NATIVE_TYPE 'DOUBLE'),
carbohydrates_100g double OPTIONS(NAMEINSOURCE '"carbohydrates_100g"', NATIVE_TYPE 'DOUBLE'),
sugars_100g double OPTIONS(NAMEINSOURCE '"sugars_100g"', NATIVE_TYPE 'DOUBLE'),
proteins_100g double OPTIONS(NAMEINSOURCE '"proteins_100g"', NATIVE_TYPE 'DOUBLE'),
fat_100g double OPTIONS(NAMEINSOURCE '"fat_100g"', NATIVE_TYPE 'DOUBLE'),
saturated_fat_100g double OPTIONS(NAMEINSOURCE '"saturated_fat_100g"', NATIVE_TYPE 'DOUBLE'),
saturated_fat_modifier string(3) OPTIONS(NAMEINSOURCE '"saturated_fat_modifier"', NATIVE_TYPE 'CHAR'),
salt_100g double OPTIONS(NAMEINSOURCE '"salt_100g"', NATIVE_TYPE 'DOUBLE'),
sodium_100g double OPTIONS(NAMEINSOURCE '"sodium_100g"', NATIVE_TYPE 'DOUBLE'),
CONSTRAINT "PRIMARY" PRIMARY KEY(idCode)
) OPTIONS(NAMEINSOURCE '"FDBProducts"', UPDATABLE 'TRUE', CARDINALITY '626285');
CREATE FOREIGN TABLE MealConfigs (
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
Weekday string(1) NOT NULL OPTIONS(NAMEINSOURCE '"Weekday"', NATIVE_TYPE 'ENUM'),
MealNumber string(1) NOT NULL OPTIONS(NAMEINSOURCE '"MealNumber"', NATIVE_TYPE 'ENUM'),
WeekdaySortOrder long NOT NULL OPTIONS(NAMEINSOURCE '"WeekdaySortOrder"', NATIVE_TYPE 'INT UNSIGNED'),
MealName string(45) DEFAULT 'strMeal' OPTIONS(NAMEINSOURCE '"MealName"', NATIVE_TYPE 'VARCHAR'),
MealEnabled boolean OPTIONS(NAMEINSOURCE '"MealEnabled"', NATIVE_TYPE 'BIT'),
MealStartTime time OPTIONS(NAMEINSOURCE '"MealStartTime"', NATIVE_TYPE 'TIME'),
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile, Weekday, MealNumber),
CONSTRAINT fkMealConfigToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)
) OPTIONS(NAMEINSOURCE '"MealConfigs"', UPDATABLE 'TRUE', CARDINALITY '2');
CREATE FOREIGN TABLE NutritionGoal (
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
Weekday string(1) NOT NULL OPTIONS(NAMEINSOURCE '"Weekday"', NATIVE_TYPE 'ENUM'),
BMR_Formula short DEFAULT '2' OPTIONS(NAMEINSOURCE '"BMR_Formula"', NATIVE_TYPE 'TINYINT UNSIGNED'),
BMR_Value integer DEFAULT '0' OPTIONS(NAMEINSOURCE '"BMR_Value"', NATIVE_TYPE 'SMALLINT UNSIGNED'),
ActivityLevel short DEFAULT '2' OPTIONS(NAMEINSOURCE '"ActivityLevel"', NATIVE_TYPE 'TINYINT UNSIGNED'),
CaloriesBurned integer DEFAULT '0' OPTIONS(NAMEINSOURCE '"CaloriesBurned"', NATIVE_TYPE 'SMALLINT UNSIGNED'),
WeekGoal short DEFAULT '4' OPTIONS(NAMEINSOURCE '"WeekGoal"', NATIVE_TYPE 'TINYINT UNSIGNED'),
GoalCaloriesDelta short DEFAULT '0' OPTIONS(NAMEINSOURCE '"GoalCaloriesDelta"', NATIVE_TYPE 'SMALLINT'),
CarbsGoalInPercent short DEFAULT '50' OPTIONS(NAMEINSOURCE '"CarbsGoalInPercent"', NATIVE_TYPE 'TINYINT UNSIGNED'),
ProteinsGoalInPercent short DEFAULT '30' OPTIONS(NAMEINSOURCE '"ProteinsGoalInPercent"', NATIVE_TYPE 'TINYINT UNSIGNED'),
FatGoalInPercent short DEFAULT '20' OPTIONS(NAMEINSOURCE '"FatGoalInPercent"', NATIVE_TYPE 'TINYINT UNSIGNED'),
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile, Weekday),
CONSTRAINT fkNutritionGoalToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)
) OPTIONS(NAMEINSOURCE '"NutritionGoal"', UPDATABLE 'TRUE', CARDINALITY '1');
CREATE FOREIGN TABLE Profile (
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
BodyHeight float DEFAULT '0.000' OPTIONS(NAMEINSOURCE '"BodyHeight"', NATIVE_TYPE 'FLOAT UNSIGNED'),
GoalWeight float DEFAULT '0.000' OPTIONS(NAMEINSOURCE '"GoalWeight"', NATIVE_TYPE 'FLOAT UNSIGNED'),
Gender string(1) DEFAULT 'm' OPTIONS(NAMEINSOURCE '"Gender"', NATIVE_TYPE 'ENUM'),
BirthDate date OPTIONS(NAMEINSOURCE '"BirthDate"', NATIVE_TYPE 'DATE'),
ActivityLevel string(17) DEFAULT 'strActive' OPTIONS(NAMEINSOURCE '"ActivityLevel"', NATIVE_TYPE 'ENUM'),
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),
CONSTRAINT fkProfileToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)
) OPTIONS(NAMEINSOURCE '"Profile"', UPDATABLE 'TRUE', CARDINALITY '2');
--############ Grants ############
GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA my_nutri_diary TO odata;
REVOKE EXECUTE,ALTER ON SCHEMA my_nutri_diary FROM odata;
GRANT ON TABLE "my_nutri_diary.Account" CONDITION 'Account.uuidUser = LEFT(user(), 36)' TO odata;
REVOKE UPDATE,DELETE,EXECUTE ON TABLE "my_nutri_diary.Account" FROM odata;
GRANT ON TABLE "my_nutri_diary.BodyWeight" CONDITION 'BodyWeight.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36) )' TO odata;
GRANT ON TABLE "my_nutri_diary.ConfigOptions" CONDITION 'ConfigOptions.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36))' TO odata;
REVOKE DELETE,EXECUTE ON TABLE "my_nutri_diary.ConfigOptions" FROM odata;
GRANT ON TABLE "my_nutri_diary.Diary" CONDITION 'Diary.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36) )' TO odata;
GRANT SELECT ON TABLE "my_nutri_diary.FDBProducts" TO odata;
REVOKE INSERT,UPDATE,DELETE,EXECUTE ON TABLE "my_nutri_diary.FDBProducts" FROM odata;
GRANT ON TABLE "my_nutri_diary.MealConfigs" CONDITION 'MealConfigs.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36) )' TO odata;
REVOKE DELETE,EXECUTE ON TABLE "my_nutri_diary.MealConfigs" FROM odata;
GRANT ON TABLE "my_nutri_diary.NutritionGoal" CONDITION 'NutritionGoal.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36))' TO odata;
REVOKE DELETE,EXECUTE ON TABLE "my_nutri_diary.NutritionGoal" FROM odata;
GRANT ON TABLE "my_nutri_diary.Profile" CONDITION 'Profile.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36))' TO odata;
REVOKE DELETE,EXECUTE ON TABLE "my_nutri_diary.Profile" FROM odata;
REVOKE SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON SCHEMA sysadmin FROM odata;
/*
###########################################
# END DATABASE svc
###########################################
*/
The procedure becomes visible in odata $metadata as follows:
<Parameter Name="var_Weekday" Type="Edm.String" MaxLength="1"/>
</Annotation>
</Annotation>
When calling it via post message:
--batch_id-1555796125616-30 Content-Type:application/http Content-Transfer-Encoding:binary POST srcProc HTTP/1.1 Accept:application/json;odata.metadata=minimal;IEEE754Compatible=true Accept-Language:de Content-Type:application/json;charset=UTF-8;IEEE754Compatible=true {"var_fkProfile":"2","var_Weekday":"1"} --batch_id-1555796125616-30--
I get the following error:
{"error":{"code":null,"message":"OData Library: An exception without message text was thrown."}}
The Teiid log file is attached.
What I tried so far:
- I red in the docs that when setting (NAMEINSOURCE '"srcProc"', UPDATECOUNT '1');
Hence, when UPDATECOUNT > 0, than Teiid uses a XA Transaction. As my database source was originally a Non-XA source, I change my mysql datasource configuration to an XA-Source. However, the resulting error is exactly the same.
2. General questions:
2.1 My plan is as follows: I would like to develop my stored procedures in the mysql database directly rather than developing them as virtual procedures in Teiid. This is my plan, as I have serious problems with understanding the Teiid documentation on how to write virtual procedures in ddl syntax. Moreover, as I have just a single database in use below Teiid, I expect that this will also be the more efficient approach.
I would like to call such a source procedure than from a virtual procedure which I write as a kind of wrapper in the vdb.ddl. This virtual procedure shall call the source procedure with the user() id as a parameter. The idea behind it is, to allow for access control in the source procedure, because all authorization stuff in my setup is handled via Keycloak and Teiid with Keyclaok/Wildfly plugin. My database does not know the user who is connected to Teiid.
The question: For this approach to make sense, I would need to be able to call the stored source procedure from a Teiid virtual procedure and at the same time the source procedure should not be accessible via odata directly. Is this possible and how?
2.2 When reading through the odata specification, I found that procedures can be mapped to odata bound or unbound Actions and the same dual mapping holds for functions. Bound in this context means the action is bound to an odata entity or entity set, hence acting like a method on the entity.
Unbound Actions seem to be more like a static function which is not associated with any entity. Now my question is, are bound actions and functions supported in Teiid, and how can I write them?
2.3 The Teiid documentation is unfortunately not very detailed on how to write virtual procedures and functions in ddl. If somone has helpful links to sample procedures or a tutorial style introduction except the Teiid documentation, it would be great if he could provide it. I would be very interested in examples which show how I could return entities or entity sets.
Thanks for your help!
Best regards
Christoph
-
log.txt.zip 1.6 KB
