Custom SQL-handling in dbExpress 1

[MagicFrisbee]

The Delphi help talks about the possibility of overriding the resulting SQL statments that TDataSetProviders use when resolving changes to the back-end database.

So, what I have is TSQLDataSet that is a join between two tables. HOWEVER, the query that specifies the join ALSO has a parameter in it and is connected to a master TSQLDataSet via the DataSource property (so the 2-table join is a "child" of a dataset that represents records from 1 table). Two TClientDataSets represent the master and the detail (the detail via the DataSetField propety).

I would like my users to be able to insert, update, and delete records in the "child/join" ClientDataSet, and when ApplyUpdates is called, I'd like to provide SQL statements so the provider/resolver knows what to do. Perhaps I handle the BeforeUpdateRecord event, but how? What do I write?

Thanks ahead of time, experts!


GIS Programmer
City of Orem, UT

http://gis.orem.org

 

[towerbase]

Yes, I think you need to write some code in the BeforeUpdateRecord handler.

However, I am confused by the structure of your database. Can you describe a very simple version of your database (using SQL CREATE statements). Ideally, only two fields per table (e.g. an integer key and a varchar to hold data) and the SQL for your JOIN?

Andrew
Hampshire, UK

 

[MagicFrisbee]

I did a bunch of dbExpress tests on Friday in a new, separate application built just for that purpose. I was able to find out that if you have an SQL statement that joins more than one table together, the resolver can only automatically apply updates to 1 of those tables via the OnGetTableName event. Without handling that event, it parses and picks up the first table name it finds in the CommandText statement (or SQL statement).

Handling updates via the OnUpdateRecord is also a good strategy, but I had misunderstood the help. I thought that if you wanted to do custom SQL processing, you had to set some property of the provider or SQLDataSet to get it done, but that is not the case. Rather, you can send multiple "side-effect" SQL statements either through other components in the data module, or through the TSQLConnection. My point is that custom SQL handling isn't done through the components involved, but through other components that act as auxiliary helpers.

GIS Programmer
City of Orem, UT

http://gis.orem.org

 

[towerbase]

Custom SQL handling can be done through the DataSetProvider component but I agree that the documentation is not very good on this topic.

This simple example might help.

Suppose we have two tables a and b. Each table consists of an integer id and and a varchar string item. They were created by

CREATE TABLE a ( ida int primary key auto_increment, itema VARCHAR(8) )
CREATE TABLE b ( idb int primary key auto_increment, itemb VARCHAR(8) )

The tables are populated with a few records with statements such as

INSERT INTO a VALUES( 1, "A1" )
INSERT INTO a VALUES( 2, "A2" )
INSERT INTO b VALUES( 1, "B1" )
INSERT INTO b VALUES( 2, "B2" )

The tables are JOINed using an SQLDataset with SQL like

SELECT ida
     , idb
     , itema
     , itemb
FROM a
JOIN b ON ida=idb

A DataSetProvider, DSP, links the SQLDataset with a ClientDataSet which enables the ClientDataSet to be visible and editable on a DBGrid (via a DataSource).

We can write a BeforeUpdateRecord handler for DSP to actually apply the updates to the underlying tables, a and b. This BeforeUpdateRecord handler should look something like:

procedure TForm1.DSPBeforeUpdateRecord(Sender: TObject; SourceDS: TDataSet;
  DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind; var Applied: Boolean);
var
  sql: string;
  connection: TSQLConnection;
  temp: string;
  id: integer;
begin
  connection := (SourceDS as TCustomSQLDataset).SQLConnection;
  case UpdateKind of
    ukModify:
      with DeltaDS do begin
        if not VarIsEmpty( FieldByName('itema').NewValue) then begin
          temp := FieldByName('itema').NewValue;
          id := FieldByName('ida').OldValue;
          sql := Format( 'UPDATE a SET itema="%s" WHERE ida=%d', [ temp, id ] );
          Connection.Execute( sql, nil, nil);
        end;
        if not VarIsEmpty( FieldByName('itemb').NewValue) then begin
          temp := FieldByName('itemb').NewValue;
          id := FieldByName('idb').OldValue;
          sql := Format( 'UPDATE b SET itemb="%s" WHERE idb=%d', [ temp, id ] );
          Connection.Execute( sql, nil, nil);
        end;
      end;
    ukInsert: ;
    ukDelete: ;
  end;
  Applied := true;
end;

I have left out the code to handle inserts and deletes but hopefully the above simple example shows how updates can be applied to the underlying tables using the DataSetProvider component.





Andrew
Hampshire, UK

 

[MagicFrisbee]

Thank you for showing me how to use DeltaDS, by the way.

GIS Programmer
City of Orem, UT

http://gis.orem.org