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 a

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

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

2006-02-09 Thread Rory McKinley
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 Yup - got it to work - but it is so much hassle that I might seriously reconsider having queries run across databases, as it works

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

2006-02-09 Thread Rory McKinley
[EMAIL PROTECTED] wrote: 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 Doh! I cannot believe that I didn't think of that - seems I was being dense after all! Thanks Shawn wi

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:

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: > > Use a "prepared statement". Build your SQL statement as a string, prepare > it

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

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_tab

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,

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 passe