MySQL probably is the most popular open source database server. To learn more about MySQL Database server, please visit MySQL Website.
MySQL library comes with some API's. You may download MySQL Library and its source code from MySQL Website. The MySQL OLE DB Provider is a thin wrapper of those MySQL library Version 5.0 API's. This provider is being implemented with ATL OLE DB Provider templates so that the provider itself has small footprint even though the provider does not have many dependencies. It only depends on MySQL library along with Windows OS runtime. 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 and it does NOT require you to open your source code.
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 The current release was tested with MySQL 4.1.x and 5.0 and only support Western European charset.I am having difficulties to setup the server to support Unicode. If you have any ideas,please give me your advise. 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.
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
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
