Bug #25231 decimal parameter in stored procedure does not accept null value

• Submitted: 21 Dec 2006 13:11
• Modified: 21 Dec 2006 13:18
• Reporter: Matthew Son
• Status: Can't repeat
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.24
• OS: Windows (Windows XP)
• CPU Architecture: Any

[21 Dec 2006 13:11] Matthew Son

Description:
I have a stored procedure that had a decimal(6,2) input field.  The input value is put directly into a table with the same type definition.  The field is nullable in the database and this is a straight feed into the table.

I am using C#.NET 2005 to call the stored procedure.

I get an ODBC error when I send a null value to the input parameter.  

Everything worked fine when I made all the definitions varchar.

How to repeat:
create a table with a decimal (6,2) field.

create a stored procedure that simply inserts a record into that table

call the stored procedure and pass a null value in that field.

Suggested fix:
stored procedure input values should be able to use null

[21 Dec 2006 13:18] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.34-BK on Linux:

mysql> create table tdec(c1 decimal(6,2));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tdec values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> create procedure p1(inp decimal(6,2)) insert into tdec values(inp);
Query OK, 0 rows affected (0.07 sec)

mysql> call p1(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tdec;
+------+
| c1   |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.34-debug |
+--------------+
1 row in set (0.00 sec)

So, please, try to use newer version, 5.0.27, on Windows. If you'll get the same results as me, it is not a server bug.