MySqlConnector
MySqlConnector copied to clipboard
mysql-net
Implement MySqlScript
Hello,
is there any MySqlScript replacement for the original code of Oracle? Couldn't find a class for that.
Regards Martin
Hi Martin,
This library is a drop-in replacement for Oracle's MySql.Data package. The MySqlConnection class uses the same namespace, MySql.Data.MySqlClient.
Read our tutorial for using with .NET Core MVC for some examples. These examples also work for the full .NET Framework.
Hi, yes i know, but there's is no MySqlScript class!
Any replacement or workaround for that?
Regards Martin
My apologies, I misunderstood your question. I do not think we implement the MySqlScript class presently.
Based on the tutorial the MySqlScript class seems like a simple wrapper around MySqlCommand.
Rough outline of public API:
public class MySqlScript
{
public MySqlScript([MySqlConnection connection,] [string query]);
public int Execute();
public Task<int> ExecuteAsync();
public MySqlConnection Connection { get; set; }
public string Query { get; set; }
public string Delimiter { get; set; }
public event StatementExecuted;
public event ScriptCompleted;
public event Error;
}
Hi,
not so simple, it has a tokenizer etc. to split a series of commands into blocks and then execute them one by one.
Look at the original code from Orcacle:
https://github.com/mysql/mysql-connector-net/blob/6.9/Source/MySql.Data/MySqlScript.cs
Regards
BTW,
is there a alternative to:
MySql.Data.Types ? like MySql.Data.Types.MySqlDateTime and others?
Regards Martin
No, many of the Oracle extensions in Connector/NET that aren't part of ADO.NET aren't implemented in this library.
I'm not seeing the benefit of MySqlScript over just using MySqlCommand directly (or a wrapper like Dapper's Execute):
- forces multiple round-trips to the server, which is needlessly inefficient
- doesn't support query parameterisation, which makes SQL injection more likely
Perhaps there could be some benefit from the StatementExecuted event, but that could almost always be simulated with a foreach loop. (Unless you're accepting arbitrary SQL from the user and need to know as each statement is processed or fails, but the use cases for that seem extremely limited.)
It's also hard to find a use of the type (on GitHub) that isn't just a copy of this test file.
@bgrainger The benefit I see is that DELIMITER doesn't work within MySqlCommand. The workaround is to break compound command into separate MySqlCommand invocations, but that won't work in cases where the SQL script can't be changed.
After investigating Oracle's code, I found out that that damn tokenizer IS the reason that MySqlScript is required in the first place!
If you want to CREATE PROCEDURE or CREATE TRIGGER or whatever, you can't with a simple MySqlCommand because every MySqlCommand uses the tokenizer, and finds out those semicolons that "breaks" the sql and makes it invalid syntax.
The MySqlScript solves that with DELIMITER and all, but we wouldn't need that in the first place if MySqlCommands worked correctly.
If you want to CREATE PROCEDURE or CREATE TRIGGER or whatever, you can't with a simple MySqlCommand because every MySqlCommand uses the tokenizer, and finds out those semicolons that "breaks" the sql and makes it invalid syntax.
Are you talking about MySql.Data or MySqlConnector? AFAIK there's no problem with doing everything in one MySqlCommand, e.g., https://github.com/mysql-net/MySqlConnector/blob/7159b637bf668de40a7e77fdca09c319921bf019/tests/SideBySide/StoredProcedureFixture.cs#L10-L127
If that's the only reason MySqlScript needs to exist in Oracle's MySQL Connector/NET, then there's little reason to add it to MySqlConnector.
It's good for running scripts from sql files on disk or supplied by users, but that's a risky case anyway.
And it could be solved by taking the tokenizer from MySql.Data and splitting to commands.
Hi just wanna share this code that was copied from mysql source, and works fine for me, thanks. https://github.com/tofilagman/Mysql.Powershell/tree/master/Mysql.Powershell/MySqlClient
@tofilagman Note that you'll have to initialize its properties (i.e BackslashEscapes) from the Mysql's MODE before usage, in order for that to be safe.
@danielgindi yes, that is why i have to expose the two tokenizer properties on MySqlScript Class because MysqlConnector doesnt support it yet and let the developer decide to configure it. hope that make sense, thanks.
tokenizer.AnsiQuotes = AnsiQuotes;
tokenizer.BackslashEscapes = !NoBackslashEscapes;
Because MySqlScript is unsafe by default, and also "unnecessary", one option would be to implement it in a new NuGet Package: MySqlConnector.MySqlScript. This would avoid cluttering up the base package and providing an API that people really shouldn't use, while still making it possible for people to port existing code to MySqlConnector. OTOH, it would probably be difficult for people to learn that a separate NuGet package is needed; the compiler error about a missing type isn't going to help them discover and install it.
Yes an extension package with stuff the Oracle adapter has but MySqlConnector not. Things should be named almost identical. So it would an ease to switch to MySqlConnector without missing functions.
OTOH, it would probably be difficult for people to learn that a separate NuGet package is needed; the compiler error about a missing type isn't going to help them discover and install it.
Yep, but I think that who needs that will find with a simple search/open issue on this repo.
Thats really not difficult. Dapper i.e. has many additional packages. No problem to find and reference them. "MySqlConnector.Extensions" package i.e.
Per https://github.com/mysql-net/MySqlConnector/issues/824, changing the default namespace for the main classes could let "legacy" types (including MySqlScript) be moved to their own namespace.
Just came here years later looking for MySqlScript in MySqlConnector, too. I'm glad I found this thread.
Guys to keep you informed:
I solved this issue by using FluentMigrator. You have built in support for lots of databases , creating/upgrade/downgrade them in an object oriented way and you can easily copy tables between databases very simply by using Dapper Entity extensions. I implemented a backup functionality by this so MySqlScript is no longer needed for that purpose.
Cheers