Board index » off-topic » TClientDataSet ignores DeleteSQL

TClientDataSet ignores DeleteSQL


2005-07-27 03:23:26 AM
off-topic19
Hi there,
I'm using a TClientDataSet, TDataSetProvider, TIBDataSet and TIBTransaction
and connect these.
Then I set the TIBDataSet->SelectSQL to SELECT * FROM sometable, rightclick
and select DataSet editor, choose the ID field as the key field and all
fields for Update fields, click generate SQL.
(i also add 'all fields' to the TIBDataSet, using the fields editor and set
the pfInKey to true).
The first thing that happens (for some tables !!) is that the RefreshSQL
does not show any fields, the Query looks like "SELECT <nothing here!>from
sometable where ID = :ID". The other Statements do have the correct fields
(selected by my in the Update Fields list).
When i change the SelectSQL to "SELECT * FROM someothertable", sometimes
'generate SQL' does show the selected fields in RefreshSQL.
Furthermore when i set DeleteSQL to "DELETE FROM sometable WHERE ID = -1" (a
non existing ID),
call the delete function, the record is deleted... I expected the DeleteSQL
to be executed. In my real-world app, this DeleteSQL sets the IsDeleted
field to true and some other fields that are hard to set by a trigger.
Why isn't the CDS using DeleteSQL ??
Rightclick on the CDS tells me i'm using version 6.06. (ibxpress62). I
installed the latest IBX update from the Borland site. I'm also using BCB 6,
update 4.
Thanks (again),
Richard
 
 

Re:TClientDataSet ignores DeleteSQL

Is DataSetProvider.ResolveToDataSet set to True?
 

Re:TClientDataSet ignores DeleteSQL

Richard Luteijn wrote:
Quote
Why isn't the CDS using DeleteSQL ??
Because CDS/DSP uses TSQLResolver, not DeleteSQL. CDS knows nothing
about IBX. If you want to control deletes write a BeforeUpdateHandler
on the DSP.
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
 

{smallsort}

Re:TClientDataSet ignores DeleteSQL

Thanks a lot !
"Kostas Terzides" < XXXX@XXXXX.COM >wrote in message
Quote
Is DataSetProvider.ResolveToDataSet set to True?
That does work much better.
Richard
 

Re:TClientDataSet ignores DeleteSQL

Thanks Craig,
"Craig Stuntz [TeamB]" < XXXX@XXXXX.COM [a.k.a. acm.org]>wrote
in message news:42e6a1e7$ XXXX@XXXXX.COM ...
Quote
Richard Luteijn wrote:

>Why isn't the CDS using DeleteSQL ??

Because CDS/DSP uses TSQLResolver, not DeleteSQL. CDS knows nothing
about IBX. If you want to control deletes write a BeforeUpdateHandler
on the DSP.
On Kostas' advice i set the DataSetProvider->ResolveToDataSet to true and
this seems to work. My problem with writing a BeforeUpdateHandler is that
it's not so clean. When I see a DeleteSQL property (that is auto generated
and therefore contains text) I assume it will be used, not some event
handler in code.
I hope setting ResolveToDataSet to true doesn't mess other things up so i
will have to do some testing.
Richard Luteijn
 

Re:TClientDataSet ignores DeleteSQL

Richard Luteijn wrote:
Quote
On Kostas' advice i set the DataSetProvider->ResolveToDataSet to true
and this seems to work.
I *strongly* recommend that you do not do that. ResolveToDataSet is
slow and means you end up doing things completely differently from how
DataSnap normally works.
Quote
My problem with writing a BeforeUpdateHandler
is that it's not so clean. When I see a DeleteSQL property (that is
auto generated and therefore contains text) I assume it will be used,
not some event handler in code.
So what happens, then, if you use a dataset other than IBX? What if
it's a dataset like dbExpress, which doesn't have a DeleteSQL (or any
means of generating one, as with the BDE) at all? What if you need to
do something which can't be expressed in a single SQL statement?
The great advantage of BeforeUpdateRecord is that it works for all
datasets and everything you need to do.
Keep in mind that in most cases you shouldn't need BeforeUpdateRecord
or DeleteSQL at all. The SQL generated by TSQLResolver should work
fine. If you are finding that you need to do this routinely, then you
have bigger problems to fix.
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
All the great TeamB service you've come to expect plus (New!)
Irish Tin Whistle tips: learningtowhistle.blogspot.com
 

Re:TClientDataSet ignores DeleteSQL

Kostas Terzides wrote:
Quote
DataSetProvider.ResolveToDataSet
Don't ever use this. See my other message for why.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt
your DBs! Open Edition users, get 6.0.1.6 from mers.com
 

Re:TClientDataSet ignores DeleteSQL

Craig Stuntz [TeamB] wrote:
Quote
Kostas Terzides wrote:


>DataSetProvider.ResolveToDataSet


Don't ever use this. See my other message for why.

I don't, I use dbexpress anyway. IIRC this was introduced mainly for
backward compatibility reasons, so that if the server dataset was BDE,
then the various events (BeforePost e.t.c) could be triggered and I know
this is contradicting the way midas is working. I just gave a quick
answer from the top of my head.
Regards
Kostas
PS:Craig, could I email you?
 

Re:TClientDataSet ignores DeleteSQL

Kostas Terzides wrote:
Quote
PS:Craig, could I email you?
My email address is in the header of my posts, but I strongly prefer
to talk about Delphi stuff in public so that everyone can benefit from
the conversation.
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Please read and follow Borland's rules for the user of their
news server: info.borland.com/newsgroups/guide.html
 

Re:TClientDataSet ignores DeleteSQL

Craig Stuntz [TeamB] wrote:
Quote
Kostas Terzides wrote:


>PS:Craig, could I email you?


My email address is in the header of my posts, but I strongly prefer
to talk about Delphi stuff in public so that everyone can benefit from
the conversation.

I clearly understand that, it's just a question I wanted to make you
about midess project (and my participation in it) and I don't know if
everyone reading this group would be interested in it.
Anyway, I'll go on here. I've been in contact with Dan Miser from
February and added a couple of components I've made in the project.
Since, Dan has already stated that he is about to release a new version
of the project, I would very much appreciate your opinion (as a project
administrator) on my components (any changes needed or whatever). I
don't know if you are currently active on the project or if you have any
time to spare, but I would certainly be very glad to have any feedback.
If you have trouble getting in the CVS, my components are also in cc (23068)
Thanks,
Kostas Terzides
 

Re:TClientDataSet ignores DeleteSQL

Thanks,
"Craig Stuntz [TeamB]" < XXXX@XXXXX.COM [a.k.a. acm.org]>wrote
in message news:42e77dad$ XXXX@XXXXX.COM ...
Quote
I *strongly* recommend that you do not do that. ResolveToDataSet is
slow and means you end up doing things completely differently from how
DataSnap normally works.
Then i will not use it.
I've created a custom component (TClientDataSet descendant with provider)
for one specific project that, amongst other things, when deleting a record
checks if the IsDeleted field exists and then sets that field or otherwise
simply deletes the record.
I'm using the CDS only because it supports CloneCursor and i need to show
data on multiple forms with different views.
I just know that until some (IBX?) update the DeleteSQL actually worked (i
never touched ResolveToDataSet so it must have been false) since some of the
records in the DB have the IsDeleted field set and lately (could have been
months...) records are dissapearing when deleting them.
I'm still wondering why the fields sometimes do not show up (only) in the
Refresh statement when using the DataSet editor.
Thanks a lot !
Richard
 

Re:TClientDataSet ignores DeleteSQL

Richard Luteijn wrote:
Quote
I just know that until some (IBX?) update the DeleteSQL actually
worked (i never touched ResolveToDataSet so it must have been false)
since some of the records in the DB have the IsDeleted field set and
lately (could have been months...) records are dissapearing when
deleting them.
It is not possible for CDS to use DeleteSQL in any other way.
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
How to ask questions the smart way:
www.catb.org/~esr/faqs/smart-questions.html