Getting return value from postgresql stored procedure

[[email protected]]

Hi
I'm calling stored procedures in postgresql using entity framework.

The stored procedure signature is below:

CREATE OR REPLACE FUNCTION cbill.insert_service_order(p_service_order_id integer, p_product_order_id integer, p_milestone_id integer, p_service_type integer, p_transaction_type_id integer, p_user character varying, p_service_id integer, p_package_id integer, p_invoice_type_id integer,
p_costcentre_id integer, p_service_name character varying, p_contract_term integer)
RETURNS integer AS

If you notice it returns an integer.

I have mapped the model to call this stored procedure on the insert which generated the following in the "Create" method of my controller

[HttpPost]
public ActionResult Create(ServiceOrder serviceorder)
{
if (ModelState.IsValid)
{
db.ServiceOrders.AddObject(serviceorder);
db.SaveChanges();
return RedirectToAction("Index", "ServiceOrderAttribute", new
{
serviceOrderId = serviceorder.Id
});
}


How do i retrieve the returned integer from the function? I have tried serviceorder.Id after adding the object and saving changes, however that returns 0.

Note: I have set storedGenerated to "identity" as i'm generating the entity key value within the stored procedure.

Thanks

[Shalex]

Here is a simple walkthrough:
1. Create database objects

Code: Select all

CREATE TABLE DEPT (
  DEPTNO INT PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);

CREATE OR REPLACE FUNCTION insert_into_dept(p_dname varchar, p_loc varchar, p_deptno OUT integer) RETURNS integer AS $$
        BEGIN
                p_deptno := 1;
                insert into dept values (p_deptno, p_dname, p_loc);
        END;
$$ LANGUAGE plpgsql;

2. Add *.edml (ObjectContext template) into your project with dept table and insert_into_dept function.
3. Open *.edml in Entity Developer designer:
- set the Concealed Function to True for insert_into_dept in Store part of your model in Model Explorer
- remove the p_deptno parameter in Model Explorer > Store > Stored Procedures > insert_into_dept (because it is OUT parameter)
- right click on Dept in designer > Configure Behaviour and set
Behaviour: Insert
----
Stored Procedure: insert_into_dept
the p_dname parameter corresponds to the Dname property
the p_loc parameter corresponds to the Loc property
----
Result Column Bindings:
the p_deptno column in result set corresponds to the Deptno property
----
- save the model
4. Run this code:

Code: Select all

    PostgreEntities ent = new PostgreEntities();
    Dept d = new Dept();
    d.Dname = "a";
    d.Loc = "a";
    ent.AddToDepts(d);
    ent.SaveChanges();
    Console.WriteLine(d.Deptno); // to make sure that the value is returned into d.Deptno

[[email protected]]

works like a charm..

Many thanks!!