Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have both DBD::CSV and SQL::Statement installed. Is it possible to write a script that will allow table joins using either one of these two modules? I'm currently using flat files as a database until we develop the db in Oracle, otherwise I'd know what to do.

I know how to write the join query, just need to know if it's possible with these modules.

Any suggestions?

Replies are listed 'Best First'.
Re: DBD::CSV and SQL::Statement
by physi (Friar) on Mar 26, 2001 at 21:21 UTC
    As far as I now, there is now 'join' implemented in CSV. You have to build your 'join'-funktion on your own with some tricky array&hash&"select here and there"-perlish lines , or you have to use another DB (like Oracle).
[reply]
      Sorry too fast fingers :-)
      I meant that there is NO 'join' implementation for CVS !!
      Sorry
[reply]
        SQL::Statement states that you can use joins, but they don't offer an example of how to combined CSV and SQL, although they state that it can be done.
[reply]
      I'm curious to see an example script of how the modules use a join statement.
[reply]
Re: DBD::CSV and SQL::Statement
by TStanley (Canon) on Mar 26, 2001 at 21:49 UTC
    There is a module called DBD::RAM that might be of use to you.

    TStanley
    In the end, there can be only one!
[reply]
      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!

[reply]
        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]
        Also, does DBD::RAM support row counts such as:

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

        Thanks.
[reply]

Back to Seekers of Perl Wisdom