/// <inheritdoc/> public static MySqlDbType NameToType(string typeName, bool unsigned, MySqlConnection connection) { switch (StringUtility.ToUpperInvariant(typeName)) { case "CHAR": return(MySqlDbType.String); case "VARCHAR": return(MySqlDbType.VarChar); case "DATE": return(MySqlDbType.Date); case "DATETIME": return(MySqlDbType.DateTime); case "NUMERIC": case "DECIMAL": case "DEC": case "FIXED": return(MySqlDbType.NewDecimal); case "YEAR": return(MySqlDbType.Year); case "TIME": return(MySqlDbType.Time); case "TIMESTAMP": return(MySqlDbType.Timestamp); case "SET": return(MySqlDbType.Set); case "ENUM": return(MySqlDbType.Enum); case "BIT": return(MySqlDbType.Bit); case "TINYINT": return(unsigned ? MySqlDbType.UByte : MySqlDbType.Byte); case "BOOL": case "BOOLEAN": return(MySqlDbType.Byte); case "SMALLINT": return(unsigned ? MySqlDbType.UInt16 : MySqlDbType.Int16); case "MEDIUMINT": return(unsigned ? MySqlDbType.UInt24 : MySqlDbType.Int24); case "INT": case "INTEGER": return(unsigned ? MySqlDbType.UInt32 : MySqlDbType.Int32); case "SERIAL": return(MySqlDbType.UInt64); case "BIGINT": return(unsigned ? MySqlDbType.UInt64 : MySqlDbType.Int64); case "FLOAT": return(MySqlDbType.Float); case "DOUBLE": return(MySqlDbType.Double); case "REAL": return(MySqlDbType.Double); case "TEXT": return(MySqlDbType.Text); case "BLOB": return(MySqlDbType.Blob); case "LONGBLOB": return(MySqlDbType.LongBlob); case "LONGTEXT": return(MySqlDbType.LongText); case "MEDIUMBLOB": return(MySqlDbType.MediumBlob); case "MEDIUMTEXT": return(MySqlDbType.MediumText); case "TINYBLOB": return(MySqlDbType.TinyBlob); case "TINYTEXT": return(MySqlDbType.TinyText); case "BINARY": return(MySqlDbType.Binary); case "VARBINARY": return(MySqlDbType.VarBinary); } throw new Exception("Unhandled type encountered"); }
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(); Debug.Assert(token == "("); // 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); }
/// <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 (IsNullOrEmpty(Query)) { 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.Where(statement => !IsNullOrEmpty(statement.text))) { 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(); } } }
public int Execute() { bool flag = false; if (this.connection == null) { throw new InvalidOperationException(Resources.ConnectionNotSet); } if (this.query == null || this.query.Length == 0) { return(0); } if (this.connection.State != ConnectionState.Open) { flag = true; this.connection.Open(); } bool allowUserVariables = this.connection.Settings.AllowUserVariables; this.connection.Settings.AllowUserVariables = true; int result; try { string text = this.connection.driver.Property("sql_mode"); text = StringUtility.ToUpperInvariant(text); bool ansiQuotes = text.IndexOf("ANSI_QUOTES") != -1; bool noBackslashEscapes = text.IndexOf("NO_BACKSLASH_ESCAPES") != -1; List <ScriptStatement> list = this.BreakIntoStatements(ansiQuotes, noBackslashEscapes); int num = 0; MySqlCommand mySqlCommand = new MySqlCommand(null, this.connection); foreach (ScriptStatement current in list) { if (!string.IsNullOrEmpty(current.text)) { mySqlCommand.CommandText = current.text; try { mySqlCommand.ExecuteNonQuery(); num++; this.OnQueryExecuted(current); } catch (Exception ex) { if (this.Error == null) { throw; } if (!this.OnScriptError(ex)) { break; } } } } this.OnScriptCompleted(); result = num; } finally { this.connection.Settings.AllowUserVariables = allowUserVariables; if (flag) { this.connection.Close(); } } return(result); }
/// <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); }
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); } }
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); }
private string ParseDataType(MySqlSchemaRow row, MySqlTokenizer tokenizer) { StringBuilder stringBuilder = new StringBuilder(StringUtility.ToUpperInvariant(tokenizer.NextToken())); row["DATA_TYPE"] = stringBuilder.ToString(); string text = row["DATA_TYPE"].ToString(); string text2 = tokenizer.NextToken(); if (text2 == "(") { text2 = tokenizer.ReadParenthesis(); stringBuilder.AppendFormat(CultureInfo.InvariantCulture, "{0}", new object[] { text2 }); if (text != "ENUM" && text != "SET") { ISSchemaProvider.ParseDataTypeSize(row, text2); } text2 = tokenizer.NextToken(); } else { stringBuilder.Append(ISSchemaProvider.GetDataTypeDefaults(text, row)); } while (text2 != ")" && text2 != "," && string.Compare(text2, "begin", StringComparison.OrdinalIgnoreCase) != 0 && string.Compare(text2, "return", StringComparison.OrdinalIgnoreCase) != 0) { if (string.Compare(text2, "CHARACTER", StringComparison.OrdinalIgnoreCase) != 0 && string.Compare(text2, "BINARY", StringComparison.OrdinalIgnoreCase) != 0) { if (string.Compare(text2, "SET", StringComparison.OrdinalIgnoreCase) == 0 || string.Compare(text2, "CHARSET", StringComparison.OrdinalIgnoreCase) == 0) { row["CHARACTER_SET_NAME"] = tokenizer.NextToken(); } else if (string.Compare(text2, "ASCII", StringComparison.OrdinalIgnoreCase) == 0) { row["CHARACTER_SET_NAME"] = "latin1"; } else if (string.Compare(text2, "UNICODE", StringComparison.OrdinalIgnoreCase) == 0) { row["CHARACTER_SET_NAME"] = "ucs2"; } else if (string.Compare(text2, "COLLATE", StringComparison.OrdinalIgnoreCase) == 0) { row["COLLATION_NAME"] = tokenizer.NextToken(); } else { stringBuilder.AppendFormat(CultureInfo.InvariantCulture, " {0}", new object[] { text2 }); } } text2 = tokenizer.NextToken(); } if (stringBuilder.Length > 0) { row["DTD_IDENTIFIER"] = stringBuilder.ToString(); } if (string.IsNullOrEmpty((string)row["COLLATION_NAME"]) && !string.IsNullOrEmpty((string)row["CHARACTER_SET_NAME"])) { row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation(row["CHARACTER_SET_NAME"].ToString(), this.connection); } 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"], this.connection) * (int)row["CHARACTER_MAXIMUM_LENGTH"]; } return(text2); }
private void ParseProcedureBody(MySqlSchemaCollection parametersTable, string body, MySqlSchemaRow row, string nameToRestrict) { List <string> list = new List <string>(new string[] { "IN", "OUT", "INOUT" }); string text = row["SQL_MODE"].ToString(); int num = 1; MySqlTokenizer mySqlTokenizer = new MySqlTokenizer(body); mySqlTokenizer.AnsiQuotes = (text.IndexOf("ANSI_QUOTES") != -1); mySqlTokenizer.BackslashEscapes = (text.IndexOf("NO_BACKSLASH_ESCAPES") == -1); mySqlTokenizer.ReturnComments = false; string text2 = mySqlTokenizer.NextToken(); while (text2 != "(") { if (string.Compare(text2, "FUNCTION", StringComparison.OrdinalIgnoreCase) == 0 && nameToRestrict == null) { parametersTable.AddRow(); ISSchemaProvider.InitParameterRow(row, parametersTable.Rows[0]); } text2 = mySqlTokenizer.NextToken(); } text2 = mySqlTokenizer.NextToken(); while (text2 != ")") { MySqlSchemaRow mySqlSchemaRow = parametersTable.NewRow(); ISSchemaProvider.InitParameterRow(row, mySqlSchemaRow); mySqlSchemaRow["ORDINAL_POSITION"] = num++; string text3 = StringUtility.ToUpperInvariant(text2); if (!mySqlTokenizer.Quoted && list.Contains(text3)) { mySqlSchemaRow["PARAMETER_MODE"] = text3; text2 = mySqlTokenizer.NextToken(); } if (mySqlTokenizer.Quoted) { text2 = text2.Substring(1, text2.Length - 2); } mySqlSchemaRow["PARAMETER_NAME"] = text2; text2 = this.ParseDataType(mySqlSchemaRow, mySqlTokenizer); if (text2 == ",") { text2 = mySqlTokenizer.NextToken(); } if (nameToRestrict == null || string.Compare(mySqlSchemaRow["PARAMETER_NAME"].ToString(), nameToRestrict, StringComparison.OrdinalIgnoreCase) == 0) { parametersTable.Rows.Add(mySqlSchemaRow); } } text2 = StringUtility.ToUpperInvariant(mySqlTokenizer.NextToken()); if (string.Compare(text2, "RETURNS", StringComparison.OrdinalIgnoreCase) == 0) { MySqlSchemaRow mySqlSchemaRow2 = parametersTable.Rows[0]; mySqlSchemaRow2["PARAMETER_NAME"] = "RETURN_VALUE"; this.ParseDataType(mySqlSchemaRow2, mySqlTokenizer); } }