Solved

TUpdateSQL

Posted on 1998-02-17
15
1,371 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now