Programming MySQL, Perl

whooploafΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 8 μήνες)

75 εμφανίσεις

Programming

MySQL, Perl

COEN 351

Reading List


Paul DuBois: MySQL and Perl for the
Web, New Riders, 2002


Jacqueline D. Hamilton: CGI Programming
101, 2004

Checking Your Installation


Use this
script

to check whether cgi and
dbi are installed:







You should see mysql among the output.

#! c:/Perl/bin/perl.exe


use CGI;

use DBI;

$cgi=new CGI;

print "CGI object was successfully created
\
n";

@driverNames = DBI
-
>available_drivers();

print "These drivers are available: @driverNames
\
n";

exit(0);

Connecting to the Database


Create database and table


In MySQL command line:


CREATE DATABASE example1;


USE example1;



CREATE TABLE nlwest(name varchar(20), wins int, losses int);


SHOW TABLES;


DESCRIBE nlwest;


INSERT INTO nlwest VALUES


('Los Angeles',13,8), ('San Francisco', 11, 8), ('San Diego',12,9),


('Arizona', 11, 11), ('Colorado', 9, 13);


SELECT * FROM nlwest;





http://dev.mysql.com/doc/refman/5.0/en/


Connecting to the Database

#! c:/perl/bin/perl.exe

use strict;

use DBI;


my $dbh = DBI
-
>connect

("DBI:mysql:host=localhost;database=example1",


"root", "none");

my $sth = $dbh
-
>prepare("SELECT name, wins, losses FROM nlwest");

$sth
-
>execute();

my $count=0;

while( my @val = $sth
-
>fetchrow_array ())

{


printf("name = %s, wins = %d, losses = %d
\
n",



$val[0], $val[1], $val[2]);


$count++;

}

print "$count teams total
\
n";

$sth
-
>finish();

$dbh
-
>disconnect();




DBI Class Method connect


$dbh = DBI
-
>connect($data_source, $username, $password)

or die $DBI::errstr;


$dbh = DBI
-
>connect($data_source, $username, $password,
\
%attr)
or die $DBI::errstr;

http://search.cpan.org/~timb/DBI
-
1.42/DBI.pm#THE_DBI_PACKAGE_AND_CLASS


Connecting to the Database

#! c:/perl/bin/perl.exe

use strict;

use DBI;


my $dbh = DBI
-
>connect

("DBI:mysql:host=localhost;database=example1",


"root", "none");

my $sth = $dbh
-
>prepare("SELECT name, wins, losses FROM nlwest");

$sth
-
>execute();

my $count=0;

while( my @val = $sth
-
>fetchrow_array ())

{


printf("name = %s, wins = %d, losses = %d
\
n",



$val[0], $val[1], $val[2]);


$count++;

}

print "$count teams total
\
n";

$sth
-
>finish();

$dbh
-
>disconnect();




DBI Class Method prepare


$sth = $dbh
-
>prepare($statement) or die $dbh
-
>errstr;

$sth = $dbh
-
>prepare($statement,
\
%attr) or die $dbh
-
>errstr;


Prepares a statement for later execution by the database engine
and returns a reference to a statement handle object.

Connecting to the Database

#! c:/perl/bin/perl.exe

use strict;

use DBI;


my $dbh = DBI
-
>connect

("DBI:mysql:host=localhost;database=example1",


"root", "none");

my $sth = $dbh
-
>prepare("SELECT name, wins, losses FROM nlwest");

$sth
-
>execute();

my $count=0;

while( my @val = $sth
-
>fetchrow_array ())

{


printf("name = %s, wins = %d, losses = %d
\
n",



$val[0], $val[1], $val[2]);


$count++;

}

print "$count teams total
\
n";

$sth
-
>finish();

$dbh
-
>disconnect();




DBI Class Method execute


$rv = $sth
-
>execute or die $sth
-
>errstr;

$rv = $sth
-
>execute(@bind_values) or die $sth
-
>errstr;


