Bug #24920 Binding Decimal Parameter Fails in MS Access
Submitted: 8 Dec 2006 17:24 Modified: 14 Dec 2007 16:00
Reporter: Jess Balint Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1 OS:Windows (Windows)
Assigned to: Jess Balint CPU Architecture:Any
Tags: bind parameter

[8 Dec 2006 17:24] Jess Balint
Description:
Attempting to bind a parameter with a SQL_DECIMAL type results in a failure when the statement is executed.

Failure message from ODBC trace log:

MSACCESS        eb0-ac4	ENTER SQLBindParameter 
		HSTMT               07A12690
		UWORD                        3 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                       -8 <SQL_C_WCHAR>
		SWORD                        3 <SQL_DECIMAL>
		SQLULEN                   10
		SWORD                        2 
		PTR                0x09442B94
		SQLLEN                     0
		SQLLEN *            0x09442B90

MSACCESS        eb0-ac4	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               07A12690
		UWORD                        3 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                       -8 <SQL_C_WCHAR>
		SWORD                        3 <SQL_DECIMAL>
		SQLULEN                   10
		SWORD                        2 
		PTR                0x09442B94
		SQLLEN                     0
		SQLLEN *            0x09442B90 (6)

MSACCESS        eb0-ac4	ENTER SQLExecDirectW 
		HSTMT               07A12690
		WCHAR *             0x09442388 [      -3] "UPDATE `newbug` SET `x`=?  WHERE `x` = ? AND `y` = ?\ 0"
		SDWORD                    -3

MSACCESS        eb0-ac4	EXIT  SQLExecDirectW  with return code -1 (SQL_ERROR)
		HSTMT               07A12690
		WCHAR *             0x09442388 [      -3] "UPDATE `newbug` SET `x`=?  WHERE `x` = ? AND `y` = ?\ 0"
		SDWORD                    -3

		DIAG [07006] [MySQL][MyODBC 5.00.09] Restricted data type attribute violation (0) 

MSACCESS        eb0-ac4	ENTER SQLErrorW 
		HENV                07A11540
		HDBC                07A115E8
		HSTMT               07A12690
		WCHAR *             0x0013EB9C (NYI) 
 		SDWORD *            0x0013EBE8
		WCHAR *             0x025319E8 
		SWORD                     4095 
		SWORD *             0x0013EBD4

MSACCESS        eb0-ac4	EXIT  SQLErrorW  with return code 0 (SQL_SUCCESS)
		HENV                07A11540
		HDBC                07A115E8
		HSTMT               07A12690
		WCHAR *             0x0013EB9C (NYI) 
 		SDWORD *            0x0013EBE8 (0)
		WCHAR *             0x025319E8 [      64] "[MySQL][MyODBC 5.00.09] Restricted data type attribute violation"
		SWORD                     4095 
		SWORD *             0x0013EBD4 (64)

How to repeat:
-- create a test table with some data
create table newbug ( x int not null, y decimal(10,2) not null, primary key (x) );
insert into newbug values (1, 1.2),(2, 2.3),(3, 3.4);

Link this table with Access and try to update the data. It will fail.

Suggested fix:
Allow binding a to a decimal field.
[3 Dec 2007 22:53] Jess Balint
Verified working against svn rev 913 (will be released in 5.1.1).
[14 Dec 2007 16:00] MC Brown
A note has been added to the 5.1.1 changelog: 

Added support for SQL_NUMERIC_STRUCT.