Man, DBIx::Recordset is sah-weet. No mind-entangling string operations, just a hash with slots for the things I need to do... In all honesty I like to code straight SQL since starting my read of SQL Queries for Mere Mortals but I hate trying to turn Perl data structures into SQL. 
use strict;

use Data::Dumper;
use DBIx::Connect;
use DBIx::Recordset;


$DBIx::Recordset::Debug = 2;

my @scf = (902 .. 908, 917, 926 .. 928); # scf is a zip prefix

use vars qw(*zipset);

*zipset = DBIx::Recordset->Search
  ({
    '!DataSource' => DBIx::Connect->to('mdb_test'),
    '!Table'      => 'customer',
    '*zip'        => 'LIKE',
    'zip'         => [ map { $_ . '%' } @scf ],
    '$max'        => 10
   });

my $count;

while (my $rec = $zipset->Next) {
  print Dumper($rec);
  exit if ++$count > 10;
}

[download]

And the debugging output to make sure everything is copacetic:


        tbone@MDB:/data/tbone/maldunn/204209-007 perl query.pl
DB:  Use already open dbh for database=test;host=localhost (id=1, numO
+pen = 0)
DB:  'SELECT * FROM customer WHERE   (  (zip LIKE ?)  or  (zip LIKE ?)
+  or  (zip LIKE ?)  or  (zip LIKE ?)  or  (zip LIKE ?)  or  (zip LIKE
+ ?)  or  (zip LIKE ?)  or  (zip LIKE ?)  or  (zip LIKE ?)  or  (zip L
+IKE ?)  or  (zip LIKE ?))      LIMIT 0,11' bind_values=<902% 903% 904
+% 905% 906% 907% 908% 917% 926% 927% 928%> bind_types=<12 12 12 12 12
+ 12 12 12 12 12 12>
$VAR1 = {
          ...
        };
$VAR1 = {
          '...
        };

[download]

Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality.

Replies are listed 'Best First'.
Re: Check out my Recordset
by mpeppler (Vicar) on Oct 09, 2003 at 23:54 UTC
    I guess I still have a philosophical problem with this sort of thing - I want to know what SQL is sent to the dataserver - in particular as most non-trivial problems require multi-table joins to satisfy the request, and such requests can create seriously suboptimal code SQL statements...

    I'm a DBA for a system where the front-end is written in Java using WebObjects, and that system suffers from the same potential problems - and just goes against my instincts regarding the care and feeding of database servers...

    Michael

[reply]
Re: Check out my Recordset
by Ovid (Cardinal) on Oct 10, 2003 at 01:55 UTC

    Out of curiosity does it always create SQL with the "SELECT *" in there? If it does, it's pretty much guaranteed to be useless in my shop. We have huge tables and millions of records. Having to grab that extra information would kill us.

    Also, why are you using a typeglob (*zipset) instead of a scalar? Is there some benefit that I'm not seeing?

    Cheers,
    Ovid

    New address of my CGI Course.

[reply]
      1. oh no, you can select whatever fields you want
      2. well, one the first things mentioned in the DBIx::Recordset docs is that you use each part of the typeglob for something different... if you feel like it... you can simply setup a scalar and use Recordset that way with no loss of functionality... the array is used to index in the result set. The hash can have one of two uses, you can access the current record in the result set... or if you called Search() with !HashAsPrimaryKey then the hash is tied to the database and the keys of the hash are used to retrieve records.
      3. DBIx::Recordset::Playground is the new living docs for Recordset.

        Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality.

[reply]
[d/l]
Re: Check out my Recordset
by jdtoronto (Prior) on Oct 10, 2003 at 03:06 UTC
    Like many I have had reservations about generated SQL, I haven't used DBIx::Recordset, just skiimed the docs. I have used SQL::Abstract to simplify the building of messy SQL queries on a single table. It doesn't do a 'SELECT *' fortunately, it builds a string that you pass yourself to DBI. It has helped a great deal with this one app where I have to offer the end user a great deal of flexibility in getting the data they want.

    The jury here is still out on things like DBIx::Recordset that handle more complex stuff.

    But it does appear that you really build the query by building a data structure that gets translated. so in fact you have a lot of control.

    jdtoronto

[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Meditations