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/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