thr3ads.net - Rails - Ruby application for sql queries [May 2008]

If this information is useful, please help other people find it:
Share via:

StudxXxent

2008-May-01 06:31 UTC

Ruby application for sql queries

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Visit Indonesia 2008

2008-May-01 11:16 UTC

head link

Re: Ruby application for sql queries

> (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 "
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

StudxXxent

2008-May-01 20:54 UTC

head link

Re: Ruby application for sql queries

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-
s.net> wrote:> > (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.htmlhttp://teapoci.blogspot.com/2008/04/ebook-pro-active-record-database-...
>
>
>
> > 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
>
> Reinharthttp://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 "
> --
> Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Pardee, Roy

2008-May-02 00:41 UTC

head link

Re: Ruby application for sql queries

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

-----Original Message-----
From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org] On Behalf Of
StudxXxent
Sent: Thursday, May 01, 2008 1:55 PM
To: Ruby on Rails: Talk
Subject: [Rails] Re: Ruby application for sql queries


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(''some_user'',
''some_password'', ''//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-
s.net> wrote:> > (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.htmlhttp://
teapoci.blogspot.com/2008/04/ebook-pro-active-record-database-...>
>
>
> > 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
>
> Reinharthttp://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 "
> --
> Posted viahttp://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rails - May 2008 - Ruby application for sql queries

Ruby application for sql queries

Re: Ruby application for sql queries

Re: Ruby application for sql queries

Re: Ruby application for sql queries