Java开发网 - ZT Using MySQL in the Win32 Environment

Topic: ZT Using MySQL in the Win32 Environment

1.ZT Using MySQL in the Win32 Environment

Posted by: merlin45
Posted on: 2003-04-21 16:56

MySQL might be just the thing to bridge the gap, when Access is too little and SQL is too much

By Kenneth Fraser
[26/11/2002]

MySQL is a relational database created by MySQL AB and available 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 is now in use by some major websites (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 u

" There are pros and cons to using MySQL, and it is fair to say that every piece of software has its strengths and weaknesses, but overall MySQL holds its own "

sed 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 be 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, lets 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 codingagainst either Access, MS SQL Server or Oracle.
Anyone who has tried to build anything more than a pilot system on Access
will know that it just doesn't scale. It was never intended to be used for
the back end of a web site and quickly runs out of steam as load increases.
Those of you building solutions against SQL Server & 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, having decent
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 commercial
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, and it is fair to say that every piece of software has its strengths and weaknesses, but overall MySQL holds its own.

MySQL does, however, have some shortcomings compared to the big commercial
products, if you're building fairly straightforward applications you can
probably work around them. However, if you're into more advanced development methods you may find its features limiting.
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 bring row level locking as standard (currently locking is at table level) as well as transactions and SSL connections to the server daemon and few other additions.
More information on features yet to be added can be found here ttp://www.mysql.com/products/mysql-4.0/index.html.

Deciding wether to implement MySQL as your 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.

The direct URL to the download page is http://www.mysql.com/downloads/download.php?file=Downloads/MySQL-3.23/mysql-3.23.51-win.zip

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.

The direct link to the NT/Win2K mirror list is http://www.mysql.com/downloads/download.php?file=Downloads/MyODBC/myodbc-2.50.39-nt.zip

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.

Last, get some documentation. The MySQL manual is available in a number of formats including a self contained Windows Help file :

http://www.mysql.com/downloads/download.php?file=Downloads/Manual/manual.hlp.zip

Aside from 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. So now you've got MySQL installed 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 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, Iive created 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 <tablename>;" 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 you're 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 the user's user-name, email address and full name, we'll also add a 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 :

+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| ID | mediumint(9) | | PRI | NULL | auto_increment |
| userName | varchar(20) | | | | |
| email | varchar(50) | YES | | NULL | |
| fullName | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+

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 it 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 indices 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.

SCREENSHOT mysql_odbcdialog

Next, we need to see if it all works.

Open up notepad or your favourite 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 of which 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 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.