Previous Page Next Page

17.10. What's Next?

The next chapter discusses how Perl scripts can interface with the operating systems by issuing system calls and Perl functions to work with directories, permissions, ownerships, hard and soft links, renaming files, get file statistics, etc. You will learn how to use these functions on both UNIX- and Windows-based systems.

Exercise 17: Select * from Chapter

Part 1. SQL Lab—Using the "northwind" database:

1.Loading the "northwind" database from a script
2.The "northwind" database is a good sample database used with Microsoft's Access Database but tailored to work with MySQL as well.
3.You will be provided with the "northwind.sql" script for this exercise (on CD), a file containing SQL statements to create and populate the northwind database. The file must be located on the client host where you are running mysql.
4.To run the script, go to your MySQL console window and type:

source c:\document\northwind.sql

(Notice that there are no quotes around the name of the file and that absolute or relative pathnames can be used.)

Ways to source the SQL script:
         mysql> SOURCE C:\path\northwind.sql;
         mysql> SOURCE ..\path\northwind.sql;
         shell> mysql db_name < input_file

As the script executes, the results will be displayed on your screen, most of them SQL "insert" statements. If an error occurs, the process will be aborted, nothing done.

See: http://showroom.declarativa.com/northwind.htm

Practice SQL Commands

1.After running the northwind.sql script, start by typing "use northwind".
2.Type the following SQL commands and explain what they do.

  1. SHOW DATABASES;

  2. SHOW TABLES FROM NORTHWIND;

  3. SHOW FIELDS FROM SHIPPERS;

  4. DESCRIBE SHIPPERS;

3.Use the SQL tutorial if you need it to:

Select all rows from the Shippers table and display the fields.

Select all rows from the Employees table and display only the FirstName and LastName fields.

Select the CompanyName and Phone from the Customers table only if the Country is Italy.

Print in sorted order the ContactName, and Country from the Customers table. Sort by Country.

Select the ContactName, CompanyName, and Country from the Customers table where the Country begins with either "Po" or "Sw".

The total number of products in the Products table.

Print only the first 10 products from the Products table.

Select countries from the Customers table in alphabetic order with no duplicates.

Find all the products between $10 and $20.

Insert a new product.

Update the products table by adding $5.25 to the unit price of Sir Rodney's Marmalade.

Select products below 10 units in stock.

Delete the oldest order in the database.

Use a SQL function to print the current date and time.

Part 2. Perl/MySQL Lab

Consult the MySQL documentation (mysql.com) to get the correct datatypes and functions to help you with this lab. The documentation is excellent.

1.Create a SQL script called "school.sql" that will:

  1. DROP SCHOOL IF EXISTS SCHOOL;

  2. CREATE SCHOOL;

  3. Create a table called "student". It will consist of the following fields:

    FirstName

    LastName

    Email

    CellPhone

    Major

    GPA

    StartDate

    StudentID (primary key)

2.Use school

  1. Insert three rows of data into the student table.

3.At the MySQL prompt, execute the "school.sql" script.

Use the SQL describe statement to see the structure of the school database.

4.Select all the rows in the student table. If the table has all the columns and data expected, then you are ready to go on to the next part of this lab.
5.In a Perl script use the DBI module to:

  1. Connect to MySQL and open the school database.

  2. Prepare a cached query to select all the rows in the student table, sorted by last names, and display all the columns with headings.

6.

  1. Create another Perl script so that the user can select options from a menu:

    1. Update a record

    2. Delete a record

    3. Insert a new record

    4. Display the table

    5. Exit

For each of the options in the menu, create a subroutine that will perform the selected task, using MySQL functions. Can you create a CGI program to handle all of this?

  1. Create a loop so that the menu will be redisplayed until the user selects number 5.

  2. Rewrite the program by turning off AutoCommit and turning on RaiseErrors. Commit all changes to the database when the user chooses exit.

Part 3. Back to Checking.pm

1.Now you can redesign your original Checking.pm module to use the MySQL database rather than the text file you originally created to keep track of the balance.

Create the database and the register table at the mysql prompt. This register should contain fields that match the text file called "register" you created in the first exercise.

In the Perl module, Checking.pm, open the connection to the database. To get the balance, you will select it from the "register" table.

When you call your exit() function, insert the last transaction into the database with the new information, using the SQL INSERT command.

Create a Perl function that, when called, displays the contents of the register.

You should know how to:

Use PPM and CPAN to install MySQL, DBI, and the database driver.

Issue basic MySQL commands.

Connect to the database server from a Perl script.

Use Perl MySQL functions to:

Select a database

Prepare and execute SQL statements

Use DBI methods to iterate through the result set

Use placeholders

Bind columns and parameters

Check for errors generated by the database server

Keep track of the number of records retrieved

Discard the planned query

Disconnect from the database from a Perl script


 

Previous Page Next Page