Re: Using MySQL table's default values upon insert

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.

Comment on Re: Using MySQL table's default values upon insert Download Code

Replies are listed 'Best First'.
Re^2: Using MySQL table's default values upon insert by Zettai (Acolyte) on Jan 30, 2010 at 20:25 UTC
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]	[reply] [d/l] [select]
Re^3: Using MySQL table's default values upon insert by roboticus (Chancellor) on Jan 30, 2010 at 23:23 UTC
You'd use 'NA' instead of 'default_blah'. Yeah, I munged up the quotes there. I'll update the node. ...roboticus	[reply]