I strictly use $dbh->quote() to quote data before inserting or selecting it with MySQL using DBI.

Get rid of that. Use placeholders. Really. Perl and DBI ain't PHP.

Alexander

Additional hint: MySQL has some documented, but non-obvious "features". NULL handling is one of them.

Side node: Oracle also has the nasty habit of treating NULL and empty strings equally or converting one to the other -- not always, but always when you don't expect it.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

Oracle also has the nasty habit of treating NULL and empty strings equally or converting one to the other -- not always, but always when you don't expect it.

With VARCHAR2 there is no "treating equally or converting".

An empty VARCHAR2-string IS a NULL - always.

but is there anyway to override this behaviour?

Yes, don't pass undef!

$dbh->quote($x // '')
[download]

moggs:

Perhaps you could use placeholders like so:

insert into foo_table (key, val1,         val2)
values                (?,   isnull(?,''), isnull(?,''))
[download]

or perhaps adapt that to use the $dbh->quote() calls you're currently using.

Update: I don't use MySQL, so I don't know the functional equivalent, so I just guessed.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

sub quotestr {
    my( $dbh, $str ) = @_;
    $str = ''
        if  ! defined $str;
    return $dbh->quote( $str );
}
[download]

Yes, I realize that you asked for $dbh->quotestr($str) and I gave you quotestr($dbh,$str), but surely you aren't restricted from doing the latter.

Yes, there actually are ways to monkeypatch extra methods into somebody else's class. But such really isn't a good idea. Though, DBI is such an all-singing/all-dancing thing, that it is extremely common for people to produce objects that delegate so that they can do the bazillions of things a DBI object is expected to do while also doing a bunch of their own custom things (often including customizations to the things DBI does).

Lots of different routes used for such interface compositions: defining a new class that inherits from DBI -- often several layers of such -- either somewhat sanely with an inside-out object or sometimes by just defining a new class name and new methods and using a re-bless()ed object; more sanely using an object that "has a" DBI attribute and delegates to it or even just provides an accessor so you do $db->dbi()->some_dbi_method_here(); etc.

"Wrapping" DBI-related objects is just so rampant, that I actually discourage wrapping DBI yourself. Combining multiple wrappings is often ugly (though not as ugly as trying to use two utility classes that each want to inherit from DBI). So by avoiding your own wrapping, you can avoid some ugliness when you eventually run across some existing module you want to use that insists on wrapping DBI objects. For more blather trying to convince you of this wisdom, you could search for dependency injection.

So, if you just have the one extra method you want so far, then you can write a procedural module that provides that. You can even have the module load DBI for you so you don't have to have extra 'use' lines:

use My::DBI qw< quotestr >;  # Also does 'use DBI;'

my $dbh = DBI->connect( ... );
my $data = $dbh->selectall_arrayref(
    sprintf $sql, map { quotestr($dbh,$_) } @params );
[download]

Don't let the rampant "use placeholders!" meme get to you too much. It seems that few people have noticed many of the bugs and problems that can come with placeholders and/or realize that the claimed performance benefits just don't really materialize or matter in a lot of situations. If you have some other scheme that ensures that $dbh->quote(...) always gets applied, that is what is important.