[DUG] Using Queries

[Paul A. Norman]

Hi all,

This is really worth a more fuller understanding – the actual lifecycle of Delphi objects in db activity.

So aside from multi usage requiring "locking", let's take a single user scenario as discussed by Gary, this then is essentially an entirely internal Delphi thing, in that the db engine is essentially agnostic as to where it is getting it's properly credentialed instructions from?

Though a local Sqlite type of thing would be more tightly Delphi integrated? - so may raise other issues?

But more generally,  if the query object is essentially an internal Delphi object, is there any (private) referencing/reference counting on it which would disqualify it from "safe" reuse, or is it essentially in a neutral "state" agnostic to whether it's being recycled?

Does the completion of a db engine transaction set a flag in the Delphi query object or in ram with Delphi?
— If not (and there being no other considerations) then it would appear to be entirely recyclible? 
In that Delphi is unaware to its previous usage at that point? — it's essentially no different to a freshly instanced, now inactive, and so far equivalently unused query object?

If there is a flag in a private property, might require enquiry into component and class source code if any one has access to it?

Regards,
Paul
-- 
https://PaulANorman.info

On 13 June 2022 10:55:50 am NZST, Tech Benner <tech at benner.nz> wrote:
>HI Adrian,
>
>I may be wrong for it is many moons ago that I taught this, but under 
>the hood TTABLE and TQUERY access the database(s) in the same way. And 
>so editing the record received from either method of database access, 
>and then posting back is identical from an academic point of view.
>
>In working with client server databases with multiple users - if that
>is 
>how you are using it in your system - you come across record locking 
>issues, and in that case going into EDIT mode may block access to other
>
>users, especially if the UI keeps blocks on the data for some time.
>
>My preference is to develop separate UPDATE queries that modify data. 
>However this must be designed with reference to how user(s) are to be 
>accessing and updating (modifying) the data, and the chances of 
>overwriting data.
>
>If there is only one user, or users are not likely to be editing the 
>same record at one time, then there is little to worry about. If there 
>are to be multiple users accessing the same record, potentially at the 
>same time, then you need to look at record locking, and develop the UI 
>to take this into account.
>
>HTH
>
>Gary
>
>
>
>
>On 13/06/2022 10:37 am, Bevan Edwards wrote:
>> Hi Adrian,
>>
>> I have used TQuery objects to read data from databases (sometimes
>> complex, sometimes simple), and then other TQuery objects to perform
>> update/insert/delete operations.
>>
>> The fact that you "can" perform update/insert/delete on a (presumably
>> simple) TQuery seems like a side-effect of how it is implemented,
>rather
>> than an acceptable/intended approach.
>>
>> In my view, a SELECT query is for read-only purposes and should not
>then
>> be used to directly update the database.
>> If you wanted to update the database like that, you would use a
>TTable
>> rather than a TQuery.
>> Again, that's probably better suited to file-based databases (like
>> Paradox and MS Access) rather than SQL-based databases.
>>
>> That's my two cents worth anyway.
>>
>> Regards,
>>
>> Bevan
>>
>>
>> On 13/06/2022 10:05 am, Adrian Williams wrote:
>>> 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
>> _______________________________________________
>> 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
>
>
>_______________________________________________
>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/20220613/140f6fc3/attachment.html