[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

the create of MySQL store procedure

Dear all,

I am creating a MysQL store proceudre and I run this:

DELIMITER $$

CREATE PROCEDURE `create_auditriggers` (IN database_needaudit VARCHAR(40))  	
BEGIN  	
DECLARE a, b INT DEFAULT 5;  	
DECLARE temptable VARCHAR(50);  	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;  	
DECLARE v1, v2, v3 TINYINT;      	
DECLARE tablelist VARCHAR(50);  		
 
declare AAA cursor for 
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS	
WHERE TABLE_SCHEMA='<database name>'; 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;     

/*Specify what to do when no more records found, 
Notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures*/  	
	
OPEN AAA;	

	   get_tabeslist: LOOP	
	        FETCH AAA INTO tablelist;	
	        IF finished = 1 THEN 	
            LEAVE  get_tabeslist;	
         END IF;	
	print @tablelist
       	
    END LOOP  get_tabeslist;	
close AAA;                             

END $$
DELIMITER ;

Open in new window


I want the cursor to scan throught a list of all DB and print that out one by one for me, but when I execute the store procedure , it gives error:

Lookup Error - MySQL Database Error: 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 'NULL' at line 1

Open in new window


anyone has an idea on what's wrong?

it seems it is highlighting the "FETCH" line with red line on both Toad and Workbench.
0
marrowyung
Asked:
marrowyung
  • 37
  • 19
  • 5
1 Solution
 
marrowyungAuthor Commented:
Dear all,

this is the near finish code :

DELIMITER $$
use PCCWSP;

CREATE PROCEDURE `create_auditriggers` (IN databaseName VARCHAR(40))  	
BEGIN  
DECLARE a, b, finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE tablelist VARCHAR(50)  ;
DECLARE fullexcutecmd VARCHAR(100)  ;

/*Declare and populate the cursor with a SELECT statement */  	
	
DECLARE AAA CURSOR FOR 	
SELECT DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.COLUMNS	
    WHERE TABLE_SCHEMA=@databaseName;   	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;     
/*Specify what to do when no more records found, notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures*/  	
		
OPEN AAA;	

	get_tabeslist: LOOP	
 FETCH AAA INTO tablelist;	
    /*
   SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'QUARTZ' AND TABLE_NAME = 'QRTZ_BLOB_TRIGGERS'; */
    
 SET @fullexcutecmd =CONCAT_WS( 'SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE  FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_SCHEMA =' , databaseName , ' AND TABLE_NAME = ' , tablelist,';');
  PREPARE stmt FROM @fullexcutecmd;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;   
        
 
  IF finished = 1 THEN 	
 
 LEAVE  get_tabeslist;	
         
 END IF;	
        	
    END LOOP  get_tabeslist;	
close AAA;                             

END $$
DELIMITER ; 

Open in new window


and I just want the following execute one by one for any table the cursor point to one by one:

SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = '<database name>'AND TABLE_NAME = '<table name>';

Open in new window


but when executing it, it gives an error:

MySQL Database Error: 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 'QUARTZSELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, CO' at line 1

Open in new window


pelase suggest why .
0
 
RobOwner (Aidellio)Commented:
I bellieve it may be here:

SET @fullexcutecmd =CONCAT_WS( 'SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE  FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_SCHEMA =' , databaseName , ' AND TABLE_NAME = ' , tablelist,';');

there is no closing bracket to your CONCAT_WS function, well there is but it's in the wrong place, should be

SET @fullexcutecmd =CONCAT_WS(';', 'SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE)  FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_SCHEMA =@databaseName AND TABLE_NAME = @tablelist;
0
 
RobOwner (Aidellio)Commented:
Also the separator needs to be at the beginning of your concat_ws function, not the end.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
marrowyungAuthor Commented:
what is the different between Concat_ws and concat, basically I use Concat at the beginning but later on change to concat_ws.

after modify that using your suggestion, when I create the procedure, I see:

MySQL Database Error: 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 ''SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_' at line 29	32	36

Open in new window

0
 
RobOwner (Aidellio)Commented:
Concat will join together everything you pass to the function.
Concat_ws will do the same but with a separatorlike a comma (that's the ws part)

I'll have a look what might cause that error
0
 
RobOwner (Aidellio)Commented:
Ha must've been later here when I posted it. The select of course had to be outside the function....
SET @fullexcutecmd =SELECT CONCAT_WS(';', COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE) AS C FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_SCHEMA =@databaseName AND TABLE_NAME = @tablelist;

Open in new window

0
 
marrowyungAuthor Commented:
However it said:

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 'SELECT CONCAT_WS(';', COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_L' at line 28

Open in new window


can't see why .
0
 
marrowyungAuthor Commented:
if I originally plan to use concat instead of concat_ws, what should I do ?
0
 
RobOwner (Aidellio)Commented:
If you want to just use concat but you want everything separated by a pipe for example, then you'd do this:

CONCAT('one','|','two','|','three','|','four');

as the equivalent is:

CONCAT_WS('|','one','two','three');
0
 
RobOwner (Aidellio)Commented:
You may need to use backticks `` for the column names:

SET @fullexcutecmd =SELECT CONCAT_WS(';', `COLUMN_NAME`,  `DATA_TYPE`, `COLUMN_TYPE`,  `CHARACTER_MAXIMUM_LENGTH`, `COLUMN_DEFAULT`, `IS_NULLABLE`) AS 'C' FROM `INFORMATION_SCHEMA`.`COLUMNS`  WHERE `TABLE_SCHEMA` =@databaseName AND `TABLE_NAME` = @tablelist;

Open in new window

0
 
marrowyungAuthor Commented:
ok this is the error:

	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 'SELECT CONCAT_WS(';', `COLUMN_NAME`,  `DATA_TYPE`, `COLUMN_TYPE`, 
  `CHARACTER_M' at line 29	0.000 sec

Open in new window


when I take a look on the Workbench UI, I see that it seems that "SELECT CONCAT_WS" is not correct, that has a red line over the "SELECT", so the concat_ws should not have "SELECT" at the beginning ?

you get the correct result in your side?
0
 
RobOwner (Aidellio)Commented:
yes i'm getting correct results this side... here is a simple example:  http://sqlfiddle.com/#!2/a2581/16829

select concat_ws("|",`type`,`details`) from supportContacts;
0
 
marrowyungAuthor Commented:
could you please use my script and generate one? I think there are something else in my script.

My script is generating the whole statement and and execute by the "EXECUTE stmt;"
0
 
marrowyungAuthor Commented:
if I do this:

DELIMITER $$


CREATE PROCEDURE `create_auditriggers` (IN databaseName VARCHAR(40))  	
BEGIN  
DECLARE a, b, finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE tablelist VARCHAR(50) DEFAULT ""; 
DECLARE fullexcutecmd VARCHAR(200)  ;

/*Declare and populate the cursor with a SELECT statement */  	
	
DECLARE AAA CURSOR FOR 	
SELECT DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.COLUMNS	
    WHERE TABLE_SCHEMA=databaseName;   	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;     
/*Specify what to do when no more records found, notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures*/  	
		
OPEN AAA;	

	get_tabeslist: LOOP	
 
   FETCH AAA INTO tablelist;	
         IF finished = 1 THEN 	
   LEAVE  get_tabeslist;	
   END IF;	

select tablelist;

    END LOOP  get_tabeslist;	
close AAA;                             

END $$
DELIMITER ; 

Open in new window


when I call it, why it only turn the first table name on that database ? I expect it return all table one by one.

this one I debug it only using:

select tablelist;
0
 
RobOwner (Aidellio)Commented:
This is how you assign a variable using a select statement:

http://sqlfiddle.com/#!2/a2581/16837

SELECT @fullexcutecmd := CONCAT_WS("|",`type`,`details`) FROM supportContacts;
0
 
marrowyungAuthor Commented:
the loop has problem ?
0
 
marrowyungAuthor Commented:
is this link http://sqlfiddle.com/#!2/a2581/16837 help to convert statement to concat format?

I don't see why you show me this .
0
 
marrowyungAuthor Commented:
will that too long for concat to complete:

SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = '<database name>'AND TABLE_NAME = '<table name>'; 

Open in new window


?

but this one works perfectly:

SET @fullexcutecmd =CONCAT( 'CREATE TABLE Audit_info.DBAudit_', tablelist,' LIKE QUARTZ.',tablelist,';');

Open in new window


how come?
0
 
marrowyungAuthor Commented:
Do concat thing has length limit during prepare?  I think that's why ?
0
 
marrowyungAuthor Commented:
ok, another proof there:

I plan to run this on all talbe I scan:

ALTER TABLE AB_Audit 
ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID,
ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,
ADD ActionDate DATETIME NULL AFTER Action ,
ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;

Open in new window


by:

  SET @fullexcutecmd = CONCAT( 'use Audit_info; ALTER TABLE DBAudit_', tablelist, ' ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID,ADD Action CHAR( 10 ) NULL AFTER TriggerAction,ADD ActionDate DATETIME NULL AFTER Action , ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;');
     PREPARE stmt FROM @fullexcutecmd;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

Open in new window


again the error message is very similiar, it kee saying syntax has problem, but I dont' see any.

Any suggestion ?
0
 
RobOwner (Aidellio)Commented:
Let me process this, get some dinner and I'll get back to you :-)
0
 
RobOwner (Aidellio)Commented:
Sorry, I would be able to help you with the simple issue of a concat and variable assignment but the stored procedure has me stumped.  I'll put the word out for more experts.
0
 
marrowyungAuthor Commented:
please help, you can do it, I don't think we should give up, right?

do you think the concat can't handle the long charters? that's why I get the error ?
0
 
RobOwner (Aidellio)Commented:
I haven't given up :-) just alerted a few more experts for you.
0
 
marrowyungAuthor Commented:
I did !  but you are the one here who is the expertist on this!

it is said that it seems the variable “fullexcutecmd” is declared to be of length 200 char.  Then the limit is 200.  FYI, limit of VARCHAR type is 65,535 characters. (http://dev.mysql.com/doc/refman/5.7/en/char.html).

how to use more ?
0
 
marrowyungAuthor Commented:
it seems that even I change the type to BLOB and TEXT, it error still

 'ALTER TABLE DBAudit_QRTZ_BLOB_TRIGGERS; ADD DBAuditID BIGINT UNSIGNED NOT NULL A' at line 1      0.046 sec

just one question, if I just want to add this to the string and execute it:

ALTER TABLE <table name>
ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID,
ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,
ADD ActionDate DATETIME NULL AFTER Action ,
ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;

Open in new window


how can I concat the string all at once ? or execute it one by one anyway ?
0
 
marrowyungAuthor Commented:
Right now I change the type to:

DECLARE fullexcutecmd BLOB ;

SET @fullexcutecmd = CONCAT( 'use Audit_info; ALTER TABLE DBAudit_', tablelist, ' ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID;'); 
     PREPARE stmt FROM @fullexcutecmd;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

Open in new window


It complains syntax error:

'ALTER TABLE DBAudit_QRTZ_BLOB_TRIGGERS ADD DBAuditID BIGINT UNSIGNED NOT NULL AU' at line 1  0.078 sec

Open in new window


Any other suggestion ?
0
 
RobOwner (Aidellio)Commented:
I'll have a look shortly
0
 
marrowyungAuthor Commented:
ok ,thanks, the next steps is to add the add field steps and execute it, I have myself fixed the cusror and talbe creation thing, it all works and just that now.
0
 
RobOwner (Aidellio)Commented:
I've tested this successfully and it returns all the tables for the given database:

DELIMITER $$

CREATE PROCEDURE `create_auditrigger`(IN databaseName VARCHAR(40))
BEGIN
DECLARE a, b, finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE tablelist VARCHAR(50) DEFAULT ""; 
DECLARE fullexcutecmd VARCHAR(200)  ;
DECLARE mytables VARCHAR(255);
/*Declare and populate the cursor with a SELECT statement */  	
	
DECLARE AAA CURSOR FOR 	
SELECT DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.COLUMNS	
    WHERE TABLE_SCHEMA=databaseName;   	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;     
/*Specify what to do when no more records found, notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures*/  	
OPEN AAA;	

	get_tabeslist: LOOP	
 
   FETCH AAA INTO tablelist;	
         IF finished = 1 THEN 	
   LEAVE  get_tabeslist;	
   END IF;	

set mytables = concat_ws(',',mytables,tablelist);/*select tablelist;*/

    END LOOP  get_tabeslist;	
close AAA;                             
select mytables;
END

Open in new window


note: I added the mytables variable.  ALSO to get the concat (also _ws) to work you need to include the variable again in the function so with each loop you get what you've already joined together with the new one
0
 
RobOwner (Aidellio)Commented:
So you've now got something that will enumerate the tables of a given database.  What's would you like to do with this?
0
 
marrowyungAuthor Commented:
the smapel you gave me working and I use select talbelist instead.

but I add one more logic:

SET @fullexcutecmd =CONCAT( 'CREATE TABLE Audit_info.DBAudit_', tablelist,' LIKE QUARTZ.',tablelist,';');

which means I create tables by coping existing table with all column inherent, this is done too !

but now for each tablelist(tablename), I would like to run the following long script:

ALTER TABLE DBAudit_<table name>
ADD TriggerAction VARCHAR( 10 ) NULL ,
ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,
ADD ActionDate DATETIME NULL AFTER Action ,
ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;

Open in new window


so that I can add extra column for these and my statement is:

 SET @fullexcutecmd = CONCAT( 'use Audit_info; ALTER TABLE DBAudit_', tablelist,
 'ADD TriggerAction VARCHAR( 10 ) NULL, ',
 'ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,', 
 'ADD ActionDate DATETIME NULL AFTER Action , ',
 'ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;'
); 
 PREPARE stmt FROM @fullexcutecmd;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

Open in new window


but when running that, it keep gives:

Lookup Error - MySQL Database Error: 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 'ALTER TABLE DBAudit_QRTZ_BLOB_TRIGGERSADD TriggerAction VARCHAR( 10 ) NULL, ADD ' at line 1

Open in new window


can see why, but if I run that separately for the table I just created, it works.
0
 
marrowyungAuthor Commented:
Also by that:

set mytables = concat_ws(',',mytables,tablelist);/*select tablelist;*/

Open in new window


it seems that the mytables string will keep growthing, right? instead of refreshed each time this statement runs.

but this one is not good  for me.
0
 
marrowyungAuthor Commented:
can GROUP_CONCAT(expr)  help on this? please suggest what will be that?
0
 
marrowyungAuthor Commented:
I tried this:

 SET @fullexcutecmd = CONCAT( 'use Audit_info; ALTER TABLE DBAudit_', tablelist,' ADD TriggerAction VARCHAR( 10 ) NULL ;'); 

Open in new window


in order to see if the string is as simple as that can run, but it still CAN'T.

this one won't work too:

 SET @fullexcutecmd = CONCAT( 'use Audit_info', ';' , 'ALTER TABLE DBAudit_', tablelist,';'); 

Open in new window

0
 
marrowyungAuthor Commented:
0
 
RobOwner (Aidellio)Commented:
I'll have a look at that other question for you too - i did see that one come through.

you are having an issue with this code because you need to add a space after the tablelist, ie , tablelist, ' ', ...
 SET @fullexcutecmd = CONCAT( 'use Audit_info; ALTER TABLE DBAudit_', tablelist,' ',
 'ADD TriggerAction VARCHAR( 10 ) NULL, ',
 'ADD Action CHAR( 10 ) NULL AFTER TriggerAction ,', 
 'ADD ActionDate DATETIME NULL AFTER Action , ',
 'ADD ActionBy VARCHAR( 50 ) NULL AFTER ActionDate ;'
); 
 PREPARE stmt FROM @fullexcutecmd;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

Open in new window

0
 
RobOwner (Aidellio)Commented:
Your question here: http:#a39658563 isn't true.  the mytables and tablelist variables scope are only while the statement runs.  They shouldn't grow with multiple calls.  If by some strange reason they are then reset them to null or an empty string at the end of the statement.  Does that make sense?
0
 
RobOwner (Aidellio)Commented:
Not sure I understand entirely what errors you are getting here: http:#a39659011 ??  Can you clarify?
0
 
marrowyungAuthor Commented:
"Your question here: http:#a39658563 isn't true.  the mytables and tablelist variables scope are only while the statement runs.  They shouldn't grow with multiple calls.  If by some strange reason they are then reset them to null or an empty string at the end of the statement.  Does that make sense? "

yes, it make sense but if it is not the case, as seen from the result, the result of mytables keep have the next tablelist append to it with the ';' in between.

"Not sure I understand entirely what errors you are getting here: http:#a39659011 ??  Can you clarify? "

it only menas if i try to see if CONCAT can't handle so much string at one time (which I don't see any even now ), I remove the number of characters I put in, then it STILL doesn't works.

so it must be something else.

One thing:

  "If by some strange reason they are then reset them to null or an empty string at the end of the statement"

  how? by SET @fullexcutecmd = " " ;    ?
0
 
RobOwner (Aidellio)Commented:
Yes do this at the end of the statement:
set @tablelist = "";
set @mytables = "";
set @fullexcutecmd = "";
0
 
RobOwner (Aidellio)Commented:
BTW I don't think GROUP_CONCAT will work as it is for aggregate results.
0
 
marrowyungAuthor Commented:
ok, one thing I make it works now !

why ? guess what :

I have to remove:

use Audit_info;

Open in new window


from

SET @fullexcutecmd = CONCAT( 'use Audit_info; ALTER TABLE DBAudit_', tablelist, ' ADD 

Open in new window


Surprise !!

Also I see how why this one don't works:

SET @fullexcutecmd =  CONCAT('SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ', databaseName, '  AND TABLE_NAME = ' ,tablelist, ' ;'); 

Open in new window


the ' ' symboe does matter, I can do ' or ", and I found using " to quote the name of database and table name when it pass in is better, therefore, here you go:

SET @fullexcutecmd =  CONCAT('SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT,
  IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_SCHEMA = ','"', databaseName,'"',' AND TABLE_NAME = ','"',  tablelist ,'"',';');

Open in new window


Then I can loop in and find out the field change of each table (if this is simple enought!).
0
 
marrowyungAuthor Commented:
"BTW I don't think GROUP_CONCAT will work as it is for aggregate results. "

you are right !!! not my cast at all !
0
 
PortletPaulCommented:
Hi. Big long question so far :)

Seems the actual purpose of this stored procedure is to create or alter "audit tables" that reside in another database. Each "audit table" will mimic the structure of a table in "this" database.

so, if "this" database has:
    CREATE TABLE supportContacts
          (
         id int auto_increment primary key,
         type varchar(20),
         details varchar(30)
        );


then the audit database will have an "audit table of:
    CREATE TABLE DBAudit_supportContacts
          (
         id int, /* auto increment and primary key removed */
         type varchar(20),
         details varchar(30)
        );

PLUS  the results of this:

use Audit_info;
ALTER TABLE DBAudit_supportContacts
 ADD DBAuditID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
 ADD TriggerAction VARCHAR( 10 ) NULL AFTER DBAuditID
 ;

Q1: Do you have a working procedure for creating the "audit tables"?
Q2: Why is this procedure for a whole database and not "per table"? e.g.
         call create_auditrigger('a_table_name1')
         call create_auditrigger('a_table_name2')
         ...
         call create_auditrigger('a_table_name')

Q3: Could you use INFORMATION_SCHEMA.TABLES or group_concat?
       e.g:
SELECT group_concat(
                     concat('call AuditDB_EchoThisTbl(', TABLE_NAME, ')')
                     SEPARATOR ';'
                   ) AS proc_calls
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_9_20979'
;

see http://sqlfiddle.com/#!9/20979/27
That example query produces a string like this:
call AuditDB_EchoThisTbl(a);call AuditDB_EchoThisTbl(b);call AuditDB_EchoThisTbl(supportcontacts)

ps: There are limits to the lengths of string variables, which can be extended, I've forgotten that detail right now, will have to hunt this down.
0
 
marrowyungAuthor Commented:
please help me on the next question please on how to not creating the table if it exists
0
 
PortletPaulCommented:
done, use
IF NOT EXISTS
0
 
marrowyungAuthor Commented:
very supportive! please keep  helping me on MySQL.
0
 
PortletPaulCommented:
just in case:

group_concat can be used without a group by clause, just like MIN() or MAX() can.

e.g. see http://sqlfiddle.com/#!9/20979/27
0
 
marrowyungAuthor Commented:
basically what this site for http://sqlfiddle.com/#!9/20979/27, you and tagit both bring me to that.
0
 
marrowyungAuthor Commented:
what this suppose to show:

SELECT group_concat(TABLE_NAME) AS x
    FROM INFORMATION_SCHEMA.TABLES
    WHERE table_schema = 'db_9_20979'
;


SELECT group_concat( 
                     concat('call AuditDB_EchoThisTbl(', TABLE_NAME, ')')
                     SEPARATOR ';'
                   ) AS proc_calls
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_9_20979'
;
	

Open in new window

0
 
marrowyungAuthor Commented:
0
 
PortletPaulCommented:
sqlfiddle provides the ability to actually run sql, it is extremely handy. When those URLs are provided it means you can go there to see a query - and run it.

>>"what this suppose to show:"

that you can use group_concat() without a group by clause
also that you can use INFORMATION_SCHEMA.TABLES


The top query in ID: 39661730 is the direct equivalent of the stored procedure
i.e. the stored procedure and that query produce the same result:

The lower query in ID: 39661730 demonstrates how you can use concat() inside group_concat()
0
 
marrowyungAuthor Commented:
I found it hangs from time to time. at this moment, it still hangs and I can't make it show up .
0
 
PortletPaulCommented:
yes, that is true, which is why I put the actual suggestion into the comments here, and sqlfiddle is just a reference.

as this question has been answered I shall now not monitor it further, so I will not see any further comments you make to this question.
0
 
marrowyungAuthor Commented:
PortletPaul,

please help on my MysQL on how to keep appending string on mySQL
0
 
marrowyungAuthor Commented:
so from that link you just want to show me that the followings  result set are the same?

CALL create_auditrigger('db_9_20979')
;

Open in new window


SELECT group_concat(TABLE_NAME) AS x
    FROM INFORMATION_SCHEMA.TABLES
    WHERE table_schema = 'db_9_20979'
;

Open in new window


it just keep adding tablename with ',' in between ?
0
 
marrowyungAuthor Commented:
0
 
marrowyungAuthor Commented:
0
 
marrowyungAuthor Commented:
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 37
  • 19
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now