Solved

How can I make a TQuery with a join editable?

Posted on 2000-02-28
7
360 Views
Last Modified: 2010-04-04
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
Comment
Question by:posconsultant
7 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2566373
why not use editable view on oracle side for the join ?
0
 
LVL 1

Expert Comment

by:bozo7
ID: 2567582
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
 

Expert Comment

by:ellessar
ID: 2567749
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Expert Comment

by:ellessar
ID: 2567765
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
 

Expert Comment

by:ellessar
ID: 2567766
Sorry about the double submission, I received an error from our firewall here and was not sure if the comment had been posted.
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 300 total points
ID: 2567798
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
 

Author Comment

by:posconsultant
ID: 2573643
Is that your final answer?...:-) Thanks for posting code sample, I really appreciate this.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now