lecture #12 database - cypress collegestudents.cypresscollege.edu/cis245/lc12.pdf · lecture #12...

331

Lecture #12 Database

Concept of

data-driven

web sites

With most of the services on the web being powered by web database applications, it becomes

important for any web developer to know how bring together the web and databases to build

applications, namely create a data-driven web site. By definition, a data-driven Web site is one

in which the content comes from some back-end data source, such as a database, and is then

formatted and presented to the user by the Web server. Typically, data-driven sites are dynamic;

they present information that is live rather than static. The benefits of using data-driven sites are

numerous. Implementing data-driven Web sites requires choosing and deploying the right

infrastructure. Common infrastructures are:

Oracle: Java ServerPages (JSP) on the front-end (front and middle-tier) connected to Oracle

or MySQL databases on the back-end.

Microsoft: This framework uses ASP .NET. and Microsoft SQL server.

Open Source: This framework uses the PHP scripting language and the MySQL relational

database. This option is claimed to be the most popular infrastructure.

Others: Middle-level programming resources, such as Cold Fusion MX, can work with

MySQL or SQL Server back-end databases.

MySQL is a relational database management system (RDBMS) that runs as a server providing

multi-user access to a number of databases. Unlike Oracle and Microsoft's MSSQL, MySQL is

under the GNU General Public License and is completely free. MySQL is claimed to be the

most popular DBMS used by most web sites on the Internet. XAMPP comes with MySQL;

therefore, the instructor prepares this lecture based on MySQL.

SQL Structured Query Language (SQL) is the language used in a query to request information from a

database. It is an easy-to-learn language. There are four basic data implementation activities to

learn about SQL:

SELECT: Find all the records that have a certain property.

INSERT: Add new records.

DELETE: Remove old records.

UPDATE: Modify records that are already there.

The SELECT statement is used to query the database and retrieve selected data that match the

field(s) you specify. The keyword FROM specifies the table that will be queried to retrieve the

desired results. The WHERE clause (optional) specifies which data values or rows will be

returned or displayed, based on the criteria described after it. The following is a simple SQL

statement that can query a list of movies by “Jackie Chan”.

SELECT title FROM movies WHERE actor = "jackie chan";

Suppose that your MySQL has a database named “Entertainment”, in which there is a table

named “Movies”. This “Movies” tables has five fields: PartID, Title, Actor, Type, and Cost.

There are two records in the “Movies” table that match the criteria, so the above SQL statement

will generate a query result telling you there are three Jackie Chan’s movies, “Around the world

in 80 days”, “Rush Hour I” and “Rush Hour II”, in your database.

The “Movies” table PartID Title Actor Type Cost

...... ...... ...... ...... ...... A1027 Around the world in 80

days

Jackie Chan DVD $10.95

332

A1028 Rush Hour I Jackie Chan DVD $10.95

...... ...... ...... ...... ...... A1067 Rush Hour II Jackie Chan DVD $10.95

...... ...... ...... ...... ......

A1135 Rush Hour I Jackie Chan Blue-ray $19.95

...... ...... ...... ...... ......

A sample query results generated by MySQL server is shown below which has three records:

mysql> SELECT * from tutorials_tbl

+-------------+----------------+-----------------+-----------------+

| tutorial_id | tutorial_title | tutorial_author | submission_date |

+-------------+----------------+-----------------+-----------------+

| 1 | Learn Perl | Jennifer Lopez | 2017-05-21 |

| 2 | Learn MySQL | Eric Yang | 2017-05-21 |

| 3 | Perl Tutorial | Amy Garcia | 2017-05-21 |

+-------------+----------------+-----------------+-----------------+

3 rows in set (0.01 sec)

You can take a crash course to learn SQL at http://www.w3schools.com/SQl/default.asp.

DBI modules The Perl DBI module provides a generic interface for database access. DBI is a database-

independent interface for the Perl programming language. DBD::mysql is the driver for

connecting to MySQL database servers with DBI. Basically,

DBI is the basic abstraction layer for working with databases in Perl.

DBD::mysql is the driver for using MySQL with DBI.

Net::MySQL is a pure-Perl implementation of the MySQL client-server protocol. (It is not

necessary when using DBI with DBD::mysql, but may be useful in environments where

you are not able to compile the MySQL client library required by DBD::mysql.)

You can write a DBI script that works with many different database engines without change. To

use DBI, you must install the DBI module, as well as a DataBase Driver (DBD) module for

each type of databas server you want to access. For MySQL, this driver is the DBD::mysql

module. However, Perl support is not included with MySQL distributions. You can obtain the

necessary modules from http://search.cpan.org for Unix, or by using the ActiveState ppm

program on Windows. The following sections describe how to do this.

In order to retrieve data from a database server through the Web, a client browser must gain

