List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 9 2006 8:55pm
Subject:Re: Passing db names to a stored proc in MySQL 5
View as plain text  
Rory McKinley <rorym@stripped> wrote on 02/09/2006 03:06:41 PM:

> SGreen@stripped wrote:
>  > Rory McKinley <rorym@stripped> 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 execute it. It's all right there in the manual.
>  >
> <snip>
> 
> I am afraid that it doesn't work - unless I am being thick (yes, I know, 

> not like it hasn't happened before).
> Used a test statement such as this:
> 
> PREPARE query_statement  FROM "SELECT * FROM ?.sites"
> 
> Which causes a syntax error right at the ?
> 
> Whereas the following statement works fine, but doesn't help me :
> 
> SELECT * FROM sites WHERE site_name = ?
> 
> While RTFM, I came across the following:
> 
> "Parameter markers can be used only where data values should appear, not 

> for SQL keywords, identifiers, and so forth."
> 
> Which, if combined with my test results leaves me with Martijn's 
> original answer of it not being possible after all :(.
> 
> Rory
> 


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

Actually build the string to contain the database name and any other 
parameter markers you may need. Then prepare THAT (the string you just 
composed). Make better sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
Passing db names to a stored proc in MySQL 5Rory McKinley9 Feb
  • Re: Passing db names to a stored proc in MySQL 5Martijn Tonies9 Feb
    • Re: Passing db names to a stored proc in MySQL 5Peter Brawley12 Feb
  • Re: Passing db names to a stored proc in MySQL 5SGreen9 Feb
    • Re: Passing db names to a stored proc in MySQL 5Martijn Tonies9 Feb
    • Re: Passing db names to a stored proc in MySQL 5Rory McKinley9 Feb
      • Re: Passing db names to a stored proc in MySQL 5SGreen9 Feb
        • Re: Passing db names to a stored proc in MySQL 5Rory McKinley10 Feb
        • Re: [SOLVED]Passing db names to a stored proc in MySQL 5Rory McKinley10 Feb
      • Re: Passing db names to a stored proc in MySQL 5Peter Brawley11 Feb