Error handling when executing SQL script with multiple INSERT, UPDATE,… statements using ADO in Delphi

2
votes

I experience difficulties with the Delphi/ADO error handling when executing an SQL script containing more than one INSERT, UPDATE,... statement. Only when the first SQL statement of the script fails, I get an exception in Delphi. If the first statement passes, there will be no exception in Delphi, whatever happens further in the script.

This is the Delphi code I use:

var
  DataSet: TADOQuery;
begin
  ...
  try      
    DataSet.Close;
    DataSet.ParamCheck := true;
    DataSet.SQL.LoadFromFile(FileName);    
    DataSet.Prepared := true;
    try
      DataSet.ExecSQL;       
    finally    
      DataSet.Close;
    end;
  except
    on E: Exception do        
      Logging.AddText(E.ClassName + ' error raised when executing ' + FileName + '. Message: ' + E.Message);  
  end;      
  ...
end;

For testing I used this simple script:

INSERT INTO TESTTABLE
VALUES ('John', 24);

INSERT INTO TESTTABLE
VALUES ('Ed', '32');

where TESTTABLE is just a simple table containing two columns: Name NVARCHAR(50) and Age INT.

When you replace, for example, 24 by 'twentyfour' in the first INSERT statement and run the script with the Delphi code, Delphi/ADO will raise an exception. But when you replace, for example, 32 by 'thirtytwo' in the second INSERT statement, there will be no exception.

I tried to solve this by putting the script in a stored procedure "dbo.ErrorHandling" and sending 

EXEC dbo.ErrorHandling

to ADO, but it did not help.

CREATE PROCEDURE dbo.ErrorHandling
AS
BEGIN
  INSERT INTO TESTTABLE
  VALUES ('John', 24);
  INSERT INTO TESTTABLE
  VALUES ('Ed', '32');
END

I can solve the problem by using TRY and CATCH in the script, and letting it log the errors to a LOGGING table. Delphi can check this table for new errors after each script execution.

However, is it possible to catch all SQL server errors in Delphi, or do I have to execute INSERTS, UPDATES,... one by one?

I use Delphi XE6 and SQLServer 2008 R2

3
sql-serversql-server-2008delphiado
I do this when upgrading the database for a new software release. I always put the entire thing in a database transaction, so it rolls back on failure. And if you let SQL Server generate the script, it puts GO after each command. If you walk through the file line by line in code, building the query as you do, look for these and execute the query before the "GO" before clearing the query and starting the next. Does any of this help? - J__

3 Answers

1
votes

I would t take a look at the Errors Collection of the AdoConnection. TAdoConnection.Errors

0
votes

I've never found any way of reliably processing several queries at once and sensibly detecting problems. I believe the correct solution is to execute the statements one at a time.

This is code from one of my programs doing exactly what J__ describes in his comment. It processes an SQL Server style script with a GO after every statement. You could replace the "GO" detector with some other indication of the end of a statement. It batches the whole thing up as a single all-or-nothing transaction. The last finally has some code to save the last query into an on screen memo so you can see what failed if there was an exception. 

procedure TDupFrame.LoadButtonClick(Sender: TObject);
var
  Query: TADOQuery;
  Reader: TStreamReader;
  Line: string;
begin
  Query := TADOQuery.Create(nil);
  try
    Query.Connection := ConfModule.ADOConnection;
    Query.Connection.BeginTrans;
    if ScriptOpenDialog.Execute(Self.Handle) then
    begin
      Reader := TStreamReader.Create(ScriptOpenDialog.FileName);
      try
        Query.SQL.BeginUpdate;
        while not Reader.EndOfStream do
        begin
          Line := Reader.ReadLine;
          if not SameText(Line, 'GO') then
          begin
            Query.SQL.Add(Line);
          end
          else
          begin
            Query.SQL.EndUpdate;
            Query.ExecSQL;
            Query.SQL.Clear;
            Query.SQL.BeginUpdate;
          end;
        end;
        Query.SQL.EndUpdate;
        if Query.SQL.Count > 0 then Query.ExecSQL;
      finally
        Reader.Free;
      end;
      Query.Connection.CommitTrans;
    end;
  finally
    SQLMemo.Lines.Assign(Query.SQL);
    // rollback if we have missed the commit (ie an exception occurred)
    if Query.Connection.InTransaction then
      Query.Connection.RollbackTrans;
    Query.Free;
  end;
end;
0
votes

You need to configure the TADOQuery or TADOCommand with the ExecuteNoRecords Option:

Query := TADOQuery.Create(nil);    
Query.ExecuteOptions := [eoExecuteNoRecords];

It's the same in .NET. If you use ExecuteReader() or ExecuteScalar() on a SqlCommand, no exception is thrown if the first of a sequence of statements within a single command succeeds - no matter of how many subsequent statements fail.

If you call ExecuteNonQuery() however, a proper exception is being thrown in any case.