It appears that MySqlDataReader.GetString() (and other GetXyz() functions) have problems dealing with ambiguous columns in a result set. GetXyz() gives the error "No data exists for the row/column", both when using a hard-coded ordinal and the ordinal retrieved by GetOrdinal(). GetOrdinal() simply picks the first field with the given column name it can find in the result set, which is correct. Unfortunately, GetXyz() has problems doing so.
To reproduce the problem, create the following two test tables:
Code: Select all
CREATE TABLE table1 (
field_id tinyint(3) NOT NULL,
foreign_field tinyint(3) default NULL,
PRIMARY KEY (field_id)
);
CREATE TABLE table2 (
field_id tinyint(3) NOT NULL,
field_value varchar(5) default NULL,
PRIMARY KEY (field_id)
);
INSERT INTO table1 VALUES (1, 1);
INSERT INTO table1 VALUES (2, 2);
INSERT INTO table2 VALUES (1, 'test1');
INSERT INTO table2 VALUES (2, 'test2');Code: Select all
using System;
using CoreLab.MySql;
namespace MySqlTest
{
class Program
{
static void Main(string[] args)
{
MySqlConnection connection = new MySqlConnection("user id=****;password=****;host=localhost;port=3306;database=test");
connection.Open();
string sql = @"SELECT *
FROM table1 t1
LEFT JOIN table2 t2
ON t2.field_id = t1.foreign_field
WHERE t1.field_id = 1";
MySqlCommand command = new MySqlCommand(sql, connection);
MySqlDataReader reader = command.ExecuteReader();
int fieldId = reader.GetOrdinal("field_id");
Console.WriteLine(reader.GetString(fieldId));
reader.Close();
connection.Close();
Console.ReadLine();
}
}
}Unfortunately, this problem is also a show-stopper for my project.
