To read the article online, visit http://www.4GuysFromRolla.com/webtech/092200-1.2.shtml

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.

      Screenshot of WinMySQLAdmin!

      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.


  • Article Information
    Article Title: Using MySQL under Windows NT, Part 2
    Article Author: Kerry Garrison
    Published Date: Friday, September 22, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/092200-1.2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers