?
Solved

problem in writing mysql scripts

Posted on 2008-11-12
8
Medium Priority
?
387 Views
Last Modified: 2012-06-21
Hi,
I dont know how to run the following code as script.
i am working with mysql query browser.
i opened a new script tab. and i pasted the below code

BEGIN
DECLARE user_table VARCHAR(80);
declare viewc integer;

      
DECLARE
      drop_tables CURSOR FOR
      SELECT table_name FROM information_schema.TABLES where table_type='BASE TABLE' and table_schema=(select database());
        

  #PRINT '  Checking for any views before dropping tables.'
 
SELECT count(*) into viewc FROM information_schema.VIEWS where table_schema=(select database());

  IF (viewc > 0) then
  BEGIN
   select 13;

  END
  ELSE
  BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_MORE_ROWS:=TRUE;
   
    OPEN drop_tables;
    CURSORLOOP: LOOP
    FETCH NEXT FROM drop_tables INTO user_table;

    IF NO_MORE_ROWS THEN
   CLOSE drop_tables;
  LEAVE CURSORLOOP;
  END IF;

   SET @Tabdrop= CONCAT('DROP TABLE',tabName);
PREPARE Tabdrop_GEN from @Tabdrop;
EXECUTE Tabdrop_GEN;
DEALLOCATE Tabdrop_GEN;
    CLOSE drop_tables;

  END;
  end if;

END  ;

but it is throwing me somany errors.
i don't know how to write script files in mysql.
please give me any link.where i can refer.
Rgds,
vijji

0
Comment
Question by:vijji_lakshmi
  • 5
  • 3
8 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 22939018
Procedural code has to go inside a procedure body/function body. So pls place this code inside a Stored Procedure and call it from command line or place the call statement inside a filename.sql and you can execute this script from mysql prompt as

mysql>source filename.sql

If you want to call it direcly from mysql prompt then


mysql>call procedurename();
0
 

Author Comment

by:vijji_lakshmi
ID: 22939138
we are migrating our database from mssql to mysql.
in mssql they have droptables.sql file in which they are using the above script directly.
i mean not in procedure and function like below.

BEGIN

DECLARE @user_table VARCHAR(80)

      
DECLARE
      drop_tables CURSOR FOR
            SELECT name
            FROM sysobjects
            WHERE xtype='U' and name not like 'cmi%' and name<>'ERR_CLK' and name<>'TMP_CLK' ORDER BY name --user table

  PRINT '  Checking for any views before dropping tables.'
 
  SELECT s2.name
  FROM sysobjects s2, syscomments s1       
  WHERE (s1.id=s2.id) AND (s2.xtype ='V')
    AND (s2.name NOT LIKE 'sys%')
  ORDER BY s2.name

  IF (@@ROWCOUNT > 0) BEGIN
    PRINT ''
    PRINT '  ######################################################################################'
    PRINT '  ### WARNING -> You must drop all user defined views before dropping tables.        ###'
    PRINT '  ###            Tables will not be dropped.                                         ###'
    PRINT '  ######################################################################################'
    PRINT ''
   
  END
  ELSE BEGIN
 
    PRINT ''
    PRINT 'No Views found, OK to drop tables.'
    PRINT ''
   
    OPEN drop_tables
    FETCH NEXT FROM drop_tables INTO @user_table
      
      WHILE(@@FETCH_STATUS=0) BEGIN
        PRINT ('  ##<INFO>## DROP TABLE ' + @user_table)
        EXEC ('DROP TABLE ' + @user_table)
        FETCH NEXT FROM drop_tables INTO @user_table
      END
   
    CLOSE drop_tables
    DEALLOCATE drop_tables
    PRINT ''
  END
 
END

GO

i wanted to create a script same as above for mysql.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22939188
If you are migrating then pls use migration tool kit from MySQL.. which may take care of compatibility issues that may come when you do it manually..

Tell me what exactly you are trying to do from above script?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:vijji_lakshmi
ID: 22939253
i wanted to create a script file to delete tables in mysql.
0
 
LVL 26

Accepted Solution

by:
Umesh earned 2000 total points
ID: 22939469
This should the homework

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`uDropTables`$$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `uDropTables`(in_schema varchar(100) )
    READS SQL DATA
BEGIN
 
  DECLARE l_table         varchar(100);
  DECLARE l_done          INT DEFAULT  0;
  DECLARE l_vewCnt        INT DEFAULT  0; 
 
 
  DECLARE cursor1 cursor  FOR
    SELECT TABLE_NAME
        FROM   information_schema.TABLES
        WHERE  information_schema.TABLES.TABLE_SCHEMA=in_schema AND information_schema.TABLES.TABLE_TYPE ='BASE TABLE' ;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
 
       ## Wrire code for view 
	SELECT count(*) into l_vewCnt FROM information_schema.VIEWS where table_schema=in_schema;
        
	IF l_vewCnt > 0 then
		
	# SET l_done=1;
          SELECT 13;
	ELSE
 
		OPEN cursor1;
		cursor_loop:LOOP
		    FETCH cursor1 INTO l_table;
		    IF l_done=1 THEN
			 LEAVE cursor_loop;
		    END IF;
	 
	    
	 
		  SET @tmp_sql= CONCAT("DROP TABLE ",in_schema,".",l_table);
		  PREPARE s1 FROM @tmp_sql;
		  EXECUTE s1;
		  DEALLOCATE PREPARE s1;
	 
		END LOOP cursor_loop;
		CLOSE cursor1;
 
        END IF;
 
 
        SET l_done=0;
END$$
 
DELIMITER ;
 
### This procedure will be created in test schema.. if you don't want #### to put this in test schema then change the prefix of procedure name
### How to call above procedure 
### make sure you pass the DB name
##### If views are not exists then this sp will remove all the tables from the supplied schema - I have passed test as database name
##### 
call uDropTables('test');

Open in new window

0
 
LVL 26

Expert Comment

by:Umesh
ID: 22940438
Any updates on this???

0
 

Author Comment

by:vijji_lakshmi
ID: 22947601
Hi,
Thanks a lot for your procedure.
i also kept my logic in procedure.
Rgds,
vijji
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22947648
Anytime..

BTW, I just noticed you have lots of questions kept opened.. Pls close which are solved your issues.

Thanks,
Umesh
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

569 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