ClientDataSet.ApplyUpdates() when JOIN is used

The ClientDataSet receives it's data via a query containing a JOIN.  The JOINed field is NOT modified by the application, however the ApplyUpdates does not apply the changes (which I can understand).

Is there a way that I can intercept the update/insert when ApplyUpdates is called and change the query/process so that the JOINed table is excluded?

I have read about the DeltaDS dataset, but I have not found any sample code that addresses my situation.

Here is the query that populates the ClientDataSet:

  BASE_JOIN_QUERY =
    'SELECT Cards.ID, Cards.Title, Topics.Topic, Cards.TopicID, Cards.Note, Cards.CreateDate ' +
    'FROM Cards ' +
    'LEFT OUTER JOIN Topics ON Topics.ID = Cards.TopicID ' +
    'ORDER BY Topics.Topic, Cards.Title';

The Topics table is NOT changed by the application; just the cards table.

Many thanks...

Todd
0
Todd
3/27/2010 3:37:21 PM
embarcadero.delphi.database 1294 articles. 0 followers. Follow

9 Replies
3362 Views

Similar Articles

[PageSpeed] 38

Create a TDataSetProvider.OnGetTableName event handler and
set the table name to Cards.

-- 
Bill Todd (TeamB)
0
Bill
3/27/2010 8:15:51 PM
The suggestion provided solved the the problem for Insert's, but not for Update's.
0
Todd
3/29/2010 1:21:33 AM
Todd Cary wrote:

> The suggestion provided solved the the problem for
> Insert's, but not for Update's.

Care to elucidate?

-- 
Bill Todd (TeamB)
0
Bill
3/29/2010 3:25:14 AM
> {quote:title=Bill Todd wrote:}{quote}
> Todd Cary wrote:
> 
> > The suggestion provided solved the the problem for
> > Insert's, but not for Update's.
> 
> Care to elucidate?
> 
> -- 
> Bill Todd (TeamB)

I am using SQLite and the Delphi Inspiration components, TDISQLite3Database and TDISQLite3UniDirQuery.  These are connected to a TDataSetProvider and TClientDataSet.  My application is a simple "Index Card" type utility to get a feel for SQLite and the TClientDataSet.  These lines of code are used in the application to update the SQLite DB when an Insert or Modify is performed:

  if (DM.ClientDataSet.State = dsEdit) or (DM.ClientDataSet.State = dsInsert) then
  begin
    DM.ClientDataSet.Post;
  end;

And the ClientDataSetAfterPost event has

  if DM.ClientDataSet.ChangeCount > 0 then
  begin
    DM.ClientDataSet.ApplyUpdates(-1);
  end;

There is

procedure TDM.DataSetProviderGetTableName(Sender: TObject;
  DataSet: TDataSet; var TableName: String);
begin
  ShowMessage(TableName);  // For debugging
  TableName := 'Cards';
end;

When the JOIN query is used (in a prior message above), the TableName is blank.

If the DM.ClientDataSet.Post is called for DM.ClientDataSet.State = dsInsert, the new record is inserted in the SQLite DB, however if DM.ClientDataSet.Post is called for DM.ClientDataSet.State = dsEdit, the update is not performed.

I may rewite the application so that just the code in question is present since all of this is a learning experience anyway.

Todd
0
Todd
3/29/2010 3:11:43 PM
Do you have an OnReconcileError event handler for the
TClientDataSet? If not, you must create one because an
error that occurs when you call ApplyUpdates does not raise
an exception. Instead it triggers an OnReconcileError
event. The place to start is with the ReconcileErrorDialog
in the object repository. See the comments at the beginning
of the unit.

My guess is that you do not have an OnReconcileError event
handler and that an error is occuring when you try to apply
an UPDATE.

