Update (crUd) using Perl and DBD::ORACLE

In this post, we’re going to take a look at the U in CRUD: Update.

We use the DBD::Oracle driver to update some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

My helper function get_all_rows() encapsulates a select statement used to verify that the updates worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

sub get_all_rows {
 my $label = $_[0];
 my $data_type = $_[1];

 # Query all rows
 my $con = DBI-&gt;connect( 'dbi:Oracle:', $connectString, '') ||
 die "Database connection not made: $DBI::errstr";
 $con-&gt;{RaiseError} = 1; # set the connection to raise errors

 my $statement = 'select id, name, age, notes from lcs_people order by id';

 if ($data_type eq "pets") {
   $statement = 'select id, name, owner, type from lcs_pets order by owner, id';
 }

 my $sth = $con-&gt;prepare($statement);
 $sth-&gt;execute;

 #Adding some space around the results to make better screenshots.
 print "\n $label: \n";
 while (my @row = $sth-&gt;fetchrow_array){
 print " @row\n";
 }
 print "\n";

 $con-&gt;disconnect;
}

sub get_all_rows {

my $label = $_[0];

my $data_type = $_[1];

# Query all rows

my $con = DBI->connect( 'dbi:Oracle:', $connectString, '') ||

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

$con->{RaiseError} = 1; # set the connection to raise errors

my $statement = 'select id, name, age, notes from lcs_people order by id';

if ($data_type eq "pets") {

$statement = 'select id, name, owner, type from lcs_pets order by owner, id';

}

my $sth = $con->prepare($statement);

$sth->execute;

#Adding some space around the results to make better screenshots.

print "\n $label: \n";

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

print " @row\n";

}

print "\n";

$con->disconnect;

}

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.perl with the following code and then run it whenever you would like to reset the data. (Notice this version adds pet data not included in other sections.)

use strict;
use DBI;
my $connectString=$ENV{'db_connect'};
my $con = DBI-&gt;connect( 'dbi:Oracle:', $connectString, '');
$con-&gt;{RaiseError} = 1; # set the connection to raise errors

# Delete rows
my $sth = $con-&gt;prepare("delete from lcs_pets");
$sth-&gt;execute;

# Reset Identity Coulmn
my $sth = $con-&gt;prepare("alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 8)");
$sth-&gt;execute;

# Delete rows
my $sth = $con-&gt;prepare("delete from lcs_people");
$sth-&gt;execute;

# Reset Identity Coulmn
my $sth = $con-&gt;prepare("alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)");
$sth-&gt;execute;

# Insert default rows
my @ids = (1, 2);
my @names = ("Bob", "Kim");
my @ages = (35, 27);
my @notes = ("I like dogs", "I like birds");

my $sth = $con-&gt;prepare("INSERT INTO lcs_people(id, name, age, notes) VALUES (?, ?, ?, ?)");
my $tuples = $sth-&gt;execute_array(
 { ArrayTupleStatus =&gt; \my @tuple_status }, \@ids, \@names, \@ages, \@notes,);
if ($tuples) {
 print "Successfully inserted $tuples records\n";
} else {
 print "Insert failed\n";
}

# Insert default rows
my @ids = (1, 2, 3, 4, 5, 6, 7);
my @names = ("Duke", "Pepe", "Princess", "Polly", "Rollo", "Buster", "Fido");
my @owners = (1, 2, 1, 1, 1, 1, 1);
my @types = ("dog", "bird", "snake", "bird", "horse", "dog", "cat");

my $sth = $con-&gt;prepare("INSERT INTO lcs_pets(id, name, owner, type) VALUES (?, ?, ?, ?)");
my $tuples = $sth-&gt;execute_array(
 { ArrayTupleStatus =&gt; \my @tuple_status }, \@ids, \@names, \@owners, \@types,);
if ($tuples) {
 print "Successfully inserted $tuples records\n";
} else {
 print "Insert failed\n";
}

$con-&gt;disconnect;

use strict;

use DBI;

my $connectString=$ENV{'db_connect'};

my $con = DBI->connect( 'dbi:Oracle:', $connectString, '');

$con->{RaiseError} = 1; # set the connection to raise errors

# Delete rows

my $sth = $con->prepare("delete from lcs_pets");

$sth->execute;

# Reset Identity Coulmn

my $sth = $con->prepare("alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 8)");

$sth->execute;

