Summary: in this tutorial, we will show you step-by-step how to insert data into a table by using Perl DBI.
We will use the links
table created in the Perl MySQL creating tables tutorial. If you haven’t created the links
table yet, we strongly recommend that you create it before going forward with this tutorial.
To insert a new row into a table by using Perl DBI, you need to perform the following steps:
- Parse the INSERT statement by calling the prepare() method of the database handle object. The prepare() method returns a statement handle object that represents a statement within the MySQL database. In this step, Perl DBI validates the INSERT statement to make sure that it is valid. If there is an error in the
INSERT
statement e.g., it references a non-existent table, or it is an invalid SQL statement, theprepare()
statement returns a value ofundef
. In addition, Perl populates the error message into the$DBI::errstr
variable. - Execute the
INSERT
statement by calling theexecute()
method of the statement handle object. In this step, Perl executes theINSERT
statement within the MySQL database. Theexecute()
method returnstrue
on success and a valueundef
on failure. In case of failure, Perl also raises an exception via thedie()
function to abort the script immediately if theRaiseError
attribute is enabled.
Notice that you could execute the UPDATE or DELETE statement by using these steps.
Perl MySQL INSERT example
The following script allows you to insert data into the links table:
#!/usr/bin/perl
use strict;
use warnings;
use v5.10; # for say() function
use DBI;
# MySQL database configurations
my $dsn = "DBI:mysql:perlmysqldb";
my $username = "root";
my $password = '';
say "Perl MySQL INSERT Demo";
# get user's input links
my @links = get_links();
# connect to MySQL database
my %attr = (PrintError=>0,RaiseError=>1 );
my $dbh = DBI->connect($dsn,$username,$password,\%attr);
# insert data into the links table
my $sql = "INSERT INTO links(title,url,target)
VALUES(?,?,?)";
my $stmt = $dbh->prepare($sql);
# execute the query
foreach my $link(@links){
if($stmt->execute($link->{title}, $link->{url}, $link->{target})){
say "link $link->{url} inserted successfully";
}
}
$stmt->finish();
# disconnect from the MySQL database
$dbh->disconnect();
sub get_links{
my $cmd = '';
my @links;
# get links from the command line
my($title,$url,$target);
# repeatedly ask for link data from command line
do{
say "title:";
chomp($title = <STDIN>);
say "url:";
chomp($url = <STDIN>);
say "target:";
chomp($target = <STDIN>);
#
my %link = (title=> $title, url=> $url, target=> $target);
push(@links,\%link);
print("\nDo you want to insert another link? (Y/N)?");
chomp($cmd = <STDIN>);
$cmd = uc($cmd);
}until($cmd eq 'N');
return @links;
}
Code language: Perl (perl)
How it works.
- First, we created a new subroutine called
get_links()
to get link information from the user’s input. Theget_links()
subroutine returns a list of links as an array. - Next, we connected to the
perlmysqldb
database. - Then, we prepared an
INSERT
statement that inserts data into thelinks
table. The question marks (?
) are the placeholders that will be substituted by the corresponding values of title, URL, and target. We passed theINSERT
statement to theprepare()
method for preparing execution. - After that, we iterated the
@links
array and executed theINSERT
statement. We displayed a message to specify if data is inserted into thelinks
table successfully. - Finally, we disconnected from the MySQL database by calling the
disconnect()
method of the database handle object.
The following illustrates the output when we inserted 4 rows into the links
table.
Perl MySQL INSERT Demo
title:
MySQL Tutorial
url:
httpx://www.mysqltutorial.org/
target:
_self
Do you want to insert another link? (Y/N)?Y
title:
Perl MySQL Insert Demo
url:
httpx://www.mysqltutorial.org/perl-mysql/perl-mysql-insert/
target:
_blank
Do you want to insert another link? (Y/N)?Y
title:
Perl
url:
httpx://www.perl.org
target:
_blank
Do you want to insert another link? (Y/N)?Y
title:
MySQL
url:
httpx://www.mysql.com
target:
_self
Do you want to insert another link? (Y/N)?N
link http://mysqltutorial.org/ inserted successfully
link https://www.mysqltutorial.org/perl-mysql/perl-mysql-insert/ inserted successfully
link http://www.perl.org inserted successfully
link http://www.mysql.com inserted successfully
Code language: PHP (php)
We can check the links
table to verify the insert operations:
SELECT * FROM links;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use Perl DBI to insert data into a MySQL database table.