Mastering MySQL 4 [Electronic resources] نسخه متنی

Using ODBC

The following examples demonstrate inserting a record into and selecting records from a MySQL database via ODBC in different programming environments. The first examples show the connection being made directly, and the DAO example shows a connection being made through a data source.

To get these examples to work, you need MyODBC installed, a preinstalled DSN (for the DAO example only—see the earlier "Setting Up a Data Source on Windows" section), and the correct environment (.NET, Visual Basic, and so on).

A Simple .NET VB Example

Listing H.1 uses VB.NET and ODBC to connect to a MySQL server, insert a record, and select and print the results. To compile the code, you'll need to specify settings appropriate for your .NET environment. The following is an example (the pause is just to make the compile options visible):

set path=%path%;C:\WINNT\Microsoft.NET\Framework\v1.0.3705–
C:\WINNT\Microsoft.NET\Framework\v1.0.3705\csc /t:exe–
/out:odbc_cnet.exe odbc_cnet.cs /r:"C:\Program–
files\Microsoft.NET\Odbc.Net\Microsoft.Data.Odbc.dll"–
pause

Listing H.1: dbnet.vb

Imports Microsoft.Data.Odbc
Imports System
Module mysql_vbnet
Sub Main()
Try
'Set the arguments for connecting to a MySQL firstdb database–
with MyODBC 3.51
Dim MySQLConnectionArgs As String = " 
DRIVER={MySQL ODBC 3.51 Driver};–
SERVER=www.testhost.co.za;DATABASE=firstdb;UID=guru2b;–
PASSWORD=g00r002b;OPTION=0"
'Open the ODBC connection and ODBC command
Dim MySQLConnection As New OdbcConnection(MySQLConnectionArgs)
MySQLConnection.Open()
Dim MySQLCommand As New OdbcCommand()
MySQLCommand.Connection = MySQLConnection
'Insert a record into the customer table
MySQLCommand.CommandText = "INSERT INTO customer–
(first_name, surname) VALUES('Frank', 'Weiss')"  
MySQLCommand.ExecuteNonQuery()  
'select a record from the customer table, return the results,
'loop through the results displaying them
MySQLCommand.CommandText = "SELECT id,first_name
,surname FROM customer"
Dim MySQLDataReader As OdbcDataReader
MySQLDataReader = MySQLCommand.ExecuteReader
While MySQLDataReader.Read
Console.WriteLine (CStr(MySQLDataReader("id")) & ":" &–
CStr(MySQLDataReader("first_name")) & " " & 
CStr(MySQLDataReader("surname")))
End While
'If theres an ODBC Exception, catch it
Catch MySQLOdbcException As OdbcException 
Console.WriteLine (MySQLOdbcException.ToString)
'If there a program exception, catch it
Catch AnyException As Exception
Console.WriteLine (AnyException.ToString)
End Try
End Sub
End Module

A Simple .NET C# Example

Listing H.2 uses C# .NET and ODBC to connect to a MySQL server, insert a record, and select and print the results.

To compile the code, you'll need to specify settings appropriate for your .NET environment. The following is an example (the pause is just to make the compile options visible):

C:\WINNT\Microsoft.NET\Framework\v1.0.3705\csc /t:exe–
/out:odbc_cnet.exe odbc_cnet.cs /r:"C:\Program–
files\Microsoft.NET\Odbc.Net\Microsoft.Data.Odbc.dll"
pause

Listing H.2: dbnet.cs

using Console = System.Console;
using Microsoft.Data.Odbc;
namespace MyODBC {
class MySQLCSharp {
static void Main(string[] args) {
try { 
// Set the arguments for connecting to a MySQL firstdb database–
with MyODBC 3.51
string MySQLConnectionArgs = "DRIVER={MySQL ODBC 3.51 Driver};–
SERVER=www.testhost.co.za;DATABASE=firstdb;UID=guru2b;–
PASSWORD=g00r002b;OPTION=0";
// Open the ODBC connection and ODBC command
OdbcConnection MySQLConnection = new OdbcConnection
(MySQLConnectionArgs);
MySQLConnection.Open();
// Insert a record into the customer table 
OdbcCommand MySQLCommand = new OdbcCommand("INSERT INTO–
customer (first_name, surname) VALUES('Frank', 'Weiss')",–
MySQLConnection);
MySQLCommand.ExecuteNonQuery();
// select a record from the customer table, return the results,
// loop through the results displaying them 
MySQLCommand.CommandText = "SELECT id, 
first_name, surname FROM customer";                
OdbcDataReader MySQLDataReader;
MySQLDataReader =  MySQLCommand.ExecuteReader();
while (MySQLDataReader.Read()) {
Console.WriteLine(" + MySQLDataReader.GetInt32(0) + ":" +–
MySQLDataReader.GetString(1) + " " + MySQLDataReader.GetString(2));
}
// Close all resources
MySQLDataReader.Close();
MySQLConnection.Close();
}
// If theres an ODBC Exception, catch it
catch (OdbcException MySQLOdbcException) {
throw MySQLOdbcException;
}
}
}
}

A Simple ADO VB Example

Listing H.3 uses VB and ADO to connect to a MySQL server via ODBC, insert a record (both directly and through adding to a result set), and select and print the results.

Listing H.3: dbado.vb

