[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1393
  • Last Modified:

TUpdateSQL

I don't get it :

I have a query with 3 joined tables.
I want to let the user be able of modifying 3 fields of one of these tables. (Use, Checked, Remarks)
To make my query 'live', I use a TupDateQuery  with the following modifying sql :

update qcmDaily
set
  RemarkSite = :RemarkSite,
  Use = :Use,
  Checked = :Checked
where
  qcmSite = :OLD_qcmSite and
  Measure_Date = :OLD_Measure_Date and
  Measure_Time = :OLD_Measure_Time

I set the UpdateObject of the query to my update object.
I then make my table active and it still says that my table is read-only!!!

Can somebody help me with this one?

Regards, Zif

Forgot to mention, I use an Access database with ODBC driver
0
ZifNab
Asked:
ZifNab
  • 7
  • 5
  • 2
  • +1
1 Solution
 
ZifNabAuthor Commented:
Edited text of question
0
 
ZifNabAuthor Commented:
Heeeeeeeeeeeelpaaaaaaaaaaaaaaaargggggg ...
0
 
mvz121697Commented:
I assume you have set the Query.CachedUpdates to true ?
0
Technology Partners: 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!

 
MatveyCommented:
Have you done it before? (did it work?)
It might be a driver problem...

nevemind, just a wild quess... :)
-Matvey
0
 
ZifNabAuthor Commented:
Hi mvz, yes i've done that too. But is it always nesescarry? CachedUpdates true for a live result?

Hi Matvey, I've never done it with an ODBC driver for access.
0
 
mvz121697Commented:
yes, that is the principe of TupdateSQL.
You connect it to a read-only recordset, that record-set remains readonly. Only when Delphi wants to update, it does it not through the dataset itself, but through a seperate SQWL-command (Tupdate-sql).
I thought that using TupdateSQl's means you MUST set Cachedupdates to TRUE.

Greetings,
MvZ

0
 
ZifNabAuthor Commented:
mvz, that's where i'm getting confused. When you use CachedUpdates, where do you have to say when he has to update?
And how?
I also thought that if you want to wait for updating you've to use Chacedupdates. I find the explanation of delphi here very confusing.

Have you used TUpdateSql before? Is my code correct. I mean, if I only want to change those 3 fields, do I only have to declare these 3 fields? It's the first time I try to use these components.

Regards,
Zif.
0
 
bijoynCommented:
ZifNab,

Hi,

Ok, One thing is that If u want ot use TUpdateSQL then the dataset that u are attaching it to should have its CachedUpdates to true. This is because Delphi creates a temporary table of the result set that the query fetches and this is not connected to the actual data that u have accessed and hence it allows u to update the join query. Whenever such a query is changed, u can trap the change in any of the events given in the TQuery component. I will provide u with an example  of what i did looking at the problem that u are facing. Remember that TUpdateSQL cannot be used without cached updates.

------------------------

This is my join query -

SELECT OBJ_COMPS.COMPONENTNAME, OBJ_COMPPROPS.PROPERTYNAME, OBJ_COMPMETHS.METHODNAME
FROM    OBJ_COMPS, OBJ_COMPPROPS, OBJ_COMPMETHS
WHERE OBJ_COMPS.COMPONENTNAME = OBJ_COMPPROPS.COMPONENTNAME
AND       OBJ_COMPS.COMPONENTNAME = OBJ_COMPMETHS.COMPONENTNAME

The UpdateObject of this query has been set to  UpdateSQL1 and the cachedUpdates property of the query has been set to True.

As an example i have written the following code to update one of the tables whenever the dataset is edited. -

UPDATE OBJ_COMPMETHS
SET         METHODDESCRIPTION = 'Update'
WHERE COMPONENTNAME = 'TDefaultDBEdit'
AND       METHODNAME = 'AddClick'

U can specify the current values for the where clause by using the OLD_FieldName clause with a ':' in front of it. I have just used a sample code to make the understanding easier.

Now on my form i dropped a button on the Click event of the button i have written the following code -

   Query1.Edit;
   Query1.ApplyUpdates;
   Query1.CommitUpdates;  

Now the first statement will put the dataset in edit mode, otherwise the other two statements will not make any difference to the database. The second statement will do the posting of the dataset. While this is being done, it will use the UpdateSQL1 object ot see what needs to be executed. It will find our Update statement and will execute it. The last statement will flush the cache created for the dataset. Clearing the cache means flushing the temporary tables created for the cachedUpdate dataset. Try this and tell me if your problem gets solved.

Bijoy
0
 
bijoynCommented:
ZifNab,

Hi,