access to the database server (e.g. MySQL) and the database stored in that server, and then use

SQL statement to provide query information to produce the query results.

A Perl code, aimed at retrieving data from a remote database server, must be able to

communicate with the server through the Web, send request for authentication, specify the

dataset to access, set the criteria for the server to run the query, and eventually deliver the query

results to the client browser in HTML form.

Perl DBI supports MySQL by providing functions specially designed for accessing, connecting,

and managing MySQL databases. The DBI architecture is split into two main groups of

software, the DBI itself, and the drivers. The detailed information is available at http://search.cpan.org/~timb/DBI-1.616/DBI.pm

Specific drivers are implemented for each different type of database and actually perform the

operations on the databases

The DBI defines three main types of objects, known as handles, to interact with databases:

Perl script

using DBI API DBI MySQL Driver MySQL Engine

333

Handles for drivers represent loaded database drivers (PostgreSQL, MySQL, etc. if

necessary). They are used by the DBI to create handles for database connections. Driver

handles are normally not explicitly referenced, they are only for internal use within the

DBI.

Handles for database connections ($dbh) encapsulate a single connection to a particular

database. They are used to create handles for statements.

Handles for statements ($sth) encapsulate individual SQL statements to be executed within

the database. Multiple statements can be created and executed within one script, and the

data can be processed as it returns.

To use Perl DBI, the DBI module must be added to the Perl script.

use DBI;

The following is a sample Perl script that demonstrates how to use DBI to access an existing

database named “customers” at the localhost server using a userID “perluser” and a password

“smile105”. The lecture will then explains details of the following sample code.

#!"X:\xampp\perl\bin\perl.exe"

use DBI;

print "Content-type: text/html\n\n";

$dbh = DBI->connect("DBI:mysql:customers:localhost", "perluser",

"smile105");

print $dbh->do('describe tickets');

$dbh->finish;

exit(0);

By the way, describe tickets is a MySQL-specific SQL statement that displays the details of

columns in the “tickets” table. The following is the sample outputs if the statement is execute

under a MySQL command line interface.

> describe tickets;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| ticketNo | varchar(10) | NO | PRI | NULL | |

| FirstName | varchar(25) | NO | | NULL | |

| LastName | varchar(25) | NO | | NULL | |

| Email | varchar(50) | NO | | NULL | |

| flag | int(1) | NO | | NULL | |

+-----------+-------------+------+-----+---------+-------+


Database drivers tell the DBI which database to use, where to find the database server

(hostname, port, etc.). The following is the DBI syntax for specifying database drivers. In this

lecture, all script will use XAMPP as the server. Since the XAMPP server is running on the

local computer, the hostname can be fixed to “localhost”. The default port for MySQL is 3306.

DBI:SQLPlatform:databaseName:hostName:portNumber

Perl DBI uses the DBI->connect() function to create a connection to the server in order to

access a database stored in that server. The following is the syntax, where the parameters

specify platform, database name, user name, and password, and so on.

$variableNmae = DBI->connect(

334

'DBI:SQLPlatform:databaseName:hostName', 'userName',

'password' );

The following example uses the connect() method to pass required parameters to DBI:mysql.

The varaiable $dbh; however, is the database handle object.

$dbh = DBI->connect("DBI:mysql:customers:localhost”, "perluser",

"smile105");

or

$db = "cis245";

$host = "localhost";

$user = "perluser";

$pass = "smile105";

$dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);

When DBI->connect() is called and the instantiation is successful, DBI->connect() returns a

connection handle. In the above code segment, the instructor used many variables to specify

connection handles. The name of variables is determined by the programmer. You can use

different names. However, the value it represents must match with that in the database server.

The following table explains them.

Variable Function Example $db mysql user database name $db = "cis245";

$user mysql database user name $user = "perluser";

$pass mysql database password $pass = "smile105";

$host hostname $host = "localhost";

According to the above table, there must be a database named “cis245” which allows a user

“perluser” with password “smile105” to access it through the “localhost” (typically through port

80 or 8080). If any of the above data does not match, the connection will fail and an error

message will be generated.

The following is a sample DBI statement to connect to an Oracle database, for the sake of

comparison. The database name is “cis245”, “scott” is username, and “tiger” is password.

$db=DBI->connect("dbi:Oracle:cis245", "scott", "tiger");

The DBI performs basic automatic error reporting when the PrintError attribute is enabled

(default). In this class, no need to disable the error reporting. However, if you need to disable

this feature, you can set the value to 0 either via the connection handle, or via the attribute hash

of the connect() method:

$dbh->{PrintError} = 0; # disable

$dbh->{PrintError} = 1; # enable

Upon failure connect() returns the value undef, which can be used to perform error checking on

the call and the error message contained within the variable $DBI::errstr will be printed.

$DBI::errstr() is the string containing a description of the error, as provided by the

