Does DreamHost support MySQL stored procedures?

software development

#1

I am having a hard time running some stored procedures from a Perl script. I had everything working on my internal server, but when I moved it to DreamHost, nothing works. I know that the database settings are correct.

I can create the stored procedures, but they will not execute from phpMyAdmin or from my Perl script. When I run the Perl script, I get an error from the Perl script that the stored procedure failed. I can run the stored procedure from MySQL Query Browser (connected to the database) and it works.

For example, I created the following stored procedure:

DELIMITER $$
CREATE PROCEDURE myproc()
BEGIN
SELECT ‘it works!’;
END $$
DELIMITER ;

When I try and run it from phpMyAdmin, it produces the following error:

#1312 - PROCEDURE agps.myproc can’t return a result set in the given context

(agps is the database name)

Does DreamHost even support stored procedures?

Any help will be appreciated.

Thanks.


#2

I’m pretty sure that a thorough search on the forums here will say that you can’t do stored procedures here. That’s just my recollection from a long time ago.


#3

Stored procedures work in general (although they’re not preserved in backups, so you’ll need to save them separately — this is a bug which we’re working on). The issue here is specific to the procedure you’ve written: stored procedures can’t return result sets. Try one of the examples from http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html instead.


#4

Why can’t the stored procedure return a result set? This stored procedure works on my server, using MySQL 5.

Is this because dreamhost has restricted permissions for stored procedures?


#5

AndrewF - from the link that you provided “http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html”, the web page states:

“Statements that return a result set can be used within a stored procedure but not within a stored function.”

So, this has to be a restriction on the part of DreamHost to not allow stored procedures to return a result set - correct?