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.
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.
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');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With