Perl and MySQL

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

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

185 εμφανίσεις

Perl and MySQL


Using apache server with perl cgi


Using MySQL


Need to install dbi and dbd modules to
your perl installation.


There are some good examples on this
site:
http://www.tutorialspoint.com/perl/perl_cgi.
htm


Using perl with mysql


If you are using ActivePerl, you may use ppm to install DBD
-
mysql.
For Perl 5.6, upgrade to Build 623 or later, then it is sufficient to run

C:
\
perl>ppm install DBI

C:
\
perl>ppm install DBD::mysql


If you need an HTTP proxy, you might need to set the environment
variable http_proxy, for example like this:


set http_proxy=
http://myproxy.com:8080/



As of this writing, DBD::mysql is missing in the ActivePerl 5.8.0
repository. However, Randy Kobes has kindly donated an own
distribution and the following might succeed:


C:
\
>ppm install
http://theoryx5.uwinnipeg.ca/ppms/DBD
-
mysql.ppd


Perl and db queries


Using apache server with perl cgi


Using MySQL


Need to install dbi and dbd modules to
your perl installation.


See next slide screen shot for

C:
\
perl>ppm install dbi

C:
\
perl>ppm install DBI

Installing dbd:mysql (not in perl5.8)

c;:
\
>ppm install
http://theoryx5.uwinnipeg.ca/ppms/DBD
-
mysql.ppd


Using perl with mysql


If you are using ActivePerl, you may use ppm to install DBD
-
mysql.
For Perl 5.6, upgrade to Build 623 or later, then it is sufficient to run

C:
\
perl>ppm install DBI

C:
\
perl>ppm install DBD::mysql


If you need an HTTP proxy, you might need to set the environment
variable http_proxy, for example like this:


set http_proxy=
http://myproxy.com:8080/



As of this writing, DBD::mysql is missing in the ActivePerl 5.8.0
repository. However, Randy Kobes has kindly donated an own
distribution and the following might succeed:


C:
\
>ppm install
http://theoryx5.uwinnipeg.ca/ppms/DBD
-
mysql.ppd


You’ll need a table in some mysql database

Using mysql


You’ll need a network connection for this
install


Create a command prompt in perl/bin and
run the ppm (program package manager)


Type


Install DBI

Using mysql: DOS window

lots of stuff installed

C:
\
PERL
\
BIN>ppm

PPM
-

Programmer's Package Manager version 3.2.

Copyright (c) 2001 ActiveState Corp. All Rights Reserved.

ActiveState is a division of Sophos.

Entering interactive shell. Using Term::ReadLine::Perl as readline library.

Type 'help' to get started.

ppm> install DBI

====================

Install 'DBI' version 1.50 in ActivePerl 5.8.7.813.

====================

Downloaded 558143 bytes.

Extracting 73/73: blib/arch/auto/DBI/Driver_xst.h

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBI
\
dbd_xsh.h

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBI
\
DBI.bs

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBI
\
DBI.dll

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBI
\
DBI.exp

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBI
\
dbi_sql.h

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBI
\
Driver.xst

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBI
\
Driver_xst.h

Installing C:
\
Perl
\
site
\
lib
\
DBI
\
ProfileDumper
\
Apache.pm

Installing C:
\
Perl
\
site
\
lib
\
DBI
\
SQL
\
Nano.pm

Installing C:
\
Perl
\
site
\
lib
\
Win32
\
DBIODBC.pm

Installing C:
\
Perl
\
bin
\
dbiprof

Installing C:
\
Perl
\
bin
\
dbiprof.bat

Installing C:
\
Perl
\
bin
\
dbiproxy

Installing C:
\
Perl
\
bin
\
dbiproxy.bat

Successfully installed DBI version 1.50 in ActivePerl 5.8.7.813.

ppm> exit


C:
\
PERL
\
BIN>

Install mysql connector from ppm

ppm> install http://theoryx5.uwinnipeg.ca/ppms/DBD
-
mysql.ppd

