데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

The Perl programming language has gone from a tool primarily used by Unix systems administrators to the most widely used development platform for the World Wide Web. Perl was not designed for the web, but its ease of use and powerful text handling abilities have made it a natural for CGI programming. Similarly, when mSQL first entered the scene, its extremely small footprint and execution time were very attractive to web developers who needed to serve thousands of transactions a day. MySQL, with its enhanced speed and capabilities provided an even greater incentive for web developers. Therefore, it was only natural that a Perl interface to both MySQL and mSQL was developed that allowed the best of both worlds.
TIP: At the time of this writing there are two interfaces between MySQL and mSQL and Perl. The original consists of Mysql.pm and Msql.pm, custom interfaces that work only with MySQL and mSQL, respectively. The other, newer, interface is a plug-in for the Database Independent (DBI) set of modules. DBI is an attempt to provide a common Perl API for all database accesses and enable greater behind-the-scenes portability. The DBI interface has become the most robust and standard, and the makers of MySQL recommend that all work be done using DBI as development of the Mysql.pm and Msql.pm modules has ceased. However, many legacy systems still use these modules, so both will be covered here.
DBI
The recommended method for accessing MySQL and mSQL databases from Perl is the DBD/DBI interface. DBD/DBI stands for DataBase Dependent/DataBase Independent. The name arises from the two-layer implementation of the interface. At the bottom is the database dependent layer. Here, modules exist for each type of database accessible from Perl. On top of these database dependent modules lies a database independent layer. This is the interface that you use to access the database. The advantage of this scheme is that the programmer only has to learn one API, the database independent layer. Every time a new database comes along, someone needs only to write a DBD module for it and it will be accessible to all DBD/DBI programmers.
As with all Perl modules, you must use DBI to get access:
#!/usr/bin/perl -w
 
use strict;
use CGI qw(:standard);
use DBI;

TIP: When running any MySQL/mSQL Perl programs, you should always include the -w command line argument. With this present, DBI will redirect all MySQL and mSQL specific error messages to STDERR so that you can see any database errors without checking for them explicitly in your program.

All interactions between Perl and MySQL and mSQL are conducted through what is known as a database handle. The database handle is an object--represented as a scalar reference in Perl--that implements all of the methods used to communicate with the database. You may have as many database handles open at once as you wish. You are limited only by your system resources. The connect() method uses a connection format of DBI:servertype:database:hostname:port (hostname and port and optional), with additional arguments of username and password to create a handle:

my $dbh = DBI->connect(\DBI:mysql:mydata\, undef, undef);
my $dbh = DBI->connect(\DBI:mSQL:mydata:myserver\, undef, undef);
my $dbh = DBI->connect(\DBI:mysql:mydata\,\me\,\mypass\);

The servertype attribute is the name of the DBD database-specific module, which in our case will be either \mysql\ or \mSQL\ (note capitalization). The first version creates a connection to the MySQL server on the local machine via a Unix-style socket. This is the most efficient way to communicate with the database and should be used if you are connecting to a local server. If the hostname is supplied it will connect to the server on that host using the standard port unless the port is supplied as well. If you do not provide a username and password when connecting to a MySQL server, the user executing the program must have sufficient privileges within the MySQL database. The username and password should always be left undefined for mSQL databases.

TIP: Perl 5 has two different calling conventions for modules. With the object-oriented syntax, the arrow symbol \->\ is used to reference a method in a particular class (as in DBI->connect). Another method is the indirect syntax, in which the method name is followed by the class name, then the arguments. The last connect method above would be written as connect DBI \DBI:mysql:mydata\, \me\, \mypass\. Early versions of the Msql.pm used the indirect syntax exclusively and also enforced a specific method of capitalization inspired by the mSQL C API. Therefore, a lot of older MsqlPerl code will have lines in it like SelectDB $dbh \test\ where a simple $dbh->selectdb(\test\) would do. If you haven\t guessed, we are partial to the object-oriented syntax, if only because the arrow makes the relationship between class and method clear.

Once you have connected to the MySQL or mSQL server, the database handle--$dbh in all of the examples in this section--is the gateway to the database server. For instance, to prepare an SQL query:

$dbh->prepare($query);

WARNING: When using mSQL you may select only one database at a time for a particular database handle. The mSQL server imposes this limit. However, you may change the current database at any time by calling connect again. With MySQL, you may include other databases in your query by explicitly naming them. In addition, with both MySQL and mSQL, if you need to access more than one database concurrently, you can create multiple database handles and use them side by side.

Chapter 21, Perl Reference, describes the full range of methods and variables supplied by DBI as well as Mysql.pm and Msql.pm.

As an example of the use of DBI consider the following simple programs. In Example 10-1, datashow.cgi accepts a hostname as a parameter--\localhost\ is assumed if no parameter is present. The program then displays all of the databases available on that host.

Example 10-1: The CGI datashow.cgi shows all of the databases on a MySQL or mSQL server
#!/usr/bin/perl -w
 
use strict;
use CGI qw(:standard);
use CGI::Carp;
	# Use the DBI module
