1

I'm working on a data conversion utility which can push data from one master database out to a number of different databases. The utility its self will have no knowledge of how data is kept in the destination (table structure), but I would like to provide writing a SQL statement to return data from the destination using a complex SQL query with multiple join statements. As long as the data is in a standardized format that the utility can recognize (field names) in an ADO query.

What I would like to do is then modify the live data in this ADO Query. However, since there are multiple join statements, I'm not sure if it's possible to do this. I know at least with BDE (I've never used BDE), it was very strict and you had to return all fields (*) and such. ADO I know is more flexible, but I don't know quite how flexible in this case.

Is it supposed to be possible to modify data in a TADOQuery in this manner, when the results include fields from different tables? And even if so, suppose I want to append a new record to the end (TADOQuery.Append). Would it append to two different tables?

The actual primary table I'm selecting from has a complimentary table which is joined by the same primary key field, one is a "Small" table (brief info) and the other is a "Detail" table (more info for each record in Small table). So, a typical statement would include something like this:

select ts.record_uid, ts.SomeField, td.SomeOtherField from table_small ts
join table_detail td on td.record_uid = ts.record_uid

There are also a number of other joins to records in other tables, but I'm not worried about appending to those ones. I'm only worried about appending to the "Small" and "Detail" tables - at the same time.

Is such a thing possible in an ADO Query? I'm willing to tweak and modify the SQL statement in any way necessary to make this possible. I have a bad feeling though that it's not possible.

Compatibility:

  1. SQL Server 2000 through 2008 R2
  2. Delphi XE2
Jerry Dodge
  • 26,858
  • 31
  • 155
  • 327
  • 2
    You could write the whole thing as a SQL stored procedure and then execute this from your Delphi program via ADO. – Keith Miller Dec 28 '12 at 21:23
  • It appears that it is possible to a certain extent, using my two tables which match up together anyway. When I introduce other lookup tables, it starts getting messy and it doesn't let me set those values. Still trying to figure out to what extent I can modify this data. – Jerry Dodge Dec 28 '12 at 21:57
  • Then there's also the question of ensuring the primary key fields (identity specification) are perfectly matched up. I would have to make sure I append to both tables if I decide to write to either. – Jerry Dodge Dec 28 '12 at 21:59
  • @Jerry Dodge: Your comment about the BDE is false: one had perfect control over which fields were returned. You might have been thinking about a 'live query': one can't edit a query which is based on data drawn from more than one table. – No'am Newman Dec 29 '12 at 05:46
  • I've never really used BDE as I mentioned, that's just what I've heard. I'm just using ADO. – Jerry Dodge Dec 29 '12 at 10:14
  • Another option is to not use datasets at all and break up into CRUD statements. If you can use an ORM you wont have this problem at all... – whosrdaddy Dec 29 '12 at 10:17

3 Answers3

6

Editing these Fields which have no influence on the joins is usually no problem. Appending is ... you can limit the Append to one of the Tables by

procedure TForm.ADSBeforePost(DataSet: TDataSet);
begin
  inherited;
  TCustomADODataSet(DataSet).Properties['Unique Table'].Value := 'table_small';
end;

but without an Requery you won't get much further. The better way will be setting Values by Procedure e.g. in BeforePost, Requery and Abort.

If your View would be persistent you would be able to use INSTEAD OF Triggers

bummi
  • 27,123
  • 14
  • 62
  • 101
2

Jerry,

I encountered the same problem on FireBird, and from experience I can tell you that it can be made(up to a small complexity) by using CachedUpdates . A very good resource is this one - http://podgoretsky.com/ftp/Docs/Delphi/D5/dg/11_cache.html. This article has the answers to all your questions.

RBA
  • 12,337
  • 16
  • 79
  • 126
0

I have abandoned the original idea of live ADO query updates, as it has become more complex than I can wrap my head around. The scope of the data push project has changed, and therefore this is no longer an issue for me, however still an interesting subject to know.

The new structure of the application consists of attaching multiple "Field Links" on various fields from the original set of data. Each of these links references the original field name and a SQL Statement which is to be executed when that field is being imported. Multiple field links can be on one single field, therefore can execute multiple statements, placing the value in various tables, etc. The end goal was an app which I can easily and repeatedly export a common dataset from an original source to any outside source with different data structures, without having to recompile the app.

However the concept of cached updates was not appealing to me, simply for the fact pointed out in the link in RBA's answer that data can be changed in the database in the mean-time. So I will instead integrate my own method of customizable data pushes.

Jerry Dodge
  • 26,858
  • 31
  • 155
  • 327
  • The real core to the issue I had was the most ideal way to handle this importing process, which is too subjective to ask on StackOverflow. I narrowed it down to the most likely case for this question. However I've shifted paths after seeing how difficult it was. My new structure is well on its way and is very ideal for my situation. – Jerry Dodge Dec 30 '12 at 01:31