TADOQuery Temp Table如果在查询中有参数，则丢失 [英] TADOQuery Temp Table Lost if it has a parameter in query

问题描述

如果我对"Where参数"进行硬编码，我有一个TADOQuery会生成一个tempTable，但是如果我使用TADO参数，则下一个查询将不知道该临时表.

我在做什么错了?

我希望我可以简化此示例，但是在这里.(SQL Server)

 创建表brFTNode_Children(pID整数NOT NULL，cID整数NOT NULL，主键(pID，cID));插入brFTNode_Children值(1,2);插入brFTNode_Children值(1,3);插入brFTNode_Children值(3,4);插入brFTNode_Children值(3,5);插入brFTNode_Children值(6,4);插入brFTNode_Children值(6,7); 

代码(无效)

 过程Foo(fDBCon:TADOConnection);constCreateTempTable ='与FT_CTE AS('+'选择pID，来自brFTNode_Children的cID'+'WHERE pID =:TOPID'+'UNION ALL'+'从brFTNode_Children e中选择e.pID，e.cID'+'INNER JOIN FT_CTE ftCTE on(ftCTE.cID = e.pID))'+'SELECT * INTO #ParentChild FROM FT_CTE;';GetSQL ='SELECT pID，来自#ParentChild OR ID BY pID的cID；';变种q1:TADOQuery；q2:TADOQuery；开始q1:= TADOQuery.Create(nil);q1.Connection:= fDBCon;q1.SQL.Text:= CreateTempTable;q1.ParamCheck:= True;q1.Parameters.ParamByName('TOPID').DataType:= ftInteger;q1.Parameters.ParamByName('TOPID').Value:= 1;q1.ExecSQL;q2:= TADOQuery.Create(nil);q2.Connection:= fDBCon;q2.SQL.Text:= GetSQL;q2.Active:= true;//这里失败不知道表#ParentChild结尾; 

代码-在SQL查询中使用常量

 函数TGenerateSolveFile.GetBinaryStream(topID:Cardinal;var bFile:TMemoryStream):布尔值;constCreateTempTable ='与FT_CTE AS('+'选择pID，来自brFTNode_Children的cID'+'WHERE pID = 1'+//更改为常量'UNION ALL'+'从brFTNode_Children e中选择e.pID，e.cID'+'INNER JOIN FT_CTE ftCTE on(ftCTE.cID = e.pID))'+'SELECT * INTO #ParentChild FROM FT_CTE;';GetSQL ='SELECT pID，来自#ParentChild OR ID BY pID的cID；';变种q1:TADOQuery；q2:TADOQuery；开始q1:= TADOQuery.Create(nil);q1.Connection:= fDBCon;q1.SQL.Text:= CreateTempTable;//q1.ParamCheck:= True;//q1.Parameters.ParamByName('TOPID').DataType:= ftInteger;//q1.Parameters.ParamByName('TOPID').Value:= 1;q1.ExecSQL;q2:= TADOQuery.Create(nil);q2.Connection:= fDBCon;q2.SQL.Text:= GetSQL;q2.Active:= true;结尾; 

解决方案

参数化查询使用的是 exec sp_executesql ，它具有自己的会话.

您将从探查器获得此信息.

.pID))SELECT * INTO #ParentChild FROM FT_CTE;'，N'@ P1 int'，1

如果您在SSMS中执行此操作，然后调用从#ParentChild 中选择*，您将得到相同的错误.

sp_executesql(Transact-SQL)

在批处理，名称范围和数据库上下文方面，

sp_executesql 具有与 EXECUTE 相同的行为.在执行 sp_executesql 语句之前，不会编译 sp_executesql @stmt 参数中的Transact-SQL语句或批处理.然后，将 @stmt 的内容编译并执行，作为与名为 sp_executesql 的批处理的执行计划分开的执行计划. sp_executesql 批处理不能引用在调用 sp_executesql 的批处理中声明的变量. sp_executesql 批处理中的本地游标或变量对于调用 sp_executesql 的批处理不可见.数据库上下文中的更改仅持续到 sp_executesql 语句的末尾.

