Update Read only SQL queries

Posted on 2005-04-19
Last Modified: 2010-04-05
I need practical soution to update read only sql queries. I usually fill DBgrids with multiple table queries that yields read only datasets. I want that the grid work as with a live query, edit and press enter and the table is updated.

The solution may be a function called by a dbgrid event (i.e. keypress), a field event or a dataset event. But I want it elegant, I mean, the less paramaters passed, the better. Doesn't matter if the update replace all the table record with unmodified fields, not only the changed one.

The related tables are mostly lookup tables, so if the function updates the id of the lookup record in the target table is OK. I don't need to mix updatable data from several tables, just one main table and the related lookup ones.

I always put an unique ID on the updated record, it can be used as a parameter to locate the record and make the changes in a second recordset.

I can develop this myself, but perhaps someone already has a nice solution.

It must be DB engine independent. I will adapt the proposed SQL if needed, or all the standard TTable or TQuery methods can be used.

I hope it is clear, thanks a lot!

Question by:DiegoC
    LVL 13

    Expert Comment

    LVL 17

    Accepted Solution

    Both the TTable and TQuery have an UpdateObject property specifically for this purpose
    You can make the handling exactly as a normal TTable, seemeless to the user.
    Drop a TUpdateSQL component on the form and set the UpdateObject property to the TUpdateSQL

    an example of a typical modifysql would be
    ModifySQL.Strings = (
          'update customer'
          '  CUST_NO = :CUST_NO,'
          '  CUSTOMER = :CUSTOMER,'
          '  PHONE_NO = :PHONE_NO,'
          '  ADDRESS_LINE1 = :ADDRESS_LINE1,'
          '  ADDRESS_LINE2 = :ADDRESS_LINE2,'
          '  CITY = :CITY,'

          '  COUNTRY = :COUNTRY,'
          '  POSTAL_CODE = :POSTAL_CODE,'
          '  ON_HOLD = :ON_HOLD'
          '  CUST_NO = :OLD_CUST_NO')

    the ":OLD_" is a special case and should be used for the key fields

    Delphi has some examples of this (hightlight the TUpdateSQL and press F1)

    Author Comment

    I didn't know about UpdateSQL object. It is not exactly what I was looking for but it helps me. Thanks!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
    Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    758 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

    13 Experts available now in Live!

    Get 1:1 Help Now