Perform whatever processing is necessary to execute the
prepared statement.

Connecting to the Database

#! c:/perl/bin/perl.exe

use strict;

use DBI;


my $dbh = DBI
-
>connect

("DBI:mysql:host=localhost;database=example1",


"root", "none");

my $sth = $dbh
-
>prepare("SELECT name, wins, losses FROM nlwest");

$sth
-
>execute();

my $count=0;

while( my @val = $sth
-
>fetchrow_array ())

{


printf("name = %s, wins = %d, losses = %d
\
n",



$val[0], $val[1], $val[2]);


$count++;

}

print "$count teams total
\
n";

$sth
-
>finish();

$dbh
-
>disconnect();




DBI Class Method fetchrow_array ()


$ary_ref = $sth
-
>fetchrow_arrayref;

$ary_ref = $sth
-
>fetch; # alias


Fetches the next row of data and returns a reference to an
array holding the field values. Null fields are returned as
undef values in the array.

Connecting to the Database

#! c:/perl/bin/perl.exe

use strict;

use DBI;


my $dbh = DBI
-
>connect

("DBI:mysql:host=localhost;database=example1",


"root", "none");

my $sth = $dbh
-
>prepare("SELECT name, wins, losses FROM nlwest");

$sth
-
>execute();

my $count=0;

while( my @val = $sth
-
>fetchrow_array ())

{


printf("name = %s, wins = %d, losses = %d
\
n",



$val[0], $val[1], $val[2]);


$count++;

}

print "$count teams total
\
n";

$sth
-
>finish();

$dbh
-
>disconnect();




DBI Class Method disconnect


$rc = $dbh
-
>disconnect or warn $dbh
-
>errstr;


Disconnects the database from the database handle.
disconnect is typically only used before exiting the
program. The handle is of little use after disconnecting.

Sample Script

Alternative Row Retrieval Methods


fetchrow_array()


Returns an array


fetchrow_arrayref()


Returns a reference to an array


fetchrow_hashref()


Returns a reference to an hash

Alternative Row Retrieval Methods

#! c:/perl/bin/perl.exe

use strict;

use DBI;

use CGI qw(:standard :html3);


print header(), start_html("MLB");

my $dbh = DBI
-
>connect ("DBI:mysql:host=localhost;database=example1",


"root", "none");

my $sth = $dbh
-
>prepare("SELECT name, wins, losses FROM nlwest");

$sth
-
>execute();


my @headings = ('Team','Wins','Losses');

my @rows = th(
\
@headings);


while( my @val = $sth
-
>fetchrow_array ())

{


push(@rows,td([$val[0],$val[1],$val[2]]));

}

print table({
-
width=>'50%',
-
align=>'center'},caption(b('NL WEST')),


Tr(
\
@rows)

);

print end_html();

$sth
-
>finish();

$dbh
-
>disconnect();

fetchrow_array ()


returns an array

Alternative Row Retrieval Methods

#! c:/perl/bin/perl.exe

use strict;

use DBI;

use CGI qw(:standard :html3);


print header(), start_html("MLB");

my $dbh = DBI
-
>connect ("DBI:mysql:host=localhost;database=example1",


"root", "none");

my $sth = $dbh
-
>prepare("SELECT name, wins, losses FROM nlwest");

$sth
-
>execute();


my @headings = ('Team','Wins','Losses');

my @rows = th(
\
@headings);


while( my $ref = $sth
-
>fetchrow_arrayref ())

{


push(@rows,td([$ref
-
>[0],$ref
-
>[1],$ref
-
>[2]]));

}

print table({
-
width=>'50%',
-
align=>'center'},caption(b('NL WEST')),


Tr(
\
@rows)

);

print end_html();

$sth
-
>finish();

$dbh
-
>disconnect();

fetchrow_arrayref()


returns reference to an array

Alternative Row Retrieval Methods

