[Mono-list] MySql Data Types.

[Daniel Morgan]

Ben,

The MySqlEnumFieldTypes enumeration was based on the enum enum_field_types
found in the mysql/include/mysql_com.h header file.  A lot of the technical
details came from the MySQL docs or their header files.

Take a look at the classes in System.Data.SqlTypes.
We can use the ideas in System.Data.SqlTypes to create MySQL type classes in
namespace Mono.Data.MySqlTypes.

Some examples of the MySqlType classes include:

MySqlType class MySQL type
=============== ==========
MySqlDateTime   datetime
MySqlString     varchar
MySqlTimestamp  timestamp
MySqlInt32      long

A new enumeration named MySqlType can be created for the MySQL types like
SqlDbType is for System.Data.SqlTypes.

The MySqlParameter, MySqlParameterCollection, and MySqlCommand classes would
be modified:
- to use the MySqlDbType enumeration in addition to the DbType enumeration
- to use the MySQL type classes in Mono.Data.MySqlTypes

By the way, once you contribute something to Mono and you are going to
continue contributing, you can get your own cvs account.

Daniel

-----Original Message-----
From: Ben Clewett [mailto:Ben@roadrunner.uk.com]
Sent: Monday, November 18, 2002 8:25 AM
To: Daniel Morgan
Subject: Re: [Mono-list] MySql Data Types.


Daniel,

I have not yet finnished testing, so I cannot validate anything.

The MySqlEnumFieldTypes are yours.  The 'U' extension for SQL:
"UNSIGNED" are not in any code, I just included them to be complete.
Thanks for the error with the TimeStamp, I'll correct that in my code.

SQL Server version 3.23.52.nt.  I am using the clinet .dll which came
with this complete instalation, and the Mono.Data.MySql from Friday's
nightly snapshot.

It didn't occure to me that there would be a data type change from ver 3
to 4.  I would hope that there is backward compatibility, something I'll
check when I have Ver 4.

I am impressed with your data conversions in MySqlTypes.cs, but it does
show the problem, with the three data types, (.NET, DbType and
MySqlEnumFieldTypes) which are all needed.  For instance, it's very
difficult to overload a function with .NET variables, where a DataTime
is used for two possible DbTypes, and three possible MySql types.  All
of which might be used if creating an SQL statement using then
MySqlParametersCollection.  There is also no bounds checking, for
instance, where an Int16 is used for both 'short' and 'tiny'.

I would be very intersted in a native MySql data type using .NET
variables as the actual storage.  Would it then be possible to 'typedef'
a .NET variable as a MySql type, including bounds checking, and
version-safe conversions?  Therefore allowing safe overloading and easy
coding for the .NET environment.

Such a class could also be inherited into a class containing Field Name
for SQL commands, and possible also a Field Comparison for 'where'
statments:  WhereField = MySqlClass("name", ">", "value");

If there is anything I can do, please let me know.

Ben



