MySQL OLE DB Provider_青春寂寞洒一地

http://blog.sina.com.cn/u/1686974573

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

MySQL OLE DB Provider

(2011-09-10 09:07:51)

标签：

杂谈

分类：软件开发

MySQL OLE DB Provider

Introduction

MySQL probably is the most popular open source database server. To learn more about MySQL Database server, please visit MySQL Website.

You may download MySQL, its tool and their source code from MySQL Website. The MySQL OLE DB Provider is implemented with ATL OLE DB Provider templates so that the provider itself has small footprint even though the provider does not have many dependencies. You may use ADO or ADO.NET 2.0 to access data with MySQL OLE DB Provider

Why Do You Need MySQL OLE DB Provider

MySQL OLE DB Provider can be used to access MySQL database in your native code and .NET code as well. It has few dependency and is is very easy to use and very easy to deploy as well.

Most importantly, Cherry City Software MySQL OLE DB Provider is the best OLE DB Provider for MySQL database.

Features

The following are the features that this MySQL OLE DB Provider supports:

Transaction

Record scroll

Parameters - input and output

Bookmarks

Database Schema - tables and their columns

Extended error information

Multiple queries in one single execution*

Transparency - all scripts are transparent to MySQL engine

Command Preparation and Column Information on command object

IColumnsRowset**

Record Add/Delete/Update on the fly**

MySQL as linked server in SQL Server 2005**

The current release was tested with MySQL 4.1.x and 5.x

MySQL TIME is mapped to string. MySQL ENUM, SET and GEOMETRY data types are not supported.

* - If the SQL statements have parameters, each statement must be executed seperately.
** - Not applicable if MySQL version is 4.1.x or older

MySQL Provider Connection String

Provider=MySQL Provider; Data Source=MySQLServerIP; User ID =MyID; Password=MyPassword; Initial Catalog=DatabaseName;

Where

MySQLServerIP is IP address of MySQL database server. If MySQL Server is listening on a port other than the default port (3306), you may specify the port number after the server name with a comma delimitor. Here is an example: 127.0.0.1,6306

MyID/MyPassword are the credentials for the user to gain access to MySQL databases

DatabaseName is the database name on the MySQL Server that you want to connect to

Working with MySQL OLE DB Provider

Using MySQL OLE DB Provider is simple if you have the knowledge of ADO or ADO.NET. The sample code is intended to demostrate how to use MySQL OLE DB Provider. You may find sample code below that demostrate how to use MySQL OLE DB Provider with ADO and ADO.NET. The sample will show you how to use the following features:

Transaction

Parameter input and output

Database schema

Record scroll

Bookmark

Using MySQL OLE DB Provider in Visual Studio 2005 IDE

Using MySQL OLE DB Provider with Visual Studio 2005 IDE is very handy. You can readily add MySQL OLE DB Provider into Visual Studio 2005 IDE by adding Data connections via the OLE DB provider.

Using MySQL OLE DB Provider with SQL Server DTS

Refer to Using MySQL Provider to transfer MySQL data into SQL Server.

Activation

MySQL OLE DB Provider must be activated before you can use it. To activate it, you must download the provider and get your passcode from the Activator included in the package. Click here to get activation code.

Download

Update History:

Date Changes

2010-08-29 Improved performance on query execution such that column schema information is retrieved if and only if needed

2010-07-22 Fixed a defect with out join and show grants in linked server

2010-07-03 Fixed a defect with blob data type in the case such that the data size is bigger than 8000 bytes

2010-06-23 Corrected error message for registration failure

MySQL OLE DB Provider (Win32 Beta)
MySQL OLE DB Provider (Win32)
MySQL OLE DB Provider (Win64 Beta)
MySQL OLE DB Provider (Win64)
Provider VB Test source
C# Sample source code

Disclaim

This whole post here including the downloads is provided 'as-is', without any express or implied warranty. In no event will the author be held liable for any damages arising from the use of this software.

VB Sample Code

'define connection used through out in the form
Private mConn As ADODB.Connection

'//Synposis:
'//1. Create an ADO connection
'//2. Enable Read Data button if the table exists
Private Sub Form_Load()
    On Error GoTo trap
    Set mConn = New Connection
    'connect to MySQL server (localhost, port 3306), Database Name: test
    mConn.Open "Provider=OleMySql.MySqlSource.1; " _
        & " Data Source=localhost,3306; Initial Catalog=test", _
        "root", "myPassword"
    If DoesTableExist("AllFields") Then
        Me.Command3.Enabled = True
    End If
    Exit Sub
trap:
    MsgBox Err.Description
End Sub

