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?