use DBI;
CGI::use_named_parameters(1);
 
my ($server, $sock, $host);
 
my $output = new CGI;
$server = param(\server\) or $server = \;
 
# Prepare the MySQL DBD driver
my $driver = DBI->install_driver(\mysql\);
 
	my @databases = $driver->func($server, \_ListDBs\);
 
# If @databases is undefined we assume
# that means that the host does not have
# a running MySQL server. However, there could be other reasons
# for the failure. You can find a complete error message by
# checking $DBI::errmsg.
if (not @databases) {
        print header, start_html(\title\=>\Information on $server\,
        \BGCOLOR\=>\white\);
        print <$server
$server does not appear to have a running mSQL server.

END_OF_HTML
        exit(0);
}
 
       print header, start_html(\title\=>\Information on $host\, 
                                \BGCOLOR\=>\white\);
       print <$host

$host\s connection is on socket $sock.

Databases:


END_OF_HTML
foreach (@databases) {
        print \$_\n\;
}
print <

END_OF_HTML
exit(0)

In Example 10-2, tableshow.cgi accepts the name of a database server (default is \localhost\) and the name of a database on that server. The program then shows all of the available tables on that server.

Example 10-2: The CGI tableshow.cgi shows all of the tables within a database 
#!/usr/bin/perl -w
 
use strict;
use CGI qw(:standard);
use CGI::Carp;
	# Use the Msql.pm module
use DBI;
CGI::use_named_parameters(1);
 
my ($db);
my $output = new CGI;
$db = param(\db\) or die(\Database not supplied!\);
 
# Connect to the requested server.
my $dbh = DBI->connect(\DBI:mysql:$db:$server\, undef, undef);
 
# If $dbh does not exist, the attempt to connect to the
# database server failed. The server may not be running,
# or the given database may not exist.
if (not $dbh) {
        print header, start_html(\title\=>\Information on $host => $db\,
        \BGCOLOR\=>\white\);
 
        print <$host
$db
The connection attempt failed for the following reason:

$DBI::errstr

END_OF_HTML
        exit(0);
} 
 
print header, start_html(\title\=>\Information on $host => $db\,
        \BGCOLOR\=>\white\);
print <$host
$db

Tables:


END_OF_HTML
# $dbh->listtable returns an array of the tables that are available
# in the current database.
my @tables = $dbh->func( \_ListTables\ );
foreach (@tables) {
         print \$_\n\;
}
print <

END_OF_HTML
       exit(0);

And, finally, Example 10-3 shows all of the information about a specific table.

Example 10-3: The CGI tabledump.cgi shows information about a specific table 
#!/usr/bin/perl -w
 
use strict;
use CGI qw(:standard);
use CGI::Carp;
	# Use the DBI module
use DBI;
CGI::use_named_parameters(1);
 
my ($db,$table);
my $output = new CGI;
$server = param(\server\) or $server = \;
$db = param(\db\) or die(\Database not supplied!\);
 
# Connect to the requested server.
my $dbh = DBI->connect(\DBI:mysql:$db:$server\, undef, undef);
 
# We now prepare a query for the server asking for all of the data in
# the table.
my $table_data = $dbh->prepare(\select * from $table\);
# Now send the query to the server.
$table_data->execute;
 
# If the return value is undefined, the table must not exist. (Or it could
# be empty; we don\t check for that.)
if (not $table_data) {
        print header, start_html(\title\=>
        \Information on $host => $db => $table\, \BGCOLOR\=>\white\);
 
        print <$host
$db
The table \$table\ does not exist in $db on $host.

END_OF_HTML
        exit(0);
}
 
# At this point, we know we have data to display. First we show the
# layout of the table.
print header, start_html(\title\=>\Information on $host => $db => $table\,
        \BGCOLOR\=>\white\);
print <$host
$db
$table


END_OF_HTML
 
# $table_data->name returns a referece to an array 
# of the fields of the database.
my @fields = @{$table_data->NAME};
# $table_data->type return an array reference of the types of fields.
# The types returned here are in SQL standard notation, not MySQL specific.
my @types = @{$table_data->TYPE};
# $table_data->is_not_null returns a Boolean array ref indicating which fields
# have the \NOT NULL\ flag.
my @not_null = @{$table_data->is_not_null};
# $table_data->length return an array ref of the lengths of the fields. This is
# fixed for INT and REAL types, but variable (defined when the table was
# created) for CHAR.
my @length = @{$table_data->length};
 
# All of the above arrays were returned in the same order, so that $fields[0],
# $types[0], $not_null[0] and $length[0] all refer to the same field.
 
foreach $field (0..$#fields) {
        print \\n\;
print \\n\;
}
 
print <

Data


END_OF_HTML
 
# Now we step through the data, row by row, using DBI::fetchrow_array().
# We save the data in an array that has the same order as the informational
# arrays (@fields, @types, etc.) we created earlier.
while(my(@data)=$table_data->fetchrow_array) {
        print \\n\;
        for (0..$#data) {
                print \$fields[$_] => $data[$_]\n\;
        }
        print \\;
}
 
