| Bug #37485 | Using CONCAT in SQL query causes Connector to return System.Byte[] | ||
|---|---|---|---|
| Submitted: | 18 Jun 2008 16:52 | Modified: | 19 Jun 2008 17:00 |
| Reporter: | Gauron Kolas | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 5.1.6 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | asp.net, concat, System.Byte[] | ||
[19 Jun 2008 8:43]
Tonci Grgin
Hi Gauron and thanks for your report. This is not a bug but expected behavior and here's why:
o) CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:
SELECT CONCAT(CAST(int_col AS CHAR), char_col);
o) No BINARY flag is set (as per above):
mysql> SELECT CONCAT("Tonci", " ", "Grgin") AS Name;
Field 1: `Name`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 11
Max_length: 11
Decimals: 31
Flags: NOT_NULL
+-------------+
| Name |
+-------------+
| Tonci Grgin |
+-------------+
1 row in set (0.01 sec)
o) BINARY flag is set (this is where your problem is!):
mysql> SELECT CONCAT("Tonci ", 1, " Grgin") AS Name;
Field 1: `Name`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 13
Max_length: 13
Decimals: 31
Flags: NOT_NULL BINARY
+---------------+
| Name |
+---------------+
| Tonci 1 Grgin |
+---------------+
1 row in set (0.00 sec)
Try setting "Respect Binary Flags" to false and read the c/NET documentation. Also, you can explicitly cast such queries to retrieve result as string (BINARY flag not set):
mysql> SELECT CAST(CONCAT("Tonci ", 1, " Grgin") AS CHAR) AS Name;
Field 1: `Name`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 13
Max_length: 13
Decimals: 31
Flags: NOT_NULL
+---------------+
| Name |
+---------------+
| Tonci 1 Grgin |
+---------------+
1 row in set (0.00 sec)
[19 Jun 2008 17:00]
Gauron Kolas
Thanks Tonci, I just inserted a CAST on the numeric argument to CHAR in the middle of the CONCAT(...) statement and it works as expected now. :)
Description: After upgrading from the latest 5.0.x connector to 5.1.6, this problem appears. Using Visual Studio .NET 2005 on Windows XP, MySql server 5.0.51a on win2k3, and a simple query inside a SqlDataSource. Strangely, the problem is only present when running on localhost with Visual Studio's built-in web browser. If I deploy it to a test area under IIS, the correct string values are displayed instead of System.Byte[]. How to repeat: Install the .NET connector, version 5.1.6 Create a new Web Form (.aspx page) Add a SqlDataSource and bind it to a simple control like a DropDownList to see the results: <asp:DropDownList ID="TestDDL" runat="server" AutoPostBack="True" DataSourceID="MySqlDS" DataTextField="NewStringField" DataValueField="MyPrimaryKeyID"> </asp:DropDownList> <asp:SqlDataSource ID="MySqlDS" runat="server" ConnectionString=" ... " ProviderName=" ... " SelectCommand="SELECT MyPrimaryKeyID, Concat(MyVarCharField, ' (MyPrimaryKeyID ', MyPrimaryKeyID, ')') As NewStringField FROM SomeTable WHERE SomeID = 25"> </asp:SqlDataSource>