12.2.2005: Thanks for corrections and improvements to: Joel Kemp, Damian Harrigan, Daniel Ranells, Damian Harrigan, Sukhvir Dhamrhat, and many other class members. (I've never been able to take notes while speaking.)
Release: 1.1 beta: Friday morning, Dec. 2. The collaborative learning & open source debugging models have functioned superbly. I had it right, apparently, but tiny missing pieces can drive any of us unstable. Corrections, explanations, and extensions in green.
12.2.2005 10:30AM. I have just run the tutorial, as modified, on my new Dell, with no problems. PLEASE try it and report success or failure. In case of failure, first try copying the full error message into Google. In many cases recently this has led to a forum where--with some scrolling--the solution is to be found. One student who reported an error subsequently wrote back and said that he had missed one character, or something, in the tutorial. Another student suggested that there might be a non-printing character coming from the website, that messes up some command. I can't duplicate that, and I've looked at the HTML and don't see likely culprits.
Release: 1.0 beta: 12:30PM Sunday, November 20: it's been proofread,
sort of, but not tested by anybody but me. Send bug reports! (See
note below on open source.)
This is intended to get you over the startup details, and point out a few essentials to getting Java programs using the MySQL JDBC driver to work. It is a pleasure to acknowledge my debt to the great tutorial from the MySQL organization in Sweden, at MySQLTutorial. Their home page is very much worth studying, too: MySQL AB. Note the report: MySQL 5 had a million downloads in the first three weeks!!!
A fine tutorial on using JDBC in Java programs is provided by Sun.
Before I forget. To compile programs using JDBC you must have the MySQL driver, and have it in the right place. The driver is mysql-connector-java-3.1.11-bin.jar, although the 3.1.11 part will, of course, be different if you download an earlier or later version. The one on the course CD is mysql-connector-java-3.0.14-production.zip, which works fine. Unzip it, obviously. Then it must be in Program Files/Java/jre1.5.0_04/lib/ext. Change the jre1.5.0_04 if you have a different version, of course. With that in place I think that Eclipse will know what to do with the statement
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
in the text program Fig. 25.25. But there are mysteries (to me, still) about the package statement. (Remember: the package listing must correspond to a file structure on your machine.)
Caveat: I don't have a "clean machine" on which to try these instructions. When I boot up, mysql.exe and mysqld-nt.exe are already running. Your mileage may vary. And this is a splendid opportunity to practice some open-source authoring: send me bug reports. That's how MySQL and other open source products get clean: the suppliers work on a short revision cycle, frequently (every 4-6 weeks, perhaps) issuing new releases as early builds that are downloaded by those who a) want the latest stuff, and b) are willing to contribute to the process by testing the release and reporting problems. The system was apparently invented by Linus Torvald with Linux; it works better than I had thought possible.
I do now. Many of the changes reflect that.
In everything that follows, I assume that you have installed the product in a folder on your C: drive named mysql. Adjust as needed, but that's a good location.
Copy mysql-connector-java-3.0.14-production.zip or download a later version, unzip, and copy to location shown in the preamble above.
Open a DOS window. (Start/run/cmd, or go to Start/All Programs/Accessories and drag the DOS icon to you quick launch bar.) Navigate to C:\MySQL\bin and at the command prompt type mysqld and press Enter. After a few seconds' pause, you will be back (silently) to the command prompt. The MySQL database server is now running.
Open another DOS window. Navigate to C:\MySQL\bin and at the command prompt type mysql -h localhost -u root . After a welcome message of a few lines, you will be at the at the MySQL prompt. The mysql> prompt tells you that mysql is ready for you to enter commands. This is not a DOS prompt. (On not having a root password during installation, see 18 below.)
You can issue MySQL commands directly from here. No
Java program needed. Try: SELECT
VERSION(), CURRENT_DATE;
(Don't forget the closing semicolon. Any time you seem to be getting no
response, try typing and entering a semicolon.) You will get a response
in standard MySQL format giving the version number of what you are running,
together with the date and time.
There's lots more you can do from here. Compute
cosines! Check out the section of the MySQL tutorial, at
http://dev.mysql.com/doc/refman/5.0/en/entering-queries.html. Try a few
things. And do find the table that shows the variations on the MySQL prompt
and what they mean, -> '> '> `> /*>
. Short answer: they all mean that the MySQL monitor is waiting for the
completion of something.
Enter select
user(); (Note the case insensitivity.) You will see that you are
root@localhost.
This is OK for getting started, but you really don't want to be running as
root all the time. What you need
to do is create an account. Now I follow the instructions from the text; you
are free to do it your way, but bear in mind that we will soon be using sample
databases supplied by the authors that work only if you have done it as now
shown.
COMMENT: If you copy something from here
and get a screenful of crazy error messages, just relax and try it again. Some
error might be hanging over from previous entries. Or something; I don't claim
to understand all the Mysteries.
Enter the following commands, still at the MySQL prompt:
USE mysql;
INSERT INTO user SET Host='localhost', User='jhtp6', Password=PASSWORD('jhtp6'), Select_priv='Y', Insert_priv='Y', Update_priv='Y', Delete_priv='Y',Create_priv='Y', Drop_priv='Y', References_priv='Y', Execute_priv='Y';
FLUSH PRIVILEGES;
EXIT;
NOTE: if you are using MySQL 5, make
the second command as follows. (Thanks to Damian Harrigan.)
INSERT INTO user SET Host='localhost',
User='jhtp6', Password=PASSWORD('jhtp6'), Select_priv='Y', Insert_priv='Y',
Update_priv='Y', Delete_priv='Y',Create_priv='Y', Drop_priv='Y',
References_priv='Y', Execute_priv='Y', ssl_cipher=' ', x509_issuer=' ',
x509_subject=' ';NOTE 2: There is an even simpler way
(thanks to Daniel Ranells):
grant all on *.* to 'jhtp6'@'localhost'
IDENTIFIED BY 'jhtp6'; When I did this, I had to be root (I think) and I had to
supply my root password instead of jhtp6. This probably doesn't apply if you
don't have a root password. This trick is not limited to MySQL 5, I assume.
(Wildly insecure, of course.)
The second command is all one line, as I copied it into FrontPage. How it
looks to you will depend on your browser and how you have it sized. You can
type it in all as one line, or as several lines with a semicolon only after
the last one. Better yet, build that command as one line in Notepad,
select and copy it, then at the prompt press the right mouse button and pull
down to copy. Better yet, select from
here, then in a DOS window right click and pull down to Paste.
If you get a "duplicate entry" error message with the second command, don't worry about it. That means you already did this, but no harm is done.
The third command is less exciting than it sounds; it just clears anything in a buffer.
The EXIT command (don't forget the semicolon) takes you back to DOS. (Which is necessary, because now you want to log in an as a user other than root.)
The second command above (in 8) made your user name
jhtp6 (from Java
How To Program, 6th
edition). Now connect to the database server from DOS with:
mysql -h localhost -u jhtp6 -p
and at the password prompt type jhtp6.
Alternatively, if not securely (but see 19 below), just enter:
mysql -h localhost -u jhtp6 -pjhtp6Note that there is no space between the
p and the password. Now if you
enter:
select user();
you will see that you are now jhtp6.
Find employee.sql
and books.sql wherever you
have them: in the examples for the sixth edition of the text, in Chapter 25;
in the fifth edition, in Chapter 23. Copy those two files into the
mysql/bin
directory.
Open another command prompt (what I call a DOS
window) and change to the C:\mysql\bin
directory.
Execute the command:
mysql –h localhost –u jhtp6 –p < books.sql
This will get you a long message with much useful information. So useful, in
fact, that you may as well redirect the output to a file:
mysql –h localhost –u jhtp6 –p < books.sql >
stuff.txt
This creates the file stuff.txt
and puts it in the same folder you are in,
C:\mysql\bin.
(I'm not absolutely sure you
need a new DOS window to do this.) Open a new DOS
window and, with the following command, log in as yourself and specify the database:
mysql -h localhost -u jhtp6 -p books
(No semicolon: you are at the DOS prompt, not the MySQL prompt.) You will be
prompted for the password, after which you will be at the MySQL prompt.
Now you need to connect to your database:
USE books; You will be told that the database has been changed.
(From what, if this is the first time? Because.) To see that
it is there, do:
show databases;If you have been working for a while, there may, of course, be
more than just
books.
We're almost there! A database is composed of tables.
Let's see what they are:
show tables;
Seems that we have four: authors,
titles,
publishers, and
authorISBN. Yes! And it
counts! Go for it:
select * from authors;
select * from titles where price < '55' order by price;
select firstname, lastname from authors where lastname = 'Deitel';
Go back to the text and study the first 15 or so
pages of Chapter 25: what a relational database is, the example database and
its tables, various SQL commands. You shouldn't need my help on that.
BUT a warning. You can copy SQL queries
from the Chapter 25 PowerPoints, but bear in mind that the authors did not end
the queries with semicolons. AND the quotes are
typographic quotes, which will fail. Retupe any such with straight
quotes, which is what you will get in the standard font in a DOS window.
Now you can import the Java programs from Chapter 25 into Eclipse, compile, and run. Results go to the Console window. These will be our starting point in building the final project, although we will use a different database. (Um, well, if everything has been set up right you can. If you get big fat error messages here, you are not alone. Send me the error reports, so I can try to pin down and fix all the problems.)
It is normal to start
installation with no root password, or with a default password that
can be looked up. Things can reasonably be kept that way during installation.
But long-term, there has to be password protection. And the password I chose
as demonstrated in class, "dan" is terrible choice in the real
world. That could be broken so fast it would make your head spin. And if you
have set a password on root you need to say:
mysql -h localhost -u root -p
It's silly, of course, to worry about security as we have set this up: any user has all the privileges root has. A systems administrator's worst nightmare, presumably. One thing at a time.
Back to Dan McCracken's Home Page