How to do simple select with Net::MySQL

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Aaargh! I cannot figure out how to pass a variable into my MySQL query in Perl. I am using Net::MySQL
This works:
$mysql->query(q{ select * from workspace where name = 'my_workspace'});
this does not:

 my $workspace = "my_workspace";
  $mysql->query(q{ select * from workspace where name = '$workspace' }
+);
[download]

I remove the single quotes, etc and the query doesn't work. Thanks for any examples! -Michael P.S. My method of figuring the count is rather innefficient as well. Any pointer welcome.

  $mysql->query(q{ select * from workspace where name = 'my_workspace'
+ });
  my $i;
my $record_set = $mysql->create_record_iterator;
while (my $record = $record_set->each) {
            $i++;
    }
 if ($i) {print "found one!\n";}
[download]

20040915 Edit by broquaint: Changed title from 'Now to do simple select with Net::MySQL'

20040915 Edit by castaway: Changed title from 'Now to do simple select with Net::MySQL'

Comment on How to do simple select with Net::MySQL Select or Download Code

Replies are listed 'Best First'.
Re: Now to do simple select with Net::MySQL by chromatic (Archbishop) on Sep 15, 2004 at 00:57 UTC
MySQL has a count function. You can simplify your second query to: `my $sth = $dbh->prepare( "SELECT COUNT(*) FROM workspace WHERE name = +?"); $sth->execute( $workspace ); my ($count) = $sth->fetchrow_array();` [download] I'm not sure what your `$mysql` contains, so I can't give you much more detail, but this is the standard DBI code.	[reply] [d/l] [select]
Re^2: Now to do simple select with Net::MySQL by doowah2004 (Monk) on Sep 15, 2004 at 05:12 UTC
Yup, he could also use $rows = $sth->rows(); to simplify the second query. This is the syntax for DBI.pm, I am not sure if the syntax would be the same for Net::MySQL. Cameron	[reply]
Re: Now to do simple select with Net::MySQL by BUU (Prior) on Sep 15, 2004 at 00:58 UTC
While the above two answers are technically correct, they do not adress the underlying problem. Which is, you shouldn't try to interpolate your perl variable directly in to your sql string. This is bad. What you should do is use a technique called "place holders". A quick example, before you had: `$sth->prepare( qq{ select foo from bar where baz = $qux } ); $sth->execute;` [download] You would know do: `$sth->prepare( qq{ select foo from bar where baz = ? } ); $sth->execute($qux);` [download] This provides many benefits, some of which are saftey and speed. For far more examples and usage of place holders, see the DBI documentation and What are placeholders in DBI, and why would I want to use them?	[reply] [d/l] [select]
Re: Now to do simple select with Net::MySQL by borisz (Canon) on Sep 14, 2004 at 23:49 UTC
Just use `qq` instead of `q`. from your example: `$mysql->query(qq{ select * from workspace where name = '$workspace' }) +;` [download] qq{...} is the same as "..." while q{...} is like '...' Boris	[reply] [d/l] [select]
Re: Now to do simple select with Net::MySQL by Velaki (Chaplain) on Sep 15, 2004 at 00:38 UTC
Looks like a quoting issue. Take a look at how the q and qq operators handle text. What you want to do is interpolate (substitute) the value of $workspace into the string. q will quote verbatim, whereas qq will permit interpolation. Try changing `q{select * from workspace where name = '$workspace'}` [download] to `qq{select * from workspace where name = '$workspace'}` [download] Hope that helped, -v "Perl. There is no substitute."	[reply] [d/l] [select]
Re^2: Now to do simple select with Net::MySQL by Anonymous Monk on Sep 15, 2004 at 05:53 UTC
Interpolating variables in a SQL query is a NO-NO. Use placeholders. Don't give bad advice.	[reply]
Re^3: Now to do simple select with Net::MySQL by hmerrill (Friar) on Sep 15, 2004 at 11:44 UTC
I've never before heard that interpolating variables in SQL is bad advice - why is that bad advice?? It's true that using placeholders is desirable for a number of reasons, but interpolating variables in SQL is not bad.	[reply]
Re: Now to do simple select with Net::MySQL by Anonymous Monk on Sep 15, 2004 at 04:01 UTC
Adding the second q did the trick. Thank you. And thanks for the count() and the place holder advice. I used Net::MySQL which doesn't appear to have as clean of support for these last two features as DBI has. Next time I go with DBI! Thanks again! You all saved my... ahem.. -Michael	[reply]