java.lang.NullPointerException when calling a stored procedure via Teiid

[cjohn001]

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_fkProfile" Type="Edm.Int64"/>

<Parameter Name="var_Weekday" Type="Edm.String" MaxLength="1"/>

<Annotation Term="teiid.NAMEINSOURCE">

<String>"srcProc"</String>

</Annotation>

<Annotation Term="teiid.UPDATECOUNT">

<Int>1</Int>

</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

[rareddy]

1) In the virtual procedure, it is not evident how many sources are involved with the update thus Teiid suggests XA, but if you know there is a single data source then you can use regular JDBC connection.

> I would like to develop my stored procedures in the mysql database directly rather than developing them as virtual procedures in Teiid

don't let the documentation be the decision for this, let your usecase define this requirement.

> have serious problems with understanding the Teiid documentation

We need to fix that, give what are issues you are seeing? how can we fix this, give some suggestions?

> Teiid virtual procedure and at the same time the source procedure should not be accessible via odata directly. Is this possible and how?

You can hide the source model, there is a flag called "visibility" on model/schema you can set to false, then all the tables in source model is hidden. anything you want to expose, you create a view in the virtual schema. Obviously, the user information is not delegated by default. The Keycloak auth context is not suitable for delegation to the MySQL anyway as it is by default user/password vs OpenIdConnect.

>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?

No, bound actions are not possible. Bound actions mean we are operating on single row of data, maybe you can design the procedures with data roles and where clauses you kind of get to same kind of functionality.

>I would be very interested in examples which show how I could return entities or entity sets.

Through OData, you only return complex objects currently, with enhancements this could be turned into entity/entityset return but would require some thought and work.

Can you post the error from server log?

[cjohn001]

>> 1) In the virtual procedure, it is not evident how many sources are involved with the update thus Teiid suggests XA, but if you know there is a single data source then you can use regular JDBC connection.

So how can I tell Teiid then that just a single source is involved? Or do you mean, I can just use the  regular JDBC connection and it should work out of the box?

> We need to fix that, give what are issues you are seeing? how can we fix this, give some suggestions?

-Full example procedures would help.

Currently I am not able to implement and call a simple virtual function or procedure neither with nor without parameters which I can call via odata.

- it is unclear to me, how I have to set the returns value, example would help

- it is unclear how to set OUT or INOUT parameters, and how to use them when calling the function via odata protocol

- unclear how to set a return collection

> You can hide the source model, there is a flag called "visibility" on model/schema you can set to false, then all the tables in source model is hidden. anything you want to expose, you create a view in the virtual schema.

-So the flag does not only set the procedure invisible but the entire model?

-Views are always visible?

-What does this invisible actually mean? I do not see the functions and tables in $metadata? If I would know the url of the procedure I could still call it via odata? Hence it is invisible but not inaccessible? I did an export of a stored procedure with Teiid Designer and converted it to ddl. The ddl did not include a GRANT permission for the stored procedure. So I assume it is always callable?

I think before I replicate all tables via views, I further try to get these virtual procedures/functions working!

>>Through OData, you only return complex objects currently, with enhancements this could be turned into entity/entityset return but would require some thought and work.

This means I could transmit an array/multiple complex objects as a procedure return as well? Then this should fit my requirements for the moment.

>>Can you post the error from server log?

The error log was attached to my original message. Couldn't you open it?

And many thanks for your help!

[cjohn001]

Hello Ramesh,

ok, looks like the null pointer exception I was seeing results from my xa datasource configuration which seems to be wrong in some mysterious way.

Update: I have to correct myself. Getting to late Ok, this here is a different example. For the stored procedure I still see a null pointer exception. This example here is using a virtual function executed in Teiid instead. However, different error is observed here. Could you please check if everything is fine with my function. Does the SELECT statement implicitly set the return value? From the documentation I am not realy sure about it.

...

I used now my old jdbc configuration. Moreover, I reduced my test environment to a simple virtual function given in ddl as:

CREATE VIRTUAL FUNCTION virtFunNoParam()

   RETURNS INTEGER AS

   BEGIN

    SELECT COUNT(Weekday) FROM NutritionGoal WHERE fkProfile = '2';

   END

GRANT SELECT,EXECUTE ON PROCEDURE "my_nutri_diary.virtFunNoParam" TO odata;

Unfortunately when calling the function via odata

GET virtFunNoParam() HTTP/1.1 Accept:application/json;odata.metadata=minimal;IEEE754Compatible=true Accept-Language:de Content-Type:application/json;charset=UTF-8;IEEE754Compatible=true

I do not get any response value, the response is simply empty:

--batch_853bb430-c6b1-4f5e-9010-8080701d120e

Content-Type: application/http

Content-Transfer-Encoding: binary

HTTP/1.1 204 No Content

Content-Length: 0

--batch_853bb430-c6b1-4f5e-9010-8080701d120e--

Well I would expect the count value to be returned. So might I have a wrong thinking regarding my function implementation. Is there some kind of return variable required which needs a select into, or some other kind of return statement which is needed? Do you have an idea what is going wrong here?

Update: Sorry, forgotten to mention the log. From the log file it looks like the function is executed.

Thanks for your help.

[rareddy]

> do you mean, I can just use the  regular JDBC connection and it should work out of the box?

In java you can create a jdbc connection using Driver implementation which is typically not capable of doing XA, whereas for XA they have special XA Datasources. By regular I mean non-XA.

> Full example procedures would help.

Log JIRA and I can coordinate with you in coming up with the details of each example for usage on stored procedures.

> -So the flag does not only set the procedure invisible but the entire model?

no entire model is not visible.

>What does this invisible actually mean? I do not see the functions and tables in $metadata? If I would know the url of the procedure I could still call it via odata? Hence it is invisible but not inaccessible?

Hides the metadata, so $metadata will not supply the info, those can be only used internally by other Teiid views/procedures. In OData it is inaccissible, in JDBC it is invisible but accessible, but recently SteveH put in fix to bring both of them to behave similarly.

> The error log was attached to my original message. Couldn't you open it?

oops, did not pay attention to the attachment. I say first make sure you can execute the procedure using any JDBC SQL client to make sure it is correct, then we can see why the exception in OData, either case it we should avoid an exception of such kind. So, that may require another JIRA.

Ramesh..

[cjohn001]

I have added the following two Jira entries:

[TEIID-5726] java.lang.NullPointerException when calling a stored procedure via Teiid - JBoss Issue Tracker

[TEIID-5725] Please improve documentation on definition of stored procedures, virtual functions and virtual procedures -…

>> I say first make sure you can execute the procedure using any JDBC SQL client to make sure it is correct, ...

I can execute the procedure via mysql workbench as expected. So there is nothing wrong with the procedure in the database.

Not sure if you have seen this. Could you please look into my second example. There I am trying to implement a virtual function rather than wrapping a stored procedure on the source database. Would be great if you could have a look at it as well. I am in the meantime not sure anymore if we do not need a third Jira. Thanks for your help.

Christoph