Home Component Writing
New Blog Post: Email: Multi-Service

my QueryDesigner, fill ppdatapipeline at runtime

edited November 2003 in Component Writing
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

Comments

  • edited November 2003

    Try setting DataDictionary.AllowManualJoins to true.

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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited November 2003
    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

  • edited November 2003

    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited November 2003
    ok, but can i write my own code to perform manual join?
  • edited December 2003

    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited December 2003
    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????




  • edited December 2003
    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


  • edited December 2003
    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
  • edited December 2003
    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
  • edited December 2003

    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited December 2003
    oh nooo...

    i need to find a solution.. i will try :-p thanks
This discussion has been closed.