in reply to Perl DBI: problems inserting data to a table

I'm going to assume (yeah, right) that this line is not throwing an exception: 

$sth->execute(@arguments) or die $sth->errstr;

[download]

You might be getting bit by the "zero but true" return that DBI uses. That is, DBI can return a value of 0E0 when an error occurs, which evaluates as being true in a Boolean context but is equal to "0" in a numeric context.

I would change your test so it looks for a return value greater than 0 (since that statement should return the number of rows inserted), and if it is not, then print the error message from DBI. That might as least give you a hint to what is going on. 

die $sth->errstr unless $sth->execute(@arguments) > 0;

[download]

You could of course use a print instead of die if you want to capture the output for all rows causing an error to a log file.

It helps to remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.

Replies are listed 'Best First'.
Re^2: Perl DBI: problems inserting data to a table
by mje (Curate) on Nov 21, 2013 at 11:16 UTC
    That is, DBI can return a value of 0E0 when an error occurs

    Where in the DBI pod does it say when execute returns 0E0 there is an error. execute returns either, rows insert/updated/deleted, -1 (rows altered not known), 0E0 (no rows altered), undef (error)

    I think you might be confusing this with the err method which can return '' (empty string) to indicate an informational state and 0 to indicate a warning.

[reply]

      From the DBI POD for "execute":

      "For a non-SELECT statement, execute returns the number of rows affected, if known. If no rows were affected, then execute returns "0E0", which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known, then execute returns -1."

      A simple text search (ctrl-f) will find it.

      It helps to remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.
[reply]

        A simple text search does in deed find what you quoted. I never argued that. The quote however, does not say that when execute returns 0E0 it is an error and that is why I took your comment "That is, DBI can return a value of 0E0 when an error occurs" to be wrong. DBI execute returns undef when an error occurs, 0E0 would mean the execute completed successfully but inserted/updated/deleted no rows.

[reply]
        For a simple insert statement, execute() should never return '0E0'. It will return undef if there's an error, or '1' if a row was inserted (though I would use RaiseError in which case it would die on error). For a 'insert into ... select ... from' type insert statement, it might return '0E0' if zero rows were selected/inserted (which is not an error), or more if rows were inserted, or (again) undef if there was an error.
[reply]
Re^2: Perl DBI: problems inserting data to a table
by homeveg (Acolyte) on Nov 21, 2013 at 13:01 UTC
    Thanks for suggestion. I check it and it did not work, unfortunately. The execution never stops and script runs as before, without throwing any exceptions 
    $sth->execute(@arguments);
die "$sth->errstr" if (!$sth); # trap errors
die "No rows updated" if ($sth eq '0E0'); # trap no insert/update

    [download]
    I am wondering, is there any way to specify which column should be used as a key column containing unique IDs? In the original table, I have 8 columns, in which only values in last 2 are always different, as long as the very first index column I've implemented explicitly. The rest very often could have equal values: 
    123    SM_Th2_2_K9me3    SM    Th2    2    K9me3    Tbx21-24    6.98
124    SM_Th2_2_K9me3    SM    Th2    2    K9me3    Tbx21-25    9.31
125    SM_Th2_2_K9me3    SM    Th2    2    K9me3    Tbx21-26    5.12

    [download]
    Could that be that DB driver just overwriting existing rows when executing INSERT?
[reply]
[d/l]
[select]

      Try using just what I wrote before: 

      print $sth->errstr unless $sth->execute(@arguments) > 0;

      [download]

      What you posted above doesn't capture the actual return value of the 'execute' call. You might need to say "$dbh->errstr" instead of "$sth->errstr".

      It helps to remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.
[reply]
[d/l]
        I tried initially your string with the same result...
[reply]

In Section Seekers of Perl Wisdom