====================

Install 'DBD
-
mysql' version 3.0002 in ActivePerl 5.8.7.813.

====================

Downloaded 637299 bytes.

Extracting 43/43: blib

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBD
\
mysql
\
mysql.bs

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBD
\
mysql
\
mysql.dll

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBD
\
mysql
\
mysql.exp

Installing C:
\
Perl
\
site
\
lib
\
auto
\
DBD
\
mysql
\
mysql.lib

Installing C:
\
Perl
\
html
\
site
\
lib
\
Mysql.html

Installing C:
\
Perl
\
html
\
site
\
lib
\
Bundle
\
DBD
\
mysql.html

Installing C:
\
Perl
\
html
\
site
\
lib
\
DBD
\
mysql.html

Installing C:
\
Perl
\
html
\
site
\
lib
\
DBD
\
mysql
\
INSTALL.html

Files found in blib
\
arch: installing files in blib
\
lib into architecture depende

nt library tree

Installing C:
\
Perl
\
site
\
lib
\
Mysql.pm

Installing C:
\
Perl
\
site
\
lib
\
Bundle
\
DBD
\
mysql.pm

Installing C:
\
Perl
\
site
\
lib
\
DBD
\
mysql.pm

Installing C:
\
Perl
\
site
\
lib
\
DBD
\
mysql
\
GetInfo.pm

Installing C:
\
Perl
\
site
\
lib
\
DBD
\
mysql
\
INSTALL.pod

Installing C:
\
Perl
\
site
\
lib
\
Mysql
\
Statement.pm

Successfully installed DBD
-
mysql version 3.0002 in ActivePerl 5.8.7.813.

ppm>





Listing dbi drivers perlscript (save
in perl/bin)

use strict;

use warnings;

use DBI;

print map "$_
\
n",


DBI
-
>available_drivers;

Listing dbi drivers

C:
\
PERL
\
BIN>perl dbidrivers.pl

DBM

ExampleP

File

Proxy

Sponge

mysql


C:
\
PERL
\
BIN>

Mysqltest.pl


running perlscript on the commandline with db
connection


Need an accounts table in mysql containing fields:
idnum, name and balance

The script: Mysqltest.pl

use DBI;

my $dsn = "DBI:mysql:database=test;host=localhost";

my $dbh = DBI
-
>connect($dsn, 'root', '',





{RaiseError => 1}) ||

die $DBI::errstr;

my $sth = $dbh
-
>prepare(qq{ SELECT idnum,name, balance FROM accounts
});


$sth
-
>execute || die "Error fetching data: $DBI::errstr";


while (my ($idnum,$name, $balance) = $sth
-
>fetchrow_array) {


print qq{ $name has balance: $balance
\
n};

}


$dbh
-
>disconnect;


Running mysqltest.pl

C:
\
PERL
\
BIN>perl mysqltest.pl


bob smith has balance: 1234567.99


sue jones has balance: 11223344.77


allison douglas has balance: 23456.64


wilson pickett has balance: 123.45


Venus Williams has balance: 98776587.11


R2D2 has balance: 12.88


Burt Lancaster has balance: 654832.11


Katie has balance: 389


Pop
-
Eye has balance: 34.16


C:
\
PERL
\
BIN>

You’ll need a table in some mysql database

Perlscript: part1

#!C:
\
perl
\
bin
\
perl.exe

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

# PERL MODULES WE WILL BE USING

use DBI;

use DBD::mysql


# HTTP HEADER


print "starting...";


$dbh= DBI
-
>connect("DBI:mysql:test;mysql_use_result=1",
"root", "");


print "connected...";

# PREPARE THE QUERY

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

execute query…display results

# EXECUTE THE QUERY


print "executed query...";


if (!$sth) {


die "Error:" . $dbh
-
>errstr . "
\
n";


}


if (!$sth
-
>execute) {


die "Error:" . $sth
-
>errstr . "
\
n";


}


