how to expose odata service for input parameter based procedure.

[Former Member]

i have created one procedure in my schema.

CREATE PROCEDURE TEST

(in id varchar) AS

BEGIN

SELECT * FROM "table" WHERE "ID" = :id;

END;

and i am able to call the procedure from console.

CALL "schema"."TEST"('1')

my question is how to expose this procedure as odata service.

is there any way to do it. ???

[former_member194780]

Hi Anand,

You can expose it as an Odata Service only if you wrap it in a Calculation View !!

Once you do that, the definition of Odata service :

service namespace "Test" {

      "<package::CalculationView>" as "CalcView"

      keys generate local "ID"

      aggregates always;

}

Check if this helps too :

Regards,

Avinash Raju

[Former Member]

hello Avinash. this is where i stuck in my code. i dont know how to wrap procedure in calculation view..

My procedure code is below.

create procedure "Get_User_for_Project"(in PROJECT_ID VARCHAR(255), out OUTPUT_TABLE "schema"."test_table")

language SQLSCRIPT sql security definer  as

BEGIN

truncate table "schema"."test_table";

OUTPUT_TABLE = SELECT "ID", "FIRST_NAME", "LAST_NAME" FROM "schema"."U_USER" WHERE "ID" IN (SELECT "USER_ID" FROM "schema"."U_USER_PROJECT" WHERE "PROJECT_ID" = :PROJECT_ID);

END;

my procedure is working fine. i just need help to wrap in calculation view with input parameter.

[former_member194780]

Hi Anand,

You will have to create a Scripted Calculation View from the Modeller perspective.

In your case since you want to wrap a procedure. Below is an example I just tried.

Add Columns & Input Parameters

Consume this Calc View in your Odata service :

service namespace "services.test"

{

"pda.Models::CV_TEST" as "Customer"

keys generate local "ID"

parameters via entity;

}

Check your service by Passing the Input Parameters

http://<host>:<port>/<>/services/test.xsodata/CustomerParameters(2)/Results?$format=json

Hope this was Helpful

Regards

Avinash Raju

[vivekbhoj]

Hi Anand,

Wrap this procedure inside a Calculation View and then create an Odata Service on  top of it

Create an Odata Service as below:

service namespace "test.view"

{"test.view::TestCV" as "CalculationView"

keys generate local "ID"

parameters via entity "InputParams";

}

Then use this InputParams Entity in your UI for using input parameters

You can also check this thread:

Regards,

Vivek

[Former Member]

hello Vivek. this is where i stuck in my code. i dont know how to wrap procedure in calculation view..

My procedure code is below.

create procedure "Get_User_for_Project"(in PROJECT_ID VARCHAR(255), out OUTPUT_TABLE "schema"."test_table")

language SQLSCRIPT sql security definer  as

BEGIN

truncate table "schema"."test_table";

OUTPUT_TABLE = SELECT "ID", "FIRST_NAME", "LAST_NAME" FROM "schema"."U_USER" WHERE "ID" IN (SELECT "USER_ID" FROM "schema"."U_USER_PROJECT" WHERE "PROJECT_ID" = :PROJECT_ID);

END;

can u tell how to write script in calculation view. ???