private DataSet GetParameters(string procName) { // if we can use mysql.proc, then do so if (Connection.Settings.UseProcedureBodies) return Connection.ProcedureCache.GetProcedure(Connection, procName); // we can't use mysql.proc so we attempt to "make do" DataSet ds = new DataSet(); string[] restrictions = new string[4]; int dotIndex = procName.IndexOf('.'); restrictions[1] = procName.Substring(0, dotIndex++); restrictions[2] = procName.Substring(dotIndex, procName.Length - dotIndex); ds.Tables.Add(Connection.GetSchema("procedures", restrictions)); // we use an internal method to create our procedure parameters table. We pass // in a non-null routines table and this will prevent the code from attempting // a show create. It will process zero routine records but will return an empty // parameters table we can then fill. DataTable zeroRoutines = new DataTable(); ISSchemaProvider sp = new ISSchemaProvider(Connection); DataTable pTable = sp.GetProcedureParameters(null, zeroRoutines); pTable.TableName = "procedure parameters"; ds.Tables.Add(pTable); // now we run through the parameters that were set and fill in the parameters table // the best we can int pos = 1; foreach (MySqlParameter p in command.Parameters) { // in this mode, all parameters must have their type set if (!p.TypeHasBeenSet) throw new InvalidOperationException(Resources.NoBodiesAndTypeNotSet); DataRow row = pTable.NewRow(); row["PARAMETER_NAME"] = p.ParameterName; row["PARAMETER_MODE"] = "IN"; if (p.Direction == ParameterDirection.InputOutput) row["PARAMETER_MODE"] = "INOUT"; else if (p.Direction == ParameterDirection.Output) row["PARAMETER_MODE"] = "OUT"; else if (p.Direction == ParameterDirection.ReturnValue) { row["PARAMETER_MODE"] = "OUT"; row["ORDINAL_POSITION"] = 0; } else row["ORDINAL_POSITION"] = pos++; pTable.Rows.Add(row); } return ds; }
private static ProcedureCacheEntry GetProcData(MySqlConnection connection, string spName) { string schema = String.Empty; string name = spName; int dotIndex = spName.IndexOf("."); if (dotIndex != -1) { schema = spName.Substring(0, dotIndex); name = spName.Substring(dotIndex + 1, spName.Length - dotIndex - 1); } string[] restrictions = new string[4]; restrictions[1] = schema.Length > 0 ? schema : connection.CurrentDatabase(); restrictions[2] = name; MySqlSchemaCollection proc = connection.GetSchemaCollection("procedures", restrictions); if (proc.Rows.Count > 1) { throw new MySqlException(Resources.ProcAndFuncSameName); } if (proc.Rows.Count == 0) { throw new MySqlException(String.Format(Resources.InvalidProcName, name, schema)); } ProcedureCacheEntry entry = new ProcedureCacheEntry(); entry.procedure = proc; // we don't use GetSchema here because that would cause another // query of procedures and we don't need that since we already // know the procedure we care about. ISSchemaProvider isp = new ISSchemaProvider(connection); string[] rest = isp.CleanRestrictions(restrictions); MySqlSchemaCollection parameters = isp.GetProcedureParameters(rest, proc); entry.parameters = parameters; return(entry); }
private static DataSet GetProcData(MySqlConnection connection, string spName) { var schema = String.Empty; var name = spName; var dotIndex = spName.IndexOf("."); if (dotIndex != -1) { schema = spName.Substring(0, dotIndex); name = spName.Substring(dotIndex + 1, spName.Length - dotIndex - 1); } var restrictions = new string[4]; restrictions[1] = schema.Length > 0 ? schema : connection.CurrentDatabase(); restrictions[2] = name; var procTable = connection.GetSchema("procedures", restrictions); if (procTable.Rows.Count > 1) { throw new MySqlException(Resources.ProcAndFuncSameName); } if (procTable.Rows.Count == 0) { throw new MySqlException(String.Format(Resources.InvalidProcName, name, schema)); } var ds = new DataSet(); ds.Tables.Add(procTable); // we don't use GetSchema here because that would cause another // query of procedures and we don't need that since we already // know the procedure we care about. var isp = new ISSchemaProvider(connection); var rest = isp.CleanRestrictions(restrictions); var parametersTable = isp.GetProcedureParameters(rest, procTable); ds.Tables.Add(parametersTable); return(ds); }
private DataSet GetParameters(string procName) { // if we can use mysql.proc, then do so if (Connection.Settings.UseProcedureBodies) { return(Connection.ProcedureCache.GetProcedure(Connection, procName)); } // we can't use mysql.proc so we attempt to "make do" DataSet ds = new DataSet(); string[] restrictions = new string[4]; int dotIndex = procName.IndexOf('.'); restrictions[1] = procName.Substring(0, dotIndex++); restrictions[2] = procName.Substring(dotIndex, procName.Length - dotIndex); ds.Tables.Add(Connection.GetSchema("procedures", restrictions)); // we use an internal method to create our procedure parameters table. We pass // in a non-null routines table and this will prevent the code from attempting // a show create. It will process zero routine records but will return an empty // parameters table we can then fill. DataTable zeroRoutines = new DataTable(); ISSchemaProvider sp = new ISSchemaProvider(Connection); DataTable pTable = sp.GetProcedureParameters(null, zeroRoutines); pTable.TableName = "procedure parameters"; ds.Tables.Add(pTable); // now we run through the parameters that were set and fill in the parameters table // the best we can int pos = 1; foreach (MySqlParameter p in command.Parameters) { // in this mode, all parameters must have their type set if (!p.TypeHasBeenSet) { throw new InvalidOperationException(Resources.NoBodiesAndTypeNotSet); } DataRow row = pTable.NewRow(); row["PARAMETER_NAME"] = p.ParameterName; row["PARAMETER_MODE"] = "IN"; if (p.Direction == ParameterDirection.InputOutput) { row["PARAMETER_MODE"] = "INOUT"; } else if (p.Direction == ParameterDirection.Output) { row["PARAMETER_MODE"] = "OUT"; } else if (p.Direction == ParameterDirection.ReturnValue) { row["PARAMETER_MODE"] = "OUT"; row["ORDINAL_POSITION"] = 0; } else { row["ORDINAL_POSITION"] = pos++; } pTable.Rows.Add(row); } return(ds); }
private static DataSet GetProcData(MySqlConnection connection, string spName) { string schema = String.Empty; string name = spName; int dotIndex = spName.IndexOf("."); if (dotIndex != -1) { schema = spName.Substring(0, dotIndex); name = spName.Substring(dotIndex + 1, spName.Length - dotIndex - 1); } string[] restrictions = new string[4]; restrictions[1] = schema.Length > 0 ? schema : connection.CurrentDatabase(); restrictions[2] = name; DataTable procTable = connection.GetSchema("procedures", restrictions); if (procTable.Rows.Count > 1) throw new MySqlException(Resources.ProcAndFuncSameName); if (procTable.Rows.Count == 0) throw new MySqlException(String.Format(Resources.InvalidProcName, name, schema)); DataSet ds = new DataSet(); ds.Tables.Add(procTable); // we don't use GetSchema here because that would cause another // query of procedures and we don't need that since we already // know the procedure we care about. ISSchemaProvider isp = new ISSchemaProvider(connection); string[] rest = isp.CleanRestrictions(restrictions); try { DataTable parametersTable = isp.GetProcedureParameters(rest, procTable); ds.Tables.Add(parametersTable); } catch (Exception) { } return ds; }
public void GetProcedureParametersDoesNotRequireSelectFromMySqlProceduresTable() { if (Version < new Version(5, 5, 3)) return; suExecSQL(String.Format("GRANT ALL ON `{0}`.* to 'simpleuser' identified by 'simpleuser'", database0)); execSQL("DROP PROCEDURE IF EXISTS spTest"); execSQL(@"CREATE PROCEDURE spTest(id INT, name VARCHAR(20)) BEGIN SELECT name; END"); string connStr = GetConnectionString("simpleuser", "simpleuser", true) + ";use procedure bodies=false"; using (MySqlConnection c = new MySqlConnection(connStr)) { c.Open(); string[] restrictions = new string[4]; restrictions[1] = c.Database; restrictions[2] = "spTest"; DataTable procTable = c.GetSchema("procedures", restrictions); ISSchemaProvider isp = new ISSchemaProvider(c); string[] rest = isp.CleanRestrictions(restrictions); MySqlSchemaCollection parametersTable = isp.GetProcedureParameters(rest, new MySqlSchemaCollection( procTable )); Assert.IsNotNull(parametersTable); } }
private static ProcedureCacheEntry GetProcData(MySqlConnection connection, string spName) { string schema = String.Empty; string name = spName; int dotIndex = spName.IndexOf("."); if (dotIndex != -1) { schema = spName.Substring(0, dotIndex); name = spName.Substring(dotIndex + 1, spName.Length - dotIndex - 1); } string[] restrictions = new string[4]; restrictions[1] = schema.Length > 0 ? schema : connection.CurrentDatabase(); restrictions[2] = name; MySqlSchemaCollection proc = connection.GetSchemaCollection("procedures", restrictions); if (proc.Rows.Count > 1) throw new MySqlException(Resources.ProcAndFuncSameName); if (proc.Rows.Count == 0) throw new MySqlException(String.Format(Resources.InvalidProcName, name, schema)); ProcedureCacheEntry entry = new ProcedureCacheEntry(); entry.procedure = proc; // we don't use GetSchema here because that would cause another // query of procedures and we don't need that since we already // know the procedure we care about. ISSchemaProvider isp = new ISSchemaProvider(connection); string[] rest = isp.CleanRestrictions(restrictions); MySqlSchemaCollection parameters = isp.GetProcedureParameters(rest, proc); entry.parameters = parameters; return entry; }