MySQL Bugs: #3028: error when updating a decimal, float or double field in .net

Bug #3028	error when updating a decimal, float or double field in .net
Submitted:	1 Mar 2004 17:40	Modified:	4 Jan 2008 23:07
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1	OS:	Windows (WIndows XP)
Assigned to:		CPU Architecture:	Any
Tags:	SQL_NUMERIC_STRUCT

Description:
I have a form that uses an odbcconnection and data adapter to modify a MyISAM table.  When I insert a new record everything's fine.  When I attempt to update a record by changing a value that has a type of decimal, double or float I get the following error:

ERROR [07006][MySQL][ODBC 3.51 Driver][mysqld-4.0.17-max-nt]Restricted data type attribute violation(SQL_C_NUMERIC)

If I use the same program but change the ODBC driver to point to a SQL Server 2000 database instead of a MySql database, it works.

How to repeat:
I am including a program and the schema for a table that reproduces the problem.  Basically, create a Windows Forms program with C# that will allow you to put a record into a table containing at least 1 value that is typed as a decimal, float or double.  Then, provide a method for updating that value.

Suggested fix:
Since I'm not sure exactly where this problem is occurring, I can't make an intelligent guess.

C# source that reproduces the problem - create a project from this

Attachment: Form1.cs (text/plain), 8.28 KiB.

SQL for creating table for demonstration program

Attachment: VenueMaster.sql (text/plain), 1.01 KiB.

extract of hopefully relevant section of ODBC trace

Attachment: SQL.LOG.txt (text/plain), 1.01 KiB.

extract from mysql odbc trace file

Attachment: logextract.txt (text/plain), 5.68 KiB.

test

Attachment: test.c (text/plain), 12.36 KiB.

According to the given log; the column defined as DECIMAL is being treated as a NUMERIC. This is supposed to be ok - but MyODBC does not handle NUMERIC data type in all functions. Presumably this is because NUMERIC requires the interpretation of a SQL_NUMERIC_STRUCT - so extra work.

This problem is demonstrated at the C API level with test.c (attached to this bug).

The solution to this is to fully implement SQL_NUMERIC/SQL_C_NUMERIC/SQL_NUMERIC_STRUCT with MyODBC. I will see about doing this.

In the meantime; a work-around may be to use a different data type. For example; I notice that he table struct supplied by customer uses DECIMAL(10,0).

Try the .NET Connector 1.0.4 it works great with decimal fields (but has problems with certain double values such as 1.38 due to rounding errors).

Support for SQL_NUMERIC_STRUCT is slated to be implemented in Connector/ODBC 5.1.

bug #10128 and bug #29631 are duplicates of this bug here.

bug #31720 is a duplicate of this bug here

Bug #27864 is a duplicate of this bug here.

Bug #27889 is a duplicate of this bug here.

sorry, I meant bug #27881 is a duplicate of this bug here

This bug has been solved by using double indeed of decimal data type. This helped me. Please visit http://forums.asp.net/t/258663.aspx for help.

Hi Irwan,

many thanks for your feedback.

Using double is just a work around and you know, using double for databases is not always a good recommendation.

That there is a work around, doesn't mean, that this bug here is fixed.

Regards,

Susanne

Thanks Tonci!

This working fine with the recent implementation of SQL_NUMERIC_STRUCT support. Please note that the original test DDL given specifies:

 defaultEventTax decimal(10,0) default '0',

This reserves 0 space for the fraction part. I've tested successfully with:

 defaultEventTax decimal(10,5) default '0',

There is a problem with updating the DataRowView thing because this app re-connects after inserting the row, before updating. The key is saved in the DataRowView as NULL and the SQL_AUTO_IS_NULL option will only work on the same connection as the next query.

I got this, too, on the 3.51 driver. The following code DOES work:

for(int tries=0; tries<3; ++tries) {
	try {
		comm.ExecuteNonQuery();
		break;
	} catch(Exception ex) {
		if(!ex.Message.Contains("SQL_C_NUMERIC")) throw ex;
	}
}

I gave it three tries just in case, but it usually works on the second (and sometimes the first).