From your terminal window, execute the activerecord.rb script by executing the following command:

The output is shown in the screenshot.

The contents of the activerecord.rb file is as follows:

# activerecord.rb: Using ActiveRecord, the ORM module
require 'config.rb'
require 'rubygems'
require 'active_record'

# Establish a connection to Oracle
ActiveRecord::Base.establish_connection(
        :adapter => "oracle_enhanced",
        :database => DB_SERVER,
        :username => DB_USER,
        :password => DB_PASSWORD )

# Prefix for id columns
ActiveRecord::Base.primary_key_prefix_type = :table_name_with_underscore

# Define the Classes, they are mapped to table countries and regions
class Country < ActiveRecord::Base
     belongs_to :region
end
class Region < ActiveRecord::Base
     has_many :countries
end

# Enjoy the automatic Object-Relation Mapping
cty = Country.find('CN')
puts 'CN refers to [' + cty.country_name + ']'
puts '[' + cty.country_name + '] is in [' + cty.region.region_name + ']'
puts

rgn = Region.find(cty.region.region_id)
puts 'Countries in the same region with [' + cty.country_name + ']'
rgn.countries.each { |country| puts ' - ' + country.country_name }

puts '-'*80

You will create a most basic Rails application named holapp. From your terminal window, execute the following command:

rails holapp

A set of create statements will appear, as shown in the screenshot below. Then execute the following commands:

cd holapp

gedit config/database.yml

Replace the development section in the file config/database.yml with the following content and then save and close the file.

In a separate terminal window, start the web server that comes with Rails:

To do so, make sure you have followed the steps above in "Connecting Ruby on Rails to Your Database", then from your terminal window, execute the following commands:

cd holapp

ruby script/generate scaffold --skip-migration employee employee_id:integer first_name:string last_name:string email:string phone_number:string hire_date:date job_id:string salary:float commission_pct:float manager_id:integer department_id:integer

Note: The above command, beginning with ruby script/generate scaffold is a single continuous command.

Note: The --skip-migration syntax prevents scaffold from creating scripts that would create database tables. Since we are using an existing database table, we do not want database creation scripts ("migrations") for this model.

Before you use AJAX, it's easiest to start with a non-AJAX "unfinished" list. You need to update your database table to know whether an article is finished. Articles will start unfinished; you'll have a button to mark them finished. You'll have in your layout a list of all unfinished articles for easy access. Perform the following steps:

Add a boolean column is_finished. From a terminal window in the holapp directory, execute the following command:

Then you need to mark the existing articles as unfinished. From your terminal window, execute the following command:

Change the following line:

  add_column :articles, :is_finished, :boolean

to

  add_column :articles, :is_finished, :boolean, :default => false
  Article.reset_column_information
  Article.find(:all).each do |a|
    a.is_finished = false
  end

Now you will update the database. From your terminal window, enter the following command:

Next, you want to add a "Mark Finished" button which will call a new mark_finished action.. From your terminal window, enter the following command:

Add the following line after <%= link_to 'Back', articles_path %>.

<%= button_to "Mark Finished", :action => :mark_finished, :id  => @article.id %>

Save and close the file.

