SQL Anywhere Mobile - Error: no upload script (for a download only table)

Author

Error: no upload script (for a download only table)

Jeffrey Kelly

2005-06-29, 8:25 pm

This is in regards to Mobilink (9.0.2.3044).

I will describe what we are using, what I wanted to happen, what I did,
and the result.

We are using an Oracle (version 9i 9.2.0.1.0) consolidated database, and
I am using ASA (version 9.0.2.3044) as the remote database. We have 18
synchronized tables defined in the consolidated db, some having only
download_cursor scripts, and others having only upload_insert scripts.
I am using only one publication to accomplish synchronization. This
scenario has been working fine.

I wanted to restrict the number of rows downloaded from one of our tables.

I revised a download_cursor script (using the Sybase Central "Mobilink
Synchronization 9" plugin) for that one table by adding a WHERE clause.
I did not add any new versions, I simply edited the existing script in
the consolidated db.

After I revised that script and attempted to synchronize, I received the
following message: "Error: [-10092] There is no upload data script
defined for table: cwt_sampler. If you want to be able to synchronize
anyway, with the risk of potentially losing upload data, use the -fr
switch." The sync server then does a rollback and disconnects from the
consolidated db and shuts down, and does not perform the download stream.

The table "cwt_sampler" is the table whose script I revised, but I do
not want any upload activity for that table.

I had encountered the error regarding "no upload data script" for a
table I only want to download into when I previously had two
publications; one for the upload tables, and another for the download
tables. When I combined them into one publication, that error message
went away.

Can anyone tell me why I am receiving this error message?

Thanks!!

Greg Fenton

2005-06-30, 3:24 am

Jeffrey Kelly wrote:
>
> After I revised that script and attempted to synchronize, I received the
> following message: "Error: [-10092] There is no upload data script
> defined for table: cwt_sampler. If you want to be able to synchronize
> anyway, with the risk of potentially losing upload data, use the -fr
> switch." The sync server then does a rollback and disconnects from the
> consolidated db and shuts down, and does not perform the download stream.
>

I think this error has nothing to do with the download_cursor change you
have made.

That error indicates that your remote is uploading a row that was
inserted, updated or deleted in the table cwt_sampler in the *remote*
and ML notices that you don't have any scripts to handle those uploads.

You can try enabling verbose logging to see the uploaded rows to confirm
that this is the case (use "-v+" on the arguments to either dbmlsync or
dbmlsrv9).

Assuming this is the case (and I don't see why it wouldn't be), your
first step is to determine why a row was modified when you aren't
expecting any such changes in the remote (bug in app? user directly
modifying db? triggers? etc...)

There are a few ways to stop this error, some better than others:

- use the "-fr" option to dbmlsrv9 telling ML to ignore uploaded rows to
tables with no upload scripts

- modify your app and/or db permissions on the remote to avoid users
updating rows in the "download only" tables

- add a WHERE clause in the *publication* to the "download only" tables
in the remote such that no row will ever match for upload. For
example:

WHERE 1 = 0

Personally, I prefer the second option best with the third option as a
good safety valve. The first option is less efficient in that the row
is actually transferred during synchronization, but ML just ignores it.

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/

David Fishburn

2005-06-30, 9:24 am

Greg Fenton <greg. fenton_NOSPAM_@ianyw
here.com> wrote in
news:42c362df$1@foru
ms-2-dub of sybase.public.sqlanywhere.mobilink:

GF> There are a few ways to stop this error, some better than others:
GF>
GF> - use the "-fr" option to dbmlsrv9 telling ML to ignore uploaded rows
GF> to
GF> tables with no upload scripts
GF>
GF> - modify your app and/or db permissions on the remote to avoid users
GF> updating rows in the "download only" tables
GF>
GF> - add a WHERE clause in the *publication* to the "download only"
GF> tables
GF> in the remote such that no row will ever match for upload. For
GF> example:
GF>
GF> WHERE 1 = 0
GF>
GF>
GF> Personally, I prefer the second option best with the third option as
a
GF> good safety valve. The first option is less efficient in that the
row
GF> is actually transferred during synchronization, but ML just ignores

There is a fourth option.
Create the ML upload events for that table, but the scripts would simply
be a comment:

call ml_add_table_script(
'v1.0', 'table_name', 'upload_insert', '
-- This is an intentional comment to ignore changes from the remote
' );

I would highly suggest this approach over the -fr switch being used.
Those warning are there for a very good reason.

If changes to the remote *are* expected, but you *want* to ignore them,
then the 3rd option is the way to go (as Greg suggested):
CREATE PUBLICATION some_name (
TABLE download_only WHERE 1=0,
TABLE both_ways
);

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Jeffrey Kelly

2005-06-30, 1:29 pm

Thanks, that did the trick (there had been data manipulation in the
remote table)!

But even though there are no error messages now, all rows from the
consolidated table are being downloaded, and it seems the the WHERE
clause in the download_cursor script is being ignored.

The consolidated and remote db do have different definitions. The
consolidated table has columns that the remote does not, and it is one
of those columns that I use to qualify in the download_cursor script.
Specifically, the script is: "SELECT sampler FROM cwt_sampler WHERE
entry_date>'01-jan-2000' " and the remote does not contain the column
entry_date.

Could this be the reason all rows are being downloaded, or am I missing
something else?

Thanks again!!

Greg Fenton wrote:

> Jeffrey Kelly wrote:
>
>
> I think this error has nothing to do with the download_cursor change you
> have made.
>
> That error indicates that your remote is uploading a row that was
> inserted, updated or deleted in the table cwt_sampler in the *remote*
> and ML notices that you don't have any scripts to handle those uploads.
>
> You can try enabling verbose logging to see the uploaded rows to confirm
> that this is the case (use "-v+" on the arguments to either dbmlsync or
> dbmlsrv9).
>
> Assuming this is the case (and I don't see why it wouldn't be), your
> first step is to determine why a row was modified when you aren't
> expecting any such changes in the remote (bug in app? user directly
> modifying db? triggers? etc...)
>
> There are a few ways to stop this error, some better than others:
>
> - use the "-fr" option to dbmlsrv9 telling ML to ignore uploaded rows to
> tables with no upload scripts
>
> - modify your app and/or db permissions on the remote to avoid users
> updating rows in the "download only" tables
>
> - add a WHERE clause in the *publication* to the "download only" tables
> in the remote such that no row will ever match for upload. For
> example:
>
> WHERE 1 = 0
>
>
> Personally, I prefer the second option best with the third option as a
> good safety valve. The first option is less efficient in that the row
> is actually transferred during synchronization, but ML just ignores it.
>
> Hope this helps,
> greg.fenton

Greg Fenton

2005-06-30, 8:24 pm

Jeffrey Kelly wrote:
> Specifically, the script is: "SELECT sampler FROM cwt_sampler WHERE
> entry_date>'01-jan-2000' " and the remote does not contain the column
> entry_date.
>

This script will retrieve all of the rows where entry_date is greater
than '01-jan-2000'. It will do this *each* and *every* time that the
remote synchronizes.

What you likely want to do is to use the last_download parameter that is
passed to your download_cursor script (as the first parameter):

WHERE entry_date > '01-jan-2000'
AND entry_date > ?

The '?' is replaced with the value of last_download, the tiemstamp of
the last download for the given remote that is synchronizing. '?' is
set to '01-jan-1900 00:00:00.000' for a "first time" synchronization

The first part of the WHERE clause will ensure you only get data newer
than 2000 on a first-time synch, whereas the second part will only fetch
data newer than the previous synchronization (for subsequent synchs).

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/

Sponsored Links