elmic11111 has asked for the wisdom of the Perl Monks concerning the following question:

I have a script that reads a file and puts the data into my log database. The log file data is setup as KEY=>VALUE and as I read it I split it and put it in a hash array called sqlinsert.

I prepare the insert query in advance using this code:

    my $fields = join(', ', @dbfields);
    my $places = join(', ', ('?') x @dbfields);
    my $insertrow = "INSERT into $table_webreporting ($fields) values 
+($places)";
    $insertrow = $dbhc->prepare($insertrow);

[download]

I then insert it with this code:

    $insertrow->execute(@sqlinsert{@dbfields});

Works fine, untill recently when my log files were changed. In the new log file, each line of the log file doesn't always have all the fields. It only has the ones need for that transaction. This present a problem since I now have empty values. I tried setting up a foreach rounting like this hoping setting undef for the missing values would work, but perl complains when it does the excute about stuff not being defined:

    foreach my $temp (@dbfields) {
        $sqlinsert{$temp} = undef unless ($sqlinsert{$temp});
    }

[download]

What I like it to do is put a null value in for any field that doesn't have a value.

Replies are listed 'Best First'.
Re: DBI and Hash
by runrig (Abbot) on Feb 13, 2012 at 21:36 UTC
    Perl complains how? The value for a hash key that doesn't exist is undef, which when inserted should be null, which sounds like what you want.
[reply]
Re: DBI and Hash
by ww (Archbishop) on Feb 13, 2012 at 21:42 UTC
    What are acceptable null values to your database? You need to determine that before you start transforming fields that are not present (in any given line of the log file).

    Could you, for example, get away with:

    foreach my $temp (@dbfields) {
    $sqlinsert{$temp} = "DUMMYDATA" unless ($sqlinsert{$temp});

    [download]

    Of course, if your DB fields are data-type constrained, you'll have to do your substitution on a per-key basis -- to ensure that INT fields get a dummy INT value, and so on ( ...and it'll get really tricky if there's a BOOL field. :-) )

    OTOH, who controls the format of the log files? If you do, perhaps you'll find it easier to tackle that end of your puzzle, supplying an appropriate dummy value to each empty field.

    Update: recast first question.

[reply]
[d/l]
      Well I feel dumb, I went and re-ran the code to get the error to paste here and didn't get an error. Now I need to go figure out why. Should have pasted it here the first time.
[reply]

        Just for what it's worth, the default value for an inserted row is a NULL value. (Unless overridden, so I guess I should have said "default default value.") Assume a table named test of columns foo, bar, baz:

        INSERT INTO test (foo, bar) VALUES('fred', 'barney');

        [download]

        Now, the column baz should be NULL. In other words, if you want to insert a NULL into a database, you can just omit it from the list of columns.

        BTW, your foreach sets the numeric value zero and the empty string to NULL, too. I doubt you want that. You should be using unless exists $sqlinsert{$temp} instead.

        Oh, and dummy values suck.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom