how to combine string together for the execution of MySQL

ASKER

from the link:

why this one:

DROP PROCEDURE IF EXISTS aProc;
DELIMITER $$
CREATE PROCEDURE aProc(tableName VARCHAR(30))
BEGIN
	SET @s = CONCAT('SELECT * FROM ', tableName);
	PREPARE stmt FROM @s;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
$$
DELIMITER ;

Return

Stored Procedure
 



Run #1
 
Run #2
 
Run #3
 
Run #4
 
Run #5
 
Run #6
 
Run #7
 
Run #8
 
Run #9
 
Run #10
 


Real time
 
0.026
 
0.036
 
0.026
 
0.025
 
0.026
 
0.026
 
0.025
 
0.025
 
0.025
 
0.027
 


Native SQL call
 



Run #1
 
Run #2
 
Run #3
 
Run #4
 
Run #5
 
Run #6
 
Run #7
 
Run #8
 
Run #9
 
Run #10
 


Real time
 
0.028
 
0.026
 
0.025
 
0.025
 
0.025
 
0.025
 
0.025
 
0.025
 
0.027
 
0.025

I don't see it runs 10 times, or the 10 rows is what from select * From tablename?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

virtuadept

More info on prepared statements but I don't think you need to know this.

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

I think what you want is something like whhat PortletPaul posted above excelpt you want to pull in the column definitions from SCHEMA.TABLES presumably.

It seems as if maybe you are trying to write some kind of schema exporter to migrate table schema from one MySQL instance to another and I'm guessing there are automated tools to make this a lot easier.

If all you want to do is have the same table definitions but a different table name maybe you could just clone the database and then do some table renaming. Some alter statements to add auxilary audit columns.

ASKER

PortletPaul,

"is this a one-time thing? or something you need to repeat?"

what ever I see table add/dropped, we would like to generate statement like that and let us select to execute that who statement to create the new table or drop the table.

we will have a list of table we check last time and we would like to compare it with the latest one when we run that again by checking the information_schema database.

ONce we see diff between 2 x result set and we like to dump that out the difff and the use the above statement to dynamically generate MySQL to print out the new create talbe or drop table statment.

BTW, if I want to debug, any way to print out some statement the result set ? something like that:

if ( select count(*) from assgn to where eid = 1 )  > 5
  print " the total number of projects employee working is more than 5 "
else
  insert the value into the assgnto table

?

just one print ?

SELECT
      concat('create table ',TABLE_NAME,' ( not sure what you want here );')
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'xxx'

What shoud l do in "not sure what you want here"? Sorry I just know something like:

PROCEDURE `pr_new_type`( IN column_name varchar(10) )
BEGIN
SET @queryText = CONCAT('ALTER TABLE `user_rights` ADD ', column_name, ' BINARY( 9 ) NULL');
PREPARE query FROM @queryText;
EXECUTE query;
DEALLOCATE PREPARE query;
END

virtuadept,

what we want to do is:
1) we want to find out which tables has add/drop since last time we check
2) if it is dropped, then we need to know which one was dropped and record it by generate those statement.
3) we will have a table list keep a list of table we found in other database for record purpose.
4) So the drop/ create statement will work for this table.

ASKER

if we later on check the change of field name one by one and see what has been changed, then this statement use again !

It seems as if maybe you are trying to write some kind of schema exporter to migrate table schema from one MySQL instance to another and I'm guessing there are automated tools to make this a lot easier.

I absolutely agree with virtuadept and I think you may have better things to do than re-invent wheels.

I would suggest you start looking for the tool(s) you need rather than try to build one.

btw: "not sure what you want here"
means you didn't indicate what you wanted - and I didn't know.

MySQL Workbench has these "Schema Synchronization" features
see: http://www.mysql.com/products/workbench/features.html

Here's another one with this feature

Compare two database schemas for differences. The XML output can be transformed into the approriate SQL ALTER statements using XSLT

http://www.sql-workbench.net/

There most certainly are tools around for what I believe you want to achieve.

ASKER

PortletPaul,

"
btw: "not sure what you want here"
means you didn't indicate what you wanted - and I didn't know. "

yea, what I mean is, is it a select statement ? this is what I mean.

"I would suggest you start looking for the tool(s) you need rather than try to build one."

The objective of this task is to automate the whole thing, not manually ! but thanks for that anyway as it is surprise that the workbench CE 6.0.x can do thsi but the Toad for MySQL can't do it.

However, I didn't do it yet !

You know we willl clone the table to another DB and name it as DBAudit_<table name> using the like statement.

but we will add aditional filed to for the use of auditing the user infrmation, timestamp, hostname, etc.

If the DBAudit_<talbe name> has more field than the source table, the sync do'nt work, right ?

In this case, I just want any field change in the souce table to replicate to the DBAudit_<table name> one but keep the new column I added in the DBAudit_<table name>, it will be a very complex case. so the sync don't work in this case?

this is what I am keep saying about finding schema diff by using MINUS:

http://www.bitbybit.dk/carsten/blog/?p=71

To transform the statement
 
SELECT member_id, name FROM a
 MINUS
 SELECT member_id, name FROM b
 
into something that MySQL can process, we can utilize subqueries (available from MySQL 4.1 onward). The easy-to-understand transformation is:
 
SELECT DISTINCT member_id, name
 FROM a
 WHERE (member_id, name) NOT IN
 (SELECT member_id, name FROM table2);
 

Of course, to any long-time MySQL user, this is immediately obvious as the classical use-left-join-to-find-what-isn’t-in-the-other-table:
 
SELECT DISTINCT a.member_id, a.name
 FROM a LEFT JOIN b USING (member_id, name)
 WHERE b.member_id IS NULL
 
which tends to be a lot more efficient.

so any hints on after finding that out, how to handle the rest ?

ASKER

virtuadept,

the link you gave me seems showing me the same thing I shown here.

"not sure what you want here"
at the time it wasn't clear to me what you wanted
>>"what I wnat to do is to scan all table name from information_schema:

Hence I just thought it was tables you were after
as you are now talking about replication of schema my advice is don't build it yourself - use proven tools.

But, yes the "not sure" part would be some further query(ies), to gather the fields constraints indexes and whatever else you need for whatever you intend to do.

I don't use Toad so I'm not familiar with what it does, nor am I that familiar with the very broad range of tools available for MySQL - but there are many - that I do know. In amongst those you would certainly find something (already tested) that will do these tasks.

Perhaps you might specifically ask for advice on such a tool?

If the DBAudit_<talbe name> has more field than the source table, the sync do'nt work, right ?
possibly not, possibly yes - see comment above about wide range of tools.

In this case, I just want any field change in the souce table
and there are ways to do this already

to replicate to the DBAudit_<table name> one but keep the new column I added in the DBAudit_<table name>,

Compare two database schemas for differences. The XML output can be transformed into the approriate SQL ALTER statements using XSLT

So, if you get a sequence of alter statements, these can be parsed to remove any field deletions you don't want.

it will be a very complex case.
even more reason for using something proven.

so the sync don't work in this case?
maybe not, maybe yes - until you learn what is allowed you won't know.

ps: I don't use either MySQL Workbench or SQL-Workbench I was just aware they had relevant features - so I'm not endorsing either. I just feel you would be better of with something proven.