I am trying to create a dynamic binding of columns, using the @_ array of a subroutine.

This is a purely educational feet, as it will probably save me a couple lines of code, if I wish to use it in the future; however, this problem struck me as a hard one to solve, and I would like any help my fellow munks(or initiates) could give me regarding creating a dynamic query. I've wondered for a good while on how to use subroutines to actually change code, and the assignements of scalars, and hope that the answers given to this node will finally clear up how to do so.

I would like to be able to call: bind_col(q{SELECT MEH, MOOSE FROM BLEH WHERE pissant = ?},q{$placeholder_value},'$meh, $moose') or preferably: bind_col(q{SELECT MEH, MOOSE FROM BLEH WHERE pissant = ?},q{$placeholder_value},'$meh', '$moose')
The subroutine should prepare a query sith the first shifted value, execute the query with the second, and bind either the third value (in the first case) or the third and fourth (and so on..) values (in the second case), and then print the values of $meh and $moose in the while loop. The first two parts were rather easy to craft, but the part that troubled me is the binding of values, to something which is not hard coded already. This is where I would like the help of my fellow monks.

The code I came up with was:

use strict;
use DBI;
    my $db = DBI->connect("dbi:mysql:dbname=database", 
               "user",
               "password");
bind_col(q{SELECT MEH, MOOSE FROM BLEH WHERE pissant = ?},q{"3"},'$meh
+', '$moose');

sub bind_col    {
my $sth;
my $statement = shift;
my $execute = shift;
$sth = $db->prepare($statement);
$sth->execute($execute);
$sth->bind_columns(\(@_));
while ($sth->fetch) {
print @_;
}
}

$db->disconnect();
[download]

This obviously didn't work, and after trying out a few more variations of this code I was no closer to the solution, that I was at the beggining. Maybe I'm supposed to eval something to get this to work, or maybe it's not possible at all? (I doubt that). I finally figured out that using bind_col would be my savior, and I could work around my lack of perl knowledge by just telling the script how many columns I wished to bind, and binding the columns to an array value. My code became

use strict;
use DBI;
    my $db = DBI->connect("dbi:mysql:dbname=revsys_elance", 
               "revsys_revsys",
               "kage");
bind_col(q{SELECT MEH, MOOSE FROM BLEH WHERE pissant = ?},q{"3"},2);

sub bind_col    {
my $sth;
my $statement = shift;
my $execute = shift;
my $num = shift;
my @col;
$sth = $db->prepare($statement);
$sth->execute($execute);
for (1 .. $num) {
$sth->bind_col($_, \$col[$_]);
}

while ($sth->fetch) {
print @col;
}
}
[download]

However, this didn't work, either. I'm hoping there is a simple correction to my code, that will fix this, but would like any help possible. This leads to an additional question: Is it possible to make a subroutine assign scalar variables of that the user choses to variables? Is there a way to do:

&subroutine('$moo')
sub soubroutine   {
my $var = shift;
   $var = "moo";
   print $moo;
}
[download]

or something of the like, where the scalar assigned to $var is not a string, but instead a regular scalar($something), which can be copped or printed like any other scalar?

Thanks for listening to my incoherant babble.

---

Gyan Kapur
gyan.kapur@rhhllp.com