# Ruby application for sql queries

**URL:** <https://www.ruby-forum.com/t/ruby-application-for-sql-queries/136370>\
**Category:** Rails\
**Created:** [May 1, 2008, 9:56am UTC](https://www.ruby-forum.com/t/ruby-application-for-sql-queries/136370 "2008-05-01T09:56:35Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![StudxXxent](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@StudxXxent](https://www.ruby-forum.com/u/StudxXxent)\
**Post date:** [May 1, 2008, 9:56am UTC](https://www.ruby-forum.com/t/ruby-application-for-sql-queries/136370/1 "2008-05-01T09:56:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![StudxXxent](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@StudxXxent](https://www.ruby-forum.com/u/StudxXxent)\
**Post date:** [May 1, 2008, 10:55pm UTC](https://www.ruby-forum.com/t/ruby-application-for-sql-queries/136370/2 "2008-05-01T22:55:39Z")

</div>

## 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](https://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-

---

<div class="post-metadata">

**Author:** ![StudxXxent](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@StudxXxent](https://www.ruby-forum.com/u/StudxXxent)\
**Post date:** [May 1, 2008, 1:16pm UTC](https://www.ruby-forum.com/t/ruby-application-for-sql-queries/136370/3 "2008-05-01T13:16:19Z")

</div>

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

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

More detail :

> **[Active Record - Session 1](http://teapoci.blogspot.com/2008/02/active-record-session-1.html)**
>
> Ruby is not Unidentified Programing Object, but it is Universal Programing Object As we know that rails is built by MVC design. Let we under...

[http://teapoci.blogspot.com/2008/04/ebook-pro-active-record-database-with.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](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](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 "

---

<div class="post-metadata">

**Author:** ![StudxXxent](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/96bed5/32.png) [@StudxXxent](https://www.ruby-forum.com/u/StudxXxent)\
**Post date:** [May 2, 2008, 2:43am UTC](https://www.ruby-forum.com/t/ruby-application-for-sql-queries/136370/4 "2008-05-02T02:43:21Z")

</div>

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