Re: Passing db names to a stored proc in MySQL 5

2006-02-12 Thread Peter Brawley
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

Re: Passing db names to a stored proc in MySQL 5

2006-02-11 Thread Peter Brawley
Rory, PREPARE query_statement FROM SELECT * FROM ?.sites If MySql 5.0 chokes on that, why couldn't you write sxomething like ... SET @sql = CONCAT(SELECT * FROM , dbname, .sites ); PREPATE qry_stmt FROM @sql; PB Rory McKinley wrote: [EMAIL PROTECTED] wrote: Rory McKinley [EMAIL

Passing db names to a stored proc in MySQL 5

2006-02-09 Thread Rory McKinley
Hello List 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

Re: Passing db names to a stored proc in MySQL 5

2006-02-09 Thread Martijn Tonies
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

Re: Passing db names to a stored proc in MySQL 5

2006-02-09 Thread SGreen
Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 07:37:17 AM: Hello List 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;

Re: Passing db names to a stored proc in MySQL 5

2006-02-09 Thread Martijn Tonies
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

Re: Passing db names to a stored proc in MySQL 5

2006-02-09 Thread Rory McKinley
[EMAIL PROTECTED] wrote: Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 07:37:17 AM: Hello List I have tried dynamically assigning a database name to a stored proc via its parameter list: snip snip Use a prepared statement. Build your SQL statement as a string, prepare it, and

Re: Passing db names to a stored proc in MySQL 5

2006-02-09 Thread SGreen
Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 03:06:41 PM: [EMAIL PROTECTED] wrote: Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 07:37:17 AM: Hello List I have tried dynamically assigning a database name to a stored proc via its parameter list: snip snip

Re: Passing db names to a stored proc in MySQL 5

2006-02-09 Thread Rory McKinley
[EMAIL PROTECTED] wrote: snip You were so close! Try it more like this: SET sSQL=CONCAT('SELECT * FROM ', passed_in_db_name, '.sites WHERE site_name=?'); PREPARE query_statement from sSQL snip Doh! I cannot believe that I didn't think of that - seems I was being dense after all! Thanks

Re: [SOLVED]Passing db names to a stored proc in MySQL 5

2006-02-09 Thread Rory McKinley
snip You were so close! Try it more like this: SET sSQL=CONCAT('SELECT * FROM ', passed_in_db_name, '.sites WHERE site_name=?'); PREPARE query_statement from sSQL snip Yup - got it to work - but it is so much hassle that I might seriously reconsider having queries run across databases, as