TADOTable & TDataSource - Creating an unknown qty at runtime.

[Johnny66]

Dear all,

May I ask for some help or pointers on the following please ?

My application reads through an SQL statement and creates a DB Grid and its columns etc at runtime. This works well if there is just a single table in the FROM clause.

But there are times when I need to create an unknown quantity of TADOTables and TDataSource’s at runtime. The problem is, I have no idea whether I need to build a single instance of each, or multiples, it all depends on what the users SQL statement contains.

I don’t have a problem creating a single instance when the users SQL statement is …

SELECT * FROM Customers

Var
	ADataSource: TDatasource;
	ATable: TADOTable;

Begin
	ATable := TADOTable.Create(Self);
	ADataSource := TDataSource.Create(Self);
	With ATable do
	Begin
		// Sort out the ATable properties;
	End;
	With ADataSource do
	Begin
		// Sort out the ADataSource properties;
	End;
End;

… I free ATable and ADataSource objects when I’m done with them.

The problem I have is how do I create multiple instances of ATable and ADataSource if the SQL statement contains a join to a second table ? Obviously, I can’t use ATable or ADataSource again as it’s being used for the first Table in the SQL statement.

I know I could declare a second instance of each object, but this just shifts the problem if the SQL’s FROM clause includes a third table, or a fourth, or a fifth.

So imagine the following SQL statement (from Northwind.mdb) …

SELECT *
FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID;

I can collect everything I need from the statement, ie get the table names and then read through their fields and get the primary and foreign keys. In this case there are 3 tables so I need 3 instances of TADOTable and TDataSources.

So my question is …

Is it possible to create multiple TADOTable and TDataSource objects at runtime (without knowing in advance how many I need) and if so how ?

Limiting the users SQL statement to, say, 3 tables isn’t a solution, as I would need to start rewriting the SQL if it contained more than 3 tables in the FROM clause ... I think this would bring a host of other problems.

I hope this post makes sense. Thanks for taking the time to read, I would be grateful for any help you could offer.

Amy.

PS - Using Delphi 7 Pro.

 

[whosrdaddy]

why not use TADOQuery and execute the statement? Or am I misssing the point?

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[Johnny66]

Hi,

Thanks for taking the time to reply. Why not just execute the query ?

I want to give me user a choice, either execute the query and get a single grid containing all the fields they have specified, or build a multi level, Master/Detail grid that they can then use as a front end to the Database.

Executing the query is great for analyzing the data, building charts and Pivot grids etc, but creates problems when there are multiple tables that the user wants to add records to.

Amy.

 

[Griffyn]

to keep track of multiple objects, use TObjectList. You may want to create one each for TADOTable and TDatasource for clarity, although it's strictly not necessary.

After creating an eg. TADOTable, you add it to the TObjectList, and search through it's list when later you need to refer to it.

TObjectList.Items returns a TObject in all cases, so if typecasting doesn't work when you're linking things up, you may want to create a descendent class of TObjectList and overwrite the Items property and the GetItem and SetItem methods to return an eg. TADOTable instead.

This should give you something to think about and play with.

 

[Johnny66]

Thanks Griffyn,

I was getting closer … I had declared a TObjectList, but I have to be honest I had not done anything with it yet. I will get cracking right away.

I think I'll use 2 for now, just so I can get my head around things, before trying to neaten things up.

Many thanks for the post.

Amy.

 

[DjangMan]

I did something similar for a project. I used a TStringList and the .AddObject method. Then I was able to give a 'name' to my connection or query (I have a list for each) and then I didn't have to build in any searching functions.

function TdmDB.CreateADOConn(const aName, aConnectionString: string): boolean;
var
  NewConn: TADOConnection;
begin
  NewConn := TADOConnection.Create(nil);
  NewConn.ConnectionString := aConnectionString;
  try
    NewConn.Open;
    ADOConnections.AddObject(aName,NewConn);
    result := true;
  except
    on e:exception do
    begin
      AddError(e.message);
      result := false;
    end;
  end;
end;

And then later I clean them up:

  while ADOConnections.Count > 0 do
  begin
    TADOConnection(ADOConnections.Objects[ADOConnections.Count-1]).Close;
    TADOConnection(ADOConnections.Objects[ADOConnections.Count-1]).Free;
    ADOConnections.Delete(ADOConnections.Count-1);
  end;

  ADOConnections.Free;

When the user adds a query I look for the named connection (and do a bunch of other stuff):

function TdmDB.CreateADOQuery(const aConnName, aQueryName, aSQL, aIndexes: string): boolean;
var
  connidx: integer;
begin
  ADOConnections.Find(aConnName,connidx);
  if connidx < 0 then
  begin
    result := false;
    exit;
  end;

  //Other stuff

Hope that helps.

 

[Johnny66]

Thanks DjangMan,

I'm really grateful you posted the code. I need to create a little project and go through what you have done.

Amy.