in reply to $dbh->quote(..., SQL_INTEGER)

1. is this a bug in $dbh->quote()

This cannot be answered without referring to the documentation. There it says:

As a special case, the standard numeric types are optimized to return $value without calling type_info.

The answer is therefore: no, this is expected documented behaviour, not a bug. You could however argue that it is a wart - having gone to the trouble of providing a quoting mechanism, it seems a bit unhelpful to exempt these types as a special case.

2. is there exists more correct way to insert numbers into SQL

All I can suggest is to create your own quoting method. Something like:

sub quote_number { my($dbh, $value) = @_; if (defined $value) { # force to number return $value + 0; } else { return $dbh->quote($value); } }

Note that as written, that will give a warning (assuming they're turned on) on anything that doesn't look enough like a number; you may prefer to treat that more severely (die rather than warn) or less severely (don't warn at all); similarly the above code will permit decimals and negative numbers: if you want to be more restrictive than that you'll need to add some code to test either mathematically or with regexps, eg:

sub quote_integer { my($dbh, $value) = @_; if (defined $value) { # pattern approach die "Not an integer" unless $value =~ /^[-+]?\d+\z/; # maths approach die "Not an integer" unless $value == int($value); return $value + 0; } else { return $dbh->quote($value); } }

Hugo

Replies are listed 'Best First'.
Re^2: $dbh->quote(..., SQL_INTEGER)
by powerman (Friar) on Sep 03, 2004 at 17:47 UTC
    Thanks for your answer!

    What do you think about this idea: redefine $dbh->quote() by this logic:
    1. check perl internals (SV) to find is my param a number or string
    2. if it's a number - return something like yours quote_integer(), else return original $dbh->quote()
    This way we will be able to control $dbh->quote() bevaviour this way:
    $dbh->do("INSERT ... SELECT ... WHERE a=? LIMIT ?", undef, $a, 0+$limit);

      Checking the internals of an SV is almost always the wrong thing to do.

      If you're happy to pass 0+$limit without checking for problems, you might as well interpolate that straight into the SQL rather than missing with quote().

      Hugo