CODEWITHSUNDEEP
phpmysqlsqlstored-procedurespdo
echo_salik
echo_salik

Reputation: 859

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.

Upvotes: 7

Views: 5922

Answers (3)

Timo Huovinen
Timo Huovinen

Reputation: 55633

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');

Upvotes: 10

echo_salik
echo_salik

Reputation: 859

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.

Upvotes: 14

RiggsFolly
RiggsFolly

Reputation: 94662

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
$$

Upvotes: 0

Related Questions