Problem whit MySql CONCAT()

Problem whit MySql CONCAT()

Postby 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.
Massimo
 
Posts: 13
Joined: Mon 28 May 2007 15:18

Postby 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"
    }
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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();
Massimo
 
Posts: 13
Joined: Mon 28 May 2007 15:18

Postby 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
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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();
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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.
Massimo
 
Posts: 13
Joined: Mon 28 May 2007 15:18

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby Massimo » Fri 08 Oct 2010 15:34

I'll wait anxiously.
Thanks
Massimo
 
Posts: 13
Joined: Mon 28 May 2007 15:18

Postby 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
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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 .
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Problem whit MySql CONCAT()

Postby 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?
bigpoint
 
Posts: 2
Joined: Fri 02 Jun 2006 17:23

Re: Problem whit MySql CONCAT()

Postby Shalex » Wed 23 May 2012 12:20

Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL