You do know that your *client* is not executing this procedure, the
*server* is. To the server, the procedure physically resides in a
database and if you need to reference tables in another database, I would
just use the full "databasename.tablename" method of identification. You
are basically asking the server to change its execution environment, its
context, to another database (even if you are giving it the name of the
database it is already running in). Would this be so that you could avoid
using the fully qualified table name to reference tables later on in the
SP?
I don't know if this is an error in allowing the USE statement within SPs
or if it is an error in the SP engine to maintain the original table's
context after a context switch. I suspect it to be the first more than the
second.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
rdo mail list address <rdomail@stripped> wrote on 07/31/2004 08:17:17
AM:
>
> Running 5.0.0-alpha-nt on a windows 2000 box. The server is mysqld-nt.
>
> If a "USE" database statement is inside a procedure, then upon
> execution, there is an error message that says that the table referenced
in
> the cursor does not exist, and the table name is preceded with a period.
> Additionally, the connection to the server is lost.
>
> Without the "USE" statement this particular procedure runs okay.
>
> After noticing a problem in another procedure, I narrowed the procedure
> down to a simpler reproducible process.
>
> C:\>mysql -u rdo
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 2 to server version: 5.0.0-alpha-nt
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> use cd2
> Database changed
> mysql> drop procedure populater;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql>
> mysql> delimiter //
> mysql> create procedure populater()
> ->
> -> begin
> -> declare done int default 0;
> -> declare continue handler for NOT FOUND set done = 1;
> -> declare c1 cursor for select pos,title,artist
> -> from table1
> -> where pos is not null;
> ->
> -> use cd2;
> -> set @x = 0;
> -> open c1;
> ->
> -> set @x = 1;
> -> close c1;
> -> end
> -> //
> Query OK, 0 rows affected (0.00 sec)
>
> mysql>
> mysql> delimiter ;
> mysql> call populater();
> ERROR 1146 (42S02): Table '.table1' doesn't exist
> mysql> select @x;
> ERROR 2013 (HY000): Lost connection to MySQL server during query
> mysql>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=1
>
