Get<SomeDataType>() and ambiguous fields

Get<SomeDataType>() and ambiguous fields

Postby Vic D'Elfant » Wed 07 Nov 2007 11:19

Hello,

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');


The following script will allow you to reproduce the issue easily:
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();
      }
   }
}

The above example, of course, could be using a totally different query without a LEFT JOIN which would make the above example work perfectly, but it's just a proof of concept. The problem I am having in my project is that there's a query that gets data from about 6 different tables, and listing every field specifically in the select expression using field_name AS table_field_name is not an option, nor is running 6 different queries due to optimization reasons.

Unfortunately, this problem is also a show-stopper for my project.
Vic D'Elfant
 
Posts: 7
Joined: Mon 03 Sep 2007 09:43

Postby Alexey » Wed 07 Nov 2007 15:03

We will investigate the problem you described.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Vic D'Elfant » Fri 09 Nov 2007 10:53

Thank you for looking into this. Would there happen to be any updates regarding the issue? I wouldn't mind using non-released build for time being if you could quickly patch the problem; I'm currently not able to do any development due to this and it's part of a school assignment :(
Vic D'Elfant
 
Posts: 7
Joined: Mon 03 Sep 2007 09:43

Postby Serious » Fri 09 Nov 2007 13:04

Please call MySqlDataReader.Read() before accessing result set data.
Serious
 

Postby Vic D'Elfant » Fri 09 Nov 2007 13:40

Yet another case of missing the obvious. Calling .Read() indeed fixed the problem, sorry for wasting your time guys.
Vic D'Elfant
 
Posts: 7
Joined: Mon 03 Sep 2007 09:43


Return to dotConnect for MySQL