Solved

the create of MySQL store procedure

Posted on 2013-11-13
61
547 Views
Last Modified: 2013-11-25
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
Comment
Question by:marrowyung
  • 37
  • 19
  • 5
61 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 39647346
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39649625
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39649637
Also the separator needs to be at the beginning of your concat_ws function, not the end.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39653410
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39653743
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39653745
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39655392
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39655393
if I originally plan to use concat instead of concat_ws, what should I do ?
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39655397
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39655399
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39655411
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39655477
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39655505
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39655516
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39655521
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39655560
the loop has problem ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39655628
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39655767
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39655780
Do concat thing has length limit during prepare?  I think that's why ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39655801
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39656053
Let me process this, get some dinner and I'll get back to you :-)
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39656097
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39656478
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39657683
I haven't given up :-) just alerted a few more experts for you.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39658280
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39658319
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39658321
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39658322
I'll have a look shortly
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39658383
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39658491
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39658502
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39658542
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39658563
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39658602
can GROUP_CONCAT(expr)  help on this? please suggest what will be that?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39659011
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39659024
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39661369
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39661371
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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39661373
Not sure I understand entirely what errors you are getting here: http:#a39659011 ??  Can you clarify?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39661381
"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
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39661406
Yes do this at the end of the statement:
set @tablelist = "";
set @mytables = "";
set @fullexcutecmd = "";
0
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
ID: 39661411
BTW I don't think GROUP_CONCAT will work as it is for aggregate results.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39661473
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39661474
"BTW I don't think GROUP_CONCAT will work as it is for aggregate results. "

you are right !!! not my cast at all !
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39661476
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39661480
please help me on the next question please on how to not creating the table if it exists
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39661489
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39661510
done, use
IF NOT EXISTS
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39661617
very supportive! please keep  helping me on MySQL.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39661719
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39661728
basically what this site for http://sqlfiddle.com/#!9/20979/27, you and tagit both bring me to that.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39661730
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39661860
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39664312
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39664831
I found it hangs from time to time. at this moment, it still hangs and I can't make it show up .
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39664839
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39664911
PortletPaul,

please help on my MysQL on how to keep appending string on mySQL
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39665097
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39665471
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39668093
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39676666
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

22 Experts available now in Live!

Get 1:1 Help Now