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

Daniel Morgan danmorg@sc.rr.com
Thu, 23 Jan 2003 00:16:39 -0500
Previous message: [Mono-list] Marshaling works with Mono .16 but not Mono .18
Next message: [Mono-list] RE: [Mono] MySQL Managed Provider
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
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;User
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
Previous message: [Mono-list] Marshaling works with Mono .16 but not Mono .18
Next message: [Mono-list] RE: [Mono] MySQL Managed Provider
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]