'//Synposis:
'//1. Create a table named AllFields if it does not exist. If the table exits,
'//delete all records from the table.
'//2. Insert some records into the table
'//3. Update the records using parameters which makes it easy for
'//binary data manipulation
Private Sub Command1_Click()
    On Error GoTo trap
    On Error GoTo trap
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = mConn

    'delete all data from the table if table exists
    If DoesTableExist("AllFields") Then
        cmd.CommandText = "DELETE FROM AllFields;"
    Else 'create AllFields table if it does not exists
        cmd.CommandText = "CREATE TABLE AllFields" _
            & "(ID INTEGER PRIMARY KEY, Name VARCHAR(20), " _
            & "LongDoc MEDIUMTEXT, Image BLOB, Date5 DATE, " _
            & "datetime6 DATETIME, time6 TIME, " _
            & "timestamp7 TIMESTAMP, Year8 YEAR);"
    End If
    cmd.Execute
    cmd.CommandText = ""

    'insert data using multiple query statements
    Dim n As Long
    Dim I As Long
    For I = 0 To 300
        cmd.CommandText = cmd.CommandText _
            & "INSERT INTO AllFields (ID, Name, LongDoc, Date5, " _
            & "datetime6, time6, timestamp7, Year8) VALUES (" _
            & I & ", 'Sean D', 'this field can hold long long" _
            & " long long ... text string', '2005-6-10', " _
            & "'2005-6-12 09:01:10', '6:05:00', '2005-6-15 1:22:11', 2005);"
    Next I
    mConn.BeginTrans
    cmd.Execute n
    mConn.CommitTrans

    'update records with Parameters
    Dim BA(999) As Byte
    For I = 0 To 999
        BA(I) = (I + 1) Mod 256
    Next I
    Dim params(2) As Parameter
    Set params(0) = New ADODB.Parameter
    params(0).Type = adInteger 'integer
    params(0).Value = 1
    Set params(1) = New ADODB.Parameter
    params(1).Type = adBSTR      'string
    params(1).Value = "Sean Deng"
    Set params(2) = New ADODB.Parameter
    params(2).Type = adBinary
    params(2).Value = BA
    params(2).Size = 1000
    cmd.CommandText = "UPDATE AllFields SET Name=?, Image=? WHERE ID=?;"
    'add parameters according to commend text
    cmd.Parameters.Append params(1)
    cmd.Parameters.Append params(2)
    cmd.Parameters.Append params(0)
    cmd.Execute

    'using transaction
    params(0).Value = 2
    mConn.BeginTrans
    cmd.Execute
    mConn.RollbackTrans 'the Name is NOT updated when read back
    params(0).Value = 3
    mConn.BeginTrans
    cmd.Execute
    mConn.CommitTrans 'the Name is updated when read back

    Me.Command3.Enabled = True
    Set cmd = Nothing
    MsgBox "Done"
    Exit Sub
trap:
    MsgBox Err.Description
End Sub

'//Synposis:
'//Create a recordset to get all data from the table and present the
'//data in a data grid.
'//To view the binary data read back from the database, you may open
'//open a watch window in the debug mode
Private Sub Command3_Click()

    On Error GoTo trap
    Dim BA As Variant
    Me.MSHFlexGrid1.Clear
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM AllFields", mConn, adOpenStatic, adLockReadOnly
    If (rs.RecordCount > 3) Then
        rs.Move 3
        BA = rs("image").Value 'add BA to the watch window to view data
    End If

    Set Me.MSHFlexGrid1.Recordset = rs
    Exit Sub
trap:
    MsgBox Err.Description

End Sub

'//Synposis:
'//Clear all data from the data grid
Private Sub Command2_Click()
    Me.MSHFlexGrid1.Clear
End Sub

'//Synposis:
'//Check if the specified table exists. Return true if it does. Return false
'//otherwise.
Private Function DoesTableExist(ByVal sTable As String) As Boolean
    Dim rs As ADODB.Recordset
    Set rs = mConn.OpenSchema(adSchemaTables)
    While (Not rs.EOF)
        If UCase(rs("table_name")) = UCase(sTable) Then
            DoesTableExist = True
            rs.Close
            Set rs = Nothing
            Exit Function
        End If
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
End Function

'//Synposis: Create a stored procedure and call it
'
Private Sub SPCall()
    On Error GoTo trap
    Dim cmd As ADODB.Command
    Set cmd = New Command
    cmd.ActiveConnection = mConn

    cmd.CommandText = "DROP PROCEDURE IF EXISTS MyProc;CREATE PROCEDURE MyProc"
    cmd.CommandText = cmd.CommandText + "(param INT, OUT param1 INT)" _
                    & " BEGIN SET param1 = param*param; END;"
    cmd.Execute

    Dim param(1) As New ADODB.Parameter
    param(0).Direction = adParamInput
    param(0).Type = adInteger
    param(0).Value = -1000
    param(1).Direction = adParamOutput
    param(1).Type = adBSTR
    param(1).Value = ""
    param(1).Size = 40


    cmd.Parameters.Append param(0)
    cmd.Parameters.Append param(1)

    cmd.CommandText = "call MyProc(?, ?)"
    cmd.CommandType = adCmdText 'must use text command type
    cmd.Execute

    Exit Sub
