Retrieve or return an output parameter from a Mysql stored procedure in Zope

Zope 2.7.6 – Mysql 5.0.7. I have always thought that output parameters from stored procedures were not useable in Zope‘s ZSQL methods, but a few minutes ago is suddenly occured to me that I could do it by using the “sql_delimiter” function in Zope. To be quick, put something like this in a ZSQL method:

call sp_test(@y)
dtml-var sql_delimiter
select @y as thecount

(put the <> around dtml-var sql_delimiter)

This works perfectly! I simply return a count in a simple sproc, but it’s exciting because this makes it even more useable! I LOVE ZOPE! (and now the MySQL has stored procedures, I LOVE MYSQL!)

Sample code if you want it:

— Here is the stored procedure —-
— This simply returns the parameter vout
— as a count.

DELIMITER $$

DROP PROCEDURE IF EXISTS `fb`.`sp_test`$$
CREATE PROCEDURE `fb`.`sp_test` (
out vout int
)
BEGIN
select count(*) into vout from blogs;
END$$

DELIMITER ;

— Here is the ZSQL Method —

call sp_test(@y)
dtml-var sql_delimiter
select @y as thecount

(put the <> around dtml-var)

Now you should note… for some reason, in MySQL Query Browser you can’t issue the commands:
call sp_test(@y);
select @y as thecount;

It wont work. But you can do that at he command line client just fine.

Also, you cannot do this AND return a recordset in the same procedure. It works from the command line client, but not in the ZSQL method. So, you will be limited to only return parameters this way.