how to get a stored proc return value in Ent lib provider

how to get a stored proc return value in Ent lib provider

Postby Guest » Tue 23 May 2006 17:05

hello, this is a basic question. We have bought the 2.5 Pro version and I need to call a Postgres function using the Enterprise Library data access block. The function takes only input parameters, and returns a bool, such as:

IF devcount >= 1 THEN
RETURN TRUE;
ELSE
RAISE NOTICE '01';
RETURN FALSE;
END IF;

I can run the function and it workd fine in pgadmin. I've not been able to get a correct return value using ExecuteScalar, but I see this only works if you are doing a SELECT. What would the syntax look like to get a function value returned from a RETURN statement like this ? Thanks.
Guest
 

Postby Alexey » Wed 24 May 2006 07:37

Don't understand what the problem.
Choose CommandType=StoredProc for PgSqlCommand component.
And write the code:
Code: Select all
      pgSqlConnection1.Open();
      pgSqlCommand1.Parameters[0].Value = 1;
      MessageBox.Show(pgSqlCommand1.ExecuteScalar().ToString());

Should return "True".
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Guest » Wed 24 May 2006 22:22

Probably my lack of knowledge writing the plpgsql functions with return values. I ended up explicitly declaring an OUT param and using ExecuteNonQuery and it worked fine.
Guest
 

Postby Alexey » Thu 25 May 2006 06:35

Function declaration might look like the following:
Code: Select all
CREATE OR REPLACE FUNCTION test.devcount(count int4)
  RETURNS bool AS
$BODY$
BEGIN
  IF $1 >= 1 THEN
  RETURN TRUE;
  ELSE
  RAISE NOTICE '01';
  RETURN FALSE;
  END IF;
END;
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test.devcount(count int4) OWNER TO postgres;
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL