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

Question

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

By any chance, is your code affected by this bug: bugs.mysql.com/bug.php?id=12514 — Praveen Kumar Purushothaman
– Praveen Kumar Purushothaman, Commented Jun 19, 2018 at 15:03
@MoStar not a duplicate, I've edited the question to explain it better — Master_T
– Master_T, Commented Jun 19, 2018 at 15:29
You should provide the version of MySQL database and MySQL Connector/ODBC that you are using. Would you add this information on your question too? — kahveci
– kahveci, Commented Jun 19, 2018 at 15:33
My suggestion comes from this link : forums.mysql.com/read.php?37,650722,651027#msg-651027 - I couldn't find any reference to JSON datatype in the Release Notes. I don't know if modifying the query to cast the column as char/varchar is possible - if so that may be a workaround for you. — PaulF
– PaulF, Commented Jun 19, 2018 at 15:43

Master_T · Accepted Answer · 2018-06-19 15:54:21Z

4

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.

answered Jun 19, 2018 at 15:54

Master_T

8,18116 gold badges95 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PaulF · Accepted Answer · 2018-06-19 15:54:27Z

2

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.

answered Jun 19, 2018 at 15:54

PaulF

6,8032 gold badges21 silver badges30 bronze badges

2 Comments

Master_T Over a year ago

Thanks, accepted this because it's you that pointed me in the right direction, if anyone needs the full solution I've posted it here: stackoverflow.com/a/50932457/300741

PaulF Over a year ago

You beat me to moving my comments to an answer - it is good that you have posted the updated query with the cast shown.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related