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:Microsoft Windows (WIndows XP)
Assigned to:
Tags: SQL_NUMERIC_STRUCT
Triage: D3 (Medium)

[1 Mar 2004 17:40] [ name withheld ]
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.
[1 Mar 2004 17:42] [ name withheld ]
C# source that reproduces the problem - create a project from this

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

[1 Mar 2004 17:44] [ name withheld ]
SQL for creating table for demonstration program

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

[1 Mar 2004 17:47] [ name withheld ]
extract of hopefully relevant section of ODBC trace

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

[1 Mar 2004 17:48] [ name withheld ]
extract from mysql odbc trace file

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

[7 Mar 2004 20:48] Peter Harvey
test

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

[7 Mar 2004 20:54] Peter Harvey
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).
[3 Feb 2005 16:14] vincent ugenti
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).
[11 Sep 2007 18:24] Jim Winstead
Support for SQL_NUMERIC_STRUCT is slated to be implemented in Connector/ODBC 5.1.
[18 Oct 2007 10:40] Susanne Ebrecht
bug #10128 and bug #29631 are duplicates of this bug here.
[22 Oct 2007 11:08] Susanne Ebrecht
bug #31720 is a duplicate of this bug here
[22 Oct 2007 13:35] Susanne Ebrecht
Bug #27864 is a duplicate of this bug here.
[22 Oct 2007 13:38] Susanne Ebrecht
Bug #27889 is a duplicate of this bug here.
[22 Oct 2007 13:39] Susanne Ebrecht
sorry, I meant bug #27881 is a duplicate of this bug here
[5 Nov 2007 16:03] Irwan Hassan
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.
[7 Nov 2007 13:38] Susanne Ebrecht
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
[4 Jan 2008 23:07] Jess Balint
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.
[1 Apr 2011 17:40] Patrick Zahra
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).