Percona Live MySQL User's Conference, San Francisco, April 10-12th, 2012 Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Saturday, November 05, 2005

MySQL 5: Prepared statement syntax and Dynamic SQL

Every now and then, people are puzzled by the precise status and extent of MySQL support for dynamic SQL.

Statement Handling


MySQL support the prepared statement syntax. For the better part, a prepared statement is much like a 'normal', immediate statement. The main difference is seen in the way the statement is processed by the server.

Immediate Statements


When an immediate statement is issued, it is processed directly. Processing comprises the following steps:

  1. Parsing: lexical and syntactic analysis of the statement

  2. Planning: optimizer devises a strategy to realise the required result or action, the execution plan or query plan

  3. Execution: retrieval/seeking, writing and reading of data and, if applicable, the construction of a resultset



After these steps, the server responds to the request by sending the client a resultset (if applicable), or an acknowledgement that the statement was executed. Of course, all these actions are performed in concert, and the client is not aware of these different steps taking place. This becomes clear when typing the following statement into the MySQL command line client tool:


mysql> select count(*) from information_schema.schemata;

+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.10 sec)

Immediately (well, almost) the request to select the number of schemata is responded to by returning the resultset.

Prepared Statements


A prepared statement is initiated by the PREPARE statement. A preparation for a query equivalent to previous one could look like this:

mysql> prepare stmt from
-> 'select count(*) from information_schema.schemata';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

This time, we didn't get a resultset.
The PREPARE statement instructs the server to parse the query, and possibly, to devise the execution plan. PREPARE associates an identifier with the statement, stmt, wich acts as a handle to refer to the statement and the corresponding execution plan.

Actual execution is postponed until called for by the EXECUTE statement, using the handle to identify the prepared statment to execute.

mysql> execute stmt;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)

Which is the same result as we got when we issued the immediate statement.

This seems like a overly complex way to do what we could also do with just one statement. It is - until we execute it again:

mysql> execute stmt;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)

....and again and again and again.

This actually touches upon the major purpose of the prepared statement concept: when a statement is to be repeatedly executed, a prepared statement is potentially more efficient. Because only the execution step needs to be repeated, there is less time wasted on parsing and building the query plan each time the result is required.
Increased efficiency is even more evident for statements that do not return a resultset (such as INSERT, UPDATE and DELETE) because the actions needed to construct a resultset are generally more timeconsuming than parsing and creating an execution plan.

Now, we need to elaborate just a little on this efficiency argument. In the Reference manual, you will bump in to this phrase pretty quickly:

MySQL 5.0 provides support for server-side prepared statements. This...takes advantage of the efficient client/server binary protocol...provided that you use an appropriate client programming interface. Candidate interfaces include the MySQL C API client library (for C programs), MySQL Connector/J (for Java programs), and MySQL Connector/NET.

An alternative SQL interface to prepared statements is available. This interface is not as efficient as using...a prepared statement API...

A little further on, the manual explains that the prepared statement syntax is available from within the SQL language primarily for development purposes; NOT to gain efficiency.

(I did some tests that suggest that the SQL prepared statement syntax is slower than immediate statements, but I don't know if this has to do with the query cache. I used INSERT statements BTW)

Using Parameters


A very powerful feature of prepared statments is the possibility to bind parameters to it. Parameters are specified by writing special ? placholders inside the sql statement that is to be prepared:

mysql> prepare stmt from
-> 'select count(*) from information_schema.schemata where schema_name = ?'
;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

When EXECUTE-ing the statement, these placeholders must be bound to user variables with the USING syntax:

mysql> set @schema := 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @schema;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)


So, even though the statement has been prepared, we can still enjoy the flexibility of controlling the query result.

One thing to keep in mind is that parameters are not implemented using simple string substitution. For example, the placeholder in the previous example is not quoted inside the statement. It merely provides a slot for a value, and the binding process takes care of transferring that value to the slot inside the statement. Quotes are merely syntactic methods to distinguish a string from the surrounding code. Because the binding process is way beyond the level of parsing, it does not make sense to use quotes.

You can't just put a placeholder wherever you like. Parameter placeholders can appear only in those places inside the statement where you would normally expect an expression. In particular, you cannot use parameter placeholders to parameterize identifiers or entire statement structures. So, the following attempt fails with a syntax error, because the parameter placeholder appears where you would normally put an identifier:

mysql> prepare stmt from 'create table ? (id int unsigned)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '? (id int unsigned)' at line 1

However, is is not impossible to paramterize identifiers using the prepared statement syntax. I'll show that in a bit. It's just that you can't do it using parameters, because parameters are just a special cases of expressions, like column references, literals, etc.

Multiple Parameters


We are not restricted to just one parameter, we can use several:


mysql> prepare stmt from
-> 'select count(*)
-> from information_schema.schemata
-> where schema_name = ? or schema_name = ?'

;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute stmt
-> using @schema1,@schema2
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

Parameter binding occurs in a positional manner.
Each ? placeholder must be matched bij exactly one user variable in the USING clause, where the first placeholder matches the first user variable, the second placeholder matches the second user variable and so on.

You really must match each placeholder by exactly one user variable, or else you will encounter an error:

1210 (HY000): Incorrect arguments to EXECUTE


Dynamic SQL


For no particular reason, PREPARE accepts either a string literal, or a user-defined variable to define the statement to prepare. It would've been just as conceivable to accept just a statement, like so:

mysql> prepare stmt from
-> select count(*) from information_schema.schemata;

But no, this does not work. This just results in a syntax error.

Anyway, we already saw how PREPARE accepts a statement in the form of a string literal. It's pretty much the same for a global user variable:

mysql> set @sql_text := 'select count(*) from information_schema.schemata';

mysql> prepare stmt from
-> @sql_text

Query OK, 0 rows affected (0.00 sec)
Statement prepared

Because we can freely assign whatever value we want to @sql_text user variable, we can use this as a device to employ dynamic SQL.

Now we know how to dynamically manipulate our identifiers too: we just manipulate our string before assigning it to the user variable, like so:

mysql> set @table_name := 'mytable';
Query OK, 0 rows affected (0.02 sec)

mysql> set @sql_text:=concat('create table ',@table_name,'(id int unsigned)');
Query OK, 0 rows affected (0.00 sec)


Cleaning up


There's one extra step in the process of using prepared statements that I did not yet mention. That's cleaning up (I guess that tells you something about me, right?). Once you've prepared a statement, the handle and the associated objects on the server's side will remain to exist until the client's session is over. This means that client will keep some of the server's resources occupied. Therefore, it's good practice to clean up afterwards. When you're sure you're done working with the statement, you should DEALLOCATE it:

mysql> deallocate prepare stmt;


This just tells the server to get rid of all the resources associated with the statement handle, and to forget about the statement handle as well.

Some links


Although it does not seem that the prepared statement syntax was designed for it (word is there will be true dynamic sql support in MySQL 5.1 in the form of the EXECUTE IMMEDIATE syntax), it sure does the job. If you want to read some articles on actually using this feature for a real purpose, check out these links:


"The Wizard revisited"

By Beat Vontobel

"The power of dynamic queries"

by Giuseppe Maxia

"Scheduling Stored Procedure Execution"

by yours truly

107 comments:

Anonymous said...

Excellent article - helped me with my problem in a quick and concise manner. A+

Sören said...

That helped a lot! Thank you!

joannmarii said...

Helped in my problem: I needed to load data to myTable_A, myTable_B or myTable_C, where A, B or C is given as a parameter.

Article written very clearly.
Very helpful information.
Thanks

Anonymous said...

I tried dynamic SQL as you show here for creating a procedure, not to run dynamically, but just to be created once dynamically. And I got the error
"This command is not supported in the prepared statement protocal yet." ErrorNr 1295. Using MySQL 5.0.37. What is my problem?

Code Follows:

use her_db;

set @other_db = "his_db" ;

set @my_proc = concat('CREATE PROCEDURE `POPULATE_LANGUAGES` () BEGIN INSERT INTO ', @other_db, '.languages (name, code) (select Language.name, Language.id from Language); END' ) ;

