Forum: Ruby Witch Oracle library should I use

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ben E. (Guest)
on 2007-05-04 19:27
(Received via mailing list)
Have had a look on rubyforge and done a little looking around but not
totaly sure which ruby oracle library's I should use.

I am connecting to a 10g database.

Ruby9i and Ruby/OCI8 seem to be the contenders, or is there something
else.

What would people recommend?

Ben
Brian C. (Guest)
on 2007-05-04 22:39
(Received via mailing list)
On Sat, May 05, 2007 at 12:27:13AM +0900, Ben E. wrote:
> Have had a look on rubyforge and done a little looking around but not
> totaly sure which ruby oracle library's I should use.
>
> I am connecting to a 10g database.
>
> Ruby9i and Ruby/OCI8 seem to be the contenders, or is there something else.
>
> What would people recommend?

ruby-oci8. It's been around for a long time, and it works well. It's the
API
used by ActiveRecord's Oracle adaptor, and it also comes with a DBD for
use
with ruby-dbi. I'm using it with Oracle 10g now.

I've never seen or used Ruby9i though.
Ulf Hellström (Guest)
on 2007-05-04 22:56
(Received via mailing list)
Hi,

I'm new on this mailing list. My name is Ulf , lives in Sweden and
been developing for Oracle for many, many years. Used to work for
Oracle for 10 years.
I've been studying and learning  Ruby for aroune 2 months now and has
done some work with Ruby and Oracle.
As suggested i would defenly say go for Ruby/OCI8

There are some good introductions on how to use Ruby with Oracle on
OTN (Oracle Technology Network)

See the following links:

http://www.oracle.com/technology/pub/articles/marx-ruby.html
http://www.oracle.com/technology/pub/articles/tate...
http://www.oracle.com/technology/pub/articles/haef...
http://www.oracle.com/technology/pub/articles/sate...

Kindly Rgds
/Ulf
Ben E. (Guest)
on 2007-05-04 23:38
(Received via mailing list)
On 04/05/07, Ulf Hellström <removed_email_address@domain.invalid> wrote:
> OTN (Oracle Technology Network)
>
> See the following links:
>
> http://www.oracle.com/technology/pub/articles/marx-ruby.html
> http://www.oracle.com/technology/pub/articles/tate...
> http://www.oracle.com/technology/pub/articles/haef...
> http://www.oracle.com/technology/pub/articles/sate...

Thanks for this.  Have been trying Ruby/OCI8 and it seems to work.
Only thing is when it fetches row it is referenced as an array
(row[0], row[1]...).  Is there a way of referencing the column names
(row["username"]...).

Ben
Dave R. (Guest)
on 2007-05-04 23:57
Ben E. wrote:
> On 04/05/07, Ulf Hellstr�m <removed_email_address@domain.invalid> wrote:
>> OTN (Oracle Technology Network)
>>
>> See the following links:
>>
>> http://www.oracle.com/technology/pub/articles/marx-ruby.html
>> http://www.oracle.com/technology/pub/articles/tate...
>> http://www.oracle.com/technology/pub/articles/haef...
>> http://www.oracle.com/technology/pub/articles/sate...
>
> Thanks for this.  Have been trying Ruby/OCI8 and it seems to work.
> Only thing is when it fetches row it is referenced as an array
> (row[0], row[1]...).  Is there a way of referencing the column names
> (row["username"]...).
>
> Ben

 i did a describe on my table that has a lot of column
 and cut & pasted the result (in order) into a ruby array
 and whose named ruby array.index(named column) address that certain
corresponding column in the ruby
 oci8 array....
 readcols="_CODE, V_NUM, CUST_CODE, STYP_CODE, CAT_CODE, ACTN_CODE,
ACTION_DATE, ACTIVITY_DATE, USER_ID, READING, UMPTION, DOS, RTYP,
REAS_CE, ARGE_DATE, UR_CALC_NUM, INVN_CODE, UIER, U_CADJM, U_FACTOR,
UR_HIGH_LOW_EXCP, UR_TRBL_CODE_1, UR_TRBL_CODE_2, U_TRBL_CODE_3,
AT_CODE, _CHARGE_FREQUENCY, U_CHARGE_START_DATE, UR_NUTS,
UR_REAS_CODE_CNCL, US_CNCL_DATE, URR_CNCL_USER_ID, UR_CUST_CODE_PRIMARY,
URRCODE_PRIMAR...and on and on....".split(", ")
 recs=conn.exec("SELECT * FROM atable WHERE CODE NOT IN
('IN','SKIP','OUT') AND CODE = '#{prem}' AND CUST_CODE = #{cust.to_i}
AND TO_CHAR(GE_DATE,'YYYYMMDD') = '#{chargedon}' AND IS_DOS > 0"){|r|
readrec<<r}
    readrec.each do |tut|
     prevdait=tut[readcols.index("_DATE")]