underlying database. This string corresponds to the error number stored in $DBI::err().

$DBI::state is the string in the format of the standard SQLSTATE five-character error

string.

The following print an errstr() message if any.

print "$dbh->errstr\n";

335

Before the SQL statement can be executed, it needs to be prepared for execution:

$query = "SELECT * FROM students";

$sql = $dbh->prepare($query);

or simply,

$sql = $dbh->prepare("SELECT * FROM students");

The prepare() function returns a statement handle (represented by $sql in the above example).

Once a statement is prepared, you can execute it using the execute() method. If there is a

matching record, the return value from execute() should be nonzero. The execute() function

executes SQL query to select all the results from the database. In this case, you can create an

if..then statement to deal with the condition of non-matching record.

$rv = $sql->execute;

if ($rv)

{

// your code here

}

else

{

print "No matching record: $dbd->errstr()!;

}

When the query completes, you should notify Perl, so that associated information can be

released. The finish() function releases Stattement handle.

$sql->finish;

Or

$rc = $sql->finish;

The combination of prepare and execute functions can returns a number of rows generated by a

“SELECT” SQL statement. However, if you just want to run a single non-SELECT statement

and return only one row (or one value such as -1, 1 or 0), you can use the do() function. The do

function is typically used for SQL that does not need to be executed repeatedly. However, you

cannot use $dbh->do() for SELECT statements because it does not return a statement handle,

making it impossible to fetch data. The following is a simple but complete Perl script that

illustrates how the do() function works.


use DBI;



"smile105");

print $dbh->do('describe customer');

$dbh->finish;

exit(0);

You can use the disconnect() function to close the connection if necessary, it ends a MySQL

session. For example,


336

use DBI;



"smile105");


$dbh->finish;

$dbh->disconnect();

exit(0);

Fetching data The most frequently used SQL statement is possible the “SELECT” statement which typically

generate a number of rows of record. For example, the following SQL statement

SELECT title FROM movies WHERE actor = "jackie chan";

can generate output similar to:

PartID Title Actor Type Cost

A1027 Around the world in 80 days Jackie Chan DVD

$10.95

A1028 Rush Hour I Jackie Chan DVD

$10.95

A1067 Rush Hour II Jackie Chan DVD

$10.95

A1135 Rush Hour I Jackie Chan Blue-ray

$19.95

The prepare(sqlStatement) function prepares a SQL statement to be passed to the

database handle object ($dbh), which represents the MySQL server, for query. The following is

an example that demonstrates how to enclose a SQL statement in the prepare() function. The

variable, $sql, is the query handler.

$sql = $dbh->prepare("SELECT title FROM movies WHERE actor =

'jackie chan'");

After preparing the SQL statement, the actual execution of the SQL statement is done by calling

the execute() function.

$sql->execute;

An alternative is to call the execute() function and asign the output to a variable. The output

will store whatever returned by the prepare() function.

$result = $sql->execute();

A SQL query typeically returns multiple records. It is necessary to loop through these returned

records to process them one by one. There are several functions provided by Perl DBI for

retrieving query results, such as fetchrow_array() to fetch the next row as an array and

fetchall_hashref() to fetch all of the results at once into a hash. Basically,

fetchrow_array(): Fetches the next row as an array of fields.

fetchrow_arrayref(): Fetches next row as a reference array of fields.

fetchrow_hashref(): Fetches next row as a reference to a hashtable.

The fetchrow_array() processes one record at a time; therefore, programmers usually create

a repetition structure to iterate through every retrieved record. One way to handle the returned

337

record is to create an array as handle to store the returned record. In the following example, the

@row array is the handle.

while (@row = $sql->fetchrow_array())

{

print "$row[0] $row[1] $row[2] $row[3]\n<br>";

}

Each record may contain several individual data. There are three records in the following

“students” table. Each record contains a piece of data of ID, name, course, and credit

respectively. Programmers can declare variables to store each of the data invidually, instead of

creating an array to store all of them.

Students ID Name Course Credit

D002141249 Helen Hunt CIS245 3

D002509873 Marie Garcia CIS246 3

D002631874 Sue Makino CIS245 3

In the following example, the instructor only needs to use data from “ID” and “Name” columns;

therefore, the code declared two variables, $id and $name, to store data of “ID” and “Name”

columns during every iteration.

while (($id, $name) = $sql->fetchrow_array())

{

print "ID: $id Name: $name\n<br>";

}

The following is a completed code demonstrates how the fetchrow_array() function works

with a “SELECT * FROM customer” statement.


use DBI;



"smile105");

$sql = $dbh->prepare("SELECT * FROM customer");

$sql->execute;


{

print "<li>$row[0]</li>";

}

print "</ul>";

$sql->finish;

$dbh->disconnect();

exit(0);

