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);
DELCLARE @var2 int;
SELECT @var2 = (SELECT COUNT(*) FROM MyTable2);
So far, I have been successful in segmenting my new MySQL script for
creating my database and creating procedures, like this...
CREATE DATABASE MyDatabase;
CREATE PROCEDURE MyProcedure()
SELECT * FROM MyTable;
...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...
DECLARE var1 INT;
SELECT var1 = (SELECT COUNT(*) FROM MyTable1);
DECLARE var2 INT;
SELECT var2 = (SELECT COUNT(*) FROM MyTable2);
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?