Cannot get return value from function

Cannot get return value from function

Postby a2z » Mon 03 Dec 2012 02:41

I am using latest dotConnect for PostgreSQL Express. I have a function:

Code: Select all
CREATE OR REPLACE FUNCTION get_count(p_app_name character varying, p_server_host character varying)
  RETURNS integer AS
$BODY$declare
  v_counter integer;
begin
  select count(*) into v_counter from sessions_info;
  return v_counter;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_count(character varying, character varying)
  OWNER TO session_store;


And I call the function from .Net:

Code: Select all
PgSqlConnection conn = null;
            try
            {
                conn = _OpenDbConn();
                PgSqlCommand cmd = new PgSqlCommand("get_count", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("p_app_name", _appName);
                cmd.Parameters.Add("p_server_host", p_server_host);
                PgSqlParameter pCount = cmd.Parameters.Add("po_count", PgSqlType.Int);
                pCount.Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                return int.Parse(pCount.Value.ToString());
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                    conn.Close();
            }

And I get the error:
Code: Select all
Parameter name is missing.
Parameter name: po_count    at Devart.Data.PostgreSql.PgSqlCommand.a(PgSqlDataReader A_0, PgSqlParameter A_1)
   at Devart.Data.PostgreSql.PgSqlCommand.a(PgSqlDataReader A_0)
   at Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
   at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Devart.Common.DbCommandBase.ExecuteNonQuery()
   at MyClass.GetCount(String p_server_host)
   at OtherClass.GetOnlineCount(Int32 version) in D:\MyDir\DF\Sb.cs:line 826


If I change po_count to Output (in C# and DB) everything is OK. But Why I cannot get it as ReturnValue? I can call the function from console to get return value well.
Please help.
a2z
 
Posts: 6
Joined: Thu 01 Nov 2012 07:43

Re: Cannot get return value from function

Postby Pinturiccio » Wed 05 Dec 2012 15:58

The return value parameters with Direction = ReturnValue commonly behave as described below. If the function returns a single value, the parameter name must be 'return_value'.

So try changing the parameter name from "po_count" to "return_value".
Pinturiccio
Devart Team
 
Posts: 2021
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL