TQuery and TUpdateSQL with Updateable data
Piotr Beńke
Hi guys !
I have a problem with creating table using SQL command and TQuery. I
have had used the following SQL
CREATE TABLE "test.dbf" (ID NUMERIC(10,0), DATA NUMERIC(10,5))
When I open the query the EDatabaseError exception has been raisen and
I get the following message
'error createing cursor handler' but the table has been created. What
I'm doing wrong ?
Piotr Beńke
Mark Edington wrote:
> LDDI <ld...@aic.fr> wrote in article <33C34564...@aic.fr>...
> > We are using this feature intensively, but there is still a big
> > problem that we consider as a bug : When we create a master and
> details
> > attach to it, and that we are working with cacheUpdates, when
> applying
> > updates, all the details are lost ! They are deleted when the master
> is
> > applyed. And the only way to keep the details is to "detach" the
> detail
> > from the master before applying master, applying detail, and then
> > reattaching detail (I mean "attaching", putting the datasource
> property
> > of the Detail Query to the master dataSource)
> > And this problem occur even if we use the applyUpdates method of a
> > TDataBase :
> > DataBase.applyUpdates([Qmaster,Qdetail]);
>
> I already responed to this issue on another thread (one on which you
> had also
> posted). Here is my reply in case you missed it: (I've also included
> the
> attachment)
>
> lucas <lu...@iquanta.com> wrote in article
> <33B120...@iquanta.com>...
> > this thread started on June 1st by me. in summary, it seems that
> the
> > bde has a problem when implementing a cachedUpdate in a
> master/detail
> > system via TQuery. setting the detail query datasource to point to
> the
> > master's datasouce and apply a SQL statement in that detail query to
>
> > point to a column of the master. when applyUpdates is performed on
> the
> > master, the connection is lost to the detail file, therefore, any
> and
> > all updates in the detail file are lost.
>
> Working with cached updates when doing master/detail using TQueries is
>
> problematic at best and nearly impossible at worst. The caching
> mechanism
> built into BDE is only designed to cache changes only while a cursor
> is open.
> Ordinarily this is not a problem. However, since the detail query in
> a
> master/detail relationship is constantly being opened and closed it
> makes it
> impractical to cache more than one master and group of related details
> at a
> time (unless you work with an open transaction, but that has problems
> too).
>
> Even working with only a single cached master record has limitations,
> with
> inserted records in particular. If you don't need to cache inserted
> master
> records then you can use cached updates pretty well by adding code to
> the
> BeforeClose event of the detail query to perform the updates:
>
> procedure TForm1.DetailBeforeClose(DataSet: TDataSet);
> begin
> if Master.UpdatesPending or Detail.UpdatesPending then
> Database1.ApplyUpdates([Detail, Master]);
> end;
>
> Unfortunately, this doesn't work for inserted records because in most
> cases
> there is a referential integrity relationship defined between the
> master and
> detail tables which requires that the master record be inserted before
> the
> details can be inserted. It's easy enough to change the BeforeClose
> event to
> handle this:
>
> procedure TForm1.DetailBeforeClose(DataSet: TDataSet);
> begin
> if Master.UpdatesPending or Detail.UpdatesPending then
> if Master.UpdateStatus = usInserted then
> Database1.ApplyUpdates([Master, Detail]) else
> Database1.ApplyUpdates([Detail, Master])
> end;
>
> The bad news is that this still doesn't work. The problem is that the
>
> TDBDataSet.ApplyUpdate method calls Resync (which it shouldn't), and
> this
> forces the detail query to be closed before the updates can be sent to
> the
> server. Since it's not really practical for developers to change the
> implementation of ApplyUpdates I've included a sample app that works
> around
> this problem (if you are interested in the fix to DBTABLES.PAS, let me
> know and
> I'll be happy to give it to you). I'll also make sure that
> ApplyUpdates gets
> fixed for the next release.
>
> Of course this still leaves us with the single cached master
> limitation and if
> you are not using TUpdateSQL you also need to re-execute your query to
> see
> inserted records (a BDE limitation with RequestLive queries). You can
> get
> around these two problems by using a TTable instead of a TQuery but
> that might
> not work for every type of query.
>
> So what is the real solution?
>
> Client datasets. Not only were they designed to work effectively with
> data
> spread across multiple tiers, but they also provide a better data
> caching and
> updating mechanism then cached updates. Because the data in the
> client dataset
> is completely separate from the data in the database, they eliminate
> many of
> the problems inherent in cached updates. They are also more flexible
> in terms
> in the way they sort and filter data, and you can even save the data
> to disk
> and reload it at a later time. Most importantly the update mechanism
> works
> better than the one used by cached updates and eliminates the need for
> the
> TUpdateSQL component.
>
> For many types of applications, I feel that client datasets are a
> better choice
> than cached updates. If you want to try using a client dataset in
> place of
> cached updates here is roughly what you need to do:
>
> o On your existing TQueries, set Active, CachedUpdates and
> RequestLive to
> false.
> o Drop a client dataset on the data module and change the query
> datasource to
> point to it.
> o Add BdeProv to the uses list of the datamodule.
> o You'll need to use code to associate the client dataset with the
> query when
> they
> are in the same data module. Something like this:
>
> ClientDataSet1.Provider := Query1.Provider;
> ClientDataSet1.Open;
>
> o From here you need to change the code which calls applyupdates on
> the query
> or database to call applyupdates on the client dataset.
> o Once you get things working for a single dataset you can connect
> things up
> in master/detail relationships using the CLIENTMD demo as a
> guideline. Coding
> the updates is still a little tricky, but were working on making that
> part
> easier too.
>
> Let me know if you have any problems getting things to work and I can
> help you.
>
> > now we spent a lot of damn money on d3 c/s and we expect some sort
> of
> > response from borland. this is a real bug that many programmers are
>
> > dealing with, but i have found no response from borland personnell.
> we
> > demand some attention on the issue. i believe that the
> implementation
> > of the this concept is important to good database coding and should
> work
> > seamlessly.
>
> I understand. If what I have given you so far is not satisfactory,
> please let
> me know what else you would like me to do.
>
> Mark
>
>
> -------------------------------------------------------------------------------------------------------------
>
>
>
>
>
--
***********************************************
Piotr Beńke - pio...@man.poznan.pl
POZNAN SUPERCOMPUTING AND NETWORKING CENTER
Wieniawskiego 17/19 61-712 POZNAN
phone ([48](0)61)8528503 ext. 263
***********************************************
Robert C. Brock
Yo Piotr:
For any SQL that DOES NOT return a result set (ie, UPDATES, DELETES or
and DDL like CREATE ), you must use the ExecSql method instead of Open.
Rob
Brian Bushay TeamB
>I have a problem with creating table using SQL command and TQuery. I
>have had used the following SQL
>CREATE TABLE "test.dbf" (ID NUMERIC(10,0), DATA NUMERIC(10,5))
>When I open the query the EDatabaseError exception has been raisen and
>I get the following message
>'error createing cursor handler' but the table has been created. What
>I'm doing wrong ?
Are you using ExecSQL to run the Query? You should use it for any
query that does not return an answer set
--
Brian Bushay (TeamB)
Bbu...@DataGuidance.com