10.4.3 Using a MySQL Database Across the Internet

10.4.3  Using a MySQL Database Across the Internet

   MySQL is a network database. In other words, a MySQL database is easily accessible through the Internet using TCP/IP. It is possible to write socket programs to access a MySQL database running on a machine that is accessible using TCP/IP. However, it is not necessary since MySQL provides us a simple way to do so, by simply providing the machine name and the port number at which the MySQL database is published. The standard port at which MySQL publishes by default is 3306. Although the example that follows uses MySQL, the same should be possible on all modern databases as long as the database has been configured to publish on the Internet at a certain pre-specified port number. In addition, the user specified in the program must have access to the database.

The following program runs on the machine cs.uccs.edu. The MySQL database server is running on the machine pikespeak.uccs.edu. The port at which the database server listens is 3306.

 Program 10.12

#!/usr/bin/perl
#fetchFromCS.pl
use strict;
use DBI;

my ($driver, $database,  $user, $password);
my ($dsn, $dbh,, $table,  $sth, $query);
$driver = "mysql";
$database = q{database=test;host=pikespeak.uccs.edu;port=3306};
$dsn = "DBI:$driver:$database";
$user = "Project";
$password = "Project";

##need to start the connection 
$dbh = DBI->connect ($dsn, $user, $password) or
    die "Cannot connect to $dsn: $DBI::errstr";

#prepare a query; 
$table = "friends";

#Find entries NOT from Springs
print "\n\nAll non-Colorado Springs entries in TABLE $table sorted by AGE and SSNUM\n\n";
$query = qq{select STUDENT_ID, SSNUM, NAME, 
               AGE, HOMETOWN, SCHOOL from $table
               WHERE HOMETOWN not like "%Springs%"
               order by AGE, STUDENT_ID, SSNUM};
fetchPrintRows ($dbh, $sth, $query);


print 
 "\n\nAll entries in TABLE $table 20 years old or younger";
print " sorted by AGE, STUDENT_ID and SSNUM\n\n";
$query = qq{select STUDENT_ID, SSNUM, NAME, 
               AGE, HOMETOWN, SCHOOL from $table
               WHERE AGE <= 20
               order by AGE, STUDENT_ID, SSNUM};
fetchPrintRows ($dbh, $sth, $query);

#disconnect from the database
$dbh -> disconnect ();

#############################
#subroutine to fetch the rows
sub fetchPrintRows{
    my ($dbhandle, $sthandle,  $aQuery) = @_;
    $sthandle = $dbhandle -> prepare ($aQuery);
    $sthandle -> execute () or warn $dbhandle -> errstr;
    while (my $row = $sthandle -> fetchrow_hashref()){
        printf "%5d  %9d  %-20s %3d %-20s %-5s\n",
        $row -> {'STUDENT_ID'},
        $row -> {'SSNUM'},
        $row -> {'NAME'},
        $row -> {'AGE'},
        $row -> {'HOMETOWN'},
        $row -> {'SCHOOL'};
    }
} #sub ends

As usual, the program uses mysql.pm driver since it connects to a MySQL database. The database is specified using the following statement.

$database = q{database=test;host=pikespeak.uccs.edu;port=3306};

This clearly specifies that the database name is test. It is listening at port 3306 of the machine

pikespeak.uccs.edu. This program can run anywhere on the Internet and can connect to the MySQL database if the machine name, user name, and password combination is correct. The user name and password are usually given for a specific machine from which the MySQL client, and hence, the Perl program can connect. It is also possible to specify, by the database administrator, that the machine from which a user connects to the database does not really matter when creating authentication of grant tables.

The program connects to the database in the usual manner. In fact, for a Perl program running on one machine and connecting to another machine, the only thing that needs to be changed is the specification of the database source. Everything else in the code is the same as the program running on the machine where the MySQL database is running.

This program executes two SQL queries. The first query finds entries where the HOMETOWN field does not contain the word Springs. The second SQL query obtains the rows of data for which the age field is 20 or less. The program uses a subroutine called fetchPrintRows to prepare an SQL query, execute the query, and obtain and print each of the rows that are returned by the database. The output of running this program on the machine cs.uccs.edu is given below. Note, once again, that the database server is running on the machine pikespeak.uccs.edu.

All non-Colorado Springs entries in TABLE friends sorted by AGE and SSNUM

    9  198640764  Griffin Heath         20 Evergreeen           UCCS

   10  198640765  Qin Jiang             22 Trinidad             UCCS

   11  198640766  Brooke Peterson       22 Denver               UCD 

All entries in TABLE friends 20 years old or

younger sorted by AGE, STUDENT_ID and SSNUM

    1  198640756  Joe White             18 Colorado Springs     PPCC

    8  198640763  Clint Trebesh         18 Glenwood Springs     UCCS

    2  198640757  Shane Jahnke          19 Colorado Springs     UCCS

    6  198640761  Justin O'Malley       19 Colorado Springs     NULL

    7  198640762  Nick Freeman          19 Colorado Springs     NULL

    3  198640758  Seth Gross            20 Colorado Springs     UCCS

    9  198640764  Griffin Heath         20 Evergreeen           UCCS