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