I am curious about what you are doing...defaults values (like tinyint(1) ;default -> 0) doesn't look like SQL to me. A do statement that creates a table with default values would look something like below. The default value is a property of the table when it is created:
$dbh->do ("CREATE TABLE ScoreCard
( id integer PRIMARY KEY AUTOINCREMENT,
Url varchar(80) DEFAULT '',
Score integer DEFAULT 0,
Club varchar(30) DEFAULT ''
);
");
When executing a pre-prepared insert statement with place holders, '' would be an empty string, which is not an integer. In order to get the Score default of zero, I figure that variable needs to be undef. Without seeing code, I'm not sure what you have.
I should mention a nice feature added in Perl 5.10.
$score //= 0; #set to zero if undef
I often have other Perl validation code before the insert and this feature comes in handy. Often my table defaults wind up essentially being, "if all else fails". Mileage varies a lot.
Update: I just did a quick cut-n-paste for the above CREATE. There are other constraints, like NOT NULL that I often use with varchar(). The main point I wanted to show was the zero default for an integer. That does not look like ";default -> 0"
Update 2:
An attempt to reproduce the OP's problem yields more questions.
What is the "right way" to coerce the DBI into using the DEFAULT value when using placeholders? Here is some runable code. I tried the obvious things like no parm, undef, null but couldn't get SQLite to use the default value. I'm sure I'm missing something obvious. But at least here is some code that can be run and modified...
#!/usr/bin/perl
use strict;
use warnings;
$|=1; #turn STDIO buffering off
use Data::Dump qw(pp);
use DBI;
my $dbfile = "./monk_test.sqlite";
unlink $dbfile if -e $dbfile;
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError =
+> 1})
or die "Couldn't connect to database: " . DBI->errstr;
$dbh->do ("CREATE TABLE ScoreCard
( id INTEGER PRIMARY KEY AUTOINCREMENT,
Score INTEGER DEFAULT 11
);
");
my $insert_row = $dbh->prepare("INSERT INTO ScoreCard(Score)
+
VALUES(?)");
# desired goal: get Score in the DB to be 11 if its not specified.
$insert_row->execute(); #row 1 Score=undef
$insert_row->execute(undef); #row 2 Score=undef
$insert_row->execute(''); #row 3 Score=null string
$insert_row->execute(333); #row 4 Score=333
my $get_all_rows = $dbh->prepare("SELECT * FROM ScoreCard");
$get_all_rows->execute();
my $aref = $get_all_rows->fetchall_arrayref;
print "all rows="; pp $aref;
__END__
all rows=[[1, undef], [2, undef], [3, ""], [4, 333]]
Maybe this is what the OP had in mind?
Update 3: I believe that Corion has got it right! If the INSERT references the column in any way, the table DEFAULT will NOT be used. There is no way to coerce the DBI into using the default value configured at the time of Table creation with a value of undef or any other value. What this means is: if you have an insert statement that can provide a value for a column, you must explicitly provide that default instead of undef.
My typical DB code knows for sure that every column is defined with a known value instead of undef. Again the Perl operator "//=" is very helpful: $value //= $default.
If you have an insert statement with some variable and don't have a valid value for it, the DB will NOT use the default value for that column - you will get undef. The default only comes into play when it is not possible for the INSERT to set that value.
|