获取TransactSql批处理中的语句数计数 [英] Getting a count of the number of statements in a TransactSql batch

问题描述

（对于不使用Delphi的读者：尽管下面的内容是关于Delphi编码的，但我的实际技术问题不是特定于Delphi的，而是关于如何找出Sql Server如何理解的问题一个TransactSql批处理提交给它。 TAdoQuery是一个Delphi类，它基本上包装了一个ADO命令和一个RecordSet并将一个TSql批处理提交给Sql Server。通常，使用TAdoQuery，该批处理是单个语句，但是我的q尤其担心该批处理可能包含多个语句。）

(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.)

假设我有一个TAdoQuery，其Sql.Text包含一个包含
的TransactSql批处理一个或多个语句S1 [... Sn]。

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

我要执行的操作是找出不执行批处理 a）批处理中的第一个（或唯一）语句S1将返回结果集（即使为空），例如通过使其成为SELECT语句或调用存储过程或表函数等， AND b）服务器认为批处理中有多少条语句。

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.

Delphi的TAdoQuery的常规用户会知道，测试第一个（或仅批处理中的语句仅通过调用TAdoQuery.Open返回结果集。如果这样做，那么它将检索该结果集，但是如果没有，则调用.Open将引发异常。

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.

因此，我这样做：

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;

调用.OpenCursor并将InfoQuery参数设置为true会导致AdoQuery的FieldDefs
在 iff 中填充Sql中的第一条语句将返回结果集，但是与调用.Open不同，它不会导致执行批处理。

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:

如何（通过AdoQuery或其他方式）让Sql Server告诉我它认为该批处理包含多少个
语句？ （我认为我可能偶然发现了一种方法（需要进行更多测试），但对是否有人知道这样做的官方技术很感兴趣。）

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.)

顺便说一句，现在我正在通过其用于SQL Server的OleDB驱动程序使用旧式（Sql Server 2000！）服务器。

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

推荐答案

您可以使用 SET SHOWPLAN_ALL 函数可在SQL Server中分析语句，而不是执行查询。请注意，您不能将此功能与 TADOQuery 一起使用，而只能与 TADOCommand 对象一起使用（例如 TADOConnection.Execute ）。

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).

测试表：

USE [TestCustomer]

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

GO

小型演示程序：

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.

输出：

SELECT
1
SELECT
DELETE
2
SELECT
INSERT
DELETE
3

这篇关于获取TransactSql批处理中的语句数计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！