Solved

how to combine string together for the execution of MySQL

Posted on 2013-10-29
13
320 Views
Last Modified: 2013-11-12
Dear all,

As you all know that in MS SQL, I can combine string and variable from a query result set to become a Query for MySQL to execute once it is done.

like

set @var1 = 'select * from' + @var2 + 'where' + @var3 + '= 3'

exec @var1

might I know how can I do in MySQL ? CONCAT() ? it seems not !

what I wnat to do is to scan all table name from information_schema:

  SELECT DISTINCT TABLE_NAME       
    FROM INFORMATION_SCHEMA.COLUMNS      
    WHERE TABLE_SCHEMA='xxx';

Then create the respective DBAudit_<table name> in other database, please help.
0
Comment
Question by:marrowyung
  • 6
  • 5
  • 2
13 Comments
 
LVL 8

Assisted Solution

by:virtuadept
virtuadept earned 250 total points
Comment Utility
	SET @var1 = CONCAT( 'select * from ', @var2 , ' where ' , @var3 , ' = 3');
	PREPARE stmt FROM @var1;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

Open in new window


More help

http://www.it-iss.com/mysql/mysql-writing-dynamic-sql-in-stored-procedures/
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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 ;

Open in new window


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

Open in new window


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

Accepted Solution

by:
PortletPaul earned 250 total points
Comment Utility
is this a one-time thing? or something you need to repeat?

does this help?

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

Expert Comment

by:virtuadept
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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  

Open in new window


?

just one print ?

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

Open in new window


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

Open in new window


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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
if we later on check the change of field name one by one and see what has been changed, then this statement use again !
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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.

Open in new window


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

Author Comment

by:marrowyung
Comment Utility
virtuadept,

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

Expert Comment

by:PortletPaul
Comment Utility
"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?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 
LVL 1

Author Comment

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

yeah, but this is just for the last sort if I can't find out who to make it using MySQL script !


hahhaa

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

then I really has to try to sync field by the workbench first ! this is the only feautrue from Workbench that make me happy, the Toad can do a lot of thing !

"I just feel you would be better of with something proven. "

yeah, but this is one of the requirement of the whole thing! if we have the field from the information_Schema, why don't we make use of it.

Developer might challenge on this as they propose it.

let me try to MINUS thing as if you read that link, that's should be what I need !

it found out the different between 2 x table schema.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now