You need to update the layout to show links to unfinished articles. You should keep this code separate from the main layout. You will make a new "partial template" (which starts with "_" (underscore) character to show it's not a full template). It will look up articles with is_finished false and link to them. From your terminal window, execute the following commands:

cd

cd holapp/app/views/articles

gedit _unfinished.html.erb

An empty file opens.

Add the following code to the file:

Save and close the file.

Next, you need to refer to the new "partial template" in your layout so all the pages will show it. From your terminal window, execute the following commands:

cd

gedit holapp/app/views/layouts/articles.html.erb

Put the following code right after <p style="color: green"><%= flash[:notice] %></p>

Save and close the file.

Next you need to code the mark_finished action that the new button will use. From your terminal window, execute the following command:

Scroll down to the bottom of the file. Right before the last 'end', add the following:

Save and close the file.

You have updated the database, added your button and action and added your list to the layout. You can test this functionality now. Enter the following URL in your browser:

Notice that the Unfinished articles are listed at the top of the page. Select the Boning a Duck link.

Click the Mark Finished button.

Notice that the Boning a Duck has been removed from the list of Unfinished Articles. Select Back.

At this point you are ready to try AJAX! You want change the application so that when you hit the button, your window changes automatically, without repainting the screen. To do that, you want the "Mark Finished" button to send an AJAX command to do the mark_finished action, then send back a command to update the "Finshed articles" header. You will first change the button to a remote (AJAX) form. From your terminal window, execute the following:

Change the following statement from:

to

Save and close the file.

When you hit the button, the application will still do its update, but instead of sending a whole page, you need to send back some JavaScript to update just the unfinished list.  With a remote javascript template, Rails can generate that JavaScript for you. From your terminal window, execute the following to create a new file:

cd

cd holapp/app/views/articles

gedit mark_finished.js.rjs

An empty file opens.

Enter the following line:

Save and close the file.

For this to work, there has to be some default JavaScript subroutines in all pages. You need to edit your layout. From your terminal window, execute the following to open the articles.html.erb file:

cd

gedit holapp/app/views/layouts/articles.html.erb

After  <%= stylesheet_link_tag 'scaffold' %> add the following line:

Save and close the file.

Next, you need to update the mark_finished action to use your new remote template, which it knows has same name as the action. From your terminal window, execute the following to edit the articles_controller.rb file:

cd

Change the following from:

to

Save and close the file.

You have switched to using an AJAX request form and to send Javascript back which will update the list. Enter the following URL in your browser:

Create a new article. Select New article.

Enter Gravity for Title, select Julia Child for Author, enter Gravity makes cakes fall for Abstract and click Create.

Notice that Gravity is listed in the list of Unfinished Articles. Select the Gravity link.

Click Mark Finished.

Gravity disappears from the list immediately. The full page is not redrawn. This is the power of AJAX. On a simple screen like this the difference may not be very obvious, since the page your web server is sending is small. But on more complicated screens, with images, Flash, and/or lots of data, it can make a huge difference that, with AJAX, the server sends only the necessary changes. Then you'll really appreciate Rails' AJAX support!

From a terminal window, execute the connect.rb script by executing the following commands:

You should see the above message if the connection is successful, or an error if it was not.

The contents of the connect.rb file is as follows:

# connect.rb: Create connections to Oracle
require 'config.rb'

begin
	# login as normal user
	conn1 = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)
	puts "login as normal user succeeded."
	conn1.logoff
	puts "logoff succeeded."	
	puts

	# login as DBA
	conn2 = OCI8.new('sys', 'oracle', DB_SERVER, :SYSDBA)
	puts "login with DBA privilege succeeded."
	conn2.logoff
	puts "logoff succeeded."

rescue OCIError
	# display the error message
	puts "OCIError occured!"
	puts "Code: " + $!.code.to_s
	puts "Desc: " + $!.message

end

puts '-'*80

The commands that will be included in the rest of your scripts throughout this tutorial are as follows:

Create a cursor, prepare to execute SQL statement and return the instance of OCI8::Cursor.

Execute the SQL statement. The type of return value depends on the type of SQL statement. When bindvars are specified, they are bound as bind variables before execution.

From your terminal window, execute the fetch.rb script by executing the following command:

The output is shown in the screenshot.

The contents of the fetch.rb file is as follows:

#fetch.rb: Fetch data from database
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# parse and exec the statement
cursor = conn.parse("select * from regions")
cursor.exec

# output column names
puts cursor.getColNames.join(",")

# output rows
while r = cursor.fetch 
	puts r.join(",")
end

# close the cursor and logoff
cursor.close
conn.logoff
puts '-'*80

There is another way to write the same code that is more specific to ruby. From your terminal window, execute the fetch_r.rb script by executing the following command:

The output is shown in the screenshot. 

The contents of the fetch_r.rb file is as follows:

