please enable stores procedure calling / security settings

[tgkprog]

origninal http://forums.x10hosting.com/programming-help/52434-sql-call-proc.html

i ran a script to make a stored procedure on php my admin using the sql window ... that worked fine.

but when i try to call it from php it does not work. using mysqli_multi_query

is the problem mysqli_multi_query ? do we not support it here?


rnivash I am also facing the same problem.. It says access denied for the user to execute stored routine..

hi i ran a script to make a stored procedure on php my admin using the sql window ... that worked fine.

but when i try to call it from php it does not work. using mysqli_multi_query

is the problem mysqli_multi_query ? do we not support it here?

echo "<br> in mn db res is <br>\n";
    $query = "call bookh_add1();";
    if (mysqli_multi_query($dbConRes, $query)) {
        do {
            /* store first result set */
            if ($result = mysqli_use_result($dbConRes)) {
                while ($row = mysqli_fetch_row($result)) {
                    //printf("row %s\n<br>", $row[0]);
                    $cntForToday = $row[0];
                }
                mysqli_free_result($result);
            }
            /* print divider */
            if (mysqli_more_results($dbConRes)) {
                //printf("-----------------\n");
            }
        } while (mysqli_next_result($dbConRes));
        //echo "-->\n";
        echo " Today's Count is " . $cntForToday . "<br>\n";    

        /*
        $c = mysqli_query($dbConRes, "select book_getCnt ()");
        //$c = mysqli_query($dbConRes, "select max(cnt)  from bookh_cnt where id=1;");
        if($c === FALSE){
            echo "c is false ";
        }else{
                echo "c is not false ";
        }
        $row = mysqli_fetch_array($c);
        echo "Count is " . $row [0];        
        */
        
    }else{
        echo " db err<br>";
    }

the sql i ran

   use  t7_parse1 ?

drop table  bookh_cnt ?

create table bookh_cnt ( id double not null auto_increment, 
cnt double, 
whn TIMESTAMP,
start TIMESTAMP,
forday date , PRIMARY KEY (id) ) ?
   
drop  procedure bookh_add1 ?
   
   create procedure bookh_add1 ()
  begin 
  declare   i int;
  repair table bookh_cnt;
 select max(cnt) into i from bookh_cnt where forday = CURRENT_DATE() ;

 IF EXISTS (select (cnt)  from bookh_cnt where forday = CURRENT_DATE())
THEN
 update bookh_cnt set cnt=cnt + 1 , whn=now() where forday = CURRENT_DATE();
 select "update";
ELSE
insert into bookh_cnt (cnt, whn, start, forday) values(1, now(), now(), CURRENT_DATE() );
select "insert";
END IF;
select max(cnt)  from bookh_cnt where forday = CURRENT_DATE() ;
 end ?

Edit:
I still do not know why its not working but i got a workaround at http://sel2in.com/prjs/php/bookh/v01 - not using stored procedures . would like to know how to use stored procedures in our environemnt ( call it and read returned result sets)

 

[Corey]

From what I understand and may be wrong this is not something we will be enabling on free hosting due to how easy it is for people to overload the MySQL server by using these features while not knowing what they are doing. Also cPanel does not currently support the setting of these permissions, it has to be done manually in the user database.

-Corey

 

[tgkprog]

well not sure about that. can always abuse / run a bad script

anyway requested seperately for my paid account. thank you

 

[hamtum]

i aggry with you dude

origninal http://forums.x10hosting.com/programming-help/52434-sql-call-proc.html

i ran a script to make a stored procedure on php my admin using the sql window ... that worked fine.

but when i try to call it from php it does not work. using mysqli_multi_query

is the problem mysqli_multi_query ? do we not support it here?


rnivash I am also facing the same problem.. It says access denied for the user to execute stored routine..