MySQL Bugs: #4439: Carriage returns in SQL causes problems

Bug #4439	Carriage returns in SQL causes problems
Submitted:	7 Jul 2004 19:43	Modified:	8 Jul 2004 17:51
Reporter:	Reggie Burnett	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:		OS:
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Hi,

I've been using your provider for a while now and i have to say its
very good.  However, I seem to be having problems when executing multiple
SQL statements in the same query for example and insertion statement
followed by a SELECT LAST_INSERT_ID().  I keep getting a "Query was empty"
message even though the process completes and the data is inserted.  It seems to affect ExecuteReader, ExecuteScalar and most annoyingly ExecuteNonQuery where it should return the number of rows affected but always causes this error.  

Any ideas?

Steve

How to repeat:
n/a

Suggested fix:
n/a

What version of the provider and of MySql are you using and can you send me an example query string you are sending?

-reggie

Hi Reggie,

This is the SQL string:

insert into survey(notifyEmail, name, active, allowCopy, autorespond, getMobile,getTelephone, getFax, getAddress,getSex) values(@notifyemail, @name,  @active, @allowCopy,@autorespond,@getMobile,
@getTelephone,@getFax,@getAddress, @getSex);select @surveyId = LAST_INSERT_ID();

The provider is 0.7.6.15073 of ByteFX.Data.SQLClient

The MySQL version is 4.0.18.

The query seems to fire fine in cmd-line or MySQL Front and the data gets created in the database successfully.  It just falls over on the line where you call dbCommand.ExecuteReader/Scalar/NonQuery within the code.

Steve

Hi Reggie,

Found the problem, our multiple query had carriage returns in it due to the fact that we are using a providerfactory object and storing our queries in XML.  For some reason, MySQL doesn't like carriage returns very much in a SQL query.  I have made a change to our source code for the ByteFX provider to strip these from the SQL statement.  Maybe its something to think about for the next release?

Steve

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html