Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Post changes back to multiple tables |
Wed, Jul 10 2013 10:51 AM | Permanent Link |
Aaron Taylor | Hi, is there an elegant way to post changes back from a canned query that contains mutliple tables.
the senario is i want to query 2 or more tables, edit some of the data and post the changes. |
Wed, Jul 10 2013 2:05 PM | Permanent Link |
Robert Kaplan | Well "elegant" I don't know, but in the query's beforepost you can test
oldvalue to see what has changed, or simply assume that everything has changed and update every updatable field. Robert K <Aaron Taylor> wrote in message news:014223CD-ECE1-4912-8892-211B41B3817C@news.elevatesoft.com... > Hi, is there an elegant way to post changes back from a canned query that > contains mutliple tables. > > the senario is i want to query 2 or more tables, edit some of the data and > post the changes. > |
Thu, Jul 11 2013 3:04 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Aaron
>Hi, is there an elegant way to post changes back from a canned query that contains mutliple tables. > >the senario is i want to query 2 or more tables, edit some of the data and post the changes. If you're looking for a simple command to say UPDATE CHANGES then no, there are however, a slew of ways to achieve the update and wether or not they are elegant depends on your definition, and how you're doing the editing. You could, for example, use a components OnChange event to set a flag (eg its Tag) and when the users says commit cycle round the components and where the flag is set add that to the UPDATE sql. You can do much the same with a persistent field and its OnValidate event. Another approach is that when the user asks to commit the changes run a piece of SQL that pulls down the data for the specific row to be updated and cycle round the fields checking to see which is different. Roy Lambert [Team Elevate] |
Thu, Jul 11 2013 11:38 PM | Permanent Link |
Aaron Taylor | thanx guys.
yes i knew there was not a simple command to post the changes (shame) My understanding is I can treat the canned result like a table and perform edit, post etc.. but take full responsibility for getting it back to the database. I was hoping someone might have a snippet of code that might show a neat way to do it. if not your replies have given me a great place to start. |
Fri, Jul 12 2013 4:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Aaron
>My understanding is I can treat the canned result like a table and perform edit, post etc.. >but take full responsibility for getting it back to the database. Correct for DBISAM and I doubt Tim has any plans for changing that. If you want to change over to ElevateDB at some point you might want to consider using in-memory tables instead of canned datasets. Much the same just a bit different on how you get there. >I was hoping someone might have a snippet of code that might show a neat way to do it. Such a "snippet" would have to start by analysing your sql so that the various tables and fields associated with them could be identified, then, since its impossible to know which controls are being used to edit the data go down the route of creating persistent fields, adding OnValidate or OnChange code and finally override the AfterPost routine of the canned dataset so that stuff could be posted back to the underlying tables. Typing the above did surface an idea - if you're prepared to write your SQL in a fashion to indicate the table the data came from eg SELECT Name AS tble1_Name, tbl1_Amount, tbl2_Customer etc FROM Invoices JOIN Customers AS Cust ON Invoices.CustID = Cust.CustID Then in the AfterPost event for the canned query you can have code along the lines of //Identify the number of tables sl1 := TStringList.Create sl1.Sorted := True; sl1.Duplicates := dupIgnore; qry.FieldDefs.Update; for Cntr := 0 to qry.FieldDefs.Count - 1 do begin fn:= qry.FieldDefs[Cntr].DisplayName; sl1.Add(Copy(fn,1,Pos('_',fn)-1); end; for Cntr := 0 to sl1.Count - 1 do begin sl2:= TStringList.Create; for SubCntr := 0 to FieldDefs.Count -1 do begin fn:= qry.FieldDefs[SubCntr].DisplayName; if fn = sl1[Cntr] then begin sl2.Add(fn + ' = ' + qry.Fields[SubCntr].AsString) ... you'll probably want to test for the field type here and quote strings, adjust dates etc end; end; if sl2.Count > 0 then begin sl2.Insert(0,'SET '); sl2.Insert(0,'UPDATE sl1[Cntr]); updaterqry.close; updaterqry.sql.assign(sl2); updaterqry.ExecSQL; end; sl2.Free; end; Totally of the top of my head and untested so if it vaporises your PC don't blame me. Roy Lambert [Team Elevate] |
Sat, Jul 13 2013 9:29 AM | Permanent Link |
Aaron Taylor | Thanx Roy, thats great, ill have a play with that.
I have considered upgrading to ElevateDB but as it does not offer me anything that justifies the extra expense. Now if it allowed posting changes back to multiple tables I would upgrade in a flash. |
Tue, Jul 16 2013 8:03 PM | Permanent Link |
Aaron Taylor | Heres what i have so far.
I found a neat property called Origin that returns the table and field name. //fill listbox1 with field names var a: integer; Field: string; begin for a:= 0 to qryMisc.FieldList.Count - 1 do begin Field := qryMisc.FieldList[a].Origin; ListBox1.Items.Add(Field); end; end; Then I found properties NewValue & OldValue that returns as the name says the new and old field values. //fill listbox2 with fields that have changed var a: integer; for a := 0 to qryMisc.FieldList.Count - 1 do begin if qryMisc.FieldList[a].NewValue <> qryMisc.FieldList[a].OldValue then ListBox2.Items.Add(qryMisc.FieldList[a].Origin + ' has changed'); end; Now I am trying to come up with the code to post the changed fields by extracting the table and field names from origin and build sql. |
Wed, Jul 17 2013 4:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Aaron
Origin must be something Tim introduced after my latest version. You may want to think about checking the tables for changed values as well. With optimistic locking someone else could have updated the table with "newer" information. Roy Lambert [Team Elevate] |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |