Procedure not working for MySQL

[realin]

Hiya all,

i tried making a simple procedure in mysql using phpmyadmin and then tried calling it, but i just couldnt do it.. Though the creation of the proc was successful but when i execute it using call statement i am just unable to do it..
please help me, thanks

This is how i created a procedure:

create procedure myProc()
begin
select avg(id) as avID from learn;
end//

and now when i try to call it using

call myProc();

I get the following error:

#1312 - PROCEDURE realin_site.myProc can't return a result set in the given context

what could be the problem ?

[ronverdonk]

It worked for me after I added the delimiter statements like[code=mysql]delimiter //
create procedure myProc()
begin
select avg(id) as avID from a;
end //
delimiter ;[/code]Ronald

[realin]

It worked for me after I added the delimiter statements like[code=mysql]delimiter //
create procedure myProc()
begin
select avg(id) as avID from a;
end //
delimiter ;[/code]Ronald

Hiya,

thanks for replying.. I was able to create the procedure, but was unable to run. I mean i was unable to execute..

call myProc();

[ronverdonk]

All I can show you is that it works ok on my MySQL server. Here is the log from the command line screen.

mysql> select id from a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE myProc()
    -> BEGIN
    -> SELECT avg(id) as avID FROM a;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call myProc;
+--------+
| avID   |
+--------+
| 5.5000 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Ronald

[realin]

All I can show you is that it works ok on my MySQL server. Here is the log from the command line screen.

mysql> select id from a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE myProc()
    -> BEGIN
    -> SELECT avg(id) as avID FROM a;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call myProc;
+--------+
| avID   |
+--------+
| 5.5000 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Ronald

hiya again,

thanks Ronald, it worked for me too.. actually i was trying thru phpmyadmin, and when i tried executing the procedure thru mysql query browser, it worked like a charm..
thanks for ur help
my bad, that i started this thread :P
heheheh ..

Anyways i am looking for transactions in php and have even started a new thread, but still no luck.. Do you know any solution to the problem.
the thread lies Here

cheers !!

[ronverdonk]

You are welcome. I'll have a look at the other thread. See you.

Ronald