-- 
Bill Todd (TeamB)
0
Bill
3/29/2010 11:24:04 PM
> {quote:title=Bill Todd wrote:}{quote}
> Do you have an OnReconcileError event handler for the
> TClientDataSet? If not, you must create one because an
> error that occurs when you call ApplyUpdates does not raise
> an exception. Instead it triggers an OnReconcileError
> event. The place to start is with the ReconcileErrorDialog
> in the object repository. See the comments at the beginning
> of the unit.
> 
> My guess is that you do not have an OnReconcileError event
> handler and that an error is occuring when you try to apply
> an UPDATE.
> 
> -- 
> Bill Todd (TeamB)

I do not have an OnReconcileError.  I'll add one and get back.  Many thanks.

Todd
0
Todd
3/30/2010 12:02:48 AM
> {quote:title=Todd Cary wrote:}{quote}
> > {quote:title=Bill Todd wrote:}{quote}
> > Do you have an OnReconcileError event handler for the
> > TClientDataSet? If not, you must create one because an
> > error that occurs when you call ApplyUpdates does not raise
> > an exception. Instead it triggers an OnReconcileError
> > event. The place to start is with the ReconcileErrorDialog
> > in the object repository. See the comments at the beginning
> > of the unit.
> > 
> > My guess is that you do not have an OnReconcileError event
> > handler and that an error is occuring when you try to apply
> > an UPDATE.
> > 
> > -- 
> > Bill Todd (TeamB)
> 
> I do not have an OnReconcileError.  I'll add one and get back.  Many thanks.
> 
> Todd

The OnReconcileError gives me for the E.Message, "Record not found or changed by another user".  I checked the OldValues and NewValues for each of the fields.  The primary key field (0) has a correct OldValue; the NewValue is empty.  I would assume this is correct since it is an update (no change in value of the key field).

The JOINed field, Topic, has an OldValue, however the NewValue is empty.  Again, I would assume this is correct since the Cards table has the reference value for the Topic (TopicID); not the actual value.

When I eliminate the JOIN with

BASE_QUERY = 'SELECT Cards.ID, Cards.Title, Cards.TopicID, Cards.Note, Cards.CreateDate ' +
                          'FROM Cards ' +
                          'ORDER BY Cards.Title';

All works fine.  My reasoning for using the JOIN is to eliminate some code to retrieve the value of the Topic independently.  Again, this is a learning app so I can get a better understanding of how one can use a JOIN (if one should).

Is there some good documentation on how the ClientDataSet handles a JOIN?

With appreciation...

Todd
0
Todd
3/30/2010 5:12:12 PM
Todd Cary wrote:

> Is there some good documentation on how the ClientDataSet
> handles a JOIN?

I am not sure what you mean since TClientDataSet does not
know anything about joins. It just holds whatever records
it receives from the TDataSetProvider in memory and sends
the changes back to the DSP when you call ApplyUpdates. The
DSP does not understand joins either so you have to create
an OnGetTableName event handler if you are only changing on
table or write a BeforeUpdateRecord event handler that
creates and executes the INSERT, UPDATE and DELETE
statements if you are changing multiple tables.

In your specific case, check the ProviderFlags property for
each field object of the source dataset and make sure the
only fields from the Cards table are flagged for inclusion.

-- 
Bill Todd (TeamB)
0
Bill
3/30/2010 7:09:41 PM
> {quote:title=Bill Todd wrote:}{quote}
> Todd Cary wrote:
> 
> > Is there some good documentation on how the ClientDataSet
> > handles a JOIN?
> 
> I am not sure what you mean since TClientDataSet does not
> know anything about joins. It just holds whatever records
> it receives from the TDataSetProvider in memory and sends
> the changes back to the DSP when you call ApplyUpdates. The
> DSP does not understand joins either so you have to create
> an OnGetTableName event handler if you are only changing on
> table or write a BeforeUpdateRecord event handler that
> creates and executes the INSERT, UPDATE and DELETE
> statements if you are changing multiple tables.
> 
> In your specific case, check the ProviderFlags property for
> each field object of the source dataset and make sure the
> only fields from the Cards table are flagged for inclusion.
> 
> -- 
> Bill Todd (TeamB)

Bill -

Thank you for your patience and helping get over this hurdle.  I now have the necessary code implemented that solves the problem!!  Once I found how to set ProviderFlag(s), it was easy.

