From: David Landrith <dlandrith@...>
Date: 2003-01-02T02:10:53+09:00
Subject: Re: Ruby DBI

On Wednesday, January 1, 2003, at 09:20 AM, Tom Sawyer wrote:

> On Wednesday 01 January 2003 07:03 am, David King Landrith wrote:
>> Is there any way to retrieve the table and database associated with
>> each column in a resultset?
>
> i have not seen a way, but i wonder how you got the resultset without 
> first
> knowing the database and table name?

Two situations come to mind immediately:

First, if you create objects corresponding to tables in your database, 
you'll want to make them subclasses of some abstract super-class that 
contains the common code.  In the superclass, there are often cases 
where you'll be dealing with result sets without table information.

Second, if you want to create utilities for dealing with result sets.  
For example, in Java you can create (and I have created) an object that 
buffers a result set and metadata.  If you are clever, you can track 
changes, deletions, and additions to the data.  If you are really 
clever, you can use the metadata to construct SQL to change the 
database to match the buffer.  This allows you to instantiate a result 
set object by sending it any SQL whatever, change the values in that 
object, add rows to the object, and delete rows to the object, and then 
simply call a "commit" method to have the object issue the appropriate 
update, insert, and delete statements.  This type of a setup allows you 
to eliminate hundreds or thousands of error-prone sql-computation lines 
from your code.  Moreover, such an object makes an ideal superclass for 
objects that correspond to database tables.

>> Also, I haven't been able to find anywhere that outlines the
>> functionality goals of Ruby DBI vis a vis other database APIs.  It
>> would be quite nice if the goal of Ruby DBI was parity with JDBC or
>> ODBC rather than parity with Perl DBI (which is just barely good 
>> enough
>> for rudimentary database programming).
>
> all i can say is that i haven't been at a loss for getting things done 
> with
> Ruby DBI. what functionality does it lack that ODBC and JDBC have?

The difference is primarily in the meta-data.  JDBC and ODBC can 
identify primary keys, foreign keys, constraints, source database, 
source schema.  This type of meta-data allows you to do real object 
oriented database programming, rather than simply bare or parameterized 
SQL.

> by the way there is a Ruby ODBC library avail., though i have no 
> experience with it.

Is this a DBD for ODBC, or an alternative to DBI?  If it's an 
alternative, then it may be workable, but using Ruby through ODBC seems 
to represent another layer of overhead.  It would be nice if Ruby 
supported robust database functionality through DBI/DBD.

-------------------------------------------------------
David King Landrith
   (w) 617.227.4469x213
   (h) 617.696.7133

One useless man is a disgrace, two
are called a law firm, and three or more
become a congress   -- John Adams
-------------------------------------------------------
public key available upon request