Temp table over multiple ADOQueries

Question

Is there a way to make multiple TADOQuery have access to the same session or transaction information?

Both TADOQuery have the same TADOCOnnection, but that doesn't work.

Example - I have one query that generates a temp table, I want to use that temp table in another query, but when I try, it doesn't know about the temp table.

procedure Foo(dbCon : TADOConnection);
var
  q1  : TADOQuery;  //TODO :  set your ADOQuery to use a forward only read only cursor.
  q2  : TADOQuery;

begin
  q1 := TADOQuery.Create(nil);
  q1.Connection := dbCon;
  q1.SQL.Text := 'SELECT id INTO #TempT FROM dFTNodes;';
  q1.Active := true;

  q2 := TADOQuery.Create(nil);
  q2.Connection := dbCon;
  q2.SQL.Text := 'SELECT id FROM #TempT;';
  q2.Active := true; //Fails here does not know table #TempT
end;

Syntax looks like SQL-Server, is it SQL-Server? BTW `q1.Active := true;` would need to be `q1.ExecSql;` — bummi, Oct 23 '14 at 21:13
.ExecSql gives me the same error "EOleException ... 'invalid object name '#TempT'" — runfastman, Oct 23 '14 at 21:25
@bummi: No, it doesn't `SELECT <> INTO ` returns a rowset, and therefore it works fine with `Open`. — Ken White, Oct 23 '14 at 22:10
@KenWhite I just can't follow, `Select INTO` will fill #TempT, what kind of rowset is returned for you, the error translated would be something like `CommandText does not return a result set' occurred.` — bummi, Oct 23 '14 at 22:33
@bummi: If you run `SELECT * INTO #temp FROM mytable WHERE 1=0`, you get a rowset of one row filled with NULL values. If your WHERE condition would return actual rows if you removed the `INTO` clause, you'll get those rows as the rowset, because the rows placed into `#temp` are returned. Run the `SELECT` in SSMS to see; you get data (rows) returned. — Ken White, Oct 23 '14 at 22:36
@KenWhite Regardless of my daily dealings with such things, I have tested it, of course, the error message I mentioned in my reply to your first comment. Since I knows about your skills, I wonder where the difference may lie. In SSMS I get a result like `(x row (s) affected)` but no resultset. x in your example would be 0. — bummi, Oct 23 '14 at 22:50
@bummi: Hmmm. It must be a configuration thing (some setting on SQL Server). We use it all the time at work, but it doesn't work here on my home system with SQL Server 2008. I'll have to see what's different at the office; I didn't configure the server, so I'm not sure what the difference might be. Until I can find out, though, I bow to your skills. They're apparently better than mine in this area. :-) — Ken White, Oct 23 '14 at 22:56

score 3 · Accepted Answer · answered Oct 23 '14 at 21:28

3

If your TADOConnection is not defined to KeepConnection=True, which is default, the connection is dropped after executing of q1, your session is lost and #TempT is gone.
In general if there are no living TCustomAdodatasets using the connection a connection using KeepConnection=False will be closed. q2 will get a new session and not be able to access #TempT.

answered Oct 23 '14 at 21:28

bummi

27,123
14
62
101

This worked for my example, but it turned out not to be my main problem. I posted another question http://stackoverflow.com/questions/26550791/tadoquery-temp-table-lost-if-it-has-a-parameter-in-query – runfastman Oct 24 '14 at 15:10

Temp table over multiple ADOQueries

1 Answers1