MySQL is a relational database created by MySQL AB and available for free under GPL.
Most of you have probably heard of MySQL, and many may have written it off as being a UNIX-only product or doubted the ability and functionality of a piece of "free" software.
Over the past couple of years, MySQL has gained enormous popularity in the Web development world, and it is now used by some major Web sites (e.g. Yahoo's finance pages and NASA's NAIS. See http://www.mysql.com/articles/user_stories.html).
This article sets out to show that MySQL can be used on the Win32 platform, and that it may well be the alternative solution to Access MDB files or MS SQL Server that you've been looking for.
I'm writing this article under the assumption most readers will be running Windows 2000.
My example code is simple VBScript intended to run under Windows Scripting Host (WSH), but it can easily be used in an ASP page or adapted to Visual Basic.
Before we get into the nitty gritty of using MySQL, let's take a look at why you should consider MySQL.
It's probably fair to say that the majority of developers using the IIS platform are coding against either Access, MS SQL Server, or Oracle.
Anyone who has tried Access to build more than a pilot system will know that it just doesn't scale. It was never intended to be used for the back end of a Web site, and it quickly runs out of steam as load increases.
Those of you building solutions against SQL Server and Oracle are no doubt wincing at the licensing costs and possibly have some grumbles about performance.
MySQL offers a viable solution for both of these issues. It offers scalable enterprise level performance at a cost you can't argue with.
In most cases MySQL is free, although they ask that you consider taking out a support contract to help keep MySQL going.
And even if you are building a product that does require a commerical license, currently a single server license costs at most a "whopping" $200 per MACHINE (regardless of processes, processors or connections).
Detailed information and pricing can be found at http://www.mysql.com/support/arrangements.html
Take a look at the performance benchmarks on the MySQL site (http://www.mysql.com/information/benchmarks.html), and you'll see how MySQL stacks up against Access, SQL Server, and a host of other database solutions.
There are pros and cons to using MySQL. Every piece of software has its strengths and weaknesses, but overall, MySQL holds its own.
MySQL does, however, have a few shortcomings compared to the big commercial products, but nothing that can't be worked around.
Stored procedures are probably the most obvious missing feature, and these are a good way off in the future, along with triggers.
The upcoming version, 4.0.0, will have row level locking as a standard (currently locking is at table level), transactions and SSL connections to the server daemon, and a few other additions.
More information on features yet to be added can be found here http://www.mysql.com/products/mysql-4.0/index.html.
Deciding whether to implement MySQL as a backend database piece is really down to your specific application needs.
Downloading and Installing
Everything you need to get your database server up and running is available from http://www.mysql.com.
Current verisons are listed on the right-hand side of the page. The current stable version is 3.23.51. Click on the link for it, and you'll be taken to the download area. Scroll down to the Win32 area then download the Zip file from your closest mirror.
Next, grab the MyODBC download, follow the products link from the top of the page, select MyODBC from the left menu, then click the link at the end of the page. For now, stick with the stable version 2.50.39, select the version appropriate for your environment, then pick your nearest mirror.
Both are pretty standard installs, extract the zip to a temporary folder, open the folder, then run the Setup.exe file.
For now, click through with the default settings.
I've installed to c:\mysql and c:\myodbc for simplicity.
Note that if you change the install directory for MySQL, you may need to do some hand editing of the config files before it will run. This is explained in one of the installation dialog boxes.
Lastly get some documentation. The MySQL manual is available in a number of formats including a self contained Windows Help file:
Besides providing information on setting up and using MySQL, it's also a handy SQL language reference.
There is further documentation on the site for MyODBC; you may wish to download that too.
Ok. You've installed MySQL, but nothing is running right now.
There are a number of graphical and Web-based admin tools out there for MySQL, and it also comes with a command line utility.
The install comes with two graphical admin tools MySQLAdmin and MySQLManager; both can be found in the bin subdirectory of the mysql install folder.
Run WinMySqlAdmin.exe, and we'll set up a database.
First off, you'll be asked for a user account and password. This will be your admin account for the server.
(If you close the tool down, you should now see a little traffic light icon in your systray, clicking it gives you the option to bring back up the admin tool.)
Have a look around at the various tabs, then move over to the Database tab, and we'll create a new database.
Under your machine name you'll see two databases, "mysql" and "test".
The mysql database is the system database that keeps track of databases, users and permissions etc.
Right click your computer's name and fill in the dialog box with the name of your new database. I'm going to create one called "webuser".
Next we'll use the command line tool to create a table in our new database.
So, open up a command box and cd into the mysql\bin folder then run the command "mysql".
You should see the "mysql>" prompt.
(enter Quit to exit at any time)
Note that when using the command line interface, you need to terminate statements with the ";". This allows you to enter multi-line commands.
There is also command history available by using the up and down cursor keys.
The command "show databases;" will list all the available databases, including "webuser", the one we just added.
You need to tell mysql which database you want to work on, so enter "use webuser;" and you should see "Database changed" come back.
At this point you can use "show tables;" to list all the tables in the database and "describe ;" to list the fields in a particular table.
Of course, we don't have any tables in our new database yet, so let's create some.
If your familiar with SQL, there's nothing new or odd here, just CREATE TABLE and away you go.
For those less experienced with SQL commands, you may want to fall back on one of the GUI tools or spend some time reading up on the CREATE statement in the MySQL manual or any other SQL language reference you have.
For our simple database, we'll add a table to hold users user-name, email address and full name, we'll also add an unique ID to the table.
Enter the command
CREATE TABLE userlist (ID mediumint(9) NOT NULL auto_increment PRIMARY KEY, userName VARCHAR(20) NOT NULL, email VARCHAR(50), fullName VARCHAR(255));
Running "describe userlist;" shows what we just set up:
Lastly, we need a user account to be able to connnect up to this database from an application. Sure you could use the administrator account, but it makes more sense to get in the habit of setting up a more restrictive account from the outset.
You might want to check out the MySQL manual for more detailed information on users and privileges. Creating and managing users and their privileges is mainly handled by the GRANT command. We'll set up an account called "webdev" with a password of "zyx245bca" and just give the ability to read and write to the database.
Issue the command
GRANT SELECT,INSERT,UPDATE,DELETE ON webuser.* TO webdev@'%' IDENTIFIED BY 'zyx245bca';
The .* after our database name indicates we're setting privileges to all the tables and idicies et al belonging to the Webuser database.
You can take this further to implement custom privileges down to the column level.
The '%' means the account webdev can log in from any host. You can lock this down further to a particular host using "webdev@somehost.com" instead. For now, we'll use the % wildcard to ease any connectivity problems.
Now we have a small database with a single table and a user account that can access it. Next, we need to see how to connect up to it and do some work.
As an aside, when you drop out of the command line, try running the mysqldump command with our new database name as a parameter "mysqldump webuser".
It'll give you a nice dump of the database definitions and, if you want it, any data present. This can be redirected to a file and used as a backup or a way to easily set the database up again on another server, even a UNIX box.
The first step in connecting an application to our new database is to set up a DSN. You need to have the MyODBC driver installed in order to do this.
Bring up the ODBC Data Source Administrator (it's under Control Panel->Administrative tools).
Depending on your needs, set up a User, System or File DSN, selecting MySQL as the driver for it.
In the code below, I've set it up as "MyWebUser".
To check the IP address that the MySQL server is listening on, bring up the MySQLAdmin tool and look in the evironment section.
Next, we need to see if it works.
Open up notepad or your favorite text editor, paste in the code below, save it as "test.vbs", then run it (you need Windows Scripting Host installed and enabled to do so. If you don't have it or prefer to work under IIS just stick it in an ASP file and run it that way).
dim dbConnn
set dbconn = createobject("ADODB.Connection")
dbConn.open "DSN=MyWebUser","webdev","zyx245bca"
dbconn.execute "insert into userlist (username,email,fullName)_
values ('kenfraser','ken@someplace.com','Kenneth Fraser');"
dbconn.close
set dbconn = nothing
If no errors pop up, then all is well, and if you run "SELECT * FROM userlist;" in the mysql command window you should see:
mysql> select * from userlist;
+----+-----------+-------------------+---------------+
| ID | userName | email | fullName |
+----+-----------+-------------------+---------------+
| 1 | kenfraser | ken@someplace.com | Kenneth Fraser |
+----+-----------+-------------------+---------------+
1 row in set (0.00 sec)
Here's a slightly more complex example showing how to retrieve the autonumbered index column:
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
dim dbConnn
dim rsTest
dim newID
set dbconn = createobject("ADODB.Connection")
set rsTest = createobject("ADODB.Recordset")
dbconn.cursorlocation = adUseserver
dbConn.open "DSN=MyWebUser","webdev","zyx245bca"
dbconn.execute "insert into userlist (username,email,fullName) _
values ('wolf','wolf@myden.com','Mr Wolf);"
rsTest.open "SELECT CONCAT(LAST_INSERT_ID(),' ');",dbConn,adOpenForwardOnly ,adLockReadOnly
if not rsTest.eof then
newID = cint(trim(rsTest.fields(0)))
msgbox "New RecordID = [" & cstr(newID) & "]"
else
msgbox "We have a problem."
end if
rsTest.close
dbconn.close
set rsTest = nothing
set dbconn = nothing
In your own code, you can use SQL commands as I've done in my examples, or you can use ADO syntax if you're happier with that.
From here on in it's pretty much plain sailing as far as database code goes.
Incidentally, if you are looking for extreme performance, there is a C++ client library available called MySQL++. Details can be found on the MySQL site in the API section.
Conclusion
I hope you can now see that MySQL IS usable under Win32, and I encourage you to at least check it out.
For my own part, I began playing around with MySQL as part of a small project, just to see if it lived up to the hype.
I'm still using it.
And if you want to explore further, there's a wealth of documentation, articles and books out there on MySQL.
About the Author
Ken Fraser has been involved with computers for nearly 20 years and has been
working professionally for the past 11 years. He gained the majority of his experience building solutions for the travel industry in the UK. His projects have ranged from desktop apps for small agencies to back-end services for .com travel sites. He's built solutions on both UNIX and Windows platforms in a variety of languages and technologies. In February this year he emmigrated to Mid West America and has recently started work at a software house in Mishawaka, IN building solutions for the mortgage industry. Ken can be reach at kenfraser@hotmail.com
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
There is never a guarantee of project success when endeavoring to build a sophisticated application. However, there are established steps to follow that will ensure a clear, concise scope, support for the team involved, and a solid opportunity for successful deployment. [Read This Article][Top]
If your SQL Server is exposed to the Internet, then hackers are probing it. This article shows how to secure a SQL Server database that's being used with a Web application [Read This Article][Top]
Feel intimidated by .NET? This article by Rob Chartier is designed to ease any level VBScripter (ASP) into .NET by clarifying some OOP concepts. [Read This Article][Top]
A few members of the 15 Seconds discussion list talk about the proper way to use methods in order to prevent ADO object errors.
[Read This Article][Top]
Solomon Shaffer explores the life cycle of an ASP.NET page from initialization to unloading. He also explains the various methods to override ASP.NET server-side events. [Read This Article][Top]
Rob Chartier creates a simple portable and reusable address book in .NET to demonstrate the power of N-tier application architecture. Complete source code included! [Read This Article][Top]
Learn about N-tier application architecture and realize that developing with multiple layers produces a flexible and reusable application for distribution to any number of client interfaces. [Read This Article][Top]
Learn about N-tier application architecture and realize that developing with multiple layers produces a flexible and reusable application for distribution to any number of client interfaces. [Read This Article][Top]
Ed Myers' article shows several ways to use a SQL calculated field and the ORDER BY clause to arrange a recordset in random order. A simple tool is provided for verifying that the results are uniformly random. A technique for bubbling records with certain attributes to the top of an otherwise randomized list is also shown. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
