in reply to Re: DBD::CSV and SQL::Statement
in thread DBD::CSV and SQL::Statement

Hi I'm the author of DBD::RAM so here's the scoop right from the horse's mouth (now is that a bad mixed metaphor or what?).

1. DBD::RAM is soon to be replaced by DBD::AnyData which handles CSV, XML, Fixed Width Records and many more formats, see its home page at http://www.vpservices.com/jeff/programs/AnyData/ including reviews by Randal and by Michel Rodriguez.

2. DBD::CSV, DBD::RAM, DBD::AnyData all depend on Jochen Wiedman's SQL::Statement to do SQL parsing.

3. SQL::Statement does *NOT* support joins.

4. I have agreed with Jochen to take over maintenance of SQL::Statement but I'm afraid I'm behind on getting to it. If anyone is interested in helping out with it, let me know.

5. I have also created AnyData.pm, a non-DBI, tied-hash access to all the formats supported by DBD::AnyData. It does allow simple joins by using queries in one table to be the key to a hash into another table.

6. It is possible to accomplish simple joins with any of the above mentioned DBDs by opening multiple statement handles for the multiple tables and doing nested fetch loops. I can supply an example if needed.

7. Good luck!

Replies are listed 'Best First'.
Re: Re: Re: DBD::CSV and SQL::Statement
by Anonymous Monk on Mar 27, 2001 at 00:10 UTC
    I like getting info straight from the horse's mouth, metaphorically speaking that is. I've installed DBD:RAM, very nice and easy to use.

    Can you supply me with an example of how to use the multiple statement handles and doing nested fetch loops. I could probably figure it out, but since you offered---post it!!

    Copied straight from the TODO's under DBD::CSV:

    Joins
    The current version of the module works with single table SELECTs only, although the basic design of the SQL::Statement module allows joins and the like.

    Maybe I'm reading it wrong?! :)
[reply]
      Ok, first on SQL::Statement "the basic design ... allows joins". Yeah, right. The design allows it and if you want to implement it, all you have to do is implement it. :-) It's on my to-do list but it is not an easy task.

      Now for the example of accomplishing joins with multiple statement handles (this uses DBD::AnyData which is newer than DBD::RAM but except for the slightly different "catalog" syntax, it's the same with DBD::RAM:

      my $dbh=DBI->connect('dbi:AnyData(RaiseError=>1):');
      $dbh->func( 'Class', 'Pipe', 'class.tbl', 'ad_catalog');
      $dbh->func( 'Reg', 'Pipe', 'reg.tbl', 'ad_catalog');
      my $class_sth = $dbh->prepare( qq{SELECT cid,cname FROM Class} );
      my $reg_sth = $dbh->prepare( qq{SELECT sid FROM Reg WHERE cid = ?} );
      $class_sth->execute;
      while (my($cid,$cname) = $class_sth->fetchrow_array) {

      $reg_sth->execute($cid);
      my $sid = $reg_sth->fetchrow_arrayref->[0];
      print "$cid : $cname : $sid\n";
      }

      That will produce the same list that this statement would:

      SELECT Class.cid, Class.cname, Reg.sid
      FROM Class, Reg
      WHERE Class.cid = Reg.cid

[reply]
Re: Re: Re: DBD::CSV and SQL::Statement
by Anonymous Monk on Mar 27, 2001 at 01:16 UTC
    Also, does DBD::RAM support row counts such as:

    select count(*) from table where id = '$id' group by type_name

    Thanks.
[reply]
      Sorry, no count or group by yet.

      But $sth->rows gives a count even without a fetch. So:

      my $sth=$dbh->prepare(qq{SELECT * FROM foo WHERE bar = baz})
      $sth->execute;
      print $sth->rows, "matched the query";

[reply]

In Section Seekers of Perl Wisdom