CISC474 H03a: Getting started with MySQL.

In the first part of this homework assignment, I will walk you through some commands to

1. Access your login account and your personal database on the MySQL server, test your login, and change your password.
2. Create a table for the beer application using interactive SQL commands.
   
   Note: due to technical difficulties, that is as far as we will get today, and as far as this file, H03a will describe. The rest will come in H03b, to be posted as soon as possible.
   

In the second part, we will link that SQL table together with your Java Servlet and JSP code from Beer-v1, to make an application called BeerDB-v1. (We'll also rename other parts of the servlet and JSP code to ensure they stay separate from Beer-v1.)

In the third part, you will use what you have learned do something similar for your Prod-v1 application, creating a ProdDB-v1 application.

The ProdDB-v1 is the part you will turn in for credit. You should script and submit that in a similar fashion to what you did for H02 (see H02 for detailed submission requirements), except that you should also include in your printout, a sample session using /jaguar/cisc474/mysql/bin/mysql, where you list out the tables, columns, and rows you created.

Useful links:

MySQL Documentation: http://dev.mysql.com/doc/mysql/en/index.html

Getting Started with the MySQL Monitor (the "mysql> prompt")

Your username for your MySQL account will be the same as your strauss username.

Your password will be given to you in class.

First change, your password as follows:

1. Logon to WebCT, look under "My Grades", and find the field called "InitPwd". There you will find your initial password for your MySQL account (it is a randomly generated five or six digit number... all digits.)
2. ssh into jaguar
3. Type the following, and enter your initial password (the all digits one you got from WebCT, not your usual jaguar password.)
   (Note: you might want to create an alias for /jaguar/cisc474/mysql/bin/mysql, or add that bin directory into your path.)
   
   jaguar> /jaguar/cisc474/mysql/bin/mysql -u username -p
Enter password:

4. When prompted, enter your password.
5. When you get the "mysql>" prompt, enter the following, substituting your chosen new passsword for yournewpassword.
   
   (Yes, apparently, you have to actually type the password in; if you can find a way to prompt for it (and not have it appear on the screen) let me know. Perhaps we could write a script that would do that somehow. So you should NOT use the same password you use for anything else, since this password will not be as secure. You may also have to encode this password in scripts later on, which is another reason to NOT make it the same as any other passsword you use.)
   
   set password = password("yournewpassword");
   
6. Type exit to exit from "mysql", and try going into mysql again with your new password:
   
   jaguar> /jaguar/cisc474/mysql/bin/mysql -u username -p
Enter password: xxxxxxx
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19 to server version: 4.1.10-standard-log

   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

   mysql>


7. If you get in, great.. move on to the next step. From now on, I'll just say "do the following at the mysql> prompt..." That means the utility we just talked about in this section.

Creating a Table for the BeerDB-v1 app

The original Beer-v1 app returned

• "Jack Amber and Red Moose" for "amber", and
• "Jail Pale Ale" and "Gout" for any other selection.

We are going to be a bit more sophisticated (though not much.) We want to create a table with two columns, as follows:

Notice that the colors in the Beer-v1 app are amber, light, dark, and brown. I've deliberately put nothing in the table for Brown, so that we can learn how to handle the case of "no results being returned" from a SQL query.

Next step is to create the table.

Useful links:

• MySQL documentation for creating tables is here: http://dev.mysql.com/doc/mysql/en/create-table.html
• Tutorial Page on creating tables: http://www.analysisandsolutions.com/code/mybasic.htm

Here's an example:

jaguar[136] > /jaguar/cisc474/mysql/bin/mysql -u pconrad -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 4.1.10-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
  +----------+
  | Database |
  +----------+
  | pconrad  |
  | test     |
  +----------+
  2 rows in set (0.01 sec)
mysql> use pconrad;
  Database changed
mysql> create table beer (name char(20), color char(10)); 
  Query OK, 0 rows affected (0.02 sec)
mysql> 

Now put some data in the database

Useful links:

• MySQL documentation for "insert" and "select" commands (you can find it yourself by now)
• Tutorial Page on creating tables and inserting data: http://www.analysisandsolutions.com/code/mybasic.htm

mysql> insert into beer (name, color) values ('Jack Amber','amber');
Query OK, 1 row affected (0.00 sec)

mysql> insert into beer (name, color) values ('Red Moose','amber');
  Query OK, 1 row affected (0.00 sec)

mysql> insert into beer (name, color) values ('Jail Pale Ale','light');
  Query OK, 1 row affected (0.00 sec)

mysql> insert into beer (name, color) values ('Gout Stout','dark');
  Query OK, 1 row affected (0.04 sec)

mysql> select name, color from beer;
  +---------------+-------+
  | name          | color |
  +---------------+-------+
  | Jack Amber    | amber |
  | Red Moose     | amber |
  | Jail Pale Ale | light |
  | Gout Stout    | dark  |
  +---------------+-------+
  4 rows in set (0.05 sec)

mysql> 

Next Steps:

Ok, now we've got a table. We just need to get to it from our servlet. That will be the next assignment.