Delphi2005 - Using TADOQuery Results in another method

Question

Because of code redundancy I generalized the usual ADOQuery handling into a method of its own, with the method returning the _RecordSet of the query. Now, when I check the query results inside that method, they check out alright. But the returned _RecordSet seems to be empty or nil (Exception: EOleException 'Item cannot be found in the collection to the corresponding name or ordinal.') From what I gathered, I seem to need to return a clone of the query's _RecordSet. So far I have tried using

res := qr.Recordset.Clone(adLockUnspecified); Result := res;,

TADOQuery.RecordSet._xClone() method

unsuccessfully, as well as THESE methods, none of which worked (several components not recognized, version differences I'm guessing).

The code I use for querying:

function queryDb(const s: WideString; const dc: boolean = true): _RecordSet;
var
  ds: String;
  conn: TADOConnection;
  qr: TADOQuery;
  res: _RecordSet;
begin
  Result := nil;
  conn := TADOConnection.Create(nil);
  qr := TADOQuery.Create(nil);
  try
    ds := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
          + ExtractFilePath(Application.ExeName)
          + 'gdkp.mdb;Persist Security Info=False';
    conn.ConnectionString := ds;
    conn.LoginPrompt := false;
    conn.Connected := true;
    qr.Connection := conn;
    if(dc = true)then begin
      qr.DisableControls
    end;
    qr.SQL.Add(s);
    qr.Open;
    Result := qr.Recordset;
  finally
    conn.Free;
    qr.Free;
  end;
end;

---

Has someone overcome this problem before and knows a suitable answer or can direct me towards helpful content?

Answer 1

I don't know which of your crazy antics is more crazy:

1. Setting up and connecting to a database inside a function and doing your queries inside this function, connecting to the database EACH TIME YOU QUERY, is going to be insanely slow.

2. ADO Query objects should have a longer lifetime than the insides of a function. This is a code smell, a severe one.

3. Keep the recordset inside the query where it belongs.

4. Normal people create TDataModules and leave their objects around for the lifetime of their app, and requery using this code:

   query.Active := false; query.Active := true;

Watch your app speed up when it doesn't have to connect to the database, create and tear down a connection and a query, each time you want some data.

What on earth are you doing with the _recordsets youre getting back? Did you learn ADO in C# and you're trying to use C# ADO.net idioms in Delphi? Don't. Stop doing that.