CREATE/ALTER procedure using Delphi

Question

I've got a small problem that I could not find an answer to. I need to create/alter some procedures from Delphi.

So this is my code that take the code from a file and tries to execute it.

procedure TfrmMainApp.actRulezaScriptExecute(Sender: TObject);
var
  j: Int32;
  sql: string;
  commandFile: TextFile;
  Linie: string;
  affRows: Int32;
  err: string;
begin

  for j := 0 to filesToExecute.Count - 1 do
  begin
    sql := 'USE ' + DBName + #10#13;
    sql := sql + '  GO ' + #10#13;
    AssignFile(commandFile, filesToExecute[j]);
    Reset(commandFile);
    while not EOF(commandFile) do
    begin
      Readln(commandFile, Linie);
      sql := sql + #10#13 + Linie;
    end;
    dmMainScriptRun.ExecuteCommand(sql, err, affRows);
    if err <> '' then
      break;
    Memo1.Lines.Add('Affected rows:' + IntToStr(affRows));
  end;

end;


function TdmMainScriptRun.ExecuteCommand(sqlCommand: string; var err: string;
  var affRows: Int32): Boolean;
begin
  err := '';
  try
    with cmd do
    begin
      CommandText := sqlCommand;
      Execute(affRows, EmptyParam);
    end;
  except
    on E: Exception do
    begin
      err := E.Message;
    end;
  end;
end;

So my file looks like this

CREATE PROCEDURE sp_TestSebi
AS

    print ('testSebi')

My command looks like this ( it was taken from the SQL Server Profiler )

USE Test

  GO 


CREATE PROCEDURE sp_TestSebi

AS


    print ('testSebi')

Executin this command returns err Incorrect syntax near 'GO' running the script without the GO statement return err CREATE/ALTER PROCEDURE' must be the first statement in a query batch because of the USE clause.

Is there a way I can create a procedure from Delphi? I need the use statement because i'm trying to execute a script on multiple databases.

Answer 1

The way you are attempting this can be improved in several ways.

Firstly, try this:

• Create a new VCL project

• Drop a TAdoConnection and a TAdoQuery on the form. Also drop a TButton on it.

• Connect the TAdoQuery to the TAdoConnection.

• Set the TAdoConnection to connect to your Sql Server

• in the code below, modify the scUse constant to refer to your target database and scCreateView to refer to a non-existing view and a valid table in your db. This is to ensure that the Create View will not fail because the view already exists or the table does not exist.

Run the code and you should get a complaint that the Create View refers to an invalid object name, because the AdoConnection isn't connected to your target database when the Create View executes.

Then change KeepConnection to True and retest. This time the view should be successfully created.

const
  scUse ='use m4common';
  scCreateView = 'create view vwtest as select * from btnames';

procedure TForm1.Button1Click(Sender: TObject);
begin
  AdoConnection1.DefaultDatabase := 'Master';
  // The point of changing the default database in the line above
  // is to ensure for demo purposes that the 'Use' statement 
  //  is necessary to change to the correct db.

  AdoConnection1.KeepConnection := False;  // retest with this set to True
  AdoQuery1.SQL.Text := scUse;
  AdoQuery1.ExecSQL;

  AdoQuery1.SQL.Text := scCreateView;
  AdoQuery1.ExecSQL;
end;

So, the point of KeepConnection is to allow you to execute two or more Sql batches in the same connection context and to satisfy the server that the Create View statement (or similar) can be the first statement in the batch, and at the same time, the database to which the Create View applies is the same one as you "USEd" in the previous batch.

Secondly, your AssignFile ... while not Eof is unnecessarily long-winded and error-prone. Try something like this instead:

var
  TL : TStringList;
begin
  TL := TStringList.Create;
  try
    for j := 0 to filesToExecute.Count - 1 do
    begin
      sql := 'USE ' + DBName + #13#10;
      sql := sql + '  GO ' + #13#10;
      TL.BeginUpdate;  // for better performance with longer scripts
      TL.LoadFromFile(filesToExecute[j]);
      TL.Insert(0, sql);
      TL.EndUpdate;  
      // execute the Sql here etc
    end;
  finally
    TL.Free;
  end;
end;

Note that I've reversed the order of your #10 and #13 so that it is correct.

The other point is that as the Lines property of your Memo already has a LoadFromFile method, you don't really need my temporary TStringList, TL, because you could do the load into your memo (though you might prefer to keep the two uses separate).

Answer 2

You can drop the connection each time and specify the USE in the params. In fact if you are clever you can establish one connection per db and parallelize the whole thing.