[Mono-list] RE: [Mono] MySQL Managed Provider
Reggie Burnett
rykr@bellsouth.net
Wed, 22 Jan 2003 23:32:44 -0600
Using a set of structs for MySQL types is the right approach.
I'll probably knock this out pretty quick so if anyone has any code for
this, send it now.
Reggie
> -----Original Message-----
> From: Daniel Morgan [mailto:danmorg@sc.rr.com]
> Sent: Wednesday, January 22, 2003 11:17 PM
> To: Marlon Baculio
> Cc: Mono-List; reggie@bytefx.com
> Subject: RE: [Mono] MySQL Managed Provider
>
> I would follow the model like System.Data.SqlTypes is for Microsoft
SQL
> Server types. Except, have a namespace Mono.Data.MySqlTypes which
holds
> types like the ones that MySQL has, such as, MySqlInt for int, and
> MySqlDateTime for datetime.
>
> As far as Type coersion, the ODBC.net provider probably keeps the data
as
> a
> string until you try to use it, such as, GetString or GetInt32 or
> GetInt16.
>
> Have you tried using something like the ProviderFactory found in
> Mono.Data?
>
> Here is some sample code:
>
> // test.cs
> using System;
> using System.Data;
>
> public class Test {
> public static void Main(string[] args) {
> IDbConnection conn =
> ProviderFactory.CreateConnectionFromConfig("PubsConnStr");
> Console.WriteLine("Open Connection");
> conn.Open();
> IDbCommand cmd = conn.CreateCommand();
> cmd.CommandText = "select somecolumn from sometable";
> IDataReader reader = cmd.ExecuteReader();
> reader.Read();
> Console.WriteLine("SomeColumn: " +
reader["somecolumn"].ToString());
> }
> }
>
> Or you could use:
> IDbConneciton conn =
> ProviderFactory.Provider["Mono.Data.MySql"].CreateConnection();
> conn.ConnectionString = "Server=localhost;Database=test;User
> ID=mysql;Password=mysecret";
> conn.Open();
>
> I have provided a modified test.exe.config for you.
>
> <?xml version="1.0" encoding="utf-8" ?>
> <configuration>
> <configSections>
> <sectionGroup name="mono.data">
> <section name="providers"
> type="Mono.Data.ProviderSectionHandler,Mono.Data" />
> </sectionGroup>
> </configSections>
> <appSettings>
> <add key="MySqlOdbc"
>
value="factory=System.Data.Odbc;DSN=myodbcdsn;UID=someuser;PWD=somepass"
> />
> <add key="MonoMySql"
> value="factory=Mono.Data.MySql;Server=localhost;Database=test;User
> ID=mysql;Password=myscret" />
> <add key="MySqlNet"
>
value="factory=ByteFX.Data.MySQLClient;Server=localhost;Database=test;Us
er
> ID=mysql;Password=myscret" />
> </appSettings>
> <mono.data>
> <providers>
> <provider
> name="System.Data.Odbc"
>
connection="System.Data.Odbc.OdbcConnection"
>
adapter="System.Data.Odbc.OdbcDataAdapter"
> command="System.Data.Odbc.OdbcCommand"
> assembly="odbc"
> description="ODBC (Mono)" />
> <provider name="Mono.Data.MySql"
>
connection="Mono.Data.MySql.MySqlConnection"
>
adapter="Mono.Data.MySql.MySqlDataAdapter"
> command="Mono.Data.MySql.MySqlCommand"
> assembly="Mono.Data.MySql"
> description="MySQL (Mono)" />
> <provider name="ByteFX.Data.MySQLClient"
>
> connection="ByteFX.Data.MySQLClient.MySQLConnection"
>
adapter="ByteFX.Data.MySQLClient.MySQLDataAdapter"
>
command="ByteFX.Data.MySQLClient.MySQLCommand"
> assembly="ByteFX.Data"
> description="MySQL (ByteFX)" />
> </providers>
> </mono.data>
> </configuration>
>
> -----Original Message-----
> From: Marlon Baculio [mailto:mbaculio@hotmail.com]
> Sent: Wednesday, January 22, 2003 11:25 PM
> To: danmorg@sc.rr.com
> Cc: reggie@bytefx.com
> Subject: RE: [Mono] MySQL Managed Provider
>
>
> Hi Daniel,
>
> All my code in the data layer is using the generic interfaces (such as
> IDataReader) as opposed to provider-specific classes (except when
> instantiating a concrete provider class, in which case, what I do is
> create
> an alias using the "using" C# keyword, so that my code just refers to
the
> (abstracted) alias then I just switch back-and-forth between ODBC.NET
and
> MySQLNet by changing the alias).
>
> Since my code has been coded around ODBC.NET, the problems I find are
> compatibility issues, for example:
>
> 0. [enhancement] In connection string, support for "UID" in addition
to
> "user id"
>
> 1. [bugs] parsing value for MySQLFieldType.BYTE should use
Byte.Parse()
> and
> not Int16.Parse()
>
> 2. ODBC.NET is more forgiving for type coercion. For example, you can
call
> IDataReader.GetString() against an integral type, or you can call
> IDataReader.GetInt32() against a 16-bit column.
>
> 3. I'm also worried about how MySQLField is storing field values as
> objects.
> This will require a lot of boxing and unboxing when value is passed to
the
> client-code, specially if we want to support more relaxed type
coercion
> (although the cost of the boxing/unboxing would be negligible compared
to
> the overall SQL call, but I'm a performance freak so I tend to be
paranoid
> :). My recommendation is to create an abstract class for a field
value,
> then
> derive classes for each type, for example:
>
> abstract class MySQLFieldValue
> {
> ...
> public virtual byte GetByte();
> public virtual Int32 GetInt32();
> ...
> };
>
> class MySQLByteFieldValue : MySQLFieldValue
> {
> public MySQLByteFieldValue(byte[] data)
> {
> ...
> }
> public override byte GetByte()
> {
> return _value;
> }
> public override Int32 GetInt32()
> {
> // no boxing required, while being type-relaxed
> return /*(Int32)*/ _value;
> }
>
> private byte _value;
> };
>
> 4. I feel confident on using MySQLNet because of its performance and
the
> fact that I can fix the code myself being open source. It would be
nice
> though to support connection pooling. Maybe we can create a simple
> ObjectPool that we can use in MySQLConnection.Open() and .Close().
>
>
> (I hope Reggie is reading this) I think what I'm going to do is
continue
> making the changes on my copy of the MySQLNet code and then send the
> original and modified code to Reggie so he can do a diff and decide if
my
> changes deserve to be included.
>
> I've been frequenting the "MySQL forum section" at
> http://www.asp.net/Forums/ShowForum.aspx?tabindex=1&ForumID=56
> Maybe we can get some help/idea from there.
>
> Thank you so much Daniel! I'm been following Mono for quite sometime
now,
> hoping that eventually I will use that to run my ASP.NET web site on
> Linux.
>
>
> Marlon Baculio
> mbaculio@hotmail.com
>
>
> >From: "Daniel Morgan" <danmorg@sc.rr.com>
> >To: "Marlon Baculio" <mbaculio@hotmail.com>
> >CC: "Reggie Burnett" <reggie@bytefx.com>
> >Subject: RE: [Mono] MySQL Managed Provider
> >Date: Wed, 22 Jan 2003 21:57:22 -0500
> >
> >Hi Marlon,
> >
> >Reggie Burneet is the lead developer of MySQLNet. He would be glad
to
> have
> >patches to the provider. Can you be more specific about the problems
you
> >are having. Can you provide simple test cases?
> >
> >Same thing with the provider in Mono - patches are welcome.
> >
> >Mono.Data.MySql is in maintenance mode and is mainly meant to work on
> Mono.
> >Try using MySQLNet which will become the choice for Mono in the
future
> >since
> >it works on Mono and .NET, faster,
> >and no dependencies on a client library.
> >
> >Have you been using the various interfaces in ADO.NET, such as,
> >IDbConnection, IDbCommand, IDataReader, IDbDataAdapter, etc. ?
> >
> >IDbConnection dbcon = new MySqlConnection();
> >dbcon.ConnectionString = "Server=localhost;Database=test;User
> >ID=mysql;Password=mysecret";
> >IDbCommand dbcmd = dbcon.CreateCommand();
> >dbcmd.CommandText = "select * from sometable";
> >IDataReader reader = dbcmd.ExecuteReader();
> >
> >These interfaces provide a generic way to use different providers.
Also,
> >there is the ProviderFactory and DataTools found in Mono.Data.dll
which
> can
> >be used to dynamically create connections, commands, parameters, and
> >adapters via some configuration.
> >Take a look at the test for it at mcs/class/Mono.Data/Test/test.cs
> >which uses a .config file named test.exe.config
> >for various app settings and providers.
> >
> >Daniel
> >
> >-----Original Message-----
> >From: Marlon Baculio [mailto:mbaculio@hotmail.com]
> >Sent: Wednesday, January 22, 2003 1:09 AM
> >To: danmorg@sc.rr.com
> >Subject: [Mono] MySQL Managed Provider
> >
> >
> >Hi Daniel,
> >
> >(Please excuse me if this e-mail or this address is inappropriate.)
> >
> >I've been checking out MySQLNet from ByteFX. I've heard you are also
> >writing
> >a separate MySQL Managed Provider for Mono.
> >
> >I'm writing a web site that uses MySQL using ODBC.NET. I am not
satisfied
> >with the performance so I am looking for alternatives. MySQLNet from
> ByteFX
> >looks so promising but there's a few problems with it. My ultimate
test
> for
> >ADO.NET compatibility (at least for my site) is to able to switch
between
> >ODBC.NET and MySQLNet (or any MySQL Provider) without a problem like
> this:
> >
> >using DBConnection = Microsoft.Data.Odbc.OdbcConnection;
> >using DBCommand = Microsoft.Data.Odbc.OdbcCommand;
> >
> >//using DBConnection = ByteFX.Data.MySQLClient.MySQLConnection;
> >//using DBCommand = ByteFX.Data.MySQLClient.MySQLCommand;
> >
> >
> >In fact, I've been making bug fixes/adjustments to my copy of the
ByteFX
> >code until I get a word from Reggie Burnette.
> >
> >If I don't get a response from him, I wish to ask you the following
> >question:
> >
> >0. Any timeline or chance your provider will use pure C# to talk
directly
> >with the MySQL Server?
> >1. Any immediate plans for connection pooling?
> >2. Any chance I could contribute? (example: for ByteFX, I wish to
make it
> >more forgiving on type coercions like ODBC.NET, and less
boxing/unboxing)
> >
> >So, basically, I'm torn as to which project I would use in my site
:-)
> >
> >Thank you!!!
> >
> >Marlon Baculio
> >mbaculio@hotmail.com
> >
> >
> >
> >_________________________________________________________________
> >Add photos to your e-mail with MSN 8. Get 2 months FREE*.
> >http://join.msn.com/?page=features/featuredemail
>
>
> _________________________________________________________________
> Protect your PC - get McAfee.com VirusScan Online
> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963