Ok, One thing is that If u want ot use TUpdateSQL then the dataset that u are attaching it to should have its CachedUpdates to true. This is because Delphi creates a temporary table of the result set that the query fetches and this is not connected to the actual data that u have accessed and hence it allows u to update the join query. Whenever such a query is changed, u can trap the change in any of the events given in the TQuery component. I will provide u with an example  of what i did looking at the problem that u are facing. Remember that TUpdateSQL cannot be used without cached updates.

------------------------

This is my join query -

SELECT OBJ_COMPS.COMPONENTNAME, OBJ_COMPPROPS.PROPERTYNAME, OBJ_COMPMETHS.METHODNAME
FROM    OBJ_COMPS, OBJ_COMPPROPS, OBJ_COMPMETHS
WHERE OBJ_COMPS.COMPONENTNAME = OBJ_COMPPROPS.COMPONENTNAME
AND       OBJ_COMPS.COMPONENTNAME = OBJ_COMPMETHS.COMPONENTNAME

The UpdateObject of this query has been set to  UpdateSQL1 and the cachedUpdates property of the query has been set to True.

As an example i have written the following code to update one of the tables whenever the dataset is edited. -

UPDATE OBJ_COMPMETHS
SET         METHODDESCRIPTION = 'Update'
WHERE COMPONENTNAME = 'TDefaultDBEdit'
AND       METHODNAME = 'AddClick'

U can specify the current values for the where clause by using the OLD_FieldName clause with a ':' in front of it. I have just used a sample code to make the understanding easier.

Now on my form i dropped a button on the Click event of the button i have written the following code -

   Query1.Edit;
   Query1.ApplyUpdates;
   Query1.CommitUpdates;  

Now the first statement will put the dataset in edit mode, otherwise the other two statements will not make any difference to the database. The second statement will do the posting of the dataset. While this is being done, it will use the UpdateSQL1 object ot see what needs to be executed. It will find our Update statement and will execute it. The last statement will flush the cache created for the dataset. Clearing the cache means flushing the temporary tables created for the cachedUpdate dataset. Try this and tell me if your problem gets solved.

Bijoy
0
 
ZifNabAuthor Commented:
Ok Bijoy, thanks for the explenation, I'm going to try this out.

Isn't there a way to update the joined table without the need of letting the user push a button?

Thanks. zif.
0
 
bijoynCommented:
Hey ZifNab,

The button was to make the understanding simple. U can do the same on any event of the query component in which u have specified the join statement.

Cheers,

Bijoy.
0
 
ZifNabAuthor Commented:
Hi Bijoy, this works perfectly! Post this as an answer, please.

Still few questions :

1. Joined tables will always be queries, so always SQLupdates? True or False
2. Does it make things slower if you call in certain events the applyupdates?
3. You seem to know a lot of DB, can you look also at this question?
http://www.experts-exchange.com/topics/comp/lang/delphi/Q.10038365

 So we can get another opinion? Or other solutions to the problem.

Thanks, already .
Zif.

0
 
bijoynCommented:
Hey Zif,

U are welcome man. Well I have not understood your first question. Well ApplyUpdates are supposed to make things faster for u, especially in case of queries that do not involve joins. In the queries that involve joins its always beter to refresh the queries after the ApplyUpdates. This will solve the problem of slowing down. Also remember that the Applyupdates are supposed to be faster because they commit to the database a batch of records at one time instead of doing it every time for each record that is updated.

I have being working on database for nearly 8 years now. I started working on Delphi only 2 years back. I will certainly lok into the question that u are referring.

Cheers,

Bijoy

0
 
ZifNabAuthor Commented:
Thanks Bijoy!!

For

 1. To there exist joined tables which are editable whitout the needed applyupdates?

And thanks for looking into my other question too.

Regards, Zif.
0
 
bijoynCommented:
Zif,

If u are asking that do u need to make the cachedupdates property to true for every query with a join then the answer is 'No'. U have to set this property only if u intend to edit it. The requirement of ApplyUpdates is because only because if u turn on the cachedupdated property to true then the changes will not be reflected if u do not do a ApplyUpdates. It is always better to do ApplyUpdates on a DatabaseComponent for e.g. Database1.ApplyUpdates[dataset1, dataset2]. The database.ApplyUpdates will always start a transaction, post the datasets specified and in the orderht u have specified in the list. It will commit only if all the datasets are posted without an error. In case an erro is encountered while posting any one of the datasets in the list then Delphi will tell u which dataset has failed and the whole transaction is rolled back. This is useful especially in tables which are of master-detail types.

Cheers,

Bijoy.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now