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).

Warning 

Be sure to keep the formatting the same or the examples may not work.

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.