Getting Started With Oracle Spatial part 2

Setup

See the previous post for setup instructions.

Is the object valid?

If you don’t have a tool with Map View or if you have problems displaying the objects (for example not enough resources to display them all) you can use the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() function to check if they are valid. Pass your object into the function with a tolerance. It will return TRUE or FALSE.

For example, I have used this function to validate the line exercise answer from the previous post.

select SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(MDSYS.SDO_GEOMETRY(2002,
                                                                  4326,
                                                                  NULL,
                                                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),
                                                                  MDSYS.SDO_ORDINATE_ARRAY(-69.322920, 26.846786,
                                                                                           -69.322920, 27.746786)),
                                               0.005) line
  from dual;

Create a Polygon

We want to create a home for our dinosaurs so let’s look at some examples to create polygons using different methods.

Let’s make a simple rectangle using 2 points on opposite corners.

select MDSYS.SDO_GEOMETRY(2003,
                          4326,
                          NULL,
                          MDSYS.SDO_ELEM_INFO_ARRAY(1, --start with 1st oordinate
                                                    1003, --an exterior polygon
                                                    3), --a rectangle
                          MDSYS.SDO_ORDINATE_ARRAY(0,0, 0.9,0.9)) -–corners
  from dual;

This creates a rectangle with one corner at Lon-0, Lat-0 and the opposite corner at Lon-0.9, Lat-0.9, approximately 100KM East and 100KM North.

Let’s make another simple rectangle using points for all corners.

select MDSYS.SDO_GEOMETRY(2003,
                          4326,
                          NULL,
                          MDSYS.SDO_ELEM_INFO_ARRAY(1, --start with 1st oordinate
                                                    1003, --an exterior polygon
                                                    1), --a simple polygon
                          MDSYS.SDO_ORDINATE_ARRAY(0,0, -–first corner
                                                   0.9,0,
                                                   0.9,0.9,
                                                   0,0.9,
                                                   0,0)) -–last corner is the same as the first
  from dual;

Notice that when we create a simple exterior polygon we define the points in counter-clockwise order, also the first and last points are the same.

This could be an OK home for our dinosaurs, it’s 100 KM by 100 KM, but living on a rectangle could be kind of boring.

Let’s make it more interesting.

This time, we’ll create an irregular shaped polygon. This is basically the same as the last rectangle polygon but we’ll be using a few more points.

select MDSYS.SDO_GEOMETRY(2003,
                          4326,
                          NULL,
                          MDSYS.SDO_ELEM_INFO_ARRAY(1, --start with 1st oordinate
                                                    1003, --an exterior polygon
                                                    1), --a simple polygon
                          MDSYS.SDO_ORDINATE_ARRAY(0,0, -–first point
                                                   0.2,-0.34,
                                                   0.5,0.14,
                                                   0.93,-0.21,
                                                   0.87,0.39,
                                                   0.96,0.48,
                                                   0.75,0.91,
                                                   0.62,0.81,
                                                   0.48,0.96,
                                                   0.1,0.89,
                                                  -0.2,0.67,
                                                   0.1,0.31,
                                                   0,0)) -–last point is the same as the first
  from dual;

Exercises:

1. Create a rectangle using 2 points starting at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920 and having its opposite corner approximately 100KM East and 100KM North. (0.9 is approximately 100KM)

Answer

2. Create the same rectangle but this time, using 4 points to define the corners.

Answer

3. Create an irregular polygon near the center of the Bermuda Triangle using at least 8 points.

Answer

Note: when creating this type of polygon (1, 1003, 1), be careful to ensure that none of the lines cross or the object will be invalid.  Also, as with lines, if your object is “off the map”, it will be invalid.

Experiment with other polygons using different point sets.

Populate Tables

Now it’s time to start adding some data for our dinosaurs and their island.  We’ll start by building populateData.sql.  Here’s a stub to get you started.

delete from dd_locations where location_name = 'dino-island';

update dd_members
   set location = null
 where location is not null;

insert into dd_locations (location_name,
                          geometry)
                  values ('dino-island', ....);
update dd_members_t
   set location = ....
 where member_id = 0;

commit;

At the start of our script we are clearing any previous changes in order to maintain a clean set of data.

We’ll be storing our data in 2 tables. Our polygons that designate an area will be stored in the DD_LOCATIONS table and we’ll keep track of where our dinosaurs are in their member record using the  DD_MEMBERS.SPTUT_LOCATION column we created in the setup.

We will be storing sdo_geometry objects. These objects must be created using the same SRID (4326) we used to populate user_sdo_geom_metadata in the setup script. We will not be going into detail on the metadata and indexes, but if you receive an error mentioning SRID, it’s probably due to mismatching the SRID.

Exercises:

1. Insert the polygon we previously defined into the dd_locations table with a location_name of ‘dino-island’.

     insert into dd_locations (location_name,
                          geometry)
     values ('dino-island', …)

2. Update our admin dinosaur (member_id = 0) and set the location to a point inside of ‘dino-island’.

     update dd_members_t
        set location = …
      where member_id = 0;

New populateData.sql

Random Polygon

Most of the time you’ll be creating pre-defined objects. But, one of the reasons our dinosaurs have been able to stay undetected for so long is their island randomly changes shape every now and then.

In previous exercises, we created our polygons one point at a time in a counter-clockwise direction so we should be able to do this programmatically with some random coordinates.

On the first attempt to create a random polygon, I went with an idea to walk the edges of a square and define points a random distance in or out from the edge. While this did work, the function was a bit more complicated than I wanted, involving nested loops and multi-layered if/then statements.

I decided to go with a circular approach instead.

Helper Functions:

Here are examples of a couple useful functions.

select SDO_UTIL.POINT_AT_BEARING(SDO_GEOMETRY(2001,
                                              4326,
                                              SDO_POINT_TYPE(0, 0, NULL),
                                              NULL,
                                              NULL),
                                 1,
                                 100)
  from dual;

SDO_UTIL.POINT_AT_BEARING accepts:

  • A standard SDO_GEOMETRY point object
  • A radian value from 0 to 2pi. The gif here explains what a radian is perfectly.
    Note: SDO_UTIL.POINT_AT_BEARING uses 12 o’clock as 0 radian and increases clockwise.
  • A distance in M.

The function returns an SDO_GEOMETRY point object, X meters from the given point in the direction of the radian value.

Since we are creating our polygon using an MDSYS.SDO_ORDINATE_ARRAY we need to extract the x/y coordinates from our new point and add them to our array. (Remember the first and last point in the array are the same.)

SELECT t.x, t.y
  from TABLE(SDO_UTIL.GETVERTICES(MDSYS.SDO_GEOMETRY(2001,
                                                     4326,
                                                     MDSYS.SDO_POINT_TYPE(0.000755906246724255,0.000488632913734697,NULL),
                                                     NULL,
                                                     NULL))) t;

