At first I would like to thank markjugg for the tip with DBIx::Abstract.

I usually use the other INSERT syntax (similar to UPDATE, with SET).

Here comes an example: 
sub db_store_event {
    my $id  = shift || 0;
    my $ret = $id;
    if ($id > 0) {
        # update
        my $hash   = db_get_event($id);
        my $event  = impand_event(); # konvertiere Uhrzeit und Datum
        my @update = ();
        foreach my $key (keys %$hash) {
            if (defined $event->{$key} and ($hash->{$key} ne $event->{
+$key})) {
                push @update, $key.'='.$dbh->quote($event->{$key});
            }
        }
        if (scalar @update > 0) {
            db_do("UPDATE ".$config->{event_table}." SET ".join(', ', 
+@update)." WHERE id=$id");
        }
    } else {
        # insert
        my $event = impand_event(); # konvertiere Uhrzeit und Datum
        my   @dat = ();
        foreach my $key (keys %$event) {
            push @dat, $key.'='.$dbh->quote($event->{$key}) if $event-
+>{$key};
        }
        my ($err, $sth) = db_sth("INSERT INTO ".$config->{event_table}
+." SET ".join(', ', @dat));
        $ret = $sth->{'mysql_insertid'} or die "no db_id";
    }
    return $ret;
}

[download]
The routine impand_event() konverts the cgi parameter to an hashref with keys like the fields in the database and does some conversion (for date and time). 
sub impand_event {
    my ($datum, $zeit);
    my %event = ();

    foreach my $key ($cgi->param) {
        if ($key =~ m/event\.(\w+)/) { $event{$1} = $cgi->param($key);
+ }
    }

    $datum = $event{datum_beginn};
    if ($datum) { $event{datum_beginn} = date_to_sql($datum); }

    $datum = $event{datum_ende};
    if ($datum) { $event{datum_ende} = date_to_sql($datum); }

    $zeit = $event{zeit_beginn};
    if ($zeit) { $event{zeit_beginn} = time_to_sql($zeit); }

    $zeit = $event{zeit_ende};
    if ($zeit) { $event{zeit_ende} = time_to_sql($zeit); }

    return wantarray ? %event : \%event;
}

[download]
Sorry for the german comments, but these are real lines of code.

uwevoelker

In reply to Re: dbi style questions (code, discussion) by uwevoelker
in thread dbi style questions (code, discussion) by deprecated

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.