print <

      END_OF_HTML

An Example DBI Application
DBI allows for the full range of SQL queries supported by MySQL and mSQL. As an example, consider a database used by a school to keep track of student records, class schedules, test scores, and so on. The database would contain several tables, one for class information, one for student information, one containing a list of tests, and a table for each test. MySQL and mSQL\s ability to access data across tables--such as the table-joining feature--enables all of these tables to be used together as a coherent whole to form a teacher\s aide application.

To begin with we are interested in creating tests for the various subjects. To do this we need a table that contains names and ID numbers for the tests. We also need a separate table for each test. This table will contain the scores for all of the students as well as a perfect score for comparison. The test table has the following structure:

CREATE TABLE test (
  id INT NOT NULL AUTO_INCREMENT,
  name CHAR(100),
  subject INT,
  num INT
) 

The individual tests have table structures like this:

CREATE TABLE t7 (
  id INT NOT NULL,
  q1 INT,
  q2 INT,
  q3 INT,
  q4 INT,
  total INT
) 

The table name is t followed by the test ID number from the test table. The user determines the number of questions when he or she creates the table. The total field is the sum of all of the questions.

The program that accesses and manipulates the test information is test.cgi. This program, which follows, allows only for adding new tests. Viewing tests and changing tests is not implemented but is left as an exercise. Using the other scripts in this chapter as a reference, completing this script should be only a moderate challenge. As it stands, this script effectively demonstrates the capabilities of DBI:[1]

#!/usr/bin/perl -w
 
use strict;
require my_end;
 
use CGI qw(:standard);
my $output = new CGI;
use_named_parameters(1);
 
# Use the DBI module.
use DBI;
# DBI::connect() uses the format \DBI:driver:database\, in our case we are
# using the MySQL driver and accessing the \teach\ database.
my $dbh = DBI->connect(\DBI:mysql:teach\);
The add action itself is broken up into three separate functions. The first 
function, add, prints out the template form for the user to create a new 
test.
sub add {
   $subject = param(\subject\) if (param(\subjects\));
   $subject = \ if $subject eq \all\;
 
   print header, start_html(\title\=>\Create a New Test\,
      \BGCOLOR\=>\white\);
   print <Create a New Test


Subject: 
END_OF_HTML
   my @ids = ();
   my %subjects = ();
   my $out2 = $dbh->prepare(\select id,name from subject order by name\);
   $out2->execute;
   # DBI::fetchrow_array() is exactly analogous to Msql::fetchrow()
   while(my($id,$subject)=$out2->fetchrow_array) {
      push(@ids,$id);
      $subjects{\$id\} = $subject;
   }
   print popup_menu(\name\=>\subjects\,
      \values\=>[@ids],
      \default\=>$subject,
      \labels\=>\%subjects);
   print <
Number of Questions: 

A name other identifier (such as a date) for the test: 
 


 

END_OF_HTML
}

This function displays a form allowing the user to choose a subject for the test along with the number of questions and a name. In order to print out a list of available subjects, the table of subjects is queried. When using a SELECT query with DBI, the query must first be prepared and then executed. The DBI::prepare function is useful with certain database servers which allow you to perform operations on prepared queries before executing them. With MySQL and mSQL however, it simply stores the query until the DBI::execute function is called.

The output of this function is sent to the add2 function as shown in the following:

sub add2 {
   my $subject = param(\subjects\);
   my $num = param(\num\);
   $name = param(\name\) if param(\name\);
 
   my $out = $dbh->prepare(\select name from subject where id=$subject\);
   $out->execute;
   my ($subname) = $out->fetchrow_array;
 
   print header, start_html(\title\=>\Creating test for $subname\,
      \BGCOLOR\=>\white\);
   print <Creating test for $subname
$name






Enter the point value for each of the questions. The points need not
add up to 100.

END_OF_HTML
   for (1..$num) {
      print qq%$_:  %;
      if (not $_ % 5) { print \
\n\; }
   }
   print <
Enter the text of the test:




 

END_OF_HTML
}

In this function, a form for the test is dynamically generated based on the parameters entered in the last form. The user can enter the point value for each question on the test and the full text of the test as well. The output of this function is then sent to the final function, add3, as shown in the following:

