Re: Multiple Line SQL for MySQL?

Peter Reid Tue, 29 Mar 2005 01:31:09 -0800

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: [EMAIL PROTECTED]
_______________________________________________
use-revolution mailing list
[email protected]
http://lists.runrev.com/mailman/listinfo/use-revolution

Re: Multiple Line SQL for MySQL?

Reply via email to