my QueryDesigner, fill ppdatapipeline at runtime

rbuser

Some time ago i found help in this newsgroup on a particular problem.


I develop my own query designer, i've a ppdatadictonary, tdasql , 3
tppdbpipeline (tables, joins, fields)
I fill table, fields and joins at runtime, and all work correctly, except in
one case:

I've a table that it is in join with 2 ore more SAME table, for example:
Primary table--> "PersonalData" with 6 fields:
- Name
- SUrname
- Living province (relation at PROVINCE
table)
- Born province (relazion at PROVINCE
table)
secondary table PROVINCE
- Code
- Description

when i fill my TDataset with correct data, i insert only 1 table PersonaData
and 1 table Province, and i create 2 joins, but it doesn't work. I can't
select for 2 times "province", only one time....

ps: available tables, once selected, are gray

Can you help me? thanks .. bye

nardmoseley

Try setting DataDictionary.AllowManualJoins to true.

--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

not so easy:

i want to create automatically the entire structure of selected table, so i
do this:

1)fill all 3 dataset (tables, fields, joins)
2)select the primary table (all other tables are related to that)
3) next i try to select all other table... (but now i've a new problem...
:-( )
In the third point i do:

memjoin.First;
while not memjoin.Eof do
begin
for i:=0 to fsql.AvailableTableCount - 1 do
begin
if fsql.AvailableTables[i].TableName =
memjoin.FieldByName('TableName2').asstring then
ltable := fsql.AvailableTables[i];
end;
fsql.SelectTable(i-1,memjoin.RecNo-1);
memjoin.Next;
end;
memjoin.First;

memjoins is the dataset filled with join information. The problem is that
when it try to SelectTable for the second time (the second time that it
select PROVINCE), the code stop and the program popup me the form of "TABLE
OF JOIN", it doesn't use the join information.

Could it be that when i fill memjoin i didn't create different alias for the
same related table?
Can you give me an example of automatic select of table related with 2
different fields at the same table?
many thanks

nardmoseley

The autojoin feture is not designed to enable self-joins.I think you can
only join the same table twice by performing a manual join.

--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

ok, but can i write my own code to perform manual join?

nardmoseley

First step is to try using the RB Query Designer without the autojoin
feature (or with DataDictionary.AllowManualJoins set to true.) to determine
whether it is possible to create the type of SQL query that you need.

If the above test works, then the next step is to determine what code is
required. The QueryDesigner builds SQL by calling methods of the TdaSQL
object. The source code to the QueryDesigner is located in
daQueryDesigner.pas. Check out the TdaTablePage class. The daSQL.pas unit
contains the source code to the TdaSQL class. Modify your Delphi library
path from RBuilder\Lib to RBuilder\Source and then trace the code to the
query designer.


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Ok, i will try to explain again

I fill Tables with:
- CUSTOMERS
- PROVINCE
(customers referencing province 2 times! at field "RagProv" and "LegalProv")

I fill Fields with all tables field

I fill Joins with: (tablename1, tablename2, jointype, fieldname1,
fieldname2, operators)
- CUSTOMERS - PROVINCE - dajtLeftOuter - RagProv - ProvCode - =
- CUSTOMERS - PROVINCE - dajtLeftOuter - LegalProv - ProvCode - =

Is this an error?????????

afrer i make the selection of principal table with this code:
var
myTablename:string; // <---- contain the string CUSTOMERS
begin
for i:= 0 to fsql.AvailableTableCount -1 do
begin
ltable:=fsql.AvailableSelectTables[i];
if ltable.TableName=myTableName then
begin
tabellaprincipale:=fsql.SelectTable(i,-1);
break;
end;
end;

After this i make the selection of secondary related table:

memjoin.First; //<--- dataset of join...
while not memjoin.Eof do
begin
for i:=0 to fsql.AvailableTableCount - 1 do
begin
if fsql.AvailableTables[i].TableName =
memjoin.FieldByName('TableName2').asstring then
ltable := fsql.AvailableTables[i];
end;
fsql.
fsql.SelectTable(i-1,memjoin.RecNo-1);
memjoin.Next;
end;

The result:

If i have datadictonary.allowmanualJoin=true at runtime it pop up me the
form of join selection...
If i have datadictonary.allowmanualjoin=false at runtime he didn't complete
the sql correctly, it's like

SELECT ........
FROM CUSTOMERS CUSTOMERS
LEFT OUTER JOIN PROVINCE PROVINCE ON
(PROVINCE.PROVCODE = CUSTOMERS.RAGPROV)
PROVINCE PROVINCE_2 ON
!!!! it didn't find join information correctely...


Can you help me????

rbuser

ops, i've posted another question .... sorry...

anyway: with allowmanualjoin at true i can make all i want
with allowmanualjoin at false the table is grayed after the first
selection!... i think there is an error in my join definition... but where?
look at my code posted in other message

rbuser

i think the problem is
fsql.SelectTable(i-1,memjoin.RecNo-1);
it didn't identify the correct join. ..

"Tefnut" ha scritto nel messaggio
news:3fcb6ea5$1@dm500....
"LegalProv")
complete
it
for

rbuser

i've tried a lot of solution but it can't work...


what is wrong? there are 2 alternative:
1) fsql.SelectTable(i-1,indexjoin); //i've never understood how it
work. How i can add select table with correct join?
2) i fill joins pipeline with incorrect information.....


"Tefnut" ha scritto nel messaggio

nardmoseley

In the Join table, the combination of TableName1, TableName2 is the pimary
key (i.e. unique).

Sorry, but the AutoJoin feature does not support what you are trying to
accomplish - you cannot define multiple join conditions between the same two
tables.


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

oh nooo...

i need to find a solution.. i will try :-p thanks