Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 9 of 9 total |
How to find circular references |
Thu, Sep 17 2015 6:58 PM | Permanent Link |
Adam H. | Just wondering if anyone has come across the need for this before, and
if so if you have a solution? Let's say I have a table with fields: CompanyID ParentCompanyID I want to detect circular references and exclude them from being an option. ie: CompanyID ParentCompanyID 2 1 3 2 4 3 .....is OK, but CompanyID ParentCompanyID 2 1 1 2 Is not OK - Ciruclar refrence. This part is easy, but I get into trouble when I'm wanting to detect more complex circular references, such as: CompanyID ParentCompanyID 1 4 2 1 3 2 4 3 , etc.. Can anyone advise of the best approach with this? Thanks & Regards Adam. |
Fri, Sep 18 2015 2:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Quick google http://dba.stackexchange.com/questions/45158/how-to-write-a-query-which-finds-all-circular-references-when-a-table-references http://stackoverflow.com/questions/7540760/detecting-circular-references-in-sql Essentially you're going to have to do some recursion and walk back up the relationship tree. Never a pleasant job in sql Roy Lambert |
Fri, Sep 18 2015 2:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Another thought - you can do it with SQL/PSM but it may well be easier to write an external function in Delphi. Roy Lambert |
Fri, Sep 18 2015 9:42 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< I want to detect circular references and exclude them from being an option. >> DBISAM's SQL implementation isn't going to be able to handle this, so you're going to need to write some navigational code to handle this. Are you wanting to do this before inserts/updates in order to prevent them ? Tim Young Elevate Software www.elevatesoft.com |
Fri, Sep 18 2015 10:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim / Adam
This post has made me realise my brain is going - I thought it was ElevateDB - sorry. I think I could do something using engine customization but if you can do it in your app as part of creating the SQL to insert / update a record I think it would be easier to go that route. All you need is a simple recursive function such as the ones I use for tree building. Roy Lambert |
Sun, Sep 20 2015 7:41 PM | Permanent Link |
Adam H. | Hi Roy & Tim,
Outside of SQL will be fine if it's going to be more simpler. (Or the only possible option) My brain is going as well Roy - would you be able to give me a hint as in regards to the tree building function you have. Cheers Adam. |
Mon, Sep 21 2015 4:30 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
I've attached what I use for building my email trees - it uses ElevateDB but should be OK for DBISAM. Roy Lambert Attachments: emTree.pas |
Mon, Sep 21 2015 7:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Had a think about it and this might do you procedure TForm1.BandABeforePost(DataSet: TDataSet); begin if DisallowedRelationship(BandA.FieldByName('_BelongsTo').AsInteger, BandA.FieldByName('_BoxNo').AsInteger, Checker) then DatabaseError('Woops'); end; function TForm1.DisallowedRelationship(Check, Blocked: integer; Qry: TDBISAMQuery): boolean; begin if Check = Blocked then begin Result := True; Exit; end; Qry.Close; if not Qry.Prepared then Qry.Prepare; Qry.ParamByName('Company').AsInteger := Check; Qry.ExecSQL; Result := Qry.RecordCount > 0; if Result then begin Result := Qry.Fields[1].AsInteger = Blocked; if not Result then Result := DisallowedRelationship(Qry.Fields[1].AsInteger, Blocked, Qry); end; end; You need a TDBISAMQuery on the form with this SELECT _CompanyID, _ParentCompanyID FROM "whatever your table name is" WHERE _CompanyID = :Company SQL You can dynamically create the query each time inside DisallowedRelationship, and if you're not doing a lot of these that's the way I'd go Roy Lambert |
Mon, Sep 21 2015 6:28 PM | Permanent Link |
Adam H. | Hi Roy,
Thanks very much - I think I get the idea now! I'll give this approach a go and see what happens. Appreciate your help! Cheers Adam. |
This web page was last updated on Thursday, March 28, 2024 at 06:05 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |