Board index » delphi » ADO not quite clever enough?, please help, urgent

ADO not quite clever enough?, please help, urgent

I have 3 tables on the server:

TLSCase
TLSCaseTraffic
TLSCaseDiviorce.

The second table is and extension of the data in the first table, so
TLSCaseTraffic holds traffic data for the case in TLSCase

Within the DB i've got  PK-FK constraints linking TLSCase &
TLSCaseTraffic.

Within the ADODataset I have the string:

select TLSCaseTraffic.TLSCaseID, UIDCase, TLSCaseTypeID, TLSAspectID,
OpenDate, CloseDate, TLSTrafficOffenceID, Location, Date ,
TLSCase.TLSCaseID as TLSCaseTLSCaseID
FROM TLSCase
 INNER  JOIN
 TLSCaseTraffic
ON
 TLSCaseTraffic.TLSCaseID = TLSCase.TLSCaseID

and I have an on before post that makes the TLSCaseTLSCaseID.Value :=
TLSCaseID.Value

If I have the PK-FK from TLSCase to TLSCaseTraffic then I can insert a
record, the resulting SQL sent to the DB is broken into 2 parts...

Insert xxxxx INTO TLSCase
Insert xxxxx INTO TLSCaseTraffic

This is fine, but when I delete the record the SQL sent to the server
is also ordered TLSCase, TLSCaseTraffic... this then fails on a
constraint error.

If I change the PK-FK to be TLSCaseTraffic to TLSCase then inserts
fail, because the SQL trys to insert the FK before the PK, but if the
records alread exist then it can delete sucessfully.

I've tried to re-order the SQL in the dataset to see if this somehow
hints at which file should get SQL'd first, but it always uses TLSCase
then TLSCaseTraffic.

The problem is that the ADO is clever enough to break the SQL into 2
parts for the updates/deletes but not clever enough to realise that
the order of inserts must be the inverse of the order of deletes...

Is there any way to give a hint to ADO on which order should be used
so that I can do something like...

BeforeDELETE
        ADO.FileOrder := 'TLSCaseTraffic; TLSCase'
end

BeforeINSERT
        ADO.FileOrder := ;TLSCase; TLSCaseTraffic'
end

I guess the important question is, who handles the seperating of the
original SQL into 2 SQL statements, is it borlands VCL or is it the
ADO?

If the ADO then I guess/hope there is a property for this?

If I really have to I can have 3 ADO datasets, one for the SELECT and
the other 2 for the updates/deletes.... but then how do I use the
normal DB aware controls, and pass the changes to the other ADO
datasets for processing, while allowing the SELECT dataset to show any
changes the current user made? I guess there must be someway to tell
the ADO to cancel the updates yet still allow the ADOdataset to think
it was sucesfull?

Jon
--

Integrated 400 Solutions Ltd.

Jonathan Wilson, AS/400 consultant/director

24 Hours: 07775 638904

 

Re:ADO not quite clever enough?, please help, urgent


Why not have two datasets, one for TLSCase and another for TLSCaseTraffic.
Set the parameter for TLSCaseTraffic so it links back to TLSCase and make
your data buttons (add, delete, etc.) point to TLSCase.  Then, in the
AfterInsert of TLSCase you can manually append a record to TLSCaseTraffic
and in the BeforeDelete of TLSCase you could manually delete the
TLSCaseTraffic record.

HTH,

--
Michael Rodriguez
Compeat Restaurant Accounting Systems
http://www.compeat.com
m...@compeat.com

Quote
"Jonathan Wilson" <101572.2...@compuserve.com> wrote in message

news:tphbkt44tk43k944l3oj041qi9m1aihvqe@4ax.com...
Quote
> I have 3 tables on the server:

> TLSCase
> TLSCaseTraffic
> TLSCaseDiviorce.

> The second table is and extension of the data in the first table, so
> TLSCaseTraffic holds traffic data for the case in TLSCase

> Within the DB i've got  PK-FK constraints linking TLSCase &
> TLSCaseTraffic.

> Within the ADODataset I have the string:

> select TLSCaseTraffic.TLSCaseID, UIDCase, TLSCaseTypeID, TLSAspectID,
> OpenDate, CloseDate, TLSTrafficOffenceID, Location, Date ,
> TLSCase.TLSCaseID as TLSCaseTLSCaseID
> FROM TLSCase
>  INNER  JOIN
>  TLSCaseTraffic
> ON
>  TLSCaseTraffic.TLSCaseID = TLSCase.TLSCaseID

> and I have an on before post that makes the TLSCaseTLSCaseID.Value :=
> TLSCaseID.Value

> If I have the PK-FK from TLSCase to TLSCaseTraffic then I can insert a
> record, the resulting SQL sent to the DB is broken into 2 parts...

> Insert xxxxx INTO TLSCase
> Insert xxxxx INTO TLSCaseTraffic

