Skip navigation links

MySQL Forums :: Microsoft SQL Server :: How To? MySQL Script


Advanced Search

How To? MySQL Script
Posted by: Joey Powell ()
Date: August 15, 2006 02:31PM

I am trying to learn how to use MySQL scripts in MySQL Query Browser. My script needs to create a database and stored procedures and then do some regular SQL statements at the end that involve commands like DECLARE, INSERT, etc... I have done this many times with MSSQL Server 2005 and MS SQL Management Studio. In that environment, one can create a SQL script and segment it into batches with the "GO" keyword, like this...

DECLARE @var1 int;
SELECT @var1 = (SELECT COUNT(*) FROM MyTable1);
GO


DELCLARE @var2 int;
SELECT @var2 = (SELECT COUNT(*) FROM MyTable2);
GO


So far, I have been successful in segmenting my new MySQL script for
creating my database and creating procedures, like this...


CREATE DATABASE MyDatabase;

USE MyDatabase;

DELIMITER //
CREATE PROCEDURE MyProcedure()
BEGIN
SELECT * FROM MyTable;
END//
DELIMITER ;


...Like I said, that works great. But now, how do I do that for the groups
of regular SQL statements at the bottom of the script file? They contain commands like DECLARE, INSERT etc (like in the first MSSQL example above). If I put just the INSERT statements, it seems to work okay, but I need to use DECLARE there also. And when I try to wrap those statements in the DELIMITER/BEGIN/END style used above (to break it apart), it doesn't work...this is what doesn't work...


DELIMITER //
BEGIN
DECLARE var1 INT;
SELECT var1 = (SELECT COUNT(*) FROM MyTable1);
END//
DELIMITER ;


DELIMITER //
BEGIN
DECLARE var2 INT;
SELECT var2 = (SELECT COUNT(*) FROM MyTable2);
END//
DELIMITER ;


The above style only works when using procedures! I even went so far as to try to create the statements inside of a (temporary) procedure, call it, and then drop it afterwards. However, then I receive an error message that says the MySQL server has "gone away"? Nothing works in the MySQL Query Browser until I close and then restart it.

Anyways, I simply need to be able to break apart the script because several places need to use different DECLARE and INSERT statements, and I can't place all of my DECLARE statements at the top (seems to be required by MySQL) for ALL the commands.


How do I break it apart in my MySQL script, like I did in the first MSSQL example above?

JP

Options: ReplyQuote


Subject Written By Posted
How To? MySQL Script Joey Powell 08/15/2006 02:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.