Published: Friday, September 22, 2000
Using MySQL under Windows NT, Part 2
By Kerry Garrison
Read Part 1
In Part 1 we looked at what, exactly, mySQL is,
where to obtain it, and how to get some GUI tools for management. In this part we'll
look at accomplishing some standard database tasks, such as: granting access permissions,
creating a table, inserting data, etc.!
Getting Started
So now we have our user set up, now it's time to create a database table, populate some data, and try
getting some results.
- Create the database:
In the \mysql\bin directory you will find an application called winmysqladmin.
Using winmysqladmin, you can view tons of information about your MySQL installation, you
can also use it to create new databases.
For the purposes of this example, click on the Databases tab, right-click on the server name in the
left hand window to create a new database. Name your database dbtest.
- Setting up our test table:
You can use the command line tools to manage databases, tables, and users. I prefer using DBTools as
described above. When you create a new user using DBTools, it gives you an easy interface to grant
different permissions for each use to specific databases. The method for using the command line tool is
given below those of you who like to do it "the hard way".
- Grant rights to our test user:
If you wish to use a different user account than root (and you should) then your next step would be to
create the user account you want. You can either use the admin tools discussed later in this article,
or simply do it from the command line as shown here with the fictional account bubba:
D:\mysql\bin>mysql -uroot -proot_password
mysql> use mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO bubba@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO bubba@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
|
This sets up our user Bubba; as a full superuser who can connect to the server from
anywhere, but who must use a password some_pass to do so. Note that you must issue
GRANT statements for both bubba@localhost and bubba@"%" to
prevent the anonymous user created during the install will take precendence.
- Create the table:
mysql -ububba dbtest
mysql> create table linkdemo (
-> LinkName varchar(50),
-> LinkURL varchar(150)
-> );
Query OK, 0 rows affected (0.07 sec)
|
- Populate the table with some data:
Again, you can use either the command line tools or DBTools to import your data. To use DBTools, select
the database table and use the SQL Query tool to run the following queries. From the command line, you
can enter them by starting up the MySQL tool as follows:
D:\mysql\bin>mysql -ububba -psome_pass
INSERT INTO LinkDemo (LinkName,LinkURL) VALUES ('Yahoo','http://www.yahoo.com');
INSERT INTO LinkDemo (LinkName,LinkURL) VALUES ('Google','http://www.google.com');
INSERT INTO LinkDemo (LinkName,LinkURL) VALUES ('Alta Vista','http://www.altavista.com');
INSERT INTO LinkDemo (LinkName,LinkURL) VALUES ('InfoSeek','http://www.infoseek.com');
|
Next, create an ODBC connection to your table using the ODBC control panel. At this point your data is
ready for you to access via and standard ODBC connection.
The following ASP code snippet was originally used with a Microsoft Access database and required no
modifications to work with MySQL.
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "dbTest"
SQL_query = "SELECT * FROM Links;"
Set RS = MyConn.Execute(SQL_query)
WHILE NOT RS.EOF
Response.write "<a href=""" & rs("LinkURL") & _
""">" & rs("LinkName") & "</a><BR>"
RS.MoveNext
Wend
|
So there you have it, a fully functioning SQL Database server populated with data and returning the
data into and ASP page.
Summary
One further advantage of using MySQL is that it is available for multiple platforms. You may want to
develop your website using Active Server Pages on a Windows NT/2000 platform but don't want the
financial hit of having to purchase a second copy on NT to run the database. By putting the MySQL database
engine on an inexpensive Linux server, your cost savings could really add up. The same Windows based
administration tools such as DBTools described above, will still connect to a Unix based MySQL server
making administration identical regardless of platform.
In the short time I have been using MySQL, I have grown to like it a lot. It is fast, reliable, and
with the visual management tools, very easy to work with. Being designed from the beginning for speed,
it actually takes up a surprisingly small amount of resources on a server allowing some sites to run
both MySQL and their web server on a single machine that would normally require two servers when running a
larger database.
By Kerry Garrison
Kerry Garrison is the Senior Sales Engineer for Dedicated Hosting Products at XO Communications.
