DBD::SQLite::st execute failed: datatype mismatch

Question

Here's a snippit of perl code:

sub insert_timesheet {
    my $dbh = shift;
    my $entryref = shift;
    my $insertme = join(',', @_);
    my $values_template = '?, ' x scalar(@_);

    chop $values_template;
    chop $values_template; #remove trailing comma

    my $insert = "INSERT INTO timesheet( $insertme ) VALUES ( $values_template );";
    my $sth = $dbh->prepare($insert);
      debug("$insert");
    my @values;
    foreach my $entry (@_){
        push @values, $$entryref{$entry}
    }
      debug("@values");
    my $rv = $sth->execute( @values ) or die $dbh->errstr;
      debug("sql return value: $rv");
    $dbh->disconnect;
}

I'm seeing the error

DBD::SQLite::st execute failed: datatype mismatch at timesheet line 85.

The value of $insert:

[INSERT INTO timesheet( idx,Start_Time,End_Time,Project,Ticket_Number,Site,Duration,Notes ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? );]

Here are @values:

[null '1270950742' '1270951642' 'asdf' 'asdf' 'adsf' 15 '']

Here's the schema of 'timesheet'

timesheet( idx INTEGER PRIMARY KEY AUTOINCREMENT, Start_Time VARCHAR, End_Time VARCHAR, Duration INTEGER, Project VARCHAR, Ticket_Number VARCHAR, Site VARCHAR, Notes VARCHAR)

Here's how things line up:

----
Insert Statement
Schema
@values
----

idx
idx INTEGER PRIMARY KEY AUTOINCREMENT
null:  # this is not a mismatch, passing null will allow auto-increment.

Start_Time
Start_Time VARCHAR
'1270950742'

End_Time
End_Time VARCHAR
'1270951642'

Project
Project VARCHAR
'asdf'

Ticket_Number
Ticket_Number VARCHAR
'asdf'

Site
Site VARCHAR
'adsf'

Duration
Duration INTEGER
15

Notes
Notes VARCHAR
''

... I can't see the data-type mis-match.

Answer 1

Try reducing your code down to smaller and similar chunks, to see how small you can get it and still exhibit the error (or alternatively, see at what step the problem disappears). You could remove a lot of code simply by calling execute() with a bare string query, and then start removing fields from the insert statement.

I'm a little suspicious of the contents of @values that you're printing -- it appears that there are quotes around every value, even the integers, which will definitely not match an integer type.

Answer 2

The problem was with inserting a 'null' for idx. Not inserting 'idx' explicitly is OK, the auto-increment takes care of that...

Shoulda broken that down earlier.