Multiple Line SQL for MySQL?

[Peter Reid]

Hi Frank

The following is a fragment of the SQL script produced by a 
phpMyAdmin dump for a table called "actionees":

--
-- Table structure for table `actionees`
--

DROP TABLE IF EXISTS `actionees`;
CREATE TABLE `actionees` (
   `Actionee` varchar(20) default NULL,
   `Fullname` varchar(50) default NULL,
   `tOrder` smallint(3) default NULL,
   `ActioneeID` mediumint(5) NOT NULL default '0',
   PRIMARY KEY  (`ActioneeID`),
   KEY `tOrder` (`tOrder`)
) TYPE=MyISAM;

--
-- Dumping data for table `actionees`
--

INSERT INTO `actionees` VALUES ('User', 'User', 70, 1);
INSERT INTO `actionees` VALUES ('Other', 'Other', 500, 6);
INSERT INTO `actionees` VALUES ('RA', 'Risk Assessor', 10, 7);

If you copy and paste this into the SQL web page of phpMyAdmin, you 
can execute it to rebuild the table in the same or a new empty 
database.  The only thing you need to add is a "COMMIT" command at 
the end.

Similarly, if you open up a Terminal session for MySQL and type 
commands in across a number of lines, it's not a problem - a command 
doesn't end until MySQL sees the ";" at the end.

So it seems that MySQL is not the problem, the problem is feeding 
such a script to MySQL using the revDB library!  At present the only 
way I can see of sending this to MySQL from Rev is to unwrap 
multi-line commands (such as the CREATE TABLE command above) so they 
are on a single line terminated with ";".  Of course this can be done 
(tedious but straightforward) but is it really necessary, am I 
missing something "obvious"?

Cheers

Peter

>No clue for MySQL, but I can tell you that it definitely supports 
>multi-line SQL commands with PostgreSQL, while the docs tell you 
>that it may not support them for all database types.  MySQL may not 
>support this (my understanding is that the database server will need 
>to have support for it, as Rev itself does not parse the statements 
>-- can anyone confirm?)
>
>
>On Mar 23, 2005, at 6:45 AM, Peter Reid wrote:
>
>>Does anyone know whether Rev supports multiple-line SQL command 
>>execution with MySQL?
>>
>>I have a Rev 2.5.1 project that is trying to create and populate a 
>>new database based on an existing template database.  I have used 
>>phpMyAdmin to dump a SQL script to a text file from a template 
>>database containing the SQL commands to construct all the tables 
>>and data I require.  However, I can't find a way of sending this 
>>script to MySQL using Rev? (I can read the file into a text 
>>container, but I can't feed this to the MySQL server using the 
>>revDB support commands and functions).
>>
>>If I use revExecuteSQL command (or equivalent function 
>>revdb_execute), I can send a single SQL command only.  If I try to 
>>use this inside a "repeat for each..." loop to work through a text 
>>object that contains multiple commands, then I hit a problem with 
>>any commands that have been laid out across several physical lines 
>>(as you get from phpMyAdmin dumps!).
>>
>>I really don't want to have to parse the SQL dump statements myself 
>>and convert each long multi-line command into a long single-line 
>>command just so I can use revExecuteSQL!  Nor do I want to use a 
>>shell() command as this is a client-server application with the 
>>code running on a client and the MySQL server running on a server 
>>on the same local network.
>>
>>Any suggestions please?!
>>
>>Peter
>- -----------------------------------------------------------
>Frank D. Engel, Jr.  <fde101 at fjrhome.net>

-- 
Peter Reid
Reid-IT Limited, Loughborough, Leics., UK
Tel: +44 (0)1509 268843 Fax: +44 (0)8700 527576
E-mail: preid at reidit.co.uk
         preid at reidit.demon.co.uk
Web: http://www.reidit.co.uk
      http://www.reidit.demon.co.uk