Private Sub MySQLADO()
Dim MySQLConnection As ADODB.Connection
Dim Results As ADODB.Recordset
Dim SQLQuery As String
'Open a connection using ADODB and set the connection string
'for connecting to a MySQL firstdb database with MyODBC 3.51
Set MySQLConnection = New ADODB.Connection
MySQLConnection.ConnectionString = 
"DRIVER={MySQL ODBC 3.51 Driver};–
SERVER=www.testhost.co.za;DATABASE=customer;UID=guru2b;–
PWD=g00r002b;OPTION=0"  MySQLConnection.Open
Set Results = New ADODB.Recordset
Results.CursorLocation = adUseServer
'There are two common ways of inserting - 
the first is the direct insert
SQLQuery = "INSERT INTO customer (first_name, surname) VALUES–
('Werner', 'Christerson')"
MySQLConnection.Execute SQLQuery
'The second way of inserting is to add to a result set using the
'AddNew method. First return a result set
Results.Open "SELECT * FROM customer",
 MySQLConnection,–
adOpenDynamic, adLockOptimistic
Results.AddNew
Results!first_name = "Lance"
Results!surname = "Plaaitjies"
Results.Update
Results.Close
'select a record from the customer table, return the results,
'loop through the results displaying them
Results.Open "SELECT id, first_name, 
surname FROM customer", MySQLConnection
While Not Results.EOF
Debug.Print Results!id & ":" & 
Results!first_name & " " & Results!surname
Results.MoveNext
Wend
Results.Close
MySQLConnection.Close
End Sub

To gain access to the ADO 2.0 objects in Visual Basic, set a reference to the ADODB type library contained in MSADO15.DLL. It appears in the References dialog box (available from the Project menu) as Microsoft ActiveX Data Objects 2.0 Library.

The code needs to appear inside a form for the Debug.Print method to work. Alternatively, you can change it to MsgBox to make the code executable.

A Simple RDO VB Example

Listing H.4 uses VB and RDO to connect to a MySQL server via ODBC, insert a record, and select and print the results. Visual Basic still supports RDO, but you may want to use the newer ADO instead.

Listing H.4: dbrdo.vb

Private Sub MySQLRDO()
Dim Results As rdoResultset
Dim MySQLConnection As New rdoConnection
Dim SQLQuery As String
'Open a connection using RDO and set the connection string
'for connecting to a MySQL firstdb database with MyODBC 3.51
MySQLConnection.Connect = "DRIVER={MySQL ODBC 3.51 Driver};–
SERVER=www.testhost.co.za;DATABASE=firstdb;UID=guru2b;–
PWD=g00r002b;OPTION=0"
MySQLConnection.CursorDriver = rdUseOdbc
MySQLConnection.EstablishConnection rdDriverNoPrompt
'There are two common ways of inserting -
 the first is the direct insert
SQLQuery = "INSERT INTO customer (first_name, surname) VALUES–
('Lance', 'Plaaitjies')"
MySQLConnection.Execute SQLQuery, rdExecDirect
'The second way of inserting is to add to a result set using the
'AddNew method. First return a result set
SQLQuery = "SELECT * FROM customer"
Set Results = MySQLConnection.OpenResultset(SQLQuery, rdOpenStatic,–
rdConcurRowVer, rdExecDirect)
Results.AddNew
Results!first_name = "Werner"
Results!surname = "Christerson"
Results.Update
Results.Close
'select a record from the customer table, return the results,
'loop through the results displaying them
SQLQuery = "select * from customer"
Set Results = MySQLConnection.OpenResultset(SQLQuery, rdOpenStatic,–
rdConcurRowVer, rdExecDirect)
While Not Results.EOF
Debug.Print Results!id & ":" & 
Results!first_name & " " & Results!surname
Results.MoveNext
Wend
'Free the result set, and the connection
Results.Close
MySQLConnection.Close
End Sub

To gain access to the RDO 2.0 objects in Visual Basic, set a reference to the RDO type library contained in MSRD020.DLL. It appears in the References dialog box (available from the Project menu) as Microsoft Remote Data Objects 2.0. The code needs to appear inside a form for the Debug.Print method to work. Alternatively, you can change it to MsgBox to make the code executable.

A Simple DAO VB Example

Listing H.5 uses VB and DAO to connect to a DSN via ODBC, insert a record (both directly and through adding to a result set), and select and print the results. Visual Basic still supports DAO, but you may want to use the newer ADO instead.

Listing H.5: dbdao.vb

Private Sub MySQLDAO()
Dim Works As Workspace
Dim MySQLConnection As Connection
Dim Results As Recordset
Dim SQLQuery As String
'Open a workspace using DAO and set the connection string
'for connecting to a DSN MySQL firstdb database with MyODBC 3.51
Set Works = DBEngine.CreateWorkspace("MySQLWorkspace", "guru2b",–
"g00r002b", dbUseODBC)
Set MySQLConnection = Works.OpenConnection("MySQLConn",–
rdDriverCompleteRequired, False, "ODBC;DSN=MyDAO")
'There are two common ways of inserting -
 the first is the direct insert
SQLQuery = "INSERT INTO customer (first_name, surname) VALUES–
('Lance', 'Plaaitjies')"  MySQLConnection.Execute SQLQuery
'The second way of inserting is to add to a result set using the
'AddNew method. First return a result set
Set Results = MySQLConnection.OpenRecordset("customer")
Results.AddNew
Results!first_name = "Werner"
Results!surname = "Christerson"
Results.Update
Results.Close
'Read customer table
Set Results = MySQLConnection.OpenRecordset("customer", dbOpenDynamic)
While Not Results.EOF
Debug.Print Results!id & ":" & 
Results!first_name & " " & Results!surname
Results.MoveNext
Wend
Results.Close
MySQLConnection.Close
Works.Close
End Sub

To gain access to the DAO objects in Visual Basic, set a reference to the DAO type library contained in DAO360.DLL. It appears in the References dialog box (available from the Project menu) as Microsoft DAO 3.6 Object Library.

The code needs to appear inside a form for the Debug.Print method to work. Alternatively, you can change it to MsgBox to make the code executable.

This example requires a DSN to be set for it to work.