in reply to Re: dbi style questions (code, discussion)
in thread dbi style questions (code, discussion)

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);

[download]
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);

[download]
That works as well of course.

Jacinta

Replies are listed 'Best First'.
Re: Re: Re: dbi style questions (code, discussion)
by edebill (Scribe) on Jan 01, 2002 at 01:10 UTC 

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

    [download]

    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.

[reply]
[d/l]
      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)

[reply]

        If you use placeholders similar to this or like in the insert_hash() example

        Yes, placeholders work, but I consider them worse than manually quoting what needs to be quoted. There's no benefit to doing a single prepare then feeding in parameters to the $sth when you only do a given query once, and Manually quoting the right values also acts as a reminder to think about them - and things like input validation.

        I guess what I'm saying is that there's really a limit to what can be done profitably through automation, at least in this case.

[reply]

In Section Seekers of Perl Wisdom