sub add3 {
   my $subject = param(\subjects\);
   my $num = param(\num\);
 
   $name = param(\name\) if param(\name\);
        
   my $qname;
   ($qname = $name) =~ s/\/\\/g;
   my $q1 = \insert into test (id, name, subject, num) values (
      \, \$qname\, $subject, $num)\;
 
   
 
   my $in = $dbh->prepare($q1);
   $in->execute;
 
   # Retrieve the ID value MySQL created for us
   my $id = $in->insertid;
 
   my $query = \create table t$id (
      id INT NOT NULL,
      \;
 
   my $def = \insert into t$id values ( 0, \;
 
   my $total = 0;
   my @qs = grep(/^q\d+$/,param);
   foreach (@qs) {
      $query .= $_ . \ INT,\n\;
      my $value = 0;
      $value = param($_) if param($_);
      $def .= \$value, \;
      $total += $value;
   }
   $query .= \total INT\n)\;
   $def .= \$total)\;
 
   my $in2 = $dbh->prepare($query);
   $in2->execute;
   my $in3 = $dbh->prepare($def);
   $in3->execute;
 
   # Note that we store the tests in separate files. This is 
   # useful when dealing with mSQL because of its lack of BLOBs. 
   # (The TEXT type provided with mSQL 2 would work, but
   # inefficently.)
   # Since we are using MySQL, we could just as well
   # stick the entire test into a BLOB.
   open(TEST,\>teach/tests/$id\) or die(\A: $id $!\);
   print TEST param(\test\), \n\;
   close TEST;
 
   print header, start_html(\title\=>\Test Created\,
     \BGCOLOR\=>\white\);
   print <Test Created

The test has been created.

Go to the Teacher\s Aide home page.

Go to the Test main page.

Add another test.

END_OF_HTML
}

Here we enter the information about the test into the database. In doing so we take a step beyond the usual data insertion that we have seen so far. The information about the test is so complex that each test is best kept in a table of its own. Therefore, instead of adding data to an existing table, we have to create a whole new table for each test. First we create an ID for the new test using MySQL auto increment feature and enter the name and ID of the test into a table called test. This table is simply an index of tests so that the ID number of any test can be quickly obtained. Then we simultaneously create two new queries. The first is a CREATE TABLE query which defines our new test. The second is an INSERT query that populates our table with the maximum score for each question. These queries are then sent to the database server, completing the process (after sending a success page to the user). Later, after the students have taken the test, each student will get an entry in the test table. Then entries can then be compared to the maximum values to determine the student\s score.

Msql.pm
The Msql.pm module is the original Perl interface to mSQL. While it has been replaced by the DBI modules, there are still many sites that depend on this old interface. To illustrate the use of Msql.pm, we will continue the teacher\s aide example.

Since we need classes in which to give the tests, let\s examine the table of subjects. The table structure looks like this:

CREATE TABLE subject (
  id INT NOT NULL,
  name CHAR(500),
  teacher CHAR(100)
) 
 
CREATE UNIQUE  INDEX idx1 ON subject (
        id,
        name,
        teacher
) 
 
CREATE SEQUENCE ON subject

The id number is a unique identifier for the class, while the name and teacher fields are the name of the course and the name of the teacher respectively. There is also an index of all three of the fields that speeds up queries. Finally, we define a sequence for the table. The ID numbers are generated by this sequence.

The CGI program to access and manipulate this data must to several things.

Search for a subject in the database. 
Show the subject that is the result of a search. 
Add a new subject to the database. 
Change the values of a subject in the database. 
With the power of Perl and mSQL, we can easily consolidate all of these functions into one file, subject.cgi. We can do this by separating each operation into its own function. The main portion of the program will be a switchboard of sorts that directs incoming requests to the proper function. We will describe the actions themselves later.

# Each of the different parts of the script is selected via the 
# \action\
# parameter. If no \action\ is supplied, the default() function is
# called.
# Otherwise the appropriate function is called.
&default if not param(\action\);
# This trick comes from Camel 2 and approximates the \switch\
# feature of C.
foreach[AO4] (param(\action\)) {
   /view/ and do { &view; last; };
   /add$/ and do { &add; last; };
   /add2/ and do { &add2; last; };
   /add3/ and do { &add3; last; };
   /add4/ and do { &add4; last; };
   /schange$/ and do { &schange; last; };
   /schange2/ and do { &schange2; last; };
   /lchange$/ and do { &lchange; last; };
   /lchange2/ and do { &lchange2; last; };
   /lchange3/ and do { &lchange3; last; };
   /delete/ and do { &delete; last; };
   &default;
}

TIP: The \add,\ \schange,\ and \lchange\ entries must have an anchoring \$\ in the regular expression so that they do not match the other functions similar to them. Without the \$\, \add\ would also match add2, add3 and add4. An alternative method would be to place \add,\ \schange,\ and \lchange\ after the other functions. That way they would only be called if none of the others matched. However, this method could cause trouble if other entries are added later. A third method would be to completely disambiguate all of the entries using /^view$/, /^add$/, etc. This involves slightly more typing but removes all possibility of error.

Now all we have to do is fill in the details by implementing each function.

The default function prints out the initial form seen by the user. This is the form that allows the user to choose which action to perform. This function is called if the CGI program is accessed without any parameters, as with http://www.myserver.com/teach/subject.cgi, or if the ACTION parameter does not match any of the existing functions. An alternative method would be to create a function that prints out an error if the ACTION parameter is unknown.

sub default {
   print header, start_html(\title\=>\Subjects\,\BGCOLOR\=>\white\);
   print <Subjects
Select an action and a subject (if applicable).

 
END_OF_HTML
   # See \sub print_subjects\ below.
   &print_subjects;
   print <
 


END_OF_HTML
 
}

There are five main actions: \view,\ \add,\ \schange\ (change the information about a subject), \lchange\ (change the class list for a subject), and \delete\. For illustration, we will examine the \add\ action in detail here. The \add\ action is broken up into four separate functions because interaction with the user is required up to four times. Hidden variables are used to pass information from form to form until the class is finally created.

