Implement MySqlScript

[ElDuderinoBerlin]

Hello,

is there any MySqlScript replacement for the original code of Oracle?
Couldn't find a class for that.

Regards
Martin

[caleblloyd]

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.

[joefrando]

Hi,
yes i know, but there's is no MySqlScript class!

Any replacement or workaround for that?

Regards
Martin

[caleblloyd]

My apologies, I misunderstood your question. I do not think we implement the MySqlScript class presently.

[bgrainger]

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;
}

[joefrando]

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

[joefrando]

BTW,

is there a alternative to:

MySql.Data.Types ? like MySql.Data.Types.MySqlDateTime and others?

Regards
Martin

[bgrainger]

No, many of the Oracle extensions in Connector/NET that aren't part of ADO.NET aren't implemented in this library.

[bgrainger]

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.

[lefth]

@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.

[danielgindi]

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.

[bgrainger]

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.,

MySqlConnector/tests/SideBySide/StoredProcedureFixture.cs

Lines 10 to 127 in 7159b63

	Connection.Execute(@"DROP FUNCTION IF EXISTS echof;
	CREATE FUNCTION echof(
	name VARCHAR(63)
	) RETURNS VARCHAR(63)
	BEGIN
	RETURN name;
	END;
	DROP FUNCTION IF EXISTS failing_function;
	CREATE FUNCTION failing_function()
	RETURNS DECIMAL(10,5)
	BEGIN
	DECLARE v1 DECIMAL(10,5);
	SELECT c1 FROM table_that_does_not_exist INTO v1;
	RETURN v1;
	END;
	DROP PROCEDURE IF EXISTS echop;
	CREATE PROCEDURE echop(
	IN name VARCHAR(63)
	)
	BEGIN
	SELECT name;
	END;
	DROP PROCEDURE IF EXISTS circle;
	CREATE PROCEDURE circle(
	IN radius DOUBLE,
	IN height DOUBLE,
	IN name VARCHAR(63),
	OUT diameter DOUBLE,
	OUT circumference DOUBLE,
	OUT area DOUBLE,
	OUT volume DOUBLE,
	OUT shape VARCHAR(63)
	)
	BEGIN
	SELECT radius * 2 INTO diameter;
	SELECT diameter * PI() INTO circumference;
	SELECT PI() * POW(radius, 2) INTO area;
	SELECT area * height INTO volume;
	SELECT 'circle' INTO shape;
	SELECT CONCAT(name, shape);
	END;
	DROP PROCEDURE IF EXISTS out_string;
	CREATE PROCEDURE out_string(
	OUT value VARCHAR(100)
	)
	BEGIN
	SELECT 'test value' INTO value;
	END;
	DROP PROCEDURE IF EXISTS out_null;
	CREATE PROCEDURE out_null(
	OUT string_value VARCHAR(100),
	OUT int_value INT
	)
	BEGIN
	SELECT NULL INTO string_value;
	SELECT NULL INTO int_value;
	END;
	drop table if exists sproc_multiple_rows;
	create table sproc_multiple_rows (
	value integer not null primary key auto_increment,
	name text not null
	);
	insert into sproc_multiple_rows values
	(1, 'one'),
	(2, 'two'),
	(3, 'three'),
	(4, 'four'),
	(5, 'five'),
	(6, 'six'),
	(7, 'seven'),
	(8, 'eight');
	drop procedure if exists number_multiples;
	create procedure number_multiples (in factor int)
	begin
	select name from sproc_multiple_rows
	where mod(value, factor) = 0
	order by name;
	end;
	drop procedure if exists multiple_result_sets;
	create procedure multiple_result_sets (in pivot int)
	begin
	select name from sproc_multiple_rows where value < pivot order by name;
	select name from sproc_multiple_rows where value > pivot order by name;
	end;
	drop procedure if exists number_lister;
	create procedure number_lister (inout high int)
	begin
	DECLARE i int;
	SET i = 1;
	WHILE (i <= high) DO
	select value, name from sproc_multiple_rows
	where value <= high
	order by value;
	SET i = i + 1;
	END WHILE;
	SET high = high + 1;
	end;
	drop procedure if exists `dotted.name`;
	create procedure `dotted.name`()
	begin
	select 1, 2, 3;
	end;
	DROP PROCEDURE IF EXISTS `GetTime`;
	CREATE PROCEDURE `GetTime`(OUT OutTime TIME)
	BEGIN
	SET OutTime = CURTIME();
	END;");

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.

[danielgindi]

I'm talking about MySql.Data :-)

[danielgindi]

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.

[tofilagman]

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

[danielgindi]

@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.

[tofilagman]

@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;

[bgrainger]

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.

[ElDuderinoBerlin]

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.

[MarcoRossignoli]

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.

[ElDuderinoBerlin]

Thats really not difficult. Dapper i.e. has many additional packages. No problem to find and reference them.
"MySqlConnector.Extensions" package i.e.

[bgrainger]

Per #824, changing the default namespace for the main classes could let "legacy" types (including MySqlScript) be moved to their own namespace.

[MPITech]

Just came here years later looking for MySqlScript in MySqlConnector, too. I'm glad I found this thread.

[ElDuderinoBerlin]

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