Solved

TUpdateSQL

Posted on 1998-02-17
15
1,380 Views
Last Modified: 2012-08-13
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
Comment
Question by:ZifNab
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 8

Author Comment

by:ZifNab
ID: 1359394
Edited text of question
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1359395
Heeeeeeeeeeeelpaaaaaaaaaaaaaaaargggggg ...
0
 
LVL 2

Expert Comment

by:mvz121697
ID: 1359396
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!

 
LVL 3

Expert Comment

by:Matvey
ID: 1359397
Have you done it before? (did it work?)
It might be a driver problem...

nevemind, just a wild quess... :)
-Matvey
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1359398
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
 
LVL 2

Expert Comment

by:mvz121697
ID: 1359399
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
 
LVL 8

Author Comment

by:ZifNab
ID: 1359400
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
 

Expert Comment

by:bijoyn
ID: 1359401
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
 

Expert Comment

by:bijoyn
ID: 1359402
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
 
LVL 8

Author Comment

by:ZifNab
ID: 1359403
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
 

Expert Comment

by:bijoyn
ID: 1359404
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
 
LVL 8

Author Comment

by:ZifNab
ID: 1359405
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
 

Accepted Solution

by:
bijoyn earned 50 total points
ID: 1359406
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
 
LVL 8

Author Comment

by:ZifNab
ID: 1359407
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
 

Expert Comment

by:bijoyn
ID: 1359408
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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

691 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