Ruby application for sql queries

rubyonrails-talk
#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

(eg. "find minimum time someone has been employed"

Employee.minimum("time")
Employee.maximum("time")

More detail :
http://teapoci.blogspot.com/2008/02/active-record-session-1.html
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: <host>:<port>/<sid>
username: <user>
password: <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('<user>', '<password>', '//<host>:<port>/<sid>')
connection.exec("select * from teapocis") do |row|
puts 'dummy: ' + row[0] # output will be "dummy: data_value"
end
connection.logoff

2) 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;')

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)

@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 %>

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

<% 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 "

#3

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

#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