rbh author
I've just spent several hours trying to figure out why I couldn't get my mysql stored procedure to work in phprunner.
I'll first show you the error, then my tip to debug stored procedures
Code with error
$sql="call `debug`.`debug_insert`('SP-test','Test');";
CustomQuery($sql);
Code witout error
$sql="call `debug`.`debug_insert`('SP-test','Test')";
CustomQuery($sql);
CustomQuery doesn't like ; (semicolon) inside my $sql string !!
Runing statement _call debug.debug_insert('SP-test','Test')_ with or without ; at end as single statement in phpMyadmin works fine. If you put ; inside string you get no syntax error in phpr, and no warning/error on website.
Debugging stored procedures are sometimes difficult. After I created a separate scheme "Debug", with 1 table "debug" and 3 stored procedures I'm spending less hours pulling my hair out. just need to look at my "log" in debug table
--
-- Database: `debug`
--
CREATE DATABASE `debug`;
USE `debug`;
DELIMITER $$
--
-- Procedures
--
CREATE PROCEDURE `debug_insert`(in p_label varchar(255),in p_txt text)
BEGIN
IF (ISNULL(@db) or (@db='')) THEN
SET @db= database();
END IF;
insert into `debug`.`debug` (`debugID`,`debug_time`,`debug_db`,`debug_label`,`debug_txt`) values (null,now(),@db,p_label, p_txt);
END$$
CREATE PROCEDURE `debug_off`(in p_label varchar(255))
BEGIN
IF (ISNULL(@db) or (@db='')) THEN
SET @db= database();
END IF;
#
SET @dbEnd=now();
SET @dbDiff=TIMEDIFF(@dbEnd,@dbStart);
SET @dbTid=TIME_TO_SEC(@dbDiff);
call debug.debug_insert(p_label,CONCAT('Debug Ended :',CONCAT(@dbEnd, ' *** Diff: ',@dbDiff,' ** sec: ',@dbTid)));
END$$
CREATE PROCEDURE `debug_on`(in p_label varchar(255))
BEGIN
IF (ISNULL(@db) or (@db='')) THEN
SET @db= database();
END IF;
SET @dbStart=now();
call debug.debug_insert( p_label,concat('Debug Started :',@dbStart));
END$$
DELIMITER ;
-- --------------------------------------------------------
--
-- Table structure for table `debug`
--
DROP TABLE IF EXISTS `debug`;
CREATE TABLE `debug` (
`debugID` int(11) NOT NULL AUTO_INCREMENT,
`debug_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`debug_db` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
`debug_label` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`debug_txt` mediumtext CHARACTER SET latin1,
PRIMARY KEY (`debugID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
COMMIT;
In my stored procedures I normally use debug_on and off to record execution time,
The log created in table, record time, which database i'm debugging, debug_label i use either for table, or name of stored procedure, Debug text for storing what I'm logging/debugging.
Look at effected rows after an update
SET @rows = ROW_COUNT();
CALL `debug`.debug_insert('fixListe',CONCAT('UPDATED `perra.liste` : ',@rows));
Log parameters passed to function
CALL `debug`.debug_insert('Param',CONCAT('pImportID: ', pImportID, ' User: ', pUser));
Several values at once
CALL `debug`.debug_insert('Serie',CONCAT('Serie: ',IFNULL(vSerieKode,'NULL'), ' ArkivID: ',IFNULL(vArkivID,'NULL')));
ps this article is related to How to call a mysql stored procedure with output parameters [snapback]http://www.asprunner.com/forums/topic/21647-how-to-call-a-mysql-stored-procedure-with-output-parameters[/snapback]
|
|