Create Stored Procedure from PHP

Question

I am creating a database setup script which create and setup database on fly. The mysql user I use have full previledge to create stored procedure. My database server is different from my application server.

I am able to create user, then database, then select database, then create tables, then insert some data into tables, then create views etc..But at last when I create stored procedures I am facing issues.

Following a the sql to create procedure

CREATE DEFINER=`newuser`@`192.168.0.10` PROCEDURE `AppCounts_Stagewise_Monthly_Weekly` (IN FILTERTYPE INTEGER)
BEGIN
IF(FILTERTYPE = 0) THEN
SELECT base_user_id, stage, COUNT(vw_application.id) AS app_counts, WEEK(DATE) AS `week`, YEAR(DATE) AS `year`
    FROM vw_application 
    WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(DATE) 
    AND DATE(DATE) <= CURDATE() GROUP BY vw_application.stage, base_user_id;
ELSE    
    SELECT base_user_id, stage, COUNT(vw_application.id) AS app_counts, MONTH(DATE) AS `month`, YEAR(DATE) AS `year`
    FROM vw_application 
    WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(DATE) AND DATE(DATE) <= CURDATE() GROUP BY vw_application.stage, base_user_id;
END IF; 
END;

The create procedures SQLs like above are separated by a special separator and put in a file. I open the file and explode each create script by the separator to get each create statement in an array and execute them using zend db adapter.

$data = file_get_contents($this->_specimenDbFiles['ROUTINES']);
$data = explode('--', $data);

foreach($data as $key => $value) {
    $clientDbAdaptor->query($value); 
}

And the problem is? If the file is pure SQL why not just use the command line tool? — Ed Heal
– Ed Heal, Commented Mar 13, 2013 at 6:42
my database server is different, the problem is that everytime i get following error Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1548 Cannot load from mysql.proc. The table is probably corrupted' — Krishan Gopal
– Krishan Gopal, Commented Mar 13, 2013 at 7:29

Krishan Gopal · Accepted Answer · 2013-03-13 09:46:13Z

2

I have just managed to get this working.

Basically what I did is run following on my database server and error was resolved.

mysql_upgrade -u root -p

answered Mar 13, 2013 at 9:46

Krishan Gopal

4,1331 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create Stored Procedure from PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related