DISCLAIMER -- Very new to Perl --

I need to pass a concatenated string of sessions to a prepared query which looks like this

my $uniqueSessions="SELECT DISTINCT  SESSION
                    FROM   $table";

my $queryUniques = $connect->prepare($uniqueSessions);
$queryUniques->execute();
$queryUniques->bind_columns(\$session);

my $query="SELECT   session, action
           FROM     $table
           WHERE    session
           IN       (?)
           ORDER BY session, id";

my $queryPrep = $connect->prepare($query);

while($queryUniques->fetch()) {
    if($counter == 1) {
        $sessionString = "'" . $session . "'";
    } else {
    开发者_运维百科    $sessionString = $sessionString . ", '" . $session . "'";
    }

    $counter++;
    if(($counter % 5) == 0) {
        $counter = 1;
        $queryPrep->execute($sessionString);
        my @test = $queryPrep->fetchall_arrayref();
    }
} 

Yet this is returning nothing, and I have tried the exact query in my db client and it works, any thoughts?

EDIT: Sorry seem to have cut too much out I have edited the code to make sure all information was present;

The string returned is sessions quoted with a single quote and with a comma between.

---

As others have mentioned:

use strict;
use warnings;

Also, every time you do a DBI call, you should be doing this:

if ($sth->err()) {
  die "ERROR: " . $sth->errstr() . "\n";
}

Even after a fetch. This will catch lots of the issues you might have.

I simplified your program a wee bit:

use strict;
use warnings;

use DBI;

my $connect = DBI->connect("$connectString", "$user", "$id");

if (not $connect) {
die qq(connection error\n);
}

my $table = "session";
my $uniqueSessions = "SELECT DISTINCT  SESSION
        FROM   $table";

print qq(\$uniqueSessions = "$uniqueSessions"\n);

my $queryUniques = $connect->prepare($uniqueSessions);
if ($queryUniques->err()) {
die "ERROR: " . $queryUniques->errstr() . "\n";
}
$queryUniques->execute();
if ($queryUniques->err()) {
die "ERROR: " . $queryUniques->errstr() . "\n";
}

my $session;
$queryUniques->bind_columns(\$session);

my $counter = 1;
my $sessionString;
while(my $hashref = $queryUniques->fetch()) {
print "Fetching Row\n";
if($counter == 1) {
    $sessionString = "'" . $session . "'";
} else {
    $sessionString = $sessionString . ", '" . $session . "'";
} 
$counter++;
}
if ($queryUniques->err()) {
print "ERROR = " . $queryUniques->errstr . "\n";
}

print "$sessionString\n";

I basically took out the second query and fixed a few things here and there. The last line prints out the $sessionString which is a quote separated list of all of your sessions. This part worked.

The second part is where things get weird. You are taking $sessionString and passing it as a SQL statement. Unless there's something I am not seeing, $sessionString is simply a list of sessions and not a SQL statement in itself.

As I said, check for errors with each DBI call, and see if you're erroring out somewhere.

You can also add a line like this:

print qq(DEBUG: SQL Query = '$sqlStatement'\n);

before you run a $sth->prepare($sqlStatement) and that way, you can see what your SQL statement is.

---

The Problem

It looks like you're preparing the second query before you figure out what (?) should be (which I assume is your group of $sessionString).

You need to substitute the sessions with the (?) BEFORE you do your DBI->prepare().

Something like this (not tested):

(my $sessionQuery = $query) =~ s/\(\?\)/$sessionString/;
my $querySth = $connect->prepare($sessionQuery);
$querySth->execute();
my @test = $queryPrep->fetchall_arrayref();

Remember that qq() quoting syntax is your friend. I use this a lot:

print qq(DEBUG: \$foo = "$foo"\n);

And sometimes, I simply copy and paste a statement and then quote it:

print qq(DEBUG: while (my $foo = foobar($bar)) };\n);

It's amazing how many errors I can catch that way. I can then search for DEBUG: in my program and delete those lines.

Remember write your programs a bit at a time, see if you can get $sessionString working. Then try to see if you can substitute that into your $query, then try executing the calculated query.

Perl is a nice and powerful language, but the syntax can be a bit crufty at times -- especially if you're not use to object oriented Perl.

---

Just two questions to ask yourself:

1) What's the value of $sessionString just before the call to execute()?

2) What's the return value from the execute method?
Try:

$queryPrep->execute($sessionString) or die $queryPrep->errstr;

---

Where is $session coming from?? First and foremost, I suggest putting

use strict;

in your perl programs so that you can catch many easy bugs before they happen.

Looks like the crux of the problem is your attempt at building single quoted strings separated by commas. In that case, I'd use an array to store the values that you need and then join them before you issue the query. For example:

my @sessions = ();

# perhaps you meant this?
while(my $session = $queryUniques->fetch()) {
    push @sessions, $session

    if((scalar @sessions) % 5 == 0) {
        my $sessionString = join ",", map { "'$_'" } @sessions;
        @sessions = ();
        $queryPrep->execute($sessionString);
        my @test = $queryPrep->fetchall_arrayref();
    }
}