<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2448.0">
<TITLE>RE: [DUG] Using Queries</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>Hi,</FONT>
</P>
<P><FONT SIZE=2>I haven't used the standard Delphi TQuery and TTable for many years, but I do heavily use a third party product called DBISAM.</FONT></P>
<P><FONT SIZE=2>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:</FONT></P>
<P><FONT SIZE=2>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.</FONT></P>
<P><FONT SIZE=2>Also, in the above there is another boolean property ResultIsLive, so you can determine the state of the result set.</FONT>
</P>
<P><FONT SIZE=2>Actually I just checked and the standard TQuery does have a RequestLive property also, so it was not just a DBISAM thing.</FONT></P>
<P><FONT SIZE=2><A HREF="https://docwiki.embarcadero.com/Libraries/Sydney/en/Bde.DBTables.TQuery.RequestLive" TARGET="_blank">https://docwiki.embarcadero.com/Libraries/Sydney/en/Bde.DBTables.TQuery.RequestLive</A></FONT>
</P>
<P><FONT SIZE=2>cheers</FONT>
<BR><FONT SIZE=2>Steve Barker</FONT>
</P>
<P><FONT SIZE=2>> -----Original Message-----</FONT>
<BR><FONT SIZE=2>> From: Adrian Williams [<A HREF="mailto:adrian@aerosupport.co.nz">mailto:adrian@aerosupport.co.nz</A>] </FONT>
<BR><FONT SIZE=2>> Sent: Monday, 13 June 2022 10:05 a.m.</FONT>
<BR><FONT SIZE=2>> To: NZ Borland Developers Group - Delphi List</FONT>
<BR><FONT SIZE=2>> Subject: [DUG] Using Queries</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Hi all</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> I wonder if someone could clarify something regarding Queries.</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> I understand that if you run a query on a table it returns a </FONT>
<BR><FONT SIZE=2>> subset or </FONT>
<BR><FONT SIZE=2>> snapshot of data from the table based on the SQL statement </FONT>
<BR><FONT SIZE=2>> supplied. (In </FONT>
<BR><FONT SIZE=2>> my Paradox days, a query created an 'ANSWER' table that could be </FONT>
<BR><FONT SIZE=2>> navigated as a table, but was separate from the original data).</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> What I have discovered (more by accident), is that if you </FONT>
<BR><FONT SIZE=2>> edit records </FONT>
<BR><FONT SIZE=2>> in the query data and call the query's POST method it will post the </FONT>
<BR><FONT SIZE=2>> changes back to original table. Also by using the APPEND and INSERT </FONT>
<BR><FONT SIZE=2>> methods on the query, you can create new records in the base </FONT>
<BR><FONT SIZE=2>> table. What </FONT>
<BR><FONT SIZE=2>> I have been able to ascertain is that the database engine seamlessly </FONT>
<BR><FONT SIZE=2>> creates the SQL code to run UPDATE and INSERT statements to amend the </FONT>
<BR><FONT SIZE=2>> original table in the background.</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> My question is: Is this a 'proper' and acceptable way of updating a </FONT>
<BR><FONT SIZE=2>> table by modifying the Query data? Or is this fraught with danger and </FONT>
<BR><FONT SIZE=2>> bad practice?</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Appreciate any comments on this.</FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>> Regards</FONT>
<BR><FONT SIZE=2>> Adrian Williams</FONT>
<BR><FONT SIZE=2>> _______________________________________________</FONT>
<BR><FONT SIZE=2>> NZ Borland Developers Group - Delphi mailing list</FONT>
<BR><FONT SIZE=2>> Post: delphi@listserver.123.net.nz</FONT>
<BR><FONT SIZE=2>> Admin: <A HREF="http://delphi.org.nz/mailman/listinfo/delphi" TARGET="_blank">http://delphi.org.nz/mailman/listinfo/delphi</A></FONT>
<BR><FONT SIZE=2>> Unsubscribe: send an email to </FONT>
<BR><FONT SIZE=2>> delphi-request@listserver.123.net.nz with Subject: unsubscribe</FONT>
<BR><FONT SIZE=2>> </FONT>
</P>
</BODY>
</HTML><img height="1" width="1" alt="" border="0" src="http://zjwgp.tsmtpclick.com/tracking/qaR9ZGL0BQH5AmN3AQH4Zwp4ZGx1Zvj.gif">