SDO_UTIL.GETVERTICES can be used to extract quite a lot of data from our point object, but since we’re only interested in the x / y coordinates we will treat the output as a table and select the values.

Exercise:

Create a function that accepts:

  • A minimum radius. We need to ensure that our dinosaurs have land to stand on.
  • A variance percent that we will use to calculate our random edge points.
  • A center point longitude.
  • A center point latitude.

The function should return a valid SDO_GEOMETRY simple polygon object.

Change populateData.sql to use the new function for the dino-island value, centered in the Bermuda Triangle.

New generate_polygon_rad.fnc

New populateData.sql

Random Point Inside a Given Polygon

Now that we have an island lets figure out where the rest of our dinosaurs are.

We want to make sure our dinosaurs are all on the island. We could take the easy way out and just use the POINT_AT_BEARING function with a random radian and distance from 0 to the minimum limit used to create the island, but that leaves a lot of unused space out by the beaches.

Helper Functions:

Here are examples of a couple useful functions.

select SDO_GEOM.SDO_INTERSECTION(
                          sdo_geometry (2002, 4326, null,
                                        sdo_elem_info_array (1,2,1),
                                        sdo_ordinate_array (0,0, 1.95,1.95)
                                       ),
                          MDSYS.SDO_GEOMETRY(2003,
                                             4326,
                                             NULL,
                                             MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 3),
                                             MDSYS.SDO_ORDINATE_ARRAY(0,0, .9,.9)),
                          0.005 --Tolerance
                      ) intersection
 from dual;

SDO_GEOM.SDO_INTERSECTION accepts 2 SDO_GEOMETRY objects and returns just the overlapping section. Pass in your dino-island and a line starting at the center that is long enough to extend outside of the island. You will get back a line from the center that stops right at the edge of the island.

select SDO_GEOM.SDO_LENGTH(SDO_GEOM.SDO_INTERSECTION(
                       sdo_geometry (2002, 4326, null,
                                     sdo_elem_info_array (1,2,1),
                                     sdo_ordinate_array (0,0, 1.95,1.95)
                                    ),
                       MDSYS.SDO_GEOMETRY(2003,
                                          4326,
                                          NULL,
                                          MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 3),
                                          MDSYS.SDO_ORDINATE_ARRAY(0,0, .9,.9)),
                       0.005 --Tolerance
                     ),
                     0.005) line_length --Tolerance
from dual;

SDO_GEOM.SDO_LENGTH accepts an SDO_GEOMETRY object and returns different information depending on the object passed in. We’re passing in a line so it will return the length of that line.

You may notice that these two functions accept a Tolerance parameter. The tolerance value determines how close two adjacent points have to be together to be considered the same point. So passing a value of 0.05 means: Points which are closer together than 5 centimeters, are considered to be one point.

Exercise:

Create a function that accepts:

  • The island as an SDO_GEOMETRY simple polygon object.
  • The island’s minimum radius.
  • The island’s variance percent.
  • The island’s center point longitude.
  • The island’s center point latitude.

The function should return a valid SDO_GEOMETRY point object located inside the island polygon.

New generate_random_location.fnc

Change populateData.sql to use the new function to update the dinosaurs locations using the same values as the island.

New populateData.sql

Using this information and the methods you’ve used previously, you can generate random points around the island without having to worry about dropping any dinosaurs in the ocean.

Using this specific methodology will tend to favor placing dinosaurs in the center of the island since the location always starts from the center and goes out. Feel free to try other methods to create a more random dispersion.

Random Point Inside a Given Polygon using only the Polygon

The function generate_random_location works well enough to generate a point inside a polygon if you already know something about it.

Let’s change it so it can accept just the polygon and figure out the rest of the data we need.

We still want to make sure our dinosaurs are all on the island.

Helper Functions:

Here are examples of a couple useful functions.

select sdo_geom.sdo_mbc_center(mdsys.sdo_geometry(2003,
                                                  4326,
                                                  null,
                                                  mdsys.sdo_elem_info_array(1, 1003, 3),
                                                  mdsys.sdo_ordinate_array(0,0, .9,.9)),
                               0.005) --Tolerance
  from dual;

SDO_GEOM.SDO_MBC_CENTER accepts an SDO_GEOMETRY object and a tolerance. The function calculates the Minimum Bounding Circle, which is the smallest circle that could contain the object. It then returns an SDO_GEOMETRY point object for the calculated center of the MBC.

select sdo_geom.sdo_mbc_radius(mdsys.sdo_geometry(2003,
                                                  4326,
                                                  null,
                                                  mdsys.sdo_elem_info_array(1, 1003, 3),
                                                  mdsys.sdo_ordinate_array(0,0, .9,.9)),
                               0.005) --Tolerance
  from dual;

SDO_GEOM.SDO_MBC_RADIUS accepts an SDO_GEOMETRY object and a tolerance. The function calculates the Minimum Bounding Circle, which is the smallest circle that could contain the object. It then returns the calculated radius of the MBC.

Exercise:

Change the generate_random_location function so it accepts:

  • The island as an SDO_GEOMETRY simple polygon object.

The function should return a valid SDO_GEOMETRY point object located inside the island polygon.

New generate_random_location.fnc

Change populateData.sql so it uses the new function to update the dinosaurs locations using only the island.

New populateData.sql

Find the Dinosaurs

Run populateData.sql to generate an island and update the dinosaur locations.

Now that we have an island and we’ve scattered the dinosaurs, let’s figure out where they are.

Helper Functions:

Here are examples of a couple useful functions.

select *
  from dd_members dinos
 where dinos.location is not null
   and sdo_within_distance (dinos.location,
                            mdsys.sdo_geometry(2001,
                                               4326,
                                               mdsys.sdo_point_type(-69.322920,26.846786, null),
                                               null,
                                               null),
                            'distance=10 unit=km') = 'TRUE'; --parameter string

SDO_WITHIN_DISTANCE accepts two SDO_GEOMETRY objects and a parameter string. The first SDO_GEOMETRY object must be in a table and have a spatial index. The parameter string is a set of parameters defined in the linked document, for our purposes we are using distance and unit. If the two SDO_GEOMETRY objects are within the given distance the function returns ‘TRUE’.

select a.gid
  from polygons a, query_polys B
 where B.gid = 1
   and SDO_RELATE(A.Geometry, B.Geometry,
                  'mask=touch') = 'TRUE';

SDO_RELATE accepts two SDO_GEOMETRY objects and a mask parameter. The mask parameter specifies the relationship you are testing. See the Usage Notes section of the linked documentation for a list of valid options.

Alternatively, if your objects are not both in tables or you are trying to determine their relationship you could use SDO_GEOM.RELATE but it is less efficient since it does not leverage a Spatial Index.