The fetchrow_hashref() function fetches the next row of data and returns it as a reference to a

hash containing field name and field value pairs. Null fields are returned as undef values in the

hash. A record can be thought of being a hash. The instructor creates the following %row hash

to explain the concept with respect to the first record of the above table.

%row = ( ID=>"D002141249",

338

Name=>"Helen Hunt",

Course=>"CIS245",

Credit=>"3" );

The following is the sample code that demonstrates how to implement return data as a hash

elements.

while ($row = $sql->fetchrow_hashref())

{

print "$row->{id} $row->{name}";

}

The following is a completed code illustrating how the fetchall_hashref() function retrieve

ONE field (firstname) based on “SELECT firstname FROM customer” to a hash.


use DBI;



"smile105");

$sql = $dbh->prepare("SELECT firstname FROM customer");

$sql->execute;

while ($row = $sql->fetchrow_hashref())

{

print "<li>$row->{firstname}</li>";

}

print "</ul>";

$sql->finish;

$dbh->disconnect();

exit(0);

The fetchall_arrayref() function can fetch all the data to be returned from a prepared and

executed statement handle. It returns a reference to an array that contains one reference per row.

The instructor creates the following $row variable to reference to a list, which also make “row”

a Perl array.

$row = ["D002141249", "Helen Hunt", "CIS245", "3"];

In the following example, the instructor creates a while loop to demonstrates how to use the

fetchrow_arrayref() function to fetch the next row of data and returns it as a reference to

an array of field values.

while ($row = $sql->fetchrow_arrayref())

{

print "$row->[0] $row->[1] $row->[2]";

}

For example:


use DBI;


339


"smile105");

$sql = $dbh->prepare("SELECT * FROM customer");

$sql->execute;

while ($row = $sql->fetchrow_arrayref())

{

print "<li>$row->[0], $row->[1], $row->[2]</li>";

}

print "</ul>";

$sql->finish;

$dbh->disconnect();

exit(0);

Other

maintenance

SQL statements for Insert, update, and delete data can be executed simply by using the do

function. The following example demonstrates how to integrate an INSERT SQL statement.


use DBI;



"smile105");

$dbh->do("INSERT INTO people(firstname, lastname, age)

VALUES('Bob', 'Johnson', 32)");

$sql->finish;

$dbh->disconnect();

exit(0);

The following example demonstrates how to integrate an UPDATE SQL statement.


use DBI;



"smile105");

$dbh->do("UPDATE people set firstname='Robert' where

firstname=?", undef, "Bob");

$sql->finish;

$dbh->disconnect();

exit(0);

The following example demonstrates how to integrate an UPDATE SQL statement.


use DBI;


340


"smile105");

$dbh->do("DELETE FROM people WHERE lastname='Johnson'");

$sql->finish;

$dbh->disconnect();

exit(0);

You can create a generic SQL processing script which allows users to enter a valid SQL

statement for online query. The following segment displays an HTML form on the web browser

for the user to enter one single but complete SQL statement to the text area. When the user

clicks submit, the SQL will be sent to a Perl script named “test.pl”.

if(!param)

{

print "<form action='test.pl' method='post'>";

print "Enter SQL:<br><textarea rows='5' cols='70'

name='sqlQuery'></textarea>\n</p>";

print "<P><INPUT TYPE='submit' VALUE='Execute'></form></P>";

}

The “test.pl” script has the following line to get the SQL statement and process it as the

statement is embedded to the script.

.........

my $query = param('sqlQuery');

.........

$sql = $dbh->prepare($query);

$sql->execute;

.........

Review

Question

1. In SQL, the __ statement/keyword/clause specifies the table that will be queried to retrieve

the desired results.

A. select

B. from

C. where

D. order by

2. Which SQL statement can possibly give you the following result?

+-------------+

| tutorial_id |

+-------------+

| 1 |

| 2 |

| 3 |

+-------------+


A. SELECT * from tutorials_tbl

B. SELECT tutorial_id from tutorials_tbl

C. SELECT * from tutorial_id

D. SELECT tutorial_id from tutorials_tbl WHERE tutorial_id = 1, 2, 3

341

3. Which can connect to a MySQL database using the following criteria?

database: cypress

host: cis245

username: admin

password: h$562

A. $dbh = DBI->connect("DBI:mysql:cis245:cypress", "admin", "h$562");

B. $dbh = DBI->connect("DBI:mysql:admin:cypress", "cis245", "h$562");

C. $dbh = DBI->connect("DBI:mysql:cypress:cis245", "admin", "h$562");

D. $dbh = DBI->connect("DBI:mysql:cypress:cis245", "h$562", "admin");

4. Which can enable basic automatic error reporting of DBI?

A. $dbh->{PrintError} = true;

B. $dbh->{PrintError} = "yes";

C. $dbh->{PrintError} = 0;