procedure TDM.DataSetProviderUpdateData(Sender: TObject;
  DataSet: TCustomClientDataSet);
var
  FieldTopic: TField;
begin
  FieldTopic := DataSet.FieldByName('Topic');
  FieldTopic.ProviderFlags := [pfHidden];
end;
0
Todd
3/31/2010 2:32:03 PM
Reply:

Similar Artilces:

To use or not to use Delphi
Sadly, it seems to me that there is a sort of race between the two threads, for and against using Delphi in new projects, with more or less the same users posting in both threads. Arguments are fiercely debated in both camps. Borland had their own vision. As a community, now that Delphi has changed ownership I believe we should try to be more consistent, more clear, and more articulate in what we expect from Embercadero in terms of Delphi. We can contribute to keeping Delphi alive and moving in the right direction. "Laurent Cocea" schrieb: > Sadly, it seems to me that there ...

Copy a database in SQL Server using Delphi
Hello All, I am not sure if this is the best place for this question or if it should go in the ADO section. Anyway, I would like to make a copy of a SQL Server database to another database within the same Server using Delphi. For example, take a copy of the Production database and put it into a What_If database. I know that I could get the IT to do a backup of Production and a restore to What_If, but I would like to avoid this method and have it user controllable. I also do not want to copy the database table by table as I think this would be very slow and cumbersome, plus there are ab...

Help needed on XML database . How to use insert , update etc operations on XML file used as database
Hi !!! I am using an xml file as my database . But I can't  do insert , update and delete ops !!! Some one please help me ...... Pleaseeee help !!!! Its urgent .....     Maybe you need XPath, take a look at:http://www.w3schools.com/xpath/default.asp  Welcome to my SQL/ASPNET forum for Chinese http://51up.org/bbs/forumdisplay.php?fid=38...

Update database from datatable when using INNER JOIN in SelectCommand
 Hi EveryBody, I've searched the net and this forum for my particular problem but can't find my exact problem.I want to update the database from a datatable. The datatable is the result of a join on the database. I can't seem to use the dataadapter.update method because it gives me an error that a certain column doesn't exist. This is because when I use the INNER JOIN, the datatable consists of the 2 joined tables from the queryWhen I execute the query in SQLServer it just gives me one table.My question is how do I get rid of the second, joined, table so i...

Creating Database Files using Firedac (Delphi XE7)
I have been able to get Firedac to display and edit the following file types in a simple app using FDConnection, FDTable, FDPhysSQLiteDriverLink (and others according to file type) : sdb, gdb, mdb and xls (with ODBC link and drivers). I have been able to create a SQLLite File (.sdb) from scratch and add tables using roughly: FDTable1.Connection = FDConnection1; (set in form) FDConnection1.Params.Clear; FDConnection1.Params.Add('DriverID=SQLite'); FDConnection1.Params.Add('Database='+DATABASE_FILE); FDConnection1.ResourceOptions.DefaultParamType := ptOutput; ...

Database Workbench, where can I set a Database Restricted so only aliases can use the database?
Hello. Now I want a database to be not available unless approached by an alias. Can't find where I set this, in Database Workbench. :) Kai Inge > Now I want a database to be not available unless approached by an alias. > Can't find where I set this, in Database Workbench. This is a setting of your Firebird server installation. Check out the DatabaseAccess configuration parameter in firebird.conf. Set it to: DatabaseAccess = None -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManag...

Selecting Distinct Top 3 rows from Database using Join
Hi guys, Just trying to select a set of Articles from a SQL Server Database. The Articles all have a Category ID which is stored in another table (as an Article could be in more than one Category). I want to select the Top 3 Articles in a Category. At the moment I have as my SQL; "SELECT TOP 3 f.ArticleID, f.Heading, f.Summary, f.WrittenDate, f.ArticleURL FROM feedTable f LEFT JOIN Categories c ON f.ArticleID = c.ArticleID WHERE c.CategoryID=" + CategoryID + " AND c.ArticleID<>" + id + " ORDER BY c.CategoryID" Which seems to work to an extent in t...

