List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 11 2006 8:16pm
Subject:Re: Passing db names to a stored proc in MySQL 5
View as plain text  
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:
> 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
>
>


-- 
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

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