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(at)iol(dot)ie Galway Cathedral Recitals
-------------------------------------------------------------