D. $dbh->{PrintError} = 1;

5. Which can print a Perl DBI errstr() message?

A. print "$dbh->errstr\n";

B. print "errstr\n";

C. print "errstr()\n";

D. print "$errstr\n";

6. The __ Perl DBI function executes a MySQL statement handle that has been processed with

the prepare() method.

A. prepare()

B. execute()

C. fetch()

D. do()

7. The __ Perl DBI function executes a MySQL statement without having to use the Perl DBI

prepare( ) method.

A. prepare()

B. execute()

C. fetch()

D. do()

8. The __ function fetches next row as a reference array of fields.

A. fetchrow_arrayref()

B. fetchrow_hashref()

C. fetchrow_array()

D. fetchall_arrayref()

9. Which ends a MySQL session?

A. $dbh->finish;

B. die();

C. $dbh->disconnect();

D. exit(0);

10. Which statement is incorrect?

A. $dbh->do("INSERT INTO people(firstname, lastname, age) VALUES('Bob', 'Johnson',

32)");

B. $dbh->do("UPDATE people set firstname='Robert' where firstname=?", undef, "Bob");

C. $dbh->do("SELECT * FROM students WHERE class = 'History'");

D. $dbh->do("DELETE FROM people WHERE lastname='Johnson'");

343

Lab #12 Perl and Database

Preparation #1: Running the server and create default database

1. Close all the applications that may cause conflicts with XAMPP such as Skype IIS, Wampserver, VMware, etc.

2. Insert the USB that contains XAMPP. Determine the drive name (such as “F:\”).

3. Change to the “X:\xampp\” directory (where X must be the correct drive name) to find the “setup_xampp.bat”

and then execute it. You should see:

######################################################################

# ApacheFriends XAMPP setup win32 Version #

#--------------------------------------------------------------------#

# Copyright (C) 2002-2011 Apachefriends 1.7.7 #

#--------------------------------------------------------------------#

# Authors: Kay Vogelgesang ([email protected]) #

# Carsten Wiedmann ([email protected]) #

######################################################################

Do you want to refresh the XAMPP installation?

1) Refresh now!

x) Exit

4. Press 1 and then [Enter]. If re-configuration succeeds, you should see the following message.

XAMPP is refreshing now...

Refreshing all paths in config files...

Configure XAMPP with awk for ‘Windows_NT’

###### Have fun with ApacheFriends XAMPP! ######

Press any key to continue ...

Preparation #2: Creating a MySql database

1. Start both Apache and MySQL servers, as shown below.

2. Open a Command Prompt.

3. In the prompt, type x: and press Enter, where “x” is the drive name of the USB drive. The following use “E” as

example.

C:\Users\user>e:

E:\>

4. Type notepad sql.bat and press [Enter] to use Notepad to create new “batch” file named “sql.bat”. Click

Yes to confirm.

@echo off

path=%path%;%CD%xampp\mysql\bin;

mysql -u root -p

344

5. Save and exit the sql.bat file.

6. Type sql.bat and press [Enter] to runt the sql.bat file.

E:\>sql.bat

7. When being prompted to enter the password, simply press [Enter]. The “MariaDB [(none)]>” prompt should

now appear.

Enter password:

Welcome to the MariaDB monitor. Command end with ; or \g.

Your MariaDB connection id is 2

Server version: 10.1.10-MariaDB mariadb.org binary distribution

............

MariaDB [(none)]>

8. Type SHOW DATABASES; and press [Enter] to display a list of the currently available databases. A sample

output looks:

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| phpmyadmin |

| test |

+--------------------+


9. Type exit and press [Enter] to exit MySQL.

MariaDB [(none)]> exit

Bye

10. Close the Command Prompt.

Preparation #3: Create a database

1. Open a Command Prompt.

2. Type x: and press [Enter] to change to the X: drive, where “x” is the drive name of the USB flash drive. The

following use “e” as example.

C:\Users>user>e:

3. Type sql.bat and press [Enter] to launch MySQL. Press [Enter] to bypass the password inquiry.

E:\>sql.bat

4. Type CREATE DATABASE IF NOT EXISTS customers; and press [Enter] to create a new database named

“customers”. The semicolon (;) is required to end the line. The “IF NOT EXISTS” statement can avoid errors in

the event that the database already exists.

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS customers;

Query OK, 1 row affected (0.00 sec)

345

5. Type SHOW DATABASES; and press [Enter] to display a list of the currently available databases. Verify that the

“customers” database is available.

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| customers |

| information_schema |

| mysql |

| performance_schema |

| phpmyadmin |

| test |

+--------------------+


6. Type SELECT database(); and press [Enter] to find out which database is currently selected.

MariaDB [(none)]> SELECT database();

+------------+

| database() |

+------------+

| NULL |

+------------+

1 row in set (0.01 sec)

