[DUG] Using Queries

[Stephen Barker]

Hi,

I haven't used the standard Delphi TQuery and TTable for many years, but I
do heavily use a third party product called DBISAM.

DBISAM has the behaviour you describe, and not surprising as it is fully
TDataset compatible. I'm not sure how its behaviour compares to the standard
TQuery, but it goes like this:

TDBISAMQuery has a boolean property called RequestLive. For safety it is
false by default. If set to true before the query is executed it instructs
the engine to attempt a live query, i.e. the result is effectively a direct
view into the underlying table data and can be edited as you observed.
However if it is false, the query result is a copy of the data - it can
still be edited but changes will be discarded after closing. This is very
useful for creating complex datasets from multiple tables, calculations,
lookups, aggregates etc that otherwise couldn't be done with straight SQL.
Usually needed for further exporting, reporting, API etc and no risk of
altering the source data.

Also, in the above there is another boolean property ResultIsLive, so you
can determine the state of the result set.

Actually I just checked and the standard TQuery does have a RequestLive
property also, so it was not just a DBISAM thing.
https://docwiki.embarcadero.com/Libraries/Sydney/en/Bde.DBTables.TQuery.Requ
estLive

cheers
Steve Barker

> -----Original Message-----
> From: Adrian Williams [mailto:adrian at aerosupport.co.nz] 
> Sent: Monday, 13 June 2022 10:05 a.m.
> To: NZ Borland Developers Group - Delphi List
> Subject: [DUG] Using Queries
> 
> Hi all
> 
> I wonder if someone could clarify something regarding Queries.
> 
> I understand that if you run a query on a table it returns a  
> subset or 
> snapshot of data from the table based on the SQL statement 
> supplied. (In 
> my Paradox days, a query created an 'ANSWER' table that could be 
> navigated as a table, but was separate from the original data).
> 
> What I have discovered (more by accident), is that if you 
> edit records 
> in the query data and call  the query's POST method it will post the 
> changes back to original table. Also by using the APPEND and INSERT 
> methods on the query, you can create new records in the base 
> table. What 
> I have been able to ascertain is that the database engine seamlessly 
> creates the SQL code to run UPDATE and INSERT statements to amend the 
> original table in the background.
> 
> My question is: Is this a 'proper' and acceptable way of updating a 
> table by modifying the Query data? Or is this fraught with danger and 
> bad practice?
> 
> Appreciate any comments on this.
> 
> Regards
> Adrian Williams
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at listserver.123.net.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to 
> delphi-request at listserver.123.net.nz with Subject: unsubscribe
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20220616/4b8106dc/attachment.html