Re: How to execute prepare statement when the placeholder is a specific table name?
Hi, I made a silly typo: Joerg Bruehe wrote: > [[...]] > > So allowing placeholders (variables) for identifiers (table, column, and > other database object names) would block (future) optimization. > > That is (probably) the reason why the SQL standard also does not allow > this. ^^^ I of course wanted to refer to ISO, ANSI, NIST and other standardizing bodies, not to the specific product. Sorry for any confusion I may have created! Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to execute prepare statement when the placeholder is a specific table name?
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.bru...@sun.com
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to execute prepare statement when the placeholder is a specific table name?
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?
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.
On Sun, Feb 15, 2009 at 12:59 AM, Baron Schwartz
wrote:
Hello,
On Sat, Feb 14, 2009 at 11:15 AM, Moon's Father
wrote:
Here is my routine.
DELIMITER $$
CREATE
PROCEDURE `t_girl`.`sp_join2`()
BEGIN
set @a = 'a';
set @b = 'g';
set @stmt = concat('select * from ?,? where a.id = g.id');
prepare s1 from @stmt;
execute s1;
drop prepare s1;
END$$
DELIMITER ;
But it didn't work for me.
So what I want to know is how to table name when there're a
placeholder
within sproc.
You can't use placeholders for identifiers, only for literal values.
So you will need to use CONCAT() to build the string with the
identifiers already in it, before you PREPARE.
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
--
Paul DuBois
Sun Microsystems / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to execute prepare statement when the placeholder is a specific table name?
Thanks for your fast reply.
Do you know MySQL will support this feature in the future?
On Sun, Feb 15, 2009 at 12:59 AM, Baron Schwartz wrote:
> Hello,
>
> On Sat, Feb 14, 2009 at 11:15 AM, Moon's Father
> wrote:
> > Here is my routine.
> > DELIMITER $$
> >
> > CREATE
> >PROCEDURE `t_girl`.`sp_join2`()
> >BEGIN
> >set @a = 'a';
> >set @b = 'g';
> >set @stmt = concat('select * from ?,? where a.id = g.id');
> >prepare s1 from @stmt;
> >execute s1;
> >drop prepare s1;
> >END$$
> >
> > DELIMITER ;
> >
> > But it didn't work for me.
> > So what I want to know is how to table name when there're a placeholder
> > within sproc.
>
> You can't use placeholders for identifiers, only for literal values.
> So you will need to use CONCAT() to build the string with the
> identifiers already in it, before you PREPARE.
>
>
> --
> Baron Schwartz, Director of Consulting, Percona Inc.
> Our Blog: http://www.mysqlperformanceblog.com/
> Our Services: http://www.percona.com/services.html
>
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Re: How to execute prepare statement when the placeholder is a specific table name?
Hello,
On Sat, Feb 14, 2009 at 11:15 AM, Moon's Father
wrote:
> Here is my routine.
> DELIMITER $$
>
> CREATE
>PROCEDURE `t_girl`.`sp_join2`()
>BEGIN
>set @a = 'a';
>set @b = 'g';
>set @stmt = concat('select * from ?,? where a.id = g.id');
>prepare s1 from @stmt;
>execute s1;
>drop prepare s1;
>END$$
>
> DELIMITER ;
>
> But it didn't work for me.
> So what I want to know is how to table name when there're a placeholder
> within sproc.
You can't use placeholders for identifiers, only for literal values.
So you will need to use CONCAT() to build the string with the
identifiers already in it, before you PREPARE.
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
