List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:February 15 2009 8:56pm
Subject:Re: How to execute prepare statement when the placeholder is a
specific table name?
View as plain text  
Hi all!


Paul DuBois wrote:
> 
> On Feb 14, 2009, at 8:00 PM, Moon's Father wrote:
> 
>> Thanks for your fast reply.
>>
>> Do you know MySQL will support  this feature in the future?
(("this feature" = placeholders for table names in prepared statements))
> 
> It doesn't need to. Baron indicated how you can solve this problem.
> 
> If you look at prepared statements in general (not just for MySQL),
> placeholders are for data values, not identifiers.

I would go further than Paul:
I sure *hope* this will never be added.

Why?
Simply because it would be against the logic of efficient SQL handling
and generating optimum query plans.

When the parser checks a SQL statement, it will do that in the context
of the user executing the query and will check the current privileges.
Also, it will check which columns this table has, whether the column
names in the statement are valid, to which table (of a JOIN) they refer,
their data types, ...   Several more things I did not list.

All this can be done without knowing the values of variables.

In fact, it *should* be done without knowing these values, because that
is the basis to analyze a statement only once and execute it repeatedly,
with varying values. Parsing once for repeated execution is good for
performance.

So allowing placeholders (variables) for identifiers (table, column, and
other database object names) would block (future) optimization.

That is (probably) the reason why the MySQL standard also does not allow
this.


Jörg

-- 
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@stripped
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

Thread
How to execute prepare statement when the placeholder is a specific table name?Moon's Father14 Feb
  • Re: How to execute prepare statement when the placeholder is a specific table name?Baron Schwartz14 Feb
    • Re: How to execute prepare statement when the placeholder is a specific table name?Moon's Father15 Feb
      • Re: How to execute prepare statement when the placeholder is aspecific table name?Paul DuBois15 Feb
        • Re: How to execute prepare statement when the placeholder is aspecific table name?Joerg Bruehe15 Feb
          • Re: How to execute prepare statement when the placeholder is aspecific table name?Joerg Bruehe15 Feb