Insert (Crud) Using Perl and DBD::ORACLE

Last time, we went over the R in CRUD using Perl and DBD::Oracle. In this post, we're going to take a look at the C in CRUD: Create.

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

Note: Please review all example code and only run it if you are sure it will not cause any problems with your system. 

Helper Function

I will be using a helper function — get_all_rows(). This is a select statement used to verify that the inserts 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];
    # 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 $sth = $con->prepare("select id, name, age, notes from lcs_people order by id");
    $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.

Reseting 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 run it whenever you would like to reset the data.

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 3)");
$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);
my @names = ("Duke", "Pepe");
my @owners = (1, 2);
my @types = ("dog", "bird");

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];
    # 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 $sth = $con->prepare("select id, name, age, notes from lcs_people order by id");
    $sth->execute;

    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 Insert

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

• Prepare a SQL INSERT statement specifying the table and columns to insert the data.
• Bind the three parameters to their values. (See the R part of this series for an explanation of bind variables)
• Execute the statement.

my $sth = $con->prepare("insert into lcs_people(name, age, notes) values (:name, :age, :notes)");
$sth->bind_param( ":name","Sandy");
$sth->bind_param( ":age",31);
$sth->bind_param( ":notes","I like horses");
$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 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses

You'll notice in the bullet points above, I did not commit. The DBD::Oracle driver is set to auto commit by default. If you plan to process multiple dependent transactions, you may want to disable AutoCommit.

What Is a Transaction?

When you execute Data Manipulation Language or DML statements, such as the insert I use in this post, those changes are only visible to your current connection or session.

Those changes will not be visible to other sessions (even another session connected to the same schema in which the changes were made) until you commit your changes. That step makes it "permanent" in the database and available for everyone else to see (and possibly change in a future transaction). This also allows you to roll back a series of uncommitted transactions if one of the later transactions fails and it would cause data problems for the previous transactions.

Extra Fun 1 and 2

1. Insert more than one row.

Using data for 'Rob', 37, 'I like snakes' and 'Cheryl', 41, 'I like monkeys,' Your results should be:

Original Data: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses

New Data: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses
4 Rob 37 I like snakes
5 Cheryl 41 I like monkeys

Answer

my $sth = $con->prepare("insert into lcs_people(name, age, notes) values (:name, :age, :notes)");
$sth->bind_param( ":name","Rob");
$sth->bind_param( ":age",37);
$sth->bind_param( ":notes","I like snakes");
$sth->execute;

$sth->bind_param( ":name","Cheryl");
$sth->bind_param( ":age",41);
$sth->bind_param( ":notes","I like monkeys");
$sth->execute;

This method will work for inserting many rows at once, but there is a better way. I cover that below.

  2. Verify that a second connection cannot see your changes until after the commit.

Using data for 'Suzy', 31, 'I like rabbits' and assuming that you did the previous exercise, your results should be:

Original Data: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses
4 Rob 37 I like snakes
5 Cheryl 41 I like monkeys

New connection after insert: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses
4 Rob 37 I like snakes
5 Cheryl 41 I like monkeys

Same connection: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses
4 Rob 37 I like snakes
5 Cheryl 41 I like monkeys
6 Suzy 31 I like rabbits

New connection after commit: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses
4 Rob 37 I like snakes
5 Cheryl 41 I like monkeys
6 Suzy 31 I like rabbits

New Data: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses
4 Rob 37 I like snakes
5 Cheryl 41 I like monkeys
6 Suzy 31 I like rabbits

Notice that after the insert, the connection that made the insert can see Suzy, but the second connection can't.

After the commit, both connections see Suzy.

Answer

I modified the helper function and the get_all_rows calls in the template code to make it a little easier. If you chose to do this, please revert the template code after this exercise.

sub get_all_rows {
    my $label = $_[0];
    my $con = $_[1]; #<< Modified to use passed in connection
    # Query all rows
    my $sth = $con->prepare("select id, name, age, notes from lcs_people order by id");
    $sth->execute;

    print "\n $label: \n";
    while (my @row = $sth->fetchrow_array){
        print " @row\n";
    }
    print "\n";
}

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

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

get_all_rows('Original Data', $con);

my $sth = $con->prepare("insert into lcs_people(name, age, notes) values (:name, :age, :notes)");
$sth->bind_param( ":name","Suzy");
$sth->bind_param( ":age",31);
$sth->bind_param( ":notes","I like rabbits");
$sth->execute;

get_all_rows('New connection after insert', $con2);
get_all_rows('Same connection', $con);

$con->commit; #Commit the connection used to insert new record.

get_all_rows('New connection after commit', $con2);
get_all_rows('New Data', $con);

$con->disconnect;
$con2->disconnect;

Reset the data

Now is a good time to run reset_data.perl.

Using Identity Columns

You may have noticed that the ID column is not passed in, but is automatically set sequentially. Prior to Oracle Database 12c, this was accomplished using a sequence and a trigger.

In 12c, this can be accomplished by using an Identity Column.

CREATE TABLE lcs_people (
id NUMBER GENERATED BY DEFAULT AS identity,
....

Returning Data After an Insert

We could run an insert then select the value back using the name. But if the name is not unique, we'll have a problem. This is where the RETURNING clause is helpful.

We will perform an insert that adds a single record into the lcs_people table. Then using the returned id we will add a pet.  These are the steps performed in the code snippet below.

• Prepare a SQL INSERT statement, specifying the table and columns to insert the people data.
• Bind the three "values" parameters to their values.
• Bind the ID parameters to a new variable $new_id using bind_param_inout.
• Execute the statement returning the id into new_id.
• Prepare a SQL INSERT statement, specifying the table and columns to insert the pet data.
• Bind the ID parameter to the $new_id value.
• Execute the statement.
• Print the new_id value.
• Prepare a SQL statement.
• Bind the owner parameter to the $new_id value.
• Execute the statement.
• Print the results with a little decoration text.

my $sth = $con->prepare("insert into lcs_people(name, age, notes) values (:name, :age, :notes) returning id into :id");
$sth->bind_param( ":name","Sandy");
$sth->bind_param( ":age",31);
$sth->bind_param( ":notes","I like horses");
$sth->bind_param_inout(":id", \my $new_id, 99);
$sth->execute;

my $sth = $con->prepare("insert into lcs_pets (name, owner, type) values (:name, :owner, :type)");
$sth->bind_param( ":name","Big Red");
$sth->bind_param( ":owner", $new_id);
$sth->bind_param( ":type","horse");
$sth->execute;

print " Our new value is: $new_id\n";

$sth = $con->prepare("select name, owner, type from lcs_pets where owner = :owner");
$sth->bind_param(":owner", $new_id);
$sth->execute;

print "\n Sandy\'s pets:\n";
while (my @row = $sth->fetchrow_array){
    print " @row\n";
}
print "\n";

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

Our new value is: 3

Sandy's pets:
Big Red 3 horse

New Data: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses

Notice the new value, the owner in Sandy's pets and Sandy's id in the New Data are all 3 .

Extra Fun 3

Insert Sandy again but return her ID and name.

Your results should be:

Original Data: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses

Our new id is: 23 name: Sandy

New Data: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses
4 Sandy 31 I like horses

Notice that (3, 'Sandy'..) is still there along with our new (4, 'Sandy'..) but the returned ID is 4. It should return the new ID each time you run it.

Answer

my $sth = $con->prepare("insert into lcs_people(name, age, notes) values (:name, :age, :notes) returning id, name into :id, :new_name");
$sth->bind_param( ":name","Sandy");
$sth->bind_param( ":age",31);
$sth->bind_param( ":notes","I like horses");
$sth->bind_param_inout(":id", \my $new_id, 99);
$sth->bind_param_inout(":new_name", \my $new_name, 99);
$sth->execute;

print " Our new id is: $new_id name: $new_name\n";

Reset the data

Now is a good time to run reset_data.perl.

Insert More Than One Row

As mentioned above, when you want to insert multiple rows, running multiple insert statements is inefficient and makes multiple trips to the database so instead, we will use execute_array. In some databases, execute_array is simply a shortcut that will call execute for each record. However, if your database is capable of bulk processing like Oracle is, the driver will create a much more efficient bulk transaction.

We will perform an insert that adds two records into the lcs_people table. These are the steps performed in the code snippet below.

• Create an array for the each column populated with the data for that column. The longest array will be used to determine the number of transactions if there are any shorter arrays they will be padded with NULL.
• Prepare a SQL INSERT statement specifying the table and columns to insert the people data.  Notice we're using positional bind variables this time.
• Use execute_array to execute the statement. We aren't accessing any of the execute_array attributes "{}." Bind the three arrays in order of use.
• Print the number of records inserted using $tuples, if $tuples is unknown the transaction failed.

my @names = ("Sandy", "Suzy");
my @ages = (31, 29);
my @notes = ("I like horses", "I like rabbits");

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

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

Successfully inserted 2 records

New Data: 
1 Bob 35 I like dogs
2 Kim 27 I like birds
3 Sandy 31 I like horses
4 Suzy 29 I like rabbits

Some Things You Could Try

• Loop through an array of people and insert each one returning its id.  Using that id add multiple pets with execute_array.
• Create a large array of people.  Time the difference between looping through single inserts and using execute_array.