MySQL Bugs: #34912: Execute VB.NET T-SQL Case When...End, result is returned as Byte.Array

Bug #34912	Execute VB.NET T-SQL Case When...End, result is returned as Byte.Array
Submitted:	27 Feb 2008 22:29	Modified:	1 Mar 2008 0:02
Reporter:	Antonis B.	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.1.2	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	Byte.Array, Connector/Net, T-SQL Case When...End

Description:
Hello, i have mySql Server version GA 5.0.51 and working with Visual Web Developer 2005 Express using MySQL ConnectorNet 5.1.2 and trying to execute the below VB.Net Function:

Private Function GetCustomValue(ByVal FieldID As Integer) As Object
Dim Conn As New MySqlConnection(ConfigurationManager.AppSettings("xxxxxxxxx"))
        Dim ReturnedValue As Object = Nothing
        Dim Sql As String = "select f.field_type, " & _
                                "case f.field_type " & _
                                    "when 1 then v.integer_value " & _
                                    "when 2 then v.number_value " & _
                                    "when 3 then v.currency_value " & _
                                    "when 4 then v.text_value " & _
                                    "when 5 then v.html_value " & _
                                    "when 6 then v.date_value " & _
                                    "when 7 then v.photo_id " & _
                                    "when 8 then v.percentage_value " & _
                                "end as field_value " & _
                            "from ContentValues v " & _
                                "inner Join " & _
                                    "fields f " & _
                                        "on v.field_id = f.field_id " & _
                            "where f.field_id = ?field_id"

        Dim cmdSql As New MySqlCommand(Sql, Conn)
        cmdSql.Parameters.AddWithValue("?field_id", FieldID)
        Conn.Open()
        Dim rd As MySqlDataReader = cmdSql.ExecuteReader
        rd.Read()
        If rd.HasRows Then
            ReturnedValue = rd("field_value")
        Else
            ReturnedValue = System.DBNull.Value
        End If
        rd.Close()
        cmdSql.Dispose()
        Conn.Close()
        Conn.Dispose()
        Return ReturnedValue
End Function

As you can see i execute a T-SQL statement with case when...end.
The result is always a Byte.Array but, in "ContentValues" table, i have NO any field as longblob or binary or other datatype that gives reason for this result.

This problem disappeared when i was use the previews connectorNet varsion 5.0.7.

Thanks

How to repeat:
I give all details in description section of the problem

Suggested fix:
use the previews connectorNet varsion 5.0.7

Antonis

I assembed a test case pretty close to what you are reporting and what you are seeing is the new binary respect that is in 5.1.  You can run your query from the mysql command line using the 5.1 client and give the --column-type-info command line parameter to see what I am talking about.  Since your case statement can return multiple types of data then mysqld is returning it as binary.  Here is a paste of that column from my test:

Field   2:  `field_value`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     65535
Max_length: 1
Decimals:   31
Flags:      BINARY
+------+-------------+
| id   | field_value |
+------+-------------+

we determine whether a field is binary by the collation and the flags.  They are both set to binary in this example which means by default this column will be returned as VAR_BINARY.

You have three options.  Rewrite your query to return a single data table that is not binary,  use a data reader and the .GetSTring() method will will convert the binary value to a string, or set the new connection string option 'Respect Binary Flags' to false which more or less reproduces the old 5.0 behavior. 
|    1 | 1           |
+------+-------------+

Mr. Burnett,
Thanks for your replay, your specifications was help me very much.
I was suspecting that but, i was confused by the old version 5.0 different behavior, than the newer.

My Best Regards,
Antonis B.