Re: dbi style questions (code, discussion)

[uwevoelker]

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

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

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

uwevoelker