select sdo_geom.relate(sdo_geometry(2001,4326,mdsys.sdo_point_type(-69.343504,26.803024,null),null,null),
                       'DETERMINE', --mask parameter
                       Geometry,
                       0.005)
  from dd_locations
 where dd_locations.location_name = 'dino-island';

Exercises:

  1. Write a query that finds all of the dinosaurs within 5 KM of the beach.
  2. Write a statement that creates a smaller polygon with a minimum radius of 10 KM and centered randomly inside the island. Using the location_name of ‘asteroid-impact’ insert this polygon into the dd_locations table.
  3. Write a query that finds the dinosaurs inside the ‘asteroid-impact’ so we can warn them to move to safety.
Answer 1
Answer 2
Answer 3

Summary

There are many more functions and capabilities included with Oracle Spatial, this tutorial has barely scratched the surface.

The ‘answers’ I have included are merely suggestions and there are many other ways to complete the same tasks. Please dig into the documentation and experiment.

Please leave comments if you have any questions, corrections or suggestions.

Getting Started With Oracle Spatial part 1

In this tutorial, we’ll go over how to create some basic spatial objects such as points, lines and polygons. We’ll create some random objects and run some fun queries.

We’ll be using the Spatial and Graph Developer’s Guide as our reference.

First, let’s take a look at the SDO_GEOMETRY Object.

CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER, 
 SDO_SRID NUMBER,
 SDO_POINT SDO_POINT_TYPE,
 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES SDO_ORDINATE_ARRAY);

SDO_GTYPE is a 4 digit number that defines what type of object we’re making, using a format of DLTT

D = Dimensions of the object 2, 3 or 4 (can not mix different dimensions in the same layer)

L = Linear Referencing System. This is a more advanced topic for another day. For our examples, we will be using 0.

TT = Type of Geometry, 00 through 09

We will be using the following Geometry Types. You can find the full list here.

Value Description
DL01 Point Geometry contains one point.
DL02 Line or Curve Geometry contains one line string that can contain straight or circular arc segments, or both. (LINE and CURVE are synonymous in this context.)
DL03 Polygon or Surface Geometry contains one polygon with or without holes,1 or one surface consisting of one or more polygons. In a three-dimensional polygon, all points must be on the same plane.

SDO_SRID is a number referencing the coordinate system used, it must either be NULL or the SRID value from the SDO_COORD_REF_SYS table.  We’ll be using 4326, which is the standard Earth-based Latitude / Longitude system.

SDO_POINT is used to define the object as a point. This is only used if this object is a point, otherwise, this should be NULL and the following two parameters will define the object.

SDO_ELEM_INFO is basically a set of numbers that work like a key for using the information in the SDO_ORDINATES parameter to create the object. These numbers are grouped into one or more sets of 3 numbers or triplets. If we are creating a complex object that has more than one segment, each segment will be defined by its own triplet.

  • The first number in the triplet is the SDO_STARTING_OFFSET and indicates which number in the following SDO_ORDINATE_ARRAY is the starting point for this segment. The first element is 1.
  • The second number is the SDO_ETYPE which is used to define the type of object we are creating in this segment. For example, 2 is a straight line and 1003 is a simple polygon.
  • The third number is the SDO_INTERPRETATION and is used in one of two ways.
    • For simple objects such as a polygon, the SDO_INTERPRETATION will further define the type of polygon. 1 is a simple straight line polygon, 2 uses circular arcs, 3 is a rectangle and 4 is a circle.
    • For complex objects such as a polygon with holes in it, SDO_INTERPRETATION is used to specify how many ordinates are used for this segment.

Please see Table 2-2 Values and Semantics in SDO_ELEM_INFO for further details.

SDO_ORDINATES are pairs of coordinates used along with SDO_ELEM_INFO to define the object

Setup

You’ll need a copy of the git repository.

  1. Go to https://github.com/oracle/dino-date
  2. Fork the repository.
  3. Clone the repository.
  4. Follow the DinoDate install instructions.
  5. Run the following as the new DD user.

This script will add the sptut_location column to our dd_members table, setup the spatial metadata and create the spatial indexes. We will not be covering these steps in the tutorial.

declare
  procedure exec_dml (stmt in varchar2, ignore_code in number default null) as
begin
  execute immediate stmt;
  exception when others then
  if ignore_code is null or sqlcode != ignore_code then
    raise;
  end if;
end;

