SQL Anywhere Mobile - Re: -828 error

Author

Re: -828 error - Yes but....

Donna Osburn

2005-08-24, 9:24 am

The problem is - I need to change it at this time and i
expect some of the records to be clobbered.
There are a series of small tables (6-30 records) that
instead of trying to deal with time stamps for 100 plus
remotes, we simply sync down the entire table each time.
However, we need to first mark all the records as "bad".
Take table A. it has 10 records on the remote. we mark all
10 records as flkeep = 0. Then during the sync, flkeep is
reset to 1 on all records that were actually downloaded (8
records). After the sync is finished, we simply delete all
the records where flkeep = 0 - because they were not touched
by the sync and must be bad data. Its certainly not
graceful - but its worked for 3 years.
NOW - I was marking the flkeep = 0 before the synce.
However, if the unit is warmbooted during the sync or the
sync is shutdown by the user, the after sync code runs and
trashes all the records. SO - i'm trying to get them
marked, synced, and the extras deleted all in the same
section so that the user doesnt shoot themselves in the
foot.
Don't tell me to change to time stamp based syncing for
these tables. Way too much consolidated code would have to
change to make that happen. That is set to change in the
next major revision of our code. Any thoughts on how to
deal with this. I can't just truncate the small tables
before the sync because they are the keys for the huge
tables.
Thanks
Donna

> Donna Osburn wrote:
>
> This indicates that you are modifying a row in the remote
> database that is also being downloaded in the download
> stream. If this error wasn't thrown, then the download
> stream would clobber the change you have made to this row
> (note: the problem is that you have modified a row *in
> between* the time that the upload phase was built and the
> download phase was received...and that row is about to be
> clobbered in the download stream. It is a good thing you
> caught this prior to going into production!!)
>
> Which sp_hook_dbmlsync stored proc did you choose to write
> your code in?
> If you did in in the _upload_end one, then this error is
> quite likely.
> You probably want to put your code into
> sp_hook_dbmlsync_end
or possibly _download_end.
>
> 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/

Breck Carter [TeamSybase]

2005-08-24, 11:25 am

The "truncate" variant of the download_delete_curs
or table might be of
interest: send one row consisting of NULL for each primary key column.

On 24 Aug 2005 06:43:10 -0700, Donna Osburn wrote:
[color=darkred]
>The problem is - I need to change it at this time and i
>expect some of the records to be clobbered.
>There are a series of small tables (6-30 records) that
>instead of trying to deal with time stamps for 100 plus
>remotes, we simply sync down the entire table each time.
>However, we need to first mark all the records as "bad".
>Take table A. it has 10 records on the remote. we mark all
>10 records as flkeep = 0. Then during the sync, flkeep is
>reset to 1 on all records that were actually downloaded (8
>records). After the sync is finished, we simply delete all
>the records where flkeep = 0 - because they were not touched
>by the sync and must be bad data. Its certainly not
>graceful - but its worked for 3 years.
>NOW - I was marking the flkeep = 0 before the synce.
>However, if the unit is warmbooted during the sync or the
>sync is shutdown by the user, the after sync code runs and
>trashes all the records. SO - i'm trying to get them
>marked, synced, and the extras deleted all in the same
>section so that the user doesnt shoot themselves in the
>foot.
>Don't tell me to change to time stamp based syncing for
>these tables. Way too much consolidated code would have to
>change to make that happen. That is set to change in the
>next major revision of our code. Any thoughts on how to
>deal with this. I can't just truncate the small tables
>before the sync because they are the keys for the huge
>tables.
>Thanks
>Donna

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Donna Osburn

2005-08-24, 11:25 am

The table in the example still contains 8 good records.
They cannot be deleted because they are the primary keys for
huge data tables. So the NULL record would not work.

