Unexpected pattern of auto-assigned parameter types

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
MikeBeaton
Posts: 4
Joined: Tue 28 Feb 2017 10:26

Unexpected pattern of auto-assigned parameter types

Post by MikeBeaton » Thu 16 Mar 2017 19:31

I am working on a cross-db ADO.NET wrapper project. I am using the DbProviderFactory for connections, and working with System.Data.Common classes. I have noticed the following, when reading back a TINYINT(1) return value from a test function:

On the Devart dotConnect for MySQL driver:

- If I assign a bool value to my return parameter, the parameter DbType gets automatically set to Int64 and the returned value is a C# short (!)
- If I assign a byte value, the parameter DbType is set to Int16 and the returned value is a C# short
- If I assign an sbyte value, the parameter DbType is set to Int16 and the returned value is a C# short

This compares to the Oracle/MySQL Connector/NET driver on which:

- If I assign a bool value to my return parameter, the parameter DbType gets automatically set to SByte and the returned value is a C# sbyte
- If I assign a byte value, the parameter DbType is set to Byte and the returned value is a C# byte
- If I assign an sbyte value, the parameter DbType is set to SByte and the returned value is a C# sbyte

I find the pattern of results in dotConnect for MySQL quite unexpected (unlike the pattern in Connector/NET) and I suspect other users might find it so too?

Thanks.

Pinturiccio
Devart Team
Posts: 2141
Joined: Wed 02 Nov 2011 09:44

Re: Unexpected pattern of auto-assigned parameter types

Post by Pinturiccio » Tue 21 Mar 2017 14:02

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

Pinturiccio
Devart Team
Posts: 2141
Joined: Wed 02 Nov 2011 09:44

Re: Unexpected pattern of auto-assigned parameter types

Post by Pinturiccio » Fri 05 May 2017 12:33

The MySqlParameter.DbType property can be set manually, otherwise it will defined automatically basing on a parameter value. If MySqlParameter.Value is set to a bool value, then its MySqlType property is automatically assigned to MySqlType.Bit. In its turn, MySqlType.Bit is represented as Int64. For more information, please refer to https://www.devart.com/dotconnect/mysql ... lType.html.

DbType determines how dotConnect for MySQL converts value before sending it to MySQL server. Bool can have two values (1 and 0), and it doesn't matter what DbType is used: Int16, Int32 or Int64.

The described mapping is used in dotConnect for MySQL for a long time, and changing it could break the backward compatibility for many users.

If you do not want to get Int64 for bool value, you can manually assign MySqlParameter.DbType=DbType.Int16 (it is the smallest integer type which can be assigned to the DbType property for MySqlParameter).

Post Reply