DBI: using the default for a column?

[Eli the Bearded]

First some code:

use DBI;

$dbh = DBI->connect(
"dbi:mysql:database=$database",
$dbuser, $dbpass
);

print "Drop it\n";
$dbh->do('
DROP TABLE IF EXISTS `foobar`;
');
print "Create it\n";
$dbh->do(q(
CREATE TABLE `foobar` (
`id` int AUTO_INCREMENT,
`one` int DEFAULT 17,
`two` varchar(31) DEFAULT 'number',
PRIMARY KEY (`id`)
);
));


print "Insert row 1 via do(), NULL DEFAULT DEFAULT\n";
$dbh->do('
INSERT INTO `foobar` VALUES (NULL, DEFAULT, DEFAULT);
');

$sth = $dbh->prepare('
INSERT INTO `foobar` VALUES (?, ?, ?);
');

print "Insert row 2 via prepared statement, undef undef undef\n";
$sth->execute( undef, undef, undef );

print "Insert row 3 via prepared statement, undef 34 DEFAULT\n";
$sth->execute( undef, 34, DEFAULT );

print "Insert row 4 via prepared statement, undef DEFAULT 'Fault'\n";
$sth->execute( undef, DEFAULT, 'Fault' );

print "What have we got? SELECT *\n";
$sth = $dbh->prepare('
SELECT * FROM `foobar`;
');
$sth->execute();
$tbl_ary_ref = $sth->fetchall_arrayref;
for $r (@$tbl_ary_ref) {
print "Row: @$r\n";
}
__END__

Output:

Drop it
Create it
Insert row 1 via do(), NULL DEFAULT DEFAULT
Insert row 2 via prepared statement, undef undef undef
Insert row 3 via prepared statement, undef 34 DEFAULT
Insert row 4 via prepared statement, undef DEFAULT 'Fault'
DBD::mysql::st execute failed: Incorrect integer value: 'DEFAULT' for column 'one' at row 1 at test-default line 41.
What have we got? SELECT *
Row: 1 17 number
Row: 2
Row: 3 34 DEFAULT

I'd like to be able to use the column defaults in a Mysql table via Perl
DBI. Ideally, I'd have some flag that makes binding "undef" work as
DEFAULT, but an explicit bind value (either in ->bind_param() or
->execute()) would be okay.

I see _nothing_ about using column defaults in my (c)2000 _Programming
the Perl DBI_ by Alligator Descartes[*] and Tim Bunce, but okay, that's
an old book. But I also see nothing about it in 'perldoc DBI' or
'perldoc DBD:mysql' either. Nor does a quick grep of the module turn
up ideas.

Am I out of luck, or am I going about this wrong?

Elijah
------
[*] The O'Reilly bio says "Alligator Descartes is not his real name"

[Rainer Weikusat]

[...]

> I'd like to be able to use the column defaults in a Mysql table via Perl
> DBI. Ideally, I'd have some flag that makes binding "undef" work as
> DEFAULT, but an explicit bind value (either in ->bind_param() or
> ->execute()) would be okay.
>
> I see _nothing_ about using column defaults in my (c)2000 _Programming
> the Perl DBI_ by Alligator Descartes[*] and Tim Bunce, but okay, that's
> an old book. But I also see nothing about it in 'perldoc DBI' or
> 'perldoc DBD:mysql' either. Nor does a quick grep of the module turn
> up ideas.
>
> Am I out of luck, or am I going about this wrong?

I'd say the latter. The default value will be used for a column if no
other value was provided. And 'undef' is a value, namely, the SQL null
value. You'll have to omit the columns you want default values for from
the insert.

[Eric Pozharski]

with <eli$22091...@qaz.wtf> Eli the Bearded wrote:

_Disclaimer_ I don't MySQL, SQLite is enough for me. But,..

> First some code:
>
> use DBI;

*SKIP*

> print "Insert row 1 via do(), NULL DEFAULT DEFAULT\n";
> $dbh->do('
> INSERT INTO `foobar` VALUES (NULL, DEFAULT, DEFAULT);
> ');
>
> $sth = $dbh->prepare('
> INSERT INTO `foobar` VALUES (?, ?, ?);
> ');
>
> print "Insert row 2 via prepared statement, undef undef undef\n";
> $sth->execute( undef, undef, undef );
>
> print "Insert row 3 via prepared statement, undef 34 DEFAULT\n";
> $sth->execute( undef, 34, DEFAULT );
>
> print "Insert row 4 via prepared statement, undef DEFAULT 'Fault'\n";
> $sth->execute( undef, DEFAULT, 'Fault' );

For this to compile, either 'DEFAULT' is Perl's constant sub (so to
speak) or you do 'no warnings' for real (boilerplate isn't omitted for
brevity).

*SKIP*

> Insert row 1 via do(), NULL DEFAULT DEFAULT
> Insert row 2 via prepared statement, undef undef undef
> Insert row 3 via prepared statement, undef 34 DEFAULT
> Insert row 4 via prepared statement, undef DEFAULT 'Fault'
> DBD::mysql::st execute failed: Incorrect integer value: 'DEFAULT'
> for column 'one' at row 1 at test-default line 41.

My understanding is, this isn't DBD::mysql what issues devastating blow.
DBD::mysql just reports how MySQL has failed.

> What have we got? SELECT *
> Row: 1 17 number
> Row: 2
> Row: 3 34 DEFAULT
>
> I'd like to be able to use the column defaults in a Mysql table via Perl
> DBI. Ideally, I'd have some flag that makes binding "undef" work as
> DEFAULT, but an explicit bind value (either in ->bind_param() or
> ->execute()) would be okay.

Now (also disclaimer), how should I put it?

* There is no hope to support what you want by DBI. DBI just doesn't
care about each and every feature, quirk, or whatever of underlying
SQL engine.

* AIUI, "perldoc DBD::mysql" has been studied extensively to no avail
already. If backends to MySQL from other ecosystems (especially
C-land) do stuff like this then there is hope -- DBD::mysql is lagging
behind (or it might be dead, because upstream of MySQL?).

* Claiming 'undef' (of perl) for 'DEFAULT' of SQL is not feasable.
Because 'undef' is already taken for 'NULL' of SQL. Now, I can see as
desirable, probable, hopeful some kind of pseudo binding that would
translate into 'INSERT INTO foobar VALUES (NULL, 34, DEFAULT )' but...

* Then you can go like this instead:

$dbh->prepare('INSERT INTO foobar VALUES (?, ?, DEFAULT)');

Because binding is for inserting literal values and not sprintfing
'quote's (or forbiden interpolation). Thus such pseudo binding
somewhat defeats purpose.

*CUT*

--
Torvalds' goal for Linux is very simple: World Domination
Stallman's goal for GNU is even simpler: Freedom

[Rainer Weikusat]

Eric Pozharski <why...@pozharski.name> writes:
> with <eli$22091...@qaz.wtf> Eli the Bearded wrote:

[...]

> * Then you can go like this instead:
>
> $dbh->prepare('INSERT INTO foobar VALUES (?, ?, DEFAULT)');
>

The same can be achieved by omitting the third column or by using an
explicit column list and omitting the columns supposed to be set to their
default values.