Brian C. (Guest)
on 2007-05-05 00:13
(Received via mailing list)
On Sat, May 05, 2007 at 04:37:34AM +0900, Ben E. wrote:
> Thanks for this.  Have been trying Ruby/OCI8 and it seems to work.
> Only thing is when it fetches row it is referenced as an array
> (row[0], row[1]...).  Is there a way of referencing the column names
> (row["username"]...).

You could steal some code from ActiveRecord's Oracle connection adaptor.
When you call Foo.connection.select_all(...) in ActiveRecord, it returns
an array of
[{colname=>value, colname=>value},
 {colname=>value, colname=>value},
...]

Or I have a vague recollection that maybe ruby-dbi will do this for you.
Ulf Hellström (Guest)
on 2007-05-05 15:09
(Received via mailing list)
Hi Ben,

You could always use fetch_hasch method instead of fetch. This will
fetch the data as a ruby Hash where each key is the column name

Below is a simple example that select all columns from USER_TABLES
but only prints out the TABLE_NAME column.

Note: The reference to the column name, in the sample TABLE_NAME has
to be in uppercase.

The OCI8 API is documented quite good see

http://ruby-oci8.rubyforge.org/en/api.html

require 'oci8'

class Testoci

    def get_data

       puts "***************************"
       puts "Connecting..."
       puts "***************************"
       connection = OCI8.new('demo', 'demo', 'XE')
       puts "***************************"
       puts "Fetching data"
       puts "***************************"
       cursor =  connection.parse("SELECT * FROM USER_TABLES")
       cursor.exec()
       # print out only the values of the TABLE_NAME column
       while res = cursor.fetch_hash()
         puts res["TABLE_NAME"]
       end
       cursor.close
       connection.logoff
    end

end

ora=Testoci.new
ora.get_data


Kindly Rgds
/Ulf
Ben E. (Guest)
on 2007-05-08 12:38
(Received via mailing list)
On 04/05/07, Brian C. <removed_email_address@domain.invalid> wrote:
> ruby-oci8. It's been around for a long time, and it works well. It's the API
> used by ActiveRecord's Oracle adaptor, and it also comes with a DBD for use
> with ruby-dbi. I'm using it with Oracle 10g now.

ruby-dbi seems like a good idea but its Beta and there hasn't been a
release for over a year.  Don't think the folks here will trust it in
a production environment.

Ben
Ben E. (Guest)
on 2007-05-08 13:05
(Received via mailing list)
On 05/05/07, Ulf Hellström <removed_email_address@domain.invalid> wrote:
>
>        puts "***************************"
>          puts res["TABLE_NAME"]
>        end
>        cursor.close
>        connection.logoff
>     end
>
> end
>
> ora=Testoci.new
> ora.get_data

Thanks, just the ticket.

Shame fetch_hash is not an iterator but think I am going to write a
wrapper to make it easier to switch librarys/databases.

Ben
Brian C. (Guest)
on 2007-05-08 16:28
(Received via mailing list)
On Tue, May 08, 2007 at 05:38:27PM +0900, Ben E. wrote:
> >> What would people recommend?
> >
> >ruby-oci8. It's been around for a long time, and it works well. It's the
> >API
> >used by ActiveRecord's Oracle adaptor, and it also comes with a DBD for use
> >with ruby-dbi. I'm using it with Oracle 10g now.
>
> ruby-dbi seems like a good idea but its Beta and there hasn't been a
> release for over a year.  Don't think the folks here will trust it in
> a production environment.

The last release was August 2006, and it has had 11,870 downloads since
then.

I used it (with Oracle) in a large project at a previous employer about
4
years ago, which is still running strong. Before ActiveRecord came
along,
this was probably the most widely used database interface from Ruby. I
wouldn't worry about the "beta" label.

OTOH, if you're writing code which *only* ever needs to talk to Oracle,
then
you may as well cut out the middle man.

Regards,

Brian.
Bertram S. (Guest)
on 2007-05-08 17:59
(Received via mailing list)
Hi Ben,

Am Samstag, 05. Mai 2007, 00:27:13 +0900 schrieb Ben E.:
> Have had a look on rubyforge and done a little looking around but not
> totaly sure which ruby oracle library's I should use.
>
> I am connecting to a 10g database.
>
> Ruby9i and Ruby/OCI8 seem to be the contenders, or is there something else.
>
> What would people recommend?

