Rails - Jan 2006 - Is anyone using oracle with RoR successfully?

Hi,

Is anyone using Oracle with RoR successfully?

Is everything running smoothly?
What are the main issues?

any response is appreciated
thanks,
Chris

-- 
Posted via http://www.ruby-forum.com/.

> Is anyone using Oracle with RoR successfully?
Yep, 8.1, rails server under Windows XP
> Is everything running smoothly?
Nothing justify changing database if you use existing databases, but
if you start a new project there are possible threads
> What are the main issues?
Some are related to the AR Adapter. (read oci_adapter.rb in
activerecord source tree)
- Each table must have a sequence if you plan to Create..
- No LIMIT in Oracle, simulated with subquery, more slower when you paginate
- Oracle''s syntax for join (+) is not used in the query builder who
respect SQL92 with plain JOINs. only for Ora < 9i. So if you use 8i
you can''t use :join but fallback to by_sql.

And globally with Rails

- Scaffolding sometimes hang when generating subforms, you have to
edit them, but as a real Ror programmer you don''t use scaffold :)
- Most of us, Oracle use mean using an existing legacy schema, and
IMHO that''s the big issues when you spend most of your time for
writing Models.
- Development environment with Webrick+Oracle is slooww, think to use
Apache+FastCGI or better(?)

I have also gotten Rails (1.1) to work successfully with Oracle (10g). I
am running Apache and FCGI locally, contacting a remote 10g database.
Oracle in my case does in fact mean legacy and there was quite a bit of
finagling to be done with the models. The queries are a little on the
sloppy side, but work. Most of all, the DBA hates not having query
control.

Biggest pitfall so far (and coincidentally the reason this enterprise is
hesitant to support Rails at all) is lack of integrated stored procedure
support. My next task is to get stored procedures working in Rails.
Those who have already done this, please post.

-- 
Posted via http://www.ruby-forum.com/.

David!

I was very pleased to read your post. I am about to setup a RHEL
4/Apache2/fcgi/RoR/Oracle 10g site. I''ve tested everything but the
Oracle bit (running with Postgresql at the moment). Haven''t quite got
fcgi working.

Did basic Oracle <-> RoR give you much trouble? I''d be very
grateful
for any hints or things to watch out for.

Regards,
Bealach

On 4/25/06, David Michael <d@d.com> wrote:
> I have also gotten Rails (1.1) to work successfully with Oracle (10g). I
> am running Apache and FCGI locally, contacting a remote 10g database.
> Oracle in my case does in fact mean legacy and there was quite a bit of
> finagling to be done with the models. The queries are a little on the
> sloppy side, but work. Most of all, the DBA hates not having query
> control.
>
> Biggest pitfall so far (and coincidentally the reason this enterprise is
> hesitant to support Rails at all) is lack of integrated stored procedure
> support. My next task is to get stored procedures working in Rails.
> Those who have already done this, please post.
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Oracle in Rails has worked well for me several times with a very
similar stack to what you describe.  The only advice I have is to
triple-check your environment variables.
You''ll want to make sure the oci libraries are in your library path,
and that you''ve built the Ruby OCI8 libraries against them.  TNS_ADMIN
also needs to be set, both in your development environment and in the
FastCGI environment.

On 4/25/06, Bealach Na Bo <bealach@gmail.com>
wrote:
> David!
>
> I was very pleased to read your post. I am about to setup a RHEL
> 4/Apache2/fcgi/RoR/Oracle 10g site. I''ve tested everything but the
> Oracle bit (running with Postgresql at the moment). Haven''t quite
got
> fcgi working.
>
> Did basic Oracle <-> RoR give you much trouble? I''d be very
grateful
> for any hints or things to watch out for.
>
> Regards,
> Bealach
>
> On 4/25/06, David Michael <d@d.com> wrote:
> > I have also gotten Rails (1.1) to work successfully with Oracle (10g).
I
> > am running Apache and FCGI locally, contacting a remote 10g database.
> > Oracle in my case does in fact mean legacy and there was quite a bit
of
> > finagling to be done with the models. The queries are a little on the
> > sloppy side, but work. Most of all, the DBA hates not having query
> > control.
> >
> > Biggest pitfall so far (and coincidentally the reason this enterprise
is
> > hesitant to support Rails at all) is lack of integrated stored
procedure
> > support. My next task is to get stored procedures working in Rails.
> > Those who have already done this, please post.
> >
> > --
> > Posted via http://www.ruby-forum.com/.
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Thanks for the pointers. I might be back here with questions later....

Bealach