# Delete rows

my $sth = $con->prepare("delete from lcs_people");

$sth->execute;

# Reset Identity Coulmn

my $sth = $con->prepare("alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)");

$sth->execute;

# Insert default rows

my @ids = (1, 2);

my @names = ("Bob", "Kim");

my @ages = (35, 27);

my @notes = ("I like dogs", "I like birds");

my $sth = $con->prepare("INSERT INTO lcs_people(id, name, age, notes) VALUES (?, ?, ?, ?)");

my $tuples = $sth->execute_array(

{ ArrayTupleStatus => \my @tuple_status }, \@ids, \@names, \@ages, \@notes,);

if ($tuples) {

print "Successfully inserted $tuples records\n";

} else {

print "Insert failed\n";

}

# Insert default rows

my @ids = (1, 2, 3, 4, 5, 6, 7);

my @names = ("Duke", "Pepe", "Princess", "Polly", "Rollo", "Buster", "Fido");

my @owners = (1, 2, 1, 1, 1, 1, 1);

my @types = ("dog", "bird", "snake", "bird", "horse", "dog", "cat");

my $sth = $con->prepare("INSERT INTO lcs_pets(id, name, owner, type) VALUES (?, ?, ?, ?)");

my $tuples = $sth->execute_array(

{ ArrayTupleStatus => \my @tuple_status }, \@ids, \@names, \@owners, \@types,);

if ($tuples) {

print "Successfully inserted $tuples records\n";

} else {

print "Insert failed\n";

}

$con->disconnect;

Boilerplate template

The template we will be using is:

use strict;
use DBI;
my $connectString=$ENV{'db_connect'};

sub get_all_rows {
 my $label = $_[0];
 my $data_type = $_[1];

 # Query all rows
 my $con = DBI-&gt;connect( 'dbi:Oracle:', $connectString, '') ||
 die "Database connection not made: $DBI::errstr";
 $con-&gt;{RaiseError} = 1; # set the connection to raise errors

 my $statement = 'select id, name, age, notes from lcs_people order by id';

 if ($data_type eq 'pets') {
 $statement = 'select id, name, owner, type from lcs_pets order by owner, id';
 }

 my $sth = $con-&gt;prepare($statement);
 $sth-&gt;execute;

 #Adding some space around the results to make better screenshots.
 print "\n $label: \n";
 while (my @row = $sth-&gt;fetchrow_array){
 print " @row\n";
 }
 print "\n";

 $con-&gt;disconnect;
}

my $con = DBI-&gt;connect( 'dbi:Oracle:', $connectString, '') || die "Database connection not made: $DBI::errstr";
$con-&gt;{RaiseError} = 1; # set the connection to raise errors

get_all_rows('Original Data');

# Your code here

get_all_rows('New Data')

use strict;

use DBI;

my $connectString=$ENV{'db_connect'};

sub get_all_rows {

my $label = $_[0];

my $data_type = $_[1];

# Query all rows

my $con = DBI->connect( 'dbi:Oracle:', $connectString, '') ||

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

$con->{RaiseError} = 1; # set the connection to raise errors

my $statement = 'select id, name, age, notes from lcs_people order by id';

if ($data_type eq 'pets') {

$statement = 'select id, name, owner, type from lcs_pets order by owner, id';

}

my $sth = $con->prepare($statement);

$sth->execute;

#Adding some space around the results to make better screenshots.

print "\n $label: \n";

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

print " @row\n";

}

print "\n";

$con->disconnect;

}

my $con = DBI->connect( 'dbi:Oracle:', $connectString, '') || die "Database connection not made: $DBI::errstr";

$con->{RaiseError} = 1; # set the connection to raise errors

get_all_rows('Original Data');

# Your code here

get_all_rows('New Data')

For each exercise, replace the “# Your code here” line with your code.

Simple update

We will perform a simple update that modifies a single record in the lcs_people table. These are the steps performed in the code snippet below.

Prepare a SQL UPDATE statement, changing age to 31 for the record with an id of 1.
Bind the age and id values. (See the R part of this series for an explanation of bind variables.)
Execute the statement.

my $sth = $con-&gt;prepare("update lcs_people set age = :age where id = :id");
$sth-&gt;bind_param( ":age",31);
$sth-&gt;bind_param( ":id",1);
$sth-&gt;execute;

my $sth = $con->prepare("update lcs_people set age = :age where id = :id");

$sth->bind_param( ":age",31);

$sth->bind_param( ":id",1);

$sth->execute;

When I run this code in my Perl session, I see:

 Original Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds

 New Data: 
 1 Bob 31 I like dogs
 2 Kim 27 I like birds

Original Data:

1 Bob 35 I like dogs

2 Kim 27 I like birds

New Data:

1 Bob 31 I like dogs

2 Kim 27 I like birds

Extra Fun 1

Update Bob’s notes to ‘I like cats’ .

Your results should be:

 Original Data: 
 1 Bob 31 I like dogs
 2 Kim 27 I like birds

 New Data: 
 1 Bob 31 I like cats
 2 Kim 27 I like birds

Original Data:

1 Bob 31 I like dogs

2 Kim 27 I like birds

New Data:

1 Bob 31 I like cats

2 Kim 27 I like birds

Answer

my $sth = $con-&gt;prepare("update lcs_people set notes = :notes where id = :id");
$sth-&gt;bind_param( ":notes","I like cats");
$sth-&gt;bind_param( ":id",1);
$sth-&gt;execute;

my $sth = $con->prepare("update lcs_people set notes = :notes where id = :id");

$sth->bind_param( ":notes","I like cats");

$sth->bind_param( ":id",1);

$sth->execute;

Reset the data

Now is a good time to run reset_data.perl.

Boilerplate change

Change the boilerplate get_all_rows statements to get pet data.

get_all_rows('Original Data', 'pets');

# Your code here

get_all_rows('New Data', 'pets');

get_all_rows('Original Data', 'pets');

# Your code here

get_all_rows('New Data', 'pets');

Make sure your where clause is specific

In the above example, notice that we used the id column in our where clause. For our data set, id is the primary key. You do not always have to use a primary key, but you should make sure you only update the rows you intend to.

Next, let’s look at updating multiple rows. We’ll have Bob give his dog Duke to Kim.

Prepare a SQL UPDATE statement, changing owner to 2 for the record with an owner of 1 and a type of ‘dog’.
Bind the newOwner, oldOwner and type values.
Execute the statement.

my $sth = $con-&gt;prepare("update lcs_pets set owner = :newOwner where owner = :oldOwner and type = :type");
$sth-&gt;bind_param( ":newOwner",2);
$sth-&gt;bind_param( ":oldOwner",1);
$sth-&gt;bind_param( ":type","dog");
$sth-&gt;execute;

my $sth = $con->prepare("update lcs_pets set owner = :newOwner where owner = :oldOwner and type = :type");

$sth->bind_param( ":newOwner",2);

$sth->bind_param( ":oldOwner",1);

$sth->bind_param( ":type","dog");

$sth->execute;

When I run this code in my Perl session, I see:

 Original Data: 
 1 Duke 1 dog
 3 Princess 1 snake
 4 Polly 1 bird
 5 Rollo 1 horse
 6 Buster 1 dog
 7 Fido 1 cat
 2 Pepe 2 bird


 New Data: 
 3 Princess 1 snake
 4 Polly 1 bird
 5 Rollo 1 horse
 7 Fido 1 cat
 <strong>1 Duke 2 dog</strong>
 2 Pepe 2 bird
 <strong>6 Buster 2 dog</strong>

Original Data:

1 Duke 1 dog

3 Princess 1 snake

4 Polly 1 bird

5 Rollo 1 horse

6 Buster 1 dog

7 Fido 1 cat

2 Pepe 2 bird

New Data:

3 Princess 1 snake

4 Polly 1 bird

5 Rollo 1 horse

7 Fido 1 cat

1 Duke 2 dog

2 Pepe 2 bird

6 Buster 2 dog

In our example we only used owner and type, assuming that Bob only had one dog, Duke, as it is in our original data. With the new reset data function, we added a second dog, Buster. This example is intended to demonstrate what may happen when multiple users are working with the same data set.

In our data, the only unique identifier for lcs_pets is id. Bob may have two dogs, or even two dogs named Duke. Make sure if you intend to change a specific row you use a unique identifier.

It also helps to…

Verify the number of affected rows

Now let’s give Buster back to Bob. This time we will use the unique id column and we will print out the number of rows affected using $sth->rows.

Prepare a SQL UPDATE statement.
Bind the newOwner to 1 and id to 6.
Execute the statement.
Get the number of changed rows.
Print the number of changed rows.

