Понеделник, Март 12, 2012
Петък, Декември 02, 2011
Вторник, Октомври 04, 2011
Вторник, Август 02, 2011
Четвъртък, Юли 21, 2011
MySQL stored procedure examples
MySQL stored procedure, examle 1:
- check for valid input parameter
- declare handler for db error (1062 => duplicate key)
- insert data
- return status
- optional p_search_criteria parameter; not used if NULL, but used to search with %LIKE% if not NULL
- pagination with prepared statement, because LIMIT cannot use input parameters directly
- return result from query directly
- check for valid input parameter
- declare handler for db error (1062 => duplicate key)
- insert data
- return status
DROP PROCEDURE IF EXISTS test_proc;
DELIMITER $$
CREATE PROCEDURE test_proc (
IN p_test_id INT,
IN p_test_string VARCHAR(255)
)
BEGIN
main: BEGIN
# declare handler when duplicate key db error is thrown
DECLARE EXIT HANDLER FOR 1062 BEGIN
SELECT 1 AS status_code, 'Duplicate entry for input key values' AS status_message;
END;
# check if input parameter is NULL
IF p_test_id IS NULL OR p_test_id < 1 THEN
SELECT 2 AS status_code, 'Missing or invalid p_test_id parameter' AS status_message;
LEAVE main;
END IF;
INSERT INTO test_table
SET test_id = p_test_id,
test_string = p_test_string;
SELECT 0 AS status_code, 'OK' AS status_message;
END main;
END;
$$
DELIMITER ;
# CALL test_proc(10, 'test string parameter');
MySQL stored procedure, example 2:- optional p_search_criteria parameter; not used if NULL, but used to search with %LIKE% if not NULL
- pagination with prepared statement, because LIMIT cannot use input parameters directly
- return result from query directly
DROP PROCEDURE IF EXISTS test_proc_2;
DELIMITER $$
CREATE PROCEDURE test_proc_2 (
IN p_search_criteria VARCHAR(100),
IN p_page INT,
IN p_page_size INT
)
BEGIN
main: BEGIN
IF p_page IS NULL OR p_page < 1 THEN
SET p_page = 0;
ELSE
SET p_page = p_page - 1;
END IF;
IF p_page_size IS NULL OR p_page_size < 1 THEN
SET p_page_size = 20;
END IF;
PREPARE STMT FROM 'SELECT * FROM test_table
WHERE ? IS NULL OR test_column_1 LIKE ? OR test_column_2 LIKE ?
LIMIT ?, ?';
SET @c1 = p_search_criteria;
SET @c2 = CONCAT('%', p_search_criteria, '%');
SET @start = p_page * p_page_size;
SET @size = p_page_size;
EXECUTE STMT USING @c1, @c2, @c2, @start, @size;
DEALLOCATE PREPARE STMT;
END main;
END;
$$
DELIMITER ;
# CALL test_proc_2('searched text', 1, 10);
Петък, Юни 24, 2011
PHP PDO with MySQL stored procedures - problem with selecting OUT parameter
One day lost in discovering how to call and afterwards get stored procedure OUT parameters, finally resolved.
Stored Procedure:
PHP Code:
Stored Procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS `mydb`.`proc_name` $$
CREATE PROCEDURE `mydb`.`proc_name`(
IN i_myInput INT,
OUT o_statusCode INT,
OUT o_statusMsg VARCHAR(255))
BEGIN
IF i_myInput IS NULL THEN
SET o_statusCode = 1;
SET o_statusMsg = 'Error: input parameter is NULL';
ElSE
SET o_statusCode = 0;
SET o_statusMsg = 'OK';
END IF;
SELECT NULL as `result`;
END $$
DELIMITER ;
PHP Code:
# create PDO DB object
$mydb = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass");
$input = 5;
$proc = $mydb->prepare("CALL proc_name($input, @o_code, @o_message)");
$proc->execute();
$proc->closeCursor(); # !!!!! the line that resolved the issue
$output = $mydb->query("select @o_code, @o_message")->fetch(PDO::FETCH_ASSOC);
var_dump($output); // array('@o_code'=>'0', 'o_message'=>'OK')
Вторник, Април 12, 2011
Абонамент за:
Публикации (Atom)