# fetch_r.rb: Fetch in a more ruby-like way
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# Fetch and display the rows in a block
nrow = conn.exec("select * from regions") do |r|
	puts r.join(",")
end

# Display count of rows
puts ' '*30 + nrow.to_s + " rows were fetched."

conn.logoff
puts '-'*80

Prefeching helps performance when many rows are fetched. From your terminal window, execute the prefetch.rb script by executing the following command:

The output is shown in the screenshot. 

The contents of the prefetch.rb file is as follows:

# prefetch.rb: Prefetch data
require 'config.rb'

SELECT_STATEMENT = "select a.first_name, a.last_name, b.first_name, b.last_name,
   a.salary - b.salary salarydiff 
from employees a, employees b 
where b.id > a.id"

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# Execute statement with different prefetch sizes
[nil,100].each do |prefetchrows|

  cursor = conn.parse(SELECT_STATEMENT)

  if prefetchrows
    cursor.prefetch_rows = prefetchrows
  else
    prefetchrows = "default"
  end

  puts "Executing with prefetch rows = #{prefetchrows}"

  time1 = Time.now

  cursor.exec

  while r = cursor.fetch()
    # puts r.join(",")
  end

  # Display count of rows
  puts ' '*30 + cursor.row_count.to_s + " rows were fetched."

  time2 = Time.now
  puts "Time cost: " + (time2-time1).to_s
  puts

end

conn.logoff
puts '-'*80

Bind variables explicitly. When key is number, it binds by position, which starts from 1. When key is string, it binds by the name of placeholder.

Can also bind variables via bindvars.

From your terminal window, execute the bind.rb script by executing the following command:

The output is shown in the screenshot.

The contents of the bind.rb file is as follows:

# bind.rb: How to bind variables
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# Prepare the data, also display what's hard-coded statement
conn.exec("DELETE FROM test_bind")
conn.exec("INSERT INTO test_bind VALUES(1, 'Unknown')")
conn.exec("INSERT INTO test_bind VALUES(2, 'Unknown')")
conn.exec("INSERT INTO test_bind VALUES(3, 'Unknown')")

# Now update the data using bind variables.
cursor = conn.parse("UPDATE test_bind SET name = :name WHERE id = :id")
cursor.bind_param(1,nil,String,100) #  Or: cursor.bind_param(1, ' '*100)
cursor.bind_param(2,Fixnum)         #  Or: cursor.bind_param(2, id) 

id = 1
['China', 'Korea', 'Japan'].each { |country|
	cursor[1] = country
	cursor[2] = id
        cursor.exec
	id = id + 1
}

# Fetch back the updated data
conn.exec("SELECT * FROM test_bind").fetch do |row|
	puts row.join(',')
end

conn.logoff

puts '-'*80

To test the performance improvement by using bind variables, execute the bind_perf_test.rb script by executing the following command:

The output is shown in the screenshot. 

The contents of the bind_perf_test.rb file is as follows:

# bind_perf_test.rb: Performance test for binding variables
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

# Not using bind varables.
puts "Fetching result, not using bind variables:"
time1 = Time.now
(100..200).each { |id|
	cursor = conn.parse("SELECT first_name FROM employees WHERE id = #{id}")
        cursor.exec
	#puts id.to_s + " --> " + cursor.fetch[0]
}
time2 = Time.now
puts "Time cost: " + (time2-time1).to_s
puts

# Using bind varables.
puts "Fetching result, using bind variables:"

time1 = Time.now
cursor = conn.parse("SELECT first_name FROM employees WHERE id = :id")
cursor.bind_param(1, Fixnum)

(100..200).each { |id|    
	cursor[1] = id
	cursor.exec
	#puts id.to_s + " --> " + cursor.fetch[0]
}
time2 = Time.now
puts "Time cost: " + (time2-time1).to_s

# End of the test
conn.logoff

puts '-'*80

Use this method within parse and exec. pos starts from 1. length is used when type is String.

From your terminal window, execute the define.rb script by executing the following command:

The output is shown in the screenshot.

