Devart.Data.MySql.MySqlScript no Delimiter attribute

[christo667]

I am evaluating Devart dotConnect for MySQL. I just ported my ADO.NET based MySQL interface, developed using MySQL Connector/NET to the Devart dotConnect assembly. I found, however, that there is no support for the Delimeter attribute in the MySqlScript class. How does one set a delimiter other than a semi-colon, as is necessary for sending down scripts containing stored routines? I've been using the standard practice of wrapping the stored routine code in

DELIMITER &&
..........
DELIMITER ;

so I can use semi-colons as the delimiters in the stored routine code. What is the work-around?

[StanislavK]

Could you please describe the problem you are encountering using the delimiter attribute in more details? I.e., how you are initializing and executing MySqlScript, and what is the exact error message you are receiving? Also, please specify the versions of dotConnect for MySQL and MySQL server you are using.

I was able to execute a script with delimiters in the following way:

Code: Select all

string scriptText = @"
  delimiter &&

  CREATE PROCEDURE procedure1 (OUT c INT)
    BEGIN
      select count(*) from Dept into c;
    END&&

  delimiter ;
  ";

MySqlScript script = new MySqlScript(scriptText, con);
script.Execute();

[christo667]

Hi, Stanislav:

I'm not getting any error messages. I encountered this incompatibility at the source porting level. As I stated, the issue is that the Devart version of MySqlScript does not support the Delimiter attribute which is a part of the standard MySql Connector/NET distribution from MySQL.com.

The issue is that in an SQL script which contains stored routine code, one must use at least two different delimiters: one to delineate among blocks of stored routine DROP and CREATE statements at the script level, and one to delimit statements within the stored procedure code.

A standard method is to use a "DELIMITER &&" statement before stored routine code, and use && to delimit stored routine DROP and CREATE statements, while using the standard semi-colon character to delimit statements within the stored routine code. Typically a script then has a "DELIMITER ;" statement after the stored routine CREATE statements in the script to switch back to the standard semi-colon delimiter for further SQL statements.

This mechanism is required in order to submit properly delimited stored routine code to the MySQL server using the MySQL distribution Connector/NET version of the MySqlScript class. The DELIMITER statements have to be interpreted by the client side code as they are encountered in a script, the client side code has to submit SQL scripts through a MySqlScript object, informing the MySqlScript object that the delimiter has changed each time it does in the source script by changing the Delimiter attribute of that MySqlScript object.

I did find some other reports of problems with the DELIMITER statements in scripts elsewhere on this forum, but found no answer that had any details regarding the lack of the Delimiter attribute in the DevArt version of the MySqlScript class. I am wondering if it is the case that the DevArt MySqlScript class actually performs the interpretation of the DELIMITER statements itself automatically and relieves the client code of having to do so? Otherwise, I don't see how anyone using the DevArt MySqlScript class could send scripts to the database in their code that had stored code. I believe that must be the case if you are able to submit the script example you supplied without having to submit it piece-wise through the DevArt MySqlScript object with changes to the Delimiter attribute as is required with the standard MySqlScript class instance objects.

BTW -- the version of MySQL Server is irrelevant, this is a client side issue. The versions of the standard MySQL.com Connector / NET I'm using are 6.1.2.0 and 6.2.3 (current latest release as of today.)

The DevArt dotConnect version is the latest: dotConnect for MySQL version 5.80.

I will experiment with just submitting scripts with DELIMITER statements in them to the DevArt version of MySqlScript class objects when I have time. In the mean time I suggest that for compatibility reasons a future release of the DevArt class include the Delimiter attribute in the MySqlScript class so code developed for the Connector/NET will compile, and just ignore it and continue to have the MySqlScript class automatically interpret DELIMITER statements.

[StanislavK]

Thank you for your suggestion, we will investigate the possibility of adding the Delimiter property for migration purposes.

MySqlScript is parsed by dotConnect for MySQL and its commands are sequentially executed on the server. Using the "DELIMITER && ... DELIMITER ;" environment is the standard approach, it is supported, e.g., by the MySQL client console. Moreover, it is more useful than setting the MySqlScript.Delimiter property, as a large script with different delimiters may be executed.

[StanislavK]

We've implemented the Delimiter property of the MySqlScript component. It specifies the command delimiter that is used in the script by default (i.e., before the DELIMITER statement is explicitly called). This feature will be available in the nearest build of dotConnect for MySQL.

[StanislavK]

We have released the new 6.0.69 version of dotConnect for MySQL where these changes are available. The new build can be downloaded from
http://www.devart.com/dotconnect/mysql/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information about the fixes and improvements available in dotConnect for MySQL 6.0.69, please refer to
http://www.devart.com/forums/viewtopic.php?t=19794