I suppose this all boils down to poor database design but
with 5 tables under 10 records - i could not justify storing
that data 100 times in a table that is user specific to
handle deletes. I believe that would be what would be
required. otherwise you could never remove a deleted record
from the table as you would never be sure if all 100 hand
helds had synced in and gotten the delete record. Yes - i
could use triggers to make a shadow table for deletes - but
once again for these very small tables - i couldn't justify
it.

Donna

> The "truncate" variant of the download_delete_curs
or table
> might be of interest: send one row consisting of NULL for
> each primary key column.
>
> On 24 Aug 2005 06:43:10 -0700, Donna Osburn wrote:
>
> all >10 records as flkeep = 0. Then during the sync,
> flkeep is >reset to 1 on all records that were actually
> downloaded (8 >records). After the sync is finished, we
> simply delete all >the records where flkeep = 0 - because
> they were not touched >by the sync and must be bad data.
> Its certainly not >graceful - but its worked for 3 years.
> and >trashes all the records. SO - i'm trying to get them
> to >change to make that happen. That is set to change in
> the >next major revision of our code. Any thoughts on how
> to >deal with this. I can't just truncate the small
> tables >before the sync because they are the keys for the
> huge >tables.
> detected >> >
> remote >> database that is also being downloaded in the
> download >> stream. If this error wasn't thrown, then
> the download >> stream would clobber the change you have
> made to this row >> (note: the problem is that you have
> modified a row *in >> between* the time that the upload
> phase was built and the >> download phase was
> received...and that row is about to be >> clobbered in the
> download stream. It is a good thing you >> caught this
> prior to going into production!!) >>
> write >> your code in?
> is >> quite likely.
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-08-24, 8:25 pm

If the dbmlsync download fails, the download_delete_curs
or truncate
will be rolled back along with everything else.

On 24 Aug 2005 09:18:01 -0700, Donna Osburn wrote:
[color=darkred]
>The table in the example still contains 8 good records.
>They cannot be deleted because they are the primary keys for
>huge data tables. So the NULL record would not work.
>
>I suppose this all boils down to poor database design but
>with 5 tables under 10 records - i could not justify storing
>that data 100 times in a table that is user specific to
>handle deletes. I believe that would be what would be
>required. otherwise you could never remove a deleted record
>from the table as you would never be sure if all 100 hand
>helds had synced in and gotten the delete record. Yes - i
>could use triggers to make a shadow table for deletes - but
>once again for these very small tables - i couldn't justify
>it.
>
>Donna
>
>http://www.amazon.com/exec/obidos/A...7/risingroad-20

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Greg Fenton

2005-08-25, 3:37 am

Donna Osburn wrote:
> The table in the example still contains 8 good records.
> They cannot be deleted because they are the primary keys for
> huge data tables. So the NULL record would not work.
>

Download_delete_curs
or rows are applied before the download_cursor rows
are. So in essence, you would delete all the rows in the table (Breck's
NULL row suggestion) and then download only the rows that you want kept.
You end up with the 8 good rows and no bad rows, which is what your
stored procs were trying to achieve in the first place.

I think Breck's suggestion is the Right Way to go.

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

Donna Osburn

2005-08-25, 11:24 am

When i try this i get the following error:
No Primary Key Value for Foreign Key xxxxx.
There is a data table with 115,000 rows in it which has a
foreign key to these 8 records. I CANNOT delete all the
records in the table. The above error kicks in.
I solved my problem by making the table not unique for a
particular field. When i do that, all the other code in
place handles everything correctly. I like the idea of the
null rows in the download delete cursor and will remember it
for the rewrite of mobilink.

Thanks for your help.
Donna

> Donna Osburn wrote:
>
> Download_delete_curs
or rows are applied before the
> download_cursor rows are. So in essence, you would
> delete all the rows in the table (Breck's NULL row
> suggestion) and then download only the rows that you want
> kept.
> You end up with the 8 good rows and no bad rows, which
> is what your stored procs were trying to achieve in the
> first place.
>
> I think Breck's suggestion is the Right Way to go.
>
> 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