Category Archives: Information

Track-a-Watt – IoT to the Database: code walkthrough

This is a companion post to my Track-a-Watt – IoT to the Database presentation.

If I missed you at GLOC 2017, you can still catch it at KScope 2017 or OpenWest 2017.

I’ve packed loads of stuff into this presentation, including: soldering (no software involved), Python, Javascript, HTML, PL/SQL and a little SQL (there has to be at least a little SQL in any application! :-)).

Even if I had a few hours of presentation time, it’d be hard to do justice to all these different scripts in their different languages, without losing lots of my audience somewhere along the way. So the presentation keeps things brief and to the point, and I will use this post to provide more depth for some of the code sections.

Python modules

sensorhistory.py

I mention that there are some names and labels used in this module that reference “5 minutes”.

I didn’t find any instances where a value for 5 minutes (300 seconds) is used in the functionality.  Five minutes is only used as labels and object names.

The declarations for these can be found on lines:

  • 103 – cumulative5mwatthr.
    A variable used to store the cumulative watts per hour readings since the timer was started.  We’ll call this total-watt-hours below.
  • 105 – fiveminutetimer.
    A variable used to store the time when the timer was initialized.  We’ll call this start-time below.
  • 119 – reset5mintimer.
    A function to reset start-time and total-watts.
  • 123 – avgwattover5min.
    A function that prints the current data and returns the calculated average watts per hour since the timer started.
  • 124 – fivetimer.
    A text label in the print statement.
  • 125 – 5mintimer and 5minwatthr
    Labels in the text returned by the __str__ function.

This is just a demo, so I didn’t rename these objects.  I only highlight these in case the names cause confusion after I change the timer to 10 seconds.

xbee.py

I only made one change in this module due to an error I received.  I have been running this project on both Windows 7 and Fedora 25 machines.  On one machine the values for p are passed in as Unicode and the other they are Strings.

The change here just checks to see if p is a String if so, convert it to Unicode otherwise accept it as is.  Thanks, Anthony Tuininga for making this clean and compact.

wattcher.py to wattcher-min.py

The original code for the Tweet-a-Watt project has some functionality that I don’t intend to use for my simple graph.  I created the wattcher-min.py module by stripping out most of these features.

Average Watts/Hour calculation

As far as I can prove with my (cough cough) math skills, the algorithm used to calculate watts per hour works for whatever time slice you want to track.

I have not gone through all of the code that leads up to this point, but as I understand it:

  • The kill-o-watt is collecting a constant stream of readings.
  • The kill-o-watt X-Bee transmits the reading to the computer every 2 seconds where the data is stored in the array, wattdata[].
  • This code calculates and stores the average watts used in the last second.
To calculate the average W/Hr during our current time slice:

  • Calculate the number of seconds since the last reading.
  • Multiply the average watts per second by the elapsed seconds then divide by 3600 (seconds in an hour).
  • Reset the last reading timer.
  • Print the data.
  • Add the calculated average W/Hr for this time slice to the running total.
Here’s a basic explanation:

When a chunk of data comes in, we calculate the average W/Hr for the first second of that chunk.  Multiply that value by the number of seconds since the previous reading.  This gives us the average W/Hr for a 2 second time slice.  If we were to collect those slices for one hour and add them together we would have X watts used in one hour.

The cumulative watts used will continue to accrue until we pass the limit of the timer we’re using to determine how often to send the data up to ORDS.

To calculate the average W/Hr during the last 10 seconds:

  • Multiply the cumulative watts used by 3600 (seconds in an hour).
  • Divide by the seconds since the last time we sent data to ORDS.
The short explanation is if we were getting a consistent reading of 5 watts per hour for every sample, every 10 seconds this calculation would come out to 5 W/Hr during the last 10 seconds.  However, it’s not likely that we will get the same 5 W/Hr every reading so this function will give us the average W/Hr during the last 10 seconds.

I can understand if you’re a bit confused at this point. There seem to be a couple extra steps here than what should be needed for my simple graph.  I had to work out a simulation in a spreadsheet before I could accept that it was working.  However, I left the calculation code alone assuming that it may be needed for some of the more advanced versions of the project.

If your math skills are better than mine and you find that my explanation is wrong or you can explain it better, please leave a comment.

Oracle Jet

