Hi I am on a RedHat box with perl 5.8.8. I have just started usinf "bind_param" but am having an issue.
In my script I have the following section of perl: ######################################## $tmp = <<EOF; SELECT DISTINCT UPPER(eS.machine) AS Machine FROM userTracking.sessions s LEFT JOIN userTracking.eventLog eS ON eS.id = s.start_id LEFT JOIN userTracking.eventLog eE ON eE.id = s.end_id LEFT JOIN userTracking.logbooks l ON s.id = l.sessions_id WHERE eS.date BETWEEN ? AND ? OR IFNULL(eE.date, NOW()) BETWEEN ? AND ? OR (eS.date < ? AND IFNULL(eE.date, NOW()) > ?) ORDER BY eS.machine EOF if (!($sth = $DBH->prepare($tmp))) { push(@{$INFO{error}}, "DB prepare failed"); return encode_json(\%INFO); } $sth->bind_param(1, $INFO{formParameters}{dateStart}, SQL_DATETIME); $sth->bind_param(2, $INFO{formParameters}{dateEnd}, SQL_DATETIME); $sth->bind_param(3, $INFO{formParameters}{dateStart}, SQL_DATETIME); $sth->bind_param(4, $INFO{formParameters}{dateEnd}, SQL_DATETIME); $sth->bind_param(5, $INFO{formParameters}{dateStart}, SQL_DATETIME); $sth->bind_param(6, $INFO{formParameters}{dateEnd}, SQL_DATETIME); if (!($sth->execute())) { push(@{$INFO{error}}, "DB prepare failed"); return encode_json(\%INFO); } for (my $i = 0; @row = $sth->fetchrow_array; $i++) { push(@{$INFO{formParameters}{machineList}}, $row[0]); } $sth->finish; $sth = undef; $tmp = <<EOF; SELECT DISTINCT UPPER(eS.netid) AS Netid FROM userTracking.sessions s LEFT JOIN userTracking.eventLog eS ON eS.id = s.start_id LEFT JOIN userTracking.eventLog eE ON eE.id = s.end_id LEFT JOIN userTracking.logbooks l ON s.id = l.sessions_id WHERE eS.date BETWEEN ? AND ? OR IFNULL(eE.date, NOW()) BETWEEN ? AND ? OR (eS.date < ? AND IFNULL(eE.date, NOW()) > ?) ORDER BY eS.netid EOF if (!($sth = $DBH->prepare($tmp))) { push(@{$INFO{error}}, "DB prepare failed"); return encode_json(\%INFO); } $sth->bind_param(1, $INFO{formParameters}{dateStart}, SQL_DATETIME); $sth->bind_param(2, $INFO{formParameters}{dateEnd}, SQL_DATETIME); $sth->bind_param(3, $INFO{formParameters}{dateStart}, SQL_DATETIME); $sth->bind_param(4, $INFO{formParameters}{dateEnd}, SQL_DATETIME); $sth->bind_param(5, $INFO{formParameters}{dateStart}, SQL_DATETIME); $sth->bind_param(6, $INFO{formParameters}{dateEnd}, SQL_DATETIME); if (!($sth->execute())) { push(@{$INFO{error}}, "DB prepare failed"); return encode_json(\%INFO); } for (my $i = 0; @row = $sth->fetchrow_array; $i++) { push(@{$INFO{formParameters}{netidList}}, $row[0]); } $sth->finish; $sth = undef; $tmp = <<EOF; SELECT UPPER(eS.machine) AS Machine, UPPER(eS.netID) AS NetID, DATE_FORMAT(eS.date, '%Y/%m/%d %H:%i:%S') AS Start, IF (eE.date IS NULL, '', DATE_FORMAT(eE.date, ''%Y/%m/%d %H:%i:%S')) AS End, FORMAT((UNIX_TIMESTAMP(IFNULL(eE.date, NOW())) - UNIX_TIMESTAMP(eS.date)) / 3600, 4) AS Elapsed, IFNULL( GROUP_CONCAT( '<div class=\"eventDisplayDiv\"><span class=\"', l.severity, '\">', DATE_FORMAT(l.date, '%m/%d/%Y %H:%i:%S'), ' ', l.netid, '</span><br/>', l.entry, '</div>' ORDER BY l.date DESC SEPARATOR '' ), '' ) AS Event FROM userTracking.sessions s LEFT JOIN userTracking.eventLog eS ON eS.id = s.start_id LEFT JOIN userTracking.eventLog eE ON eE.id = s.end_id LEFT JOIN userTracking.logbooks l ON s.id = l.sessions_id WHERE eS.date BETWEEN ? AND ? OR IFNULL(eE.date, NOW()) BETWEEN ? AND ? OR (eS.date < ? AND IFNULL(eE.date, NOW()) > ?) GROUP BY s.id ORDER BY eS.date LIMIT ?, ? EOF if (!($sth = $DBH->prepare($tmp))) { push(@{$INFO{error}}, "DB prepare failed"); return encode_json(\%INFO); } $sth->bind_param(1, $INFO{formParameters}{dateStart}, SQL_DATETIME); $sth->bind_param(2, $INFO{formParameters}{dateEnd}, SQL_DATETIME); $sth->bind_param(3, $INFO{formParameters}{dateStart}, SQL_DATETIME); $sth->bind_param(4, $INFO{formParameters}{dateEnd}, SQL_DATETIME); $sth->bind_param(5, $INFO{formParameters}{dateStart}, SQL_DATETIME); $sth->bind_param(6, $INFO{formParameters}{dateEnd}, SQL_DATETIME); $sth->bind_param(7, $INFO{formParameters}{limitOffset}, SQL_INTEGER); $sth->bind_param(8, $INFO{formParameters}{limitLength}, SQL_INTEGER); if (!($sth->execute())) { push(@{$INFO{error}}, "DB prepare failed"); return encode_json(\%INFO); } for (my $i = 0; @row = $sth->fetchrow_array; $i++) { $row[4] += 0; push(@{$INFO{table}{$row[0]}{$row[1]}}, [@row[2 .. $#row]]); } $sth->finish; $sth = undef; ######################################## I have used DBI->trace("2|SQL") and I see that the first two queries are being run correctly. However, I get the message: myscript.pl DBD::mysql::st bind_param failed: Illegal parameter number at ./myscript.pl line 268. Line 268 corresponds to the first bind_param line of the last select. I have tried the binds without the types. I tried putting the values in the execute(). I tried removing the LIMIT. ideas?