MySQL ODBC stored procedure MySQL result missing columns

Question

I have a set of stored procedures that I use to populate ASP.NET CheckBoxList. When executing this procedure from code in the form {CALL ProcedureName (params); } with the type set as the stored procedure, I only return a partial result (that is, many columns from the actual result are missing.)

If I copy a CommandText from the query (using a breakpoint to get the exact text) and run it directly in Navicat (or any other MySQL GUI), I get all the expected columns.

Here is the code that does not work:

using (OdbcCommand command = OdbcConnection.CreateCommand()) { command.CommandType = CommandType.StoredProcedure; command.CommandText = "{ " + string.Format(StoredProcedureCall, foundationId, fpids, "", "", "NULL", "2001/01/02", "2001/01/01", "*") + " }"; using (OdbcDataReader reader = command.ExecuteReader()) { for (int i = 0; i < reader.FieldCount; i++) { columns.Add(reader.GetName(i)); } } } 

If I changed the code to the following, however, it starts working (just adding another):

 using (OdbcConnection) using (OdbcCommand command = OdbcConnection.CreateCommand()) { command.CommandType = CommandType.StoredProcedure; command.CommandText = "{ " + string.Format(StoredProcedureCall, foundationId, fpids, "", "", "NULL", "2001/01/02", "2001/01/01", "*") + " }"; using (OdbcDataReader reader = command.ExecuteReader()) { for (int i = 0; i < reader.FieldCount; i++) { columns.Add(reader.GetName(i)); } } } 

What's going on here?

For reference: OdbcConnection property:

 public static OdbcConnection OdbcConnection { get { // If we have no connection or our connection isn't open if (null == odbcConnection || ConnectionState.Open != odbcConnection.State) { odbcConnection = new OdbcConnection(BaseAccess.DBConnectionString); odbcConnection.Open(); } return odbcConnection; } } 

Answer 1

This was somehow related to reconnections, and some of the @ variables in the stored procedures were not reset between runs. Going to merged connections that are closed / deleted correctly is what fixed my problem.

Answer 2

This may be a bug in the Odbc MySql driver. Try using the ADO.NET driver . In addition, I would advise you not to access connections manually inside static properties. Leave this task in ADO.NET, which will efficiently handle the connection pool:

 using (var conn = new MySqlConnection(DBConnectionString)) using (var cmd = conn.CreateCommand()) { conn.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_name"; cmd.Parameters.Add(new SqlParameter("@foundationId", foundationId)); cmd.Parameters.Add(new SqlParameter("@fpids", fpids)); ... using (var reader = cmd.ExecuteReader()) { for (int i = 0; i < reader.FieldCount; i++) { columns.Add(reader.GetName(i)); } } } 

Answer 3

make sure your columns have unique names.