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

When dealing with parameter passing in DBI, it's often best to use '?' style replacements in the DBI engine. Most (*grumble*FreeTDS*grumble*) of the DBD drivers support this.

So, something like this is what I normally do:

my @values = ( 'SomeValue', 5 );
my $dbh= DBI->connect( $DSN, $user, $pass );
my $SQL = "SELECT * FROM mytable WHERE field1=? LIMIT ?";
my $sth = $dbh->prepare($SQL);
$sth->execute( @values );

[download]

The 'execute" method of a statement handle expects a standard list, so this is acceptable as well: 

$sth->execute( 'SomeValue', 42 );

[download]

The nice thing about using the bind parameters is that DBI will automagically quote your values for you. You are responsible for data validation, as is true with most applications. Your solution of using a regular expression to perform data validation is how I usually do it.

Replies are listed 'Best First'.
Re^2: $dbh->quote(..., SQL_INTEGER)
by iburrell (Chaplain) on Sep 02, 2004 at 19:09 UTC
    LIMIT clauses can be different with placeholders. The number is sometimes considered not a normal data value. Some databases, like MS-SQL, don't support placeholders in the LIMIT clause.

    With PostgreSQL and DBD::Pg, the placeholders are substituted on the client. Without bind_param types, they use strings which are converted for all normal types. This doesn't work for LIMIT clause which must be an unquoted number. Supposedly, setting the type with bind_param will work: 

    my $sth = $dbh->prepare("SELECT * FROM big_table LIMIT ?");
$sth->bind_param(1, $value, SQL_INTEGER);
$sth->execute(10);

    [download]
    I don't know what will happen when the server-side parameters are implemented.
[reply]
[d/l]

In Section Seekers of Perl Wisdom