• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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
0
DiegoC
Asked:
DiegoC
1 Solution
 
TheRealLokiSenior 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now