Steve Zimmelman

The basic concept of processing scripts is this: Give the developer the ability to place multiple SQL statements and comments in one file. Then execute the statements in that file from top to bottom in a single process.

I designed my script engine to use the familiar double slash (//) for comments, and a semicolon (;) for the statement delimiter. A simple example of a script file might look like this:

// Create a table
 Create Table "MyTable.dbf"
 (
    Name Char(30),
    Phone Char(13)
 );
 
 // Create an index
 CREATE INDEX Name ON "MyTable.dbf" (Name) ;
 

The component merely parses each statement into a TStringList, and then executes the statement via a TQuery object. The StringLists are managed using a TList object.

Procedure TSQLScripts.ParseSQLScripts ;
 Var
    sl : TStringList ;
    i : Integer ;
    s : String ;
    NeedNew : Boolean ;
 
    Procedure NewScript ;
    Begin
       sl := TStringList.Create ;
       FList.Add(sl);
    End;
 
 Begin
    // Make sure all scripts are gone.
    FreeScripts ;
 
    // Add a new StringList for the first
    // SQL statement.
    NewScript ;
 
    // Parse each SQL statement into its
    // own StringList.
    For i := 0 To (FScripts.Count-1) Do Begin
       NeedNew := False ;
 
       s := Trim(FScripts[i]);
 
       // Ignore comments and blank lines.
       If (Copy(s,1,2)<>'//') And (s<>'') Then Begin
          If (s[1]<>';') Then Begin
             // Is the delimeter in this line?
             If (s[Length(s)]=';') Then Begin
                // Is there another SQL statement?
                If (i<(FScripts.Count-1)) Then
                   NeedNew := True ;
                // Remove the delimeter
                Delete(s,Length(s),1);
             End;
             // Add the SQL statement its StringList
             sl.Add(s);
          End;
       End;
 
       // Is there another SQL statement?
       If (Length(s)>0) Then Begin
          If (Copy(s,1,2)<>'//') Then Begin
             If ((s[1]=';') Or (s[Length(s)]=';'))
                        And (i<(FScripts.Count-1)) Then
                NeedNew := True ;
          End;
       End;
 
       If NeedNew Then NewScript ;
 
    End; // For (FScripts.Count-1)
 
    // Remove any empty StringLists.
    For i := (FList.Count-1) DownTo 0 Do Begin
       With TStringList(FList.Items[i]) Do Begin
          If (Text='') Or (Count=0) Then Begin
             Free ;
             FList.Delete(i);
          End;
       End;
    End;
 
 End;
 

The SQL statements are parsed from the script file, and then executed sequentially in the same order they appear in the script file. The statement is checked to see whether it requires a result set. If it does, the query is opened using the Open method. If a result isn't required, the ExecSQL method is used. The code that executes each SQL command looks something like this:

Var
    i : Integer ;
    sText : String ;
 
 ParseSQLScripts;
 For i := 0 To (FList.Count-1) Do Begin
    With FQuery Do Begin
       Close ;
       SQL.Clear ;
 
       // Get the SQL statement
       SQL.Text :=
           TStringList(FList.Items[i]).Text ;
 
       // Does the statement require a result?
       If (UpperCase(Copy(SQL.Text,1,7))=
                         'SELECT ') Then Begin
          Open ;
       End Else Begin
          FQuery.ExecSQL ;
       End;
    End;
 End;
 

The component has a property called SQLScripts, a type of TStrings, which contains the actual script. It also has a LoadFromFile method, so you can use an external text file for scripts. This allows script execution to be more dynamic in nature.

A simple usage of the component might look this:

   SQLScripts1.LoadFromFile('MyScript.txt')
   SQLScripts1.ProcessScript;

That's pretty much it. Using this technique, you can dynamically build entire data sets on the fly, or practically anything else you might do with Local SQL.