[Mono-list] MySql Data Types.
Ben Clewett
Ben@roadrunner.uk.com
Mon, 18 Nov 2002 15:21:09 +0000
Daniel,
The plot thickens. In the MySqlTest, the following var types are used,
which do not appear in MySqlEnumFieldTypes:
MySQL Var .NET Var
========= ========
smallint Int16
mediumint Int64
int Int32
integer Int32
bigint Int64
real Double
numeric Decimal
char String
tinytext String
text String
mediumtext String
longtext String
And the folling appear in MySqlEnumFieldTypes, but are not used in the
MySqlTest:
MySQL Var DbType Conversion
========= ================
FIELD_TYPE_SHORT Int16
FIELD_TYPE_LONG Int64
FIELD_TYPE_LONGLONG Int64
FIELD_TYPE_INT24 Int64
FIELD_TYPE_DATETIME DateTime
FIELD_TYPE_YEAR Int16
FIELD_TYPE_NEWDATE Date
Although some of these are probably a re-naming of the first, it's
confusing, especially considering the number of different integers. For
instance 'mediumint' is Int64. Should this map to LONG, or LONGLONG...
Also I note that FIELD_TYPE_ENUM is an Int32 in the Mono code, yet the
underlying data type MySQL interfaces is a String. This I would guess
is that FIELD_TYPE_ENUM should be created as a mirror Enumerated list as
used internally with MySQL. Although I can forsee terrible bugs when
the Enumerated String list is edited in SQL and not the code, and
visa-versa.
The 'blob' type is also slightly worrying, as this uses String. But
since this is I belive supposed to be 8-bit clean binary, then using
16-bit unclean Unicode might cause problems, eg, the '\0' value. But I
might be missunderstanding the System.String data type.
There is definitelly some fun to be had here...
Daniel Morgan wrote:
> 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).
>
>
>
>
>
> _______________________________________________
> Mono-list maillist - Mono-list@ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-list