begin
  /* delete tutorial objects from dd.dd_locations */
  delete from dd_locations
   where LOCATION_NAME in ('dino-island', 'asteroid-zone');

  commit;

  /* Remove dd.dd_members_t.sptut_location column index*/
  exec_dml('drop index dd_member_sptut_location_sx', -1418);

  /* Remove dd.dd_members_t.sptut_location column*/
  exec_dml('alter table dd.dd_members_t drop column sptut_location', -904);

  /* add dd.dd_members_t.sptut_location column*/
  exec_dml('alter table dd.dd_members_t add sptut_location sdo_geometry');
 
  /* rebuild dd.dd_members view*/
  exec_dml('create or replace editioning view dd_members as
  select *
    from dd_members_t');

  /* set up oracle spatial for dd.dd_members_t.sptut_location */
  delete from user_sdo_geom_metadata
  where table_name='DD_MEMBERS_T';

  insert into user_sdo_geom_metadata (table_name,
                                      column_name,
                                      diminfo,
                                      srid)
                              values ('DD_MEMBERS_T',
                                      'sptut_location',
                                      sdo_dim_array (sdo_dim_element ('X',
                                                                      -180,
                                                                      180,
                                                                      1),
                                                     sdo_dim_element ('Y',
                                                                      -90,
                                                                      90,
                                                                      1)),
                                      4326);

  commit;
 
  /* create spatial index on dd.dd_members_t.sptut_location */
  exec_dml('create index dd_member_sptut_location_sx
  on dd.dd_members_t (sptut_location)
  indextype is mdsys.spatial_index', -955);

  /* rebuild index on location table, just in case */
  exec_dml('drop index dd_location_sx', -1418);
  exec_dml('create index dd_location_sx
  on dd_locations_t (geometry)
  indextype is mdsys.spatial_index', -955);
end;
/

show errors

 

This script can also be used to reset the database if something goes wrong.

We’ll need some dinosaurs to locate so let’s randomly generate a few.

Connected as dd run the following command to generate 1,000 dinosaurs.

exec dbms_output.put_line(dd_admin_pkg.generate_members(1000));

Cleanup

If you’d like to restore the DD schema to its default state, run this script to remove the new column and re-create the dd_locations index.

declare
  procedure exec_dml (stmt in varchar2, ignore_code in number default null) as
begin
  execute immediate stmt;
  exception when others then
    if ignore_code is null or sqlcode != ignore_code then
      raise;
    end if;
end;

begin
  /* delete tutorial objects from dd.dd_locations */
  delete from dd_locations
   where location_name in ('dino-island', 'asteroid-zone');

  commit;

  /* remove dd.dd_members_t.sptut_location column index*/
  exec_dml('drop index dd_member_sptut_location_sx', -1418);

  /* remove dd.dd_members_t.sptut_location column*/
  exec_dml('alter table dd.dd_members_t drop column sptut_location', -904);
 
  /* rebuild dd.dd_members view*/
  exec_dml('create or replace editioning view dd_members as
  select *
    from dd_members_t');

  /* remove oracle spatial data for dd.dd_members_t.sptut_location */
  delete from user_sdo_geom_metadata
   where table_name='DD_MEMBERS_T';

  /* rebuild index on location table */
  exec_dml('drop index dd_location_sx', -1418);
  exec_dml('create index dd_location_sx on dd_locations_t (geometry) indextype is mdsys.spatial_index', -955);
end;
/

show errors

View the Objects

For my examples, I will be using Oracle SQL Developer which includes a Map View utility to display the objects.

Create a Point

Select the following example from dual to create a point.

MDSYS.SDO_GEOMETRY(2001, --2 dimensions, 0 LRS, 01 is a point
                   4326, --SRID for Earth lat/lon system
                   MDSYS.SDO_POINT_TYPE(0, 0,NULL), --point located at Lon-0,Lat-0
                   NULL, --not used for a point
                   NULL) --not used for a point

When you run the query you’ll see it generates an SDO_GEOMETRY type object. In SQL Developer you can select the results, right click the object and “Invoke Map View on Results Set” to display the point in the Map Viewer.

Note that there is no background map displayed, just an empty background with a point. If there were a map, this point would be just west of Africa.

Exercise:

Create a point at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920.

If you invoke the map view for both points, you can change the marker type for one to identify which is which. If your Bermuda Triangle point is South East of 0,0 you have your Lat and Lon backward. If this is a consistent issue, you can use named parameters.

Answer

Create a Line

Select the following example from dual to create a line.

MDSYS.SDO_GEOMETRY(2002, --2 dimensions, 0 LRS, 02 is a line
                   4326, --SRID for Earth lat/lon system
                   NULL, -- only used for a point
                   MDSYS.SDO_ELEM_INFO_ARRAY(1, --start with 1st oordinate
                                             2, --a straight line
                                             1), --a simple straight line
                   MDSYS.SDO_ORDINATE_ARRAY(0,0, -–start coordinates
                                            0.9,0)) -–end coordinates

This creates a line starting at Lon-0, Lat-0 and ending at Lon-0.9, Lat-0, approximately 100KM East.

Exercise:

Create a line starting at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920 and going approximately 100KM North. (0.9 is approximately 100KM)

Answer

Experiment with other lines. Keep in mind if your object is “off the map”, it will be invalid.

In the next post, I’ll go over creating polygons and run some fun spatial queries.

Delete (cruD) using Ruby-OCI8

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

We use the ruby-oci8 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.

def get_all_rows (label, data_type = 'people')
 connectString = ENV['db_connect']
 con = OCI8.new(connectString)

 # Query all rows
 statement = 'select id, name, age, notes from lcs_people order by id';

 if data_type == 'pets'
   statement = 'select id, name, owner, type from lcs_pets order by owner, id'
 end

 cursor = con.parse(statement)
 cursor.exec
 printf " %s:\n", label
 cursor.fetch() {|row|
 if data_type == 'people'
   printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
 else
   printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
 end
 }
 printf "\n"
end

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

require 'oci8'
connectString = ENV['db_connect']
con = OCI8.new(connectString)

# Delete rows
cursor = con.parse("delete from lcs_pets")
cursor.exec

# Reset Identity Coulmn
cursor = con.parse("alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 8)")
cursor.exec

# Delete rows
cursor = con.parse("delete from lcs_people")
cursor.exec

# Reset Identity Coulmn
cursor = con.parse("alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 8)")
cursor.exec

# Insert default people rows
cursor = con.parse("INSERT INTO lcs_people(id, name, age, notes) VALUES (:id, :name, :age, :notes)")
cursor.max_array_size = 7
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
cursor.bind_param_array(:name, ["Bob", "Kim", "Cheryl", "Bob", "Stacey", "Pete", "Pat"])
cursor.bind_param_array(:age, [35, 27, 23, 27, 45, 23, 36])
cursor.bind_param_array(:notes, ["I like dogs", "I like birds", "I like horses", "I like rabbits", "I like snakes", "I like cats", "I like dogs"])
people_row_count = cursor.exec_array
printf " %d people rows inserted\n", people_row_count

# Insert default pet rows
cursor = con.parse("INSERT INTO lcs_pets(id, name, owner, type) VALUES (:id, :name, :owner, :type)")
cursor.max_array_size = 7
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
cursor.bind_param_array(:name, ["Duke", "Dragon", "Sneaky", "Red", "Red", "Buster", "Fido"])
cursor.bind_param_array(:owner, [1, 2, 5, 2, 3, 1, 7])
cursor.bind_param_array(:type, ["dog", "bird", "snake", "bird", "horse", "dog", "cat"])
pet_row_count = cursor.exec_array
printf " %d pet rows inserted\n", pet_row_count

con.commit
Boilerplate template

The template we will be using is:

require 'oci8'
connectString = ENV['db_connect']

def get_all_rows (label, data_type = 'people')
 connectString = ENV['db_connect']
 con = OCI8.new(connectString)

 # Query all rows
 statement = 'select id, name, age, notes from lcs_people order by id';

 if data_type == 'pets'
   statement = 'select id, name, owner, type from lcs_pets order by owner, id'
 end

 cursor = con.parse(statement)
 cursor.exec
 printf " %s:\n", label
 cursor.fetch() {|row|
 if data_type == 'people'
   printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
 else
   printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
 end
 }
 printf "\n"
end

con = OCI8.new(connectString)

get_all_rows('Original Data', 'pets')

# Your code here

get_all_rows('New Data', 'pets')

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

Reset the data

First, let’s run reset_data.rb 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.
  • Parse the statement to create a cursor.
  • Bind the id value.  (See the R part of this series for an explanation of bind variables.)
  • Execute the statement.
  • Commit the transaction.
statement = "delete from lcs_pets where id = :id"
cursor = con.parse(statement)
cursor.bind_param(:id,1)
cursor.exec
con.commit

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

 Original Data:
 Id: 1, Name: Duke, Owner: 1, Type: dog
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

 New Data:
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat
Extra Fun 1

Delete all the birds.

Your results should be:

 Original Data:
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

 New Data:
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat
Answer
Reset the data

Now is a good time to run reset_data.rb.

Boilerplate change

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

get_all_rows('Original People Data', 'people')
get_all_rows('Original Pet Data', 'pets')

# Your code here

get_all_rows('New People Data', 'people')
get_all_rows('New Pet Data', 'pets')
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.

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
/

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

statement = "delete from lcs_people where id = :id"
cursor = con.parse(statement)
cursor.bind_param(:id,1)
cursor.exec
con.commit

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

stmt.c:243:in oci8lib_230.so: ORA-02292: integrity constraint (BLAINE.FK_LCS_PETS_OWNER) violated - child record found (OCIError)
 from /home/bcarter/.rvm/gems/ruby-2.3.1/gems/ruby-oci8-2.2.2/lib/oci8/cursor.rb:129:in `exec'
 from delete2.rb:36:in `<main>'

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.
  • Parse the statement to create a cursor.
  • Bind the new and old owner values.
  • Execute the statement.
  • Prepare a SQL DELETE statement, deleting records with an id of 1 (Bob).
  • Parse the statement to create a cursor.
  • Bind the id value.
  • Execute the statement.
  • Commit both transactions.
statement = "update lcs_pets set owner = :newOwner where owner = :oldOwner"
cursor = con.parse(statement)
cursor.bind_param(:newOwner,2)
cursor.bind_param(:oldOwner,1)
cursor.exec

statement = "delete from lcs_people where id = :id"
cursor = con.parse(statement)
cursor.bind_param(:id,1)
cursor.exec
con.commit

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

 Original People Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Cheryl, Age: 23, Notes: I like horses
 Id: 4, Name: Bob, Age: 27, Notes: I like rabbits
 Id: 5, Name: Stacey, Age: 45, Notes: I like snakes
 Id: 6, Name: Pete, Age: 23, Notes: I like cats
 Id: 7, Name: Pat, Age: 36, Notes: I like dogs

 Original Pet Data:
 Id: 1, Name: Duke, Owner: 1, Type: dog
 Id: 6, Name: Buster, Owner: 1, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

 New People Data:
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Cheryl, Age: 23, Notes: I like horses
 Id: 4, Name: Bob, Age: 27, Notes: I like rabbits
 Id: 5, Name: Stacey, Age: 45, Notes: I like snakes
 Id: 6, Name: Pete, Age: 23, Notes: I like cats
 Id: 7, Name: Pat, Age: 36, Notes: I like dogs

 New Pet Data:
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: dog
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

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:

 Original People Data:
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Cheryl, Age: 23, Notes: I like horses
 Id: 4, Name: Bob, Age: 27, Notes: I like rabbits
 Id: 5, Name: Stacey, Age: 45, Notes: I like snakes
 Id: 6, Name: Pete, Age: 23, Notes: I like cats
 Id: 7, Name: Pat, Age: 36, Notes: I like dogs

 Original Pet Data:
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: dog
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 3, Name: Sneaky, Owner: 5, Type: snake
 Id: 7, Name: Fido, Owner: 7, Type: cat

 New People Data:
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Cheryl, Age: 23, Notes: I like horses
 Id: 4, Name: Bob, Age: 27, Notes: I like rabbits
 Id: 6, Name: Pete, Age: 23, Notes: I like cats
 Id: 7, Name: Pat, Age: 36, Notes: I like dogs

 New Pet Data:
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Dragon, Owner: 2, Type: bird
 Id: 4, Name: Red, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: dog
 Id: 5, Name: Red, Owner: 3, Type: horse
 Id: 7, Name: Fido, Owner: 7, Type: cat
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

Delete (cruD) using Perl and DBD::ORACLE

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.

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

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 8)");
$sth->execute;

# Insert default rows
my @ids = (1, 2, 3, 4, 5, 6, 7);
my @names = ("Bob", "Kim", "Cheryl", "Bob", "Stacey", "Pete", "Pat");
my @ages = (35, 27, 23, 27, 45, 23, 36);
my @notes = ("I like dogs", "I like birds", "I like horses", "I like rabbits", "I like snakes", "I like cats", "I like dogs");

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", "Dragon", "Sneaky", "Red", "Red", "Buster", "Fido");
my @owners = (1, 2, 5, 2, 3, 1, 7);
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->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', 'pets');

# Your code here

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

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.
my $sth = $con->prepare("delete from lcs_pets where id = :id");
$sth->bind_param( ":id",1);
$sth->execute;

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

 Original Data: 
 1 Duke 1 dog
 6 Buster 1 dog
 2 Dragon 2 bird
 4 Red 2 bird
 5 Red 3 horse
 3 Sneaky 5 snake
 7 Fido 7 cat

 New Data: 
 6 Buster 1 dog
 2 Dragon 2 bird
 4 Red 2 bird
 5 Red 3 horse
 3 Sneaky 5 snake
 7 Fido 7 cat

Extra Fun 1

Delete all the birds .

Your results should be:

 Original Data: 
 6 Buster 1 dog
 2 Dragon 2 bird
 4 Red 2 bird
 5 Red 3 horse
 3 Sneaky 5 snake
 7 Fido 7 cat


 New Data: 
 6 Buster 1 dog
 5 Red 3 horse
 3 Sneaky 5 snake
 7 Fido 7 cat
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.

get_all_rows('Original People Data', 'people');
get_all_rows('Original Pet Data', 'pets');

# Your code here

get_all_rows('New People Data', 'people');
get_all_rows('New Pet Data', 'pets');
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.

ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
/

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

my $sth = $con->prepare("delete from lcs_people where id = :id");
$sth->bind_param( ":id",1);
$sth->execute;

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

DBD::Oracle::st execute failed: ORA-02292: integrity constraint (BLAINE.FK_LCS_PETS_OWNER) violated - child record found (DBD ERROR: OCIStmtExecute) [for Statement "delete from lcs_people where id = :id" with ParamValues: :id=1] at delete2.perl line 41.
DBD::Oracle::st execute failed: ORA-02292: integrity constraint (BLAINE.FK_LCS_PETS_OWNER) violated - child record found (DBD ERROR: OCIStmtExecute) [for Statement "delete from lcs_people where id = :id" with ParamValues: :id=1] at delete2.perl line 41.

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.
my $sth = $con->prepare("update lcs_pets set owner = :newOwner where owner = :oldOwner");
$sth->bind_param(":newOwner",2);
$sth->bind_param(":oldOwner",1);
$sth->execute;

my $sth = $con->prepare("delete from lcs_people where id = :id");
$sth->bind_param(":id",1);
$sth->execute;

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

 Original People Data: 
 1 Bob 35 I like dogs
 2 Kim 27 I like birds
 3 Cheryl 23 I like horses
 4 Bob 27 I like rabbits
 5 Stacey 45 I like snakes
 6 Pete 23 I like cats
 7 Pat 36 I like dogs


 Original Pet Data: 
 1 Duke 1 dog
 6 Buster 1 dog
 2 Dragon 2 bird
 4 Red 2 bird
 5 Red 3 horse
 3 Sneaky 5 snake
 7 Fido 7 cat


 New People Data: 
 2 Kim 27 I like birds
 3 Cheryl 23 I like horses
 4 Bob 27 I like rabbits
 5 Stacey 45 I like snakes
 6 Pete 23 I like cats
 7 Pat 36 I like dogs


 New Pet Data: 
 1 Duke 2 dog
 2 Dragon 2 bird
 4 Red 2 bird
 6 Buster 2 dog
 5 Red 3 horse
 3 Sneaky 5 snake
 7 Fido 7 cat

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:

 Original People Data: 
 2 Kim 27 I like birds
 3 Cheryl 23 I like horses
 4 Bob 27 I like rabbits
 5 Stacey 45 I like snakes
 6 Pete 23 I like cats
 7 Pat 36 I like dogs


 Original Pet Data: 
 1 Duke 2 dog
 2 Dragon 2 bird
 4 Red 2 bird
 6 Buster 2 dog
 5 Red 3 horse
 3 Sneaky 5 snake
 7 Fido 7 cat


 New People Data: 
 2 Kim 27 I like birds
 3 Cheryl 23 I like horses
 4 Bob 27 I like rabbits
 6 Pete 23 I like cats
 7 Pat 36 I like dogs


 New Pet Data: 
 1 Duke 2 dog
 2 Dragon 2 bird
 4 Red 2 bird
 6 Buster 2 dog
 5 Red 3 horse
 7 Fido 7 cat
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

Update (crUd) using Ruby-OCI8

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

We use the ruby-oci8 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.

def get_all_rows (label, data_type = 'people')
 connectString = ENV['db_connect']
 con = OCI8.new(connectString)

 # Query all rows
 statement = 'select id, name, age, notes from lcs_people order by id';

 if data_type == 'pets'
 statement = 'select id, name, owner, type from lcs_pets order by owner, id'
 end

 cursor = con.parse(statement)
 cursor.exec
 printf " %s:\n", label
 cursor.fetch() {|row|
   if data_type == 'people'
     printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
   else
     printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
   end
 }
 printf "\n"
end

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

# Query all rows
require 'oci8'
connectString = ENV['db_connect']
con = OCI8.new(connectString)

# Delete rows
cursor = con.parse("delete from lcs_pets")
cursor.exec

# Reset Identity Coulmn
cursor = con.parse("alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 8)")
cursor.exec

# Delete rows
cursor = con.parse("delete from lcs_people")
cursor.exec

# Reset Identity Coulmn
cursor = con.parse("alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)")
cursor.exec

# Insert default people rows
cursor = con.parse("INSERT INTO lcs_people(id, name, age, notes) VALUES (:id, :name, :age, :notes)")
cursor.max_array_size = 2
cursor.bind_param_array(:id, [1, 2])
cursor.bind_param_array(:name, ["Bob", "Kim"])
cursor.bind_param_array(:age, [35, 27])
cursor.bind_param_array(:notes, ["I like dogs", "I like birds"])
people_row_count = cursor.exec_array
printf " %d people rows inserted\n", people_row_count

# Insert default pet rows
cursor = con.parse("INSERT INTO lcs_pets(id, name, owner, type) VALUES (:id, :name, :owner, :type)")
cursor.max_array_size = 7
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
cursor.bind_param_array(:name, ["Duke", "Pepe", "Princess", "Polly", "Rollo", "Buster", "Fido"])
cursor.bind_param_array(:owner, [1, 2, 1, 1, 1, 1, 1])
cursor.bind_param_array(:type, ["dog", "bird", "snake", "bird", "horse", "dog", "cat"])
pet_row_count = cursor.exec_array
printf " %d pet rows inserted\n", pet_row_count

con.commit
Boilerplate template

The template we will be using is:

require 'oci8'
connectString = ENV['db_connect']

def get_all_rows (label, data_type = 'people')
 connectString = ENV['db_connect']
 con = OCI8.new(connectString)

 # Query all rows
 statement = 'select id, name, age, notes from lcs_people order by id';

 if data_type == 'pets'
   statement = 'select id, name, owner, type from lcs_pets order by owner, id'
 end

 cursor = con.parse(statement)
 cursor.exec
 printf " %s:\n", label
 cursor.fetch() {|row|
   if data_type == 'people'
     printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
   else
     printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
   end
 }
 printf "\n"
end

con = OCI8.new(connectString)

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.
  • Parse the statement to create a cursor.
  • Bind the age and id values.  (See the R part of this series for an explanation of bind variables.)
  • Execute the statement.
  • Commit the transaction.
statement = "update lcs_people set age = :age where id = :id"
cursor = con.parse(statement)
cursor.bind_param(:age,31)
cursor.bind_param(:id,1)
cursor.exec
con.commit

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

 Example: ruby update1.rb 
 Original Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds

 New Data:
 Id: 1, Name: Bob, Age: 31, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
Extra Fun 1

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

Your results should be:

 Original Data:
 Id: 1, Name: Bob, Age: 31, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds

 New Data:
 Id: 1, Name: Bob, Age: 31, Notes: I like cats
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
Answer
Reset the data

Now is a good time to run reset_data.rb.

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');
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’.
  • Parse the statement to create a cursor.
  • Bind the newOwner, oldOwner and type values.
  • Execute the statement.
  • Commit the transaction.
statement = "update lcs_pets set owner = :newOwner where owner = :oldOwner and type = :type"
cursor = con.parse(statement)
cursor.bind_param(:newOwner,2)
cursor.bind_param(:oldOwner,1)
cursor.bind_param(:type,"dog")
cursor.exec
con.commit

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

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

 New Data:
 Id: 3, Name: Princess, Owner: 1, Type: snake
 Id: 4, Name: Polly, Owner: 1, Type: bird
 Id: 5, Name: Rollo, Owner: 1, Type: horse
 Id: 7, Name: Fido, Owner: 1, Type: cat
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Pepe, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: 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 the return value from cursor.exe.

  • Prepare a SQL UPDATE statement.
  • Parse the statement to create a cursor.
  • Bind the newOwner to 1 and id to 6.
  • Execute the statement returning the number of changed rows.
  • Commit the transaction.
  • Print the number of changed rows.
statement = "update lcs_pets set owner = :newOwner where id = :id"
cursor = con.parse(statement)
cursor.bind_param(:newOwner,2)
cursor.bind_param(:id,6)
changed = cursor.exec
con.commit

printf "Number of rows updated: %d\n\n", changed

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

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

Number of rows updated: 1

 New Data:
 Id: 3, Name: Princess, Owner: 1, Type: snake
 Id: 4, Name: Polly, Owner: 1, Type: bird
 Id: 5, Name: Rollo, Owner: 1, Type: horse
 Id: 7, Name: Fido, Owner: 1, Type: cat
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Pepe, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: dog

cursor.exe will return the number of rows affected for insert, update and delete statements and for a select statement, it returns the number of the select-list.

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:
 Id: 3, Name: Princess, Owner: 1, Type: snake
 Id: 4, Name: Polly, Owner: 1, Type: bird
 Id: 5, Name: Rollo, Owner: 1, Type: horse
 Id: 7, Name: Fido, Owner: 1, Type: cat
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Pepe, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: dog

Number of rows updated: 1

 New Data:
 Id: 3, Name: Princess, Owner: 1, Type: snake
 Id: 5, Name: Rollo, Owner: 1, Type: horse
 Id: 7, Name: Fido, Owner: 1, Type: cat
 Id: 1, Name: Duke, Owner: 2, Type: dog
 Id: 2, Name: Pepe, Owner: 2, Type: bird
 Id: 4, Name: Polly, Owner: 2, Type: bird
 Id: 6, Name: Buster, Owner: 2, Type: dog
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

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

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->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
 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->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
 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
 2 Pepe 2 bird
 4 Polly 2 bird
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

Insert (Crud) using Ruby-OCI8

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

We will be using the ruby-oci8 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.

def get_all_rows (label)
 con = OCI8.new(connectString)

 # Query all rows
 statement = 'select id, name, age, notes from lcs_people order by id'
 cursor = con.parse(statement)
 cursor.exec
 cursor.fetch() {|row|
 printf "Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
 }
 printf "\n"
end

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.rb with the following code and then run it whenever you would like to reset the data.

# Query all rows
require 'oci8'
connectString = ENV['db_connect']
con = OCI8.new(connectString)

# Delete rows
cursor = con.parse("delete from lcs_pets")
cursor.exec

# Reset Identity Coulmn
cursor = con.parse("alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 3)")
cursor.exec

# Delete rows
cursor = con.parse("delete from lcs_people")
cursor.exec

# Reset Identity Coulmn
cursor = con.parse("alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 3)")
cursor.exec

# Insert default people rows
cursor = con.parse("INSERT INTO lcs_people(id, name, age, notes) VALUES (:id, :name, :age, :notes)")
cursor.max_array_size = 2
cursor.bind_param_array(:id, [1, 2])
cursor.bind_param_array(:name, ["Bob", "Kim"])
cursor.bind_param_array(:age, [35, 27])
cursor.bind_param_array(:notes, ["I like dogs", "I like birds"])
people_row_count = cursor.exec_array
printf " %d people rows inserted\n", people_row_count

# Insert default pet rows
cursor = con.parse("INSERT INTO lcs_pets(id, name, owner, type) VALUES (:id, :name, :owner, :type)")
cursor.max_array_size = 2
cursor.bind_param_array(:id, [1, 2])
cursor.bind_param_array(:name, ["Duke", "Pepe"])
cursor.bind_param_array(:owner, [1, 2])
cursor.bind_param_array(:type, ["dog", "bird"])
pet_row_count = cursor.exec_array
printf " %d pet rows inserted\n", pet_row_count

con.commit
Boilerplate template

The template we will be using is:

# Query all rows
require 'oci8'
connectString = ENV['db_connect']

def get_all_rows (label)
 connectString = ENV['db_connect']
 con = OCI8.new(connectString)

 # Query all rows
 statement = 'select id, name, age, notes from lcs_people order by id'
 cursor = con.parse(statement)
 cursor.exec
 printf " %s:\n", label
 cursor.fetch() {|row|
 printf "  Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
 }
 printf "\n"
end

con = OCI8.new(connectString)

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.
  • Parse the statement to create a cursor.
  • Bind the three parameters to their values.  (See the R part of this series for an explanation of bind variables)
  • Execute the statement.
  • Commit the transaction.
statement = "insert into lcs_people(name, age, notes) values (:name, :age, :notes)"
cursor = con.parse(statement)
cursor.bind_param(:name,"Sandy")
cursor.bind_param(:age,31)
cursor.bind_param(:notes,"I like horses")
cursor.exec
con.commit

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

Original Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds

New Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Sandy, Age: 31, Notes: I like horses
What is a transaction?

You’ll notice in the bullet points above, I said to commit the 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).

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:

Original Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Sandy, Age: 31, Notes: I like horses

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

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:

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

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

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

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

New Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Sandy, Age: 31, Notes: I like horses
 Id: 4, Name: Rob, Age: 37, Notes: I like snakes
 Id: 5, Name: Cheryl, Age: 41, Notes: I like monkeys
 Id: 6, Name: Suzy, Age: 31, Notes: 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
Reset the data

Now is a good time to run reset_data.rb.

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,
 ....
 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 parameters to their values.
  • Bind the id parameters defining it as a Fixnum type.
  • Execute the statement.
  • Store the returned id in new_id.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the pet data.
  • Bind the owner parameter to the new_id value, name and type to “Big Red” and “horse”.
  • Execute the statement.
  • Commit both transactions.
  • 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.
statement = "insert into lcs_people(name, age, notes) values (:name, :age, :notes) returning id into :id"
cursor = con.parse(statement)
cursor.bind_param(:name,"Sandy")
cursor.bind_param(:age,31)
cursor.bind_param(:notes,"I like horses")
cursor.bind_param(:id, Fixnum)
cursor.exec

new_id = cursor[:id]

statement = "insert into lcs_pets (name, owner, type) values (:name, :owner, :type)"
cursor = con.parse(statement)
cursor.bind_param(:name,"Big Red")
cursor.bind_param(:owner,new_id)
cursor.bind_param(:type,"horse")
cursor.exec

con.commit

printf " Our new value is: %d\n", new_id

 statement = 'select name, owner, type from lcs_pets where owner = :owner'
 cursor = con.parse(statement)
 cursor.bind_param(:owner, new_id)
 cursor.exec
 printf "\n Sandy\'s pets:\n"
 cursor.fetch() {|row|
 printf " Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2]
 }
 printf "\n"

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

Original Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds

Our new value is: 3

Sandy's pets:
 Name: Big Red, Owner: 3, Type: horse

New Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Sandy, Age: 31, Notes: 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

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

Your results should be:

Original Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Sandy, Age: 31, Notes: I like horses

Our new id is: 4 name: Sandy

New Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Sandy, Age: 31, Notes: I like horses
 Id: 4, Name: Sandy, Age: 31, Notes: 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
 Reset the data

Now is a good time to run reset_data.rb.

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 exec_array.  When 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.

  • Prepare a SQL INSERT statement, specifying the table and columns to insert the people data.
  • We need to let the driver know how many records to expect by setting the max_array_size.
  • This time, we use bind_param_array to bind an array with the data for that column to the bind variable for each column.
  • Use exec_array to execute the  statement.  This returns the number of rows inserted which we’ll store in people_row_count.
  • Commit the transaction.
  • Print out the number of records inserted.
cursor = con.parse("INSERT INTO lcs_people(name, age, notes) VALUES (:name, :age, :notes)")
cursor.max_array_size = 2
cursor.bind_param_array(:name, ["Sandy", "Suzy"])
cursor.bind_param_array(:age, [31, 29])
cursor.bind_param_array(:notes, ["I like horses", "I like rabbits"])
people_row_count = cursor.exec_array
con.commit
printf " Successfully inserted %d records\n\n", people_row_count

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

Original Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds

Successfully inserted 2 records

New Data:
 Id: 1, Name: Bob, Age: 35, Notes: I like dogs
 Id: 2, Name: Kim, Age: 27, Notes: I like birds
 Id: 3, Name: Sandy, Age: 31, Notes: I like horses
 Id: 4, Name: Suzy, Age: 29, Notes: 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 exec_array.
  • Create a large array of people.  Time the difference between looping through single inserts and using exec_array.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Insert (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.

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.

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.

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

 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

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:

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

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

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Select (cRud) using Ruby-OCI8

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

We will be using the ruby-oci8 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.  We will use this object to perform our database operations.
  2. Define the SQL SELECT statement, specifying the columns desired from the table.
  3. Create a cursor by parsing the statement.
  4. Execute the cursor.
  5. Fetch and loop through the rows.
  6. Print each row.
# Query all rows
con = OCI8.new(connectString)
statement = 'select id, name, age, notes from lcs_people'
cursor = con.parse(statement)
cursor.exec
cursor.fetch() {|row|
 print row
}

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

[#<BigDecimal:15bcb00,'0.1E1',9(18)>, "Bob", #<BigDecimal:15bc790,'0.35E2',9(18)>, "I like dogs"][#<BigDecimal:15bd1b8,'0.2E1',9(18)>, "Kim", #<BigDecimal:193bf60,'0.27E2',9(18)>, "I like birds"]

This is not real pretty, so from here on we’ll use printf.

printf "Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]

Much better:

select1pretty

Id: 1, Name: Bob, Age: 35, Notes: I like dogs
Id: 2, Name: Kim, Age: 27, Notes: I like birds

Extra Fun 1

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

Id: 2, Name: Kim, Age: 27, Notes: I like birds
Id: 1, Name: Bob, Age: 35, Notes: I like dogs
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.

statement = "select id, name, age, notes from lcs_people where name = 'Kim'"

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.

person_name = 'Kim'

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

statement = "select id, name, age, notes from lcs_people where name = '#{person_name}'"

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:

Positional:
statement = "select id, name, age, notes from lcs_people where name = :1 and age = :2"
cursor = con.parse(statement)
cursor.bind_param(1, 'Bob')
cursor.bind_param(2, 35)

statement = "select id, name, age, notes from lcs_people where name = :2 and age = :1"
cursor = con.parse(statement)
cursor.bind_param(1, 'Bob')
cursor.bind_param(2, 35)

Notice the :1 and :2 are switched in the two examples. With a positional statement, the labels do not matter, it could just as well have been :1 and :something. What matters is the first :variable in the statement will be assigned the value of the bind_param with the index of 1, the second the value of index 2 and so on.

Positional bind parameters will us an integer as the key in bind_param().

Named:
statement = "select id, name, age, notes from lcs_people where name = :name and age = :age"
cursor = con.parse(statement)
cursor.bind_param('name', 'Bob')
cursor.bind_param('age', 35)

statement = "select id, name, age, notes from lcs_people where name = :age and age = :name"
cursor = con.parse(statement)
cursor.bind_param('age', 'Bob')
cursor.bind_param('name', 35)

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

In the second example, I switched the labels in the SQL, and I also had to switch the keys in the bind_parameter calls so the correct value is still used.

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.
  2. Create the person_name variable and assign it a value of ‘Kim’.
  3. Define the SQL SELECT statement, specifying the columns desired from the table.
  4. Create a cursor by parsing the statement.
  5. Bind the value of person_name to :name.
  6. Execute the cursor.
  7. Fetch and loop through the rows.
  8. Print each row.
# Query for Kim
con = OCI8.new(connectString)

person_name = 'Kim'
statement = "select id, name, age, notes from lcs_people where name=:name"
cursor = con.parse(statement)
cursor.bind_param('name', person_name)
cursor.exec
cursor.fetch() {|row|
 printf "Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
}

This will return only the data for Kim:

Id: 2, Name: Kim, Age: 27, Notes: I like birds
Extra Fun 2

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

Id: 1, Name: Bob, Age: 35, Notes: I like dogs
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

Select (cRud) using Perl and DBD::ORACLE

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.
# Query all rows
my $con = DBI->connect( 'dbi:Oracle:', $connectString, '');
$con->{RaiseError} = 1; # set the connection to raise errors

my $sth = $con->prepare("select id, name, age, notes from lcs_people");
$sth->execute;
DBI::dump_results($sth);

$con->disconnect;

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

'1', 'Bob', '35', 'I like dogs'
'2', 'Kim', '27', 'I like birds'
2 rows
Extra Fun 1

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

'2', 'Kim', '27', 'I like birds'
'1', 'Bob', '35', 'I like dogs'
2 rows
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.

my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = 'Kim'");

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.

my $person_name = 'Kim';

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

my $sth = $con->prepare("select id, name, age, notes from lcs_people where name= '${person_name}'");

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:
my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = ? and age = ?");
$sth->bind_param(1,'Bob');
$sth->bind_param(2, 35);

my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = ? and age = ?");
$sth->bind_param(2, 35);
$sth->bind_param(1,'Bob');

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:
my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = :name and age = :age");
$sth->bind_param( ":name",'Bob');
$sth->bind_param( ":age", 35);

my $sth = $con->prepare("select id, name, age, notes from lcs_people where name = :name and age = :age");
$sth->bind_param( ":age", 35);
$sth->bind_param( ":name",'Bob');

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.
# Query for Kim
my $con = DBI->connect( 'dbi:Oracle:', $connectString, '');
$con->{RaiseError} = 1; # set the connection to raise errors

my $person_name = 'Kim';
my $sth = $con->prepare("select id, name, age, notes from lcs_people where name=:name ");
$sth->bind_param( ":name",$person_name);

$sth->execute;
DBI::dump_results($sth);

This will return only the data for Kim:

'2', 'Kim', '27', 'I like birds'
1 rows
Extra Fun 2

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

'1', 'Bob', '35', 'I like dogs'
1 rows
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