I managed to compile Ruby9i 16 months ago. I sucessfully
connected to Oracle 8 and 9 databases. The thread started
with message id 175798.

I remember I was quite happy with it. Good luck!

Bertram
Ben E. (Guest)
on 2007-05-08 22:31
(Received via mailing list)
On 08/05/07, Brian C. <removed_email_address@domain.invalid> wrote:
> > >>
>
> The last release was August 2006, and it has had 11,870 downloads since
> then.
>
> I used it (with Oracle) in a large project at a previous employer about 4
> years ago, which is still running strong. Before ActiveRecord came along,
> this was probably the most widely used database interface from Ruby. I
> wouldn't worry about the "beta" label.

Need to have a look at ActiveRecord bit it seems not to be geared
towards SQL.  This is not a bad thing per say but I have a lot of SQL.

I do fancy using some type of wrapper as we are thinking of moving
stuff to postgress.

Regards,
Ben
unknown (Guest)
on 2007-05-08 22:54
(Received via mailing list)
Ben E. wrote:

>> > ruby-dbi seems like a good idea but its Beta and there hasn't been a
>> > release for over a year.  Don't think the folks here will trust it in
>> > a production environment.

Don't read too much into that "beta" label nor the last release.  I've
been using ruby-dbi in many, many production apps for years.  It's fine.
And releases aren't frequent because, while DBI could certainly be
improved, it is also stable and works.  If it's not broken.....


Kirk H.
James G. (Guest)
on 2007-05-08 23:04
(Received via mailing list)
On May 8, 2007, at 1:53 PM, removed_email_address@domain.invalid wrote:

> It's fine. And releases aren't frequent because, while DBI could
> certainly be improved, it is also stable and works.  If it's not
> broken.....

I know the DBI has been handed off at least once.  My understanding
was also that the current maintainers aren't too interested in
resurrecting it.  I agree that it works, but I'm not so sure not
abandonware.  I know there are some very old bugs in it still not
fixed, though they are minor issues.

Sequel is looking like a pretty promising library to replace the DBI,
but it doesn't yet support Oracle:

http://sequel.rubyforge.org/

James Edward G. II
Ball, Donald A Jr (Library) (Guest)
on 2007-05-08 23:21
(Received via mailing list)
> I know the DBI has been handed off at least once.  My
> understanding was also that the current maintainers aren't
> too interested in resurrecting it.  I agree that it works,
> but I'm not so sure not abandonware.  I know there are some
> very old bugs in it still not fixed, though they are minor issues.
>
> Sequel is looking like a pretty promising library to replace
> the DBI, but it doesn't yet support Oracle:
>
> http://sequel.rubyforge.org/

Sequel seems to be more oriented towards ORM, competing with
ActiveRecord. Isn't there still a need for an abstract database
interaction layer underneath it, along the lines of JDBC for Java? I'd
naively thought that everyone was using DBI for that. What substantive
problems with DBI lead sequel, ActiveRecord, etc. to write their own
database-specific connection drivers?

- donald
James G. (Guest)
on 2007-05-08 23:38
(Received via mailing list)
On May 8, 2007, at 2:20 PM, Ball, Donald A Jr (Library) wrote:

>
> Sequel seems to be more oriented towards ORM, competing with
> ActiveRecord.

True.  Of course, ActiveRecord can issue low-level queries and return
results.  You don't have to use it's high-level ORM layer.  That
almost makes it a viable alternative to the DBI, though some features
are not present or well exposed from the lower-level.

As for reasons:  I sure find ActiveRecord easier to install for one.

James Edward G. II
unknown (Guest)
on 2007-05-08 23:54
(Received via mailing list)
On Wed, 9 May 2007, James Edward G. II wrote:

> On May 8, 2007, at 1:53 PM, removed_email_address@domain.invalid wrote:

> I know the DBI has been handed off at least once.  My understanding was also
> that the current maintainers aren't too interested in resurrecting it.  I
> agree that it works, but I'm not so sure not abandonware.  I know there are
> some very old bugs in it still not fixed, though they are minor issues.

I am one of those maintainers.  My attitude, at this point, with that
codebase, is basically one of hand-off maintenance unless a major
problem
rears its head.

> Sequel is looking like a pretty promising library to replace the DBI, but it
> doesn't yet support Oracle:
>
> http://sequel.rubyforge.org/

Sequel looks quite promising, but it's also not really in the same niche
as DBI.  It's more of an ORM variation.  It has some really neat ideas
in
it, though.

