Connection.
# create simple $HOME/.my.cnf file
% cat > $HOME/.my.cnf
[client]
host=localhost
user=real_user_name
password=real_password
% chmod 600 $HOME/.my.cnf
connect.pl
1 #!/usr/bin/perl -w
2
3 use DBI;
4 use strict;
5
6 # DBI:driver:database;function_that_read_.my.cnf_file
7 my $dsn = "DBI:mysql:test;mysql_read_default_file=$ENV{HOME}/.my.cnf";
8 my ($user,$pass) = ("","");
9 my ($dbh,$sth,$query);
10
11 $dbh = DBI->connect($dsn,$user,$pass,{ RaiseError => 1 });
12 # or if $HOME/.my.cnf doesn't exist -
13 $dbh = DBI->connect("DBI:mysql:host=localhost;database=test",
14 "user_name",
15 "password",
16 { RaiseError => 1 });
17
18 print "Connection established!\n";
19
20 $dbh->disconnect();
21 exit(0);
Creatint Table. Insertion Data.
1 #!/usr/bin/perl -w
2
3 use DBI;
4 use strict;
5
6 my ($dbh,$sth,$query);
7
8 my_connect();
9
10 $dbh->do(qq{ DROP TABLE IF EXISTS test });
11 $query = q{ CREATE TABLE test(
12 id INT(6) ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,
13 fname VARCHAR(50),
14 lname VARCHAR(50)) };
15 $dbh->do($query);
16 $query = q{ INSERT INTO test(fname,lname) VALUES("Taras","Shevchenko") };
17 $dbh->do($query);
18
19 # or using place holder
20 # (better, because no quotation marks, and only one 'prepare' call)
21 my @names = (
22 [ qw(Fred Lindberg) ],
23 [ qw(Mikael Chertoff) ] );
24
25 $query = q{ INSERT INTO test(fname,lname) VALUES(?,?) };
26 $sth = $dbh->prepare($query);
27
28 for ( @names ) {
29 $sth->execute($_->[0],$_->[1]);
30 }
31
32 my_disconnect();
33 exit(0);
34 #-------------------------------------------------------------------------
35 sub my_connect {
36 my ($dsn) = "DBI:mysql:test;mysql_read_default_file=$ENV{HOME}/.my.cnf";
37 my ($user,$pass) = ("","");
38 $dbh = DBI->connect($dsn,$user,$pass,{ RaiseError => 1 });
39 }
40 sub my_disconnect {
41 $sth->finish();
42 $dbh->disconnect();
43 }
Retrieving Data.
1 #!/usr/bin/perl -w
2
3 use DBI;
4 use strict;
5
6 my ($dbh,$sth,$query,$ref);
7
8 my_connect();
9
10 #-------------------------------------------------------------------------
11 # fetchrow_array
12 $sth = $dbh->prepare(q{ SELECT count(*) FROM test });
13 $sth->execute();
14 # scalar value
15 print "There are ", $sth->fetchrow_array(),
16 " records in the 'test' table\n";
17 # or $rows = $str->rows();
18
19 # OUTPUT:
20 # There are 3 records in the 'test' table
21
22 $sth = $dbh->prepare(q{ SELECT id,fname,lname FROM test limit 2});
23 $sth->execute();
24 # list value
25 while ( my @ary = $sth->fetchrow_array() ) {
26 printf "%-8s%-20s%s\n", @ary;
27 }
28 # OUTPUT:
29 # 000001 Taras Shevchenko
30 # 000002 Fred Lindberg
31
32 #-------------------------------------------------------------------------
33 # fetchrow_arrayref() or alias fetch()
34 $sth = $dbh->prepare(q{ SELECT * FROM test limit 1,2});
35 $sth->execute();
36 while ( my $ary_ref = $sth->fetchrow_arrayref() ) {
37 printf "%-20s%s\n", $ary_ref->[1],$ary_ref->[2];
38 }
39 # or
40 # my ($id,$fname,$lname);
41 # $sth->bind_columns(\$id,\$fname,\$lname);
42 # while ( my $ary_ref = $sth->fetchrow_arrayref() ) {
43 # printf "%-20s%s\n", $fname, $lname;
44 # }
45
46 # OUTPUT:
47 # Fred Lindberg
48 # Mikael Chertoff
49
50 #-------------------------------------------------------------------------
51 # fetchall_arrayref() or alias fetch()
52 $sth = $dbh->prepare(q{ SELECT * FROM test limit 2});
53 $sth->execute();
54 # second column, and last column
55 my $ary_ref = $sth->fetchall_arrayref([1,-1]);
56 for my $r ( @{$ary_ref} ) {
57 printf "%-20s%s\n", @{$r};
58 }
59 # OUTPUT:
60 # Taras Shevchenko
61 # Fred Lindberg
62 #-------------------------------------------------------------------------
63 # fetchrow_hashref()
64 $sth = $dbh->prepare(q{ SELECT fname,lname FROM test limit 1,2});
65 $sth->execute();
66 while ( my $hash_ref = $sth->fetchrow_hashref() ) {
67 printf "%-20s%s\n", $hash_ref->{'fname'},$hash_ref->{'lname'};
68 }
69 # OUTPUT:
70 # Fred Lindberg
71 # Mikael Chertoff
72
73 #-------------------------------------------------------------------------
74 # selectcol_arrayref
75 $ref = $dbh->selectcol_arrayref(q{ SELECT lname FROM test });
76 print "Last names are: @{$ref}\n" if defined($ref);
77 # OUTPUT:
78 # Last names are: Shevchenko Lindberg Chertoff
79
80 #-------------------------------------------------------------------------
81 # selectall_arrayref()
82 $ref = $dbh->selectall_arrayref(q{ SELECT * FROM test ORDER BY lname });
83 if ( defined($ref) ) {
84 for ( @{$ref} ) {
85 printf "%-8s%-20s%s\n", $_->[0],$_->[1],$_->[2];
86 }
87 }
88 # OUTPUT:
89 # 000003 Mikael Chertoff
90 # 000002 Fred Lindberg
91 # 000001 Taras Shevchenko
92
93 #-------------------------------------------------------------------------
94 # selectall_hashref()
95
96 my_disconnect();
97 exit(0);
98 #-------------------------------------------------------------------------
99 sub my_connect {
100 my ($dsn) = "DBI:mysql:test;mysql_read_default_file=$ENV{HOME}/.my.cnf";
101 my ($user,$pass) = ("","");
102 $dbh = DBI->connect($dsn,$user,$pass,{ RaiseError => 1 }) or
103 die($DBI::errstr);
104 }
105 sub my_disconnect {
106 $sth->finish();
107 $dbh->disconnect();
108 }