#! c:/perl/bin/perl.exe

use strict;

use DBI;

use CGI qw(:standard :html3);


print header(), start_html("MLB");

my $dbh = DBI
-
>connect ("DBI:mysql:host=localhost;database=example1",


"root", "none");

my $sth = $dbh
-
>prepare("SELECT name, wins, losses FROM nlwest");

$sth
-
>execute();


my @headings = ('Team','Wins','Losses');

my @rows = th(
\
@headings);


while( my $ref = $sth
-
>fetchrow_hashref())

{


push(@rows,td([$ref
-
>{name}, $ref
-
>{wins}, $ref
-
>{losses}]));

}

print table({
-
width=>'50%',
-
align=>'center'},caption(b('NL WEST')),


Tr(
\
@rows)

);

print end_html();

$sth
-
>finish();

$dbh
-
>disconnect();

fetchrow_hashref ()


returns reference to an hash

Letter
-
Case Variation

#! c:/perl/bin/perl.exe

use strict;

use DBI;

use CGI qw(:standard :html3);


print header(), start_html("MLB");

my $dbh = DBI
-
>connect ("DBI:mysql:host=localhost;database=example1",


"root", "none",{PrintError => 0, RaiseError => 1} );

my $sth = $dbh
-
>prepare("SELECT name, wins, losses FROM nlwest");

$sth
-
>execute();


my @headings = ('Team','Wins','Losses');

my @rows = th(
\
@headings);


while( my $ref = $sth
-
>fetchrow_hashref( 'NAME_lc'))

{


push(@rows,td([$ref
-
>{name}, $ref
-
>{wins}, $ref
-
>{losses}]));

}

print table({
-
width=>'50%',
-
align=>'center'},caption(b('NL WEST')),


Tr(
\
@rows)

);

print end_html();

$sth
-
>finish();

$dbh
-
>disconnect();

MySQL is not case specific, but Perl is.


When using field names, this parameter forces the
names to be lower case.


Use NAME
-
uc to force the names to be upper case.

High
-
Level Retrieval Methods


Low level access uses


prepare()


execute()


fetch loop


finish()


New versions of DBI have


High
-
Level Retrieval Methods

#! c:/perl/bin/perl.exe

use strict;

use DBI;

use CGI qw(:standard :html3);


print header(), start_html("MLB");

my $dbh = DBI
-
>connect
("DBI:mysql:host=localhost;database=example1",


"root", "none",{PrintError => 0, RaiseError => 1} ) or die
"error";

my @row = $dbh
-
>selectrow_array("SELECT name, wins, losses FROM
nlwest");

print "The leader is $row[0]" if @row;

print end_html();

$dbh
-
>disconnect();


selectrow_array returns a complete row in a Perl row
context.

selectrow_array returns the first column value in a Perl
scalar context.

High
-
Level Retrieval Methods

#! c:/perl/bin/perl.exe

use strict;

use DBI;

use CGI qw(:standard :html3);


print header(), start_html("MLB");

my $dbh = DBI
-
>connect
("DBI:mysql:host=localhost;database=example1",


"root", "none",{PrintError => 0, RaiseError => 1} ) or die
"error";

my $ref = $dbh
-
>selectcol_arrayref ("SELECT name FROM nlwest");

print "The teams in order are @{$ref}
\
n" if defined ($ref);

print end_html();

$dbh
-
>disconnect();

selectcol_arrayref returns a complete column in a Perl row
context.

If the query has no result, it returns an empty list or ‘undef’

High
-
Level Retrieval Methods

#! c:/perl/bin/perl.exe

use strict;

use DBI;

use CGI qw(:standard :html3);


print header(), start_html("MLB");

my $dbh = DBI
-
>connect ("DBI:mysql:host=localhost;database=example1",


"root", "none",{PrintError => 0, RaiseError => 1} ) or die "error";

my $ref = $dbh
-
>selectall_arrayref("SELECT name, wins, losses FROM nlwest");

my @headings = ('Team','Wins','Losses');

my @rows = th(
\
@headings);

foreach my $row_ref (@{$ref})

{


push(@rows,td([$row_ref
-
>[0], $row_ref
-
>[1], $row_ref
-
>[2] ]));

}

print table({
-
border=>'1',
-
width=>'50%',
-
align=>'center'},caption(b('NL
WEST')),


Tr(
\
@rows)


);


print end_html();

$dbh
-
>disconnect();

selectall_arrayref returns all values as a matrix.

All methods die if “RaiseError” is enabled

Modifying the database


do()


To issue a query that does not return rows


do() returns number of rows affected or
undef for an error.


If RaiseError is enabled, you do not need to
check

Modifying the database

#! c:/perl/bin/perl.exe

use strict;

use DBI;

use CGI qw(:standard :html3);


print header(), start_html("MLB");

my $dbh = DBI
-
>connect ("DBI:mysql:host=localhost;database=example1",


"root", "none",{PrintError => 0, RaiseError => 1} ) or die "error";

$dbh
-
>do ("INSERT INTO nlwest (name, wins, losses)



VALUES('San Francisco',12,8)" );

my $ref = $dbh
-
>selectall_arrayref("SELECT name, wins, losses FROM nlwest");

my @headings = ('Team','Wins','Losses');

my @rows = th(
\
@headings);

foreach my $row_ref (@{$ref})

{


push(@rows,td([$row_ref
-
>[0], $row_ref
-
>[1], $row_ref
-
>[2] ]));

}

print table({
-
border=>'1',
-
width=>'50%',
-
align=>'center'},caption(b('NL WEST')),


Tr(
\
@rows)


);


print end_html();

$dbh
-
>disconnect();

do() to insert a row

Modifying the database

#! c:/perl/bin/perl.exe

use strict;

use DBI;

use CGI qw(:standard :html3);


print header(), start_html("MLB");

my $dbh = DBI
-
>connect ("DBI:mysql:host=localhost;database=example1",


"root", "none",{PrintError => 0, RaiseError => 1} ) or die "error";

$dbh
-
>do ("INSERT INTO nlwest (name, wins, losses)



VALUES('San Francisco',12,8)" );

my $ref = $dbh
-
>selectall_arrayref("SELECT name, wins, losses FROM nlwest");

my @headings = ('Team','Wins','Losses');

my @rows = th(
\
@headings);

foreach my $row_ref (@{$ref})

{


push(@rows,td([$row_ref
-
>[0], $row_ref
-
>[1], $row_ref
-
>[2] ]));

}

print table({
-
border=>'1',
-
width=>'50%',
-
align=>'center'},caption(b('NL WEST')),


Tr(
\
@rows)


);


print end_html();

$dbh
-
>disconnect();

do() to insert a row

Modifying the database

use DBI;

use CGI qw(:standard :html3);


sub updateWinsAndLosses

{


my ($dbh, $winner, $looser) = @_;


$dbh
-
>do ( "UPDATE nlwest SET wins = wins + 1 WHERE name = ?",




undef, $winner);


$dbh
-
>do ( " UPDATE nlwest SET losses = losses + 1 WHERE name = ?",



undef, $looser);

}


print header(), start_html("MLB");

my $dbh = DBI
-
>connect ("DBI:mysql:host=localhost;database=example1",


"root", "none",{PrintError => 0, RaiseError => 1} ) or die "error";

&updateWinsAndLosses($dbh, 'San Francisco', 'Los Angeles');

my $ref = $dbh
-
>selectall_arrayref("SELECT name, wins, losses FROM nlwest");

my @headings = ('Team','Wins','Losses');

my @rows = th(
\
@headings);

foreach my $row_ref (@{$ref})

{


push(@rows,td([$row_ref
-
>[0], $row_ref
-
>[1], $row_ref
-
>[2] ]));

}

do() to change values