The Oracle Jet graph used in the example is the basic Line with Area Chart.  I’m using the Y axis for the W/Hr data and the X axis for the timestamps.

The graph has the capability to track multiple series of data which would be useful for multiple kill-a-watts, but I’m only using one in the presentation.

The relationship between the X and Y axises is positional using the array position for the data elements in two arrays.

JavaScript

This is a typical jQuery ajax GET function.

Inside the success function:

  • Get the items array from the response.
  • Create a variable for the X-axis data.
  • Create a variable for the Y-axis data.  Since we’re only tracking one sensor we can define the name value and initialize an items array for it.
  • Loop through the response items.
  • Populate the items array for our sensor (Y axis).
  • Populate the timestamp array (X axis).
  • Set the ko.observable objects for the two axises of the graph.

Next is a short function to call getData() every 5 seconds.

HTML

We copy the HTML from the cookbook for just the graph component.

Since we’re not using the additional functionality from the Jet Cookbook example we remove the highlighted lines (14, 15).

Go try something new

The goal of this presentation is to encourage people to go out and try something a little out of their comfort zone.  If you think your soldering skills are lacking find a maker group in your area and take a class.  If you are strong in one programming language try another.

This is a great project to experiment with, there are a few different skills all mixed together, each of them is fairly close to entry level and they are popular enough that there should be a lot of help available.

As always, if you run into issues feel free to leave a comment here or hit me up on twitter and I’ll be glad to help get you going.

I plan to update this post as questions arise.  If you’d like to see it all running together catch one of my upcoming sessions.

JDBC Connection to Exadata Express Cloud Database from a Raspberry Pi

Spoiler Alert: It’s pretty much the same as any other Linux system.

The official documentation can be found on the Oracle website.

Install Linux on your Raspberry Pi

I prefer to use BerryBoot, it makes it real easy to install several flavors of Linux on my pi.  You can get it from their repo.  For this example, I’m using Raspbian.

If you plan to use SSH this is a good guide for setting up passwordless SSH access.

Java Version

Verify that you have the correct Java Version:
JDK 8 – JDK8u71 or higher
JDK 7 – JDK7u80 or higher

When this post was written, Raspbian was using an older version of Java 8 so an upgrade is needed.

There are already quite a few guides out there if you need help.  I used this one.

JCE Files

Next, we need to download and install the JCE Unlimited Strength Jurisdiction Policy Files.

After you download the .zip file, extract the files and place the two .jar files (local_policy.jar & US_export_policy.jar) in
<java-home>/lib/security.

<java-home> refers the home for the JRE not the JDK.  My full path is:
/opt/java/jdk1.8.0_121/jre/lib/security/

Oracle Exadata Express Cloud Database

Now that Linux and Java are setup, it’s time to setup your Oracle Exadata Express Cloud Database.

Log into your Service Console and find the Client Access section.

If client access is not already enabled, you will see a link to Enable Clent Access.  Click that link and follow the instructions.  Once it’s done it will look like this.

Click on Download Client Credentials and you’ll get this popup:

Enter and confirm a password.  This is the password that your JDBC client will use to access your credentials.

Click download then extract the file.  I extracted mine into my home directory in a directory named EE_Credentials.
/home/pi/EE_Credentials

Create a Test Class

Now we have everything in place we can try it out.

In order to ensure that nothing else is interfering with our test, let’s compile a simple test class.

  1. Replace the strings for your user and password.
  2. Save this into a file called testJDBC.java.
  3. Copy the oracle driver into the same directory.  We’ll manually set the classpath to keep our test self-contained.
  4. Compile the class.
    javac testJDBC.java

Required System Properties

The last thing we need to do is set some system properties using the -D option for Java.

The parameters we’ll be using are:

If you’re using JDK7 you’ll also need to include

Remember to replace YourPassword with the password you set above and /home/pi/EE_Credentials with the path you extracted the credentials file into.

To run the test: (all one line)

If you see the error:
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier “dbaccess”
Make sure the path in the properties is where you extracted the credentials file to.  You should see a tnsnames.ora file in there.

Use Environment Variables

If everything worked then we can make it a bit easier by moving those properties to an environment variable.

Then run it with:

Now we can be confident that we can connect to and query from the Oracle Exadata Express Cloud Database.

To use the connection in your other applications, follow these instructions and read the documentation on setting the Java properties.

