Skip to content

Commit a818f1e

Browse files
OsBlaineOraOsBlaineOra
committed
added update examples
1 parent c349a58 commit a818f1e

File tree

7 files changed

+310
-0
lines changed

7 files changed

+310
-0
lines changed

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

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/04/update-crud-using-ruby-oci8/
2+
# section titled "Extra Fun 1"
3+
# Using the base template, the example code executes a simple update using positional bind variables.
4+
5+
require 'oci8'
6+
connectString = ENV['DB_CONNECT']
7+
8+
def get_all_rows(label, data_type = 'people')
9+
connectString = ENV['DB_CONNECT']
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+
con = OCI8.new(connectString)
33+
34+
get_all_rows('Original Data')
35+
36+
statement = 'update lcs_people set notes = :notes where id = :id'
37+
cursor = con.parse(statement)
38+
cursor.bind_param(:notes, 'I like cats')
39+
cursor.bind_param(:id, 1)
40+
cursor.exec
41+
con.commit
42+
43+
get_all_rows('New Data')

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

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/04/update-crud-using-ruby-oci8/
2+
# section titled "Extra Fun 2"
3+
# Using the base template, the example code executes a simple update using positional bind variables
4+
# and displays the number of affected rows.
5+
6+
require 'oci8'
7+
connectString = ENV['DB_CONNECT']
8+
9+
def get_all_rows(label, data_type = 'people')
10+
connectString = ENV['DB_CONNECT']
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+
con = OCI8.new(connectString)
34+
35+
get_all_rows('Original Data', 'pets')
36+
37+
statement = 'update lcs_pets set owner = :newOwner where type = :type and owner != :oldOwner'
38+
cursor = con.parse(statement)
39+
cursor.bind_param(:newOwner, 2)
40+
cursor.bind_param(:type, 'bird')
41+
cursor.bind_param(:oldOwner, 2)
42+
changed = cursor.exec
43+
con.commit
44+
45+
printf "Number of rows updated: %d\n\n", changed
46+
47+
get_all_rows('New Data', 'pets')

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

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/04/update-crud-using-ruby-oci8/
2+
# section titled "Resetting the data"
3+
# The following resets the data for use with the update section
4+
# For both tables:
5+
# Table data is removed.
6+
# The identity column is set to start with the id after the starting data.
7+
# Using the executemany function an array of starting data is inserted into the table.
8+
9+
require 'oci8'
10+
connectString = ENV['DB_CONNECT']
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 3)')
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 = 2
32+
cursor.bind_param_array(:id, [1, 2])
33+
cursor.bind_param_array(:name, %w[Bob Kim])
34+
cursor.bind_param_array(:age, [35, 27])
35+
cursor.bind_param_array(:notes, ['I like dogs', 'I like birds'])
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 Pepe Princess Polly Rollo Buster Fido])
44+
cursor.bind_param_array(:owner, [1, 2, 1, 1, 1, 1, 1])
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/update/simple.rb

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/04/update-crud-using-ruby-oci8/
2+
# section titled "Simple update"
3+
# Using the base template, the example code executes a simple update using positional bind variables.
4+
5+
require 'oci8'
6+
connectString = ENV['DB_CONNECT']
7+
8+
def get_all_rows(label, data_type = 'people')
9+
connectString = ENV['DB_CONNECT']
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+
con = OCI8.new(connectString)
33+
34+
get_all_rows('Original Data')
35+
36+
statement = 'update lcs_people set age = :age where id = :id'
37+
cursor = con.parse(statement)
38+
cursor.bind_param(:age, 31)
39+
cursor.bind_param(:id, 1)
40+
cursor.exec
41+
con.commit
42+
43+
get_all_rows('New Data')

‎ruby/CRUD-examples/update/specific_where.rb

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/04/update-crud-using-ruby-oci8/
2+
# section titled "Make sure your where clause is specific"
3+
# Using the base template, the example code executes a simple update using positional bind variables.
4+
5+
require 'oci8'
6+
connectString = ENV['DB_CONNECT']
7+
8+
def get_all_rows(label, data_type = 'people')
9+
connectString = ENV['DB_CONNECT']
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+
con = OCI8.new(connectString)
33+
34+
get_all_rows('Original Data', 'pets')
35+
36+
statement = 'update lcs_pets set owner = :newOwner where owner = :oldOwner and type = :type'
37+
cursor = con.parse(statement)
38+
cursor.bind_param(:newOwner, 2)
39+
cursor.bind_param(:oldOwner, 1)
40+
cursor.bind_param(:type, 'dog')
41+
cursor.exec
42+
con.commit
43+
44+
get_all_rows('New Data', 'pets')

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

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/04/update-crud-using-ruby-oci8/
2+
# section titled "Boilerplate template"
3+
# The following code is used as the base template for the other examples.
4+
5+
require 'oci8'
6+
connectString = ENV['DB_CONNECT']
7+
8+
def get_all_rows(label, data_type = 'people')
9+
connectString = ENV['DB_CONNECT']
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+
con = OCI8.new(connectString)
33+
34+
get_all_rows('Original Data')
35+
36+
# Your code here
37+
38+
get_all_rows('New Data')

‎ruby/CRUD-examples/update/verify_number.rb

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/04/update-crud-using-ruby-oci8/
2+
# section titled "Verify the number of affected rows"
3+
# Using the base template, the example code executes a simple update using positional bind variables
4+
# and displays the number of affected rows.
5+
6+
require 'oci8'
7+
connectString = ENV['DB_CONNECT']
8+
9+
def get_all_rows(label, data_type = 'people')
10+
connectString = ENV['DB_CONNECT']
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+
con = OCI8.new(connectString)
34+
35+
get_all_rows('Original Data', 'pets')
36+
37+
statement = 'update lcs_pets set owner = :newOwner where id = :id'
38+
cursor = con.parse(statement)
39+
cursor.bind_param(:newOwner, 2)
40+
cursor.bind_param(:id, 6)
41+
changed = cursor.exec
42+
con.commit
43+
44+
printf "Number of rows updated: %d\n\n", changed
45+
46+
get_all_rows('New Data', 'pets')

0 commit comments

Comments
 (0)