problem in writing mysql scripts

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

vijji_lakshmiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshMySQL Principle Technical Support EngineerCommented:
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
vijji_lakshmiAuthor Commented:
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
UmeshMySQL Principle Technical Support EngineerCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

vijji_lakshmiAuthor Commented:
i wanted to create a script file to delete tables in mysql.
0
UmeshMySQL Principle Technical Support EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UmeshMySQL Principle Technical Support EngineerCommented:
Any updates on this???

0
vijji_lakshmiAuthor Commented:
Hi,
Thanks a lot for your procedure.
i also kept my logic in procedure.
Rgds,
vijji
0
UmeshMySQL Principle Technical Support EngineerCommented:
Anytime..

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

Thanks,
Umesh
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.