Ruby application for sql queries

Rails
1

Hello everybody,

I am currently working on a database system that utilizes ruby on
rails
with a oracle database. Here’s the situation:
the ruby on rails web application is up and running (scaffolds have
been generated);
my oracle database is full of tables that are populated with data;
there is a successful connection between rails and my oracle database;
however, what still needs to be done is I currently have sql
statements/queries in which I am using to mine data from my database
(eg. “find minimum time someone has been employed”
Select min(time) from table…etc)

What I need to do is write a ruby application (one for each sql
statement), where the sql statement is hardcoded into the ruby code.
I need the ruby code to:

  1. connect to the oracle database
  2. run the sql statement on the oracle database
  3. take the results and return them in an array or
    something of that nature (so that they can be used in an html file for
    output on the web application)

If someone could give me a skeleton of how this should look in ruby or
give me example code I would forever be in your debt.
Thanks

2

Thanks the for reply. Right now I have written the following ruby
code to pass to Oracle:

#fetch.rb: Fetch data from database
require ‘oci8’

Create a connection to Oracle

conn = OCI8.new(‘DBCLASS126’, ‘TiGeR_TeAm’, ‘//database.grace.umd.edu:
1521/dbclass1’)

parse and exec the statement

cursor = conn.parse("SELECT min(end_date - start_date) as
minimum_days, max(end_date - start_date) as maximum_days, avg(end_date

  • start_date) as average_days FROM People P, Worked_for_rels W WHERE
    P.PID = W.PID AND P.PID = 1")
    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

An example of the results I am getting are something like:
Minimum_days, Maximum_days, Average_days
5 10 7.5
…etc

Because my ruby code above is currently printing values the results
above are outputted to the command prompt. However, how would I take
these results and instead of printing them like above, pass them back
to the caller of the ruby code in an array of some sort so that the
caller can take those results and do as it pleases with it (in my case
they will be used in an html file but I only need to know how to pass
the results above back).

Thanks again

On May 1, 7:16 am, Visit Indonesia 2008 <rails-mailing-l…@andreas-

3

(eg. “find minimum time someone has been employed”

Employee.minimum(“time”)
Employee.maximum(“time”)

More detail :

http://teapoci.blogspot.com/2008/04/ebook-pro-active-record-database-with.html

What I need to do is write a ruby application (one for each sql
statement), where the sql statement is hardcoded into the ruby code.

Employe.find_by_sql(“put your sql statement here”)

I need the ruby code to:

  1. connect to the oracle database

[*] Download ruby-oci8-1.0.1-mswin32.rb here:
http://rubyforge.org/frs/?group_id=256&release_id=21562

[*] After that run it D:>ruby\project_name\ruby
ruby-oci8-1.0.1-mswin32.rb

[*] Then go to {#RAILS_ROOT}/config/database.yml

development:
adapter: oci
host: :/
username:
password:

If not work, you can make connection testing, create a file is named
testing_oci.rb, then put it and save it after that run it :

#example you have table teapocis

require ‘oci8’

connection = OCI8.new(‘’, ‘’, ‘//:/’)
connection.exec(“select * from teapocis”) do |row|
puts 'dummy: ’ + row[0] # output will be “dummy: data_value”
end
connection.logoff

  1. run the sql statement on the oracle database

Store procedure and Transaction Support for Oracle is so friendly in
Ruby. Example Store Procedure :

ActiveRecord::Base.connection.execute(‘BEGIN do_the_calculation; END;’)

  1. take the results and return them in an array or
    something of that nature (so that they can be used in an html file for
    output on the web application)

@variable_name = Employee.find(:all)

This command is the same like
SELECT * FROM employee

The @variable (read : instance variable of variable_name) saved query of
“SELECT * FROM employee”, in your html.erb or rhtml just do it.:

<% for posting in @variable_name %>

<%= posting.name %> | <%= posting.time %> | <%= posting.salary %>

<% end %>

If someone could give me a skeleton of how this should look in ruby or
give me example code I would forever be in your debt.
Thanks

You’re welcome

Reinhart
http://teapoci.blogspot.com

My Advice " You should read book :

  • PRO ACTIVE RECORD
  • BEGINNING RAILS FROM NOVICE TO PROFESIONAL, or book
  • AGILE WEB DEVELOPMENT IN RAILS "
4

This is a ruby question I think, rather than a rails question–you’re
not using any of the rails framework. You could be using rails for
this–rails would be good for everything from submitting your query to
spitting the results out in an HTML page. It’s hard to know from the
little info you’ve given, but you’re at risk of reinventing rails (and
about a jillion other dynamic web frameworks :P) here.

But to answer the question, I think you want to wrap your code up in a
method, and have that method return say, an array of arrays. So
something like:

def get_statistics
res = []

[[everything you’ve got below up to ‘output column names’]]

output column names

res << cursor.getColNames

output rows

while r = cursor.fetch
res << r
end

close the cursor and logoff

cursor.close
conn.logoff

return res
end

HTH,

-Roy