The first add function generates the form used to enter the initial information about the class, including its name, the teacher\s name, and the number of students in the class.

sub add {
   my (%fields);
   foreach (\name\,\size\,\teacher\) {
      if (param($_)) { $fields{$_} = param($_); }
      else { $fields{$_} = \; }
   }
 
   print header, start_html(\title\=>\Add a Subject\,\BGCOLOR\=>\white\);
   print <Add a Subject


Subject Name: 

Teacher\s Name: 

Number of Students in Class: 


 



Go back to the main Subject page.

Go to the Teacher\s Aide Home Page.

END_OF_HTML
 
}

The function checks to see if any of the fields have preassigned values. This adds extra versatility to the function in that it can now be used as a template for classes with default values--perhaps generated by another CGI program somewhere.

The values from the first part of the add process are passed back to CGI program into the add2 function. The first thing that add2 does is check whether the class already exists. If it does, an error message is sent to the user and he or she can change the name of the class.

If the class does not already exist, the function checks how many students were entered for the class. If none were entered, the class is created without any students. The students can be added later. If the number of students was specified, the class is created and a form is displayed where the user can enter the information about each student.

sub add2 {
   ...
   my $name = param(\name\);
   # We need one copy of the name that is encoded for the URL.
   my $enc_name = &cgi_encode($name);
   # We also need a copy of the name that is quoted safely for insertion
   # into the database. Msql provides the Msql::quote() function for that
   # purpose.
   my $query_name = $dbh->quote($name);
 
   # We now build a query to see if the subject entered already exists.
   my $query = 
      \select id, name, teacher from subject where name=$query_name\;
 
   # If the user supplied a teacher\s name, we check for that teacher
   # specifically, since there can be two courses with the same name but
   # different teachers.
   if (param(\teacher\)) {
      $teacher = param(\teacher\);
      $enc_teacher = &cgi_encode($teacher);
      my $query_teacher = $dbh->quote($teacher);
      $query .= \ and teacher=$query_teacher\;
   }
 
   # Now we send the query to the mSQL server.
   my $out = $dbh->query($query);
   # We check $out->numrows to see if any rows were returned. If
   # there were any, and the user didn\t supply an \override\
   # parameter, then we exit with a message that the class already
   # exists, and giving the user a change to enter the class anyway
   # (by resubmitting the form with the \override\ parameter set. 
    if ($out->numrows and not param(\override\)) {
      # Print \Class already exists\ page.
      ...
   } else {
      # Now we enter the information into the database.
      # First, we need to select the next number from the
      # table\s sequence.
      $out = $dbh->query(\select _seq from subject\);
      my ($id) = $out->fetchrow;
 
      # Then we insert the information into the database, using
      # the sequence number we just obtained as the ID.
      $query = \INSERT INTO subject (id, name, teacher) 
         VALUES ($id, \$name\, \$teacher\)\;
      $dbh->query($query);
 
      # If the user did not specify a class size, we exit with
      # a message letting the user know that he or she can add
      # students later.
      if (not param(\size\)) {
         # Print success page.
         ...
      } else {
         # Now we print a form, allowing the user to enter the
         # names of each of the students in the class.
         print header, start_html(\title\=>\Create Class List\,
            \BGCOLOR\=>\white\);
         print <Create Class List

$name has been added to the database. You can
now enter the names of the students in the class. 
You may add or drop students later from the 
main
Subject page.





Fields

 Field Type Size NOT NULL

$fields[$field] $types[$field] \;
        print $length[$field] if $types[$field] eq \SQL_CHAR\;
        print \ \;
        print \Y\ if ($not_null[$field]);
        print \
END_OF_HTML
         for $i (1..$size) {
         print <
END_OF_HTML
 
         }
         print <



END_OF_HTML
 
      }
   }
}

Note that the function used three copies of the name parameter. To use a variable as part of a URL, all special characters must be URL-escaped. A function called cgi_encode is provided with the code for this example which performs this operation. Secondly, to insert a string into the mSQL database, certain characters must be escaped. The MsqlPerl interface provides the function quote--accessible through any database handle--to do this. Finally, an unescaped version of the variable is used when displaying output to the user.
 
When adding the class to the database, mSQL\s sequence feature comes in handy. Remember that a sequence was defined on the class table. The values of this sequence are used as the unique identifiers for each class. In this way two classes can have the same name (or same teacher, etc.) and still be distinct. This also comes in handy when modifying the class later. As long as the unique ID is passed from form to form, any other information about the class can safely be changed.
 
Finally, notice that the student entry form displayed by this function is dynamically generated. The number of students entered for the class is used to print out a form with exactly the right number of entries. Always remember that the CGI program has complete control over the generated HTML. Any part, including the forms, can be programmatically created.
 
