Re: Consecutive queries

On 6 Apr 2003 at 19:41, Tom Lane wrote:

> This behavior is, quite simply, not possible.  Unless perhaps you are
> issuing the two queries across different connections, so that the
> second one actually starts to execute before the first one is done.

I've tried to ensure that the two queries are run by the same
backend. I'm using Delphi 6 to manipulate ADO, which talks to
Postgres via ODBC. The code of the specific procedure which seems to
be misbehaving is given below - as you'll see, the two queries are
built as a single string and so are executed together when
TheQry.Open is called.

>  If you are (mis)using a client library that implements pooling of
> connections, I can see how such a mistake might happen.

The connection pooling is handled by ODBC.

--Ray.

-----[Delphi code follows]------

function TDatabaseLink.CountApplicantsAvailable(const CourseCode:
WideString): Integer;
var
  Conn: TADOConnection;
  TheQry: TADOQuery;
begin
  Conn := TADOConnection.Create(nil);
  TheQry := TADOQuery.Create(nil);
  try
    Conn.ConnectionString := ADOConnStr; // defined elsewhere
    Conn.Open;
    TheQry.Connection := Conn;

    TheQry.SQL.Text := 'begin; ';

    // add the UPDATE query
    TheQry.SQL.Add('update applications set status=' +
MakeIntegerStr(statAvailable + statChoice2)
      + ' where applicationnumber in '
      + '(select applicationnumber from applications a inner join
courses c on (a.choice1=c.coursecode) '
      + 'where (a.choice2=' + QuotedStr(CourseCode) + ') '
      + 'and (c.isfull=' + MakeBooleanStr(true) + ') '
      + 'and (a.status=' + MakeIntegerStr(statAvailable +
statChoice1) + ')'
      + '); ');

    TheQry.SQL.Add('commit; ');

    // add the SELECT query
    TheQry.SQL.Add('select count(applicationnumber) from applications
'
      + 'where (choice1=' + QuotedStr(CourseCode) + ' and status=' +
MakeIntegerStr(statAvailable + statChoice1) + ') '
      + 'or (choice2=' + QuotedStr(CourseCode) + ' and status=' +
MakeIntegerStr(statAvailable + statChoice2) + ') '
      + 'or (choice3=' + QuotedStr(CourseCode) + ' and status=' +
MakeIntegerStr(statAvailable + statChoice3) + ');');

    // execute the query
    TheQry.Open;
    Result := TheQry.Fields[0].AsInteger;
  finally
    TheQry.Close;
    TheQry.Free;
    Conn.Close;
    Conn.Free;
  end;
end;


-------------------------------------------------------------
Raymond O'Donnell     http://www.galwaycathedral.org/recitals
rod@iol.ie                          Galway Cathedral Recitals
-------------------------------------------------------------