[Mono-list] Re: New stuff in Mono.Data.MySql

tim@timcoleman.com tim@timcoleman.com
Fri, 15 Nov 2002 11:44:23 -0500


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,

-- 
Tim Coleman <tim@timcoleman.com>                       [43.43 N 80.45 W]
BMath, Honours Combinatorics and Optimization, University of Waterloo
Software Developer, Global Services, Open Text Corporation
"Under capitalism, man exploits man.  Under communism, it's just the
 opposite." -- J.K. Galbraith