trap:
    MsgBox Err.Description

End Sub

.Net Sample code

//OLEDB connection used throughout this class
private System.Data.OleDb.OleDbConnection m_conn
            = new System.Data.OleDb.OleDbConnection();

//On form load, establish an connection to MySQL database
//When a connection is established, enable button to
//allow user populate data
private void Form1_Load(object sender, EventArgs e)
{
    try
    {
        //establish a db connection
        this.m_Read.Enabled = false;
        this.m_SP.Enabled = false;
        m_conn.ConnectionString = @"Provider=OleMySql.MySqlSource.1;"+
            " Data Source=localhost,3306; Initial Catalog=test;" +
            " User ID=root; Password=MySQL";
        m_conn.Open();
        this.m_Create.Enabled = true;

        return;
    }
    catch (System.Data.OleDb.OleDbException ex)
    {
        MessageBox.Show(
            "Failed to open connection to MySQL database. Error Details:"
            + ex.Message);
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    this.m_Create.Enabled = false;
    this.m_Read.Enabled = false;
}

/// <summary>
/// Find if a table has been created by checking the table schema.
/// </summary>
/// <param name="sTable">sTable is the table name</param>
/// <returns>return true if found, false otherwise</returns>
private bool TableExist(string sTable)
{
    System.Data.DataTable schema =
        m_conn.GetOleDbSchemaTable(
        System.Data.OleDb.OleDbSchemaGuid.Tables, null);
    foreach (DataRow r in schema.Rows)
    {
        if (r.ItemArray.GetValue(2).ToString().ToLower() ==
            sTable.ToLower() &&
            r.ItemArray.GetValue(3).ToString().ToLower() == "table")
            return true;

    }
    return false;
}

//Create a test table if it does not exist. If it does exist,
//delete data if any. Then insert data into the test table.
private void m_Create_Click(object sender, EventArgs e)
{
    try
    {
        //create command object
        System.Data.OleDb.OleDbCommand cmd
            = new System.Data.OleDb.OleDbCommand();
        cmd.Connection = m_conn;

        //query string to create table
        cmd.CommandText = @"CREATE TABLE FieldTest" +
             "(ID INTEGER PRIMARY KEY, Name VARCHAR(20), " +
             "LongDoc MEDIUMTEXT, Image BLOB, Date5 DATE, " +
             "datetime6 DATETIME, time7 TIME, " +
             "timestamp8 TIMESTAMP)";

        //if test has been created, delete all data
        if (this.TableExist("FieldTest"))
            cmd.CommandText = "DELETE FROM FieldTest";
        cmd.ExecuteNonQuery();

        //insert data into FieldTest table
        cmd.CommandText = "INSERT INTO FieldTest " +
            "(ID, Name, LongDoc, Image, Date5, datetime6, Time7, TimeStamp8)"
            + " VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
        System.Data.OleDb.OleDbParameter[] oParams
            = new System.Data.OleDb.OleDbParameter[8];
        for (int i = 0; i < 8; i++)
        {
            oParams[i] = new System.Data.OleDb.OleDbParameter();
            cmd.Parameters.Add(oParams[i]);
        }


        //start transaction
        System.Data.OleDb.OleDbTransaction tran
            = m_conn.BeginTransaction();
        cmd.Transaction = tran;
        byte[] ba = new byte[1000];
        for (int i = 0; i < 260; i++)
        {
            oParams[0].Value = 100 + i;
            oParams[0].DbType = DbType.Int32;
            oParams[1].Value = "MyName " + i.ToString();
            oParams[2].Value = "A long long long long long long text";
            ba[i] = (byte)i;
            oParams[3].Value = ba;
            oParams[4].Value = System.DateTime.Now - new TimeSpan(i, 0, 0, 0);
            oParams[4].DbType = DbType.Date;
            oParams[5].Value = System.DateTime.Now
               - new TimeSpan(i, i % 24, i % 60, i % 60);
            oParams[5].DbType = DbType.DateTime;
            oParams[6].Value = new TimeSpan(i % 24, i % 60, i % 60);
            oParams[6].DbType = DbType.Time;
            oParams[7].Value = System.DateTime.Now;
            oParams[7].DbType = DbType.DateTime;
            cmd.ExecuteNonQuery();
        }
        //at last commit transaction
        tran.Commit();

        //test all time related fields
        DateTimeTest();

        MessageBox.Show("Done");
        m_Read.Enabled = true;
        m_SP.Enabled = true;
    }
    catch (System.Data.OleDb.OleDbException ex)
    {
        MessageBox.Show(ex.Message);
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

}

//Test date and time related data types
private void DateTimeTest()
{
    System.Data.OleDb.OleDbCommand cmd =
        new System.Data.OleDb.OleDbCommand();
    cmd.Connection = m_conn;
    if (!this.TableExist("TimeTest"))
    {
        cmd.CommandText =
            "CREATE TABLE TimeTest (date0 DATE, time1 TIME," +
            " dateTime2 DATETIME, ts3 TIMESTAMP)";
    }
    else
    {
        cmd.CommandText = "delete from timeTest";
    }
    cmd.ExecuteNonQuery();

    System.Data.OleDb.OleDbParameter[] oParams =
        new System.Data.OleDb.OleDbParameter[4];
    for (int i = 0; i < 4; i++)
    {
        oParams[i] = new System.Data.OleDb.OleDbParameter();
        oParams[i].Value = DateTime.Now;
        cmd.Parameters.Add(oParams[i]);
    }
    oParams[0].DbType = DbType.Date;
    oParams[1].Value = new TimeSpan(-80, 6, 2);
    oParams[1].DbType = DbType.Time;
    oParams[2].DbType = DbType.DateTime;
    oParams[3].DbType = DbType.DateTime;
    cmd.CommandText =
        "INSERT INTO TimeTest (date0, time1, dateTime2, ts3) values (?,?,?,?)";
    cmd.ExecuteNonQuery();

}
/// <summary>
/// Create an stored procedure and test input and output parameters
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void m_SP_Click(object sender, EventArgs e)
{
    try
    {
        //Create a stored procedure
        System.Data.OleDb.OleDbCommand cmd =
            new System.Data.OleDb.OleDbCommand();
        cmd.CommandText =
            "DROP PROCEDURE IF EXISTS MyProc;CREATE PROCEDURE MyProc" +
            " (param INT, OUT param1 INT, OUT Param2 DATE, OUT param3" +
            " DATETIME, OUT param4 TIME) BEGIN SET param1 = param*param;" +
            " SET param2='2000-10-10', param3='2001-12-31 01:02:03'," +
            " param4='9:8:7'; END;";
        cmd.Connection = m_conn;
        cmd.ExecuteNonQuery();

        //call stored procedure with input and output parameters
        cmd.CommandText = "call MyProc(?, ?, ?, ?, ?)";

        System.Data.OleDb.OleDbParameter[] oParams =
            new System.Data.OleDb.OleDbParameter[5];
        for (int i = 0; i < 5; i++)
        {
            oParams[i] = new System.Data.OleDb.OleDbParameter();
            oParams[i].Direction = ParameterDirection.Output;
            cmd.Parameters.Add(oParams[i]);
        }

        //setup input parameters
        oParams[0].Value = 10;
        oParams[0].DbType = DbType.Int32;
        oParams[0].Direction = ParameterDirection.Input;

        //setup output parameter types
        oParams[1].DbType = DbType.Int32;
        oParams[2].DbType = DbType.Date;
        oParams[3].DbType = DbType.DateTime;
        oParams[4].DbType = DbType.Time;

        cmd.ExecuteNonQuery();

        //run debug to view output values
        System.Diagnostics.Debug.WriteLine(oParams[1].Value.ToString());
        System.Diagnostics.Debug.WriteLine(oParams[2].Value.ToString());
        System.Diagnostics.Debug.WriteLine(oParams[3].Value.ToString());
        System.Diagnostics.Debug.WriteLine(oParams[4].Value.ToString());

        MessageBox.Show("Done");
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

}

/// <summary>
/// read data back from a FieldTest table and present it on a data grid
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void m_Read_Click(object sender, EventArgs e)
{
    try
    {

        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
        cmd.Connection = m_conn;
        cmd.CommandText = "select * from FieldTest";
        System.Data.OleDb.OleDbDataAdapter da =
            new System.Data.OleDb.OleDbDataAdapter();
        da.SelectCommand = cmd;
        DataTable dt = new DataTable();
        da.Fill(dt);
        HexDecBinding.Util.SetupGVColumns(m_DGVResult, dt);
        this.m_DGVResult.DataSource = dt;
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

}

http://cherrycitysoftware.com/ccs/Providers/ProvMySQL.aspx

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：SQLite OLE DB Provider

后一篇：C++ Builder 2007中应用数据库SQLite

新浪BLOG意见反馈留言板　欢迎批评指正