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: https://bugs.mysql.com/bug.php?id=12514 — Praveen Kumar Purushothaman, Jun 19 '18 at 15:03
Possible duplicate of [How to convert json into datatable?](https://stackoverflow.com/questions/7641004/how-to-convert-json-into-datatable) — Mo Star, Jun 19 '18 at 15:15
@MoStar not a duplicate, I've edited the question to explain it better — Master_T, Jun 19 '18 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, Jun 19 '18 at 15:33
@PraveenKumar well, the error message is the same, although no mention of JSON there so I'm not sure... what I do know is that if I exclude the JSON-type column from the select query it works without issues. — Master_T, Jun 19 '18 at 15:35
@Master_T Hey, in that case, have you upgraded the MySQL to the latest version that **supports JSON based data**? — Praveen Kumar Purushothaman, Jun 19 '18 at 15:35
I think it is more an issue of the JSON datatype not being supported by the ODBC driver. — PaulF, Jun 19 '18 at 15:38
@PraveenKumar I'm using the very latest version of MySql (8.0.11, on both the server and the odbc driver). As a side note: if I have a table with column type JSON I must be using a version of MySql server that supports it... otherwise I wouldn't have it :D — Master_T, Jun 19 '18 at 15:38
@PaulF: thanks for the suggestion... is this documented anywhere? I'm using the latest version of the official ODBC driver (8.0.11). Are there any versions or alternative drivers that DO support it? — Master_T, Jun 19 '18 at 15:39
My suggestion comes 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](https://dev.mysql.com/doc/relnotes/connector-odbc/en/). 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, Jun 19 '18 at 15:43
@PaulF: thanks, casting the column to CHAR worked correctly! — Master_T, Jun 19 '18 at 15:51
This has become a nice discussion. It is also suggested to CAST the JSON type to CHAR as workaround in this link: https://mysqlserverteam.com/getting-started-with-mysql-json-on-windows — kahveci, Jun 19 '18 at 15:58

score 3 · Answer 1 · answered Jun 19 '18 at 15:54

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.

score 2 · Accepted Answer · answered Jun 19 '18 at 15:54

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 '18 at 15:54

PaulF

6,673
2
18
29

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: https://stackoverflow.com/a/50932457/300741 – Master_T Jun 19 '18 at 15:57
You beat me to moving my comments to an answer - it is good that you have posted the updated query with the cast shown. – PaulF Jun 19 '18 at 16:00

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

2 Answers2