If the user did not enter any students for the class, we are now finished. The user can use the change feature to add students later. However, if students were requested, the information about those students is passed onto the stage in the add3 function, as shown in the following:
sub add3 {
   if (not param(\id\)) { &end(\An ID number is required\); }
   my $id = param(\id\);
 
   my @list = &find_last_student;
   my ($ref_students,$ref_notstudents) = 
&find_matching_students(@list);
 
   @students = @$ref_students if $ref_students;
   @notstudents = @$ref_notstudents if $ref_notstudents;
 
   if (@notstudents) {
      # Print form telling the user that there are nonexisting 
      # students in the list. The user can then automatically create 
      # the students or go back and fix any typos.
      ...
   } else {
      &update_students($id,@students);
      # Print success page.
      ...
   }
}

The bulk of this function\s work is performed by other functions. This is because other parts of the CGI program have similar needs so it is efficient to factor the common code into shared functions. The first such function is find_last_student, which examined the form data and returns a list of the form numbers--the form numbers are not related to the ID numbers in the database--of each student entered by the user. This is necessary because, as mentioned earlier, the previous form is dynamically generated and there is no way to immediately know how many students are included.
sub find_last_student {
   my @params = param;
   my @list = ();
   foreach (@params) {
      next if not param($_); # Skip any \empty\ fields
      if (/^(first|middle|last|ext)(\d+)/) { 
         my $num = $2;
         if (not grep(/^$num$/,@list)) { push(@list,$num); }
      }
   }
   @list = sort { $a <=> $b} @list;
   return @list;
}

Note that the function returns all of the numbers, not just the last number--which would presumably be the number of students entered. Even though the previous form printed out the number of entries the user requested, there is no guarantee that the user filled all of them out. He or she may have missed or skipped a row, which would not be included with the form data. Therefore, it is necessary to find out each number that was entered. The output of this function is then sent to the next \helper\ function: find_matching_students, as shown in the following:
sub find_matching_students {
   my @list = @_;
   my ($i,@students,@notstudents);
   @students = ();
   @notstudents = ();
   if (@list) {
      foreach $i (@list) {
         my @query = ();
         # Build a query that looks for a specific student.
         my $query = \select id, subjects from student where \;
         foreach (\first\,\middle\,\last\,\ext\) {
            if (param(\$_$i\)) {
               my $temp = param(\$_$i\);
               # Single quotes are the field delimiters for mSQL (and MySQL),
               # so they must be preceded with the escape character \\, 
               # which is escaped itself so that it is interpreted literally.
               $temp =~ s/\/\\/g;
               push(@query,\$_ = \$temp\);
            }
         }
         $query .= join(\ and \,@query);
        
         # Send the query to the database.
         my $out = $dbh->query($query);
         # If the database doesn\t return anything, add the
         # student to the @notstudents array.
         if (not $out->numrows) {
            push(@notstudents,[ param(\first$i\), 
            param(\middle$i\),
            param(\last$i\), param(\ext$i\) ]);
            # Otherwise add the student to the @students array.
         } else {
            my ($id,$subjects) = $out->fetchrow;
            push(@students,[$id,$subjects]);
         }
      }
   }
   return(\@students,\@notstudents);
}

This function goes through each of the given student names and checks the database to see if they already exist. If they do exist their information is stored in an array called @students, otherwise they are put in @notstudents. The information about each student is kept in an anonymous array, creating a student object of sorts. Finally the function returns references to both of the arrays. It cannot return the data as regular arrays because there would be no way to tell where one array ended and the other began.
 
The final helper function is update_students, which adds the class to each existing student\s list of classes.
sub update_students {
   my $id = shift;
   my @students = @_;
   foreach (@students) {
      my($sid,$subjects)=@$_;
      if (not $subjects) { $subjects = \:$id:\; }
      elsif ($subjects !~ /:$id:/) { $subjects .= \$id:\; }
      my $query = \update student set subjects=\$subjects\
         where id=$id\;
      $dbh->query($query);
   }
}

This function queries the student table, which is entirely separate from the subject table. Within a single CGI program, you can interact with any number of different tables within a database. You can even switch between databases, but you can only have one database selected at a time. This function retrieves the subject list for each given student and adds the new subject to their list if it is not there already.
 
At this point all contingencies are taken care of except for the case where the subject has students that do not already exist in the student table. For this case, the list of new students are sent to the add4 function as shown in the following:
sub add4 {
        # Get list of @students and @notstudents
        &update_students($id,@students) if @students;
        &insert_students($id,@notstudents) if @notstudents;
 
        # Print success page.
}

This function separates the list of students into existing and nonexisting students using the same method as add3. It then updates the existing students using update_students shown earlier. Nonexisting students, shown in the following, are sent to the new helper function insert_students:
sub insert_students {
   foreach $i (@list) {
      # This selects the next number in the sequence defined on the
      # table. We then use this number as the ID of the student.
      my $out = $dbh->query(\select _seq from student\);
      my($sid) = $out->fetchrow;
 
      # We have to quote all of the text strings for inclusion
      # in the database.
      my ($first, $middle, $last, $ext) = ( 
         $dbh->quote(param(\first$i\)),
         $dbh->quote(param(\middle$i\)), 
         $dbh->quote(param(\last$i\)), 
         $dbh->quote(param(\ext$i\))
      );
      my $query = \insert into student (id, first, middle, last, 
         ext, subjects) VALUES ($sid, $first, $middle, 
         $last, $ext, \:$id:\)\;
      $dbh->query($query);
   }
}

