Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

DBD::mysql::st bind_param failed: Illegal parameter number

30 views
Skip to first unread message

Seven Reeds

unread,
Aug 10, 2012, 3:30:16 PM8/10/12
to dbi-...@perl.org
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?

Rudolf Lippan

unread,
Aug 10, 2012, 5:08:07 PM8/10/12
to dbi-...@perl.org, Seven Reeds
On Friday, August 10, 2012 at 03:30:16 PM, Seven Reeds wrote:
> Hi
>
> I am on a RedHat box with perl 5.8.8. I have just started usinf
> "bind_param" but am having an issue.


Is RedHat still shipping Perl 5.8.8?



> $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,
^^^^^^
...

> ideas?
>


Unless I am missing something, it looks like your SQL is broken.


-r

Peter J. Holzer

unread,
Aug 13, 2012, 4:49:33 AM8/13/12
to Rudolf Lippan, dbi-...@perl.org, Seven Reeds
On 2012-08-10 17:08:07 -0400, Rudolf Lippan wrote:
> On Friday, August 10, 2012 at 03:30:16 PM, Seven Reeds wrote:
> > I am on a RedHat box with perl 5.8.8. I have just started usinf
> > "bind_param" but am having an issue.
>
>
> Is RedHat still shipping Perl 5.8.8?

The current release of RHEL (6) is shipping with perl 5.10.1, but RHEL 5
will be supported until 2017, so people aren't in a hurry to upgrade.
Some are probably still running RHEL 4 or even RHEL 3.

hp

--
_ | Peter J. Holzer | Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: "Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern."
__/ | http://www.hjp.at/ | -- Wolfram Heinrich in desd
signature.asc
0 new messages