> This is fine, but when I delete the record the SQL sent to the server
> is also ordered TLSCase, TLSCaseTraffic... this then fails on a
> constraint error.

> If I change the PK-FK to be TLSCaseTraffic to TLSCase then inserts
> fail, because the SQL trys to insert the FK before the PK, but if the
> records alread exist then it can delete sucessfully.

> I've tried to re-order the SQL in the dataset to see if this somehow
> hints at which file should get SQL'd first, but it always uses TLSCase
> then TLSCaseTraffic.

> The problem is that the ADO is clever enough to break the SQL into 2
> parts for the updates/deletes but not clever enough to realise that
> the order of inserts must be the inverse of the order of deletes...

> Is there any way to give a hint to ADO on which order should be used
> so that I can do something like...

> BeforeDELETE
> ADO.FileOrder := 'TLSCaseTraffic; TLSCase'
> end

> BeforeINSERT
> ADO.FileOrder := ;TLSCase; TLSCaseTraffic'
> end

> I guess the important question is, who handles the seperating of the
> original SQL into 2 SQL statements, is it borlands VCL or is it the
> ADO?

> If the ADO then I guess/hope there is a property for this?

> If I really have to I can have 3 ADO datasets, one for the SELECT and
> the other 2 for the updates/deletes.... but then how do I use the
> normal DB aware controls, and pass the changes to the other ADO
> datasets for processing, while allowing the SELECT dataset to show any
> changes the current user made? I guess there must be someway to tell
> the ADO to cancel the updates yet still allow the ADOdataset to think
> it was sucesfull?

> Jon
> --

> Integrated 400 Solutions Ltd.

> Jonathan Wilson, AS/400 consultant/director

> 24 Hours: 07775 638904

Re:ADO not quite clever enough?, please help, urgent


On Fri, 6 Jul 2001 10:46:50 -0500, "Michael Rodriguez"

Quote
<mike@yada_yada_yada.compeat.com> wrote:
>Why not have two datasets, one for TLSCase and another for TLSCaseTraffic.
>Set the parameter for TLSCaseTraffic so it links back to TLSCase and make
>your data buttons (add, delete, etc.) point to TLSCase.  Then, in the
>AfterInsert of TLSCase you can manually append a record to TLSCaseTraffic
>and in the BeforeDelete of TLSCase you could manually delete the
>TLSCaseTraffic record.

I had thought of setting a detail/master relationship up (although
there would only ever be a 1-1 relationship) but three problems occur,

The first is that as each TLSxxx is read the program fires off a
select SQL to the other TLSxxx2. so the over head can be significent.

The second problem is that I need to somehow enclose both datasets
into 1 single commit processes to prevent partial updates.

The third is when the records presented need to be selected based on
the value of fields from both files.

None of my forms allow "grid" editing, rather the program presents a
list that the user selects items from, these are then displayed one at
a time for manipulation/deletion.

What I am now wondering is, how can I perform changes to a dataset
without the changes being imposed on the underlying database?

Quote

>HTH,

Thanks

Jon

--

Integrated 400 Solutions Ltd.

Jonathan Wilson, AS/400 consultant/director

24 Hours: 07775 638904

Re:ADO not quite clever enough?, please help, urgent


Quote
> I had thought of setting a detail/master relationship up (although
> there would only ever be a 1-1 relationship) but three problems occur,
> The first is that as each TLSxxx is read the program fires off a
> select SQL to the other TLSxxx2. so the over head can be significant.

You will always have a select fired against both tables, no matter what you
do.  Not sure I understand this one.

Quote
> The second problem is that I need to somehow enclose both datasets
> into 1 single commit processes to prevent partial updates.

Use a lock type of ltBatchOptimistic and commit all of the changes at once
inside of a transaction.

Quote
> The third is when the records presented need to be selected based on
> the value of fields from both files.

When you show the records available for selection, use a separate read-only
query that joins the two tables together.  Then, when they find the record
they want, go back to your two datasets for editing.

Quote
> None of my forms allow "grid" editing, rather the program presents a
> list that the user selects items from, these are then displayed one at
> a time for manipulation/deletion.

That should work fine.

Quote
> What I am now wondering is, how can I perform changes to a dataset
> without the changes being imposed on the underlying database?

???

--
Michael Rodriguez
Compeat Restaurant Accounting Systems
http://www.compeat.com
m...@compeat.com

Re:ADO not quite clever enough?, please help, urgent


On Fri, 6 Jul 2001 14:30:35 -0500, "Michael Rodriguez"

Quote
<mike@yada_yada_yada.compeat.com> wrote:
>> I had thought of setting a detail/master relationship up (although
>> there would only ever be a 1-1 relationship) but three problems occur,
>> The first is that as each TLSxxx is read the program fires off a
>> select SQL to the other TLSxxx2. so the over head can be significant.

>You will always have a select fired against both tables, no matter what you
>do.  Not sure I understand this one.

