Sadly, when you prepare an insert statement with a particular set of fields using placeholders, the "default" value for a given field in the mysql table definition will never be applied, ever -- even when the associated variable is undef.

To handle that in a way that does not require database details to be embedded in your perl script, you have to use a flexible method of preparing the statement, so that you can leave out fields when their associated values are empty:

sub add_foo
{
    my @expected_fields = qw/bar blah/;
    my ( @insert_flds, @insert_vals );

    for my $f ( @expected_fields ) {
        my $v = shift;
        next unless ( defined( $v ) and length( $v ));
        push @insert_flds, $f;
        push @insert_vals, $v;
    }
    my $sth = $dbh->prepare_cached( 'insert into foo (' .
        join( ',', @insert_flds ) . ') values (' .
        join( ',', ('?') x @insert_flds ) . ')' );

    $sth->execute( @insert_vals );

    return $dbh->last_insert_id( undef, undef, 'foo_id' ));
}
[download]

Thank you very much for this. This is exactly what I was looking for. Very much appreciated.

my $sth = $dbh->prepare(q[INSERT INTO foo (bar) VALUES(?)];
$sth->execute($bar);
[download]

If you use or die after each statement, you could just as well use the RaiseError => 1 option to DBI's connect.

Zettai:

You can handle it on the perl side, as others have suggested, or on the database side like this:

sub add_foo {
   my ($bar, $blah) = @_;
   
   my $sth = $dbh->prepare('INSERT INTO foo SET '
                            . "bar  = isnull(?, 'default_bar'), "
                            . "blah = isnull(?, 'default_blah')) "
        or die "Couldn't prepare statement: " . $dbh->errstr;

    $sth->execute($bar, $blah)
        or die "Couldn't execute statement: " . $sth->errstr;
    
    my $id = $dbh->last_insert_id(undef, undef, qw(foo id))
        or die "no insert id?";
    
    return $id;
}
[download]

...roboticus

UPDATE: I changed the final quote on the line containing default_bar from a single quote to a double quote.

Does this mean that I literally type in 'default_blah' and it will insert "NA" or do I need to be putting 'NA' where 'default_blah' is?

I was hoping for a solution so that if I didn't know what the default value was I could still write a query that would insert the default value if the user hadn't provided one.

- Sorry didn't see the solution offered by graff below before I posted this. Is there a reason why you have used a combination of " and '?

my $sth = $dbh->prepare('INSERT INTO foo SET '
                            . "bar  = isnull(?, 'default_bar'), '
                            . "blah = isnull(?, 'default_blah')) "
[download]

Must admit the mixing of " and ' characters is confusing and when I plug it into my code as is I get some errors.

Bareword found where operator expected at line 442, near ". "blah
"
  (Might be a runaway multi-line "" string starting on line 441) (#1)
    (S syntax) The Perl lexer knows whether to expect a term or an ope
+rator.
    If it sees what it knows to be a term when it was expecting to see
+ an
    operator, it gives you this warning.  Usually it indicates that an
    operator or delimiter was omitted, such as a semicolon.

        (Missing operator before blah?)
Unquoted string "blah" may clash with future reserved word at line 442
+ (#2)
[download]

Line 442 is:

. "blah = isnull(?, 'default_blah')) "
[download]

You'd use 'NA' instead of 'default_blah'.

Yeah, I munged up the quotes there. I'll update the node.

...roboticus

In my application I have a sub that read the user input and creates a hash ref that includes only the fields with a non empty value, then I build the insert.

my $record_ref = {
    bar => 'barbar',
};

# Check if ID field is present, add it, if not
#if ( ! exists $record_ref->{id} ) {
#    $record_ref->{id} = undef;
#}
# Not needed for mysql.

# Transform hash into two arrays
my @fields = keys %{$record_ref};
my @values = map { $record_ref->{$_} } @fields;

# code stolen from the IBPerl module examples ;)
my $sql = "INSERT INTO foo ("
               . join( ',', @fields ) . ')
              VALUES (' . ( '?,' x $#fields ) . "?)";

print "sql=", $sql, "\n";
[download]

Update:
This is (also) a solution based on mysql's default mechanism, that is general enough to work with other rdbms's.

Update 2: Commented out the code regarding the ID column

You can assign default value to $blah in Perl code before execute.

$blah="NA" unless defined $blah;
[download]

Thanks Zwon, this is a nice and easy solution if I know what the default value is in the database.