'Create Stored Procedures with PDO in PHP
I am reading a TEXT file from PHP and trying to execute commands from it, like creating a DB and all the tables and procedures it has. My code creates the tables but does not create Stored Procedures given in the file.
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_hits`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
select hits into @hits from db_books where Book_ID = id;
update db_books set hits=@hits+1 where Book_ID = id;
END$$
The PDO is not creating the SPs, how will be able to accomplish this task?
I have tried executing all the code part together and line by line, but nothing works.
I am trying to make a DB installer script.
Solution 1:[1]
Well, PMA Helped me with answering this Question of my own.
To overcome this you need to remove the delimiter part of the procedure, so that your queries become like:
DROP PROCEDURE IF EXISTS `add_hits`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
declare hits_bk int;
select hits into hits_bk from db_books where Book_ID = id;
update db_books set hits=hits_bk+1 where Book_ID = id;
END;
Now the queries will work.
Thanks to @Your Common Sense and @RiggsFolly for helping out.
Solution 2:[2]
PHP only allows you to execute one query at a time normally, so $$ delimiters are not necessary.
$pdo = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$pdo->exec('DROP PROCEDURE IF EXISTS `add_hits`');
$pdo->exec('CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
declare hits_bk int;
select hits into hits_bk from db_books where Book_ID = id;
update db_books set hits=hits_bk+1 where Book_ID = id;
END');
Solution 3:[3]
In an effort to keep on topic and answer the question
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_hits`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
select hits into @hits from db_books where Book_ID = id;
update db_books set hits=@hits+1 where Book_ID = id;
END$$
The first occurance of $$ will terminate the DDL so
DROP PROCEDURE IF EXISTS `add_hits`$$
So I think it should be
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_hits`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
select hits into @hits from db_books where Book_ID = id;
update db_books set hits=@hits+1 where Book_ID = id;
END
$$
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | echo_salik |
| Solution 2 | |
| Solution 3 | RiggsFolly |