This function again accesses the student table rather than the subject table. An ID number for the new students is retrieved from the sequence defined on the student table, and then the student is inserted into the table using that ID.
 
MysqlPerl
 
Monty Widenius, the author of MySQL, also wrote the Perl interface to MySQL, Mysql.pm. This was based on the mSQL module, Msql.pm. Thus, the interfaces of the two modules are almost identical. In fact, we recently converted an entire site from mSQL to MySQL by running \perl -e \s/^Msql/Mysql/\ *.cgi\ in every directory containing a CGI. This covers 95% of the work involved. Of course, this does not give you any of the advantages of MySQL, but it is a quick and easy way to start down the road to MySQL. Mysql.pm is maintained as part of msql-mysql-modules by Jochen Wiedmann.
 
TIP: One of the largest differences between MySQL and mSQL is the way they handle sequences. In mSQL, a sequence is defined on a table with a command like CREATE SEQUENCE on tablename. The sequence value is then read as if it were a normal table value with the command SELECT _seq from tablename. MySQL adds the flag AUTO_INCREMENT to the primary key. Whenever a null value is inserted into this field, it is automatically incremented. Both MySQL and mSQL allow only one sequence per table. For a full discussion on sequences in MySQL and mSQL, see Chapter 6, SQL According to MySQL and mSQL.
 
As an example of some of the features of Mysql.pm, let\s go back to the tests example. Now that we have subject.cgi taken care of, the next step is the table of student information. The structure of the students table is as follows:
CREATE TABLE student (
  id INT NOT NULL auto_increment,
  first VARCHAR(50),
  middle VARCHAR(50),
  last VARCHAR(50),
  ext VARCHAR(50),
  subjects VARCHAR(100),
  age INT,
  sex INT,
  address BLOB,
  city VARCHAR(50),
  state VARCHAR(5),
  zip VARCHAR(10),
  phone VARCHAR(10),
  PRIMARY KEY (id)
) 

All of the information used by the subject.cgi program is in this table, as well as other information pertaining to the student. The program that handles this table, student.cgi must perform all of the functions that subject.cgi did for the subject table.
 
TIP: It is not possible to access a mSQL database with the Mysql.pm module, or MySQL with Msql.pm. The student.cgi program expects to find a MySQL version of the subjects table. Likewise, the subject.cgi program expects an mSQL version of the students table.
 
To illustrate the operation of Mysql.pm, we will examine in detail the portion of student.cgi that enables a user to change the information about a student. Just like the \add\ action in the Msql.pm example was broken up into four separate functions, the \change\ action here is separated into three individual functions.
 
The first function, change, prints out a form that allows the user to search for a student to change, as shown in the following:
sub change {
   print header, start_html(\title\=>\Student Change Search\,
      \BGCOLOR\=>\white\);
   &print_form(\search2\,\Search for a Student to Change\,1);
   print <


 
 

END_OF_HTML
}

The form used for searching for a student to \change\ is so similar to the form used to searching for a student to \view\ and the one to \add\ a student that a single function, print_form, is used for all three purposes, as shown in the following:
sub print_form {
   my ($action,$message,$any) = @_;
 
   print <

$message
END_OF_HTML
   if ($any) {
      print <Search for  of your choices.
END_OF_HTML
   }
   print <
First: 
 Middle: 
 Last: 
 Jr./III/etc.: 


Address: 

City:  
 State: 
 ZIP: 

Phone: 

Age:  Sex: 


Enrolled in:

END_OF_HTML
   &print_subjects(\MULTIPLE SIZE=5\);
 
}

By using three parameters, this function customizes a form template so that it can be used for several very different purposes. Notice that this helper function calls another helper function, print_subjects. This function queries the subject table as seen in the Msql.pm example and prints out a list of all of the available subjects.
sub print_subjects {
   my $modifier = \;
   $modifier = shift if @_;
   print qq%\n\;
}

The search parameters entered in this first form are then sent to the search2 function, which actually performs the search. This is actually the function written to search for a student to view. Since its function is exactly what we need, we can piggy-back off of it as long as we tell it that we want to go to the next change function, change2, after the search. That is why we have the hidden variable subaction=change2 in the form. It tells search2, as shown in the following, where to send the user next:
sub search2 {
   my $out = $dbh->query(&make_search_query);
   my $hits = $out->numrows;
   my $subaction = \view\;
   $subaction = param(\subaction\) if param(\subaction\);
   print header, start_html(\title\=>\Student Search Result\,
      \BGCOLOR\=>\white\);
 
   if (not $hits) {
      print <No students found

No students matched your criteria.
END_OF_HTML
   } else {
      print <$hits students found


END_OF_HTML
      while(my(%fields)=$out->fetchhash) {
         print qq%
$fields{\first\}
 $fields{\middle\} $fields{\last\}%;
         print \, $fields{\ext\}\ if $fields{\ext\};
         print \n\;
      }
   }
   print <

Search again.

END_OF_HTML
}

