
February 24th, 2012, 04:40 AM
|
|
Registered User
|
|
Join Date: Feb 2012
Posts: 1
Time spent in forums: 1 h 9 m 44 sec
Reputation Power: 0
|
|
|
C# Problem ODBC output parameter from postgresql funtion
Hi
How i get the output parameter from the function,
is always nothing in console.
What is wrong?
Here in c#
string connstring = "DSN=PostgreSQL35W";
string procedure = "{CALL f1001.fn_check_insert_mission02(?,?)}";
OdbcConnection connection = new OdbcConnection(connstring);
OdbcCommand cmd = new OdbcCommand(procedure, connection);
cmd.CommandType = CommandType.StoredProcedure;
OdbcParameter themissionNameParam = new OdbcParameter( "@p_mission_name", OdbcType.NVarChar, 256 );
themissionNameParam.Value = "mission1";
themissionNameParam.Direction = ParameterDirection.Input;
cmd.Parameters.Add( themissionNameParam );
OdbcParameter themissionIdParam = new OdbcParameter( "@p_mission_id", OdbcType.Int);
themissionIdParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add( themissionIdParam );
// Add the output parameter.
// Execute the command.
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
Console.WriteLine("New Mission has ID of " + themissionIdParam.Value);
Console.WriteLine();
Console.ReadKey();
here is the function
CREATE OR REPLACE FUNCTION f1001.fn_check_insert_mission02
(
IN p_mission_name varchar,
OUT p_mission_id integer
)
RETURNS integer AS
$$
DECLARE
v_mission_id int;
BEGIN
select mission_id into v_mission_id from f1001.mission where mission_name = p_mission_name;
if not found then
insert into f1001.mission (mission_name) values (p_mission_name) returning mission_id into v_mission_id;
end if;
p_mission_id = v_mission_id;
END;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
|