MySQL Bugs: #10486: MySqlDataAdapter.Update error for decimal column

Bug #10486	MySqlDataAdapter.Update error for decimal column
Submitted:	9 May 2005 16:56	Modified:	25 Jun 2005 18:47
Reporter:	petro	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	1.0.4	OS:	Windows (Windows XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
I use MySQL Connector Net 1.0.4 against mysql server 4.1.11.

MySqlDataAdapter.Update method fires exception like:

"#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0, 0, 0, 75.00, 0, 0, 0, 75.00, 0, 0, 'NO', 'YES', 1, '2005-04-04 08:2' at line 1"

Connector Net 1.0.4  DOES NOT HANDLE PROPERLY DECIMAL(NUMERIC) COLUMNS WHEN WORKS WITH mysql server 4.1.11.

However, Connector Net 1.0.4  DOES HANDLE PROPERLY DECIMAL(NUMERIC) COLUMNS WHEN WORKS WITH mysql server 4.1.9.

So I have to use older version of 4.1.*.
Since you recomend using currently 4.1.* , this error has to be fixed.

I'm aware, that Connector Net 1.0.4 works fine with MySQL 5.0, but  MySQL 5.0 is not a release yet.

Thank you.

How to repeat:
Use MySQL Connector Net 1.0.4 against mysql server 4.1.11.
Create a table with decimal column.
Run MySqlDataAdapter.Update for the table.

Suggested fix:
Use older version 4.1.9 of MySQL server

Could you write example code and structure of table ?

I am having the same issue, here are my steps to reproduce:

Table structure for copy_of_orders

OrderId int(11), not null, Primary key, auto-increment
UserAccountId int(11) unsigned, not null, default=0
SubTotal decimal(10,2), not null, default=0.00
ShippingCharge decimal(10,2), not null, default=0.00
DateEntered timestamp, not null, default = CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Quick test insert code:

		private void Test(MySqlConnection conn)
		{
			MySqlCommand oCommand = new MySqlCommand();

			oCommand.Parameters.Add( "@UserAccountID", 11) ;
			oCommand.Parameters.Add( "@SubTotal", (decimal) 175.88) ;
			oCommand.Parameters.Add( "@ShippingCharge", (decimal) 3.95) ;
			oCommand.Parameters.Add( "@DateEntered", DateTime.Now) ;
			oCommand.CommandText = "insert into copy_of_orders (UserAccountID, SubTotal, ShippingCharge, DateEntered) VALUES (@UserAccountID, @SubTotal, @ShippingCharge, @DateEntered)" ;

			oCommand.Connection = conn ;
			oCommand.CommandType = CommandType.Text ; 
			oCommand.ExecuteNonQuery() ;
		}

Error Msg:

#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '175.88, 3.95, '2005-05-25 21:07:03')' at line 1

Bellow are the error related data:

the table with decimal columns structue:

CREATE TABLE `film` (
  `FilmIndex` int(10) NOT NULL auto_increment,
  `Title` varchar(50) NOT NULL default '',
  `FilmCode` varchar(9) NOT NULL default '',
  `DistributorIndex` int(11) NOT NULL default '1',
  `RatingIndex` int(11) NOT NULL default '1',
  `SpecialCode` varchar(5) NOT NULL default '',
  `OpeningDate` datetime NOT NULL default '2004-01-01 00:00:00',
  `ClosingDate` datetime NOT NULL default '2004-01-01 00:00:00',
  `Runtime` smallint(5) unsigned NOT NULL default '5',
  `BookingNumber` int(2) unsigned NOT NULL default '0',
  `RunType` char(1) NOT NULL default '',
  `DistributorID` char(3) NOT NULL default '',
  `ReleaseNumber` varchar(6) NOT NULL default '',
  `Reason` varchar(30) NOT NULL default '',
  `Synopsis` text NOT NULL,
  `ActiveFlagsValue` bigint(20) unsigned NOT NULL default '0',
  `ICAACertNumber` varchar(12) NOT NULL default '',
  `ICAAFilmCopy` varchar(6) NOT NULL default '',
  `ICAAVersionIndex` int(10) NOT NULL default '1',
  `ICAAShortIndex` int(10) NOT NULL default '1',
  `ICAANationalityIndex` int(20) NOT NULL default '1',
  `ICAALanguageIndex` int(20) NOT NULL default '1',
  `ICAAGenVersionIndex` int(20) NOT NULL default '1',
  `ICAAOriginIndex` int(20) NOT NULL default '1',
  `ICAAAutonomiaIndex` int(20) NOT NULL default '1',
  `ICAMIGACNumber` varchar(20) NOT NULL default '',
  `ICAMProducer` varchar(30) NOT NULL default '',
  `ICAMDirector` varchar(30) NOT NULL default '',
  `ICAMOriginCountry1Index` int(5) NOT NULL default '1',
  `ICAMOriginCountry2Index` int(5) NOT NULL default '1',
  `ICAMOriginCountry3Index` int(10) NOT NULL default '1',
  `Test` decimal(10,2) NOT NULL default '0.00',
  `CNCGuarantMinAm` decimal(5,2) NOT NULL default '0.00',
  `CNCRent` decimal(4,2) NOT NULL default '0.00',
  `CNCTrailerCode` varchar(9) NOT NULL default '',
  `CNCTrailer` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`FilmIndex`),
  UNIQUE KEY `FilmCodeUnique` (`FilmCode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

void CallingFun()
{
this.currentDBHandler.InitTableAdapter("film", "FilmIndex",
"INSERT INTO film (FilmCode) VALUES( '' );",
ref mainDataSet, out this.filmCAdapter) )

//Where exception fires
this.currentDBHandler.UpdateTable("film", ref this.mainDataSet, this.filmCAdapter);

}

public bool InitTableAdapter(string sourceTableName, string inIndexColumnName, ref DataSet dataSet, 
out CustomAdapter inCustomAdapter)
{
DataRow defDataRow;
inCustomAdapter = null;
							
MySqlDataAdapter curDataAdapter = new MySqlDataAdapter();

curDataAdapter.SelectCommand = new MySqlCommand("SELECT * FROM " + sourceTableName.Trim() + " order by " + inIndexColumnName, this.currentConnection);
		
curDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

MySqlCommandBuilder curCommandBuilder = new MySqlCommandBuilder(curDataAdapter);

try
{

//......

curDataAdapter.Fill(dataSet, sourceTableName.Trim());
				
dataSet.Tables[sourceTableName].AcceptChanges();

return true;
}
catch (Exception e)
{
//.....
return false;
				
}

public bool UpdateTable(string sourceTableName, ref DataSet dataSet, CustomAdapter inCAdapter)
{
ArrayList exprColArrayList = new ArrayList();
							
try
{
//.......

//Where exception fires
inCAdapter.dataAdapter.Update(dataSet, sourceTableName.Trim());

dataSet.Tables[sourceTableName].AcceptChanges();

return true;
}
catch (MySqlException e)
{
//......
return false;
				
}
catch (Exception e)
{
//.......
return false;
				
}

Tested on Win 2000 Sp4, Microsoft Visual C# .NET , Connector .NET 1.0.4 Test case is attached.

Test case

Attachment: 10486.zip (application/x-zip-compressed, text), 3.77 KiB.

No the new connector/ mysql 5.07 does not solve the problem on Windows XP SP2. This is nasty. Help please.

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

Additional info:

I believe this bug is fixed as a result of fixing Bug #11550  	Adding decimal parameters problem [fixed]

This fix is in 1.0.5

Documented in 1.0.5 changelog.