I am trying to execute the command 'SET SESSION sql_mode = 'PAD_CHAR_TO_FULL_LENGTH' using .NET connector (MySql.Data version 6.8.3.0). Everything works fine, except in the case that I call a stored procedure. In this case it causes the exception (0x80004005): Incorrect database name 'sakila
To restore the problem, here is the C# code and a short script that builds a test DB. Note that commenting the lines:
cmd.CommandText = "SET SESSION sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'";
cmd.ExecuteNonQuery();
eliminates the error.
It turns out that executing "SET GLOBAL sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'" in MySQL workbench causes the problem even if these lines are commented but it is not fired in MySQL workbench.
Here is the C# code:
using MySql.Data.MySqlClient;
using System.Data;
namespace PadCharDBTest
{
class Program
{
static void Main(string[] args)
{
using (var conn = new MySqlConnection("Server=localhost;Database=testquestion;Uid=root;Pwd=*****;"))
{
conn.Open();
using(var cmd = new MySqlCommand(null, conn))
{
//1.sets the sql mode
cmd.CommandText = "SET SESSION sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'";
cmd.ExecuteNonQuery();
// 2. simple select
cmd.CommandText = "select * from mytable";
using (var r = cmd.ExecuteReader()) { }
// 3. stored procedure
cmd.CommandText = "getTopNames";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("top", MySqlDbType.Int32, 3));
using (var reader = cmd.ExecuteReader()) { }
}
}
}
}
}
And here is an SQL script to build the test DB:
CREATE DATABASE IF NOT EXISTS testQuestion;
USE testQuestion;
DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable (
Id int(11) NOT NULL AUTO_INCREMENT,
Name varchar(45) NOT NULL,
PRIMARY KEY (`Id`)
);
INSERT INTO mytable (Name)
VALUES
('AAA'),
('BBB'),
('CCC'),
('DDD'),
('EEE');
DROP PROCEDURE IF EXISTS getTopNames;
DELIMITER $$
CREATE PROCEDURE getTopNames (IN top INT)
BEGIN
SELECT * FROM mytable LIMIT top;
END$$
DELIMITER ;
Thanks
User contributions licensed under CC BY-SA 3.0