Has anybody used OpenOffice Base's ODB as a database with Delphi?
How does it interface with Delphi? Is it similar to MDB? How good and reliable is Base's ODB? I do understand that Base itself can act as a front end to MySQL, and both are OpenSource with Sun support. > {quote:title=Anil Subbarao wrote:}{quote} > How does it interface with Delphi? Is it similar to MDB? How good and reliable is Base's ODB? I do understand that Base itself can act as a front end to MySQL, and both are OpenSource with Sun support. I think you could use an ODBC driver and then use ADO components unless they are direct components but I am not aware of them........

Using Delphi 7 and Delphi 2010
I had a project in Delphi 7. I worked on it in Delphi 2010. When I try to open the .dpr again in Delphi 7, the IDE toolbar widens across both monitors, and Delphi 7 locks up. It appears that Delphi 2010 modifies the .dpr in a way that makes Delphi 7 unable to open it again. All my forms & units appear to open fine in D7 after D2010, except for the warnings on the ExplicitHeight and ExplicitWidth properties being missing... I can easily recreate the .dpr, but is this expected behavior? Thanks. Tom Field wrote: > I had a project in Delphi 7. > > I worked o...

Using aspnet_regsql to use a local project database
Instead of the default ASPNETDB.MDF, I want to incorporate the Membership tables into an existing local database within my project. When asked for the Server and Database, the default server gives me a "Failed to query a list of database names from the SQL Server." error. When I set the Server to: .\SQLEXPRESS, my database doesn't appear to be in the list, however manually typing in the database name (without the .MDF extension) seems ok. When I run the ASP.NET configuration, I get an error "There is a problem with your selected data store... Could not find sto...

How design a COM using Delphi and Using in PB7?
I need some component, So, we want using DELPHI5 to develop a COM or Activex. What type of knowege or Skill I must know it. Can anyone tell us? from DRAOGN Can you explain what type of component you want to create? You may not need to a) build your own, or b) use Delphi. If the component should be used from within a PB application, you ought to use PB to develop it unless it is something that PB does not let you do. regards, Bill DRAGON wrote: > I need some component, So, we want using DELPHI5 to develop a COM or > Activex. > What type of knowege or Skill I ...

using ProviderUserKey as an ownerid in database tables? or what should i use?
hey, what should I use from the membership classes as an ownerid in database tables? for example if I am writing a user forum and I want to have a table that lists posts how do I connect the user with their posts with a foreign key? There is no customer unique identifier apart from a guid or email address which could slow my database down id rather use an int.....  Peter Heard Check this out. http://www.asp.net/learn/security/tutorial-08-vb.aspx...

Joining Two Tables using Join class
Background: At the moment SQL queries are reading from SQL.XML file. I think this is not good practise / standard.  I think it is possible to have a class with methods say 'Join' class. Method can take parameters such as (TableName, PK etc) and join two Tables and return the SQL statement dynamatically. To enhance further the functionality same method may be over ridden/overloaded with more number of parameters to join above 2 Tables (if possible). I like to request for a code snippet or reference sites/urls. your input is very much appreciated.  I believe what yo...

Make DBValid safe to use while database in use
It would be great if databases could be reliably validated when in use (I always thought they could until we had some issues recently and I see that it is now documented that they can't). The alternative of creating complex batch jobs to start a backed-up copy of the database in read only mode and then validate it is far more susceptible to error. Justin Willey From the ASA 9.0.1 documentation for the Validation Utility Caution Validating a table or an entire database should be performed while no connections are making changes to the database; otherwise, spurious err...

Web resources about - ClientDataSet.ApplyUpdates() when JOIN is used - embarcadero.delphi.database

Determining the file format from the data
AWare Systems TechTalk 001 Determining the file format from the data > I have a either a .bmp, .jpg, and .gif file in a ClientDataset's Blob ...

FireMonkey Q&A
Questions and answers from a FireMonkey webinar

Resources last updated: 2/5/2016 11:12:41 AM