On 4/26/06, Wilson Bilkovich <wilsonb@gmail.com>
wrote:
> Oracle in Rails has worked well for me several times with a very
> similar stack to what you describe.  The only advice I have is to
> triple-check your environment variables.
> You''ll want to make sure the oci libraries are in your library
path,
> and that you''ve built the Ruby OCI8 libraries against them. 
TNS_ADMIN
> also needs to be set, both in your development environment and in the
> FastCGI environment.
>
> On 4/25/06, Bealach Na Bo <bealach@gmail.com> wrote:
> > David!
> >
> > I was very pleased to read your post. I am about to setup a RHEL
> > 4/Apache2/fcgi/RoR/Oracle 10g site. I''ve tested everything
but the
> > Oracle bit (running with Postgresql at the moment). Haven''t
quite got
> > fcgi working.
> >
> > Did basic Oracle <-> RoR give you much trouble? I''d be
very grateful
> > for any hints or things to watch out for.
> >
> > Regards,
> > Bealach
> >
> > On 4/25/06, David Michael <d@d.com> wrote:
> > > I have also gotten Rails (1.1) to work successfully with Oracle
(10g). I
> > > am running Apache and FCGI locally, contacting a remote 10g
database.
> > > Oracle in my case does in fact mean legacy and there was quite a
bit of
> > > finagling to be done with the models. The queries are a little on
the
> > > sloppy side, but work. Most of all, the DBA hates not having
query
> > > control.
> > >
> > > Biggest pitfall so far (and coincidentally the reason this
enterprise is
> > > hesitant to support Rails at all) is lack of integrated stored
procedure
> > > support. My next task is to get stored procedures working in
Rails.
> > > Those who have already done this, please post.
> > >
> > > --
> > > Posted via http://www.ruby-forum.com/.
> > > _______________________________________________
> > > Rails mailing list
> > > Rails@lists.rubyonrails.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Hi, i created the initial post all the way back in January.

I''ve been using ROR + Oracle pretty much every day since then and 
everything has gone great!

-- 
Posted via http://www.ruby-forum.com/.

On 1/12/06, Mathieu Chappuis <mathieu.chappuis.lists@gmail.com>
wrote:
> > Is anyone using Oracle with RoR successfully?
>
> Yep, 8.1, rails server under Windows XP
>
> > Is everything running smoothly?
>
> Nothing justify changing database if you use existing databases, but
> if you start a new project there are possible threads
>
> > What are the main issues?
>
> Some are related to the AR Adapter. (read oci_adapter.rb in
> activerecord source tree)
> - Each table must have a sequence if you plan to Create..
This isn''t a limitation per se on the oci_adapter.rb, but on the
current Rails requirement that tables have a simple auto-numbered
primary key field, and in Oracle, you do this with sequences, because
there is no autonumber attribute for Integer fields ala Postgres, SQL
Server, MySQL, etc. Yes, it completely blows if you''re using a
database that wasn''t set up this way, however...

It''s a reasonable assumption at this point, but it''ll need to
be
addressed if supporting existing database schemas is a desired
long-term goal of Rails for the more flexible RDBMSs like Oracle.

Sequences work great in Oracle because they''re guaranteed to be
transaction-safe, and they''re not tightly tied to the table''s
design.
> - No LIMIT in Oracle, simulated with subquery, more slower when you
paginate
Otherwise, the alternative would seem to be to set up a package in
Oracle that could run the query (pass in the SQL, do some basic SQL
injection checking, run the SQL as dynamic sql) in a cursor that
returns pages of records. This would probably freak out most Oracle
DBAs when they look at some of the system tables and see all sorts of
open cursor handles, etc.

If you look at the paging code for other adapters (i.e.,
sqlserver_adapter.rb), it''s really no better for them, either, unless
their SQL flavor supports non-standard syntax to do it, of which TSQL
doesn''t, so it''s nested SQL query hacks like the one above is
required
for other adapters, too.

Don''t want to suck the entire dataset into Rails just to do it
in-memory on the server though.
Kind of defeats the purpose of the database server, and adds all sorts
of complexity downstream...
> - Oracle''s syntax for join (+) is not used in the query builder
who
> respect SQL92 with plain JOINs. only for Ora < 9i. So if you use 8i
> you can''t use :join but fallback to by_sql.
oci_adapter.rb could override the default SQL presented by
activerecord::base, based on perceived (or configured in database.yml)
Oracle version, no (by reimplement the ActiveRecord::Base methods
involved)?

I''m not sure if anyone has tried using the sqlserver_adapter.rb
against older versions of SQL Server that don''t support ANSI join
syntax, either (6.0? 6.5?), either.
> And globally with Rails
>
> - Scaffolding sometimes hang when generating subforms, you have to
> edit them, but as a real Ror programmer you don''t use scaffold :)
> - Most of us, Oracle use mean using an existing legacy schema, and
> IMHO that''s the big issues when you spend most of your time for
> writing Models.
Actually, this will be a problem for any existing database system (I''m
thinking of people who write apps against SAP, Lawson, PeopleSoft,
etc. databases, where the design considerations of those databases do
not map well to the conventions or requirements of Rails
development...)
> - Development environment with Webrick+Oracle is slooww, think to use
> Apache+FastCGI or better(?)
If there is one thing that would be nice for Oracle to improve it
would be to increase the speed of opening database connections...
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>