Multiple Line SQL for MySQL?

[Sarah Reichelt]

Hi Peter,

Here is a segment of the handler I use for restoring data to MySQL. 
This takes the file produced by mysqldump and re-instates it. tRestore 
is a variable containing the text data.

   repeat
     get offset(";" & cr, tRestore)
     if it = 0 then exit repeat
     put char 1 to it of tRestore into tSQL
     delete char 1 to it of tRestore

     get doSQLexecute(tSQL, tID)
     if it is not a number then
       answer error "Error restoring from backup." & cr & it & cr & tSQL 
as sheet
       exit to top
     end if
   end repeat

I have a doSQLexecute function that is just a wrapper for the 
revExecuteSQL command. I realize that you didn't want to have to do 
this, but it is really quite fast.

HTH,
Sarah


On 23/03/2005, at 9:46 PM, 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