PREPARE pop_lang_proc FROM @my_proc ;

Roland Bouman said...

Hi!

Well, the problem is just as advertised by the error message: you cannot create stored procedures (yet) using the prepare syntax.

It's a pity - but that's how it is. There are ways to work around it though:

If you are adventurous, check out this article by Giuseppe Maxia. He explains how you can create stored procedures dynamically by directly inserting the procedure text into the mysql.proc table:
http://datacharmer.blogspot.com/
(search blog for "Higher Order MySQL")

Anonymous said...

Simply A+

1Store said...

I found it doesn't support multiple lines

such as

'
select count(*) from table1;
select count(*) from table2;

'

Anyone else get errors with multiple lines?

Roland Bouman said...

Hi Store1,

PREPARE does not support 'batching' of statements.

Murali.V said...

Hi Rouland,

Is that any way i can get the values in to a variable after executing the statement?

i.e,

Something like this,
SET @vSql := 'SELECT COUNT(*) FROM mytable';
PREPARE vStmt FROM @vSql;
EXECUTE vStmt INTO @vResult;

-Murali

Murali.V said...

Sorry for Disturbing you,

I got the solution by myself.

Here it is,

SET @vSql := 'SELECT COUNT(*) INTO @vResult FROM mytable';
PREPARE vStmt FROM @vSql;
EXECUTE vStmt;
SELECT @vResult;

Anyway Thanks,
Murali

Michele said...

hi! great article, but i've got an question..

i've done a stored procedure that modify multiple table, when i call this procedure into a trigger don't do any modification,
in the same way when i do a "stand-alone" call to this procedure (es: CALL proc(2); ) it works.. it's a not implemented yet funcionality in MySQL 5.0.51a-9+lenny2 ? thanks and sorry for my bad english ;)

Roland Bouman said...

Hi!

I have to see code and error messages, else I won't look into it.

Flopsy said...

First, I just wanted to say great blog! You should write a book :)


I need to create a prepared statement that will return more than one row. It made me think at first I needed to use a cursor, but it never worked.

I'll try to use a short example.

DECLARE v_county VARCHAR(1000);

-- Prepare stmt:
prepare stmt_listings from
select
record_id as "custom11",
from table_nm
where (?)
;

-- cursor stmt for using var:
DECLARE cur_county_ouput CURSOR FOR
select DISTINCT GROUP_CONCAT(CONCAT('f.fips_county_nm like ',Quote(CONCAT(title,'%'))) ORDER BY title ASC SEPARATOR ' OR ') as counties from table_nm where level=2 order by title ASC ;

-- fetch cursor:
OPEN cur_county_ouput;
FETCH cur_county_ouput INTO v_county;
CLOSE cur_county_ouput;

-- execute stmt:
EXECUTE stmt_listing USING v_county;

At this point I need to loop through the executed statement results. any idea how I could do this?

thanks,
Sara

Roland Bouman said...

Hi Flopsy,

thanks for your kind words!

Actually, I am co-author to a book. It's called the MySQL 5.1 Cluster Certification Study Guide (ISBN 595352502, 380 pages, $40.00)

Now concerning your problem with prepared statements: I have to disappoint you I'm afraid. Currently you cannot traverse the resultset returned by a prepared statement. However, there are ways around it:

1) Instead of preparing a SELECT statement, prepare a CREATE TEMPORARY TABLE ... AS SELECT statement, and write a regular cursor to traverse the temporary table

2) Instead of preparing a SELECT statement, prepare a CREATE VIEW statement that uses your SELECT. Then, write a regular cursor to traverse the view.

BTW - there is another issue with the code you posted. Sadly, this type of syntax:

EXECUTE stmt_listing USING v_county;

does not work. I know its daft, it should work, but alas it doesn't. What doe work is this:

EXECUTE stmt_listing USING @county;

So, conversely, your FETCH would look like:

FETCH cur_county_ouput INTO @county;

That said - maybe you can tell us what you are trying to achieve - I mean, both PREPAREd statments and CURSORs are pretty ugly beasts in MySQL and if you can avoid them (you almost always can) you should. So tell us what the result should be and I'll try and come up with a suggestion to avoid cursors and prepared statements, Ok?

kind regards,

Roland

Flopsy said...

Ok, I've changed my code up a bit and created a prepared statement using a insert/select to put into the temp table.

Sooo... Here I am still stuck.

What I need to accomplish is to be able to generate a dynamic where clause, which is working. If manually build the statement using the generated clause then I get the correct result.

Somehow when I try to create the prepared statement with the where clause as the variable param to the statement, I do not get any records. It makes me think that the value is either getting lost or there's a syntax error in the prepped statement.

It would be great if you could actually access properties of the prep statement to debug things like this.

So here's the code I'm using...

prepare stmt_counties from
'select DISTINCT GROUP_CONCAT(CONCAT(''f.fips_county_nm like '',Quote(CONCAT(title,''%''))) ORDER BY title ASC SEPARATOR '' OR '') INTO @county from rochesterhomepage_net.pmd_locations where level=2 order by title ASC' ;

prepare stmt_listings from
'insert into business_data_import.import_roch_listings
(login,
`password`,
membership,
firmname,
business_short,
category0,
category1,
category2,
category3,
category4,
location0,
location1,
loc_text,
listing_address1,
listing_zip,
www,
custom11,
custom21,
custom2,
custom3
)
select
null,
null,
null,
business_nm,
null,
bdc_1,
bdc_2,
bdc_3,
bdc_4,
bdc_5,
country_subdiv_cd,
fips_county_nm,
city_nm,
address,
zip_cd,
url,
record_id,
CONCAT_WS(" ",search_terms,business_nm,city_nm,country_subdiv_cd,fips_county_nm),
phone_num,
fax_num

from business_data_import.import_rochester i
inner join business_data_import.bus_fips f on f.fips_cd = LEFT(i.fips_cd,5)
inner join business_data_import.import_bdc b on i.bdc_1 = b.bdc_cd
where (?)
order by f.fips_county_nm';


EXECUTE stmt_counties;
EXECUTE stmt_listings USING @county;

And the "temp" table:

CREATE TABLE if not exists business_data_import.import_roch_listings (
Id int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (Id),
login varchar(20),
`password` varchar(20),
membership varchar(20),
firmname varchar(20), INDEX (firmname),
business_short varchar(20),
category0 varchar(20), INDEX (category0),
category1 varchar(20), INDEX (category1),
category2 varchar(20), INDEX (category2),
category3 varchar(20), INDEX (category3),
category4 varchar(20), INDEX (category4),
location0 varchar(20), INDEX (location0),
location1 varchar(20), INDEX (location1),
loc_text varchar(20), listing_address1 varchar(20), listing_zip varchar(20), www varchar(20),
custom11 varchar(20), custom21 varchar(20),custom2 varchar(20),custom3 varchar(20)
);

*pulling hair out*

Thanks!

Roland Bouman said...

Flopsy,

the direct cause of trouble is in this part of your PREPARE stmt_listings statement:

where (?)

You see, you can use the parameter placeholder (the question mark ?) only for *values*. It is not a text-substitution device - rather, it is a variable.

So, the immediate solution to your problem would be to simply rewrite the statement string from your second PREPARE statement to:

concat(
'insert ... where ('
, @county
, ') order by f.fips_county_nm'
)

That said, I think you can do yourself a favour and make things a lot easier than they seem now.

If I am not mistaken, the intention of the PREPARE stmt_counties to create a series of conditions of the form

f.fips_county_nm LIKE '[title]%'

where [title] is the value of the title column of the pmd_locations table. These conditions are then OR-ed together. So this begs the question - why aren't these statements merged together?

Can you please test if this:

SELECT ...columns...
FROM import_rochester i
INNER JOIN bus_fips f
ON f.fips_cd = LEFT(i.fips_cd, 5)
INNER JOIN import_bdc b
ON i.bdc_1 = b.bdc_cd
INNER JOIN pmd_locations
ON f.fips_county_nm like CONCAT(title,'%')

is any slower or faster then the list of OR-ed conditions?

Flopsy said...

