Skip to content

Commit a67c6fb

Browse files
gvenzlgvenzl
authored
Merge pull request oracle-samples#17 from OsBlaineOra/ruby-crud
Add Ruby CRUD operations examples.
2 parents 7654de1 + e60286a commit a67c6fb

31 files changed

+1188
-0
lines changed

‎ruby/CRUD-examples/README.md

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
The scripts in this directory follow the [BASIC CRUD OPERATIONS USING RUBY-OCI8](https://learncodeshare.net/2016/08/26/basic-crud-operations-using-ruby-oci8/) tutorial found on [LearnCodeShare.net](https://learncodeshare.net).

‎ruby/CRUD-examples/createDBObjects.sql

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
# The following creates and populates the tables used for the tutorial
2+
# These scripts use identity columns available in Oracle 12.1. If you are using an older version you will need
3+
# to add your own trigger/sequence functionality to generate the id values.
4+
# code Sample from the tutorial at https://learncodeshare.net/2016/08/26/basic-crud-operations-using-ruby-oci8/
5+
6+
CREATE TABLE LCS_PEOPLE (
7+
id NUMBER GENERATED BY DEFAULT AS identity,
8+
name VARCHAR2(20),
9+
age NUMBER,
10+
notes VARCHAR2(100)
11+
)
12+
/
13+
14+
ALTER TABLE LCS_PEOPLE
15+
ADD CONSTRAINT PK_LCS_PEOPLE PRIMARY KEY ("ID")
16+
/
17+
18+
CREATE TABLE LCS_PETS (
19+
id NUMBER GENERATED BY DEFAULT AS IDENTITY,
20+
name VARCHAR2(20),
21+
owner NUMBER,
22+
type VARCHAR2(100)
23+
)
24+
/
25+
26+
ALTER TABLE LCS_PETS ADD CONSTRAINT PK_LCS_PETS PRIMARY KEY ("ID")
27+
/
28+
29+
ALTER TABLE LCS_PETS ADD CONSTRAINT FK_LCS_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "LCS_PEOPLE" ("ID")
30+
/
31+
32+
INSERT INTO LCS_PEOPLE (name, age, notes)
33+
VALUES ('Bob', 35, 'I like dogs')
34+
/
35+
36+
INSERT INTO LCS_PEOPLE (name, age, notes)
37+
VALUES ('Kim', 27, 'I like birds')
38+
/
39+
40+
INSERT INTO LCS_PETS (name, owner, type)
41+
VALUES ('Duke', 1, 'dog')
42+
/
43+
44+
INSERT INTO LCS_PETS (name, owner, type)
45+
VALUES ('Pepe', 2, 'bird')
46+
/
47+
48+
COMMIT
49+
/

‎ruby/CRUD-examples/delete/extra_fun_1.rb

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
# The example code below executes a simple delete using named bind variables.
2+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
3+
# section titled "Extra Fun 1"
4+
5+
require 'oci8'
6+
7+
def get_all_rows(label, data_type = 'people')
8+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
9+
con = OCI8.new(connectString)
10+
11+
# Query all rows
12+
statement = 'select id, name, age, notes from lcs_people order by id'
13+
14+
if data_type == 'pets'
15+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
16+
end
17+
18+
cursor = con.parse(statement)
19+
cursor.exec
20+
printf " %s:\n", label
21+
cursor.fetch do |row|
22+
if data_type == 'people'
23+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
24+
else
25+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
26+
end
27+
end
28+
printf "\n"
29+
end
30+
31+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
32+
con = OCI8.new(connectString)
33+
34+
get_all_rows('Original Data', 'pets')
35+
36+
# Example code showing a simple delete using named bind variables.
37+
statement = 'delete from lcs_pets where type = :type'
38+
cursor = con.parse(statement)
39+
cursor.bind_param(:type, 'bird')
40+
cursor.exec
41+
con.commit
42+
# End Example
43+
44+
get_all_rows('New Data', 'pets')

‎ruby/CRUD-examples/delete/extra_fun_2.rb

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
# The example code below executes two simple deletes using named bind variables.
2+
# The child records are removed, followed by the parent record.
3+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
4+
# section titled "Extra Fun 2"
5+
6+
require 'oci8'
7+
8+
def get_all_rows(label, data_type = 'people')
9+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
10+
con = OCI8.new(connectString)
11+
12+
# Query all rows
13+
statement = 'select id, name, age, notes from lcs_people order by id'
14+
15+
if data_type == 'pets'
16+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
17+
end
18+
19+
cursor = con.parse(statement)
20+
cursor.exec
21+
printf " %s:\n", label
22+
cursor.fetch do |row|
23+
if data_type == 'people'
24+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
25+
else
26+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
27+
end
28+
end
29+
printf "\n"
30+
end
31+
32+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
33+
con = OCI8.new(connectString)
34+
35+
get_all_rows('Original People Data', 'people')
36+
get_all_rows('Original Pet Data', 'pets')
37+
38+
# Example code showing two simple deletes using named bind variables.
39+
statement = 'delete from lcs_pets where owner = :owner'
40+
cursor = con.parse(statement)
41+
cursor.bind_param(:owner, 5)
42+
cursor.exec
43+
44+
statement = 'delete from lcs_people where id = :id'
45+
cursor = con.parse(statement)
46+
cursor.bind_param(:id, 5)
47+
cursor.exec
48+
con.commit
49+
# End Example
50+
51+
get_all_rows('New People Data', 'people')
52+
get_all_rows('New Pet Data', 'pets')

‎ruby/CRUD-examples/delete/foreign_keys.rb

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
# The example code below executes a simple delete using named bind variables.
2+
# When following the tutorial with default data this section intentionally throws an error
3+
# to demonstrate foreign key functionality.
4+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
5+
# section titled "Deleting records referenced by Foreign Keys" 1st example
6+
7+
require 'oci8'
8+
9+
def get_all_rows(label, data_type = 'people')
10+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
11+
con = OCI8.new(connectString)
12+
13+
# Query all rows
14+
statement = 'select id, name, age, notes from lcs_people order by id'
15+
16+
if data_type == 'pets'
17+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
18+
end
19+
20+
cursor = con.parse(statement)
21+
cursor.exec
22+
printf " %s:\n", label
23+
cursor.fetch do |row|
24+
if data_type == 'people'
25+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
26+
else
27+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
28+
end
29+
end
30+
printf "\n"
31+
end
32+
33+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
34+
con = OCI8.new(connectString)
35+
36+
get_all_rows('Original People Data', 'people')
37+
get_all_rows('Original Pet Data', 'pets')
38+
39+
# Example code showing a simple delete using named bind variables.
40+
# The FK will cause this to throw an error.
41+
statement = 'delete from lcs_people where id = :id'
42+
cursor = con.parse(statement)
43+
cursor.bind_param(:id, 1)
44+
cursor.exec
45+
con.commit
46+
# End Example
47+
48+
get_all_rows('New People Data', 'people')
49+
get_all_rows('New Pet Data', 'pets')

‎ruby/CRUD-examples/delete/handle_pets.rb

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,54 @@
1+
# The example code below executes two simple deletes using named bind variables.
2+
# The child records are updated to a different parrent,
3+
# followed by deleting the original parent record.
4+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
5+
# section titled "Deleting records referenced by Foreign Keys" 2nd example
6+
7+
require 'oci8'
8+
9+
def get_all_rows(label, data_type = 'people')
10+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
11+
con = OCI8.new(connectString)
12+
13+
# Query all rows
14+
statement = 'select id, name, age, notes from lcs_people order by id'
15+
16+
if data_type == 'pets'
17+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
18+
end
19+
20+
cursor = con.parse(statement)
21+
cursor.exec
22+
printf " %s:\n", label
23+
cursor.fetch do |row|
24+
if data_type == 'people'
25+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
26+
else
27+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
28+
end
29+
end
30+
printf "\n"
31+
end
32+
33+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
34+
con = OCI8.new(connectString)
35+
36+
get_all_rows('Original People Data', 'people')
37+
get_all_rows('Original Pet Data', 'pets')
38+
39+
# Example code showing how to update existing child records before deleting the parent.
40+
statement = 'update lcs_pets set owner = :newOwner where owner = :oldOwner'
41+
cursor = con.parse(statement)
42+
cursor.bind_param(:newOwner, 2)
43+
cursor.bind_param(:oldOwner, 1)
44+
cursor.exec
45+
46+
statement = 'delete from lcs_people where id = :id'
47+
cursor = con.parse(statement)
48+
cursor.bind_param(:id, 1)
49+
cursor.exec
50+
con.commit
51+
# End Example
52+
53+
get_all_rows('New People Data', 'people')
54+
get_all_rows('New Pet Data', 'pets')

‎ruby/CRUD-examples/delete/reset_data.rb

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
# The following resets the data for use with the update section
2+
# For both tables:
3+
# Table data is removed.
4+
# The identity column is set to start with the id after the starting data.
5+
# Using the executemany function an array of starting data is inserted into the table.
6+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
7+
# section titled "Resetting the data"
8+
9+
require 'oci8'
10+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
11+
con = OCI8.new(connectString)
12+
13+
# Delete rows
14+
cursor = con.parse('delete from lcs_pets')
15+
cursor.exec
16+
17+
# Reset Identity Coulmn
18+
cursor = con.parse('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 8)')
19+
cursor.exec
20+
21+
# Delete rows
22+
cursor = con.parse('delete from lcs_people')
23+
cursor.exec
24+
25+
# Reset Identity Coulmn
26+
cursor = con.parse('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 8)')
27+
cursor.exec
28+
29+
# Insert default people rows
30+
cursor = con.parse('INSERT INTO lcs_people(id, name, age, notes) VALUES (:id, :name, :age, :notes)')
31+
cursor.max_array_size = 7
32+
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
33+
cursor.bind_param_array(:name, %w[Bob Kim Cheryl Bob Stacey Pete Pat])
34+
cursor.bind_param_array(:age, [35, 27, 23, 27, 45, 23, 36])
35+
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'])
36+
people_row_count = cursor.exec_array
37+
printf " %d people rows inserted\n", people_row_count
38+
39+
# Insert default pet rows
40+
cursor = con.parse('INSERT INTO lcs_pets(id, name, owner, type) VALUES (:id, :name, :owner, :type)')
41+
cursor.max_array_size = 7
42+
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
43+
cursor.bind_param_array(:name, %w[Duke Dragon Sneaky Red Red Buster Fido])
44+
cursor.bind_param_array(:owner, [1, 2, 5, 2, 3, 1, 7])
45+
cursor.bind_param_array(:type, %w[dog bird snake bird horse dog cat])
46+
pet_row_count = cursor.exec_array
47+
printf " %d pet rows inserted\n", pet_row_count
48+
49+
con.commit

‎ruby/CRUD-examples/delete/simple.rb

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
# The example code below executes a simple delete using named bind variables.
2+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
3+
# section titled "Simple delete"
4+
5+
require 'oci8'
6+
7+
def get_all_rows(label, data_type = 'people')
8+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
9+
con = OCI8.new(connectString)
10+
11+
# Query all rows
12+
statement = 'select id, name, age, notes from lcs_people order by id'
13+
14+
if data_type == 'pets'
15+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
16+
end
17+
18+
cursor = con.parse(statement)
19+
cursor.exec
20+
printf " %s:\n", label
21+
cursor.fetch do |row|
22+
if data_type == 'people'
23+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
24+
else
25+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
26+
end
27+
end
28+
printf "\n"
29+
end
30+
31+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
32+
con = OCI8.new(connectString)
33+
34+
get_all_rows('Original Data', 'pets')
35+
36+
# Example code showing a simple delete using named bind variables.
37+
statement = 'delete from lcs_pets where id = :id'
38+
cursor = con.parse(statement)
39+
cursor.bind_param(:id, 1)
40+
cursor.exec
41+
con.commit
42+
# End Example
43+
44+
get_all_rows('New Data', 'pets')

‎ruby/CRUD-examples/delete/template.rb

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
# The following code is used as the base template for the other examples.
2+
# It is intended to be helper code not part of the examples.
3+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
4+
# section titled "Boilerplate template"
5+
6+
require 'oci8'
7+
8+
def get_all_rows(label, data_type = 'people')
9+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
10+
con = OCI8.new(connectString)
11+
12+
# Query all rows
13+
statement = 'select id, name, age, notes from lcs_people order by id'
14+
15+
if data_type == 'pets'
16+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
17+
end
18+
19+
cursor = con.parse(statement)
20+
cursor.exec
21+
printf " %s:\n", label
22+
cursor.fetch do |row|
23+
if data_type == 'people'
24+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
25+
else
26+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
27+
end
28+
end
29+
printf "\n"
30+
end
31+
32+
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
33+
con = OCI8.new(connectString)
34+
35+
get_all_rows('Original Data', 'pets')
36+
37+
# Your code here
38+
39+
get_all_rows('New Data', 'pets')

‎ruby/CRUD-examples/dropDBObjects.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
# The following drops the tables created for the tutorial
2+
# code Sample from the tutorial at https://learncodeshare.net/2016/08/26/basic-crud-operations-using-ruby-oci8/
3+
4+
drop table LCS_PETS
5+
/
6+
7+
drop table LCS_PEOPLE
8+
/

0 commit comments

Comments
 (0)