Configuring and Using MySQL from a Windows Computer System.

The article provides a detailed overview of configuring, connecting, and working with MySQL database from a Windows Operating System. The Windows computer may be running any Windows OS such as 9x, 2000, or XP. The MySQL database is available free, and offered under GPL. In the example provided, the MySQL server is running on a separate Redhat 9 (Linux) Operating System.

1. Server Configuration.

   1. How to install MySQL database.

   2. Granting permission for a username and password. 

2. Client Configuration.

   1. How to install MyODBC Driver.

   2. Configuring the MyODBC DSN on Windows.

   3. DSN-less connection.

   4. Example program in VB.

   5. Viewing the stored data using Telnet.

Establishing a Remote Connection to Server System from Client Systems:  

 On server system, follow these steps: (Configuration)  

 How to install MySQL database:

1.  MySQL database Open Source. You can find the newest version at                http://dev.mysql.com/downloads/

2. MySQL is installed during OS install itself. If not installed, you can download latest version of MySQL at the above URL and install the same. The installation is simple and self explanatory.

Granting permission for a username and password:

1. Now, you need to setup a Username and Password for accessing MySQL database. For this purpose, you use GRANT statement to set up an account with a username of demo that can connect from a client system using a password of demo:    
   GRANT ALL PRIVILEGES ON *.* TO 'demo'@'%' IDENTIFIED BY 'demo' WITH GRANT OPTION;

2. The GRANT statement grants all privileges to user 'demo' for connecting from client using the password 'demo'. To execute this statement, you should be either root on server system or another user who has appropriate privileges.  

 On client system, follow these steps: (Configuration)  

 Installing MyODBC Driver:

1.  Install MyODBC Driver in the client machine. (How to Install MyODBC)  
   

2.  Configure a MyODBC DSN (Data Source Name) using the following connection parameters: 

DSN= myodbc1

SERVER or HOST=IP address of server system or Host name

DATABASE=test (The default database or an appropriate one)

USER=demo

PASSWORD=demo  

The connection string for MyODBC 3.51 using DSN-Oriented connection is as follows :

ConnectionString = "DSN=myodbc1;"

DSN-less connection:

The connection string for MyODBC 3.51 using DSN-Less connection is as follows:

ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=IP address or\ Hostname; DATABASE=test;USER=demo;PASSWORD=demo;OPTION=3;"              

 Note: Check whether you are able to access server from the client machine by using ping or other means. If you are not able to reach server, check your network or Internet connections.  

 Example:( Download Example)

This section contains simple example of the use of MySQL ODBC 3.51 Driver with ADO(ActiveX Data Object).

 Creating the form and coding in Visual Basic:

 1. Create a Form as follows,

 Note: Add Microsoft ActiveX Data Objects 2.0 Library Reference to the form

Write the following code for the above form:

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim fld As ADODB.Field

Dim sql As String

Dim cname As String

Dim roll As Integer

Private Sub cmdAdd_Click()

cname = txtName.Text

roll = txtRollNo.Text

'insert

conn.Execute "INSERT INTO candidate(name,rollno) values('" & cname & "'," & roll & ")"

txtName.Text = ""

txtRollNo.Text = ""

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM candidate", conn

Debug.Print "-----------------------"

rs.MoveFirst

For Each fld In rs.Fields

Debug.Print fld.Name,

Next

Debug.Print

Do Until rs.EOF

For Each fld In rs.Fields

Debug.Print fld.Value,

Next

rs.MoveNext

Debug.Print

Loop

End Sub

Private Sub Form_Load()

'connect to MySQL server using MySQL ODBC 3.51 Driver  

Set conn = New ADODB.Connection  

'Configure the Driver name, IP address of the Server or Host name, Database name, username and password according to the configuration in your system

'for DSN-less Connection use the following connection string

conn.ConnectionString = "DRIVER=MySQL ODBC 3.51 Driver;" & "SERVER=192.168.0.200;" & "                   DATABASE=test;" & "UID=demo;PWD=demo; OPTION=3"  

'for DSN-oriented Connection use the following connection string

'conn.ConnectionString = "DSN=myodbc1;"

conn.Open

'create table

conn.Execute "DROP TABLE IF EXISTS candidate"

conn.Execute "CREATE TABLE candidate(name varchar(20),rollno int)"

End Sub

 'Note: Compile and run the project, add some data .  

 Viewing the stored data using Telnet:

      1)      Open Telnet

2)      Connect to the Server machine

3)      Enter the login and password of the server machine

4)      After getting the prompt enter "mysql -h [host name or IP address] -u [User name] -p [Password]"

5)      Select the database as “test” using the query

USE test;

 6)   Enter the select query

 “Select * from candidate” in the mysql prompt

       7)   The data will be displayed as follows:

             name              rollno

 Sachin                   1

 Tina                      2

 Bijju                      3

 Revan                   4

 Jim                        5

5 rows in set (0.00 sec)  

The Telnet window appears as shown below:

How to Install MyODBC Driver

MyODBC works on Windows 9x, Me, NT, 2000, XP, and 2003, and on most Unix platforms.

MyODBC is Open Source. You can find the newest version at http://dev.mysql.com/downloads/connector/odbc/.

Normally, MyODBC is needed to be installed only on Windows machines. MyODBC is needed for Unix only if you have a program like ColdFusion that is running on a Unix machine and uses ODBC to connect for database access.  

Server Machine Configuration:

RedHat Linux with MySQL 3.23.54

Client Machine Configuration:

Windows 98 with MySQL ODBC 3.51 Driver and Service pack 2

Configuring a MyODBC DSN on Windows

To add and configure a new MyODBC data source on Windows, use the ODBC Data Source Administrator. The ODBC Administrator updates your data source connection information. 

To open the ODBC Administrator from the Control Panel:

To add a data source on Windows:

Note: You can also test whether your settings are suitable for connecting to the server using the button Test. This feature is available only for the MyODBC 3.51 driver. A successful test results in the following window: