Getting a count of the number of statements in a T

问题:

(For readers who don't use Delphi: Although the following is couched in terms of Delphi coding, my actual technical question isn't Delphi-specific, but is about how to find out how the Sql Server will "understand" a TransactSql batch submitted to it. A "TAdoQuery" is a Delphi class which basically wraps an ADO Command and a RecordSet and submits a TSql batch to a Sql Server. Usually, using the TAdoQuery, the batch is a single statement, but my q is specifically concerned with the possibility that the batch may contain more than one statement.)

Suppose I have a TAdoQuery whose Sql.Text contains a TransactSql batch comprising one or more statements S1[...Sn].

What I'm trying to do is to find out without executing the batch whether a) the first (or only) statement, S1, in the batch will return a result set (even if empty) e.g by dint of it being a SELECT statement or an invocation of a stored procedure or table function, or whatever, AND b) how many statements the server thinks there are in the batch.

Regular users of Delphi's TAdoQuery will know that it's easy but slightly messy to test whether the first (or only) statement in a batch returns a result set just by calling TAdoQuery.Open. If it does, then it retrieves that result set, but if doesn't, then calling .Open will provoke an exception.

So, instead, I do something like this:

type
  TMyDataSet = class(TDataSet);

procedure TForm1.Button1Click(Sender: TObject);
begin
  if AdoQuery1.Active then
    AdoQuery1.Close;
  AdoQuery1.FieldDefs.Clear;
  TMyDataSet(AdoQuery1).OpenCursor(True);
  AdoQuery1.FieldDefList.Update;
  //AdoQuery1.FieldList.Update;
  //Listbox1.Items.Assign(AdoQuery1.FieldList);
end;

The call to .OpenCursor with its InfoQuery param set to true causes the AdoQuery's FieldDefs to be populated iff the first statement in its Sql would return a result set, but, unlike calling .Open, it will not cause the batch to be executed.

So far, so good. Here's my question:

How (via the AdoQuery or otherwise) do I get the Sql Server to tell me how many statements it thinks the batch contains? (I think I may have stumbled on a way (subject to a lot more testing), but am interested in whether anyone knows an "official" technique for doing this.)

Btw, for now I'm using an antique (Sql Server 2000!) server via its OleDB driver for Sql Server.

回答1:

You can use the SET SHOWPLAN_ALL function to analyze the statement in SQL server instead of executing the query. Please note that you can't use this functionality with TADOQuery but only with a TADOCommand object (Like TADOConnection.Execute).

test table:

USE [TestCustomer]

GO
CREATE TABLE [dbo].[Tbl_test](
    [Id] [int] NULL,
    [col1] [varchar](50) NULL
) ON [PRIMARY]

GO

small demo program:

program SO27007086;

{$APPTYPE CONSOLE}

uses
  ActiveX,
  Db,
  AdoDb,
  SysUtils;

var
  DbConn : TADOConnection;

function GetNumberOfStatements(SQLQuery: String): Integer;

var
  Rs  : _RecordSet;
  LastId : Integer;   

begin
 Result := 0;
 LastId := -1;
 DbConn.Execute('SET SHOWPLAN_ALL ON');
 Rs := DbConn.Execute(SQLQuery, cmdText, []);
 while not Rs.EOF do
  begin
   if Rs.Fields['StmtId'].Value <> LastId then
    begin
     Inc(Result);
     LastId := Rs.Fields['StmtId'].Value;
    end;
   if Rs.Fields['Parent'].Value = 0 then
    Writeln(Rs.Fields['Type'].Value);
   Rs.MoveNext;
  end;
 DbConn.Execute('SET SHOWPLAN_ALL OFF');
end;

begin
  try
   try
    CoInitialize(nil);
    DbConn := TADOConnection.Create(nil);
    try
     DbConn.ConnectionString := 'Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=TestCustomer;Data Source=localhost\SQLEXPRESS;MARS Connection=True;';
     DbConn.Connected := True;
     Writeln(GetNumberOfStatements('SELECT * FROM Tbl_test'));
     Writeln(GetNumberOfStatements('SELECT * FROM Tbl_test DELETE FROM Tbl_test WHERE 1 = 2'));
     Writeln(GetNumberOfStatements('SELECT * FROM Tbl_test INSERT INTO Tbl_Test (Id, Col1) VALUES (3, ''c''),(4, ''d'')'#13#10'DELETE FROM Tbl_test WHERE 1 = 2'));
    finally
      DbConn.Free;
    end;
   finally
    CoUninitialize;
   end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Readln;
end.

Output:

SELECT
1
SELECT
DELETE
2
SELECT
INSERT
DELETE
3