I have a TADOQuery that generates a tempTable if I hard code the "Where parameter, it works fine, but if I use a TADO Parameter the next query doesn't know about the temp table.

What am I doing wrong?

I wish I could simplify this example but here it is. (SQL Server)

    CREATE TABLE brFTNode_Children ( 
      pID integer NOT NULL, 
      cID integer NOT NULL, 
      primary key (pID, cID)
    );

    insert into brFTNode_Children values(1,2);
    insert into brFTNode_Children values(1,3);
    insert into brFTNode_Children values(3,4);
    insert into brFTNode_Children values(3,5);
    insert into brFTNode_Children values(6,4);
    insert into brFTNode_Children values(6,7);

Code (Doesn't work)

procedure Foo(fDBCon : TADOConnection);
const
    CreateTempTable =
                  'WITH FT_CTE AS( ' +
                  'SELECT pID, cID FROM brFTNode_Children ' +
                  'WHERE pID = :TOPID ' +
                  'UNION ALL ' +
                  '  SELECT e.pID, e.cID FROM brFTNode_Children e ' +
                  '  INNER JOIN FT_CTE ftCTE on (ftCTE.cID = e.pID)) ' +
                  'SELECT *  INTO #ParentChild FROM FT_CTE; ';


    GetSQL =
                  'SELECT pID, cID  FROM #ParentChild ORDER BY pID; ';
var
  q1  : TADOQuery; 
  q2  : TADOQuery;

begin
  q1 := TADOQuery.Create(nil);
  q1.Connection := fDBCon;
  q1.SQL.Text := CreateTempTable;
  q1.ParamCheck := True;
  q1.Parameters.ParamByName('TOPID').DataType := ftInteger;
  q1.Parameters.ParamByName('TOPID').Value := 1;
  q1.ExecSQL;

  q2 := TADOQuery.Create(nil);
  q2.Connection := fDBCon;
  q2.SQL.Text := GetSQL;
  q2.Active := true; //Fails here does not know table #ParentChild
end;

Code - Works with the constant in the SQL query

function TGenerateSolveFile.GetBinaryStream(    topID  : Cardinal;
                                            var bFile: TMemoryStream): Boolean;

const
    CreateTempTable =
                  'WITH FT_CTE AS( ' +
                  'SELECT pID, cID FROM brFTNode_Children ' +
                  'WHERE pID = 1 ' + //Changed To a constant
                  'UNION ALL ' +
                  '  SELECT e.pID, e.cID FROM brFTNode_Children e ' +
                  '  INNER JOIN FT_CTE ftCTE on (ftCTE.cID = e.pID)) ' +
                  'SELECT *  INTO #ParentChild FROM FT_CTE; ';


    GetSQL =
                  'SELECT pID, cID  FROM #ParentChild ORDER BY pID; ';
var
  q1  : TADOQuery;  
  q2  : TADOQuery;

begin
  q1 := TADOQuery.Create(nil);
  q1.Connection := fDBCon;
  q1.SQL.Text := CreateTempTable;
//  q1.ParamCheck := True;
//  q1.Parameters.ParamByName('TOPID').DataType := ftInteger;
//  q1.Parameters.ParamByName('TOPID').Value := 1;
  q1.ExecSQL;

  q2 := TADOQuery.Create(nil);
  q2.Connection := fDBCon;
  q2.SQL.Text := GetSQL;
  q2.Active := true;
end;

解决方案

A parameterized query is using exec sp_executesql, which has it's own session.

You will get this from the profiler.

exec sp_executesql N'WITH FT_CTE AS( SELECT pID, cID FROM brFTNode_Children WHERE pID = @P1 UNION ALL   SELECT e.pID, e.cID FROM brFTNode_Children e   INNER JOIN FT_CTE ftCTE on (ftCTE.cID = e.pID)) SELECT *  INTO #ParentChild FROM FT_CTE; 
',N'@P1 int',1

If you execute this in the SSMS and call select * from #ParentChild afterwards you will get the same error.

sp_executesql (Transact-SQL)

sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Changes in database context last only to the end of the sp_executesql statement.

这篇关于TADOQuery Temp Table如果在查询中有参数，则丢失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！