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
LVL 8
ZifNabAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZifNabAuthor Commented:
Edited text of question
0
ZifNabAuthor Commented:
Heeeeeeeeeeeelpaaaaaaaaaaaaaaaargggggg ...
0
mvz121697Commented:
I assume you have set the Query.CachedUpdates to true ?
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.