From: Peter Brawley
Date: February 12 2006 6:53pm
Subject: Re: Passing db names to a stored proc in MySQL 5
List-Archive: http://lists.mysql.com/mysql/194819
Message-Id: <43EF8433.90302@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-43EF843339D3======="
--=======AVGMAIL-43EF843339D3=======
Content-Type: multipart/alternative; boundary=------------090600000909060703060009
--------------090600000909060703060009
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Rory, Martin
>>CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR)
>>BEGIN
>>SELECT * FROM test_db_name.test_table;
>>END;
MySQL does not resolve test_db_name to the value passed in the
parameter, and the query fails because there is no database with that name.
> Is there any way around this?
>
>No.
Yes:
DELIMITER |
CREATE PROCEDURE testStoredProc( IN test_db_name CHAR(64) )
BEGIN
SET @sql = CONCAT( "SELECT * FROM ", test_db_name, ".test_table" );
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
|
DELIMITER ;
PB
-----
Martijn Tonies wrote:
>> I have tried dynamically assigning a database name to a stored proc via
>> its parameter list:
>>
>> CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR)
>> BEGIN
>> SELECT * FROM test_db_name.test_table;
>> END;
>>
>> MySQL does not resolve test_db_name to the value passed in the
>> parameter, and the query fails because there is no database with that
>>
> name.
>
>> Is there any way around this?
>>
>
> No.
>
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
>
--------------090600000909060703060009
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Rory, Martin
>>CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR)
>>BEGIN
>>SELECT * FROM test_db_name.test_table;
>>END;
MySQL does not resolve test_db_name to the value passed in the
parameter, and the query fails because there is no database with that name.
Is there any way around this?
>No.
Yes:
DELIMITER |
CREATE PROCEDURE testStoredProc( IN test_db_name CHAR(64) )
BEGIN
SET @sql = CONCAT( "SELECT * FROM ", test_db_name, ".test_table" );
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
|
DELIMITER ;
PB
-----
Martijn Tonies wrote:
I have tried dynamically assigning a database name to a stored proc via
its parameter list:
CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR)
BEGIN
SELECT * FROM test_db_name.test_table;
END;
MySQL does not resolve test_db_name to the value passed in the
parameter, and the query fails because there is no database with that
name.
Is there any way around this?
No.
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--------------090600000909060703060009--
--=======AVGMAIL-43EF843339D3=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006
--=======AVGMAIL-43EF843339D3=======--