Perl DBI: problems inserting data to a table

[homeveg]

Dear PerlMonks,

I am currently trying to use a perl DBI module to store my data in dbi:DBM:mldbm=Storable database.

Original data is loaded from a text file using RegExps. The resulting table contains about 5k rows and several columns and does not have any missing values.

My problem is that while I am parsing the data to a database, only some rows from my original table are converted to a DB entree and, unfortunately, I don't see the reason for that.

I will be very grateful for any help or ideas how to overcome this obstacle.

Here is simplified code I am having problems with:

#/local/bin/perl -w

use DBI;
use strict "vars";

#initialize database
my $dbh = DBI->connect('dbi:DBM:mldbm=Storable');
$dbh->{RaiseError} = 1;

#create table to store data
my $sth = $dbh->
    prepare("CREATE TABLE hist_mod_score (Index INTEGER, Col1 char(30)
+, Col2 char(7),Col3 char(4) )");
    $sth->execute;
    
    my $sql = 'INSERT INTO hist_mod_score (Index, Col1, Col2,Col3) VAL
+UES (?,?,?,?)';
    $sth = $dbh->prepare($sql) or die $dbh->errstr;
    
# open input file containing raw data
open(INPUT, "Input.txt") || die "Can't open input file for reading!:$!
+\n";
    
my $index=0;
while (<INPUT>) {
        for my $chank  (split/\n/) {
        $chank =~/RegExp to extract data from string/;
        
        my $Col1 = $1;
        my $Col2 = $2;
        my $Col3 = $3;        
        my @arguments = ($index, $Col1,$Col2,$Col3);

        # check if regexp works fine
        print STDERR join("\t",@arguments),"\n";
        
        #parse arguments to a DB
        $sth->execute(@arguments) or die $sth->errstr;
        $index++;
    }
}   
$sth->finish;
close (INPUT);

# check if all data is parsed correctly

$sth = $dbh->prepare("
             select *
             from hist_mod_score
             ");
$sth->execute();
$sth->dump_results if $sth->{NUM_OF_FIELDS};

In the result, I've got only 107 from 4800 rows parsed and I did not get any warning or error message.

Thanks in advance for your help!

UPDATE

I have found a problem - in my case it was the Storable driver. I created new SQLite DB and my code works now.

Thanks to everybody for your help!

[Laurent_R]

Are the rows that are inserted into the table correct and complete (i.e. with all columns)? Did you confirm that the regex splits the data as you expect?

[homeveg]

Yes, all rows are complete. To check RegExp, I was printing each new line and after parsing the data to the DB directly was quering for a new entry using unique ID (index). To check each parsed line I use the following code:

# Display RexExp results
print STDERR join("\t",@arguments),"\n";

<here add all data from @argumens to a DB (as described in the origina
+l post)>

# check if data parsed successfully
my $sql2 = 'SELECT *
    FROM hist_mod_score
    WHERE Counts=?';
    $sth = $dbh->prepare($sql2) or die $dbh->errstr;
    $sth->execute($index) or die $sth->errstr;

# dump fetched query results        
    $sth->dump_results if $sth->{NUM_OF_FIELDS};

In the output I've got the following:

0    SM_Th2_1_K27ac    SM    Th2    1    K27ac    Tbx21-1    4.8878613
+2768108e-10
0 rows
1    SM_Th2_1_K27ac    SM    Th2    1    K27ac    Tbx21-2    3.8016699
+2152973e-10
0 rows

...

120    SM_Th2_2_K9me3    SM    Th2    2    K9me3    Tbx21-21    4.1899
[phone]e-10
'120', 'SM_Th2_2_K9me3', 'SM', 'Th2', '2', 'K9me3', 'Tbx21-21', '4.189
[phone]e-10'
1 rows

[homeveg]

I have found the problem. I've replaced a Storable DB driver with a SQLite driver and my code is now working.

my $dbh = DBI->connect('dbi:SQLite:dbname=hist_mod.db');

I am not sure what was the reason to my problems with Storable DB, but for my task it does not meter which type of DB to use.

Thanks to everybody for your help and suggestions

[boftx]

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

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

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;

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.

[mje]

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.

[boftx]

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.

[homeveg]

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

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

Could that be that DB driver just overwriting existing rows when executing INSERT?

[boftx]

Try using just what I wrote before:

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

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".