my $names = $sth
-
>{'NAME'};


my $numFields = $sth
-
>{'NUM_OF_FIELDS'};


for (my $i = 0; $i < $numFields; $i++) {


printf("%s%s", $i ? "," : "", $$names[$i]);


}


print "
\
n";


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


for (my $i = 0; $i < $numFields; $i++) {


printf("%s%s", $i ? "," : "", $$ref[$i]);


}


print "
\
n";


}

http://localhost/cgi
-
bin/dbex6.cgi

Inserting a record

#!C:
\
perl
\
bin
\
perl.exe

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

# PERL MODULES WE WILL BE USING

use DBI;

use DBD::mysql

# HTTP HEADER

print "starting...";

$dbh= DBI
-
>connect("DBI:mysql:test;mysql_use_result=1", "root", "");

print "connected...";

# PREPARE THE QUERY

$query = "INSERT INTO students ( name,id, grade) VALUES ( 'tomatoes', '11111','4')";

my $sth = $dbh
-
>prepare($query);

# EXECUTE THE QUERY


if (!$sth) {


die "Error:" . $dbh
-
>errstr . "
\
n";


}


if (!$sth
-
>execute) {


die "Error:" . $sth
-
>errstr . "
\
n";


}


print "executed query...";

Updating records

#!C:
\
perl
\
bin
\
perl.exe

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

# PERL MODULES WE WILL BE USING

use DBI;

use DBD::mysql

# HTTP HEADER

print "starting...";

$user = “user";

$pw = "pw";

print "defined vars...";

# DATA SOURCE NAME

$dsn = "DBI:mysql:test:localhost:3306";

# PERL DBI CONNECT

$dbh= DBI
-
>connect("DBI:mysql:test;mysql_use_result=1", $user,$pw)||


die "Database connection not made: $DBI::errstr";

print "connected...";

$dbh
-
>{RaiseError} = 1;

my $sth = $dbh
-
>prepare("UPDATE students


SET id = '1212'


WHERE name = 'john'");

print "defined sth...";

$sth
-
>execute();

print "executed..";

$sth
-
>finish;

$dbh
-
>disconnect;

exit;

__END__



Updating records

Updating multiple records

Updating multiple records

$dbh= DBI
-
>connect("DBI:mysql:test;mysql_use_result=1",
$user,$pw)||


die "Database connection not made: $DBI::errstr";

print "connected...";

my @names = ("john", "Katie", "tomatoes");

$dbh
-
>{RaiseError} = 1;

my $sth = $dbh
-
>prepare("UPDATE students


SET grade= '99.9'


WHERE name = ?");

print "defined sth...";


foreach $value (@names){


$sth
-
>execute($value);


}

print "executed..";

$sth
-
>finish;

$dbh
-
>disconnect;

Using Bind Values

There may be a case when values to be
entered into the table are not given in
advance. In such a case, binding values
are used. A question mark is used in place
of actual value and then actual values are
passed through the execute() API.


Example: bind and execute using form
parameters


my($name, $street, $city) =


(param("name"), param("street"),


param("city"));#params from a form

my $sth = $dbh
-
>prepare("INSERT INTO TEST_TABLE
(NAME, STREET, CITY) values (?,?,?)");

$sth
-
>execute($name,$street,$city) or die $DBI::errstr;

$sth
-
>finish();

$dbh
-
>commit or die $DBI::errstr;

bind and execute for an arbitrary query

$age = param(‘age’);#or read from a file or from input

my $sth = $dbh
-
>prepare("SELECT FIRST_NAME,
LAST_NAME FROM TEST_TABLE WHERE AGE >
?
");


$sth
-
>execute( $age ) or die $DBI::errstr;

print "Number of rows found :" + $sth
-
>rows;

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


{ my ($first_name, $last_name ) = @row;

print "First Name = $first_name, Last Name =
$last_name
\
n";

}

$sth
-
>finish();

An html to perform sql

Perl performs query