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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.