7. Type USE customers; and press [Enter] to select the "Customers" database for subsequent operations.

MariaDB [(none)]> USE customers;

Database changed

8. Type SELECT database(); and press [Enter] to verify the change.

SELECT database();

+--------------+

| database() |

+--------------+

| customers |

+--------------+


9. With the “customers” database being selected, type the following SQL statement to create a new table named

“tickets” and save it to the “customer” database.

CREATE TABLE IF NOT EXISTS tickets (

ticketNo varchar(10) NOT NULL,

FirstName varchar(25) NOT NULL,

LastName varchar(25) NOT NULL,

Email varchar(50) NOT NULL,

flag int(1) NOT NULL,

PRIMARY KEY(ticketNo)

);

10. The SQL statement is long, the screen should look similar to the following.

MariaDB [customers]> CREATE TABLE IF NOT EXISTS tickets (

-> ticketNo varchar(10) NOT NULL,

-> FirstName varchar(25) NOT NULL,

-> LastName varchar(25) NOT NULL,

-> Email varchar(50) NOT NULL,

346

-> flag int(1) NOT NULL,

-> PRIMARY KEY(ticketNo)

-> );

Query OK, 0 rows affected (0.70 sec)

11. Type show columns in tickets; and press [Enter] to display the details of columns.

MariaDB [customers]> show columns in tickets;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| ticketNo | varchar(10) | NO | PRI | NULL | |

| FirstName | varchar(25) | NO | | NULL | |

| LastName | varchar(25) | NO | | NULL | |

| Email | varchar(50) | NO | | NULL | |

| flag | int(1) | NO | | NULL | |

+-----------+-------------+------+-----+---------+-------+


12. Type the following bold-faced SQL statement to insert a record to the “tickets” table.

MariaDB [customers]> INSERT INTO tickets VALUES ('DB16-24381', 'Sandy', 'Liu',

'[email protected]', 0);


13. Type select * from tickets; and press [Enter] to run a SQL query, where the “*” means all columns.

MariaDB [customers]> select * from tickets;

+------------+-----------+----------+----------------+------+

| ticketNo | FirstName | LastName | Email | flag |

+------------+-----------+----------+----------------+------+

| DB16-24381 | Sandy | Liu | [email protected] | 0 |

+------------+-----------+----------+----------------+------+


14. Open another Command Prompt. Type notepad data1.sql and press [Enter] to use Notepad to create a new

text file named “data1.sql” under the “X” drive (“X” is the drive name of the USB flash drive) with the

following lines of SQL statements. INSERT INTO tickets VALUES ('DB16-33040', 'Linda', 'Buffet', '[email protected]', 0);

