Multiple Line SQL for MySQL?

[Peter Reid]

Hi Sarah

Thanks for the response below.  In the end, I had done something similar:

function execSQLsequence dbID,dbSQL
   -- execute a sequence of SQL commands
   put unwrappedSQL(dbSQL) into theSQL
   repeat for each line i in theSQL
     revExecuteSQL dbID, i
     put the result into theResult
     if theResult is not a number then
       return theResult
     end if
   end repeat
   return empty
end execSQLsequence

function unwrappedSQL wrapped
   -- ignore blank & comment lines, unwrap multiple lines into single lines
   put empty into unwrapped
   put empty into thisCmd
   repeat for each line i in wrapped
     if i is not empty and char 1 to 2 of i is not "--" then
       put trimmed(i) after thisCmd
       if last char of i = ";" then
         put thisCmd & return after unwrapped
         put empty into thisCmd
       end if
     else
       put i & return after unwrapped
     end if
   end repeat
   if thisCmd is not empty then
     put thisCmd & return after unwrapped
   end if
   return unwrapped
end unwrappedSQL

function trimmed theText
   -- remove leading & trailing spaces
   put 1 into startCh
   put length(theText) into lenText
   put lenText into endCh
   repeat for each char c in theText
     if c <> space then
       exit repeat
     end if
     add 1 to startCh
   end repeat
   repeat with c = lenText to startCh step -1
     if char c of theText <> space then
       put c into endCh
       exit repeat
     end if
   end repeat
   return char startCh to endCh of theText
end trimmed

The "execSQLsequence" function can be used with a dump file produced 
by phpMySQL or mysqldump to reload a database or individual tables.

Cheers

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

-- 
Peter Reid
Reid-IT Limited, Loughborough, Leics., UK
E-mail: preid at reidit.co.uk