MySQL Stored Procedures, c# and inout parameters

Question

I have a stored procedure that basically looks to see if an ID exists in a cross reference table...If it exists, I want to get it back. If not, i want to create a new one and get it back....here is the stored proc:

BEGIN

declare data_found int default 1;
declare l_id int default -1;

declare continue handler for 1329
  set data_found=0;

Set p_unique_id = -1;

begin

  select unique_id, is_default into p_unique_id, p_is_default  from jmax.ids where alt_number=p_alt_num;
end;

if p_unique_id>0 then
   set p_existed=1;
else
  insert into jmax.ids (alt_number,is_default) VALUES (p_alt_num,p_is_default);
  set p_existed=0;
  select unique_id into p_unique_id from jmax.ids where alt_number=p_alt_num;

end if;

END

I ran it in dforge with a value it should find, and it did set my outparam fine.

When I call it in c# I get an error: You have an invalid Column Ordinal...here is the c#:

DBAccess.DBUtils dbObj =   DBAccess.DBUtils.Instance();
        MySqlDataReader theReader;
        MySqlCommand theCommand = new MySqlCommand("TestInOut", dbObj.GetLocalConnection());

        MySqlParameter p_alt_num, p_is_default, p_unique_id, p_existed;
        theCommand.CommandType = CommandType.StoredProcedure;

        p_alt_num = theCommand.Parameters.Add("p_alt_num", MySqlDbType.VarChar);
        p_alt_num.Value = "12044";  //my text value
        p_is_default = theCommand.Parameters.Add("p_is_default", MySqlDbType.Int32);
        p_unique_id = theCommand.Parameters.Add("p_unique_id", MySqlDbType.Int32);
        p_unique_id.Direction = ParameterDirection.InputOutput;
        p_existed = theCommand.Parameters.Add("p_existed", MySqlDbType.Int32);
        p_existed.Direction = ParameterDirection.InputOutput;

        theReader = theCommand.ExecuteReader();
        theReader.Close();

        Console.WriteLine("unique ID = <" + theReader.GetInt32(1));  //this line blows up

Any suggestions?

Thanks in advance

Could you post definition of your SP including header?

vissi
– vissi

2011-03-02 00:56:27 +00:00
Commented Mar 2, 2011 at 0:56 — vissi
– vissi, Commented Mar 2, 2011 at 0:56
very old but may be( ?) support.microsoft.com/kb/308614

Shoban
– Shoban

2011-03-02 00:57:08 +00:00
Commented Mar 2, 2011 at 0:57 — Shoban
– Shoban, Commented Mar 2, 2011 at 0:57

Joshua · Accepted Answer · 2011-03-02 01:31:56Z

1

ExecuteReader is expecting a table result set from your stored procedure, which it doesn't look like it does (you do a SELECT ... INTO instead of SELECT ... FROM). Try using ExecuteNonQuery and reading the values from the parameters themselves.

// retVal contains the number of records affected by the query. It may return
// the number of rows found, not sure for mysql.
int retVal = theCommand.ExecuteNonQuery();
Console.WriteLine("unique ID = <{0}", p_unique_id.Value);

Also note, you cannot access data from the reader after you close it. In your sample you call theReader.Close(); then try to read it after it's closed.

answered Mar 2, 2011 at 1:31

Joshua

8,2123 gold badges37 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Stored Procedures, c# and inout parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related