SQL Anywhere Mobile - "UNION ALL" in Mobilink (download delete) script? Doesn't seem to work

Author

"UNION ALL" in Mobilink (download delete) script? Doesn't seem to work

Mad Vlad

2006-02-01, 11:25 am

Hi,

ASA & ML 9.0.2.3221

We are using "shadow" <tablename>_deleted approach for syncing deletes in
consolidated with remotes, so most of our download delete cursors are simple
"select PKEYFILD1, PKEYFIELD2... from tablename_deleted where LAST_MODIFIED
>= ?".

Due to complexities in our database design, we were forced to use a join in
the download_delete cursor, and not even a simple three-table join. The
script is a UNION of one three-table join with other three-table join.
Something like:
select t3_deleted.key1, t3_deleted.key2 from t3_deleted, t2_deleted,
t1_deleted where (t1_deleted-join-t2_deleted-join-t3_deleted) and
t3_deleted.last_modified >= ?
UNION ALL
select t3_deleted.key1, t3_deleted.key2 from t3_deleted, t2, t1 where
(t1-join-t2-join-t3_deleted) and t3_deleted.last_modified >= ?

Fails on the first sync on that exact script. Says "Cannot convert 'Hamburg'
to a timestamp". 'Hamburg' is the Mobilink user, by the way.