This is negated by your sugestion to have a read only set for the list
presentation. It would only have been a problem if the list was
generated via 2 seperate datasets, as each record in the primary table
was read then the program would have fired the select for the second
dataset.

The problem would have been similar to the way the TDBNav control
performs a refresh, instead of re-issuing the original SQL it performs
a select for every record currently in the dataset.... boy is this
slow and inefficient, or what!

Quote

>> The second problem is that I need to somehow enclose both datasets
>> into 1 single commit processes to prevent partial updates.

>Use a lock type of ltBatchOptimistic and commit all of the changes at once
>inside of a transaction.

With ADO is the transaction based upon the connection? So if I have
many datasets that I want to be under a common transaction control
they need to use the same connection to link them all together?

Also does ADO allow nested transaction? While I'm sure I don't need
this facility, it may be useful where I modularise my code, so I don't
need to keep track of who/where the commits begin and end, so I don't
end up commiting a transaction for one part of the program while
another part performs it's own commit.

Quote

>> The third is when the records presented need to be selected based on
>> the value of fields from both files.

>When you show the records available for selection, use a separate read-only
>query that joins the two tables together.  Then, when they find the record
>they want, go back to your two datasets for editing.

>> None of my forms allow "grid" editing, rather the program presents a
>> list that the user selects items from, these are then displayed one at
>> a time for manipulation/deletion.

>That should work fine.

>> What I am now wondering is, how can I perform changes to a dataset
>> without the changes being imposed on the underlying database?

The read only dataset you suggest needs to be "re-freshed", or at
least kept in sync with the current users changes.

If the user has added 3 records, deleted 1, and amended 4 records on
the updateable datasets [edit form], then the program aught to reflect
these changes in the read only dataset [list form].

I realise that I can just close and open the read only dataset,
forcing it to re-run the SQL, but if this takes 30 seconds then the
user is forced to wait.

I know from experience that the users will not be worried about seeing
changes performed by other users in "live time", they know that to see
the current "live" data off everyone will involve refreshing the
lists. But they will have a problem if they have deleted a record on
the edit form, but it still shows in the list form.  

Is it possible to have an updateable dataset that is "disconnected"
from the database? If this is possible then I could perform the
updates/changes/deletes in the "list dataset" for presentation
purposes, then just drop the dataset and discard the changes when the
user manually requests the list is refreshed from the backend
database.

The "real" updateable datasets that are used to present the edit
screen would perform live updates and selects as the user presses the
Apply button.

Quote

>???

Jon

--

Integrated 400 Solutions Ltd.

Jonathan Wilson, AS/400 consultant/director

24 Hours: 07775 638904

Re:ADO not quite clever enough?, please help, urgent


Quote
"Jonathan Wilson" <101572.2...@compuserve.com> wrote in message

news:fggdkt8pf3q98635mj56c8lp0u4ml9hakc@4ax.com...

Quote
> On Fri, 6 Jul 2001 14:30:35 -0500, "Michael Rodriguez"
> <mike@yada_yada_yada.compeat.com> wrote:

> With ADO is the transaction based upon the connection? So if I have
> many datasets that I want to be under a common transaction control
> they need to use the same connection to link them all together?

Transactions in ADO are based on connections. For me is good idea to use one
connection with many datasets as possible. Each ADO Connection is overhead
of program.

Quote

> Also does ADO allow nested transaction? While I'm sure I don't need
> this facility, it may be useful where I modularise my code,

ADO support nested transaction by design! But not all providers support this
feature! Nested transaction are very useful, but when you make one whole
system that use in many places nested transactions and then you try to
change ADO provider and it say that nested transaction is not supported -
then I want to kill someone ;).

This is true for Jet provider and MSSQL provider: second do not support of
nested transactions.

Quote
> so I don't
> need to keep track of who/where the commits begin and end, so I don't
> end up commiting a transaction for one part of the program while
> another part performs it's own commit.

I not understand why you do not need to keep track of begin/commits/rollback
of transactions. With nested transaction you may make code of program more
clear and logical, but you stay need to keep track of your transactions.

Quote

> The read only dataset you suggest needs to be "re-freshed", or at
> least kept in sync with the current users changes.

I think that you cannot go on from this need.

Quote

> If the user has added 3 records, deleted 1, and amended 4 records on
> the updateable datasets [edit form], then the program aught to reflect
> these changes in the read only dataset [list form].

> I realise that I can just close and open the read only dataset,
> forcing it to re-run the SQL, but if this takes 30 seconds then the
> user is forced to wait.

> I know from experience that the users will not be worried about seeing
> changes performed by other users in "live time", they know that to see
> the current "live" data off everyone will involve refreshing the
> lists. But they will have a problem if they have deleted a record on
> the edit form, but it still shows in the list form.

I think that this problem is the same as one user delete record - other user
will still see it!

Other Threads