DBI bind_param... is there way to see the final query?

[cmayo]

Hi all,

I'm currently having to modify PERL scripts created by an ex-coworker. I'm unfamiliar with DBI and could use a little help if anyone is so inclined.

The problem I'm having is that he's using bind_parameter in the scripts to substitute placeholders in the SQL with command line arguments, and I'd like to change a statement's WHERE clause from "id = ?" to "id in (?)" to allow a range of possible values to be input from the command line.

#!/opt/csw/bin/perl

# Script Name: test

use DBI;

$dbh = DBI->connect('DBI:mysql:databasename', password);

$query = "SELECT description from sport where id IN (?)";

$table_event_list = $dbh->prepare("$query");

$sports = "7,8";

$rv = $table_event_list->bind_param(1,$sports);

# Execute the query
$table_event_list->execute;

# Loop through the list of sport ID's

while( @row = $table_event_list->fetchrow_array() ) {
        print "SPORT:" . $row[0] . "\n";
}

$table_event_list->finish;

When the statement I'd expect to result from the bind_paramater statement, "SELECT id from sport where id IN (7,8)" is executed from the MySQL monitor, MySQL returns the correct two values:

mysql> select description from sport where id in (7,8);
+------------------+
| description      |
+------------------+
| Girls Basketball |
| Boys Soccer      |
+------------------+
2 rows in set (0.00 sec)

When the PERL code above executes, though, PERL returns only one result:

# ./test
SPORT:Girls Basketball
#

So... I guess my question is less how to use bind_param to subsitute a series of values into an IN clause, and more how I can view the literal result of the bind_parameter statement, but an answer to either would be tremendously helpful.

In other words, I'd really like to be able to see the resultant SQL that's created after the parameters are bound to help in debugging this kind of stuff in general.

Anyone have any suggestions?

Thanks in advance,
Chuck

 

[vjcyrano]

Instead of binding the parameters you could use variables.

eg:


$sports = "7,8";

my $sql = "select * from table where field in ($sports)";

print $sql;

 

[cmayo]

Yes, but I'm dealing with dozens of external SQL files that are being read into variables as text, and all need placeholders substituted with command line arguments. I suppose I could recode everything to use PERL's substitution operators and that'd likely solve both my problems, but I'd rather not have to make such sweeping changes at this point.

 

[vjcyrano]

I dont think you can view the bind params as part of the sql query.

 

[cmayo]

If that's the case, then I'm asking the wrong question. Can someone give me an idea how to debug syntactical problems in poorly-formed SQL that's been created with bind_paramater?

 

[icrf]

What you're attempting to do is not how placeholders work. A placeholder is just that, a token for a single SQL value. It is not a piece of text that gets inserted into the string. It is checked for datatype and properly escaped and passed to the database. Not interpolating allows for reuse of execution plans on statement (preparing a single statement with placeholders and callig it multiple times with different values).

A more extreme but equally wrong example would be "SELECT * from ?". You cannot use a placeholder as a table name, only a single scalar value. Since it's not interpolated in the SQL, there's no simple visual way to view it, either. You get two distinct parts, the statement with the ?'s and the values that go in their place.

The only thing you can do is to make the in () list a perl scalar and interpolate it into the SQL (like vjcyrano showed). Just be careful to check the values you're putting in. If it's coming from external input, be sure to watch for SQL-injection holes and properly escape them (such as $db->quote).

You could also have some dynamic allocation and fulfillment of placeholders, but that's likely more trouble than it's worth.

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[MikeLacey]

As Andrew says - you can't use placeholders like that. You can see that Perl is doing it's best for you - it replaces the single ? with a single value even though you've given it more than one to look at.

You're picking up the values that will be used for the "IN" lists from a file by the sound of it. If there's always going to the same number of items in the list - then fine. Prepare, use and then re-use a query with the correct number of ?'s in it.

If the number of items in the list is variable then you'll have to prepare the statement for each item in the list I'm afraid.

Mike

I am not inscrutable.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[cmayo]

Andrew, Mike and vjcyrano,

Thanks very much for clearing that up for me. As I said, I'm quite unfamiliar with DBI and had completely misunderstood what bind_parameter was doing. Now that I know what has to be done and why, I'll get about the business of coding it another way.

Thanks again,
Chuck