Ok, I have created a analysis database on my home server to track idiots that constantly try to brute force my home file server. All my development on my home machine is done in Perl 5.8. I installed the latest version of MySQL because I read about the stored procedures that have been implemented since 5.0.
Everything was cooking with oil until I tried to write a procedure that returned multiple rows to the Perl code.
Example of select in stored procedure:
[i]SELECT tb_FH.IP,
tb_FH.RuleNumber,
tb_F.OrgCountry,
DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 DAY), ‘%c-%d-%Y’) “Date Blocked”,
tb_F.TimesBlocked “Total Times Blocked”,
COUNT(tb_FH.IP) “Blocked Today”
FROM tb_FireWalledHistory tb_FH,
tb_FireWalled_IP_CIDR tb_F
WHERE tb_FH.DateBlocked >= DATE_SUB(CURDATE(),INTERVAL 1 DAY)
AND tb_FH.IP = tb_F.IP
GROUP BY tb_FH.IP, tb_FH.RuleNumber;[/i]
Yea, ok.. The procedure works fine when launched from a command line secession, or even when launched out of toad. But, when you try to launch this from a Perl program, you receive the following error message:
[b]PROCEDURE FireWallStatistics.prc_DailyStats can’t return a result set in the given context at ./teststats.pl line 37.
Issuing rollback() for database handle being DESTROY’d without explicit disconnect().[/b]
I checked in the MySQL forums, and found that this is a problem others have encountered which seems to be caused by the DBI cpan module. There was discussion about about a patch to this module to correct this issue, but then I see where others are still encountering problems. These post’s were from the 2005 time frame, so I kind of moved on and handled these types of selects directly within the Perl code which works fine. I have updated my DBI module to the latest that is found on CPAN, which has a latter release date then the forum post’s that I found in MySql.com and am still receiving this error.
Has anyone done anything with stored procedures with Perl, and knows how to get around this??????
I mean, I have everything working. Just not how I want them to work. I wanted to use stored procedures/functions for all sql interaction other then the call to these procedures/functions.
Dan
grrrrr, yes I am having a crap day.
