* Jim Mahoney ([EMAIL PROTECTED]) [010402 22:03]:
>
> I recently spent more time than I'd like to admit
> trying to understand a bug in my DBI program, and
> now that I understand what's going on, I'm writing
> to suggest that the documentation show better how the type
> conversion from perl scalars to SQL data types works in
> the DBI - because it sure didn't do what I expected.
>
> Here's what happened.
>
> First I set up a table like this:
>
> mysql> CREATE TABLE data (id VARCHAR(32), size INT);
>
> Then, using a database handle $dbh
>
> my $dbh = DBI->connect(...);
>
> I add a row to the table, like this.
>
> my $id = "S23";
> my $size = 1.2;
> # ... other code here ....
> my $sth = $dbh->prepare("INSERT INTO data VALUES (?, ?)");
> $sth->execute( $id, $size );
With most DBDs you can also do:
use DBI qw( :sql_types );
...
my $sth = $dbh->prepare("INSERT INTO data VALUES (?, ?)");
$sth->bind_param( 1, $id, SQL_VARCHAR )
$sth->bind_param( 2, $size, SQL_INTEGER );
$sth->execute();
Which bind the values to the placeholders using specific
datatypes. This is a cue to the DBD to do the proper type of
quoting. You can find this in the DBI documention under 'Data Types
for Placeholders'.
Chris
--
Chris Winters ([EMAIL PROTECTED])
Building enterprise-capable snack solutions since 1988.