Category Archives: Perl

Information, Learning, Perl

Delete (cruD) using Perl and DBD::ORACLE

Leave a comment

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

We use the DBD::Oracle driver to delete 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 deletes worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

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 run it whenever you would like to reset the data. (Notice this version adds people and pet data not included in other sections.)

Boilerplate template

The template we will be using is:

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

Reset the data

First, let’s run reset_data.perl to set up our data.

Simple delete

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

  • Prepare a SQL DELETE statement, deleting the record with an id of 1.
  • Bind the id value. (See the R part of this series for an explanation of bind variables.)
  • Execute the statement.
When I run this code in my Perl session, I see:

Extra Fun 1

Delete all the birds .

Your results should be:

Answer

Reset the data

Now is a good time to run reset_data.perl.

Boilerplate change

Change the boilerplate get_all_rows statements to get people and pet data.

Deleting records referenced by Foreign Keys

If you are using integrity constraints in your database (of course you are, because then you let the database do some heavy lifting for you), you will sometimes need to change the way you process your changes.

In our design, we have a Foreign Key constraint in lcs_pets that ensures if a pet has an owner, that owner exists.

This is the statement that creates the constraint in the Creating the Database Objects section of the Initial Setup post.

If we attempt to delete a record in lcs_people that is referenced in lcs_pets (Person has a pet,) we get an error.

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

Before deleting the person you have to handle the pet (watch out for claws and teeth).

There are a few options here, depending on your database design:

  • If: pets are not required to have an owner and you only want to delete the person, not the pets.  Then: you can update the pets and set their owner to null.
  • If: pets are required to have an owner.  Then: you can delete the pets for the owner.

In either of the above scenarios, you can update the pets and set their owner to another person.

Bob is moving out of our area and his new apartment doesn’t allow pets, so he’s giving them to Kim.  Let’s use that last option here.

  • Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob).  Updating is covered in the U part of this series.
  • Bind the new and old owner values.
  • Execute the statement.
  • Prepare a SQL DELETE statement, deleting records with an id of 1 (Bob).
  • Bind the id value.
  • Execute the statement.
When I run this code in my Perl session, I see:

When you change data it’s a good idea to verify the number of affected rows.  This is covered in the R part of this series.

Extra Fun 2

Due to a zoning change, snakes are no longer allowed in our area.  Stacey has decided to move and take Sneaky with her.

Let’s fix our data.

Your results should be:

Answer

Some other things you could try
  • Change the database constraints to delete or Null the child record on delete (a cascading delete).  Delete a person and let the database handle the children.
  • Remove the people who don’t have any pets.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Information, Learning, Perl

Update (crUd) using Perl and DBD::ORACLE

Leave a comment

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.

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.)

Boilerplate template

The template we will be using is:

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.
When I run this code in my Perl session, I see:

Extra Fun 1

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

Your results should be:

Answer

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.

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.
When I run this code in my Perl session, I see:

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.
When I run this code in my Perl session, I see:

$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:

Answer

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

Learning, Perl

Insert (Crud) using Perl and DBD::ORACLE

2 Comments

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.

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.

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.

Boilerplate template

The template we will be using is:

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.
When I run this code in my Perl session, I see:

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 & 2

1.  Insert more than 1 row .

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

Answer

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 till after the commit.

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

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.

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.

 You can find more information on identity columns here(pdf).
Returning data after an insert
 Sometimes we need to perform additional operations after an insert using data generated by the database, such as the identity column above.  For example, let’s add a person and a pet for them.

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.
When I run this code in my Perl session, I see:

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

Extra Fun 3

3.  Insert Sandy again but return her id and name.

Your results should be:

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
 Reset the data

Now is a good time to run reset_data.perl.

Insert more than 1 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.
When I run this code in my Perl session, I see:

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.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Information, Learning, Perl

Select (cRud) using Perl and DBD::ORACLE

Leave a comment

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

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

Simple query

We will perform a simple query that pulls all of the records in no particular order.  Here are the steps we’ll follow in the code snippet below.

  1. Create the connection object and set it to raise errors.  We will use this object to perform our database operations.
  2. Prepare the SQL SELECT statement, specifying the columns desired from the table.
  3. Execute the statement.
  4. Fetch the results and dump them to $fh.
  5. Disconnect.
