Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Re: dbi style questions (code, discussion)

by edebill (Scribe)
on Dec 29, 2001 at 22:39 UTC ( [id://135141] : note . print w/replies, xml ) Need Help??

in reply to dbi style questions (code, discussion)

You're right that it gets ugly fast. Like you, I've been doing a lot of DBI work, and I've found that the bigger the SQL statements, the uglier it gets. Here's what I came up with

Starting with the "everything in a line" technique, but showing more columns/values

 my $bsql = "insert into messages(messageid, quoteid, subject, body, viewed, toid, fromid, timestamp_c, timestamp_m) values($next_messageid, $quoteid, $q_subject, $q_body, 0, $toid, $fromid, CURRENT TIMESTAMP, CURRENT TIMESTAMP)";

How long does it take to make sure all your columns names and values line up? What happens the next time you need to add a column? I get lost pretty quick looking at that style.

Now, let's try it in a vertical layout:

my $sql = "insert into messages ( messageid, quoteid, toid, fromid, subject, body, viewed, timestamp_c, timestamp_m ) values ( $next_messageid, $quoteid, $toid, $fromid, $q_subject, $q_body, 0, CURRENT TIMESTAMP, CURRENT TIMESTAMP)";

OK, so you have a little better hope of adding/removing columns without breakage. And people are generally better at counting lines than counting words (for figuring out exactly which column a value goes with). C-k C-k C-y in emacs (or the vi equivalent) can move columns,values around very efficiently. I definitely like this one better (even though it grows off screen pretty quick).

Now, for a third way. How about something like:

my $msgdata = { messageid => $next_messageid, quoteid => $quoteid, toid => $toid, fromid => $fromid, subject => $q_subject, body => $q_body, viewed => 0, timestamp_c => "CURRENT TIMESTAMP", timestamp_m => "CURRENT TIMESTAMP" }; my $sql = "insert into msg ("; my $values = ") values ("; my $started = 0; foreach my $datum (keys(%$msgdata)) { if (length($msgdata->{$datum}) > 0) { if ( $started ){ $sql .= ",\n $datum"; $values .= ",\n $msgdata->{$datum}"; } else { $started = 1; $sql .= " $datum"; $values .= " $msgdata->{$datum}"; } } } $sql .= $values;

Now, before you reach for that barf bag, take another look. This third way does some things the first 2 don't.

  • It checks to make sure there's something there. If one of the values is undefined, it will still generate valid SQL.
  • You always know exactly which column a given value goes with.
  • In fact, everything after creating the hash can be put into a nice little sub so you don't have to look at the foreach.
It takes some getting used to, but I find the third option a lot more maintainable.

Also notice I'm always creating a scalar to hold the SQL, never putting it right into the prepare(). That's to make it easier to print(). I find that I have to do that so often I might as well just plan on it, no matter which style I'm making the SQL with.

SQL is an ugly language (why does update have nice name=value syntax, while insert doesn't?) and it just gets uglier as you add code to $dbh->quote() all your values and wrap things in the eval{} blocks that make your code robust enough to handle a failed transaction. Automatically generating the SQL at least cuts out some typo problems.

Replies are listed 'Best First'.
Re: Re: dbi style questions (code, discussion)
by jarich (Curate) on Dec 30, 2001 at 14:51 UTC
    Nice idea. I've some simple improvements to make it more human friendly.
    # some sample data. my %msgdata = ( messageid => $next_messageid, quoteid => $quoteid, body => $q_body, viewed => 0, timestamp_m => "CURRENT TIMESTAMP" ); # you can do a sort here if you care about the order # of the keys. my @keys = keys %msgdata; # uncomment this if you want to exclude all pairs # that have no value. #@keys = map {defined($msgdata{$_})? $_ : ()} @keys; # get all my values in the same order as my keys. my @values = @msgdata{@keys}; # replace any undefined values with "NULL". # Comment this out if you're excluding these above. # Note: if you're using $dbh to quote your values # then use $dbh->quote($_) for the true option @values = map {defined($_)? $_ : "NULL"} @values; # my sql statement. my $sql = "INSERT INTO msg ( " . join (",\n", @keys) . ") " . "VALUES " . join (",\n", @values); # or with DBI: (but not so good for printing) # you don't need to use the $dbh->quote above if # you use this. my $sql2 = $dbh->do( "INSERT INTO msg ( " . join (",\n", @keys) . ") " . "VALUES " . substr(("?,\n" x @values), 0, -2), undef, @values);
    I like DBI. But a friend who doesn't like his SQL to mess up his code puts all of the queries into a module and then calls them functionally. eg:
    insert($table, @keys, @values); # or possibly insert($table, $data_ref); # and definately $data_ref = select($table, $conditions_ref, $desired);
    That works as well of course.


      # my sql statement. my $sql = "INSERT INTO msg ( " . join (",\n", @keys) . ") " . "VALUES (" . join (",\n", @values) . ")";

      I like it. Using join() this way simplifies the SQL generation a lot.

      Unfortunately, there's no real help for manually $dbh->quote()ing values that need it - we use DB2 at work, and quoting a number gives a SQL error (grrrrrrrr). That's my #1 complaint about the thing.

      We'll probably move to something between what your friend does, and the inline code. Generate the actual SQL in a module, but maintain control of it's execution inline - otherwise you're borked with transactions, and I hate passing $dbh's around all over the place.

        Unfortunately, there's no real help for manually $dbh->quote()ing values that need it - we use DB2 at work, and quoting a number gives a SQL error (grrrrrrrr). That's my #1 complaint about the thing.

        If you use placeholders similar to this or like in the insert_hash() example in the DBI documentation you might not have that problem. (Update: Or like in jarich's sql2 string above :) (Update: Yes, its usually the optimizer that doesn't have to parse your statement again if the database has a SQL cache, but placeholders are still worthwhile IMO, even on a database like MySQL)