[DUG] Using Queries

[Tech Benner]

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