Working With MySQL Database : Configuring And Using MySQL

• 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;
• 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:

• Install MyODBC Driver in the client machine. (How to Install MyODBC)
• 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).