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.

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.
>

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]

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.

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]

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.

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.   

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]