CODEWITHSUNDEEP
c#mysqldatatable
Master_T
Master_T

Reputation: 7913

Loading data to DataTable in C# gives "Unknown SQL type - 0" error

I've been successfully using the following code to load data from an ODBC connection into a C# DataTable for some time without issues:

public static DataTable ExecuteSqlSelect(string sql, string connectionString)
        {
            var result = new DataTable();
            using (var connection = new OdbcConnection(connectionString))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = sql;
                var dbReader = command.ExecuteReader();
                result.Load(dbReader);

                connection.Close();
            }

            return result;
        }

However, I now have a MySql table with a column of type JSON. When I try to load data from that table with that method, I get the following error:

Unknown SQL type - 0.

I assume this is because the JSON type is not recognized by C#'s DataTable. Is this correct? And more importantly: is there a solution/workaround for this?

EDIT: I'm not trying to convert a JSON string to a DataTable, as a commenter suggested... I'm trying to load a SQL table that contains a columns of MySQL type 'JSON' into a DataTable. I don't need JSON parsing, it would be fine if I just got the raw JSON string into the DataTable.

EDIT 2: both MySql and the ODBC connector are the latest version: 8.0.11

Upvotes: 0

Views: 2351

Answers (2)

PaulF
PaulF

Reputation: 6773

I think it is more an issue of the JSON datatype not being supported by the ODBC driver - my suggestion coming from this link : https://forums.mysql.com/read.php?37,650722,651027#msg-651027 - I couldn't find any reference to JSON datatype in the Release Notes.

You could try casting the column to char/varchar as a workaround for you.

Upvotes: 2

Master_T
Master_T

Reputation: 7913

Thanks to PaulF's suggestion in the comments, I was able to solve this. Since the ODBC driver doesn't properly support JSON, you have to cast the column to text, directly in the query. So if before I had:

SELECT col1, col2, jsonCol FROM table;

I replaced it with:

SELECT col1, col2, CAST(jsonCol as CHAR(256)) as jsonCol FROM table;

this converts the column to normal text and it is then correctly loaded into the DataTable.

Upvotes: 3

Related Questions