[Mono-list] RE: [Mono] MySQL Managed Provider

Marlon Baculio mbaculio@hotmail.com
Thu, 23 Jan 2003 06:43:05 +0000


(Daniel,) The Provider Factory looks interesting. Haven't really seen it yet 
until now. I'll check it out soon.

And, YES, it's tempting to keep the string specially since:
0. MySQL seems to return things/integrals in string
1. MySQLNet converts byte array to string anyway (GC for that string won't 
really happen immediately)
2. there would be users (like me) who'd call GetString() instead of the more 
appropriate GetInt32, for example. If they can get away with that in 
ODBC.NET/SqlClient, they'd expect the same behavior in MySQLNet.

I agree with Reggie on structs for different MySQL types, though we'd have 
to keep an internal "DB Null" flag since structs can't be null when unboxed.

Marlon


>From: "Reggie Burnett" <rykr@bellsouth.net>
>To: "'Daniel Morgan'" <danmorg@sc.rr.com>,"'Marlon Baculio'" 
><mbaculio@hotmail.com>
>CC: "'Mono-List'" <mono-list@ximian.com>
>Subject: RE: [Mono] MySQL Managed Provider
>Date: 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


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail