Update Read only SQL queries

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!

Diego
DiegoCAsked:
Who is Participating?
 
TheRealLokiConnect With a Mentor Senior DeveloperCommented:
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'
      'set'
      '  CUST_NO = :CUST_NO,'
      '  CUSTOMER = :CUSTOMER,'
      '  CONTACT_FIRST = :CONTACT_FIRST,'
      '  CONTACT_LAST = :CONTACT_LAST,'
      '  PHONE_NO = :PHONE_NO,'
      '  ADDRESS_LINE1 = :ADDRESS_LINE1,'
      '  ADDRESS_LINE2 = :ADDRESS_LINE2,'
      '  CITY = :CITY,'
      '  STATE_PROVINCE = :STATE_PROVINCE,'

      '  COUNTRY = :COUNTRY,'
      '  POSTAL_CODE = :POSTAL_CODE,'
      '  ON_HOLD = :ON_HOLD'
      'where'
      '  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)
0
 
DiegoCAuthor Commented:
I didn't know about UpdateSQL object. It is not exactly what I was looking for but it helps me. Thanks!

Diego
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.