Using Multiple Temporary tables in Delphi with an ADO connection to the database produces errors

Question

I'm having an issue with temp tables using an ADO connection to my database. The select into queries run fine, but it seems that they are dropping the tables after each select into. Their is no open recordset so I am unsure as to why the tables are dropping, it shouldn't be opening a 2nd ADO connection that I can see.

Any idea what I can do to keep the temp tables from dropping?

  begin
    // Get Data


    // Build the work tables for case and claims
    // Find CasemmyyyyEOM for prior month
    //

    begin
        cmd := TStringList.Create;
        cmd.Add(
            'select casenumber, fundsavail, totalrcpts, totalobjectn, totaldisbursed, confirmdate, confirmcode into #casemasterwork from CaseMaster');

        cmd.Add(
            'select caseid, claimnumber, splitcode, prinpdtodate, intpdtodate into #claimswork from claims');
        fConnection.Execute(cmd.text);
{$IFDEF DEBUG}
        MessageLog(cmd.text, 'Query');
{$ENDIF}
        cmd.Free;

        // Sum up receipts posted after end of month
        cmd := TStringList.Create;
        cmd.Add(
            'select rcpthistcasekey, amount = sum(rcpthistamt) into #rcpts from rcptshist where ');
        If cbxRunbyDate.Checked = False then
            cmd.Add('substring(rcpthistinputid,1,4) > ''' + copy(sJrnlYM, 3,
                    4) + ''' And rcpthisttrandate > ''' + sMonthBegin + '''')
        Else
            cmd.Add(' rcpthisttrandate > ''' + sMonthEnd + ''' ');
        cmd.Add(' group by rcpthistcasekey');
{$IFDEF DEBUG}
        MessageLog(cmd.text, 'Query');
{$ENDIF}
        fConnection.Execute(cmd.text);
        cmd.Free;

        // Sum up disbursements by case posted after end of month
        cmd := TStringList.Create;
        cmd.Add(
            'select disbcasekey, amount = sum(disbprinamt + disbintamt) into #disb from disbursed where ');
        If cbxRunbyDate.Checked = False then
            cmd.Add('disbrunyrmo > ''' + sJrnlYM + '''')
        Else
            cmd.Add('DisbTrDate > ''' + sMonthEnd + '''');
        cmd.Add('group by disbcasekey');
{$IFDEF DEBUG}
        MessageLog(cmd.text, 'Query');
{$ENDIF}
        fConnection.Execute(cmd.text);
        cmd.Free;

        // Sum up disbursements by case/claim posted after end of month
        cmd := TStringList.Create;
        cmd.Add(
            'select disbcasekey, disbclaimnum, prinamt = sum(disbprinamt), intamt = sum(disbintamt) into #clmdisb from disbursed where');
        If cbxRunbyDate.Checked = False then
            cmd.Add('disbrunyrmo > ''' + sJrnlYM + '''')
        Else
            cmd.Add('DisbTrDate > ''' + sMonthEnd + '''');
        cmd.Add('group by disbcasekey, disbclaimnum');
{$IFDEF DEBUG}
        MessageLog(cmd.text, 'Query');
{$ENDIF}
        fConnection.Execute(cmd.text);
        cmd.Free;

        // back out the disbursements from the claims work table
        cmd := TStringList.Create;
        cmd.Add('update #claimswork set prinpdtodate = prinpdtodate - prinamt, intpdtodate = intpdtodate - intamt from #clmdisb');
        cmd.Add('where disbcasekey = caseid and disbclaimnum = claimnumber + splitcode');
{$IFDEF DEBUG}
        MessageLog(cmd.text, 'Query');
{$ENDIF}
        fConnection.Execute(cmd.text);
        cmd.Free;

        // back out the disbursements from the casemaster work table
        cmd := TStringList.Create;
        cmd.Add(
            'update #casemasterwork set fundsavail = fundsavail - amount, totalrcpts = totalrcpts - amount from #rcpts');
        cmd.Add('where casenumber = rcpthistcasekey');
{$IFDEF DEBUG}
        MessageLog(cmd.text, 'Query');
{$ENDIF}
        fConnection.Execute(cmd.text);
        cmd.Free;

        // back out the receipts from the casemaster work table
        cmd := TStringList.Create;
        cmd.Add(
            'update #casemasterwork set fundsavail= fundsavail + amount, totaldisbursed = totaldisbursed - amount from #disb');
        cmd.Add('where casenumber = disbcasekey');
{$IFDEF DEBUG}
        MessageLog(cmd.text, 'Query');
{$ENDIF}
        fConnection.Execute(cmd.text);
        cmd.Free;

        // Set the confirmcode based on the confirm transfer table
        Try

            cmd := TStringList.Create;
            cmd.Add('update #casemasterwork set confirmcode = ''U''');
            cmd.Add(
                'from confirmdxfer, #casemasterwork  where #casemasterwork.casenumber = confirmdxfer.casenumber');
            cmd.Add('and xferdate > ''' + sMonthEnd + '''');
            // del 11-28-2004 ebs   readded 10/25/2005 esatori
            // cmd.Add('and xferdate between ''' + sMonthBegin + ''' and ''' + sMonthEnd + ''' '); // 11-28-2004 ebs
{$IFDEF DEBUG}
            MessageLog(cmd.text, 'Query');
{$ENDIF}
            fConnection.Execute(cmd.text);
            cmd.Free;
        Except
        End;

        cmd := TStringList.Create;
        cmd.Add('select TotRcpts = sum(amount) from #rcpts');
{$IFDEF DEBUG}
        MessageLog(cmd.text, 'Query');
{$ENDIF}
        rs := fConnection.Execute(cmd.text);
        cmd.Free;
        rs.MoveFirst;
        cTotalCurRcpts := rs.Fields.Item('TotRcpts').Value;
        rs.Close;

Answer 1

Rather than letting "select...into #TempTable..." implicitly create the temp table, try executing a SQL statement to explicitly create the table first "create table #TempTable(...)", then reference those tables in your inserts and updates.

Answer 2

My Delphi knowledge is very, very dated - is there any chance this is using any kind of connection pooling? You can verify by running EXEC sp_who2 and seeing if there's multiple connections from your app.

If there is and you are 100% sure that only one of these operations will run at a time then switch the temporary tables from #casemasterwork to ##casemasterwork this will make them global temporary tables and thus accessible from any connection.

Remember to drop them!

Answer 3

Is your fConnection.KeepConnection property set to True? If not, this is your problem. (Assuming fConnection is a TADOConnection)

Is the above code the actual code used? Are you using parameterized queries? If so, that's another issue with a Microsoft KB article on it.

Answer 4

After much research on this issues I have found the answer. ADO has the tendency to open a second hidden connection to the DB. To avoid this, make sure all recordsets are closed before the temp tables are executed and be sure to close any recordset used in the temp table logic.