Thank you for finding that in the docs for me. I still believe your
problem resides in the fact that you are creating a link to a table in one
context then changing to another context within the same procedure. I
would suggest you try moving the USE statement to the top of the procedure
or avoid using it completely by using fully qualified table names.
A new problem related to how you interpreted my previous suggestion: The
USE statement will expect only database names as it's parameter, not fully
qualified table names. The USE command is also a *client* command, not a
server command (http://dev.mysql.com/doc/mysql/en/mysql_Commands.html). I
understand it is documented in the section that details how to create and
use stored procedures
(http://dev.mysql.com/doc/mysql/en/Stored_Procedure_Syntax.html) but I
believe that the text does not go far enough to warn the developer of the
hazards of changing contexts mid-procedure.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
rdo mail list address <rdomail@stripped> wrote on 08/02/2004 10:12:34
AM:
> Thanks, Shawn, I'll try the USE batabasename.tablename in a stored
> procedure and see if that works.
>
> Section 20.1 of the MySQL manual specifically refers to using USE within
a
> stored procedure.
>
> Again, thanks for the reply.
>
> rdo
>
> At 09:44 AM 8/2/2004 -0400, SGreen@stripped wrote:
> >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
>
>
