Insert (Crud) Using Ruby-OCI8
Today, we will be using the ruby-oci8 driver to create some data in our database tables. Follow along to learn how you can use Ruby do set up your CRUD operations.
Join the DZone community and get the full member experience.
Join For FreeIn 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.
Note: Please review all example code and only run it if you are sure it will not cause any problems with your system.
Helper Function
I will be using a helper function get_all_rows(). This is a select statement used to verify that the inserts worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.
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 and 2
1. Insert more than one row.
Using data for 'Rob', 37, 'I like snakes' and 'Cheryl', 41, 'I like monkeys' Your results should be:
Original Data:
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
statement = "insert into lcs_people(name, age, notes) values (:name, :age, :notes)"
cursor = con.parse(statement)
cursor.bind_param(:name,"Rob")
cursor.bind_param(:age,37)
cursor.bind_param(:notes,"I like snakes")
cursor.exec
cursor.bind_param(:name,"Cheryl")
cursor.bind_param(:age,41)
cursor.bind_param(:notes,"I like monkeys")
cursor.exec
con.commit
This method will work for inserting many rows at once, but there is a better way. I cover that below.
2. Verify that a second connection cannot see your changes till after the commit.
Using data for 'Suzy', 31, 'I like rabbits' and assuming that you did the previous exercise your results should be:
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
I modified the helper function and the get_all_rows calls in the template code to make it a little easier. If you chose to do this, please revert the template code after this exercise.
def get_all_rows (label, con)
# 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)
con2 = OCI8.new(connectString)
get_all_rows('Original Data', con)
statement = "insert into lcs_people(name, age, notes) values (:name, :age, :notes)"
cursor = con.parse(statement)
cursor.bind_param(:name,"Suzy")
cursor.bind_param(:age,31)
cursor.bind_param(:notes,"I like rabbits")
cursor.exec
get_all_rows('New connection after insert', con2)
get_all_rows('Same connection', con)
con.commit
get_all_rows('New connection after commit', con2)
get_all_rows('New Data', con)
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,
....
Returning Data After an Insert
We could run an insert then select the value back using the name. But if the name is not unique we'll have a problem. This is where the RETURNING clause is helpful.
We will perform an insert that adds a single record into the lcs_people table. Then using the returned id we will add a pet. These are the steps performed in the code snippet below.
- Prepare a SQL INSERT statement, specifying the table and columns to insert the people data.
- Bind the three 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 three .
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
statement = "insert into lcs_people(name, age, notes) values (:name, :age, :notes) returning id, name into :id, :name_out"
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.bind_param(:name_out, String)
cursor.exec
new_id = cursor[:id]
name_out = cursor[:name_out]
con.commit
printf " Our new id is: %d name: %s\n\n", new_id, name_out
Reset the Data
Now is a good time to run reset_data.rb.
Insert More Than One Row
As mentioned above, when you want to insert multiple rows, running multiple insert statements is inefficient and makes multiple trips to the database so instead, we will use 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.
Published at DZone with permission of Blaine Carter, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments