grrr, MySql stored procedures and Perl

By DanLM ·
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:
tb_F.TimesBlocked "Total Times Blocked",
COUNT(tb_FH.IP) "Blocked Today"
FROM tb_FireWalledHistory tb_FH,
tb_FireWalled_IP_CIDR tb_F
AND tb_FH.IP = tb_F.IP
GROUP BY tb_FH.IP, tb_FH.RuleNumber;

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:
PROCEDURE FireWallStatistics.prc_DailyStats can't return a result set in the given context at ./ line 37.
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

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 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.


grrrrr, yes I am having a crap day.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

What happens if you use the sp like a query

by Tony Hopkinson In reply to grrr, MySql stored proced ...

ie build some command text (exec Mystoredproc(myparam1,myparam2).

You won't get the return code, but you should get the resultset.

Not ideal, but it's worked for me in the past in other situations.

Collapse -

I didnt even think of doing it that way, thats a good idea

by DanLM In reply to What happens if you use t ...

Hmmmmm, would I launch an external log in to the db... ie MySQL -password blablabla then launch the stored procedure?

I like that idea though, it would do what I want. Have all sql stored separately from my Perl code.

thanks Tony


Collapse -

Well you could do that

by Tony Hopkinson In reply to I didnt even think of doi ...

but I was thinking more of just building an SQL command and firing it off to pick up the result set. It was what I had to do way back when for SPs with a result set in Delphi through it's propriety database engine.

Collapse -

Oh, thats what I am doing now.

by DanLM In reply to Well you could do that

I'm sorry, I misunderstood. I ended up doing all the sql selects that would return multiple rows right in the Perl code. They work just fine, no issues at all. Your right, thats what was used prior to the stored procedures.


Related Discussions

Related Forums