Edit TIBQuery without TIBUpdateSQL - IBX Feature
Mark E.
when not coupled with a TIBUpdateSQL component.
It would be nice if the TIBQuery did not require a
TIBUpdateSQL component to do edits of a dataset.
The situation:
It is better to use a query component than a TIBTable
when working with remote clients via TClientDataSets.
By requiring the TIBUpdateSQL component to accompany
the query, it nearly doubles the number of components
needed in a given DataModule.
Using the same methods introduced in TIBTable, a
TIBQuery could manage its own updates, inserts,
deletes, etc. This would allow for more targeted use
than a Table (where clauses) while not forcing the
component clutter to increase.
It could be implemented as an option like on the
TQuery. A "RequestLive" like property. It would also
need the UpdateType so it could limit it to the
primary key for locating the record to change.
-Mark
Craig Stuntz (TeamB)
"Mark E." wrote:
>
> It seems that the TIBQuery is effectivley read-only
> when not coupled with a TIBUpdateSQL component.
> It would be nice if the TIBQuery did not require a
> TIBUpdateSQL component to do edits of a dataset.
Sounds like TIBDataset to me.
-Craig
--
Craig Stuntz (TeamB) Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog: http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html
Mark E.
> > when not coupled with a TIBUpdateSQL component.
> > It would be nice if the TIBQuery did not require a
> > TIBUpdateSQL component to do edits of a dataset.
>
> Sounds like TIBDataset to me.
If you mean to use the TIBDataset in place of the TIBQuery then that would
only partially solve the problem. If you mean that the change should be
introduced at the TIBDataset component then that would be fine.
One point that I don't believe I expressed before is that if that the SQL
used to perform updates/inserts should have the option of being
automatically and dynamically generated. At present I am not aware of such a
method. It is only available in a component editor but it is not available
at run-time. Additionally, you do not have access to the 'Params' property
that is available on a TIBQuery.
What I am proposing is making it optionally auto-generate the SQL
update/insert/delete/refresh code when the SQL property is set. It would
remove the need for any other UpdateObject components except for the more
complex queries. But for most uses, the needs are simplistic and straight
forward. This suggestion is an addition to the current method of doing
things.
-Mark E.
Craig Stuntz (TeamB)
"Mark E." wrote:
>
> > Sounds like TIBDataset to me.
>
> If you mean to use the TIBDataset in place of the TIBQuery then that would
> only partially solve the problem.
That's what I mean.
> If you mean that the change should be
> introduced at the TIBDataset component then that would be fine.
Can't do, as TIBQuery is not a TIBDataset subclass.
> One point that I don't believe I expressed before is that if that the SQL
> used to perform updates/inserts should have the option of being
> automatically and dynamically generated.
IBX doesn't do this, and for a good reason: IBX will not change your
SQL. This is a *feature*. If you want Insert/Update/Delete SQL
generated for you, an easy option is to use TClientDataset via
TDatasetProvider. The MIDAS/DataSnap resolver will generate the SQL for
you at runtime.
> At present I am not aware of such a
> method. It is only available in a component editor but it is not available
> at run-time. Additionally, you do not have access to the 'Params' property
> that is available on a TIBQuery.
Huh? Or do you mean it's not there at *design time.* I'm pretty sure
the latter is on the list of features to add in a future version of IBX.
Mark E.
SQL
> > used to perform updates/inserts should have the option of being
> > automatically and dynamically generated.
>
> IBX doesn't do this, and for a good reason: IBX will not change your
> SQL. This is a *feature*. If you want Insert/Update/Delete SQL
> generated for you, an easy option is to use TClientDataset via
> TDatasetProvider. The MIDAS/DataSnap resolver will generate the SQL for
> you at runtime.
There are occassions where I want that to happen where a TClientDataset is
not appropriate. Specifically, I have written some software that I am
switching to use Interbase and was looking to IBX as the data access
components. The situtation is one where the database design can change
relatively often. Customers request custom modifications that introduce a
new table or a new field, etc. The server portion of the software prepares
data for an inherently disconnected client. The client needs an SQL database
as well because of the complexity of its tasks. So the server builds a
subset of the data for the client on a rule-based design that can be
hundreds of MB in size. Because it is rule-based, the data format can change
(add a new field or table) and the program continues to work unaltered. The
problem with the IBX components is that a new field change would also
require a new executable that is able to recognize and include that field in
the Update/Insert/Refresh/Delete SQL statements. It is therefore *less*
flexible than the BDE counterpart components.
I agree that in many instances you don't want the SQL to be changed from
under you, but there are situations where you just want it to automatically
do the thing for you. I feel it should be a component property and allow the
develper to determine which behavior is appropriate.
> > At present I am not aware of such a
> > method. It is only available in a component editor but it is not
available
> > at run-time. Additionally, you do not have access to the 'Params'
property
> > that is available on a TIBQuery.
>
> Huh? Or do you mean it's not there at *design time.* I'm pretty sure
> the latter is on the list of features to add in a future version of IBX.
I am refering to the component editor started by double-clicking the
TIBUpdateSQL component. I select "Select Primary Keys" and "Generate SQL" at
design-time. There is no run-time equivelent to do this. If that exisited
then I could at least work around my current problem.
-Mark E.
Craig Stuntz (TeamB)
"Mark E." wrote:
>
> There are occassions where I want that to happen where a TClientDataset is
> not appropriate. Specifically, I have written some software that I am
> switching to use Interbase and was looking to IBX as the data access
> components. The situtation is one where the database design can change
> relatively often. Customers request custom modifications that introduce a
> new table or a new field, etc.
I don't see why the use of CDS is inappropriate here. The
MIDAS/DataSnap resolver will do exactly what you want -- create
INSERT/UPDATE/DELETE queries based on a SELECT query which you pass it
at runtime.
Jeff Overcash (TeamB)
"Craig Stuntz (TeamB)" wrote:
>
>
> I don't see why the use of CDS is inappropriate here. The
> MIDAS/DataSnap resolver will do exactly what you want -- create
> INSERT/UPDATE/DELETE queries based on a SELECT query which you pass it
> at runtime.
>
And with D6/Kylix the IBLocalClientDataset has everything built together
(IBDataset/Provider/ClientDataset).
All you supply is the CommandText to select the data.
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Anyone who cannot cope with mathematics is not fully human.
At best he is a tolerable subhuman who has learned to wear
shoes, bathe and not make messes in the house. (Heinlein)
Mark E.
TIBUpdateSQL to auto-generate (at run-time) the SQL for
Update/Insert/Delete/Refresh.
I have two structurally identical tables. I want to delete from the main
table all records that match the minor (updated data) table. Weird? Yes.
This is to accomadate and use a seperate third-party external database.
There may be 20+ paired up (major, minor) tables. My tool needs to be able
to adapt to the tables as new ones are added or old ones disappear. I cannot
control the format of the database that is provided. I must adapt to it
(which causes headaches because it is poorly designed).
The following SQL syntax is not allowed in InterBase:
delete
from
MAINTABLE main
inner join MINORTABLE minor on
minor.FIELD1 = main.FIELD1 and
minor.FIELD2 = main.FIELD2 and
minor.FIELD3 = main.FIELD3
So to provide a workaround for that, I would like to:
1) Use a TIBQuery with:
select minor.FIELD1, minor.FIELD2, minor.FIELD3
from
MAINTABLE main
inner join MINORTABLE minor on
minor.FIELD1 = main.FIELD1 and
minor.FIELD2 = main.FIELD2 and
minor.FIELD3 = main.FIELD3
2) Use a TIBUpdateSQL component to auto-generate the DELETE SQL (which it
does at design-time) because the same component will be used for an unknown
number of unknown tables.
delete from MAINTABLE
where
FIELD1 = :OLD_FIELD1 and
FIELD2 = :OLD_FIELD2 and
FIELD3 = :OLD_FIELD3
At which point I can walk the table and delete all the records. This
accomplishes the goal.
However, I don't have the run-time ability to force the DELETE SQL to be
generated. Therefore I am left to generating it myself when I know that the
component is fully capable of it.
I chose this method because the TBatchMove.Mode := bmDelete is too slow.
This process may work on several hundred MB of data. Performance is
important though a long wait is expected.
-Mark E.
Mark E.
"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote in message
news:3B2FA02C.FBF990E9@no_spam.vertexsoftware.com...
>
> "Mark E." wrote:
> >
> > There are occassions where I want that to happen where a TClientDataset
is
> > not appropriate. Specifically, I have written some software that I am
> > switching to use Interbase and was looking to IBX as the data access
> > components. The situtation is one where the database design can change
> > relatively often. Customers request custom modifications that introduce
a
> > new table or a new field, etc.
>
> I don't see why the use of CDS is inappropriate here. The
> MIDAS/DataSnap resolver will do exactly what you want -- create
> INSERT/UPDATE/DELETE queries based on a SELECT query which you pass it
> at runtime.
The client program is used by inherently disconnected traveling Sales
Representatives. They connect to the Server via TCP/IP and make the simple
request of "I am Sales Rep L67, get me all updated data since yesterday at X
time." The entire logic for what queries are run and what data is output is
stored/determined at the server. It is Admin configurable to improve
efficiency and performance. The resulting data is compressed (zipped) and
FTP'd down to the client. Zipped datasets save transmission time (since most
are on 56K connections) and are easily backed up once received. The client
decompresses the data files and applies the updates to their local SQL
database.
Since the logic for responding to that Sales Rep's request is stored at the
server, the admin can at any point change the size/type of data provided to
the client. Sales Rep A may be working in the usual territory of Sales Rep B
for a week while the Rep B is on vacation. The Server now sends data for
both territories.
This is more flexible, backward compatible (using ClientDataSets would
require a complete re-write of the client application), and performance
enhanceable than the alternative.
I was trying not to get too muddled in details with my requests. I really
didn't think you'd care to hear the about a complex situtation just so I can
make a feature request.
-Mark Ericksen
Craig Stuntz (TeamB)
"Mark E." wrote:
>
> The following SQL syntax is not allowed in InterBase:
> delete
> from
> MAINTABLE main
> inner join MINORTABLE minor on
> minor.FIELD1 = main.FIELD1 and
> minor.FIELD2 = main.FIELD2 and
> minor.FIELD3 = main.FIELD3
No, but you can do it with a subquery:
DELETE FROM MAINTABLE MAIN
WHERE EXISTS (SELECT
1
FROM
MINORTABLE MINOR
WHERE
MINOR.FIELD1 = MAIN.FIELD1
AND MINOR.FIELD2 = MAIN.FIELD2
AND MINOR.FIELD3 = MAIN.FIELD3)
If performance is not as fast as you like with this query, a stored
proc with a FOR SELECT should be somewhat faster due to IB's
optimization.
HTH,
Mark E.
> > delete
> > from
> > MAINTABLE main
> > inner join MINORTABLE minor on
> > minor.FIELD1 = main.FIELD1 and
> > minor.FIELD2 = main.FIELD2 and
> > minor.FIELD3 = main.FIELD3
>
> No, but you can do it with a subquery:
>
> DELETE FROM MAINTABLE MAIN
> WHERE EXISTS (SELECT
> 1
> FROM
> MINORTABLE MINOR
> WHERE
> MINOR.FIELD1 = MAIN.FIELD1
> AND MINOR.FIELD2 = MAIN.FIELD2
> AND MINOR.FIELD3 = MAIN.FIELD3)
>
> If performance is not as fast as you like with this query, a stored
> proc with a FOR SELECT should be somewhat faster due to IB's
> optimization.
I tried that. It worked well for small datasets. However, when the MINOR
table had 22K records and the MAJOR had 66K it brought the machine to its
knees. I watched 'perfmon' as the query ran and the available RAM dropped
until it hit bottom and the CPU was pegged at 100%. It took about 6min to
run. The RAM was never fully freed either until I closed all connections to
the database. Committing or rollingback transactions had little effect.
I had thought about the Stored Proc idea as well. I was hoping to be able to
write a generic one that could perform the task on all paired tables.
However, I think it more realistic (and simpler) to have the program
dynamically create the stored procedure used for each table pair (MAJOR and
MINOR).
Yup, I think that's what I'll do. It would be faster than doing it in Delphi
code. Thanks for not giving up on me. :)
-Mark E.