Connect to MySQL with Perl using DBI

By | February 4, 2013

Example of a connection to MySQL with Perl using DBI

#! /usr/bin/perl -w
#
#  Example code to connect to MySQL, create a table, fill it with some data and select it again.
#
#  To install DBI using cpan:
#
#  perl -MCPAN -e shell
#  cpan> install DBI
#  cpan> install DBD::mysql
#
 
use strict;
use DBI;
 
my $database = "test";
my $user = "user";
my $passwd = "secret";
my $sqlCreate = "CREATE TABLE IF NOT EXISTS test ( pkey int(11) NOT NULL auto_increment, a int, b int, c int, timeEnter timestamp(14), PRIMARY KEY  (pkey) ) ";
 
my $insert = "insert into test (a,b,c) values (1,2,3),(4,5,6),(7,8,9)"; 
my $select = "select a,b,c from test"; 
my $dsn = "DBI:mysql:host=localhost;database=${database}";
my $dbh = DBI->connect ($dsn, $user, $passwd) or die "Cannot connect to server\n";
 
# Execute the sql to create the table test
my $stmnt = $dbh->prepare($sqlCreate);
$stmnt->execute();
 
# Insert the testdata
$stmnt = $dbh->prepare($insert);
$stmnt->execute();
 
# Run a select on the table test
$stmnt = $dbh->prepare($stmntelect);
$stmnt->execute();
 
while(my @val = $stmnt->fetchrow_array())
{
	print " $val[0]  $val[1]  $val[2]\n";
}
$stmnt->finish();
 
# Close the connection to the database
$dbh->disconnect ( );

 

Leave a Reply