SQL Anywhere Mobile - cascade delete columns

Author

cascade delete columns

MJN

2005-04-28, 1:26 pm

Am having a problem on syncing my databases and I believe it must be linked
in with cascade deletes I have on certain tables. I have an example of two
tables script below. The cascade delete is on the FK Sales_id in table
Payment. So when the sync happens it gets all the rows where deleted = Y and
starts to delete them on the remotes. But what I see happening I think is
when the sales table row is deleted then the trigger for the cascade delete
is called and the payment row is also deleted which ends in the sync
throwing an error of "No row in payment table" and thus sync fails.

So what is the best way to handle this? Should I just delete the upload
delete script for all tables that have cascade deletes on the FK's? Or can
it be handled a different way?

Thanks

ASA 9.02 3044

-- scripts for Sales (upload, download and deletes)
call ml_add_table_script(
'v1', 'Sales', 'download_cursor', '
select Sales_Id, Sale_time, QUANTITY, TERM_REMAIN, Amount, START_DATE,
C_CODE, COURSE_NO, PRODUCT_NO, service_no, staff_did, staff_sold, term_date,
c_id, sale_date, amt_remain, inv_no, shop_id, shop_cust, previous_c_amt,
refunded, deleted, last_update
from Sales where deleted = ''N'' and last_update >= ?' );

call ml_add_table_script(
'v1', 'Sales', 'upload_insert', '
insert into Sales (Sales_Id, Sale_time, QUANTITY, TERM_REMAIN, Amount,
START_DATE, C_CODE, COURSE_NO, PRODUCT_NO, service_no, staff_did,
staff_sold, term_date, c_id, sale_date, amt_remain, inv_no, shop_id,
shop_cust, previous_c_amt, refunded, deleted, last_update)
values (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
? )' );

call ml_add_table_script(
'v1', 'Sales', 'upload_update', '
update Sales set Sale_time = ?, QUANTITY = ?, TERM_REMAIN = ?, Amount = ?,
START_DATE = ?, C_CODE = ?, COURSE_NO = ?, PRODUCT_NO = ?, service_no = ?,
staff_did = ?, staff_sold = ?, term_date = ?, c_id = ?, sale_date = ?,
amt_remain = ?, inv_no = ?, shop_id = ?, shop_cust = ?, previous_c_amt = ?,
refunded = ?, deleted = ?, last_update = ?
where Sales_Id = ?' );

call ml_add_table_script(
'v1', 'Sales', 'upload_delete', '
update Sales set deleted = ''Y'' where Sales_Id = ?' );

call ml_add_table_script(
'v1', 'Sales', 'download_delete_cur
sor', '
select Sales_Id from Sales where deleted = ''Y'' ' );

-- scripts for Payments (upload, download and delete )
call ml_add_table_script(
'v1', 'PAYMENT', 'download_cursor', '
select PAYMENT_NUMBER, DATE_PAYED, PAYMENT_FORM, PAYMENT_TYPE2, TERM,
AMOUNT, BALANCE, C_CODE, Sales_Id, pay_time, refund, deleted, last_update
from PAYMENT where deleted = ''N'' and last_update >= ?' );

call ml_add_table_script(
'v1', 'PAYMENT', 'upload_insert', '
insert into PAYMENT (PAYMENT_NUMBER, DATE_PAYED, PAYMENT_FORM,
PAYMENT_TYPE2, TERM, AMOUNT, BALANCE, C_CODE, Sales_Id, pay_time, refund,
deleted, last_update)
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )' );

call ml_add_table_script(
'v1', 'PAYMENT', 'upload_update', '
update PAYMENT set DATE_PAYED = ?, PAYMENT_FORM = ?, PAYMENT_TYPE2 = ?, TERM
= ?, AMOUNT = ?, BALANCE = ?, C_CODE = ?, Sales_Id = ?, pay_time = ?, refund
= ?, deleted = ?, last_update = ?
where PAYMENT_NUMBER = ?' );

call ml_add_table_script(
'v1', 'PAYMENT', 'upload_delete', '
update PAYMENT set deleted = ''Y'' where PAYMENT_NUMBER = ?' );

call ml_add_table_script(
'v1', 'PAYMENT', 'download_delete_cur
sor', '
select PAYMENT_NUMBER from PAYMENT where deleted = ''Y'' ' );

--MLSYNCLOG.TXT--

I. 04/29 02:58:26. Downloading into table: SALES
I. 04/29 02:58:26. Delete row:
I. 04/29 02:58:26. <Sales_Id>: 461133
I. 04/29 02:58:26. Delete row:
I. 04/29 02:58:26. <Sales_Id>: 461134
I. 04/29 02:58:26. Delete row:
I. 04/29 02:58:26. <Sales_Id>: 461512
I. 04/29 02:58:26. Delete row:
I. 04/29 02:58:26. <Sales_Id>: 461513
I. 04/29 02:58:26. Delete row:
I. 04/29 02:58:26. <Sales_Id>: 461514
I. 04/29 02:58:26. Delete row:
I. 04/29 02:58:26. <Sales_Id>: 461515
I. 04/29 02:58:26. Delete row:
I. 04/29 02:58:26. <Sales_Id>: 463462
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <Sales_Id>: 465029
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <Sales_Id>: 466174
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <Sales_Id>: 466180
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <Sales_Id>: 466206
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <Sales_Id>: 466208
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <Sales_Id>: 466183
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <Sales_Id>: 466207
I. 04/29 02:58:27. # rows inserted/updated into table SALES : 0
I. 04/29 02:58:27. # rows deleted in table SALES : 14
I. 04/29 02:58:27. Downloading into table: course_current
I. 04/29 02:58:27. # rows inserted/updated into table course_current : 0
I. 04/29 02:58:27. # rows deleted in table course_current : 0
I. 04/29 02:58:27. Downloading into table: PAYMENT
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <PAYMENT_NUMBER>: 465117
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <PAYMENT_NUMBER>: 465131
I. 04/29 02:58:27. SQL statement failed: (100) Row not found
I. 04/29 02:58:27. Delete row:
I. 04/29 02:58:27. <PAYMENT_NUMBER>: 465133
I. 04/29 02:58:27. SQL statement failed: (100) Row not found
I. 04/29 02:58:27. # rows inserted/updated into table PAYMENT : 0
I. 04/29 02:58:27. # rows deleted in table PAYMENT : 1
I. 04/29 02:58:27. 2 delete operations downloaded for table "PAYMENT" were
for rows not found in the remote database.
I. 04/29 02:58:27. Setting last download time to 2005-04-29 02:58:25.713.
I. 04/29 02:58:27. COMMIT
I. 04/29 02:58:27. Completed processing of download stream

Greg Fenton

2005-04-28, 8:26 pm

MJN wrote:
> Am having a problem on syncing my databases and I believe it must be linked
> in with cascade deletes I have on certain tables.

First, note that what you have posted is not resulting in a failed
synchronization. The messages in the synch log are *I*nformation messages:

> I. 04/29 02:58:27. Delete row:
> I. 04/29 02:58:27. <PAYMENT_NUMBER>: 465131
> I. 04/29 02:58:27. SQL statement failed: (100) Row not found

So to that end, there isn't anything you *must* do.

However there are a couple of things you might consider doing:

- disable triggers from firing during the download phase by specifying
the FireTriggers=OFF (it is ON by default)
- don't download deletes for PAYMENT (the deletes for SALES would remove
the referencing rows)
- change the table order such that PAYMENT deletes are sent first

Of those, the last is not advised. We encourage everyone to avoid
specifying the table order (let dbmlsync "do the right thing" based on
your defined RI).

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

MJN

2005-04-29, 3:26 am

Well I think that only leaves me with one option and that is to get rid of
the download deletes.
Cause I have a SP that inserts a payment which gets fired when a sale is
inserted.
Thus if this is not present then the log will not insert the payment row and
this I guess will cause Sync to fail.

Thanks
"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:427145be$1@foru
ms-2-dub...
> MJN wrote:
>
>
> First, note that what you have posted is not resulting in a failed
> synchronization. The messages in the synch log are *I*nformation
> messages:
>
>
> So to that end, there isn't anything you *must* do.
>
> However there are a couple of things you might consider doing:
>
> - disable triggers from firing during the download phase by specifying the
> FireTriggers=OFF (it is ON by default)
> - don't download deletes for PAYMENT (the deletes for SALES would remove
> the referencing rows)
> - change the table order such that PAYMENT deletes are sent first
>
> Of those, the last is not advised. We encourage everyone to avoid
> specifying the table order (let dbmlsync "do the right thing" based on
> your defined RI).
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Greg Fenton

2005-04-29, 9:25 am

MJN wrote:
> Cause I have a SP that inserts a payment which gets fired when a sale is
> inserted.
> Thus if this is not present then the log will not insert the payment row and
> this I guess will cause Sync to fail.
>

Are you saying that you are downloading SALES records without child
PAYMENT records, yet you want on the remote to have PAYMENT records
created (during the synchronization)?

Seems very peculiar. Personally I would be opting to turn off triggers
(note: this only applies during the synchronization download).

But then again, I avoid triggers everywhere I can. I have only found
myself using triggers in the event that I cannot make application
changes. Triggers have their place, but IMO they should be considered a
*last resort* for applying business logic. The complication of your
synchronization scripts you are running into here is just one type of
problem that has me avoid triggers.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

MJN

2005-04-29, 1:26 pm

Hang on now I am getting alittle confused..
Do trigger generated transactions get processed by mlsync?

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:4272457e$1@foru
ms-2-dub...
> MJN wrote:
>
> Are you saying that you are downloading SALES records without child
> PAYMENT records, yet you want on the remote to have PAYMENT records
> created (during the synchronization)?
>
> Seems very peculiar. Personally I would be opting to turn off triggers
> (note: this only applies during the synchronization download).
>
> But then again, I avoid triggers everywhere I can. I have only found
> myself using triggers in the event that I cannot make application changes.
> Triggers have their place, but IMO they should be considered a *last
> resort* for applying business logic. The complication of your
> synchronization scripts you are running into here is just one type of
> problem that has me avoid triggers.
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Greg Fenton

2005-04-29, 8:26 pm

MJN wrote:
> Hang on now I am getting alittle confused..
> Do trigger generated transactions get processed by mlsync?
>

There are 2 extended options we are now talking about :

SendTriggers - upload any rows that were affected by trigger actions
FireTriggers - fire triggers on rows that are downloaded

The default for SendTriggers in 9.x is:

SendTriggers - OFF
FireTriggers - ON

In the SQLAnywhere 9.x online docs, see:

MobiLink Clients
Adaptive Server Anywhere Client Synchronization Parameters
dbmlsync options
dbmlsync extended options
- FireTriggers (ft) extended option
and
- SendTriggers (st) extended option

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links