You can see the difference between the two blocks of output.

The contents of the define.rb file is as follows:

# define.rb: Define output columns
require 'config.rb'


# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)


# Prepare the data
conn.exec("DELETE FROM test_define");
conn.exec("INSERT INTO test_define VALUES(1,'Scott Tiger', SYSDATE, SYSTIMESTAMP)")

# Define to fetch Date and Time
cursor = conn.parse("SELECT name,birthdate,lastvisit FROM test_define WHERE id = :id")
cursor.bind_param(1, 1)
cursor.define(1, String, 100)
cursor.define(2, Date)
cursor.define(3, Time)
cursor.exec

while r = cursor.fetch
	puts r.join("\n")
end

puts 

# Define to fetch Date and Time as String
#conn.exec("ALTER SESSION SET nls_territory='TAIWAN' nls_language='TRADITIONAL CHINESE'")
cursor = conn.parse("SELECT name,birthdate,lastvisit FROM test_define WHERE id = :id")
cursor.bind_param(1, 1)
cursor.define(1, String, 100)
cursor.define(2, String, 100)
cursor.define(3, String, 100)
cursor.exec

while r = cursor.fetch
	puts r.join("\n")
end

conn.logoff
puts '-'*80

Get/Set the state of the autocommit mode. The default value is false (remember: not committed transactions are rollbacked when logoff). If true, the transaction is committed automatically whenever executing insert/update/delete statements.

From your terminal window, execute the transaction.rb script by executing the following command:

This script updates a row using connection conn1. In Oracle, new data is only visible in the original database session until it is committed. The output is shown in the screenshot.

In this case, connection conn2 doesn’t know what happened in conn1’s not-committed transactions.

The contents of the transaction.rb file is as follows:

# transaction.rb: How to use transactions
require 'config.rb'

# Create connections to Oracle
conn1 = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)
conn2 = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

conn1.exec("DELETE FROM test_transaction")
conn1.exec("INSERT INTO test_transaction VALUES(1, 'old value')")
conn1.commit

#conn1.autocommit = true
puts "OCI8.autocommit = " + conn1.autocommit.to_s
puts "conn1 updated the name to 'something new'";
conn1.exec("UPDATE test_transaction SET name = 'something new' WHERE id = 1");
#conn1.commit

puts "conn2 got the name as '" + 
		conn2.exec('SELECT name FROM test_transaction WHERE id = 1').fetch[0] + "'"

conn1.logoff
conn2.logoff

puts '-'*80

Update the transaction.rb script and uncomment conn1.autocommit = true and save the file.

Execute the script again using the following command:

Now conn2 knows there is something new. The output is shown in the screenshot.

Committing each row individually causes extra load on the server. You can compare the performance difference between committing each row individually versus at the end of the transaction. To test the difference, execute the trans_perf_test.rb script using the following command:

Notice that the second number is smaller than the first one. The output is shown in the screenshot.

The contents of the trans_perf_test.rb file is as follows:

# trans_perf_test.rb: Performance test for transactions
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)
conn.exec("DELETE FROM test_transaction")
conn.commit

# Commit on each time
conn.autocommit = true
time1 = Time.now
300.times{
	conn.exec("INSERT INTO test_transaction VALUES(1, 'something')")
}
time2 = Time.now

# Commit on the last step
conn.autocommit = false	# It's the default
time3 = Time.now
300.times{
	conn.exec("INSERT INTO test_transaction VALUES(1, 'something')")
}
conn.commit
time4 = Time.now

# Output the comparation
puts "Time cost of each-time commit(sec): " + (time2-time1).to_s
puts "Time cost of one-time commit(sec) : " + (time4-time3).to_s

conn.logoff

puts '-'*80

Oracle stored packages. TO_CHAR is a built-in function.

From your terminal window, execute the plsql.rb script by executing the following command:

The output is shown in the screenshot.

The contents of the plsql.rb file is as follows:

# plsql.rb: Call PL/SQL procedures and functions
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

