SPUG: DBI bind_param in MySQL

[Ivan Heffner]

To kind of follow up on what Colin said, what you are actually sending over 
the pipe to MySQL is:

SELECT 'first_name' FROM bod

which most likely not what you want.  Say you have a list of columns that 
you want to select:

my @columns = qw(
         first_name
         middle_name
         last_name
         suffix
         title
         company
         image_file
);

You can dynamically build your query, but not by using bind params:

my $query = 'select '.join(', ', @columns).' from bod';

You use the bind params in the WHERE clause of you query so that you can 
prepare a query once and execute it multiple times with different values to 
the WHERE section.

$query .= ' where first_name = ? and last_name = ?';

my $sth = $dbh->prepare($query);
my $results = [];

for my $fname (qw(Mike Joe John)) {
         for my $lname (qw(Jones Smith Brown Doe)) {
                 $sth->execute($fname, $lname);
                 my $r = $sth->fetchall_arrayref();
                 push @$results, @$r;
         }
}

__END__


Ivan


At 11/26/2004 02:53 PM, Colin Meyer wrote:
>On Fri, Nov 26, 2004 at 02:16:45PM -0800, Michael R. Wolf wrote:
> >
> > When I try this, I get a literal (e.g. "first_name") in the output,
> > not the value of the field.
> >
> >     my $sth = $dbh->prepare("SELECT ? FROM bod")
> >       or die "Cannot create sth: $DBI::errstr";
> >
> >     $sth->bind_param(1, "first_name");
> >
> >     $sth->execute()
> >       or die "Cannot execute statement: $DBI::errstr";
> >
> > Am I missing something important, or is this feature unavailable with
> > MySQL?
>
>Every relational database that I have used (including MySQL) only lets
>you use placeholders for values, not for identifiers (e.g. column or
>table names).
>
>You could, for example:
>
>   my $sth = $dbh->prepare( <<'End_of_SQL' );
>     SELECT first_name
>     FROM bod
>     WHERE first_name LIKE ?
>   End_of_SQL
>
>   $sth->execute( 'Mi%' );
>
>-Colin.
>_____________________________________________________________
>Seattle Perl Users Group Mailing List
>POST TO: spug-list at mail.pm.org  http://spugwiki.perlocity.org/
>ACCOUNT CONFIG: http://mail.pm.org/mailman/listinfo/spug-list
>MEETINGS: 3rd Tuesdays, Location: Amazon.com Pac-Med
>WEB PAGE: http://seattleperl.org/