yea combining the statements worked alot better. I didn't know you could do a join on a like. Thanks for your help.

Sara

Anonymous said...

Hello Roland,
Your article doesn't distinguish between resultsets from select-statements and resultsets produced by prepared statements, so they should fit together?
I am trying to combine a select-statement with a prepared statement like this:
select * from dislike where name in(execute livesright using @name);
It is not accepted by MySql 5.0.72.
It is also not possible to union a select-resultset with an prepared statement-resultset.
Do you know why? Is there a solution?

Thank you very much,
Alex

Roland Bouman said...

Hi Alex!

"Your article doesn't distinguish between resultsets from select-statements and resultsets produced by prepared statements..."

Thanks for pointing that out - it's a good point.

"...so they should fit together?"

The answer is no ;)
I agree that it would be a nice feature. However, it is currently not supported by MySQL (and I don't know any construct from the SQL standard that describes this either)

"Do you know why?"

No - I don't. Many things that would be nice aren't implemented in many products.

"Is there a solution?"

Well, it depends on what you want to do.

You could perhaps leave the generation of the SQL statement to your application code, and not deal with server side dynamic sql at all. If you can, I would recommend that approach.

If you really must use server side dynamic sql, it would be pretty trivial to adjust the code that generates the statement text that you excute dynamically, and wrap the static part of the statement ('select * from dislike where name in (...)') in there.

Finally, if this is not acceptable for some reason (for example, if you want to run a cursor on dynamic sql), you could try to use dynamic SQL to generate a view, and use that view instead. In this particular case you would have to somehow generate the sql statement without a reference to the ? placeholder (so you would have to hardwiare the parameter as a constant value into the view code). You would also need to make the view name unique for your connection (use connection_id() as part of the view name) and after you are done, clean up the view.

I hope this helps. Just drop a new comment if you need more help.

Kind regards,

Roland.

Anonymous said...

Thank you very much for your fast answer!
So it's really a pity because I asume now, that it is a matter of fact that also the combination of select-resultsets with stored routine-resultsets will not be possible as far as procedures are concerned.
It works with functions but they can't give back a resultset but only a single value...
Maybe this will be a new feature in MySql 6?

Kind regards,
Alex

Roland Bouman said...

"So it's really a pity because I asume now, that it is a matter of fact that also the combination of select-resultsets with stored routine-resultsets will not be possible as far as procedures are concerned."

That is correct. That said, personally I don't see why you really need it to be stored procedures...why not use a view instead?

"It works with functions but they can't give back a resultset but only a single value..."

Yes, you can use function calls in SQL statements (and stored routines for that matter).

I know that postgresql and ms sql server do have facilities for this type of thing (table functions is the term for this device I believe)

Anonymous said...

Hi Roland,
I want to simulate a PROLOG-Knowledgebase with MySql and thought of using stored procedures to realize the more complex rule-statements of PROLOG.
But I thought about your suggestion of using views and will start now with that.

Many regards,
Alex

Beat Vontobel said...

Hi Alex,

Roland pointed me to your comment, as I did a presentation at the MySQL Users Conference in Santa Clara back in 2007, titled "The Declarative Power of VIEWs". There I compared PROLOG to MySQL and implemented a simple knowledge base/expert system using MySQL VIEWs.

So maybe you might want to check that out online: It's definitely something along the same lines you want to go, though just a tiny "proof of concept"/demo.

Cheers,
Beat


Slides (PDF): http://www.futhark.ch/upload/The_Declarative_Power_of_VIEWs.pdf

Source (SQL): http://www.futhark.ch/upload/expert.sql

Online Demo (log in using MySQL command line client, password "demo"): mysql -h mysql.futhark.ch -u demo -p expert (mysql://demo:demo@mysql.futhark.ch)

Anonymous said...

Hi Beat,

Congratulation!
This is really great stuff - Excellent research work!

Many regards,
Alex

Lila21M said...

Hi:
I really like you article,it is very good and it help me with a problem but I have another one, I´m doing a store procedure and already I have it and it works but I have to give the name of a table as a parameter, at this moment I know how to use the prepared statement buy in my procedure I use a sentence with exists and not exists, then my question, Can I use the prepared stmt with exist?

This is what I´m trying

BEGIN
declare anio int(4);
declare mes int(4);
set mes=1;

set @qry1=CONCAT('select min(', usingtable,'.anio) into @result from ', usingtable);
PREPARE stmt1 FROM @qry1;
EXECUTE stmt1;
set anio=@result;
DEALLOCATE PREPARE stmt1;


while anio<=2007 do

while mes<=12 do

if exists
set @qry2=CONCAT('SELECT ', usingtable,'.anio from ', usingtable,' where ', usingtable,'.anio=anio');
PREPARE stmt1
FROM @qry1;
EXECUTE stmt1
DEALLOCATE PREPARE stmt1;

and not exists

set @qry3=CONCAT('SELECT ', usingtable,'.anio, ', usingtable,'.mes from ', usingtable,' where ', usingtable,'.anio=anio and ', usingtable,'.mes=mes ');
PREPARE stmt1
FROM @qry3;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

then
....
....

The error: Script line: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @qry2=CONCAT('SELECT ', usingtable,'.anio from ', usingtable,' where ', usin' at line 21

I hope you can help me

Thanks in advance

Roland Bouman said...

Hi Lila21M,

"Can I use the prepared stmt with exist?"

yes, should just work. However the way you set it up will not work, because the EXISTS operator expects a query. Although you can use a prepared statement to execute a query, it is not a query itself - it is a prepared statement. Confusing perhaps, but it does make sense.

Anyway, there is another way to solve your problem. Basically, your code has this form:

IF EXISTS ..dynamic SQL...
AND NOT EXISTS ..dynamic SQL...
THEN ...

The trick is to execute the dynamic SQL before the IF, and test for the result inside the IF. Now, I don't have quite enough information to write the most optimal solution, but one way of doing it would be:

set @qry2:=CONCAT(
'SET '
,'@r1 := (SELECT COUNT(*) FROM ', usingtable, ' WHERE anio = ', anio,')'
,',@r2 := (SELECT COUNT(*) FROM ', usingtable, ' WHERE anio = ', anio,' AND mes = ', mes,')'
);
PREPARE stmt2 FROM qry2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

IF @r1 = 1 AND @r2 = 0 THEN
....


I hope this helps,

kind regards,

Roland

Lila21 said...

Hi:

Thank you, the way you said works.
I really appreciate your help

Regards.

Lila

Anonymous said...

Hello, I already learned a lot reading the above explanations. But now I'm stucked with the following.

I want a (nested?) loop to update the (golf) handicap from players based on results in a match. I have this (not working) loop:

SELECT COUNT(id) INTO @count FROM scores WHERE user_id = 1 GROUP BY user_id;

SELECT @handicap:=handicap, @score:=punten FROM scores WHERE user_id = 1 ORDER BY datum ASC LIMIT 1;

SET @user := 0; WHILE @user <> @count DO SELECT @id:=id,@handicap:=handicap,@score:=punten FROM scores WHERE user_id = 1;
SET @adjustment = CONCAT("SELECT ","P",@score," INTO @adj FROM adjustment WHERE handicap = ",@handicap);
PREPARE stmt FROM @adjustment; EXECUTE stmt; UPDATE scores SET handicap = @handicap+@adj WHERE id = @id;
SET @user = @user + 1; END WHILE;

The idea is that a table is sorted > the handicap on the first of January is retreived and that the second hcp. is adjusted based on the first hcp., the third based on the second, etc.
If this is done for one player then the loop starts again for player two, etc. If someone can give me a push in the right direction this will be very much appreciated!

Rob

Ishan Dave said...

Hi Roland,

First of all Thank You Very Much for a very helpful article you published. It helps a lot.

well I need your help in one of my problem. which is as follows.

I tried to execute a dynamic query in a function but not succeed and then I come to know that dynamic queries are not supported in functions.
So I move my code to a procedure where it works fine & set a out variable as output.

Then I try to call this procedure in a function as any way I want to return a value but when I try this again I go to :-( the previous loop of following error...
Error Code : 1336
Dynamic SQL is not allowed in stored function or trigger

so is there any way to execute the dynamic queries in a function ?

Actually by doing all these what I want to achieve is as follows. So if you have any idea for how to achieve these then plz share your valuable views.

I am having a table
1. DataTable
id-----field1---field2---fieldn---itemid
1--------5--------2--------10--------1
2--------8--------7--------9---------2

Now I having one meta data table where the definition of field1, filed2 etc is defined at row level.
2.MetaDataTable
id----- fieldName-----fieldDbColumnName
1--------TAX1--------------field1
2--------TOTTAX1---------field1*50
3--------TAX2--------------field2
4--------Something-------field1*field2

So these are my two tables & I want actual field value from DataTable by passing a fieldname of MetaDataTable
so my query would be something like...

select (select fieldDbColumnName from MetaDataTable where fieldName like 'TAX!')
from DataTable where itemid = 1;

desired output should be : 5
but it gives output as : 'field1'

this query returns 'field1' not the value at field1.

I mean the above query should behave like
select field1 from from DataTable where itemid = 1;

the same can be accessed from following link as well.
http://forums.mysql.com/read.php?118,279075,279075#msg-279075

Thanks a lot in advance for your time and guidance.

Roland Bouman said...

Hi Ishan!

thanks for your kind words, I'm glad you like it.

"so is there any way to execute the dynamic queries in a function ?"

No. The limitation is listed here: http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

I agree that it would be nice if this limitation was lifted.

As for your problem: you are mixing the metadata query directly with the data query. The way to go is to query the metadata first to get the information to generate the appropriate sql statement text for the data query, and then dynamically execute that. You can either use PREPARE for that, or you can solve it in your application.

That said, I think it is better to avoid these types of solutions. I mean, what is the benefit of queying your database through this self-made metadata layer? It would be much better if you could simply avoid dynamic SQL and write the appropriate query directly.

kind regards, Roland

Ishan Dave said...

Hi Roland,

Thanks for a prompt reply. Finally I'll go for the simple query instead of dynamic. The reason why I choose the above way is that in my application I would like give the flexibility to the users to define the formulas for instance.

in UI they see Tax1, Tax2, Tot Tax1 etc... but in back end I am having field1, field2 etc
and I store the formulas accordingly. So this way there is not dependency on business layer. User will define what they want and my query will give the results dynamically. But :-( unfortunately time constraint and some other pending tasks are there so I prefer to go by other way. Anyway when I success will again come to disturb you.

Thank you very much for your valuable inputs which really help me.

One more thing would like add is, "The way you answer our queries and the way you explains the things, it creates a feeling that you are standing in front of us and guiding us."


with best regards,
Ishan Dave

Roland Bouman said...

Hi Ishan!

ok - I'm glad I could help out. Thanks for the compliment - Post back anytime.

Anonymous said...

Hi..
Is it possible to use 'Prepare' stmt alng wt 'Alter Table' to add new columns in a table?

The column name however is stored in a variable thats wat creates the problem!

So is possible add columns to a table where required column name is stored in a variable?
If yes could you depicte the code?

-DNA

Roland Bouman said...

Hi Anonymous,

"
Is it possible to use 'Prepare' stmt alng wt 'Alter Table' to add new columns in a table?

The column name however is stored in a variable thats wat creates the problem!
"

If you do it like this, there should be no problem at all:

SET v_column_definition := CONCAT(
v_column_name
,' ',v_column_type
,' ',v_column_options
);
SET @stmt := CONCAT(
'ALTER TABLE ADD COLUMN '
, v_column_definition
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


However, you cannot use the ? placeholders for this type of thing - those are only valid for *value expression* - not identifiers, keywords etc.

picas.line said...

Hello Roland..
I Have a stuck in this kind of variable table name in my Mysql stored procedure..


DROP PROCEDURE IF EXISTS addnew;

DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `db_simaset`.`addnew`(in ptabel varchar(20), in kodeaset varchar(20), in banyak numeric, in awalancounter numeric)

BEGIN
set @awalancounter=awalancounter;
set @inc=1;
set @ptabel = (SELECT tabel from tb_mtahun where tahun = (SELECT YEAR(CURRENT_DATE()) as tahun ));


while (@inc <= banyak) do
begin
set @awalancounter=@awalancounter+1;
insert @ptabel tb_aset_2009 (kode_aset, no_aset) values(kodeaset,@awalancounter);
set @inc=@inc+1;

end;
end while;

END$$

DELIMITER ;


The name of the table was saved in a table so i'm doing a query to save in variable

set @ptabel = (SELECT tabel from tb_mtahun where tahun = (SELECT YEAR(CURRENT_DATE()) as tahun ));

and the inserting data is

insert @ptabel tb_aset_2009 (kode_aset, no_aset) values(kodeaset,@awalancounter);
set @inc=@inc+1;

what wrong with MySQL so i cant use the variable table name..
Please help me about this problem..

Roland Bouman said...

Hi Picas, I am not exactly sure what your question is. But I get the feeling there are a couple of things wrong with your code. For exampl, you are mixing user-defined variables with local variables. Perhaps you should clean up your code and ask on some forum. Be sure to alsways mention what error message you are getting.


good luck,
Roland.

picas.line said...

I'm so sorry make you confuse..

but the main trouble is "I can make the stored procedure work with a variable table name".

the stored procedure can only work with static table name..

I have a table with huge amount of data, so i divide the table in yearly format as follow :

tb_asset_2007
tb_asset_2008
tb_asset_2009

the inserting data is using stored procedure by PHP Program..

I want in my stored procedure can do a dynamic inserting data like this

insert into tb_asset_$year

but the error was unknown table..
do you have any solution..
Please Help..

Roland Bouman said...

Just read my post - this post. The answer is in there alrady. Keyword: "identifier"

picas.line said...

or maybe this query have a trouble

@sql_text:=concat('insert into ',@tahuntabel,'(kode_aset, no_aset) values('kodeaset,@awalancounter')');

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@sql_text:=concat('insert into ',@tahuntabel,'(kode_aset, no_aset) values('kodea' at line 10
(0 ms taken)

Picas said...

someone read my problem..it was just clear with the single and double quote in field name..


'"FieldName"'

Michael Baker said...

This is the closest blog to help with my problem - THANK YOU.

Here is my stored function:

CREATE DEFINER=`root`@`localhost` FUNCTION `inClass4`($scode varchar(40),$idnumb varchar(20),$cllist text) RETURNS varchar(200)
BEGIN

declare idn,stuid varchar(20);
declare $clist varchar(400);
declare $stmt_txt varchar(400);

set $clist = cs2in($cllist,',');


set $stmt_txt = "select distinct id into stuid from stuclass where id='";
set $stmt_txt = CONCAT($stmt_txt, $idnumb , "' and schcode='", $scode);
set $stmt_txt = CONCAT($stmt_txt, "' and clname in " , $clist);

PREPARE stmt FROM $stmt_txt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
set idn = stuid;
return idn;
END

When I try to save this, I get an error just after the PREPARE stmt FROM.

I can return the $stmt_txt and view the query without a problem, and it runs correctly.

I read above about the problems with recordsets, so I also tried the $stmt_txt as an insert into a table, then tried to read from that table. Same error.

I'd really like to know what I am doing wrong.
THANK YOU.

Roland Bouman said...

@Michael Baker:
(I just approved your comment, but blogger is acting funky and not showing it. Just replying anywyay....)

thanks for the kind words. Unfortunately, the PREPARE syntax is not allowed in functions (and also not in triggers). It's documented here:

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

I know it sucks, but that is how it is.

If you just need a stored routine that returns a value, you can rewrite your code as a stored procedure using an OUT parameter to convey the return value.

There is another problem in your code though. You are using this SQL to grab a value from the database:

set $stmt_txt = "select distinct id into stuid from stuclass where id='";

where stuid is a local variable in your routine. Although this will compile, it will generate a runtime error (undeclared variable).

The reason is that the prepared statement is executed in its own scope, and does not see the variables of the procedure that defines it.

The work around is to use a user-defined variable instead (http://dev.mysql.com/doc/refman/5.1/en/user-variables.html)

So, your SQL should be:

set $stmt_txt = "select distinct id into @stuid from stuclass where id='";

etc.

Michael Baker said...

THANK YOU - I will try this

Michael Baker said...

I am really missing something obvious.

I have rewritten the basics as a procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Counts`())
BEGIN

declare $clist ,$stxt varchar(200);

set $stxt = "select 12345";

prepare stmt from "select 12345";
execute stmt;
END

That works and compiles.

However, this doesn't:

prepare stmt from $stxt;

I can't figure out why. I'm using mysql v. 5.0.21 if that matters.

Thanks again! (and do you consult.....?)

Roland Bouman said...

Hi Michael,

yeah this is a quirk, but I do actually mention it in my post here beneath the heading "Dynamic SQL":

"For no particular reason, PREPARE accepts either a string literal, or a global user variable to define the statement to prepare."

You are using a local variable. If you do something like this:

set @stxt = "select 12345";

prepare stmt from @stxt;

it does work.

Concerning consulting: depends...send me a private email at roland.bouman@gmail.com with you request, and I will consider it.

Thanks, and kind regards,

Roland

Anonymous said...

hi,
My name is Sumit. I have a situation where i am creatig a prepared statement as i have ti give the value to limit dynamically.The code is as follows:--

set @a=concat('select PushQueueID,mobileno,RegDateTime,SentDateTime,Comments,PushProviderID,status, ResponseMessage,KeyWord,
case when MC.Source is null then PQ.Source else MC.Source end as Source,
case when PQ.Message is null then MC.Message else PQ.Message end as Message,
StartSchedule,EndSchedule,PQ.MessageID
from PushQueueMessages PQ Left join MessageConf MC on PQ.MessageID=MC.MessageID and length(MC.message) > 1
where
Status=0 and recordstatus =1
and PushProviderId=intPushProviderId
and StartSchedule <=sysdate() and sysdate()< EndSchedule limit ',throughPut);

PREPARE stmt2 FROM @a;

now what i want to do is i want the whole result to be put into a temporary table name bkpTempTbl;

when i try to do
create TEMPORARY table bkpTempTbl EXECUTE stmt2 ;

it gives me an error

Please help

Anonymous said...

THANK YOU SO MUCH! Excellent article!

Anonymous said...

Hi Roland- looks like it's been a few months since any activity on this post/blog. But, I'll try anyway...

Nice article by the way. Helped me a bunch. But I still have a problem.

I'm "cloning" a web app a sister company wrote. They dropped it on top of an MS SQL Server db and my company doesn't want to drop the coins for SQL Server so we went with MySQL. The sister company wrote the app with every query in a stored procedure and there are a few that employ dynamic SQL. My boss doesn't want me to modify any of the backend server code if at all possible. So I'm kind of stuck at this point.

The query I'm trying to dynamically build is one query with 4 sub-queries and uses 3 of the parameters in all 5 queries. I set up 15 user variables and assigned them as such (just an example - not the real thing):

declare uservar1, uservar2, uservar3, queryText varchar(500);


##build the dynamic query substituting ? for the parameter names...

set queryText = 'select....';

set uservar1 = param1;
set uservar2 = param1;
set uservar3 = param1;

set @a = uservar1;
set @b = uservar2;
set @c = uservar3;

prepare stmt from @queryText;

execute stmt using @a, @b, @c;

deallocate prepare stmt;

END;

I get the error: Incorrect arguments to EXECUTE

You stated:

"You really must match each placeholder by exactly one user variable, or else you will encounter an error..."

I thought this should work but doesn't. Any idea why?

Thanks so much for your blogs. I've read several of them and they are all informative.

Thanks,
David

Roland Bouman said...

Hi David,

thanks for your kind words.

Regarding the error you encountered: I would say the most likely cause is that you accidentally have a placeholder too many or too little in your generated statement.

If you run the procedure, and then do

select @queryText

you can see the statement you generated. If you can't find it, don't hesitate to post back and I'll give it a look.

cheers,

Roland.

Anonymous said...

Hi Roland,

Wow! Thanks for the quick response!

You know what, I did a boo-boo when I was created the procedure. I quoted the ? (double singles - ''?''). I removed those quotes and viola!

Also took out the 15 vars and went with:

set @a=param1;
set @b=param2;
set @c=param3;
set @d=param1;...

Much cleaner.

Thanks again!
David

Roland Bouman said...

Hi David!

yeah, I was actually going to suggest the direct assignment, but I was multitasking and, as usual, doing two things less than half assed so it slipped my mind :(

Anyway, you figured it out yourself - good :)

Cheers,

Roland.

Anonymous said...

Hello Roland!
gr8 job! u explained evrything very lucidly in ur article......

Anonymous said...

Hi Roland!
Simply d Gr8 work!! U explained evrything very lucidly..
Plz help me with my problm.....
I am developin an audit trail application in which i hav to record

all updates n deletes made to the tables in the database.

For this i create a table auditlog as

CREATE TABLE `an1`.`auditlog` (
`tableName` varchar(60) NOT NULL DEFAULT 'nA',
`colum` varchar(50) NOT NULL DEFAULT 'NA',
`old` varchar(50) NOT NULL DEFAULT 'NA',
`new` varchar(50) NOT NULL DEFAULT 'NA',
`UpdatedOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON

UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Than i create update trigger on the tables in the database. Inside

trigger im tryin to compare old value n new value of each column,

if not equal then old value get inserted into auditlog table. My

code is.....

CREATE TRIGGER T3 AFTER UPDATE ON tab
FOR EACH ROW
BEGIN
DECLARE COL VARCHAR(20);
DECLARE done int default 0;
DECLARE CUR1 CURSOR FOR select column_name from

information_schema.columns where table_name='tb1' AND

table_schema='an1';
declare continue handler for not found set done=1;
OPEN CUR1;
REPEAT
FETCH CUR1 INTO COL;
IF not done THEN
IF concat('OLD.', COL) <> concat('NEW.' ,COL) THEN
INSERT INTO auditlog VALUES('TB1', COL, concat('OLD.', COL),

concat('NEW.' ,COL), now() );
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE CUR1;
END;//

Im getting the err: Unknown column name COL.
Can i use prepare Statement 4 this?? how??
Plz tell me hw to use variables for "column names".

Anshul

Roland Bouman said...

Hi Anshul,

there are a few problems with your code. The main problem is that you cannot dynamically refer to the OLD and NEW columns. There is a way around it though - you can write queries on the information schema that generate the trigger code that performs the task of logging to the audit log.

However, there are a few serious problems with using triggers. The main problem is that triggers are not fired for UPDATEs and DELETEs that result from cascading foreign keys. Another problem is that triggers are quite slow, so you're going to get a performance hit. The third problem is that you can have only one type (as in BEFORE/AFTER, INSERT/UPDATE/DELETE) of trigger per table, so if you're using these audit triggers, you cannot also use application triggers (or you'll have to find something clever to merge application level triggers with audit triggers. If you generate the code, you code implement some hooks in there to always call a stored procedure but still, it's not a very elegant solution.

I would advise you to try it another way. You simply need an audit trail, right? Couldn't you use the binary log? If you use row-based logging, and then use the mysqlbinlog tool (in combination with --verbose) you can see exactly what is going on. You just need something that periodically analyzes the binlogs.

Anonymous said...

Hi Roland,

It's David again from a few weeks ago. How are you?

I'm working with a SP and the SP has to take a "list" (could be 1, could be 10) of IDs. Part of the select statement is "select * from table where id in('id1', 'id2', 'id3') and ...". Now, that works perfectly if the application generates the query string and submits it to the DB. But the SP returns nothing. The app stores the IDs in a variable like this:

parm1 = "'id1', 'id2', 'id3'";

Again, if the app generates the query it works but if that same string is passed to a call to the SP, I get nada.

I coded the SP to generate a dynamic query as well as a static one. I cannot get it to work either way.

Have any ideas?

Thanks,
David

Roland Bouman said...

Hi David,

if you're using a plain, "orphan" SELECT statement (in order to return the resultset to the client), it should work fine with the dynamic SQL statement. Most likely, there is some error in the generated SQL. Just use

SELECT @generated_sql;

to find out if there is some logical error in it. If you like, you can post it here, and I can try to have a look at it.

PS: I noticed you are quoting the values. Is that correct? I mean, typically ID is a surrrogate integer key, so I'd expect a list of integers like:

id IN (1,2,3,...)

Anonymous said...

Hi Roland!

I am new to MySql and i dnt knw how to use binary log and mysqlbinlog tool..
for audit trailing. Plzzzz explain me with some example....

Anshul

Roland Bouman said...

Anshul,

if you're completely new to it, it's probaly best if you read the relevant documentation. Follow these links and read all of it:

http://dev.mysql.com/doc/refman/5.1/en/binary-log.html
and
http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html

Anonymous said...

Hi Roland,

Yeah, if the ID was simply an integer it would make this whole deal easier. But, the ID field in this case is a char field and the values can be alpha, numeric or alphanumeric. So, got to have them quotes.

I did look at the generated SQL using select @queryText. Didn't look like there were any errors.

I'm going to go with the app generated select statement for now as it works.

Now if I can figure out how to get the slow, slow query to execute faster. I think it's the in(). Am I correct in my thought that if columns are passed through a function like in() in a select, that MySQL cannot use an index then? When the user selects just one ID, the query returns in about 2 or 3 seconds. When the user selects 2 or more IDs, the query takes 3 to 5 minutes...

Thanks!
David

Roland Bouman said...

Hi David,

nah, typically IN for a list is fast, like it's fast for a single value. But do you have the right data type? If the column is int, you shouldn't quote the values.

Anonymous said...

Hey Roland,

Yeah, the column is not int though, it's character. The original developer decided to let alpha, numerics and alphanumerics be allowed and all three are in there.

I've got it worked out with temporary tables for now. It's kludgy but it works and it's fast. Now hopefully it will be just as fast, or close, when there is more than just me hitting the DB!

Thanks Roland!

David

Anonymous said...

Hello Roland!

This is Anshul again with audit trail Problem.
As u suggested, im least interested in using binary logs, as it is complicated nd it may create a problem if it is disabled at server side.

The other way u suggested - write queries on the information schema that generate the trigger code that performs the task of logging to the audit log.

I know the information schema nd the tables in it, but plz tell me how to write such queries that generate trigger code.. Plzzzzzzz

Anshul

Roland Bouman said...

Anshul,

please make an effort yourself first. If you get stuck, you can tell me where you got stuck, and then I can try to help. Of course, you can also try to find help on one of the numerous online forums.

Anonymous said...

I want to ask roland, can make the procedure using the prepare statement ?

for ex. :
PREPARE query FROM ‘CREATE PROCEDURE myProc (IN field1 varchar(5),
IN field2 varchar(10))
BEGIN
INSERT INTO Table1 VALUES (field1, field2);
END’;
EXECUTE query;

thank's

regards,
Febry

Roland Bouman said...

@Febry,

yes. But your PREPARE syntax is wrong. Read the article.

haha said...

Roland that is an awesome article. The usage of plain English alongside step-by-step made it very readable even for newbie programmers with a mother tongue which is not English. Peace man.

Mr. Parker said...

good day sir!... can you give me a more step by step tutorial regarding parameters and dynamic sql?

Roland Bouman said...

@Mr. Parker, the blog post covers pretty much what you are asking for. If you feel you need another tutorial, buy a book or something like that.

hope this helps,

roland.

Mr. Parker said...

ok sir!... actually I have an prepared statements here or rather parameterized query... but i have a problem with regards to displaying many records... In addition to this I have a problem regarding bind_param;

prepare("select name from tbltest")) {

/* this is my problem
$stmt -> bind_param("ss", $user, $pass, $db);*/

$stmt -> execute();

$stmt -> bind_result($result);

$stmt -> fetch();

echo $user . "'s level of priviledges is " . $result;

echo $result;
$stmt -> close();

}

$mysqli -> close();

?>


by the way thanks for your reply...

Roland Bouman said...

@Mr Parker,

the sample code you posted is PHP. This is completely outside the scope of this blog post.

Apart from that, you mention that you have a problem, but fail to give even the slightest clue as to exactly what problem you are experiencing. Your code doesn't help either - it won't even run as it contains PHP syntax errors.

Please, put some effort into properly researching what problem you're trying to solve. And please, post your questions on a forum, or google it.

Mr. Parker said...

sir, sorry for yesterday... I beg your pardon...anyway, last question, can I know what is the most popular editor on creating "dynamic" sql scripts in ubuntu or on a windows Operating System?

Roland Bouman said...

Mr Parker, this is a very interesting question...If only there would be a website that would allow people to search the internet, or a site that would allow people to post question, so that other, more knowledgeable people could answer them...

Anonymous said...

Hi Roland,
I m using prepared statement in one of the my stored procedure where i derive table name from some input parameters.
i am afraid if the global variables used in my query will give me proper results in mysql cluster's multi threaded environment.

my code is like:

SET @qrytext = NULL, @authId =0;
SET @qrytext = CONCAT("select auth_id into @authId from ",Derived_Table," where dept = ",in_dept," and service = ",in_service);
PREPARE stmt FROM @qrytext;
EXECUTE stmt;
SET l_authId = @authId;
DEALLOCATE PREPARE stmt;
SET @qrytext = NULL, @authId = 0;

please provide your views about performance of prepared statement.


i m a beginner in mysql so please ignore if m asking some foolish thing to u.

Thanks,
deep

Roland Bouman said...

Deep,

I read your comment but I don't have a very clear understanding of what you're asking from me.

Anonymous said...

Hi Roland,

is it possible that after execution of statement
EXECUTE stmt;
say it fetched 76 in @authid but before execution of
SET l_authId = @authId;
some other thread may change the value of @authid to say 89 and i get 89 in l_authid instead of expected value 76,
because of mysql's multithreaded env and as @authid is a global variable.
i hope u may get some understanding of my doubt by this.
basically is it possible for some other thread to access @authid and change value in between execution of EXECUTE and SET statements.
do i need to use some locking before and after these two lines?
also if everything is ok than i also want to know about performance of above code as my application will require very high scalability.

Thanks,
Deep.

Roland Bouman said...

Deep,

please note that this is not a free forum. Go to stackoverflow next time.

Short answer: no, that is not possible. @auhid is a user-defined variable. User-defined variables have session scope, not global scope. So if it contains a value you didn't expect, I'm afraid your procedure is wrong somewhere.

Anonymous said...

Thanks Roland,

My application is not doing that yet but i was thinking of a high load scenario, so this doubt came in my mind,

anyways thanks a ton for your responses.

deep

Roland Bouman said...

Deep,

is it possible the last time you called the prepared statement the derived table didn't yield any rows, and thus did not write a new value to the user-defined variable? In that case, it retains whatever was in it from the previous call.


HTH.

Anonymous said...

I dont think this is possible because i m initializing this variable in the very first line
SET @qrytext = NULL, @authId =0;
and then preparing the query.

and i would like to repeat that the problem i raised is not happening now but i was just thinking if this kind of situation may arrive in high load (TPS) scenario thus asked you about it.

thanks again
deep

Roland Bouman said...

Hi Deep,

do you mean this behavior isn't happening now, or indeed never happened at all? If you want to know the to be expected behavior, please check the manual. MySQL's manual is pretty good:

http://dev.mysql.com/doc/refman/5.1/en/user-variables.html

When in doubt where to start, just read all of it (that's what I did), you're bound to learn what you need to know eventually.

HTH,

Roland.

Anonymous said...

Hi Roland,
it's possible do that??

set sql_string = concat("insert into ",out_table," (id_record,nome_campo,data,table_name,operation)
values (check_id,check_fieldname,now(),check_tablename,check_operation);");
prepare stmt from sql_string;
execute stmt;
deallocate prepare stmt;

I have an syntax error but i don't understand where.
Thanks!!
nando
p.s: sorry for my english

Roland Bouman said...

Nando,

you're English writing skills are fine - I could understand your comment just fine.

Your error reporting skills however are sub-par considering that you didn't supply any specific error message :p

Having read the statement you're trying to prepare, I think I know what it is though.

My advice is that you simply read my blog article. All the information you need to solve your problem is in my article, clear-cut, plain and simple.

Good luck.

Roland.

Anonymous said...

Roland thank you very much for your fast answer!
I have a syntax error near the prepare stmt. The complete procedure is:

CREATE PROCEDURE check_tab_out(IN check_id INT,IN check_TableName VARCHAR(50),IN check_operation VARCHAR(1),
IN check_FieldName VARCHAR(50),IN check_value_new VARCHAR(50),IN check_value_old VARCHAR(50))
BEGIN

DECLARE done INT DEFAULT 0;
declare out_table varchar(50);
declare sql_string varchar(250);
declare crsINS cursor for select tab_out from tab_mw_config where tipo_op='I';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

if (check_operation='I') then
open crsINS;
ciclo: loop
fetch crsINS into out_table;
if (done=1) then
leave ciclo;
else
set sql_string = concat("insert into ",out_table," (id_record,nome_campo,data,table_name,operation)
values (check_id,check_fieldname,now(),check_tablename,check_operation);");
prepare stmt from sql_string;
execute stmt;
deallocate prepare stmt;
end if;
end loop ciclo;
close crsINS;
...

i have read the article but i don't find the error.
Thanks anyway fro the answer!
Nando

Roland Bouman said...

Nando,

PREPARE stmt from <local-variable>;

is not valid. Instead use

PREPARE stmt from <user-defined-variable>;

so your code should be:

set @sql_string := concat('...', '...' etc.);
prepare stmt from @sql_string;

Anonymous said...

Thanks Roland,
i put this procedure in a trigger e now i have another problem.
"Dynamic sql is not allowed in a stored function or trigger".
But i use the dynamic sql in a procedure and not in a trigger!!
I must find another way or there is a little hope??

Roland Bouman said...

Nando,

it always goes like this. People post a comment that is only slightly on topic, and I try to gently point them to the manual. They come back, I help em one step, and they post again: "now i have other problem".

Please. This is not a forum. This is not google. This is not stackoverflow. Don't abuse the comments section for these things.

thanks for understanding.

Somi said...

Hi Roland,
I have just started studying mysql few months ago
I want to create a trigger which sends automatic emails on updation of any field in database
i wrote a trigger
delimiter //
create trigger email_sender
after update on loan for each row
begin
set @to="myfriend@haha.com";
set @from="saumitra@gmail.com";
set @subjct="hello";
set @body="body";
set @update=100;
set @email_path=concat("'",'c:\\',now()+0,'.eml' ,"'");
set @myvar=concat('select ','\n',"To:",@to,'\n',"From:",@from,'\n',"Subject",@subjct,'\n',"Body:",@body,'\n',@update,'\n',' into outfile ', @email_path);
PREPARE sender FROM @myvar;
execute sender ;
DEALLOCATE PREPARE sender;
end; //
delimiter ;

but it is showing an error
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

can you help in this
thanks somi

Roland Bouman said...

Hi Somi,

like the error message indicates, dynamic SQL inside triggers is not allowed. Last time i played with this kind of stuff, i think you could get around it by putting the dynamic sql inside a procedure and calling that from the trigger. Otherwise, you're out of luck I'm afraid.

Can't you just poll for changes from the application layer, and send mail from there?


regards,

Roland

Anonymous said...

hi Roland,
i have a question about the mysql stored procedures prepare statement. The code is something like below:

prepare updateQuery from @updateSQL;
execute updateQuery;

my question is when I execute, how do i know any record being updated?
Regards,
John

Roland Bouman said...

Hi John!

this is an excellent question, thanks!

I think you should be able to figure out the number of rows by calling the ROW_COUNT() function immediately after executing the prepared statement. see: http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_row-count.

Please let me know if this works.

kind regards,

Roland

Anonymous said...

Hi Roland,
Thanks for the prompt reply. Your answer perfectly works for me, but i wonder an extract
select row_count statement will cause any overhead?
Regards,
John

Roland Bouman said...

Hi John,

calling ROW_COUNT() itself is not expensive. MySQL automatically computes the value, grabbing it is cheap.

What may not be so cheap is the roundtrip to run another SQL statement. You can try and wrap multiple statements in a stored procedure to minimze the number of roundtrips.

HTH

Roland

Murali.V said...

Hi Roland,

This is really very nice thread, i have been following it from the beginning..

Thanks,
MuraliV

Anonymous said...

Hi Roland,
I have a series of (16)PREPARE, EXECUTE and DEALLOCATE statements (in a stored procedure), each inserting rows into a diffterent table (table 1 to table16)
eg.
------------------------
SET @Command1 = CONCAT("insert into TABLE1" , ...etc.. );
PREPARE stmt1 FROM @Command1 ;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SET @Command1 = CONCAT("insert into TABLE2" , ...etc.. );
PREPARE stmt1 FROM @Command1 ;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

.
.
.

SET @Command1 = CONCAT("insert into TABLE16" , ...etc.. );
PREPARE stmt1 FROM @Command1 ;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
-------------------------------------
But when I execute the stored procedure, the INSERT works intermittently. sometimes all the 16 inserts works, but sometimes it doesn't. In the last CALL of the stored procedure, the first 2 inserts (into TABLE1 and TABLE2 ) and the last 4 inserts (TABLE 13 to 16) work, but not the inserts into Table 3 to 12.
Can you explain why ? Can't be because I'm using the same variable/handle command1 and stmt1 ?
Paul

Anonymous said...

Took me time to read all the comments, but I really enjoyed the write-up. It proved to become Pretty useful to me and I am positive to all the commenters here Its always great when you can not only be informed, but also entertained Im certain you had fun writing this write-up.

zserj kian said...

hello sir Bouman.

im randy, im a starter of mysql, i have a question in alter table add column,. here my code

$title = "title";
$alter = mysql_query("ALTER TABLE tablename ADD '".$colname."' varchar(3) not NULL ");

no error came up, but it didnt get my desired output,,,i wrote here because i found it very relative and interesting blog to me...thank you..

Roland Bouman said...

zserj,

I think you should ask questions like this on stackoverflow.com or on forums.mysql.com

Joe J said...

Roland,

First off great blog post. I am running into a similar problem to what Flopsy posted a couple years back where I am trying to run a PREPARE statement inside a CURSOR. You noted that there is almost always a way to achieve this without a PREPARE or a CURSOR; I wanted to get your feedback as a possible way to eliminate these pesky structures.

My specific situation is a shopping cart situation where I am trying to determine if a discount is applicable. The is_applicable column contains a COUNT() SQL statement; if the COUNT is greater than 0, then the discount would apply.. As a site may contain multiple discounts, I had anticipated using a cursor to iterate through each of the site's rows and then evaluate the row to determine if the discount is applicable. The (simplified) table in question is:

CREATE TABLE IF NOT EXISTS `discounts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`is_applicable` varchar(1000) NOT NULL,
`site_id` int(11) NOT NULL,
`amount` decimal(6,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The (simplified) stored procedure is as follows:
CREATE PROCEDURE spTest()
BEGIN
BEGIN
DECLARE @colsql VARCHAR(1000);
DECLARE Finished BOOLEAN DEFAULT FALSE;
DECLARE Cur CURSOR FOR SELECT is_applicable FROM discounts;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;

OPEN Cur;
LOOP1: LOOP
FETCH Cur INTO @colsql;

IF Finished THEN
CLOSE Cur;
LEAVE LOOP1;
END IF;

SET @sqlStatement = CONCAT('SET @output = (', @colsql, ')');
PREPARE stmt FROM @sqlStatement;
EXECUTE stmt;

IF @output > 0 THEN
-- Do Stuff
END IF;

DEALLOCATE PREPARE stmt;
END LOOP LOOP1;
END;
END

Any input on ways to get around this limitation are valued. If I can't get away from either the cursor or the dynamic sql, I will be headed down one of the other options you outlined in a response to Flopsy, either TEMPORARY TABLE option or VIEW option.

Thanks for your time!
Joe J

Joe J said...

Roland,

First off great blog post. I am running into a similar problem to what Flopsy posted a couple years back where I am trying to run a PREPARE statement inside a CURSOR. You noted that there is almost always a way to achieve this without a PREPARE or a CURSOR; I wanted to get your feedback as a possible way to eliminate these pesky structures.

My specific situation is a shopping cart situation where I am trying to determine if a discount is applicable. The is_applicable column contains a COUNT() SQL statement; if the COUNT is greater than 0, then the discount would apply.. As a site may contain multiple discounts, I had anticipated using a cursor to iterate through each of the site's rows and then evaluate the row to determine if the discount is applicable. The (simplified) table in question is:

CREATE TABLE IF NOT EXISTS `discounts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`is_applicable` varchar(1000) NOT NULL,
`site_id` int(11) NOT NULL,
`amount` decimal(6,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The (simplified) stored procedure is as follows:
CREATE PROCEDURE spTest()
BEGIN
BEGIN
DECLARE @colsql VARCHAR(1000);
DECLARE Finished BOOLEAN DEFAULT FALSE;
DECLARE Cur CURSOR FOR SELECT is_applicable FROM discounts;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;

OPEN Cur;
LOOP1: LOOP
FETCH Cur INTO @colsql;

IF Finished THEN
CLOSE Cur;
LEAVE LOOP1;
END IF;

SET @sqlStatement = CONCAT('SET @output = (', @colsql, ')');
PREPARE stmt FROM @sqlStatement;
EXECUTE stmt;

IF @output > 0 THEN
-- Do Stuff
END IF;

DEALLOCATE PREPARE stmt;
END LOOP LOOP1;
END;
END

Any input on ways to get around this limitation are valued. If I can't get away from either the cursor or the dynamic sql, I will be headed down one of the other options you outlined in a response to Flopsy, either TEMPORARY TABLE option or VIEW option.

Thanks for your time!
Joe J

Roland Bouman said...

Hi Joe J,

in this case, it depends completely on what statements you stored in the is_applicable column.

What I would try to do is investigate the statements that are in there now, and try to categorize them. My assumption is that the different statements will fall into a fairly small set of types of statements.

Then, I'd try to analyze those statement types to see which data items (tables, columns) are used to determine if the discount is applicable.

In a further step, you'd have to analyze in what way these data items are related to the site rows. If you find that there are now relationships, then you will need to extend your database schema to store the relationships between that data and the sites to which they apply. An implicit data item of this sort is the data that determines why you assigned any given statement to a particular site.

When you did all that you should be in a position to write a single query that can determine the discounts. By then you won't need a cursor anymore, nor would you be fored to use dynamic SQL.

I hope this helps.

Anonymous said...

I want to call a stored procedure for creating table which should accept as an argument the name of the table which is to be created dynamically.How ca i do this from servlet?

Joe J said...

Roland,

Thanks for your reply and sorry for my delay in getting back to the blog. Between the holidays and work, there has just been no free time!

I believe your analysis is 100% correct in that there will likely be a small set of statements or columns that are needed to do determine if a discount is applicable. Unfortunately, as I am building the application, I only have one client and limited business knowledge and thus I am trying to build a solution that is as flexible as possible.

That being said, I was able to build a proof of concept and wanted to post it here to get your feedback as well as provide a possible solution for others who are in my predicament.

I have made some adjustments to the table and stored procedures previously posted, so I will repost them for clarity.

Discounts Table:
CREATE TABLE IF NOT EXISTS `discounts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`discount_calc` varchar(1000) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Adding some data to represent discount to be applied to a fictional invoices table:
INSERT INTO discounts (discount_calc)
VALUES ('SELECT 5 FROM invoices WHERE id = 1'),
('SELECT 10 FROM invoices WHERE id = 2');
Note: Where clause is arbitrary and can be as complex as one desires.

Invoices table:
CREATE TABLE `invoices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`total` decimal(6,2) NOT NULL DEFAULT '0.00',
`discount` decimal(6,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Fictional invoices:
INSERT INTO invoices (id, total)
VALUES (1, 100),
(2, 300);

Now for the good stuff! I have two stored procedures the first is a wrapper procedure and could possibly be eliminated for this example, however, I am leaving it as is it helps illustrate how the cursor is segregated from the worker procedure.

Wrapper Procedure:
CREATE PROCEDURE spDiscountsApplyToInvoice(IN inInvoiceId INT)
BEGIN
BEGIN
DECLARE inDiscountId INT;
DECLARE Finished BOOLEAN DEFAULT FALSE;
DECLARE Cur CURSOR FOR SELECT id FROM discounts;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;

OPEN Cur;
LOOP1: LOOP
FETCH Cur INTO inDiscountId;

IF Finished THEN
CLOSE Cur;
LEAVE LOOP1;
END IF;

CALL spDiscountsIterate(inDiscountId, inInvoiceId);

END LOOP LOOP1;
END;
END

In the worker procedure, I use the MySql CONCAT function to build a string which sets a discount variable. If this returns with a value, then we update the invoices table with the applicable discount.
CREATE PROCEDURE spDiscountsIterate(IN inDiscountId INT, IN inInvoiceId INT)
BEGIN
SET @colsql = CONCAT('SET @discount = (', (SELECT discount_calc FROM discounts WHERE id = inDiscountId LIMIT 0, 1), ')');

PREPARE stmt FROM @colsql;
EXECUTE stmt;

IF @discount <> 0 AND @discount IS NOT NULL THEN
UPDATE invoices
SET discount = @discount
WHERE id = inInvoiceId;
END IF;

DEALLOCATE PREPARE stmt;
END




After running:
CALL spDiscountsApplyToInvoice(1);

We see success:
id – total – discount
1 – 100.00 – 10.00
2 – 300.00 – 0.00

A production version of these stored procedures would include more inputs (such as a site_id mentioned in my previous post) to reduce unnecessary work and improve performance.

Thoughts? Feedback? Improvements?

Joe J

Varun said...

Hi Roland

I am actually making procedure where I need to prepare the 'execute stmt using @lv_constant_1,@lv_constant_2' statement. This is because the dynamic variables are being generated in the procedure itself.

It says 'This command is not supported in the prepared statement protocol yet'.

What can be the solution for this?

Roland Bouman said...

Varun,

MySQL doesn't support dynamic sql inside dynamic sql. How to solve it depends one what you want to do exactly. What exactly are you generating, and what does it aim to do?

Deva said...

Hi All,

Can you please help me out. I have created prepare statement in procedure. But when I execute procedure then it give me an error.
"#1243 - Unknown prepared statement handler (auto_partition) given to EXECUTE"

CREATE PROCEDURE auto_partition()
BEGIN
DECLARE _stmt VARCHAR(1024);
SET @l_SQL := CONCAT('ALTER TABLE tbl_location_history ADD PARTITION (PARTITION ', concat( 'Part_Y', year( now( ) ) , '_M', month( now( ) ) , '_W', week( now( ))+1), ' VALUES LESS THAN (TO_DAYS(\'', DATE_FORMAT(date_add(NOW(), interval 15 day),'%Y-%m-%d'), '\')));');
PREPARE _stmt FROM @l_SQL;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt;
END

Anonymous said...

I use the following in workbench:

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(property_name = ''',
property_name,
''', value, NULL)) AS ',
property_name
)
) INTO @sql
FROM
properties;
SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The following error was generated:
20:34:34 DEALLOCATE PREPARE stmt Error Code: 1243. Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE 0.001 sec

MYSQL Version: MySQL 5.5.15 via socket
Workbench Version: 5.2.44

What did I do wrong?

Roland Bouman said...

@Anonymous,

you said you did this inside workbench. Can workbench execute batches?

Type

SELECT CONNECTION_ID();

SELECT CONNECTION_ID();

and execute it in workbench the same as you did with your code. Does it return the same number for both queries? It should be. If not, I'd argue workbench is broken and you should use another tool.