[Mono-list] MySql Data Types.

[Ben Clewett]

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:

> Ben,
>
> Understand below that I only refer to SQL Server.  I don't know the
> MySQL Mono.Data provider.
>
>
> >Tim,
> >
> >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).
>
> The parameter object doesn't really use the DbType value, but it
> is useful if you are writing more general-purpose code that you want
> to switch to a different provider, etc.
>
> >Unfortunatelly, none of these types hold the same list of data types.
> >For example, the 'blob' types, which may I think be used with
> >DbType.Binary.  But I can't find any reasonable type for the 'TimeStamp'.
> >
> >This is with respect to your Parameter.  However, since they are all
> >being written as a String into the final SQL, should I just use String
> >for any which don't match up?
>
>
> No.  You should not use string.  In the SqlParameter example, assume
> that you have a statement like
>
> insert into my_table (int_value) values (@p1)
>
> and you create a parameter @p1 with a string type (i.e. varchar)
> with value 42.  When you execute the statement, it will send to
> SQL Server:
>
> sp_executesql N'insert my_table (int_value) values (@p1)', N'@p1 
> varchar', '42'
>
> which will give you a SqlException, because you're trying to insert
> a varchar value into an int column.
>
> If you want to look at the SqlClient code, you'll see that it isn't
> *exactly* converting the value to a string.  If it's a string type,
> it actually converts the value to a string contained in apostrophes (').
> Again, this may be completely different in MySQL.
>
>
> >In short, does is actually matter what data type I use as long as MySQL
> >understands it with respect to the colunm definition?
> >
> >Hope this is an easy question to answer,
>
>
> Perhaps someone else will be able to talk about MySQL.
>
> Cheers,
>