| Bug #62435 | not declaring variables returns a byte array instead of a varchar in .Net app | ||
|---|---|---|---|
| Submitted: | 14 Sep 2011 17:39 | Modified: | 16 Sep 2011 16:48 |
| Reporter: | Eduardo Ceballos | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.1.49, 5.1.60 | OS: | Linux (Debian) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | .net, 5.1.49, declaring, stored procedures, variables | ||
[16 Sep 2011 16:19]
Valeriy Kravchuk
This change is easy to demonstrate. Compare 5.1:
macbook-pro:5.1 openxs$ bin/mysql -uroot --column-type-info test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.60 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> call pruebados('');
Field 1: `a`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: MEDIUM_BLOB
Collation: latin1_swedish_ci (8)
Length: 16777215
Max_length: 2
Decimals: 31
Flags:
+------+
| a |
+------+
| 01 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
to 5.0:
macbook-pro:5.0 openxs$ bin/mysql -uroot -T test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.95-debug Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> call pruebados('');
Field 1: `a`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 8192
Max_length: 2
Decimals: 31
Flags:
+------+
| a |
+------+
| 01 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
So, indeed, we see that user variable in 5.1 is returned as BLOB while it is returned as string in 5.0.
I do not see this incompatible change documented at http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html.
[16 Sep 2011 16:48]
Eduardo Ceballos
So how do I have to proceed? the problem is really a bug? thank you
Description: We changed from a MYSQL5.0.26 to a MYSQL5.1.49 server on a Debian server. The stored procedure worked ok on the 5.0.26 but when i moved to 5.1.49 the problem appeared. Description of the problem: =========================== .Net won't understand the result of the Stored procedure as a varchar, while setting a variable inside of a stored procedure without declaring the variable before. It receives a Byte array containing the the chars of the varchar that should had been returned. This is the section of the SProcedure that isn't working in 5.1.49 for .net: ---------------------------------------------------- CREATE DEFINER=`bancaria`@`%` PROCEDURE `pruebados`(par1 varchar(2)) BEGIN set @a = lpad('1',2,'0'); select @a as "a"; END; ---------------------------------------------------- This is the app in .net: ---------------------------------------------------- Dim sqlCommand As New MySqlCommand sqlCommand.Connection = ConectarToMySql(True) sqlCommand.CommandType = CommandType.StoredProcedure sqlCommand.CommandText = "pruebados" sqlCommand.Parameters.AddWithValue("par1", "1") Dim adapt = New MySqlDataAdapter(sqlCommand) adapt.Fill(dt) ---------------------------------------------------- The result in dt is 'System.Byte[]' which contains a 48,49 instead of '01' as it should. If I change the Stored procedure by adding the declaration of the variable, it works ok: ---------------------------------------------------- CREATE DEFINER=`bancaria`@`%` PROCEDURE `pruebados`(par1 varchar(2)) BEGIN declare a varchar(2); set a = lpad('1',2,'0'); select a as "a"; END; How to repeat: CREATE DEFINER=`bancaria`@`%` PROCEDURE `pruebados`(par1 varchar(2)) BEGIN set @a = lpad('1',2,'0'); select @a as "a"; END; ---------------------------------------------------- This is the app in .net: ---------------------------------------------------- Dim sqlCommand As New MySqlCommand sqlCommand.Connection = ConectarToMySql(True) sqlCommand.CommandType = CommandType.StoredProcedure sqlCommand.CommandText = "pruebados" sqlCommand.Parameters.AddWithValue("par1", "1") Dim adapt = New MySqlDataAdapter(sqlCommand) adapt.Fill(dt) ---------------------------------------------------- The result in dt is 'System.Byte[]' which contains a 48,49 instead of '01' as it should. Suggested fix: it can be fixed by declaring all the variables before using them, but i would like to know why this is happening.