INSERT INTO tickets VALUES ('DB16-20500', 'George', 'Bush',


INSERT INTO tickets VALUES ('DB16-84214', 'Erica', 'Cartman', '[email protected]', 0);

INSERT INTO tickets VALUES ('DB16-78210', 'Stephanie', 'Crockett',


INSERT INTO tickets VALUES ('DB16-20021', 'Jane', 'Horita',


INSERT INTO tickets VALUES ('DB16-98052', 'Bill', 'Gates', '[email protected]',

0);

INSERT INTO tickets VALUES ('DB16-10041', 'Nancy', 'Jefferson',


INSERT INTO tickets VALUES ('DB16-10118', 'Kimberly', 'Chen', '[email protected]',

0);

INSERT INTO tickets VALUES ('DB16-58102', 'David', 'Tanaka', '[email protected]',

0);

INSERT INTO tickets VALUES ('DB16-46556', 'Miguel', 'Garcia', '[email protected]', 0);

INSERT INTO tickets VALUES ('DB16-12345', 'Nicole', 'Simpson',


INSERT INTO tickets VALUES ('DB16-46001', 'Bob', 'Smith', '[email protected]', 0);

INSERT INTO tickets VALUES ('DB16-87693', 'Pedro', 'Manrique',


348

3. Type the following SQL statement to grant the new user with all privileges. The two asterisks (*) refer to the

database and table respectively. This specific command allows to the user to read, edit, execute and perform all

tasks across all the databases and tables.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON * . * TO 'tcm159'@'localhost';

4. Issue the following bold-faced statement to limit the access of “tcm159” to only the “customers” database. Yes,

the asterisk still allow full access to all tables in the “customers” database.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON customers . * TO 'tcm159'@'localhost';

5. Type FLUSH PRIVILEGES; and press [Enter] to reload all the privileges; therefore, the changes can take effect.

6. In the “X:\xampp\htdocs\myperl” directory, use Notepad to create a new file named lab12_0.pl with the

following contents:


use DBI;


$dbh = DBI->connect("DBI:mysql:customers:localhost", "tcm159", "sGkY4651");


$dbh->finish;

$dbh->disconnect();

exit(0);

7. Use Web browser to go to http://localhost/myperl/lab12_0.pl (or

http://localhost:81/myperl/lab12_0.pl). You should see the following which means the above configuration

succeeded.

Learning Activity #1: Accessing the database

1. Launch both Apache and MySQL server with XAMPP control panel.


following contents:


##### Instructor’s comment: Be sure to use the correct drive name #####

349

use DBI;


## mysql user database name

$db ="customers";

## mysql database user name

$user = "tcm159";

## mysql database password

$pass = "sGkY4651";

## hostname

$host="localhost";

## SQL query

$query = "show tables";


$sql = $dbh->prepare($query) or print "Cannot prepare $query: $dbh->errstr\n";

$rv = $sql->execute or print "cannot execute the query: $sql->errstr";

print "Welcome to my database <b>$db</b>. It has the following table(s):<ul>";


{

print "<li>$row[0]</li>";

}

print "</ul>";

$rc = $sql->finish;

$dbh->disconnect();

exit(0);

3. Use Web browser to go to http://localhost/myperl/lab12_1.pl (or

http://localhost:81/myperl/lab12_1.pl). You should see:

4. Download the “assignment template”, and rename it to lab12.doc if necessary. Capture a screen shot similar to

the above figure and paste it to a Word document named lab12.doc (or lab12.docx).

Learning Activity #2: Accessing database and retrieve data from a given table


following contents:

350



use DBI;


## mysql user database name

$db ="customers";

## mysql database user name

$user = "tcm159";

## mysql database password

$pass = "sGkY4651";

## hostname

$host="localhost";

## SQL query

$query = "SELECT * FROM customer";




print "<table border=1><tr><th>Customer ID</ht><th>Last Name</th><th>First

Name</th></tr>";

while (@row= $sql->fetchrow_array())

{

print "<tr><td>$row[2]</td><td>$row[1]</td><td>$row[0]</td></tr>";

}

print "</table>";

$rc = $sql->finish;

$dbh->disconnect();

exit(0);

2. Use Web browser to go to http://localhost/myperl/lab12_2.pl. You should see:

3. Capture a screen shot similar to the above figure and paste it to a Word document named lab12.doc (or

lab12.docx).

Learning Activity #3: Inserting new data

1. In the “X:\xampp\htdocs\myperl” directory (where X is the drive name of your USB drive), use Notepad to

create a new file named lab12_3.pl with the following contents:

351

#!"F:\xampp\perl\bin\perl.exe"


use CGI qw(:standard);

print header;

print start_html;

if(!param)

{

print "<form action='$ENV{SCRIPT_NAME}' method='post'>";

print "Ticket No.: <input type='text' name='ticketNo'>\n<br>";

print "First Name: <input type='text' name='fname'>\n<br>";

print "Last Name: <input type='text' name='lname'>\n<br>";

print "Email: <input type='text' name='email'>\n<br>";

print "<P><INPUT TYPE='submit' VALUE='Submit'></form></P>";

}

else

{

use DBI;

$db ="customers";

$user = "tcm159";

$pass = "sGkY4651";

$host="localhost";

my ($ticketNo, $fname, $lname, $email);

$ticketNo = param('ticketNo');

$fname = param('fname');

$lname = param('lname');

$email = param('email');

## insert new record

$query = "INSERT INTO `customers`.`tickets` (`ticketNo`, `FirstName`, `LastName`,

`email`, `flag`) VALUES ('$ticketNo', '$fname', '$lname', '$email', '0')";




$rc = $sql->finish;

## retrieve data

$query = "SELECT * FROM customer";



print "<table border=1><tr><th>Customer ID</th><th>Last Name</th><th>First

Name</th></tr>";

while (@row= $sql->fetchrow_array())

{

352

print "<tr><td>$row[2]</td><td>$row[1]</td><td>$row[0]</td></tr>";

}

$rc = $sql->finish;

$dbh->disconnect();

print "</table>";

exit(0);

}

print end_html;


3. Add the following records.

FirstName Renee Ashley

LastName Zellweger Judd

CID D889712364 D8897123482

4. Capture a screen shot similar to the above figure and paste it to a Word document named lab12.doc (or

lab12.docx).

Learning Activity #4: SQL processing


following contents:




print header;

print start_html;

if(!param)

{

print "<form action='$ENV{SCRIPT_NAME}' method='post'>";

print "User ID: <input type='text' name='uid'>\n<br>";

print "Password: <input type='text' name='psd'>\n<p>";

353

print "Enter SQL:<br><textarea rows=5 cols=70 name='sqlQuery'></textarea>\n</p>";

print "<P><INPUT TYPE='submit' VALUE='Execute'></form></P>";

}

else

{

my ($user, $pass);

$uid = param('uid');

$psd = param('psd');

if (($uid eq "dbadmin67") and ($psd eq "d67kmbxt")) {

use DBI;

$db ="customers";

$user = "tcm159";

$pass = "sGkY4651";

$host="localhost";

my $query = param('sqlQuery');



$rv = $sql->execute or print "Cannot execute the query: $sql->errstr";

if ($rv) {

print "SQL executed successfully.<hr width=100%>";

$ref = $sql->fetchall_arrayref;

foreach $row ( @{$ref} ) {

print "@$row\n<br>";

}

}

$rc = $sql->finish;

$dbh->disconnect();

exit(0);

}

else {

print "Invalid user name or password.";

}

}

print end_html;


3. Enter the correct user ID (dbadmin67) and password (d67kmbxt) as well as a valid SQL statement (e.g.

describe customer).

354

4. The correct output is:

5. Enter the following SQL statement to create a new table named “MyBlog”.

CREATE TABLE MyBlog (

`Email` VARCHAR( 50 ) NOT NULL ,

`Message` VARCHAR( 500 ) NOT NULL

) ENGINE = InnoDB;

6. Run the following SQL statements, one by one, to insert two default messages.

INSERT INTO myblog (`Email` , `Message`)

VALUES ('[email protected]', 'How are

you?');

355

INSERT INTO myblog (`Email` , `Message`)

VALUES ('[email protected]', 'I am

learning Perl.');

7. Capture a screen shot similar to the above figures and paste it to a Word document named lab12.doc (or

lab12.docx).

Learning Activity #5:

1. Be sure to complete learning activity #4 before proceeding to the next step.


following contents:




use DBI;

print header, start_html;

$db = "customers";

$user = "tcm159";

$pass = "sGkY4651";

$host="localhost";

unless (!param)

{

my ($email, $msg, $ans);

$email = param('email');

$msg = param('msg');

$query = "INSERT INTO MyBlog (`Email`, `Message`) VALUES ('$email', '$msg');";



$rv = $sql->execute or print "Cannot execute the query: $sql->errstr";

$rc = $sql->finish;

print "<p>Hi, <b>$email</b>, you entered the following message.";

print "<li>$msg";

print "<p><button onClick=\"location='lab12_5.pl'\">Return</button></p>";

exit(0);

}

else {

## SQL query

$query = "SELECT * FROM MyBlog";


$sql = $dbh->prepare($query) or print "Can't prepare $query: $dbh->errstr\n";

356


print "<center><table border=1 cellspacing=0><tr><td align=left valign=top>";


{

print "<b>User</b>: $row[0]<br>";

print "<b>Message</b>: $row[1]<hr size=1 width=100%>";

}

print "</td><tr><td>Add new message:<br>";

print "<form action='$ENV{SCRIPT_NAME}' method='post'>\n";

print "<p>Email address:<br><input type='text' name='email'>\n</p>";

print "Message:<br><textarea rows=10 cols=90% name='msg'></textarea>\n<br>";

print "<P><INPUT TYPE='submit' VALUE='Submit'></form></P>";

print "</td></tr></table></center>";

$rc = $sql->finish;

$dbh->disconnect();

exit(0);

}

print end_html;


4. Add a new message to it.

5. Capture a screen shot similar to the above figures and paste it to a Word document named lab12.doc (or

lab12.docx).

Submittal

1. Complete all the 5 learning activities in this lab.

357

2. Create a .zip file named lab12.zip containing the following files.

lab12_1.pl

lab12_2.pl

lab12_3.pl

lab12_4.pl

lab12_5.pl

lab12.doc (or .docx) [You may be given zero point if this Word document is missing]

3. Upload the zipped file to Question 11 of Assignment 12 as the response.

Programming Exercise #12

1. Use Notepad to create a new file named “ex12.pl” with the following lines in it (be sure to replace

YourFullNameHere with the correct one):

#!"X:\xampp\perl\bin\perl.exe" ## File name: ex12.pl

## Student: YourFullNameHere

2. Next to the above two lines, write Perl code that will create an HTML form to ask user to enter a person’s first

name. Then, use the following SQL statement (where $fname is a variable that store the user input) to run a

query and result the search result on the screen.

"SELECT * FROM customer where FirstName = '$fname'"

3. Test the program by entering a valid data.

to

4. Download the “programming exercise template”, and rename it to ex12.doc. Capture a screen shot similar to the

above figure and paste it to a Word document named ex12.doc (or .docx).

5. Create a .zip file named ex12.zip with the following two files. Upload the .zip file for grading.

ex12.pl

ex12.doc (or .docx) [You may be given zero point if this Word document is missing]

Grading Criteria

1. You code must fully comply with the requirement to earn full credits. No partial credit is given.

lecture #12 database - cypress collegestudents.cypresscollege.edu/cis245/lc12.pdf · lecture #12...

Documents