I've believed, for a couple years, that we need a DBI2.  Something that
isn't so directly modeled on the Perl DBI, but is, instead, more
rubyesque
in it's API design, and something that learns from DBI and fixes some of
DBI's faults without overachieving -- lean, fast, simple, with a clean
Ruby API.

I've discussed this with some other people, off and on, and decided a
couple months ago that this would be a good project to start on here at
the beginning of the summer.  I have a couple things ahead of it in my
queue still, but consider this a prerelease announcement.  :)

So if anyone has any specific suggestions or requests to voice regarding
a
fast, clean DBI replacement, now would be a good time.


Kirk H.
James G. (Guest)
on 2007-05-09 00:00
(Received via mailing list)
On May 8, 2007, at 2:53 PM, removed_email_address@domain.invalid wrote:

> announcement.  :)
I think this is an exceptionally good idea.  You will be very
popular.  ;)

James Edward G. II
Ball, Donald A Jr (Library) (Guest)
on 2007-05-09 00:49
(Received via mailing list)
> So if anyone has any specific suggestions or requests to
> voice regarding a fast, clean DBI replacement, now would be a
> good time.

Blob support for all databases would be nice. I use ActiveRecord against
sqlserver and cannot store blobs therein. Granted ActiveRecord is part
of the problem, since it models sql queries as strings instead of
objects, but as far as I recall, when I looked at solving the problem,
even DBI didn't offer an abstract way of working with blobs.

Optional connection pooling could be nice as well.

The ability to install DBI and the various DBD's as gems would be
useful.

Another thing that seems to be missing is a specification on how the
various sql types are converted to and from ruby types, not to mention
some way of altering the mapping.

- donald
Robert K. (Guest)
on 2007-05-09 13:00
(Received via mailing list)
On 08.05.2007 22:00, James Edward G. II wrote:
>> at the beginning of the summer.  I have a couple things ahead of it in
>> my queue still, but consider this a prerelease announcement.  :)
>
> I think this is an exceptionally good idea.  You will be very popular.  ;)

Completely agree!  This sounds promising.

Kirk, what do you think about starting a Wiki to collect requirements
and / or improvement ideas?  I have at least one requirement that I
believe is not covered by DBI right now.

I'd also love to help although I can offer only limited resources.

Kind regards

  robert
unknown (Guest)
on 2007-05-09 19:43
(Received via mailing list)
On Wed, 9 May 2007, Robert K. wrote:

> Kirk, what do you think about starting a Wiki to collect requirements and /
> or improvement ideas?  I have at least one requirement that I believe is not
> covered by DBI right now.

I will do that.  Thanks for the suggestion.  I'll follow up to the list
in
about a week with wiki and initial project information.


Kirk H.
Robert K. (Guest)
on 2007-05-09 19:56
(Received via mailing list)
On 09.05.2007 17:43, removed_email_address@domain.invalid wrote:
> On Wed, 9 May 2007, Robert K. wrote:
>
>> Kirk, what do you think about starting a Wiki to collect requirements
>> and / or improvement ideas?  I have at least one requirement that I
>> believe is not covered by DBI right now.
>
> I will do that.  Thanks for the suggestion.  I'll follow up to the list
> in about a week with wiki and initial project information.

Cool!

  robert
Clifford H. (Guest)
on 2007-05-11 04:45
(Received via mailing list)
removed_email_address@domain.invalid wrote:
> I've believed, for a couple years, that we need a DBI2.

Just to add my voice in approval, in addition to which I've
sent you a private message offering help. I'd love to have
a better basis on which to build my ActiveFacts library,
instead of relying on the adapters of ActiveRecord.

I'm also running a BOF session on support for Enterprise
and Legacy databases at the RailsConf next week. Hopefully
some of you will be there to discuss things and maybe help
out on the panel.

Clifford H., Data Constellation.
Daniel B. (Guest)
on 2007-05-16 20:50
(Received via mailing list)
James Edward G. II wrote:
>>>
>>> http://sequel.rubyforge.org/
>>
>> Sequel seems to be more oriented towards ORM, competing with
>> ActiveRecord.
>
> True.  Of course, ActiveRecord can issue low-level queries and return
> results.  You don't have to use it's high-level ORM layer.  That almost
> makes it a viable alternative to the DBI, though some features are not
> present or well exposed from the lower-level.

Last I checked ActiveRecord still did not handle bind parameters, which
means it's regenerating the execution plan every time it executes a
query.

Regards,

Dan
This topic is locked and can not be replied to.