With help from the make_search_query function, this function first searches for students that match the search term. It then displays a list of the matches from which the user can select. The ID number of the selected entry is then sent to the change2 function, as shown in the following:
sub change2 {
      my $out = $dbh->query(\select * from student where id=$id\);
        
      my($did,$first,$middle,$last,$ext,$subjects,$age,$sex,$address,
         $city,$state,$zip,$phone) = $out->fetchrow;
 
      my @subjects = split(/:/,$subjects);
      shift @subjects;
      my $name = \$first $middle $last\;
      if ($ext) { $name .= \, $ext\; }
 
      print header, start_html(\title\=>\$name\,\BGCOLOR\=>\white\);
      print <$name




First: 
Middle: 
Last: 
Jr./III/etc.: 


Address: 

City:  
State: 
ZIP: 

Phone: 

Age:  Sex: 
END_OF_HTML
      my %sexes = ( \1\ => \Male\,
         \2\ => \Female\
      );
      print popup_menu(\name\=>\sex\,
         \values\=>[\1\,\2\],
         \default\=>\$sex\,
         \labels\=>\%sexes);
      print <
Enrolled in:

END_OF_HTML
      my @ids = ();
      my %subjects = ();
      my $out2 = $dbh->query(\select id,name from subject order by name\);
      while(my($id,$subject)=$out2->fetchrow) { 
         push(@ids,$id); 
         $subjects{\$id\} = $subject;
      }
      print scrolling_list(\name\=>\subjects\,
         \values\=>[@ids],
         \default\=>[@subjects],
         \size\=>5,
         \multiple\=>\true\,
         \labels\=>\%subjects);
      print <

 
 

END_OF_HTML
}

The primary purpose of this function is to print out a form very similar to the one generated from print_from. However, the values of this form must have the values of the chosen student preinserted as default values. This way, the user can edit whichever fields of the student he or she wishes without changing the rest.
 
A couple of functions provided by the CGI.pm module come in very handy when printing form with default values. Most importantly, the function CGI::scrolling_list prints out an HTML <SELECT> block with the parameters you provide. Among other parameters, the function takes the parameters values, default, and labels which are references to the values of each <OPTION> tag, the ones which should be preselected and the labels that user sees respectively.
 
The output of this function is a complete set of information, just as if it were coming from an add form. The difference is that the data is for a student which already exists in the database. The change3 function accepts this data and updates the student, as shown in the following:
sub change3 {
   if (param(\delete\)) { &delete2($id); }
   else {
      my $query = \update student set \;
      my @query = ();
      foreach (\first\, \middle\, \last\, \ext\, \address\, \city\,
         \state\, \zip\, \phone\) {
         if (param($_)) { push(@query,\$_ = \. 
            $dbh->quote(param($_))); 
         }
      }
      push(@query,\age=\.param(\age\)) if param(\age\);
      push(@query,\sex=\.param(\sex\)) if param(\sex\);
        
      my $subjects = \:\;
      $subjects .= join(\:\,param(\subjects\));
      $subjects .= \:\ unless $subjects eq \:\;
      $subjects .= \\;
      push(@query,\subjects=$subjects\);
 
      $query .= join(\, \,@query) . \ where id=$id\;
      $dbh->query($query);
 
      print header, start_html(\title\=>\Student Changed\,
         \BGCOLOR\=>\white\);
      # Print success form
      ...   
   }
}

Note that if the user chose the \Delete\ button on the change page, this function automatically passes the ball to the delete function. This is one major advantage of integrating several functions into one program. If no user interaction is required, you can skip from function to function without sending redirect messages to the user.
 
The rest of this function is fairly straightforward. The information about the student is gathered into an UPDATE query, which is sent to the MySQL server. A success page is then sent to the user.
 
원본 출처 : http://www.oreilly.com/catalog/msql/chapter/ch10.html
	


First Name Middle Name/Initial
Last Name Jr.,Sr.,III,etc
$i 
        
[Top]


	No.
	제목
	작성자
	작성일
	조회
	
	
16572 MYSQL C++ API를 이용한 QUERY결과 가져오기 소스와 컴파일 [1] 정재익 2002-07-22 12927
16233 [프로그램] DB 이용한 컨텐츠 관리 시스템 문태준 2002-06-12 11353
15744 MySQL 에러코드별 에러메세지 입니다. 강병권 2002-04-18 23862
14706 Perl with MySQL 정재익 2002-01-11 12856
13809 간단한  api 사용예 이노성 2001-11-25 8520
13937 또 다른 간단한 C API 사용예 정재익 2001-12-03 03:01:50 7705
13126 [예제] mysql c 프로그램(소켓 프로그래밍 포함) [1] 문태준 2001-10-20 12929
13077 JDBC 를 이용한 MySQL 응용 프로그램 작성법 정재익 2001-10-18 9742
작업시간: 0.021초, 이곳 서비스는
PostgreSQL v16.2로 자료를 관리합니다