public virtual MySqlSchemaCollection GetProcedures(string[] restrictions) { MySqlSchemaCollection dt = new MySqlSchemaCollection("Procedures"); dt.AddColumn("SPECIFIC_NAME", typeof(string)); dt.AddColumn("ROUTINE_CATALOG", typeof(string)); dt.AddColumn("ROUTINE_SCHEMA", typeof(string)); dt.AddColumn("ROUTINE_NAME", typeof(string)); dt.AddColumn("ROUTINE_TYPE", typeof(string)); dt.AddColumn("DTD_IDENTIFIER", typeof(string)); dt.AddColumn("ROUTINE_BODY", typeof(string)); dt.AddColumn("ROUTINE_DEFINITION", typeof(string)); dt.AddColumn("EXTERNAL_NAME", typeof(string)); dt.AddColumn("EXTERNAL_LANGUAGE", typeof(string)); dt.AddColumn("PARAMETER_STYLE", typeof(string)); dt.AddColumn("IS_DETERMINISTIC", typeof(string)); dt.AddColumn("SQL_DATA_ACCESS", typeof(string)); dt.AddColumn("SQL_PATH", typeof(string)); dt.AddColumn("SECURITY_TYPE", typeof(string)); dt.AddColumn("CREATED", typeof(DateTime)); dt.AddColumn("LAST_ALTERED", typeof(DateTime)); dt.AddColumn("SQL_MODE", typeof(string)); dt.AddColumn("ROUTINE_COMMENT", typeof(string)); dt.AddColumn("DEFINER", typeof(string)); StringBuilder sql = new StringBuilder("SELECT * FROM mysql.proc WHERE 1=1"); if (restrictions != null) { if (restrictions.Length >= 2 && restrictions[1] != null) { sql.AppendFormat(CultureInfo.InvariantCulture, " AND db LIKE '{0}'", restrictions[1]); } if (restrictions.Length >= 3 && restrictions[2] != null) { sql.AppendFormat(CultureInfo.InvariantCulture, " AND name LIKE '{0}'", restrictions[2]); } if (restrictions.Length >= 4 && restrictions[3] != null) { sql.AppendFormat(CultureInfo.InvariantCulture, " AND type LIKE '{0}'", restrictions[3]); } } MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { MySqlSchemaRow row = dt.AddRow(); row["SPECIFIC_NAME"] = reader.GetString("specific_name"); row["ROUTINE_CATALOG"] = DBNull.Value; row["ROUTINE_SCHEMA"] = reader.GetString("db"); row["ROUTINE_NAME"] = reader.GetString("name"); string routineType = reader.GetString("type"); row["ROUTINE_TYPE"] = routineType; row["DTD_IDENTIFIER"] = StringUtility.ToLowerInvariant(routineType) == "function" ? (object)reader.GetString("returns") : DBNull.Value; row["ROUTINE_BODY"] = "SQL"; row["ROUTINE_DEFINITION"] = reader.GetString("body"); row["EXTERNAL_NAME"] = DBNull.Value; row["EXTERNAL_LANGUAGE"] = DBNull.Value; row["PARAMETER_STYLE"] = "SQL"; row["IS_DETERMINISTIC"] = reader.GetString("is_deterministic"); row["SQL_DATA_ACCESS"] = reader.GetString("sql_data_access"); row["SQL_PATH"] = DBNull.Value; row["SECURITY_TYPE"] = reader.GetString("security_type"); row["CREATED"] = reader.GetDateTime("created"); row["LAST_ALTERED"] = reader.GetDateTime("modified"); row["SQL_MODE"] = reader.GetString("sql_mode"); row["ROUTINE_COMMENT"] = reader.GetString("comment"); row["DEFINER"] = reader.GetString("definer"); } } return(dt); }
/// <summary> /// Executes this instance. /// </summary> /// <returns>The number of statements executed as part of the script.</returns> public int Execute() { bool openedConnection = false; if (connection == null) { throw new InvalidOperationException(Resources.ConnectionNotSet); } if (query == null || query.Length == 0) { return(0); } // next we open up the connetion if it is not already open if (connection.State != ConnectionState.Open) { openedConnection = true; connection.Open(); } // since we don't allow setting of parameters on a script we can // therefore safely allow the use of user variables. no one should be using // this connection while we are using it so we can temporarily tell it // to allow the use of user variables bool allowUserVars = connection.Settings.AllowUserVariables; connection.Settings.AllowUserVariables = true; try { string mode = connection.driver.Property("sql_mode"); mode = StringUtility.ToUpperInvariant(mode); bool ansiQuotes = mode.IndexOf("ANSI_QUOTES") != -1; bool noBackslashEscapes = mode.IndexOf("NO_BACKSLASH_ESCAPES") != -1; // first we break the query up into smaller queries List <ScriptStatement> statements = BreakIntoStatements(ansiQuotes, noBackslashEscapes); int count = 0; MySqlCommand cmd = new MySqlCommand(null, connection); foreach (ScriptStatement statement in statements) { if (String.IsNullOrEmpty(statement.text)) { continue; } cmd.CommandText = statement.text; try { cmd.ExecuteNonQuery(); count++; OnQueryExecuted(statement); } catch (Exception ex) { if (Error == null) { throw; } if (!OnScriptError(ex)) { break; } } } OnScriptCompleted(); return(count); } finally { connection.Settings.AllowUserVariables = allowUserVars; if (openedConnection) { connection.Close(); } } }
/// <summary> /// Parses out the elements of a procedure parameter data type. /// </summary> private string ParseDataType(MySqlSchemaRow row, MySqlTokenizer tokenizer) { StringBuilder dtd = new StringBuilder( StringUtility.ToUpperInvariant(tokenizer.NextToken())); row["DATA_TYPE"] = dtd.ToString(); string type = row["DATA_TYPE"].ToString(); string token = tokenizer.NextToken(); if (token == "(") { token = tokenizer.ReadParenthesis(); dtd.AppendFormat(CultureInfo.InvariantCulture, "{0}", token); if (type != "ENUM" && type != "SET") { ParseDataTypeSize(row, token); } token = tokenizer.NextToken(); } else { dtd.Append(GetDataTypeDefaults(type, row)); } while (token != ")" && token != "," && String.Compare(token, "begin", StringComparison.OrdinalIgnoreCase) != 0 && String.Compare(token, "return", StringComparison.OrdinalIgnoreCase) != 0) { if (String.Compare(token, "CHARACTER", StringComparison.OrdinalIgnoreCase) == 0 || String.Compare(token, "BINARY", StringComparison.OrdinalIgnoreCase) == 0) { } // we don't need to do anything with this else if (String.Compare(token, "SET", StringComparison.OrdinalIgnoreCase) == 0 || String.Compare(token, "CHARSET", StringComparison.OrdinalIgnoreCase) == 0) { row["CHARACTER_SET_NAME"] = tokenizer.NextToken(); } else if (String.Compare(token, "ASCII", StringComparison.OrdinalIgnoreCase) == 0) { row["CHARACTER_SET_NAME"] = "latin1"; } else if (String.Compare(token, "UNICODE", StringComparison.OrdinalIgnoreCase) == 0) { row["CHARACTER_SET_NAME"] = "ucs2"; } else if (String.Compare(token, "COLLATE", StringComparison.OrdinalIgnoreCase) == 0) { row["COLLATION_NAME"] = tokenizer.NextToken(); } else { dtd.AppendFormat(CultureInfo.InvariantCulture, " {0}", token); } token = tokenizer.NextToken(); } if (dtd.Length > 0) { row["DTD_IDENTIFIER"] = dtd.ToString(); } // now default the collation if one wasn't given if (string.IsNullOrEmpty(( string )row["COLLATION_NAME"]) && !string.IsNullOrEmpty(( string )row["CHARACTER_SET_NAME"])) { row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation( row["CHARACTER_SET_NAME"].ToString(), connection); } // now set the octet length if (row["CHARACTER_MAXIMUM_LENGTH"] != null) { if (row["CHARACTER_SET_NAME"] == null) { row["CHARACTER_SET_NAME"] = ""; } row["CHARACTER_OCTET_LENGTH"] = CharSetMap.GetMaxLength(( string )row["CHARACTER_SET_NAME"], connection) * (int)row["CHARACTER_MAXIMUM_LENGTH"]; } return(token); }
internal string GetCommandTextForBatching() { if (batchableCommandText == null) { // if the command starts with insert and is "simple" enough, then // we can use the multi-value form of insert if (String.Compare(CommandText.Substring(0, 6), "INSERT", StringComparison.OrdinalIgnoreCase) == 0) { MySqlCommand cmd = new MySqlCommand("SELECT @@sql_mode", Connection); string sql_mode = StringUtility.ToUpperInvariant(cmd.ExecuteScalar().ToString()); MySqlTokenizer tokenizer = new MySqlTokenizer(CommandText); tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1; tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1; string token = StringUtility.ToLowerInvariant(tokenizer.NextToken()); while (token != null) { if (StringUtility.ToUpperInvariant(token) == "VALUES" && !tokenizer.Quoted) { token = tokenizer.NextToken(); if (token != "(") { throw new Exception(); } // find matching right paren, and ensure that parens // are balanced. int openParenCount = 1; while (token != null) { batchableCommandText += token; token = tokenizer.NextToken(); if (token == "(") { openParenCount++; } else if (token == ")") { openParenCount--; } if (openParenCount == 0) { break; } } if (token != null) { batchableCommandText += token; } token = tokenizer.NextToken(); if (token != null && (token == "," || StringUtility.ToUpperInvariant(token) == "ON")) { batchableCommandText = null; break; } } token = tokenizer.NextToken(); } } // Otherwise use the command verbatim else { batchableCommandText = CommandText; } } return(batchableCommandText); }
private void ParseProcedureBody(MySqlSchemaCollection parametersTable, string body, MySqlSchemaRow row, string nameToRestrict) { List <string> modes = new List <string>(new string[3] { "IN", "OUT", "INOUT" }); string sqlMode = row["SQL_MODE"].ToString(); int pos = 1; MySqlTokenizer tokenizer = new MySqlTokenizer(body); tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1; tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") == -1; tokenizer.ReturnComments = false; string token = tokenizer.NextToken(); // this block will scan for the opening paren while also determining // if this routine is a function. If so, then we need to add a // parameter row for the return parameter since it is ordinal position // 0 and should appear first. while (token != "(") { if (String.Compare(token, "FUNCTION", StringComparison.OrdinalIgnoreCase) == 0 && nameToRestrict == null) { parametersTable.AddRow(); InitParameterRow(row, parametersTable.Rows[0]); } token = tokenizer.NextToken(); } token = tokenizer.NextToken(); // now move to the next token past the ( while (token != ")") { MySqlSchemaRow parmRow = parametersTable.NewRow(); InitParameterRow(row, parmRow); parmRow["ORDINAL_POSITION"] = pos++; // handle mode and name for the parameter string mode = StringUtility.ToUpperInvariant(token); if (!tokenizer.Quoted && modes.Contains(mode)) { parmRow["PARAMETER_MODE"] = mode; token = tokenizer.NextToken(); } if (tokenizer.Quoted) { token = token.Substring(1, token.Length - 2); } parmRow["PARAMETER_NAME"] = token; // now parse data type token = ParseDataType(parmRow, tokenizer); if (token == ",") { token = tokenizer.NextToken(); } // now determine if we should include this row after all // we need to parse it before this check so we are correctly // positioned for the next parameter if (nameToRestrict == null || String.Compare(parmRow["PARAMETER_NAME"].ToString(), nameToRestrict, StringComparison.OrdinalIgnoreCase) == 0) { parametersTable.Rows.Add(parmRow); } } // now parse out the return parameter if there is one. token = StringUtility.ToUpperInvariant(tokenizer.NextToken()); if (String.Compare(token, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0) { MySqlSchemaRow parameterRow = parametersTable.Rows[0]; parameterRow["PARAMETER_NAME"] = "RETURN_VALUE"; ParseDataType(parameterRow, tokenizer); } }