Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

How can I make a TQuery with a join editable?

Using Delphi 4, Oracle 8, BDE Woll2Woll components...
How can I make a TQuery with a join editable? I tried using lookups and calculated fields, the problem is that one of the tables will have over 3,000,000 records, so doing client side stuff like lookups and calc fields takes way, way too long.  I've scoured the newsgroups and found a few clues how to do this (cached updates?) but I want to think this through before I begin, and I really do need a few details.  Thanks for any help,

PC.
0
posconsultant
Asked:
posconsultant
1 Solution
 
kretzschmarCommented:
why not use editable view on oracle side for the join ?
0
 
bozo7Commented:
I have used a TQuery before set the RequestLive property to true.
It will be really picky about the sql when you do this. I had to use the query editor to create the sql and paste it into the query object how I wanted it.

It worked great though.

bozo
0
 
ellessarCommented:
Have you thought of using parameters or of creating the sql you want to on the fly??


eg: with a tquery you can write the following in the sql editor:

select * from clients where custid = :ID order by lastdate

Then you set the datatype of the parameter in the parameters section. You can at this point also link the query to a previous query, and create a link on the field name of the linked/parent dataset. The parameter name must be the same as a field name for this to work. I have found that it works wonderfully for reports.

Anyway, when you want to make dynamic changes to the statement within the defined parameter above, you just modify the parameter value to change your criteria. You can also edit the sql by using the sql property of the tquery, but this will require that the query be closed and then opened.

Another option is to filter your data or use selectrange on the current query to narrow your search down afterwards, but this defeats the purpose of using SQL and queries.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ellessarCommented:
Have you thought of using parameters or of creating the sql you want to on the fly??


eg: with a tquery you can write the following in the sql editor:

select * from clients where custid = :ID order by lastdate

Then you set the datatype of the parameter in the parameters section. You can at this point also link the query to a previous query, and create a link on the field name of the linked/parent dataset. The parameter name must be the same as a field name for this to work. I have found that it works wonderfully for reports.

Anyway, when you want to make dynamic changes to the statement within the defined parameter above, you just modify the parameter value to change your criteria. You can also edit the sql by using the sql property of the tquery, but this will require that the query be closed and then opened.

Another option is to filter your data or use selectrange on the current query to narrow your search down afterwards, but this defeats the purpose of using SQL and queries.
0
 
ellessarCommented:
Sorry about the double submission, I received an error from our firewall here and was not sure if the comment had been posted.
0
 
kretzschmarCommented:
back again,

you've never said, how your joined query looks like.

well, the cached-update stuff, how to do:

- drop your wwquery1 on your form and enter your sql-statement
- set cachedupdates to true

- drop a tupdatesql-component on your form (UpdateSQL1)

- in your query select the UpdateSQL1 as UpdateObject (property)

- set your query active

- doubleclick the UpdateSQL1
(keep in mind, that you can only updates,inserts,deletes perform only for one table)

- select the keyfield(s) and Fields you will be change

- select generate sql

to perform changes use some code like this (assign this also to the afterdelete-event):

procedure TForm1.wwQuery1AfterPost(DataSet: TDataSet);
begin
  try
    wwquery1.ApplyUpdates;
  except
    //here can also coded a analyses, why the changes aren't posted
    wwquery1.CancelUpdates;
  end;
  wwquery1.CommitUpdates;
end;

well, for the other parts of the joien query you can placed several queries with predefined update-insert statements like the generated in the UpdateSQL (with parameters), which is connected via a datasource to your joined wwquery1 and you can fire it also in the afterpost-event like

procedure TForm1.wwQuery1AfterPost(DataSet: TDataSet);
begin
  try
    wwquery1.ApplyUpdates;
    if DataSet.State = dsInsert then
      wwQueryInsert.Execute;
    if DataSet.State = dsEdit then
      wwQueryUpdate.Execute;
  except
    //here can also coded a analyses, why the changes aren't posted
    wwquery1.CancelUpdates;
  end;
  wwquery1.CommitUpdates;
end;

well thats all, but last not least,
if you want higher-performance then use
the doa-components instead of the ww-access controls.

info and trial at:
http://www.allroundautomations.nl/doa.html

also keep in mind that oracle is able to create editable views (just include the primary keys into the view). that's the choice, how i would do it, because the real hard work does the server.

meikl
0
 
posconsultantAuthor Commented:
Is that your final answer?...:-) Thanks for posting code sample, I really appreciate this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now