When I run this code in my Perl session, I see:

Extra Fun 1

Modify the statement to order by age.  When you’re done the results should be:

Answer

Select specific rows

Now suppose I only want to see the data for Kim. I want, therefore, to restrict the rows returned by the SELECT. This is done with a WHERE clause. There are several ways to do this.

We could just put the where clause in the statement and it would work.

However, we want to choose the name at run time and store it in a variable called person_name.  You could accept the value in as an argument passed into a function, but we’ll just set a variable to keep it simple.

It is possible to simply concatenate the value into the statement.

This is very dangerous and opens our code to a SQL Injection attack.  You can follow that link for more information, but we won’t be going into detail in this series.  Just know that you should, generally, never allow end user input to be fed directly into a dynamic SQL statement.

A much safer way to pass external values into the SQL statement is by using bind variables with prepared statements.

You have a couple different options:

Placeholders:
Notice the bind_param(1,  and bind_param(2, are switched in the two examples.  With a placeholders statement, you use a ? to indicate where the bind variable value goes, then when you assign the bind_param you indicate which placeholder to assign the value to.

Named:
With this method, the :name variable will be assigned the value of ‘name’ in the provided key value set.

Notice, in both examples, that we do not wrap the bind variable for the name with quotes.  This is handled automatically when the statement is prepared for execution.

Example:
  1. Create the connection object and set it to raise errors.
  2. Assign ‘Kim’ to person_name.
  3. Prepare the SQL statement using a bind variable.
  4. Bind the value of $person_name to :name.
  5. Execute the statement.
  6. Fetch the results and dump them to $fh.
  7. Disconnect.
This will return only the data for Kim:

Extra Fun 2

Modify the statement and variable to get the people older than 30.  When you’re done the results should be:

Answer

In this section, we took a look at some basic query functionality.  When you experiment with more complex queries, if you run into problems leave a comment here or on twitter and we’ll find an answer together.

Some things you could try
  • Join the lcs_people and lcs_pets table to get the people and their pets
  • Only retrieve the person’s name and age
  • Change the order to display in descending order.

Hint – If you have trouble getting a query to run in your code, try running it in SQL Plus or another database console tool.  This will help determine if the problem is with the query or the code.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Information, Learning, Perl

Basic CRUD operations using Perl and DBD::Oracle

Leave a comment

In this series, we’re going to take a look at performing CRUD (Create Retrieve Update Delete) operations using the DBD::Oracle driver.

A good ORM will handle most of your needs.

An ORM tool can handle many of the repetitive processes when interfacing with a database.  Your project might call for an ORM tool such as DBIx::Class.  No doubt about it,  a good ORM can come in very handy.  An ORM application will typically have a function for passing in raw SQL if needed, so you may not need to go straight to the driver.

Why learn to use the driver directly?

An ORM brings its own, different complexity to a project.  It may be overkill for some projects.  There are also times when a specific task is just different enough that an ORM may not be able to help, or its application to your requirements become so complex that your code becomes difficult to maintain.

And if you’re like me, it’s hard to be satisfied with a black box approach.  You want to know more about how your tools work and you want to have options, just in case.

Martin Fowler said “Mapping to a relational database involves lots of repetitive, boiler-plate code.  A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%.”

When you have enough knowledge to implement direct CRUD operations, you are in a better position to choose the right tool for the right job.

Common Setup

All examples in this series will use the same database objects and connection information.

Creating the Database Objects

The following can be used to setup the initial tables we’ll use.  Please make sure you’re connected to a schema in which you can safely execute commands like these.

 Making the Connection
  1. use strict
  2. use the DBD::Oracle driver.
  3. Get the connection string from the environment variable.
  4. Create the connection object.
  5. Set the connection to raise errors.

We will include this code section with all examples and use the connection object “con” throughout the series.

Cleanup

To  clean up the database when you are finished with the series, you just need to drop the two tables.  Please make sure you’re connected to the correct schema where you created the tables.

Guide to Perl DBD::Oracle CRUD Series

Initial Setup
Create records
Retrieve records
Update records
Delete records