Problem whit MySql CONCAT()

Massimo · Post by **Massimo** » Mon 27 Sep 2010 10:38

With the "CoreLab Library", the result of a query that uses the concat () was a column of type System.String, and now with the "Devart Library" the result is a System.byte []. What to do to change my whole solution?
Thanks.

Shalex · Post by **Shalex** » Wed 29 Sep 2010 14:18

I have tried the following code with the 5.80.170 version of dotConnect for MySQL. My output is "System.String". Please tell us your current version of dotConnect for MySQL (Tools > MySQL > About) and MySQL Server. How should we modify this sample to reproduce the issue?

Code: Select all

    using (MySqlConnection conn = new MySqlConnection("server=db;port=3309;database=test;uid=***;pwd=***;")) {
        MySqlDataTable table = new MySqlDataTable("select concat('D','e','v','a','r','t')", conn);
        table.FetchAll = true;
        table.Open();
        Console.WriteLine(table.Columns[0].DataType.ToString()); // "System.String"
    }

Massimo · Post by **Massimo** » Wed 29 Sep 2010 15:26

The problem occurs on concat () with evaluation type string and numeric columns.

CREATE TABLE `nodo` (
`Id` int(11) NOT NULL auto_increment,
`codice` int(10) NOT NULL default '0',
`DescNodo` varchar(60) NOT NULL default '',
`DescNodoTD` varchar(20) NOT NULL default '',
`DescRiferimento` varchar(40) NOT NULL default '',
`Codice_TipoNodo` varchar(50) NOT NULL default '',
`Id_Comune` int(11) default NULL,
`Longitudine` decimal(12,6) NOT NULL,
`Latitudine` decimal(12,6) NOT NULL,
`DistanzaIn` int(11),
`DistanzaOut` int(11),
`FlgTiddy` enum('S','N') NOT NULL default 'N',
`IdNodoCol` int(11) default NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `UKDescNodo` USING BTREE (`DescNodo`,`DescRiferimento`),
KEY `Id_Comune` (`Id_Comune`),
KEY `KIdNodoCol` (`IdNodoCol`),
CONSTRAINT `0_542` FOREIGN KEY (`Id_Comune`) REFERENCES `comune` (`id`),
CONSTRAINT `FKIdNodoCol` FOREIGN KEY (`IdNodoCol`) REFERENCES `nodo` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

StringBuilder oSql = new StringBuilder();
oSql.Append("SELECT Concat(Descnodo,'(',DescRiferimento,')') Descrizione, ");
oSql.Append("Concat('Long. ',Longitudine,'Lat. ',Latitudine) Coordinate ");
oSql.Append("FROM nodo ");
MySqlCommand oCmd = new MySqlCommand(oSql.ToString(), VariabiliGlobali.oConn);
MySqlDataReader oNodi = oCmd.ExecuteReader();
while (oNodi.Read()) {
Console.WriteLine(oNodi["Descrizione"].GetType().ToString()); //System.String
Console.WriteLine(oNodi["Coordinate"].GetType().ToString()); //System.Byte[]
}
oNodi.Close();

Shalex · Post by **Shalex** » Thu 30 Sep 2010 13:58

Thank you for your sample. We will investigate the difference in behaviour of our provider on concat () with strings and numeric columns in the 4.85.36 and 5.80.170 versions of dotConnect for MySQL and notify you about the results.

Shalex · Post by **Shalex** » Tue 05 Oct 2010 10:53

Server returns binary data for the Coordinate column. The 4.85.36 version of our provider converts binary to System.String() in the default encoding implicitly. But we decided that the right approach in this case is to return data as is (byte[]) in order to allow users to convert data in strings with the necessary encoding if needed.

You can use the following methods to obtain results of the previous version: reader.GetString(colId) or reader.GetMySqlBinaryString(colId). The reader.GetDataTypeName(colID) method returns the server type.

Code: Select all

    while (oNodi.Read()) {
        Console.WriteLine(oNodi["Descrizione"].GetType().ToString());
        Console.WriteLine(oNodi.GetString(oNodi.GetOrdinal("Coordinate")).GetType().ToString());
    }
    oNodi.Close();

Massimo · Post by **Massimo** » Thu 07 Oct 2010 13:26

There will be plenty confuzione in source code.
You will never have the certainty of the result.

The query "SHOW CREATE TABLE datigps" columns System.byte returns [].

In MySQL, there are no columns byte [].
Wonder if you can insert a parameter to allow for MySqlConnection copatibilità earlier.

In recent years I have developed a variety of applications, which now in danger of crashing.

Shalex · Post by **Shalex** » Fri 08 Oct 2010 14:53

We will investigate the possibility of adding an option for the previous version compatibility and notify you about the results as soon as possible.

Massimo · Post by **Massimo** » Fri 08 Oct 2010 15:34

I'll wait anxiously.
Thanks

Shalex · Post by **Shalex** » Wed 13 Oct 2010 09:16

We have answered you by e-mail. I will post here when the new build is available.

Shalex · Post by **Shalex** » Thu 14 Oct 2010 15:28

New build of dotConnect for MySQL 5.80.180 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=19238 .

bigpoint · Post by **bigpoint** » Wed 23 May 2012 11:48

Why dont you post a solution to the problem.
I hav just upgraded from 4.85 to 7.06 and experience the same problems. My application crashes at every statement with "CONCAT". What is the solution to this?

Shalex · Post by **Shalex** » Wed 23 May 2012 12:20

Please refer to http://forums.devart.com/viewtopic.php?t=23877.

Devart Forums

Problem whit MySql CONCAT()

Problem whit MySql CONCAT()

Re: Problem whit MySql CONCAT()

Re: Problem whit MySql CONCAT()