> Ben,
>
> What version of MySQL server and what version of MySQL client are you
> using?
>
> The MySQL provider is based on Production version 3.23.
> Currently, the provider does not support Development version 4.
>
> In theory, it should be upwards compatible to version 4.
>
> Eventually, we would want the provider to support 3.23, 4.x, and future
> versions...
>
> If what you provided is for MySQL version 4.x, are those
> MySqlEnumFieldTypes
> enumerations backwards compatible with version 3.23?
>
> If they are not, then we will have to create a separate MySQL type
> conversion function for each particular version.  The provider already
> gets
> the version of the database in the MySqlConnection class as property
> ServerVersion, so you could use this to determine which conversion
> function
> to use.
>
> I made a mistake on the MySQL timestamp; it should be mapped to a DateTime
> instead of a String.
>
> Just like SqlClient for Microsoft SQL Server has database-specific type
> classes at System.Data.SqlTypes which allows a DBNull for Int32 via
> SqlInt32, a DBNull for String via SqlString, a DBNull for DateTime via
> SqlDateTime, etc...   Maybe we should create MySqlTypes classes too.
>
> Daniel
>
> -----Original Message-----
> From: mono-list-admin@ximian.com [mailto:mono-list-admin@ximian.com]On
> Behalf Of Ben Clewett
> Sent: Monday, November 18, 2002 5:14 AM
> To: tim@timcoleman.com; mono-list@ximian.com
> Subject: [Mono-list] MySql Data Types.
>
>
> Thanks.  I have found the correct conversions in the existing code, below.
>
> As the MySql code does not use the SqlDbType, neither shall I.  This
> appears to be a too small subset of values for MySql.
>
> If I then use this, the MySqlParameter is always going to work?
>
> --------------
>
> Correct DataType Conversion for use of MySql, from MySqlHelper.
> (Systerm.Data.DbType equal or larger than MySql Types)
>
> MySqlEnumFieldTypes	System.Data.DbType
>
> FIELD_TYPE_DECIMAL	Decimal
> FIELD_TYPE_TINY		Int16
> FIELD_TYPE_SHORT	Int16
> FIELD_TYPE_SHORT U	UInt16
> FIELD_TYPE_LONG		Int64
> FIELD_TYPE_LONG U	UInt64
> FIELD_TYPE_FLOAT	Single
> FIELD_TYPE_DOUBLE	Double
> FIELD_TYPE_NULL		String
> FIELD_TYPE_TIMESTAMP	String
> FIELD_TYPE_LONGLONG	Int64
> FIELD_TYPE_LONGLONG U	UInt64
> FIELD_TYPE_INT24	Int64
> FIELD_TYPE_INT24 U	UInt64
> FIELD_TYPE_DATE		Date
> FIELD_TYPE_TIME		Time
> FIELD_TYPE_DATETIME	DateTime
> FIELD_TYPE_YEAR		Int16
> FIELD_TYPE_NEWDATE	Date
> FIELD_TYPE_ENUM		Int32
> FIELD_TYPE_SET		String
> FIELD_TYPE_TINY_BLOB	Binary
> FIELD_TYPE_MEDIUM_BLOB	Binary
> FIELD_TYPE_LONG_BLOB	Binary
> FIELD_TYPE_BLOB		Binary
> FIELD_TYPE_VAR_STRING	String
> FIELD_TYPE_STRING	String
>
> tim@timcoleman.com wrote:
>
>
> >Understand below that I only refer to SQL Server.  I don't know the
> >MySQL Mono.Data provider.
> >
> >
> >>I am running into a problem with the Data Types.
> >>
> >>The MySqlParameterCollection used the data types 'DbType'.
> >>In MySqlTypes there are the types MySqlEnumFieldTypes.
> >>There is also the SqlDbType in System.Data, as included in your email
> >>yesterday.
> >
> >Ah, yes.  There are several different type enumerations.  If you
> >read the .NET Framework documentation closely, you may get an
> >understanding as to the difference between SqlDbType and DbType
> >in System.Data.
> >
> >SqlDbType is a set of data types used by SQL Server.  In fact,
> >most of these types are supported by SQL Server 7, with the exception
> >of SqlDbType.BigInt and SqlDbType.Variant, which are SQL Server 2000
> >types.
> >
> >But, then, what is DbType?  The way I see it, DbType is a more general
> >set of data types.  Most of these types will work with SQL Server,
> >but then, some of them won't.  For instance, DbType.UInt32 will not
> >work.  Go ahead and try to create a SqlParameter with this DbType.
> >You'll get an ArgumentException when you do.
> >
> >So, DbType is more abstract, and SqlDbType is specific to SQL Server.
> >Magically, with a SqlParameter, if you set the SqlDbType, it will
> >also set the corresponding DbType in the parameter and vice versa
> >(and, if you provide neither, both will be inferred from the type
> >of its value...I'm working on this at the moment).