We tried first half of the UNION on its own - works. Second half of the
UNION on its own - ditto. But UNION (ALL) both scripts together - nope. By
the error message, I have a hunch that dbmlsync actually passes Mobilink
User name as another parameter. download_delete_curs
or should always have
one parameter, but ours has two, due to the UNION. I think that is why is
saying that it cannot convert mobilink user string to a timestamp (I think
it's passing 'Hamburg') to the second part of the union's "?" which should
be last_modified.

Is this the case? And if so, I guess UNIONS are a no-no in Mobilink scripts.
Any way around it, or do we have to change the database design, so we do not
need complex download_delete_curs
or scripts? I was thinking of having a view
of these two queries, and use just a view in download_delete_curs
or to fool
it, but that seems a bit too far-fetched...

Thanks,
Vlad

Reg Domaratzki $iAnywhere Solutions$

2006-02-01, 11:25 am

According to the docs, there are two parameters passed into the
download_delete_curs
or script.

1 - last_download - TIMESTAMP
2 - ml_username - VARCHAR(128)

When you use two ? in your download_delete_curs
or script, MobiLink assumes
your are passing both parameters to the script, but in your case, you want
to pass the first parameter twice. You have two options :

Option #1 : Create a Variable

Note : Assumes ASA consolidated. It can be done this way with other RDBMS,
but the syntax is different, since other RDBMS don't support CREATE
VARIABLE.

Create a few scripts to create and set a variable to store the last_download
timestamp, then modify your download_delete_curs
or to use the variable.
Note that you could also create a variable for the MobiLink user at the same
time if you wanted to.

call ml_add_connection_sc
ript( 'v1', 'begin_connection', 'create variable
@ldt timestamp');
call ml_add_connection_sc
ript( 'v1', 'begin_download', 'set @ldt=?' );
call ml_add_table_script(
'v1', 't1', 'download_delete_cur
sor',
'select pkey from t1_del where last_mod >= @ldt
union all
select pkey from other_del where last_mod >= @ldt '
);

Option #2 : Create a stored procedure that returns a result set.

CREATE PROCEDURE ddc_t1 ( in @ldt timestamp )
begin
select pkey from t1_del where last_mod >= @ldt
union all
select pkey from other_del where last_mod >= @ldt;
end;
call ml_add_table_script(
'v1', 't1', 'download_delete_cur
sor', 'call ddc_t1
( ? )' );

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Mad Vlad" <vlad@NOSPAMpcr.ltd.uk> wrote in message
news:43e0ec6f@forums
-1-dub...
> Hi,
>
> ASA & ML 9.0.2.3221
>
> We are using "shadow" <tablename>_deleted approach for syncing deletes in
> consolidated with remotes, so most of our download delete cursors are
simple
> "select PKEYFILD1, PKEYFIELD2... from tablename_deleted where
LAST_MODIFIED
>
> Due to complexities in our database design, we were forced to use a join
in
> the download_delete cursor, and not even a simple three-table join. The
> script is a UNION of one three-table join with other three-table join.
> Something like:
> select t3_deleted.key1, t3_deleted.key2 from t3_deleted, t2_deleted,
> t1_deleted where (t1_deleted-join-t2_deleted-join-t3_deleted) and
> t3_deleted.last_modified >= ?
> UNION ALL
> select t3_deleted.key1, t3_deleted.key2 from t3_deleted, t2, t1 where
> (t1-join-t2-join-t3_deleted) and t3_deleted.last_modified >= ?
>
> Fails on the first sync on that exact script. Says "Cannot convert
'Hamburg'
> to a timestamp". 'Hamburg' is the Mobilink user, by the way.
>
> We tried first half of the UNION on its own - works. Second half of the
> UNION on its own - ditto. But UNION (ALL) both scripts together - nope. By
> the error message, I have a hunch that dbmlsync actually passes Mobilink
> User name as another parameter. download_delete_curs
or should always have
> one parameter, but ours has two, due to the UNION. I think that is why is
> saying that it cannot convert mobilink user string to a timestamp (I think
> it's passing 'Hamburg') to the second part of the union's "?" which should
> be last_modified.
>
> Is this the case? And if so, I guess UNIONS are a no-no in Mobilink
scripts.
> Any way around it, or do we have to change the database design, so we do
not
> need complex download_delete_curs
or scripts? I was thinking of having a
view
> of these two queries, and use just a view in download_delete_curs
or to
fool
> it, but that seems a bit too far-fetched...
>
> Thanks,
> Vlad
>
>

Breck Carter [TeamSybase]

2006-02-01, 11:25 am

You have two "?" in your SELECT and you expect them to both have the
last download timestamp substituted... that won't happen... they are
positional, and the second "?" gets the MobiLink user name value.

One workaround is to call a stored procedure passing it the "?" as
parameter values, then refer to the parameter in the WHERE clause.

Another solution is to fill a global variable with the last download
timestamp in an earlier event and use that variable in the WHERE.

Breck

On 1 Feb 2006 09:14:23 -0800, "Mad Vlad" <vlad@NOSPAMpcr.ltd.uk>
wrote:

>Hi,
>
>ASA & ML 9.0.2.3221
>
>We are using "shadow" <tablename>_deleted approach for syncing deletes in
>consolidated with remotes, so most of our download delete cursors are simple
>"select PKEYFILD1, PKEYFIELD2... from tablename_deleted where LAST_MODIFIED
>
>Due to complexities in our database design, we were forced to use a join in
>the download_delete cursor, and not even a simple three-table join. The
>script is a UNION of one three-table join with other three-table join.
>Something like:
>select t3_deleted.key1, t3_deleted.key2 from t3_deleted, t2_deleted,
>t1_deleted where (t1_deleted-join-t2_deleted-join-t3_deleted) and
>t3_deleted.last_modified >= ?
>UNION ALL
>select t3_deleted.key1, t3_deleted.key2 from t3_deleted, t2, t1 where
>(t1-join-t2-join-t3_deleted) and t3_deleted.last_modified >= ?
>
>Fails on the first sync on that exact script. Says "Cannot convert 'Hamburg'
>to a timestamp". 'Hamburg' is the Mobilink user, by the way.
>
>We tried first half of the UNION on its own - works. Second half of the
>UNION on its own - ditto. But UNION (ALL) both scripts together - nope. By
>the error message, I have a hunch that dbmlsync actually passes Mobilink
>User name as another parameter. download_delete_curs
or should always have
>one parameter, but ours has two, due to the UNION. I think that is why is
>saying that it cannot convert mobilink user string to a timestamp (I think
>it's passing 'Hamburg') to the second part of the union's "?" which should
>be last_modified.
>
>Is this the case? And if so, I guess UNIONS are a no-no in Mobilink scripts.
>Any way around it, or do we have to change the database design, so we do not
>need complex download_delete_curs
or scripts? I was thinking of having a view
>of these two queries, and use just a view in download_delete_curs
or to fool
>it, but that seems a bit too far-fetched...
>
>Thanks,
>Vlad
>

--
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

David Fishburn

2006-02-01, 8:29 pm

"Reg Domaratzki $iAnywhere Solutions$"
<FirstName.LastName@ianywhere.com> wrote in news:43e0f6b0$1@foru
ms-1-dub
of sybase.public.sqlanywhere.mobilink:

Since you are using ASA as your consolidated I would always go
with Option #1 : Create a Variable

--
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]

Mad Vlad

2006-02-02, 7:24 am

Thanks guys

We took that approach, making a couple of connection scripts to note down
the value of "?" and store it in @last_download_time variable, and just use
that in the horrible download_delete_curs
or script. Works like a charm. In
"full" sunchronisation there would be no need for it, but due to some
business rules, there is only a certain subset of data that needs to be
sync'ed. Hence the complicated scripts.

Cheers

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:btp1u11l54pqv0q
lqlg91mt86cn7edre8e@
4ax.com...
> You have two "?" in your SELECT and you expect them to both have the
> last download timestamp substituted... that won't happen... they are
> positional, and the second "?" gets the MobiLink user name value.
>
> One workaround is to call a stored procedure passing it the "?" as
> parameter values, then refer to the parameter in the WHERE clause.
>
> Another solution is to fill a global variable with the last download
> timestamp in an earlier event and use that variable in the WHERE.
>
> Breck
>
>
> On 1 Feb 2006 09:14:23 -0800, "Mad Vlad" <vlad@NOSPAMpcr.ltd.uk>
> wrote:
>
>
> --
> 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

Sponsored Links