my $sth = $con-&gt;prepare("update lcs_pets set owner = :newOwner where id = :id");
$sth-&gt;bind_param( ":newOwner",1);
$sth-&gt;bind_param( ":id",6);
$sth-&gt;execute;

my $changed = $sth-&gt;rows;

print "Number of rows updated: $changed\n";

my $sth = $con->prepare("update lcs_pets set owner = :newOwner where id = :id");

$sth->bind_param( ":newOwner",1);

$sth->bind_param( ":id",6);

$sth->execute;

my $changed = $sth->rows;

print "Number of rows updated: $changed\n";

When I run this code in my Perl session, I see:

 Original Data: 
 3 Princess 1 snake
 4 Polly 1 bird
 5 Rollo 1 horse
 7 Fido 1 cat
 1 Duke 2 dog
 2 Pepe 2 bird
 6 Buster 2 dog

Number of rows updated: 1

 New Data: 
 3 Princess 1 snake
 4 Polly 1 bird
 5 Rollo 1 horse
 <strong>6 Buster 1 dog</strong>
 7 Fido 1 cat
 1 Duke 2 dog
 2 Pepe 2 bird

Original Data:

3 Princess 1 snake

4 Polly 1 bird

5 Rollo 1 horse

7 Fido 1 cat

1 Duke 2 dog

2 Pepe 2 bird

6 Buster 2 dog

Number of rows updated: 1

New Data:

3 Princess 1 snake

4 Polly 1 bird

5 Rollo 1 horse

6 Buster 1 dog

7 Fido 1 cat

1 Duke 2 dog

2 Pepe 2 bird

$sth->rows will show you the number of rows affected for insert, update and delete statements and -1 for a select statement.

Extra Fun 2

Give all birds to Kim that she doesn’t already have and print the number of affected rows .

Your results should be:

 Original Data: 
 3 Princess 1 snake
 4 Polly 1 bird
 5 Rollo 1 horse
 6 Buster 1 dog
 7 Fido 1 cat
 1 Duke 2 dog
 2 Pepe 2 bird

Number of rows updated: 1

 New Data: 
 3 Princess 1 snake
 5 Rollo 1 horse
 6 Buster 1 dog
 7 Fido 1 cat
 1 Duke 2 dog
 <strong>2 Pepe 2 bird</strong>
 4 Polly 2 bird

Original Data:

3 Princess 1 snake

4 Polly 1 bird

5 Rollo 1 horse

6 Buster 1 dog

7 Fido 1 cat

1 Duke 2 dog

2 Pepe 2 bird

Number of rows updated: 1

New Data:

3 Princess 1 snake

5 Rollo 1 horse

6 Buster 1 dog

7 Fido 1 cat

1 Duke 2 dog

2 Pepe 2 bird

4 Polly 2 bird

Answer

my $sth = $con-&gt;prepare("update lcs_pets set owner = :newOwner where type = :type and owner != :oldOwner");
$sth-&gt;bind_param( ":newOwner",2);
$sth-&gt;bind_param( ":type","bird");
$sth-&gt;bind_param( ":oldOwner",2);
$sth-&gt;execute;

my $changed = $sth-&gt;rows;

print "Number of rows updated: $changed\n";

my $sth = $con->prepare("update lcs_pets set owner = :newOwner where type = :type and owner != :oldOwner");

$sth->bind_param( ":newOwner",2);

$sth->bind_param( ":type","bird");

$sth->bind_param( ":oldOwner",2);

$sth->execute;

my $changed = $sth->rows;

print "Number of rows updated: $changed\n";

Some other things you could try

Change multiple column values
Perform an update that changes all rows, if the row count is greater than 2, rollback the change

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Learn Code Share

Update (crUd) using Perl and DBD::ORACLE

Helper Function

Resetting the data

Boilerplate template

Simple update

Extra Fun 1

Reset the data

Boilerplate change

Make sure your where clause is specific

Verify the number of affected rows

Extra Fun 2

Some other things you could try

Related

Leave a Reply Cancel reply

Helper Function

Resetting the data

Boilerplate template

Simple update

Extra Fun 1

Reset the data

Boilerplate change

Make sure your where clause is specific

Verify the number of affected rows

Extra Fun 2

Some other things you could try

Share this:

Related

Leave a Reply Cancel reply