For example:
If you’re using Liquibase, set the environment variable like in the example above.  If you’re using OpenHab the environment variable would be named EXTRA_JAVA_OPTS.

Connecting to Oracle Exadata Express Cloud Database is easy and it provides a secure reliable database to support your applications.

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.

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

Boilerplate template

The template we will be using is:

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

Reset the data

First, let’s run reset_data.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.
When I run this code in my Ruby session, I see:

Extra Fun 1

Delete all the birds.

Your results should be:

Answer
Reset the data

Now is a good time to run reset_data.rb.

Boilerplate change

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

Deleting records referenced by Foreign Keys

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

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

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

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

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

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

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

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

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

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

  • Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob).  Updating is covered in the U part of this series.
  • 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.
When I run this code in my Ruby session, I see:

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

Extra Fun 2

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

Let’s fix our data.

Your results should be:

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

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

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.

Add this function to the top of your file.

Resetting the data

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

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

Boilerplate template

The template we will be using is:

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

Reset the data

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

Simple delete

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

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

Extra Fun 1

Delete all the birds .

Your results should be:

Answer
Reset the data

Now is a good time to run reset_data.perl.

Boilerplate change

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

Deleting records referenced by Foreign Keys

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

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

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

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

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

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

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

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

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

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

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

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

Extra Fun 2

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

Let’s fix our data.

Your results should be:

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

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

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.

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

Boilerplate template

The template we will be using is:

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

Simple update

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

  • Prepare a SQL UPDATE statement, changing age to 31 for the record with an id of 1.
  • 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.
When I run this code in my Ruby session, I see:

Extra Fun 1

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

Your results should be:

Answer
Reset the data

Now is a good time to run reset_data.rb.

Boilerplate change

Change the boilerplate get_all_rows statements to get pet data.

Make sure your where clause is specific

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

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

  • Prepare a SQL UPDATE statement, changing owner to 2 for the record with an owner of 1 and a type of ‘dog’.
  • Parse the statement to create a cursor.
  • Bind the newOwner, oldOwner and type values.
  • Execute the statement.
  • Commit the transaction.
When I run this code in my Ruby session, I see:

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

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

It also helps to…

Verify the number of affected rows

Now let’s give Buster back to Bob.  This time we will use the unique id column and we will print out the number of rows affected using 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.
When I run this code in my Ruby session, I see:

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:

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.

Add this function to the top of your file.

Resetting the data

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

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

Boilerplate template

The template we will be using is:

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

Simple update

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

  • Prepare a SQL UPDATE statement, changing age to 31 for the record with an id of 1.
  • Bind the age and id values.  (See the R part of this series for an explanation of bind variables.)
  • Execute the statement.
When I run this code in my Perl session, I see:

Extra Fun 1

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

Your results should be:

Answer
Reset the data

Now is a good time to run reset_data.perl.

Boilerplate change

Change the boilerplate get_all_rows statements to get pet data.

Make sure your where clause is specific

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

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

  • Prepare a SQL UPDATE statement, changing owner to 2 for the record with an owner of 1 and a type of ‘dog’.
  • Bind the newOwner, oldOwner and type values.
  • Execute the statement.
When I run this code in my Perl session, I see:

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

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

It also helps to…

Verify the number of affected rows

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

  • Prepare a SQL UPDATE statement.
  • Bind the newOwner to 1 and id to 6.
  • Execute the statement.
  • Get the number of changed rows.
  • Print the number of changed rows.
When I run this code in my Perl session, I see:

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

Extra Fun 2

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

Your results should be:

Answer
Some other things you could try
  • Change multiple column values
  • Perform an update that changes all rows, if the row count is greater than 2, rollback the change

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

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.

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.

Boilerplate template

The template we will be using is:

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

Simple insert

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

  • Prepare a SQL INSERT statement, specifying the table and columns to insert the data.
  • 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.
When I run this code in my Ruby session, I see:

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:

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:

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.

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

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

Extra Fun 3

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

Your results should be:

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

Answer
 Reset the data

Now is a good time to run reset_data.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.
When I run this code in my Ruby session, I see:

Some things you could try
  • Loop through an array of people and insert each one returning its id.  Using that id add multiple pets with 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

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

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

select1pretty

Extra Fun 1

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

Answer
Select specific rows

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

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

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

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