Unexpected pattern of auto-assigned parameter types

Unexpected pattern of auto-assigned parameter types

Postby 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.
MikeBeaton
 
Posts: 4
Joined: Tue 28 Feb 2017 10:26

Re: Unexpected pattern of auto-assigned parameter types

Postby 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: 2046
Joined: Wed 02 Nov 2011 09:44

Re: Unexpected pattern of auto-assigned parameter types

Postby 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/docs/?Devart.Data.MySql~Devart.Data.MySql.MySqlType.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).
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for MySQL