puts 'Get version information from Oracle:'
cursor = conn.parse("BEGIN DBMS_UTILITY.db_version(:ver, :comp); END;")
cursor.exec(' '*50,' '*50)
puts "Oracle DB Version: " + cursor[1]
puts "Oracle DB Compatibility: " + cursor[2]
puts

puts 'Call TO_CHAR function:'
cursor = conn.parse("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;")
cursor.exec('ABCD', 123)
puts "TO_CHAR input: " + cursor[2].to_s
puts "TO_CHAR output: " + cursor[1]
puts

puts 'Get DBMS_OUTPUT:'
conn.exec("BEGIN DBMS_OUTPUT.ENABLE(NULL); END;")
conn.exec("BEGIN DBMS_OUTPUT.put_line('Hello world!'); END;")
conn.exec("BEGIN DBMS_OUTPUT.put_line('Can you see me?'); END;")

cursor = conn.parse("BEGIN DBMS_OUTPUT.get_line(:line, :status); END;")
cursor.bind_param(':line', nil, String, 255)
cursor.bind_param(':status',Fixnum)

while true
	cursor.exec
	break if cursor[':status'] == 1
	puts cursor[':line']
end

puts '-'*80

Check whether BLOB is available or not. To use BLOB you need to insert EMPTY_BLOB() at first.

A PNG file is in the same folder with the script file. Create a folder called download.

From your terminal window, execute the blob.rb script by executing the following commands:

cd

mkdir download

ruby blob.rb

The output is shown in the screenshot.

The contents of the blob.rb file is as follows:

#blob.rb: Save and Load BLOB
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

conn.exec("DELETE FROM test_blob")

# Must insert a EMPTY_BLOB before save real data
cursor = conn.parse("INSERT INTO test_blob VALUES(:name, EMPTY_BLOB())")
Dir["*.png"].each do |fname|
	cursor.exec(fname)
end

# Save BLOB into Oracle
conn.exec("SELECT name,image FROM test_blob") do |name, image|
	puts "uploading file: " + name
	File.open(name, 'r') do |f|
		image.write(f.read)
		image.size = f.pos
	end
end

# Load BLOB from Oracle
conn.exec("SELECT name,image FROM test_blob") do |name, image|
	puts "downloading file: " + name
	File.open("download/"+name, 'w') do |f|
		f.write(image.read)
	end
end

# End
conn.logoff
puts '-'*80

Review the file sizes for the ruby.png files. Execute the following command:

Generate XML from relational data based on a SELECT statement. It returns CLOB.

From your terminal window, execute the xml.rb script by executing the following command:

The output is shown in the screenshot.

The contents of the xml.rb file is as follows:

# xml.rb: Generate a xml document based on relational data
require 'config.rb'

# Create a connection to Oracle
conn = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)

sql = "SELECT DBMS_XMLGEN.getxml('
		SELECT dept.name,dept.id,
		CURSOR(
			SELECT emp.first_name,emp.last_name,emp.phone_number
			FROM employees emp
			WHERE emp.department_id=dept.id
		) AS employees
		FROM departments dept
		WHERE dept.id in (20,110)'
	) AS xml
	FROM dual"

# Fetch as CLOB
puts conn.exec(sql).fetch[0].read

conn.logoff
puts '-'*80

You can now generate the article model. From your terminal window, execute the following commands:

cd

cd holapp

ruby script/generate migration add_price_to_articles

You want to edit the script that was generated. From your terminal window, enter the following command:

Replace the self.up section with the following content and then save and close the file.

You can now rake the database migration to generate target tables. From your terminal window, enter the following command:

Before the application will show the new column, the various views will need to be changed to allow prices to be entered and displayed.

Note: To remove a column we can add a line like "remove_column :articles, :price" in the self.up method; To rename a column we can add a line like "rename_column :articles, :price, :new_price" in the self.up method; To change a column we can add a line like "change_column :articles, :price, :integer" in the self.up method.
Moreover we can rollback the migration operation by issuing the command "rake db:migrate VERSION=xx", where the xx is the version you expect to rollback to.