|
public GetProcedureParameters ( string restrictions, |
||
| restrictions | string | |
| routines | ||
| return | ||
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;
}
